September 20, 2010 at 12:45 am
Looking to expand on a simple idea I have implemented to monitor just how current a particular database is. Came about when I was having log shipping alerts report "success", but what that really meant was "I encountered no errors when I tried to restore (0) files" and my users were not particularly impressed when I told them everything was fine. Anyway, fast forward a few years and I want to take my idea across my farm.
My monitoring currently works by sitting a table in my production database and scheduling a job to update a time stamp every 15 min. This way I can report to my users on the snapshot they are reporting from, just when the last transactions when into said reports database. Simple really.
Its easy, one table, one row, one job, and whilst I can't think of any reason why I can't just place a copy of this table in each of my databases on my production servers, I was wondering if anyone had a more holistic approach to this. Something with version information, patches etc information that I could replication - not reinventing the wheel and all.
This is part of a DR morning health check, and obviously is an easy task, but would love to know how others monitor their log shipping, mirroring, restore "currency".
September 20, 2010 at 2:39 pm
Have you tried playing with fn_dblog (or ::fn_dblog)?
For log shipping, perhaps cut over to a manual agent job methodology, and have your "up to date" table updated as part and parcel of the log backup job itself.
September 20, 2010 at 11:28 pm
Someone might find this useful in future
I've gone with this approach.
Every db on my 4 production instances has a CopyCheck table.
EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
CREATE TABLE [dbo].[CopyCheck](
[last_update] [smalldatetime] NOT NULL
) ON [PRIMARY]'
Every table needs one seed value
EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
INSERT INTO CopyCheck
VALUES (GETDATE())'
A job to update the value is scheduled for every 15 minutes
EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
UPDATE CopyCheck
SET [last_update]=GETDATE()'
A job is scheduled for every morning on the disaster recovery server to email my DBAs
EXECUTE sp_msforeachdb 'USE ?
IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')
SELECT db_NAME(), last_update
FROM CopyCheck'
Can also be very useful when using copies of databases for report development or even app development to know how current the schema is. I know proper change management would overcome some of this, however users, being users, sometimes just want updated data here, not there and this is handy.
If you find like I did, that you can't trust the log shipping alerts and associated job status, you might find this approach useful. I know its very low tech, however as the chief "accidental dba", I quite like it 😀
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply