Database suspect __ Database

Information A



1, stop the database server, the database MDF files and LDF files copy backup a copy
2, start the database server, delete the suspect database
3, only with the backup of the database MDF file attached to the database, sp_attach_db or sp_attach_single_file_db can attach a database, similar to the following prompts information:
Device activation error. The physical filename ' c:/program files/microsoft SQL server/mssql/data/mydb_log.ldf ' may be incorrect.
Created named ' C:/Program Files/microsoft SQL server/mssql/data/mydb_log. LDF ' new log file.
This indicates that the database is attached successfully, the problem is solved, if successful, congratulations to you, anyway, I am alphanumeric unsuccessful, hint similar to the following error message
Failed to open new database ' MyDb '. The CREATE DATABASE will terminate.
Device activation error. Physical filename ' E:/www/mydb_log. LDF ' may be wrong.
At this point I used the following methods to solve (refer to the online method).
A Our SQL Server Enterprise Manager has newly established a database with the same name for recovery use (Note: This example is MyDB) with the same name as the problem database.
B Deactivate the database server.
C Delete the log file mydb_log.ldf of the database you just generated (in this case, the name of the column database, actually using your own database name), overwriting the newly generated database data file Mydb_data.mdf with the database MDF file you just backed up.
D Start the database server. You will see that the status of the database MyDB is "suspect." No action can be made on this database at this time.
E. Setting the database allows direct operating system tables. This action allows you to select the database server in SQL Server Enterprise Manager, press the right-key, select Properties, and select "Allow direct modifications to system directories" on the server Settings page. You can also use the following statement to implement it.
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go F. Set MyDB as Emergency Repair mode
In the query Manager, set the following command:
Update sysdatabases set status=-32768 where dbid=db_id (' myDb ') can now see the database in SQL Server Enterprise Manager as read-only/suspect/offline/ Emergency mode "can see the tables inside the database, but only the system tables
G The following performs a real recovery operation to rebuild the database log file
DBCC REBUILD_LOG (' myDb ', ' C:/Program files/microsoft SQL server/mssql/data/mydb_log.ldf ') warns that the database ' myDb ' log has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
When you open the SQL Server Enterprise Manager, you will see that the state of the database is "for dbo use only." You can now access the user table inside the database.
H. Verify database consistency (can be omitted)
The results of the general implementation of the DBCC CHECKDB (' myDb ') are as follows:
CHECKDB found 0 allocation errors and 0 consistency errors (in the database ' MyDb ').
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
I. Set the database to a normal state
sp_dboption ' myDb ', ' dbo use only ', ' false ' J. In the final step, we will restore the "allow direct modifications to the system directory" set in step e. Because the ordinary direct operating system table is a more dangerous thing. Of course, we can recover in SQL Server Enterprise Manager, or we can use the following statement to complete
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go

To this database suspect problem resolution.





Information Two



Recovery step after MS SQL Server database questioning
--sql Server database after suspect recovery step
--1. Recovery steps:
--a. Back up the Smlog_log.ldf file to a different directory;
--b. Rename the Smlog_log.ldf file in the source directory to smlog_log_bak.ldf;
--c. Execute the following statement to modify the state of the database:
Use Master
Go
Update sysdatabases set status=32768 where name= ' database name '--Modify state
Go
Shutdown with NOWAIT-stop database server
Go
--d. Exit SQL and restart SQL in command command-line mode with the following code:
Sqlservr-c-t3608-t4022--Safe mode to start SQL SERVER
--e. Execute the following statement in Query Analyzer to view the state of the database that has just been modified:
Select Name,status from sysdatabases where name= ' database name '
--f. Execute the following code to create a new log file:
DBCC TRACEON (3604)--tracking
DBCC REBUILD_LOG (' database name ', ' log file full path ')--file name must have full paths and extensions
--DBCC rebuild_log (' prs_msc ', ' d:/mscsql/mssql/data/prs_msc_log.ldf
--g. Put the database back into a normal state:
Update sysdatabases set status=0 where name= ' database name '
--h. After restarting the database, execute the following statement to check the database:
DBCC CHECKDB--repair with the following statement if the error is done
I. To repair a database, you must change the database to single user mode:
Exce sp_dboption ' database name ', ' Single user ', ' true '---(' false ' to restore multiple users)
--j. To repair the database by executing the following statement:
DBCC CHECKDB (' database name ', Repair_allow_data_loss)
Repair_allow_data_loss: A more advanced method of repair
Repair_fast: A simple and fast way to repair
/*
The processing state is the "suspect" data library.
Back up the data file, and then follow the steps below:
1. Create a new database with the same name (the data file is identical to the original)
2. Stop SQL Server again (be careful not to detach the database)
3. Overwrite the new database with the data file of the original database
4. Restart SQL Server
5. When you open Enterprise Manager, there will be doubt, regardless, execute the following statement (note modify the database name)
6. After the completion of the general access to the data in the database, at this time, the database itself is generally a problem, the solution is to use the database script to create a new database, and the data to guide the line.

*/
Use MASTER
Go
sp_configure ' ALLOW UPDATES ', 1
Go
Reconfigure with OVERRIDE
Go
UPDATE sysdatabases SET STATUS =32768 WHERE name= ' suspect database name '
Go
sp_dboption ' suspect database name ', ' Single user ', ' true '
Go
DBCC CHECKDB (' Suspect database name ')
Go
Update sysdatabases set status=28 where name= ' suspect database name '
Go
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go
sp_dboption ' suspect database name ', ' Single user ', ' false '
Go



/*
Only the MDF file recovery technology
For a variety of reasons, if we had just backed up the MDF file, it would be a hassle to recover.
If your MDF file is generated by the current database, then it is a fluke that you may be able to recover the database using sp_attach_db or sp_attach_single_file_db, but there will be a hint like the following
Device activation error. The physical filename ' c:/program files/microsoft SQL server/mssql/data/test_log.ldf ' may be incorrect.
Created named ' C:/Program Files/microsoft SQL server/mssql/data/test_log. LDF ' new log file.
However, if your database files are replicated from other computers, unfortunately, perhaps the above approach will not work. You may get an error message similar to the following
Server: Message 1813, Level 16, State 2, line 1
Failed to open new database ' test '. The CREATE DATABASE will terminate.
Device activation error. Physical filename ' D:/test_log. LDF ' may be wrong.
How to do it. Don't worry, here are some examples of recovery options.
*/
--a. We use the default method to create a database (such as test) for recovery use. Can be built in SQL Server Enterprise Manager.
--b. Deactivate the database server.
--c. Delete the log file test_log.ldf the database you just generated, overwriting the database data file you just generated with the database MDF file you want to recover test_data.mdf.
--d. Start the database server. You will see that the state of the database test is suspect. No action can be made on this database at this time.
--e. Setting the database allows direct operating system tables. This action allows you to select the database server in SQL Server Enterprise Manager, press the right-key, select Properties, and select "Allow direct modifications to system directories" on the server Settings page. You can also use the following statement to implement it.
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go
--f. Set test to Emergency Repair mode
--Set the following command in the query manager:
Update sysdatabases set status=-32768 where dbid=db_id (' test ')
--you can see in SQL Server Enterprise Manager that the database is in read/suspect/offline/emergency mode and you can see the tables in the database, but only the system tables
--g. The following performs a real recovery operation to rebuild the database log file
DBCC REBUILD_LOG (' Test ', ' C:/Program files/microsoft SQL server/mssql/data/test_log.ldf ')
/*
During execution, if you encounter the following prompt information:
Server: Message 5030, Level 16, State 1, line 1
Failed to lock the database to perform the operation.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
Indicates that your other program is using the database, and if you have just opened the system table of the test library using SQL Server Enterprise Manager in step F, then quit SQL Server Enterprise Manager.
The prompt for completion should resemble the following:
Warning: Log of database ' Test ' has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
When you open the SQL Server Enterprise Manager, you will see that the state of the database is "for dbo use only." You can now access the user table inside the database.
*/
--h. Verify database consistency (can be omitted)
DBCC CHECKDB (' test ')
/* General implementation results are as follows:
CHECKDB found 0 allocation errors and 0 consistency errors (in the database ' test ').
DBCC execution completed. If DBCC prints an error message, contact your system administrator. */
--I. Set the database to a normal state
sp_dboption ' test ', ' dbo use only ', ' false '
If there is no error, then congratulations, you can now use the restored database normally.
--j. In the final step, we will restore the "allow direct modifications to the system directory" set in step e. Because the ordinary direct operating system table is a more dangerous thing. Of course, we can recover in SQL Server Enterprise Manager, or we can use the following statement to complete
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go


--Log file problem (missing or illegal file format), how to restore the database to normal
-If you use Sp_attach_single_file ' TEST ', ' C:/Program files/microsoft SQL server/mssql/data/test_log.mdf ' failure, you need to complete the following steps
--1. Detach the suspect database and remove or rename the MDF file.
sp_detach_db ' TEST '
--2. Re-create a database with the same name in the original directory test
--3. Stop the SQL Service, overwrite (or rename) the previous MDF file copy back, delete the original log file (or rename it)
--4. Start SQL Service (otherwise the following statement is not able to run)
--5. Set the database to emergency mode (status=32768)
sp_configure ' allow updates ', 1
Reconfigure with override
Update sysdatabases set status=32768 where name= ' TEST '
--Re-establish the log file
DBCC Traceon (3604)
DBCC REBUILD_LOG (' Test ', ' C:/Program files/microsoft SQL server/mssql/data/test_log.ldf ')
Go
--6. Restart SQL Service
--7. Set the database to single user mode (all of the following three statements are available)
--sp_dboption ' TEST ', ' Single user ', ' true '
Update sysdatabases set status=4096 where name= ' TEST '
--alter Database TEST Set Single_user

--8. Check the integrity and consistency of the database, OK you can use the
DBCC CheckDB (TEST)

--9. Set the access rights of the data to multiuser mode
sp_dboption ' TEST ', ' Single user ', ' false '
--or ALTER DATABASE TEST set Multi_user

--10. Turn off advanced options
sp_configure ' allow updates ', 0
Reconfigure with override

--End







SQL Server Installation hangs

When you install SQL Server, "a previous program installation has created a pending file operation on the installation computer." You must restart your computer before running Setup "error. Can't go on. After referring to the relevant information, the following steps can basically be resolved:

1 Remove SQL Server from the Add/Remove program completely.

2 delete the SQL Server directory that is not deleted.

3 Open Registry Editor and locate the PendingFileRenameOperations project in Hkey_local_machine/system/currentcontrolset/control/session Manager , and delete it. This allows you to clear the installation pending items.

4 delete the keys associated with SQL Server in the registry

Attention:
If you do not want to restart the smooth installation, generally in accordance with the third step can be resolved ...





Data three:




--sql Server database after suspect recovery step
--1. Recovery steps:
--a. Back up the Smlog_log.ldf file to a different directory;
--b. Rename the Smlog_log.ldf file in the source directory to smlog_log_bak.ldf;
--c. Execute the following statement to modify the state of the database:
Use Master
Go
Update sysdatabases set status=32768 where name= ' database name '--Modify state
Go
Shutdown with NOWAIT-stop database server
Go
--d. Exit SQL and restart SQL in command command-line mode with the following code:
Sqlservr-c-t3608-t4022--Safe mode to start SQL SERVER
--e. Execute the following statement in Query Analyzer to view the state of the database that has just been modified:
Select Name,status from sysdatabases where name= ' database name '
--f. Execute the following code to create a new log file:
DBCC TRACEON (3604)--tracking
DBCC REBUILD_LOG (' database name ', ' log file full path ')--file name must have full paths and extensions
--DBCC rebuild_log (' prs_msc ', ' dmscsqlmssqldataprs_msc_log.ldf
--g. Put the database back into a normal state:
Update sysdatabases set status=0 where name= ' database name '
--h. After restarting the database, execute the following statement to check the database:
DBCC CHECKDB--repair with the following statement if the error is done
I. To repair a database, you must change the database to single user mode:
Exce sp_dboption ' database name ', ' Single user ', ' true '---(' false ' to restore multiple users)
--j. To repair the database by executing the following statement:
DBCC CHECKDB (' database name ', Repair_allow_data_loss)
Repair_allow_data_loss: A more advanced method of repair
Repair_fast: A simple and fast way to repair

The processing state is a questionable data library.
Back up the data file, and then follow the steps below
1. Create a new database with the same name (the data file is identical to the original)
2. Stop SQL Server again (be careful not to detach the database)
3. Overwrite the new database with the data file of the original database
4. Restart SQL Server
5. When you open Enterprise Manager, there will be doubt, regardless, execute the following statement (note modify the database name)
6. After the completion of the general access to the data in the database, at this time, the database itself is generally a problem, the solution is to use the database script to create a new database, and the data to guide the line.


Use MASTER
Go
sp_configure ' ALLOW UPDATES ', 1
Go
Reconfigure with OVERRIDE
Go
UPDATE sysdatabases SET STATUS =32768 WHERE name= ' suspect database name '
Go
sp_dboption ' suspect database name ', ' Single user ', ' true '
Go
DBCC CHECKDB (' Suspect database name ')
Go
Update sysdatabases set status=28 where name= ' suspect database name '
Go
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go
sp_dboption ' suspect database name ', ' Single user ', ' false '
Go




Only the MDF file recovery technology
For a variety of reasons, if we had just backed up the MDF file, it would be a hassle to recover.
If your MDF file is generated by the current database, then it is a fluke that you may be able to recover the database using sp_attach_db or sp_attach_single_file_db, but there will be a hint like the following
Device activation error. The physical filename ' cprogram filesmicrosoft SQL servermssqldatatest_log.ldf ' may be incorrect.
Created named ' Cprogram filesmicrosoft SQL servermssqldatatest_log. LDF ' new log file.
However, if your database files are replicated from other computers, unfortunately, perhaps the above approach will not work. You may get an error message similar to the following
Server message 1813, Level 16, State 2, line 1
Failed to open new database ' test '. The CREATE DATABASE will terminate.
Device activation error. Physical filename ' Dtest_log. LDF ' may be wrong.
How to do it. Don't worry, here are some examples of recovery options.

--a. We use the default method to create a database (such as test) for recovery use. Can be built in SQL Server Enterprise Manager.
--b. Deactivate the database server.
--c. Delete the log file test_log.ldf the database you just generated, overwriting the database data file you just generated with the database MDF file you want to recover test_data.mdf.
--d. Start the database server. You will see that the state of the database test is suspect. No action can be made on this database at this time.
--e. Setting the database allows direct operating system tables. This action allows you to select the database server in SQL Server Enterprise Manager, press the right-key, select Properties, and select "Allow direct modifications to system directories" on the server Settings page. You can also use the following statement to implement it.
Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with override
Go
--f. Set test to Emergency Repair mode
--Set the following command in the query manager:
Update sysdatabases set status=-32768 where dbid=db_id (' test ')
--you can see in SQL Server Enterprise Manager that the database is in "read-only suspect offline emergency mode" to see the tables in the database, but only the system tables
--g. The following performs a real recovery operation to rebuild the database log file
DBCC REBUILD_LOG (' Test ', ' Cprogram filesmicrosoft SQL servermssqldatatest_log.ldf ')

During execution, if you encounter the following prompt information:
Server message 5030, Level 16, State 1, line 1
Failed to lock the database to perform the operation.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
Indicates that your other program is using the database, and if you have just opened the system table of the test library using SQL Server Enterprise Manager in step F, then quit SQL Server Enterprise Manager.
The prompt for completion should resemble the following:
Warning database ' test ' log has been rebuilt. The consistency of the transaction has been lost. DBCC CHECKDB should be run to verify physical consistency. You will have to reset the database options, and you may need to delete the extra log files.
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
When you open the SQL Server Enterprise Manager, you will see that the state of the database is "for dbo use only." You can now access the user table inside the database.

--h. Verify database consistency (can be omitted)
DBCC CHECKDB (' test ')
The results of the general implementation are as follows:
CHECKDB found 0 allocation errors and 0 consistency errors (in the database ' test ').
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
--I. Set the database to a normal state
sp_dboption ' test ', ' dbo use only ', ' false '
If there is no error, then congratulations, you can now use the restored database normally.
--j. In the final step, we will restore the "allow direct modifications to the system directory" set in step e. Because the ordinary direct operating system table is a more dangerous thing. Of course, we can recover in SQL Server Enterprise Manager, or we can use the following statement to complete
sp_configure ' allow updates ', 0
Go
Reconfigure with override
Go


--Log file problem (missing or illegal file format), how to restore the database to normal
-If you use Sp_attach_single_file ' TEST ', ' Cprogram filesmicrosoft SQL servermssqldatatest_log.mdf ' failure, you need to complete the following steps
--1. Detach the suspect database and remove or rename the MDF file.
sp_detach_db ' TEST '
--2. Re-create a database with the same name in the original directory test
--3. Stop the SQL Service, overwrite (or rename) the previous MDF file copy back, delete the original log file (or rename it)
--4. Start SQL Service (otherwise the following statement is not able to run)
--5. Set the database to emergency mode (status=32768)
sp_configure ' allow updates ', 1
Reconfigure with override
Update sysdatabases set status=32768 where name= ' TEST '
--Re-establish the log file
DBCC Traceon (3604)
DBCC REBUILD_LOG (' Test ', ' Cprogram filesmicrosoft SQL servermssqldatatest_log.ldf ')
Go
--6. Restart SQL Service
--7. Set the database to single user mode (all of the following three statements are available)
--sp_dboption ' TEST ', ' Single user ', ' true '
Update sysdatabases set status=4096 where name= ' TEST '
--alter Database TEST Set Single_user

--8. Check the integrity and consistency of the database, OK you can use the
DBCC CheckDB (TEST)

--9. Set the access rights of the data to multiuser mode
sp_dboption ' TEST ', ' Single user ', ' false '
--or ALTER DATABASE TEST set Multi_user

--10. Turn off advanced options
sp_configure ' allow updates ', 0
Reconfigure with override

--End





Information Four:



How to fix SQL Server database "suspect" (ii) collection
If SQL Server cannot complete recovery of the database due to insufficient disk free space, SQL Server 2000 returns error 1105 and sets the status column in sysdatabases to suspect.

You can see that there should be 1105 error messages in the SQL Server's error log and OS Application log:

The SQL Server transaction log may be filled, which blocks subsequent database operations, including update, DELETE, INSERT, and checkpoint.
Full transaction log fills can cause 1105 errors:
Can ' t allocate space for object syslogs in database dbname because
The logsegment is full. If you are ran out of spaces in syslogs, dump
The transaction log. Otherwise use ALTER DATABASE or
Sp_extendsegment to increase the size of the segment.

This behavior may occur in any database, including master and tempdb. Some unpredictable factors may consume log space. For example:
A large transaction, especially as a batch data update, insert, or delete.
A transaction that has not been committed.
The required bandwidth is too large for a checkpoint handler to intercept.
Truncation exceeded threshold
The results of interaction between the various conditions mentioned above.
The token transaction used for the publication is not read by the Log reader


Here are the steps to repair and shrink the log:

1. Run the following command at a command prompt to start SQL Server:

Sqlserver-f-M

Note: the-m switch starts SQL Server in Single-user mode. In Single-user mode, you can only successfully establish a connection. Note that any other client or service may use that connection before you establish a connection through SQL Server Query Analyzer.

2. Resets the status of the suspect database.

Sp_resetstatus ' database_name '

Here is the result set:

Database ' database_name ' status reset!
Warning:you must reboot SQL Server prior to accessing this database!

3. Add a data file or log file to the database using ALTER database:

Use master
Go
CREATE DATABASE db_name on
(
NAME = DBNAME_DAT1,
FILENAME = ' d:/mssql/data/dbname_dat1.ndf ',
SIZE = 1000MB,
FileGrowth = 50MB
)
Go--Change the database to add a new data file of 2GB size
ALTER DATABASE db_name
ADD FILE
(
NAME = Dbname_dat2,
FILENAME = ' f:/mssql/data/dbname_dat2.ndf ',
SIZE = 2000MB,
FileGrowth = 50MB
)
Go
--Change the database to add a new log file of 1GB size
ALTER DATABASE db_name
ADD LOG FILE
(NAME = db_name_log2,
FILENAME = ' F:/mssql/data/db_name_log2.ldf ',
SIZE = 1000MB,
FileGrowth = 20MB),
Go
4. Stop and restart SQL Server:
With the additional space provided by the new data file or log file, SQL Server should be able to complete the recovery of the database.

5. Free up disk space and rerun the recovery operation, shrink the log by following these steps.
Sp_resetstatus closes the suspect flag for the database, but retains other options for the database intact.


To fundamentally solve such problems, you can configure SQL Server 2000 by following these actions:
A. If you do not need to revert to a specified point in time, you can configure the recovery model for the database to be simple, so
Update,delete,select will not record the log, the log will not be increased significantly:

Use MASTER

Go
ALTER DATABASE db_name SET RECOVERY Simple
B. If your recovery model is full, you must configure the log field to shrink:

Use MASTER

Go
sp_dboption ' databasename ', ' trunc. Log on chkpt. ', True
sp_dboption ' databasename ', ' autoshrink ', true
C. Shrink the log by daily backup:
BACKUP DATABASE database_name to Backup_devices
BACKUP LOG database_name to Log_devices
OR
BACKUP LOG database_name with TRUNCATE_ONLY

* * Check the volume of the log: DBCC sqlperf (logspace) The log does not shrink!

D. Restart the MS SQL Server SERVICE every day after the backup database completes.
Use database_name
Go
DBCC Shrinkfile (2,truncateonly)

* * Check the volume of the log: DBCC sqlperf (Logspace) This time the log has shrunk!

E. Manual Quick Shrink log:
/*run below script,you'll shrink you database log files
Immediately, in I experience,you need to run the script for 3 or
4 minutes before stopping it manually * *
Use DatabaseName
DBCC SHRINKFILE (2,notruncate)
DBCC SHRINKFILE (2,truncateonly)
CREATE TABLE T1 (char1 char (4000))
Go
DECLARE @i int
Select @i=0
while (1=1)
Begin
while (@i<100)
Begin
INSERT into T1 VALUES (' A ')
SELECT @i=@i+1
End
TRUNCATE Table T1
BACKUP LOG Youdatabasename with TRUNCATE_ONLY
End
Go



Note You can use only if you are guided by your primary support provider or have troubleshooting recommended practices
Sp_resetstatus. Otherwise, the database may be corrupted.

Because the process modifies the system tables, the system administrator must enable system table updates before running sp_resetstatus this procedure. To
To enable updates, use the following procedure:

Use master
Go
sp_configure ' allow updates ', 1
Go
Reconfigure with OVERRIDE
Go
When the procedure is created, disable system table updates immediately:

sp_configure ' allow updates ', 0
Go
Reconfigure with OVERRIDE
Go
Only system administrators can perform sp_resetstatus. After you perform this procedure, shut down SQL Server immediately.




Please refer to:

http://support.microsoft.com/default.aspx?scid=kb;zh-cn;317375

http://support.microsoft.com/default.aspx?scid=kb;zh-cn;307775


This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/leimin/archive/2004/02/26/12900.aspx