How to recover SQL Server database from suspect status

One of the database in our SQL Server 2000 environment is in the suspect status. We need to bring it back to the normal status.

The problem occurred because the disk on which the data file and log file for this database were placed ran out of space.

Pls note other databases in the same server are working fine.
Later on more space was made available on this disk. We tried the following options but with no success.

1. Reset the status of database and restarted the SQL Server. After restarting the SQL Server, the database once again was showing the suspect status.
2. Used the same data and log file in another SQL Server and attached with the database in this another SQL Server.
3. Tried dbcc chkdb with repair_allow_data_loss.

Since the database is in suspect status, we are neither able to export the data nor able to back up the database.

Please suggest some options to recover the database from the suspect status. Also it would be great if we can get the commands, scripts to find if the data/log file is corrupt and a way to correct it (even with data loss is fine).

Did you first run a checkdb? What were the errors from the checkdb?

What errors are in the SQL Server error log? Do you have backups available to restore from? That's a better option if the checkdb reported allow_data_loss was the repair level required.

-Sue

|||

Thanks for the response. The problem is resolved. We brought the database in emergency mode and then in single user mode. Later we were able to recover the database using the checkdb utility, but with some loss of data.

How to Reboot a Publication server ?

We have to add some hardware and I was wondering if someone could provide a stepwise tip to do it .

It is a 3 Server Environment Pub/Dist/Sub
All three running under SQL2K
Here is what I am planning.

a. Uncheck the Enable in Distributed Agent for each replication
b. Wait for all replications to complete
c. Shut down Publisher ? Or do I have to do something else before that.

Please explain a little .

ThanksStop Distribution Agent, make CHECKPOINT on Publisher and shut it down. You also could just shut down publisher but it is not a good practise.|||Thanks . I guess I don't have to worry about Log reader agents ! They will die once publisher is out ?

How to Reboot a Clustered SQL Server Agent

If I am running SQL Server 2005 in a clustered environment, what is the safest way to restart the SQL server agent? It is currently running but I need to restart it for maintenance purposes.

Is the safeway way to restart is to login to the SQL Server Management Studio as the system administrator, select the SQL Server Agent object, right-mouse click and select "Restart?"

When you stop agent, all running jobs will stop. This may not be what you want. Other than that, you can stop the service via services manager or enterprise mgr.

|||

if you run SQL 2005 in a cluster environment .

i think you would better to manage the program via "cluster administrator"

So if you want to stop/restart the services running in cluster .you could choose the "cluster administrator"

how to query the SQL server name?

I use :

select @.@.servername to find SQL server name. But it will return the virtual OS name in cluster environment, not SQL server name. I need this server name for error handling as well as generating query scripts.

How to query SQL server name?

You can:

-Query the name through xp_cmdshell, reading it from the SET command
-Creating your own CLR function to read environment variables
-Read the registry for the needed value through xp_regread /buit be careful this is undocumented and might be deprecated in further versions)

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

If you are using SQL Server 2005, then you can use the SERVERPROPERTY built-in to get the various names.

select SERVERPROPERTY('MachineName') as [Cluster Virtual Server Name]

, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as [Cluster Node Name]

Otherwise, you will have to get the netbios name using any of the OS utilities.

|||

1. Suppose I have serveral instance on a cluster, how do I figure out my current connecting virtual sql server name by querying in CMD, or regitry? I do not see how.

2. Wrote CLR will incur higher cost (time to create and install, and maitenance). The cost out weights benefit. I donot want to do.

3. xp_cmdshell is disabled due to our security concern on production system. We do not have plan to use it.

|||I did the query but the SERVERPROPERTY('MachineName') returns OS virtual name not SQL virtual name. I had looked into other serverproperty parameter, none of them will return current SQL virtual server name.