Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-03-15 : 08:13:24
|
| Tobin writes "I need to be able to detect if a database is going to be 'suspect' without generating an error by trying to operate on the DB before SQL Server marks the database as suspect. I am using the DATABASEPROPERTYEX(Name, 'Status') = 'online') function however, SQL Server marks all databases as online when the service first starts up and doesn't flag it as suspect until after some other process tries to access the database. My proc is probably going to be the first thing that tries to connect to the databases to determine if the database matches some selection criteria.The databases get marked as suspect because the data files have been removed from the system without dropping the database from the server. I know I can (should) clean up the bad databases but this is not the responsibility of the proc that I am writing that tries to find some special databases that may exist on a server.I could also create an autoexec proc that would look at all mounted databases so that SQL server would correctly mark the suspect ones. I am looking for a less obtrusive approach that runs inside of my proc.I am running SQL Server 2000 on Windows XP. Both have all of the service packs installed." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-15 : 08:20:32
|
quote:
I need to be able to detect if a database is going to be 'suspect' without generating an error by trying to operate on the DB before SQL Server marks the database as suspect.
Well, a database is not suspect until it is marked as such, so you cannot test for it in advance. DATABASEPROPERTYX(Name, 'Status') will return "Suspect" as a status too. You can also use DATABASEPROPERTY(name, 'IsSuspect'), it will return 1 for a suspect DB.quote:
I know I can (should) clean up the bad databases but this is not the responsibility of the proc that I am writing...I could also create an autoexec proc that would look at all mounted databases so that SQL server would correctly mark the suspect ones. I am looking for a less obtrusive approach that runs inside of my proc.
I can never understand why people who know how to fix a problem never do, and insist on spending 100 times the effort trying to work around a problem they should never have in the first place. Just drop the database(s) and be done with it. |
 |
|
|
|
|
|
detecting suspect databases in SQL Server