Tuesday, February 28, 2012 9:32 AMNeed help with Script to know
- In the part of logshiping Configuration we need Validation script that mentions that a db is or is not Involved for log shipping.
- we need to know the size of the backups on Primary side and Duration of Restore on Secondary side
Please help on this situation.
Tuesday, February 28, 2012 10:39 AM
1.-- Show Current Status of Log Shipping
exec sp_executesql @stmt=N'exec sp_help_log_shipping_monitor',@params=N''
2. take a look at msdb.dbo.backupset
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
Tuesday, February 28, 2012 11:33 AM
The script You provided showing only DB'S which Databases involved in Logshipping, But i need which Databases are not involved in Logshipping and in the same time we would like to know size of backups and Duration of restore on Replicate side? Can you please help
Tuesday, February 28, 2012 12:08 PM
This article gives you the different ways to monitor logshipping and the transaction log shipping status report looks really good.
Regards, Ashwin Menon My Blog - http:\\sqllearnings.com
Tuesday, February 28, 2012 12:53 PM
On your secondary site, run below statement to query backup/restore info under msdb. see [backup_size]and [Restore_Duration] columns
-- Restore Info
msdb.dbo.restorehistory rh (NOLOCK)
INNERJOINmsdb.dbo.backupset BS (NOLOCK)ONrh.backup_set_id=BS.backup_set_id
INNERJOINmsdb.dbo.backupmediafamily MF(NOLOCK)ONBS.media_set_id =MF.media_set_id
Tuesday, February 28, 2012 2:39 PM
Your Script works but i am not getting Restore_Duration 0 for Databases, How can we fix this?
Tuesday, February 28, 2012 3:21 PM
Tuesday, February 28, 2012 4:21 PM
Yes I am getting those dates. But i am not getting Restore Duration time, Just getting 0 for alll db's, I guess do we need to change to Micro seconds?
Tuesday, February 28, 2012 5:25 PM
Right now is configured to return in Minutes. you can replace MI for SS in below line and you will get the results in Seconds
- Marked As Answer by amber zhangModerator Thursday, March 08, 2012 6:26 AM