January 26, 2004 at 4:41 pm
I was wondering what will be the best way to handle a suspected database as I'm having them daily? The traditional way from BOL is toooooooo long...
Every comment is greatly appreciated.
Thanks,
MJ
January 26, 2004 at 5:37 pm
you need to determine the cause for the suspect flag being set. Do you know the reason?
January 26, 2004 at 9:48 pm
The cause is a very bad network trafic, but as the SQL server runs on NAS and the connections are waiting a lot, there's nothing I could do until somebody realize and buy a separate SQL server. The only thing is to try to handle the situation in the day as fast as I could, because the timing is also an issue.
Thanks a lot.
MJ
January 28, 2004 at 1:46 am
Hi mj, here's what i normally do; (1) In QA point to 'master' db (2) Run: sp_configure 'allow updates', 1 (3) Run: reconfigure with override (4) Run: update sysdatabases set status = 12 where name = DBNAME (5) Run: select * from sysdatabases [ just to check it's status is ok ] (6) Run: sp_configure 'allow updates', 0 (7) Run: reconfigure with override (8) Sql services must be stopped & restarted to access the previous suspect Db !!!! These are the minimum steps that I take to un'suspect' a Db, but there are other ways e.g using sp_resetstatus etc etc I prefer to just keep the steps I listed in a script which I can easily run on any of our servers rather than have to install a supplimentary stored proc on them all !! Hope that helps. j
January 28, 2004 at 2:01 am
Oh yeah, forgot to say ( my disclaimer ! ) that the steps I posted don't always work 100% as it all depends on why the Db was marked suspect !!!, but if it's a simple reason e.g db files were locked by another user when Sql was started, these steps will clear it. P.S Silly question i know but how are you guys getting CR/LF, smileys etc into your replies, mine are all in one big paragraph !!!. Regards j.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply