Automating status check db's

Question

Automating status check db's

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

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

Greg Larsen SSC-Insane Points: 20961 More actions · Answer 1

I think I would consider using the status bit, but look for whether it is in a mode that is not 'normal', loading, recovery, recovering, etc. This should give what you want, but of course you will need to determine all the statuses that would make a database not 'normal'

Gregory Larsen, DBA

If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

Gregory A. Larsen, MVP

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 2

have you just considered posting a maintenance window when your data bases/servers will be off line? That would be easier than trying to do all this junk.

Steve Jones - SSC Editor SSC Guru Points: 739542 More actions · Answer 3

Why would your servers not be in "normal" mode? IS this a regular occurrance? If you force this manually to do maintenance, then I'd setup a maint window.

At JD EDwards, we have every Fri, 7pm-7am Sat, as a regular maint window. Don't always take it, and there are a number of weeks that are blacked out from changes, but this is a regular time that people know to check for changes if they plan on working.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

Schil SSC-Addicted Points: 433 More actions · Answer 4

Hi all,

thanks for the replies.

It's not that I need a maintenance window. I want to check the status of the db's 24hrs/7days. When at night a db is not 'open normal' the event I drop in the eventlog will cause our stand-by people to be called and they check what's going on.

Furthermore I don't want to have to check EM all the time and would like to be able to respond to not open normal situations, before complains of the users reach me (pro-active).

Checking all the not normal stati and their combinations using the status bits is not really watertight (for example when recovering a db using a third-party tool, the db is left 'loading'. This status is not mentioned in the doc about status bits....).

Kind Regards,

Marcel

With Kind Regards/Met vriendelijke groet

Schil

'It's never ending and never surrendering' Unida 1999

With Kind Regards/Met vriendelijke groet
Schil
'It's never ending and never surrendering' Unida 1999

nkern Grasshopper Points: 14 More actions · Answer 5

I had a similiar problem with failed maint plan jobs along with failed mail - no notification unless I looked in EM or the log files were out of control because backups failing - not a good picture.

What I developed was a 'master list' of Servers, Databases and 'Activities' (e.g, backup database, delete text files, etc.). All backup plans then log to a central server. I then outer join the 'to be monitored' file with the activity from the last 2 days using views with the datediff function. I can determine last or missing (two different views) from this original view.

Using this approach, you could create a table of all databases on all servers (querying sysdatabases). Issue a query against a know table in each database (e.g., sysobjects) loading the result set into a cumulative table. Join these two together and look for nulls or late items.

I'm getting ready to try something similiar (although we have TNG and Rational Robot, both which can monitor either URL's or actual queries in the case of Rational's tool).