SQL 6.5 repair suspect database

[nortelfan]

I have a SQL 6.5 database that is coming up suspect. This is an achive system. Are there any commands that are available to fix/repair the database.

 

[jitter]

You might be looking for this.

sp_resetstatus <db_name>

Here is a link to check out.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ra-rz_9go9.asp

Hope this helps.

jitter

Instead of shooting where I was, you should have shot where I was going to be. - Lrrr (Planet Nintendio64)

 

[nortelfan]

I am using the enterprise manager and the query tool.

Says stored procedure'sp_resetstatus' not found

 

[jitter]

Are you running the stored procedure against the master database? I believe you have to.

jitter

Instead of shooting where I was, you should have shot where I was going to be. - Lrrr (Planet Nintendio64)

 

[nortelfan]

I ran it against the database that is showing suspect.

sp_resetstatus 'databasename'

The result shows

msg 2812, level 16, state 4
Stored procedure 'sp_resetstatus' not found

 

[jitter]

Switch your query tool to the master database and run it from there. You do not want to run it against the suspect database.

jitter

Instead of shooting where I was, you should have shot where I was going to be. - Lrrr (Planet Nintendio64)

 

[jitter]

Do not run it sp_statusreset 'Master' but just make sure you are connected to the master database instead of the suspect one.

Once you have connected to the master database than run the stored procedure sp_resetstatus 'suspectdatabase'.

jitter

Instead of shooting where I was, you should have shot where I was going to be. - Lrrr (Planet Nintendio64)

 

[nortelfan]

My DB:Master is set.

In the Query window, I run the above and get the same message. I know it has to be me, but I don't know what I am doing wrong??

 

[jitter]

you might try this.

Set the SQL Server configuration option Allow Updates to "1". This will allow you to directly edit the master database. 
 
Using the query tool, issue a command similar to the following:


     use master
go
sp_configure "allow updates", 1
reconfigure with override
go

sp_resetstatus YOURDATABASENAME

go
sp_configure "allow updates", 0
reconfigure
go
Set the SQL Server configuration option Allow Updates back to "0". 
Stop and start the SQL Server.

This was taken from :

by Jeff Browning, O.C.P. M.C.S.E. & Maryam Basiji, Network Appliance, Inc.


hope this works.

jitter


Instead of shooting where I was, you should have shot where I was going to be. - Lrrr (Planet Nintendio64)

 

[nortelfan]

Strange -
Every line command works, except for the sp_resetstatus

keeps resulting in Stored procedure 'sp_resetstatus' not found