SQL query to check log shipping error

Question

SQL query to check log shipping error

Peter2012

SSCommitted

Points: 1652
More actions
January 12, 2015 at 8:24 pm

#313623

Hi Gurus,
We're using SQL server 2008 R2 and have been receiving the following error messages recently.
----------------------------------------------------------------------------------
Error: 14421, Severity: 16, State: 1.
Date12/1/2015 12:06:00 PM
LogSQL Server (Current - 12/1/2015 5:45:00 PM)
Sourcespid55
Message
Error: 14421, Severity: 16, State: 1.
----------------------------------------------------------------------------------
Date12/1/2015 12:06:00 PM
LogSQL Server (Current - 12/1/2015 5:45:00 PM)
Sourcespid55
Message
The log shipping secondary database DB01.KOP has restore threshold of 180 minutes and is out of sync. No restore was performed for 4 minutes. Restored latency is 182 minutes. Check agent log and logshipping monitor information.
----------------------------------------------------------------------------------
How to use SQL query to extract this information, especially for error 14421?
Appreciate for your help.
Thank you.
- Peter

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 1

Peter2012 (1/12/2015)
Hi Gurus,
We're using SQL server 2008 R2 and have been receiving the following error messages recently.
----------------------------------------------------------------------------------
Error: 14421, Severity: 16, State: 1.
Date12/1/2015 12:06:00 PM
LogSQL Server (Current - 12/1/2015 5:45:00 PM)
Sourcespid55
Message
Error: 14421, Severity: 16, State: 1.
----------------------------------------------------------------------------------
Date12/1/2015 12:06:00 PM
LogSQL Server (Current - 12/1/2015 5:45:00 PM)
Sourcespid55
Message
The log shipping secondary database DB01.KOP has restore threshold of 180 minutes and is out of sync. No restore was performed for 4 minutes. Restored latency is 182 minutes. Check agent log and logshipping monitor information.
----------------------------------------------------------------------------------
How to use SQL query to extract this information, especially for error 14421?
Appreciate for your help.
Thank you.
- Peter

Peter, I'm pretty sure that additional information on the errors are not stored in SQL. Your best bet is to Google these errors.

Unless I'm misunderstanding what you need...

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Peter2012 SSCommitted Points: 1652 More actions · Answer 2

Hi Brandie,

Thank you for your response.

Those errors are coming from SQL logs, so there should a way to extract them using SQL query. This is what I'm looking for.

Thanks.

- Peter

lmarkum SSCertifiable Points: 7989 More actions · Answer 3

Peter2012,

I did a little research and came across a link to a Microsoft Support article that discusses how this error gets triggered. That led me to identify the two Log Shipping stored procedures that contain the data used to trigger the error.

http://support.microsoft.com/kb/329133

http://msdn.microsoft.com/en-us/library/ms180203(v=sql.105).aspx

http://msdn.microsoft.com/en-us/library/ms188331(v=sql.105).aspx

Here is a link to an article on MSSQLTips about querying the SQL Server Error Log.

http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 4

Making the links clickable...

lmarkum (1/14/2015)
Peter2012,
I did a little research and came across a link to a Microsoft Support article that discusses how this error gets triggered. That led me to identify the two Log Shipping stored procedures that contain the data used to trigger the error.
http://support.microsoft.com/kb/329133
http://msdn.microsoft.com/en-us/library/ms180203(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms188331(v=sql.105).aspx
Here is a link to an article on MSSQLTips about querying the SQL Server Error Log.
http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/[/url]

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 5

Peter2012 (1/13/2015)
Hi Brandie,
Thank you for your response.
Those errors are coming from SQL logs, so there should a way to extract them using SQL query. This is what I'm looking for.

So far as I know, there is no documented way to use T-SQL to read the error logs. There may be a third party tool out there, though, that allows you to do so.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Peter2012 SSCommitted Points: 1652 More actions · Answer 6

Hi lmarkum,

Thanks for your reply and help.

It's very useful and that's what I've been looking for 🙂

- Peter