1. DBCC CHECKDB
After restarting the server, perform the following SQL database repairs in SQL Query Analyzer without doing anything, fixing the consistency errors and allocation errors in the database.
Use master
DECLARE @databasename varchar (255)
Set @databasename = ' Name of the database entity that needs to be repaired '
exec sp_dboption @databasename, n ' single ', n ' true '--set the target data base to Single-user state
DBCC CHECKDB (@databasename, Repair_allow_data_loss)
DBCC CHECKDB (@databasename, Repair_rebuild)
exec sp_dboption @databasename, n ' single ', n ' false '--set target data base to multiuser state
Then perform the DBCC CHECKDB (the name of the database entity to be repaired) to check that the database is still in error. Note: The repair may result in some loss of data.
2. DBCC checktable
If the DBCC CHECKDB check still has errors, you can use DBCC CHECKTABLE to fix them.
Use the name of the database entity that you want to fix
DECLARE @dbname varchar (255)
Set @dbname = ' Name of the database entity that needs to be repaired '
exec sp_dboption @dbname, ' Single user ', ' true '
DBCC CHECKTABLE (' name of the data table to be repaired ', repair_allow_data_loss)
DBCC CHECKTABLE (' name of the data table to be repaired ', repair_rebuild)
------Change the name of the ' data table to be repaired ' to the name of the data table that executes the DBCC CHECKDB times error
exec sp_dboption @dbname, ' Single user ', ' false '
3. Some of the other common repair commands
DBCC Dbreindex rebuilds one or more indexes of a table in the specified database
Usage: DBCC dbreindex (table name, ') fixes all indexes in this table.
===================================
Methods of detecting and repairing SQL Server database
With the promotion of K/3 products, customer service personnel are required to understand the SQL Server database further. In the use of K/3, database files are frequently used, for some reason, the database may be corrupted, this article will be for this situation database detection and repair methods to do a simple explanation. I hope you have new discoveries in the actual work process, timely provide us with information in order to make further updates.
1.1 SQL Server Database detection
SQL Server provides commands for database detection, which can be detected by the DBCC CHECKDB for the allocation of objects in the database and the correctness of the structure, and can be controlled by one parameter to display all error messages. The syntax is as follows:
DBCC CHECKDB
(' database_name ' [, NOINDEX | {Repair_allow_data_loss
| Repair_fast
| Repair_rebuild
}]
) [With {ALL_ERRORMSGS | NO_INFOMSGS}]
Parameter description:
' database_name ' represents the database entity name being detected;
Noindex refers to the non-clustered index of non-system table detection;
Repair_allow_data_loss | Repair_fast| Repair_rebuild refers to the direct repair of the found error, where the REPAIR_ALLOW_DATA_LOSS representative, if this error can not be repaired, the system will directly delete the relevant data. With either of these three parameters, the database must be in Single-user mode, set in the database properties in Enterprise Manager;
The ALL_ERRORMSGS represents all the detected error messages, otherwise, displays up to 200 error messages for each table;
NO_INFOMSGS represents the concealment of all information and space-occupying reports.
After detection, for the wrong object, will be in the form of an Object ID report specific error information, can be based on the object ID to the system table sysobjects to find the relevant table, that is, name.
1.2 SQL Server problem database repair
After the database is tested, the corresponding measures can be taken to deal with the problems that arise. If the physical storage of the object is found to be problematic after detection, the DBCC CHECKALLOC can be used to fix it:
DBCC checkalloc (' database_name ' | Repair_rebuild}]) [With {ALL_ERRORMSGS | NO_INFOMSGS}]
If the index of a non-system object fails, the DBCC DBREINDEX can be used to fix it:
DBCC dbreindex ([' Database.owner.table_name ' [, index_name [, FillFactor]]]) [with NO_INFOMSGS]
In either case, you can also use DBCC CHECKDB (' db_name ', repair_rebuild) directly to fix it.
In another case, when you are testing, you are prompted not to establish a data connection, which indicates that the database is corrupted. In this case, we can take the following measures to try to repair.
First, create a new database in SQL Enterprise, such as a database named Test, and after you have built the database, stop SQL Server Service Manager and rename the MDF file in the customer database to test _data.mdf ( The primary file name of the new database, and then overwrite the file with the same name as the newly renamed file, and then start SQL Server Service Manager. To set the system table to a changed state on the master database
Use Master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go
To set a database as a state of emergency:
Update sysdatabases Set status = 32768 where database '
Stop and restart SQL Server Service Manager and rebuild the log file:
DBCC Traceon (3604)
DBCC rebuild_log (' Test ', ' Test _log_ldf ')
Set the database to Single-user mode and then detect:
sp_dboption ' Test ', ' Single user ', ' true '
DBCC CHECKDB (' test ')
Go
When this database performs CHECKDB, some tables are found to be corrupted, and the index is rebuilt for a specific table:
DBCC dbreindex (table name)
If the above operation is still not resolved, if the index destroyed table is a temporary table or not a key table, you can introduce in the new establish accounts, if the primary table, it may be through the recent backup to (partial) recovery. If you do not have a backup, you cannot repair it.
Why is the 1.3 SQL Server database vulnerable to corruption?
The following are some of the reasons that Microsoft may have caused database corruption and some preventative measures:
Operation problems, including cold starter, hot pull hard drive, delete some database files;
Hardware problems, including disk controller issues;
Operating system problems, including some fatal errors associated with the system.
1.4 Preventive measures:
1, Periodic/irregular execution chkdsk (without parameters) to detect the physical structure of the hard disk and repair some of the problems CHKDSK reported;
2, standing data.
1.5 Application Database Repair examples
DECLARE @databasename varchar (255)
Set @databasename = ' AIS20021224170730 '------must be entered manually
A fix that allows data loss when there is a problem---------performing a general fix
---------The repair of data loss is required to be done under a single user, please exit the middle tier, client, other SQL modules
---All features exit, set the database to single user in Query Analyzer master
exec sp_dboption @databasename, n ' single ', n ' true '
-----in Query Analyzer master to repair the database
DBCC CHECKDB (@databasename, Repair_allow_data_loss)
DBCC CHECKDB (@databasename, Repair_rebuild)
------RESTORE DATABASE state
exec sp_dboption @databasename, n ' single ', n ' false '
Chapter 2nd repair of database log corruption
Follow the steps below to attempt to rebuild the database transaction log.
Note: The database may have no submitted data due to the loss of the transaction log.
Note: All must replace the real database name
2.1 Step 1:
Create a new database named the original database name.
2.2 Step 2:
Stop SQL Server
2.3 Step 3:
Replace the MDF file of the old database with the corresponding MDF file of the new database and delete the LDF file
2.4 Step 4:
Restart the SQL Server service, and then run the following command:
Copy Code code as follows:
Use Master
Go
sp_configure ' allow updates ', 1
Reconfigure with override
Go
BEGIN Tran
Update sysdatabases Set status = 32768 where Db_name '
--Verify One row is updated before committing
Commit Tran
2.5 Step 5:
Stop SQL and then restart the SQL Server service, and then run the following command:
DBCC Traceon (3604)
DBCC rebuild_log (' db_name ', ' C:\mssql7\data\dbxxx_3.LDF ')
Go
2.6 Step 6:
Stop SQL and then restart the SQL Server service, and then run:
Copy Code code as follows:
Use master
Update sysdatabases SET status = 8 where
Go
sp_configure ' allow updates ', 0
Reconfigure with override
Go
2.7 Step 7:
Run DBCC CHECKDB (DB_NAME) to check the integrity of the database.
3rd chapter The General processing of database query
1, execute the following SQL (open Modify system table switch):
EXEC sp_configure ' allow updates ', 1
Reconfigure with OVERRIDE
2. Modify the table in master database: sysdatabases
Change the Status field value to 4
3, and then execute the following sql:
EXEC sp_configure ' allow updates ', 0
Reconfigure with OVERRIDE.
