Database questionable Problem Solving

Document 1

 

1. Stop the database server and copy and back up the MDF and LDF files of the database.
2. Start the database server and delete the suspicious Database
3. Only use the backup database MDF file to append the database. sp_attach_db or sp_attach_single_file_db can append the database. The following message is displayed:
Device activation error. The physical file name 'C:/program files/Microsoft SQL Server/MSSQL/data/mydb_log.ldf' may be incorrect.
You have created a new log file named 'C:/program files/Microsoft SQL Server/MSSQL/data/mydb_log.ldf.
This indicates that the database has been attached successfully and the problem has been solved. If the problem is solved successfully, congratulations. Anyway, I failed to add it, and the following error message is displayed:
The new database 'mydb' cannot be opened '. Create database will be terminated.
Device activation error. The physical file name 'E:/www/mydb_log.ldf' may be incorrect.
At this time, I used the following methods to solve the problem (refer to the online method ).
A. Create a database of the same name for recovery in SQL Server Enterprise Manager (note: the same name as the problematic database, in this example mydb ).
B. Stop the database server.
C. delete the log file mydb_log.ldf of the database you just generated (in this example, the name of the database is shown in the column and your own database name is used ), overwrite the newly generated database data file mydb_data.mdf with the MDF file of the database you just backed up.
D. Start the database server. The status of mydb in the database is "questionable ". At this time, you cannot perform any operations on this database.
E. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select the database server, press the right -- key, and select "properties ", on the "Server Settings" Page, select "allow direct modification to System directories. You can also use the following statement.
Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go f. Set mydb to Emergency Repair Mode
Set the following command in the query Manager:
Update sysdatabases set status =-32768 where dbid = db_id ('mydb ') at this time, you can see in SQL Server Enterprise Manager that the database is in "Read-Only/suspicious/offline/emergency mode", you can see the tables in the database, but only the system tables
G. Execute the true recovery operation below to recreate the database log file.
DBCC rebuild_log ('mydb', 'c:/program files/Microsoft SQL Server/MSSQL/data/mydb_log.ldf') warning: the database 'mydb' log has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
In SQL Server Enterprise Manager, you can see that the database status is "only for DBO ". Now you can access the user tables in the database.
H. Verify Database Consistency (omitted)
DBCC checkdb ('mydb') is generally executed as follows:
Checkdb finds 0 allocation errors and 0 consistency errors (in database 'mydb ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
I. Set the database to normal
Sp_dboption 'mydb', 'dbo use only', and 'false' J. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL Server Enterprise Manager or use the following statement.
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go

This database undoubtedly solves the problem.

 

 

Document 2

 

Ms SQL Server database recovery steps after doubt
-- SQL Server database recovery steps
-- 1. Recovery steps:
-- A. Back up the smlog_log.ldf file to another 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 database status:
Use master
Go
Update sysdatabases set status = 32768 where name = 'database name' -- modify the status and set it to an emergency
Go
Shutdown with Nowait -- stop the Database Server
Go
-- D. Exit the SQL statement and restart the SQL statement in command line mode using the following code:
Sqlservr-C-t3608-t4022 -- enable SQL Server in Safe Mode
-- E. Execute the following statement in the query analyzer to view the database status that has just been modified:
Select name, status from sysdatabases where name = 'database name done'
-- F. Run the following code to create a log file:
DBCC traceon (3604) -- tracking
DBCC rebuild_log ('database name', 'Log file full path') -- the file name must have a full path and extension.
-- DBCC rebuild_log ('prs _ MSC ', 'd:/mscsql/MSSQL/data/prs_msc_log.ldf
-- G. Set the database back to normal:
Update sysdatabases set status = 0 where name = 'database name'
-- H. Run the following statement to check the database after restarting the database:
DBCC checkdb -- if an error occurs after execution, use the following statement to fix it:
-- I. To fix the database, you must change the database to the single-user mode:
Exce sp_dboption 'database name', 'single user', 'true' --- ('false' restores multiple users)
-- J. Execute the following statement to restore the database:
DBCC checkdb ('database name', repair_allow_data_loss)
Repair_allow_data_loss: a relatively advanced repair method.
Repair_fast: A simple and quick solution.
/*
The processing status is "suspicious" data records
Back up the data file and follow these steps:
1. Create a database with the same name (the data file must be consistent with the original one)
2. Stop SQL server again (do not detach the database)
3. overwrite the new database with the data file of the original database.
4. Restart SQL Server.
5. When you open the Enterprise Manager, there will be a doubt. Ignore it and execute the following statement (note that you must modify the database name)
6. after completion, You can generally access the data in the database. In this case, the database itself is still problematic. The solution is to create a new database using the database script, and export the data.

*/
Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go
Update sysdatabases set status = 32768 where name = 'suspicious database name'
Go
Sp_dboption 'questionable database name', 'single user', 'true'
Go
DBCC checkdb ('questionable database name ')
Go
Update sysdatabases set status = 28 where name = 'questionable database name'
Go
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go
Sp_dboption 'questionable database name', 'single user', 'false'
Go

 

/*
Only the MDF file restoration technology is available.
For various reasons, if we only backed up the MDF file at that time, it would be very troublesome to restore it.
If your MDF file is generated by the current database, you may be lucky to use sp_attach_db or sp_attach_single_file_db to restore the database, but the following prompt will appear:
Device activation error. The physical file name 'C:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf' may be incorrect.
You have created a new log file named 'C:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf.
However, if your database files are copied from other computers, the above method may not work. You may get an error message similar to the following.
Server: Message 1813, level 16, status 2, Row 1
Failed to open the new database 'test '. Create database will be terminated.
Device activation error. The physical file name 'd:/test_log.ldf' may be incorrect.
What should we do? Don't worry. The following is an example of the restoration method.
*/
-- A. We use the default method to create a database for recovery (such as test ). It can be created in SQL Server Enterprise Manager.
-- B. Stop the database server.
-- C. Delete the log file test_log.ldf of the database just generated, and overwrite the generated database data file test_data.mdf with the MDF file to be restored.
-- D. Start the database server. In this case, the database test status is "Suspect ". At this time, you cannot perform any operations on this database.
-- E. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select the database server, press the right -- key, and select "properties ", on the "Server Settings" Page, select "allow direct modification to System directories. You can also use the following statement.
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 ')
-- In this case, you can see that the database is in "Read-Only/suspicious/offline/emergency mode" in SQL Server Enterprise Manager. You can see the tables in the database, but there are only system tables.
-- G. Execute the true recovery operation below to recreate the Database Log File
DBCC rebuild_log ('test', 'c:/program files/Microsoft SQL Server/MSSQL/data/test_log.ldf ')
/*
If the following prompt is displayed during execution:
Server: Message 5030, level 16, status 1, Row 1
The database cannot be locked for this operation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
This indicates that other programs are using this database. If you opened the system table of the test database using SQL Server Enterprise Manager in step f, you can exit SQL Server Enterprise Manager.
The prompt for correct execution should be similar:
Warning: the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
In SQL Server Enterprise Manager, you can see that the database status is "only for DBO ". Now you can access the user tables in the database.
*/
-- H. Verify Database Consistency (omitted)
DBCC checkdb ('test ')
/* The General execution result is as follows:
Checkdb finds 0 allocation errors and 0 consistency errors (in the database 'test ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. */
-- I. Set the database to normal
Sp_dboption 'test', 'dbo use only', 'false'
-- If no error occurs, Congratulations. Now we can use the recovered database normally.
-- J. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL Server Enterprise Manager or use the following statement.
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go

-- How to restore the database to normal if a log file is faulty (lost or the file format is invalid)
-- If sp_attach_single_file 'test', 'c:/program files/Microsoft SQL Server/MSSQL/data/test_log.mdf 'fails, perform the following steps:
-- 1. Detach the suspicious database and remove or rename the MDF file!
Sp_detach_db 'test'
-- 2. Create a database named test in the original directory again.
-- 3. Stop SQL Service, copy the MDF file back to overwrite (or rename), and delete (or rename) the original log file)
-- 4. Start SQL Service (otherwise the following statements cannot be 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-create a 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 (the following three statements are acceptable)
-- 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. You can use it if you are OK.
DBCC checkdb (test)

-- 9. Set the data access permission to the multi-user mode.
Sp_dboption 'test', 'single user', 'false'
-- Or alter database Test Set multi_user

-- 10. Disable advanced options
Sp_configure 'Allow updates', 0
Reconfigure with override

-- End

 

 

 

SQL Server Installation suspended

When installing SQL Server, the following error occurs: "A previously installed program has created a suspended file on the installation computer. You must restart the computer before running the installer. Unable to proceed. After referring to the relevant information, follow these steps:

1) Add/delete programs to completely delete SQL Server.

2) Delete the SQL Server Directory that has not been deleted.

