December 11, 2002 at 9:28 am
Hi,
I'm trying to find a way to check whether the db's are all user accessible (so not recovering, restricted mode etc).
I've been looking at two possible solutions.
1) directly querrying the sysdatabases table. If a db is not 'open normal' then generate error in eventlog
2) using a simple select on each db and if it fails generate error in eventlog
Solution 1:
There seem to be only bits for 'exceptions', such as in resticted mode, recovering etc. But none for status normal. Is this true?
Solution 2:
So far I came up with the following structures:
Procedure A runs a cursor for to select the db_names
In each loop of the cursor a stored procedure B is called which does a simple select into a variable.
Immediately after the call to Procedure B I catch the @@ERROR and dump it into @iets (expecting that if a db is not accesible the call to Proc B will fail)
I then do IF(@iets <> 0) generate event.
This resulted in no event even if one of the db's was offline
I then used a temp table to store the @ERROR from the select, along with the db_name. I inserted the temp table in procedure B
immediately after the call to proc B I querry the temp table to fetch the @ERROR.
Again this resulted in no event even if one of the db's was offline.
I've run out of ideas.
Can somebody show me some alternatives?
Thanks in advance,
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
December 11, 2002 at 9:43 am
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
December 11, 2002 at 10:40 am
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.
December 11, 2002 at 10:53 am
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
December 12, 2002 at 1:50 am
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
December 12, 2002 at 8:55 pm
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).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply