How to check SQL Server Error logs

When you are trying to troubleshoot problems in SQL Server, one of the best places to check is SQL Server Error logs.  These will have basic information on the issue, what was the source of the problem, what time did it occur so on and so forth.  In order to get to the SQL Server Error log, you would need to do connect to the instance and the following:

Server - Management - SQL Server Logs - Current

I have included a screen shot of this right below:

When you go ahead and double click on one of the SQL Server Error logs, it will open up a new Log File Viewer. This will not only contain information on SQL Server Logs, but also SQL Server Agent Logs, Database Mail and Windows Logs like Application, Security and System Logs.  Here’s a screen capture of what I’m talking about:

You will notice that on the left pane, you can choose which particular log you want to work with.  Within this log, you will see a Current Log and a set of Archive Logs.  Every time SQL Server or the SQL Server Agent is restarted, SQL Server goes ahead and recycles the log and creates a new one.  In this manner you can go back in time to see what issues happened with SQL Server.

On the right side you will have the details pane with information on particular SQL Server related event, when it happened? what was the source? which SQL Server process generated it ETC.  You can further filter these results if you like, you can search for a particular string and even export this SQL Server log to a text file if you need to save it or email it to somebody.

Sometimes the SQL Server error log does not load properly using the SQL Server Management Studio.  For these occasions, SQL Server has an undocumented feature that will let you let you load up SQL Server logs and SQL Server Agent logs using an extended stored procedure.  The procedure is this one xp_readerrorlog and I have included an example of how to use this.



/*------------------------------------------------------------------------------------------------------------

In this script we are looking at two important stored procedures

-master..xp_readerrorlog
-master..xp_fixeddrives

we cover xp_cmdshell in this script using_xp_cmdshell_0011.sql

------------------------------------------------------------------------------------------------------------*/



--reads the current sql server log

exec master..xp_readerrorlog
exec master..xp_readerrorlog 0, 1


--reads the previous sql server log

exec master..xp_readerrorlog 1, 1




--reads the current sql server agent log

exec master..xp_readerrorlog 0, 2


--reads the current sql server agent log

exec master..xp_readerrorlog 1, 2



--get information on disk drives from within sql server


exec master..xp_fixeddrives

/* --OUTPUT

C    30371
D    25803
Q    1011
R    189
S    80834
T    19013

*/


For more information on SQL Server tips and tricks, please visit our site on SQL Server 2008
http://sqlserver2008tutorial.com/

TAGS include SQL Server Error logs, SQL Server Agent logs, Windows NT Log

PS. Happy Birthday baby Sofia, she turns 6 today!!

Timeout expired. The timeout period elapsed prior to completion of the operation

Timeout expired. The timeout period elapsed prior to completion of the operation

This is by far my favorite error in Microsoft SQL Server 2008 to date!!!
Let us say that you are trying to modify an existing table using SQL server management studio. Using SSMS Object Explorer, you browse down to the Database, Tables down to the specific table. Right click on the table and select Modify. Next you find the field that needs to be renamed or maybe the size needs to be changed or worse the field needs to be a new data type (yes this does happen in REAL life all the time). Regardless you go ahead and make the change and then try to save your work and then BAMM!! You get this nice wonderful error. In my case I was trying to add a Primary key to a table when this happened. I have included screen shots and some description here.

'stock_price_historical2' table



- Unable to create index 'PK_stock_price_historical'.


Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.


Could not create constraint. See previous errors.
--Error 2

===================================

Next I get this fine dialog box show below with error description after it;

User canceled out of save dialog



(MS Visual Database Tools)

------------------------------


Program Location:


at Microsoft.SqlServer.Management.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite)


at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)

So how do you get around this issue. One option is to change the timeout limit under Tools - Options. In my case it is already set to 0 (infinite!)
 
This is what I have discovered when wrestling with the issue in SQL Server Management Studio. If you go ahead and make the change using the graphic user interface, then right before saving the change, you can go ahead and generate a SQL script that will encompass the modification that you are trying to make through SQL 2008 Management Studio. I know the real DBA'S as are saying, why even bother with this and use the TRANSACT SQL (TSQL) from the getgo. Good point and I am in agreement here 100%. However if you are new to these DBA tasks, this may be your only option.

Getting back to the point, I’m going to show you an example where I was trying to add a primary key to my table, a simple task that you would think, however I’m getting my favorite error so this is what I’m going to do next. I’m going to go to modify the table, right click and choose Set Primary Key as shown below.

I

Next instead of saving this change, I’m going to right click in the empty space and select Generate Change Script. This is also shown below so you can see this in action.

SQL server will then go ahead and generate the change script including all the necessary sql code that you need. You can either select the portion that you like or you can save the whole SQL code to a SQL script. In our case I have just highlighted the portion (ALTER TABLE) that I need.

Finally you can open up a new SQL window, paste the code as shown above and execute the bad boy!!Pretty cool to get around the Annoying Timeout error in my opinion. Hope that helps.

Tags:Timeout expired. The timeout period elapsed prior to completion of the operation

How do you do loop through records one at a time in SQL server?

How do you do loop through records one at a time in SQL server?

This task is quite elementary when it comes to regular programming, however this can be challenging when we are talking about programming in SQL server. The reason for this is that Transact SQL is best at fetching recordsets altogether and not one record at a time. It is optimized for performance and hence is the happiest when working with a bunch of records.

Nevertheless we need to be able to loop through records one at a time. You may want to get customer id from a CUSTOMER table, then use this primary key to pull related data from the Orders table. One way you can do this is using CURSORS which will offer you the ability to loop through records one at a time. Cursors should never be your first choice as they can add a performance hit when it comes to database tuning. Instead you could use while loop or temp tables to do the same action.
Regardless for this blog post I am including an example of a CURSOR that will loop through all the databases and then switch RECOVERY mode to SIMPLE, one by one.

---

--declaration of variables

declare
@dbnm sysname,
@sql varchar(100)


-- begin cursor to get the database names

declare cursor_db cursor
for select name from sys.databases where name != 'tempdb'

-- using a cursor to loop through database names and change recovery model

open cursor_db
fetch next from cursor_db into @dbnm

while @@fetch_status = 0

begin

--print 'database is ' + @dbnm

set @sql='alter database ' + @dbnm + ' set recovery simple'
print 'sql is ' + @sql
exec (@sql)


fetch next from cursor_db into @dbnm
end

close cursor_db
deallocate cursor_db