How to fix sqlserver database supect? How to fix database status "Troubleshooting"?
*********************************************************** *********
Author: Huangshan Guangmingding
Mail: leimin@jxfw.com
Version: 1.0.0
Date: 2004-1-30
(If you need to reprint, please indicate the source! If you have any questions, please send me Mail :-))
*********************************************************** ******** There is always such a problem in MS SQL Server, and SQL Server's status ", let's first analyze the reason for the SQL Server database" Troubleshoot: 1. Error deletion log; 2. Hardware ( HD) damage, causing log and data file writing error; 3. The space of the hard disk is not enough, such as the log file is too large;
Solution:
This is the easiest way to have a full backup of the database, and then restore it. Steps:
1. Delete the original database: Use master Go Drop Database DB_SUEPECT
2. Establish the same name database: Use master go create database db_suspect over (name = dbname_dat, filename = 'c:', size = 10, filegrowth = 5) log on (name = 'dbname_log', filename = 'g:', Size = 5MB, FileGrowth = 5MB) GO
3. Recovery Database: Restore Database DB_SUSPECT from DBNAME_BACKUP.DAT
4. Database Integrity Detection: DBCC Checkdb ('DB_Suspect')
5. Restart the MSSQLServer service.
If there is no full backup, then use some special methods:
1. Set the database to emergency mode Use master go sp_configure 'allow updates', 1 reconfigure with override go update sysdatabases set status = 32768 where name = 'db_suspect' Go
2. Stop SQL Server Service: Net Stop MssqlServer
3. Remove the data file DBNAME_DAT.mdf of the original database, DBNAME_LOG.LDF:
4. Start SQL Server service: Net Start MSSQLServer
5. Re-establish a database DB_SUSPECT for the same name;
USE master GO CREATE DATABASE DB_SUSPECT ON (NAME = DBNAME_DAT, FILENAME = 'C:', SIZE = 10, FILEGROWTH = 5) LOG ON (NAME = 'DBNAME_LOG', FILENAME = 'g:', SIZE = 5MB, FILEGROWTH = 5MB ) GO6. Setting the database to run in single user mode: Use master Go alter Database DB_SUSPECT SET SINGLE_USER GO
7. Stop SQL Services: Net Stop MssqlServer
8. Cover the original data file back:
9. Start SQL Server service: Net Start MSSQLServer
10. Reset Status of SQL Server: Use master Go Exec sp_resetstatus "db_suspect"
11. Database Integrity Detection: DBCC Checkdb ('DB_Suspect')
12. Restore the database for multi-user mode: Use master Go alter Database DB_SUSPECT SET MULTI_USER GO
13. Restore the original configuration of SQLServer: Use mat
Go
Update sysdatabases set status = 4194320 where name = 'db_suspect' Go
14. Configuring SQLServer Not Allow Update System Tables: Use Master Go SP_Configure 'Allow Updates', 0 Reconfigure with Override Go
15. Restart the MSSQLServer service:
It is best to restart the operating system
16. Backup Database:
You can use SQLServer Enterprise Manager or T-SQL. Need to back up Master and DB_SUSPECT
