It’s usually something simple that’s overlooked that ends up causing the biggest troubleshooting headache. Let me explain. I was creating a test database called “testDB” with a test table called “testTable” (yes I know, I put a lot of thought in the naming of these objects) and when I tried to insert data into this new testTable I got the following error:
Msg 208, Level 16, State 1, Line 1 Invalid object name ‘testTable’.
I attended my very first SQL Server User Group meeting last night and it was awesome! I want to start by giving my friend Tim Radney a BIG thank you for telling me about the importance of attending these sessions!
There were a few times where I had to delete/close an existing SQL Server Trace and create a new one. It’s a 2-step process to completely remove a SQL Server Trace. First, you stop the trace. Second, you delete/close it.
I recently created a database called “testDB” and when I typed “USE testDB” in the SSMS Query window it gave me a little red squiggly underline (see below snapshot).Why did this happen when I just created a new database? That’s because the local IntelliSense cache hasn’t updated yet. Continue reading “How to Refresh IntelliSense Cache in SSMS”
I recently had to resize a data file on of my production SQL Server databases and needed to know an appropriate size to resize it to. So, I created this simple script that queries sys.database_files and brings back the File ID, File Location, File Name, Original Size, Space Used and Space Left.
SELECT sdb.file_id as [File ID],
sdb.physical_name [File Location],
sdb.name [File Name],
CONVERT(numeric(10,2),ROUND(sdb.size/128.,2)) AS [Original Size in MB],
CONVERT(numeric(10,2),ROUND(FILEPROPERTY(sdb.name, 'SpaceUsed')/128.,2)) AS [Space Used in MB],
CONVERT(numeric(10,2),ROUND((sdb.size - FILEPROPERTY(sdb.name, 'SpaceUsed'))/128.,2)) AS [Space Left in MB]
FROM sys.database_files sdb
Before I do any testing I like to execute the sp_cycle_errorlog stored procedure. That way I can quickly see the specific SQL Server log entry without having to scroll / filter through tons of log. Continue reading “How To Use sp_cycle_errorlog”
This morning at work I created a whole bunch of test tables with dummy data in them. After inserting all that data I realized I created the table with the wrong name. So instead of dropping and recreating everything, I used the “sp_rename” stored procedure. The syntax is as follows:
exec sp_rename ‘old Table name’, ‘new Table name’
It’s pretty simple and easy to execute.
One thing to note according to Microsoft (see image below)
If you plan on using sp_rename make sure all your objects (stored procedures, tirggers, functions, etc.) reference the new name or they will break.
I had an incident at work where I had to analyze the SQL Server error log. I was looking for a specific piece of information and so I thought I could just open the error log in notepad and find what I was looking for with the good old “CTRL+F.” That just took longer due to the confusing format. Below is a better and faster way. Continue reading “How to Read SQL Server Error Log Using sp_readerrorlog”