How to Restore SQL Server Database When DBCC CHECKDB Fail?

We have a client who uses Microsoft Dynamics NAV with a SQL 2005 database. Six days ago they started getting consistency errors in their database. We had restore from the last updated backup, and that seemed to fix the issue.

Yesterday they again started getting consistency errors in different area of the database. After everyone was off, I ran a DBCC CHECKDB command on the database with REPAIR_ALLOW_DATALOSS (the minimum level stated in the first CHECKDB I ran), and it failed to restore.

Here is a partial list of the results;

DBCC results for 'NAVSQL'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 281498259750912 (type Unknown), page ID (3:2179484) contains an incorrect page ID in its page header. The PageId in the page header = (8192:65536).
Repairing this error requires other errors to be corrected first.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 3906872676743905280 (type Unknown), page (34749:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 46139657 and -4.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 5641040008258584576 (type Unknown), page ID (4:675855) contains an incorrect page ID in its page header. The PageId in the page header = (256:10150144).
Repairing this error requires other errors to be corrected first.
CHECKDB found 0 allocation errors and 3 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 32308 rows in 186 pages for object "sys.sysrowsetcolumns".

...

CHECKDB found 0 allocation errors and 44 consistency errors in database 'NAVSQL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

At last we used stellar sql recovery software to fix the problem. Stellar SQL recovery software is an advanced tool that repair and recover corrupted sql server database. This software is compatible with window 7.

»»  Read More...

Truncating Transaction Log in SQLServer 2000

Few days back I had a situation when size of my database transcation log was growing too fast (at a point it reached 60 GB). so I had to find some way to truncate it to some minimal size. After lot of googling, I found how to truncate the transaction log.

Run these two commands in Query Analyser.

1. BACKUP LOG (DBName) WITH TRUNCATE_ONLY
2. DBCC SHRINKFILE((DBName)_log, (Desired transaction filesize) )

Remarks :

* DBCC SHRINKFILE applies to the files in the current database. If you try to run this command in a different DB then the DB you want to shrink, SQL Server will give following error

Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file ‘(DBName)_log’ in sysfiles.

* SQL Server uses (Desired transaction filesize) to calculate the target size for the entire log; therefore, target_size is the amount of free space in the log after the shrink operation. Target size for the entire log is then translated to target size for each log file. DBCC SHRINKFILE attempts to shrink each physical log file to its target size immediately. If no part of the logical log resides in the virtual logs beyond the log file’s target size, the file is successfully truncated and DBCC SHRINKFILE completes with no messages. However, if part of the logical log resides in the virtual logs beyond the target size, SQL Server frees as much space as possible and then issues an informational message. The message tells you what actions you need to perform to move the logical log out of the virtual logs at the end of the file. (Remarks are taken from MSDN)

References…..

1. Microsoft Knowledge Base (KB272318)
2. DBCC SHRINKFILE

Source: http://gargmanoj.wordpress.com/category/ms-sql-server-2000/

»»  Read More...