{"id":77,"date":"2011-12-15T07:14:08","date_gmt":"2011-12-15T07:14:08","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/12\/15\/read-sql-server-error-log-using-tsql-query\/"},"modified":"2011-12-15T07:14:08","modified_gmt":"2011-12-15T07:14:08","slug":"read-sql-server-error-log-using-tsql-query","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/12\/15\/read-sql-server-error-log-using-tsql-query\/","title":{"rendered":"Read SQL Server Error Log Using TSQL Query"},"content":{"rendered":"

Read SQL Server Error Log Using TSQL Query<\/strong><\/p>\n

xp_readerrorlog procedure can be used to read error logs on SQL Server.<\/p>\n

To read error logs you can use the following query:<\/p>\n

[sql]
\nEXEC sys.xp_readerrorlog
\n[\/sql]<\/p>\n

By default this procedure reads the Current SQL Server error log.<\/p>\n

In order to read the archive logs give the Archive log number as a parameter.<\/p>\n

[sql]
\nEXEC sys.xp_readerrorlog 1
\n[\/sql]<\/p>\n

The above TSQL Query reads the first archive log.<\/p>\n

To read the SQL Server agent error log, pass the second parameter value as 2<\/p>\n

[sql]
\nEXEC sys.xp_readerrorlog 0,2
\n[\/sql]<\/p>\n

The above TSQL Statement reads the current error log for sql server agent.<\/p>\n

The xp_readerrorlog procedure also has the ability to search for a string in error logs.
\nSearching error logs for a string can be achieved using the third parameter.<\/p>\n

[sql]
\nEXEC sys.xp_readerrorlog 0,1,’error’
\n[\/sql] <\/p>\n

This TSQL query searches the current sql server error log for the string ‘error’ and returns all the rows that coontain the string error.<\/p>\n

It is also possible to search for two string in the error log. The second string is the fourth parameter for the procedure xp_readerrorlog.<\/p>\n

[sql]
\nEXEC sys.xp_readerrorlog 0,1,’error’,’17836′
\n[\/sql]<\/p>\n

The above TSQL Query searches for rows that contain both the srtings ‘error’ and ‘17836’ in the current sql server error log.<\/p>\n

NOTE:<\/strong>
\nIf the first parameter value exceeds the number of archive error logs present then the procedure gives the error.<\/p>\n

[sql]
\nEXEC sys.xp_readerrorlog 50
\n[\/sql]<\/p>\n

Whey you execute the above query and if the 50th archive error log is not present in the system then you will get the below error:
\n
\nMsg 22004, Level 16, State 1, Line 0
\nxp_readerrorlog() returned error 2, ‘The system cannot find the file specified.’
\n<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

Read SQL Server Error Log Using TSQL Query xp_readerrorlog procedure can be used to read error logs on SQL Server. To read error logs you can use the following query: [sql] EXEC sys.xp_readerrorlog [\/sql] By default this procedure reads the Current SQL Server error log. In order to read the archive logs give the Archive…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[273],"tags":[274,275,276,277,278,279,280],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/77"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=77"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/77\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}