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 |
|
HollyMax
Starting Member
7 Posts |
 Posted - 2007-02-07 : 05:46:48
|
| I've posted this before in another section and just realised it wasn't in the correct area, and I'm fairly desperate at this stage to resolve it. The database is in windows 2003 sbs premium with sql 2000 enterprise."can anyone tell me how to run the sp_resetstatus command. It's mentioned everywhere, but I haven't seen anywhere where the starting point is. I'm fairly desperate at this stage to get a problem fixed in a medical database." My guess is that it is run in a SQL scripting area???HollyMax |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 05:59:50
|
| Ideally you should install the SQL Client Tools (which come on the SQL 2000 installation CD) on your PC.Then update them with the latest Service Pack from Microsoft (again, this is on your PC).Alternatively the Client Tools will (should!) have been installed on the Server. You could connect to that (sitting at its terminal, or remotely) and fire them up.Use Query Analyser to run ad-hoc SQL commands like this.I recommend that you set Query Analyser to show the results in Text (press Control-T - if you need Grid display its Control-D)Once you've got it reset, and you can connect to it, I suggest you set it to "Administrator only" to stop any applications connecting to itALTER DATABASE MyDatabaseName SET RESTRICTED_USER WITH ROLLBACK IMMEDIATEthen you should test it (there is presumably a reason why it was set to Suspect in the first place):DBCC CHECKDB('MyDatabaseName') WITH NO_INFOMSGSwhich will churn away for a while and should just give you one line saying something like "0 errors 0 problems found"If that works then the database should be safe to use, and you can remove the "Administrator-only" block:ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATEKristen |
 |
|
|
HollyMax
Starting Member
7 Posts |
Posted - 2007-02-07 : 06:17:39
|
| Thankyou Kristen,I thought it might be in this area, but when I click on either SQL Profiler or SQL Query Analyser I get file not found so the tools mustn't be installed, and I'm logging remotely so I can't get to the Disks to install it.Is this the only way to remove the suspect flag? I can go to Generate SQL Scripts etc..HollyMax |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 07:16:12
|
| Just to check:You are connecting remotely and running Enterprise Manager?And if you click on Query Analyzer from withing Enterprise Manager you are getting some sort of "file not found" error?What happens if you try running Query Analyser from START : Programs : Microsoft SQL Server : Query Analyser?Kristen |
|
|
|
HollyMax
Starting Member
7 Posts |
Posted - 2007-02-07 : 07:36:29
|
| Thanks Kirsten. Actually I just did that and it worked opening throught the start menu. Ran the command to reset and then check as you have written and it came up with 0, 0 errors. Just restarting the server now to see if that has helped. Tried to log into the software and it has put the suspect flag back in. I'm thinking that the file is damaged because I can't copy it with the SQL Service stopped, but maybe there is something else I should do after checking it for errors to run some sort of rebuild. Trouble is I've spent so much time doing a crash course just to learn how to do a reset I haven't look at how I should fix any problems after resetting it.HollyMax |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 07:41:37
|
| "I haven't look at how I should fix any problems after resetting it."Simple advise. If the database failed the DBCC CHECKDB test you are hosed. No sense knowing how to get beyond that step, just plan to restore.Your database passed the CHECKDB test, so its OK (its a very robust test, I doubt there is anything wrong, UNLESS perhaps some options are turned off - such as Torn Page Detection (I think that's in the properties for the database, if not then probably for the server as a whole).Not sure what could be resetting the status. perhaps the application is making some "data integrity checks" (at an application-logic-level)??Try running a backup - we could then try restoring the backup to a NEW database, and swap the names over etc. The Backup will only read the "used" part of the database, so if there is damage elsewhere a backup-and-restore will solve that.We should also perhaps plan to find WHAT is resetting the database status. (SQL Profiler would help with that). If its the application then that moves the responsibility for who will have the knowledge to find WHY its getting reset (i.e. to the application providers)Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 07:43:05
|
| "Try running a backup:"Reset the status againSet the database to RESTRICTED_USERThen:BACKUP DATABASE MyDatabaseName TO DISK = 'x:\path\filename.BAK' WITH NOINIT, STATS=10 |
|
|
|
HollyMax
Starting Member
7 Posts |
Posted - 2007-02-07 : 08:16:57
|
| Now after I do a reset and check it aborts with errors. Also backup aborts with errors. I'm thinking the safest is to do a restore and re-enter 4 hours work than find it fails again and loose more. I've probably run out of time now before the surgery re-opens and seeing I'm getting no support from the software company on this I might have to do the restore. I wish I had a bit more time to learn. Thanks for your assistance Kirsten.HollyMax |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-07 : 09:28:41
|
"Also backup aborts with errors."Check the Event Log on the server in case there are hardware faults which have induced this (check if a RAID drive has failed too - a first RAID failure may go unnoticed, a subsequent one won't!!)If not too late / still possible:What was the error from the backup?Consider:- Rename the existing database
- Restore to a new database (with the original name)
you can then review what caused the problem. A straight restore-over-the-top will preclude that option.Kristen |
|
|
|
|
|
|
|
|
marked suspect resetting status