3) Open the Registry Editor, find the pendingfilerenameoperations project in HKEY_LOCAL_MACHINE/system/CurrentControlSet/control/session manager, and delete it. In this way, you can clear the temporary installation project.

4) Delete the SQL Server-related keys in the registry.

Note:
If you do not want to restart, the installation will be successful. Generally, you can solve the problem in step 3 ......

 

 

Document 3:

 

-- SQL Server database recovery steps
-- 1. Recovery steps:
-- A. Back up the smlog_log.ldf file to another 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 database status:
Use master
Go
Update sysdatabases set status = 32768 where name = 'database name' -- modify the status and set it to an emergency
Go
Shutdown with Nowait -- stop the Database Server
Go
-- D. Exit the SQL statement and restart the SQL statement in command line mode using the following code:
Sqlservr-C-t3608-t4022 -- enable SQL Server in Safe Mode
-- E. Execute the following statement in the query analyzer to view the database status that has just been modified:
Select name, status from sysdatabases where name = 'database name done'
-- F. Run the following code to create a log file:
DBCC traceon (3604) -- tracking
DBCC rebuild_log ('database name', 'Log file full path') -- the file name must have a full path and extension.
-- DBCC rebuild_log ('prs _ MSC ', 'dmscsqlmssqldataprs _ msc_log.ldf
-- G. Set the database back to normal:
Update sysdatabases set status = 0 where name = 'database name'
-- H. Run the following statement to check the database after restarting the database:
DBCC checkdb -- if an error occurs after execution, use the following statement to fix it:
-- I. To fix the database, you must change the database to the single-user mode:
Exce sp_dboption 'database name', 'single user', 'true' --- ('false' restores multiple users)
-- J. Execute the following statement to restore the database:
DBCC checkdb ('database name', repair_allow_data_loss)
Repair_allow_data_loss: a relatively advanced repair method.
Repair_fast: A simple and quick solution.

The handling status is the suspicious data volume.
Back up the data file and process it as follows:
1. Create a database with the same name (the data file must be consistent with the original one)
2. Stop SQL server again (do not detach the database)
3. overwrite the new database with the data file of the original database.
4. Restart SQL Server.
5. When you open the Enterprise Manager, there will be a doubt. Ignore it and execute the following statement (note that you must modify the database name)
6. after completion, You can generally access the data in the database. In this case, the database itself is still problematic. The solution is to create a new database using the database script, and export the data.

Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go
Update sysdatabases set status = 32768 where name = 'suspicious database name'
Go
Sp_dboption 'questionable database name', 'single user', 'true'
Go
DBCC checkdb ('questionable database name ')
Go
Update sysdatabases set status = 28 where name = 'questionable database name'
Go
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go
Sp_dboption 'questionable database name', 'single user', 'false'
Go

 

Only the MDF file restoration technology is available.
For various reasons, if we only backed up the MDF file at that time, it would be very troublesome to restore it.
If your MDF file is generated by the current database, you may be lucky to use sp_attach_db or sp_attach_single_file_db to restore the database, but the following prompt will appear:
Device activation error. The physical file name 'cprogram filesmicrosoft SQL servermssqldatatest_log.ldf' may be incorrect.
You have created a new log file named 'cprogram filesmicrosoft SQL servermssqldatatest_log.ldf.
However, if your database files are copied from other computers, the above method may not work. You may get an error message similar to the following.
Server Message 1813, level 16, status 2, Row 1
Failed to open the new database 'test '. Create database will be terminated.
Device activation error. The physical file name 'dtest _ log. ldf' may be incorrect.
What should we do? Don't worry. The following is an example of the restoration method.

-- A. We use the default method to create a database for recovery (such as test ). It can be created in SQL Server Enterprise Manager.
-- B. Stop the database server.
-- C. Delete the log file test_log.ldf of the database just generated, and overwrite the generated database data file test_data.mdf with the MDF file to be restored.
-- D. Start the database server. In this case, the database test status is "Suspect ". At this time, you cannot perform any operations on this database.
-- E. Set the database to allow direct operation of system tables. In SQL Server Enterprise Manager, select the database server, press the right -- key, and select "properties ", on the "Server Settings" Page, select "allow direct modification to System directories. You can also use the following statement.
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 ')
-- In this case, you can see in SQL Server Enterprise Manager that the database is in "read-only questionable offline emergency mode". You can see the tables in the database, but there are only system tables.
-- G. Execute the true recovery operation below to recreate the Database Log File
DBCC rebuild_log ('test', 'cprogram filesmicrosoft SQL servermssqldatatest_log.ldf ')

If the following prompt is displayed during execution:
Server Message 5030, level 16, status 1, Row 1
The database cannot be locked for this operation.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
This indicates that other programs are using this database. If you opened the system table of the test database using SQL Server Enterprise Manager in step f, you can exit SQL Server Enterprise Manager.
The prompt for correct execution should be similar:
Warning that the log of the database 'test' has been rebuilt. Transaction consistency is lost. DBCC checkdb should be run to verify physical consistency. The database must be reset and redundant log files may need to be deleted.
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
In SQL Server Enterprise Manager, you can see that the database status is "only for DBO ". Now you can access the user tables in the database.

-- H. Verify Database Consistency (omitted)
DBCC checkdb ('test ')
The general execution result is as follows:
Checkdb finds 0 allocation errors and 0 consistency errors (in the database 'test ).
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
-- I. Set the database to normal
Sp_dboption 'test', 'dbo use only', 'false'
-- If no error occurs, Congratulations. Now we can use the recovered database normally.
-- J. In the last step, we need to restore the "allow direct modification to the system directory" set in Step E. It is dangerous to directly operate system tables. Of course, we can recover the data in SQL Server Enterprise Manager or use the following statement.
Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go

-- How to restore the database to normal if a log file is faulty (lost or the file format is invalid)
-- If sp_attach_single_file 'test' and 'cprogram filesmicrosoft SQL servermssqldatatest_log.mdf 'fail, follow these steps:
-- 1. Detach the suspicious database and remove or rename the MDF file!
Sp_detach_db 'test'
-- 2. Create a database named test in the original directory again.
-- 3. Stop SQL Service, copy the MDF file back to overwrite (or rename), and delete (or rename) the original log file)
-- 4. Start SQL Service (otherwise the following statements cannot be 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-create a 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 (the following three statements are acceptable)
-- 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. You can use it if you are OK.
DBCC checkdb (test)

-- 9. Set the data access permission to the multi-user mode.
Sp_dboption 'test', 'single user', 'false'
-- Or alter database Test Set multi_user

-- 10. Disable advanced options
Sp_configure 'Allow updates', 0
Reconfigure with override

-- End

 

 

Document 4:

 

How to fix SQL Server database "Doubt" (2) favorites
If SQL Server cannot recover the database because of insufficient disk space, SQL Server 2000 Returns Error 1105 and sets the Status column in sysdatabases as questionable.

You can see that there should be a 1105 error message in the error log of sqlserver and the application log of OS:

SQL Server transaction logs may be filled up, which will block subsequent database operations, including update, delete, insert, and checkpoint.
The following error occurs when the transaction log is filled:
Can't allocate space for object syslogs in database dbname because
The logsegment is full. If you ran out of space in syslogs, dump
The transaction log. Otherwise use alter database or
Sp_extendsegment to increase the size of the segment.

This phenomenon may occur in any database, including master and tempdb. Unpredictable factors may consume log space. For example:
A large transaction, especially a batch data update, insertion, or deletion.
An uncommitted transaction.
The bandwidth required by the checkpoint handler is too large.
Threshold Exceeded during Truncation
The result of the interaction between the above conditions.
The transaction marked for publishing is not read by the log reader.

The repair steps and log shrinking steps are as follows:

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

Sqlserver-F-m

Note: The-M switch starts SQL Server in single-user mode. In single-user mode, only one connection can be established successfully. Note whether any other client or service may use that connection before you establish a connection through the SQL Server Query analyzer.

2. Reset the suspicious database status.

Sp_resetstatus 'database _ name'

The following is the result set:

Database 'database _ name' status reset!
Warning: You must reboot SQL Server prior to accessing this database!

3. Use alter database to add a data file or log file to the database:

Use master
Go
Create Database db_name on
(
Name = dbname_dat1,
Filename = 'd:/MSSQL/data/dbname_dat1.ndf ',
Size = 1000 MB,
Filegrowth = 50 MB
)
Go -- change the database to add a new 2 GB Data File
Alter database db_name
Add File
(
Name = dbname_dat2,
Filename = 'f:/MSSQL/data/dbname_dat2.ndf ',
Size = 2000 MB,
Filegrowth = 50 MB
)
Go
-- Change the database to add a 1 GB Log File
Alter database db_name
Add Log File
(Name = db_name_log2,
Filename = 'f:/MSSQL/data/db_name_log2.ldf ',
Size = 1000 MB,
Filegrowth = 20 mb ),
Go
4. Stop and restart SQL Server:
With the additional space provided by new data files or log files, SQL server should be able to restore the database.

5. Release the disk space and run the Restoration Operation again. follow the steps below to contract the log.
Sp_resetstatus indicates whether to shut down the database, but other options of the database are kept intact.

To fundamentally solve this problem, you can configure sqlserver 2000 as follows:
A. If you do not need to restore to the specified time point, you can configure the database recovery mode as simple.
Update, delete, select will not record the log, and the log will not increase greatly:

Use master

Go
Alter database db_name set recovery simple
B. If your recovery mode is full, you must configure log field contraction:

Use master

Go
Sp_dboption 'databasename', 'trunc. Log On chkpt. ', true
Sp_dboption 'databasename', 'autowrite', true
C. Shrink logs through 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 log capacity: DBCC sqlperf (logspace). At this time, the log does not contract!

D. Restart Ms sqlserver service every day after the database is backed up.
Use database_name
Go
DBCC shrinkfile (2, truncateonly)

** Check the log capacity: DBCC sqlperf (logspace). At this time, the log has been reduced!

E. Manually fast log shrinking:
/* Run below script, you will shrink you Database Log Files
Immediately, in my 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 that you can use it only under the guidance of your primary support provider or with troubleshooting suggestions.
Sp_resetstatus. Otherwise, the database may be damaged.

Because the system table is modified in this process, the system administrator must enable system table update before running sp_resetstatus. Yes
Start the update and use the following process:

Use master
Go
Sp_configure 'Allow updates', 1
Go
Reconfigure with override
Go
Immediately disable system table update after the process is created:

Sp_configure 'Allow updates', 0
Go
Reconfigure with override
Go
Only the system administrator can execute sp_resetstatus. Disable SQL Server immediately after the process is executed.

 

See:

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 the csdn blog, reproduced please indicate the source: http://blog.csdn.net/leimin/archive/2004/02/26/12900.aspx