Solutions to SQL Server database doubts

Solutions to SQL Server database doubts

Keywords: SQL Server doubt

 

I have already encountered this situation twice in my work. I think we should record it for the benefit of others.

This problem is usually caused by insufficient hard disk space or hard disk read/write errors.

Symptom:

There is a question behind the database. The following error occurs when viewing the system transaction diary:
Error 1 ---------------------------------------------
Error: 823, severity: 24, status: 2
I/O error 23 (data error (cyclic redundancy check ).) Detected During read at offset 0x00000000200000 in file 'C:/program files/Microsoft SQL Server/MSSQL/data/jiapei_data.mdf '.
Error 2 ---------------------------------------------
Error: 3313, severity: 21, status: 2
An error occurred while restoring operations recorded in the database 'jiapei 'logs. The error location is in the log record ID (274: 377: 2 ).
Error 3 ---------------------------------------------
Error: 3313, severity: 21, status: 2
Error while redoing logged operation in database 'jiapei '. error at log record ID (274: 377: 2 ).
The database can be separated, but cannot be attached after the separation. The error "823" is returned when the database is attached.

Solution:

1. Create a database with the same name (the file name and file group are the same as the original one), stop the Database Service, replace the new database file with the original file, start the database, and set the database to suspect.

2. Change the database to the emergency mode:
Sp_configure 'allow', 1
Reconfigure with override
Update sysdatabases set status = 32768 where name = 'database name'
3. Rename the LDF file and execute
DBCC rebuild_log ('database name', 'e:/fdzz/database/fdzz1204_log.ldf ')
4. Restore the database emergency mode
Update sysdatabases set status = 0 where name = 'database name'
Run
Restore database name with recovery
Sp_configure 'allow', 0
Reconfigure with override
5. Use DBCC checkdb ('database name') to check for any errors.
6. If the problem persists, set the database to the emergency mode. You can see the data and export the data to a new database.

 

Other useful operations:

/* -- Reset the suspicious status
1. system method:
If SQL Server cannot recover the database because the disk drive has no available space,
Microsoft SQL Server 2000 Returns Error 1105
And set the Status column in sysdatabases as questionable. Follow these steps to solve the problem:
Execute sp_resetstatus.
Syntax:
Sp_resetstatus 'database name'
Use alter database to add a data file or log file to the database.
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.
Release the disk space and run the Restoration Operation again.
Sp_resetstatus indicates whether to shut down the database, but other options of the database are kept intact.
--*/
-- 2. manually reset the suspicious status
Use master
Go
Sp_configure 'Allow updates', 1 reconfigure with override
Go
Declare @ dbname varchar (30)
Set @ dbname = 'name of the database you want to process'
If @ trancount> 0
Print 'transaction processing in progress, operation not allowed'
Else if suser_id ()! = 1
Print 'you are not a System Administrator (SA) and cannot perform this operation'
Else if not exists (select 1 from Master .. sysdatabases where name = @ dbname)
Print 'the database you want to operate does not exist'
Else if not exists (select 1 from Master .. sysdatabases where name = @ dbname and status & 256 = 256)
Print 'your database is unquestionable'
Else
Begin
Begin tran
Update master .. sysdatabases set status = status ^ 256 where name = @ dbname
If @ error! = 0 or @ rowcount! = 1
Rollback tran
Else
Begin
Commit tran
Print 'Operation successful. Please restart SQL'
End
End
Go
Sp_configure 'Allow updates', 1 reconfigure with override
Go

--------------------------------------------------------------------------------

But now I have separated the database and cannot attach it, so the operation sp_resetstatus cannot be used.

--------------------------------------------------------------------------------

Right-click the database in the suspicious state --> all tasks --> offline
Right-click a database in the Offline state --> all tasks --> online
Reset questionable status
If SQL Server cannot recover the database because the disk drive has no available space
Microsoft? SQL Server? 2000 will return error 1105 and set the status in sysdatabases
The column is set to suspect. Follow these steps to solve the problem:
1. Execute sp_resetstatus.
2. Use alter database to add a data file or log file to the database.
3. 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
.
4. Release the disk space and run the Restoration Operation again.
Sp_resetstatus indicates whether to shut down the database, but other options of the database are kept intact.
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 creating this process. To start
Use update to 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.
Syntax:
Sp_resetstatus database_name
The following example will disable the question mark of the production database.
Sp_resetstatus Production
The following is the result set:
Database 'production 'status reset!
Warning: You must reboot SQL Server prior to accessing this database!
Sp_resetstatus Stored Procedure Code
The code for the sp_resetstatus stored procedure is as follows:
If exists (select * From sysobjects where name = 'SP _ resetstatus ')
Drop procedure sp_resetstatus
Go
Create proc sp_resetstatus @ dbname varchar (30)
Declare @ MSG varchar (80)
If @ trancount> 0
Begin
Print 'can't run sp_resetstatus from within a transaction .'
Return (1)
End
If suser_id ()! = 1
Begin
Select @ MSG = 'you must be the system administrator (SA )'
Select @ MSG = @ MSG + 'to execute this procedure .'
Return (1)
End
If (select count (*) from Master .. sysdatabases
Where name = @ dbname )! = 1
Begin
Select @ MSG = 'database' + @ dbname + 'does not exist! '
Print @ msg
Return (1)
End
If (select count (*) from Master .. sysdatabases
Where name = @ dbname and status & 256 = 256 )! = 1
Begin
Print 'SP _ resetstatus can only be run on suspect databases .'
Return (1)
End
Begin tran
Update master .. sysdatabases set status = status ^ 256
Where name = @ dbname
If @ error! = 0 or @ rowcount! = 1
Rollback tran
Else
Begin
Commit tran
Select @ MSG = 'database' + @ dbname + 'status reset! '
Print @ msg
Print''
Print 'Warning: You must reboot SQL Server prior'
Print 'accessing this database! '
Print''
End
Go