SUSDB Consistency Checks Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

SUSDB Consistency Checks Issue

Author

Topic

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-17 : 05:59:33

Hi All. Our Clients sbs2008 server uses backupexec. This daily reports "One or more SQL Database consistency checks have failed". I have traced this down to SUSDB. In sql management Studio I ran DBCC CHECKDB and reported "CHECKDB found 0 allocation errors and 11 consistency errors in database 'SUSDB' " . This has been happening for months so a restore from backup is unlikely. Therefore please could someone suggest a route whereby we could end up with a fully consistent 'SUSDB' and a backup that runs without errors.
P.s please note I am a baffoon where SQL is concerned so please respond in idiots language.....!!

Many Thanks for any help offered.

S Smith

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 06:30:54

Please run the following and post the full and complete results


DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS

Why has a corruption issue been ignored for months? You're likely to lose data because of this (data loss that could have been prevented if there was a clean backup available)

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 06:42:34

quote:
Originally posted by STUARTSMITHZ

This has been happening for months so a restore from backup is unlikely.

Is the database in FULL Recovery model? and if so would you be able to restore a FULL back and abolutely-every-single-LOG-backup since?

If so, and if you decide you need to, you can restore a FULL backup, test that it is "clean" (if not then restore an earlier one) and then restore every single LOG backup since, in order, and you should get a clean database with zero loss. Make a (final) log backup before you start ... better still would probably be to do this on either a different machine, or to a separate temporary database, until you are sure that you have a clean database, and then you can Backup/Restore over the original

NOTE: Database corruptions are 99% caused by hardware faults. The hardware fault will still be there, unless it has been fixed, so you need to address that too. Have a look in Event log to see if there is any indication of the problem.

P.s please note I am a baffoon where SQL is concerned so please respond in idiots language.....!!

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-17 : 07:22:43

As Requested ---

Msg 8928, Level 16, State 1, Line 1
Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data): Page (1:836494) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data), page (1:836494). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data). The off-row data node at page (1:836494), slot 0, text ID 121448955904 is referenced by page (1:836475), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data): Page (1:836496) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data), page (1:836496). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data). The off-row data node at page (1:836496), slot 0, text ID 121448955904 is referenced by page (1:836475), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data): Page (1:836500) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data), page (1:836500). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 63047689 and -4.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594050969600 (type LOB data). The off-row data node at page (1:836500), slot 0, text ID 121449283584 is referenced by page (1:834628), slot 9, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 1
Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594051035136 (type In-row data): Errors found in off-row data with ID 121448955904 owned by data record identified by RID = (1:835842:122)
Msg 8929, Level 16, State 1, Line 1
Object ID 194099732, index ID 1, partition ID 72057594046906368, alloc unit ID 72057594051035136 (type In-row data): Errors found in off-row data with ID 121449283584 owned by data record identified by RID = (1:835842:126)
CHECKDB found 0 allocation errors and 11 consistency errors in table 'tbXml' (object ID 194099732).
CHECKDB found 0 allocation errors and 11 consistency errors in database 'SUSDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SUSDB).

S Smith

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 07:49:51

You are going to lose data if this is repaired. 3 rows in the tbXML table.

No chance of backups?

--
Gail Shaw
SQL Server MVP

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-17 : 08:30:11

Hi GilaMonster,
By way of backups...are we talking server restore or just this DB.

quote:
Originally posted by GilaMonster

You are going to lose data if this is repaired. 3 rows in the tbXML table.

No chance of backups?

--
Gail Shaw
SQL Server MVP

S Smith

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 08:43:25

quote:
Originally posted by STUARTSMITHZ

Hi GilaMonster,
By way of backups...are we talking server restore or just this DB.

Just this DB

However, if you have corruption in this DB you might have in others, so suggest you repeat the DBCC CHECKDB command on all databases on that server when you have a moment.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 08:57:17

Database backups - full backups and maybe log backups since the last clean full backup.

--
Gail Shaw
SQL Server MVP

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-17 : 09:19:06

HI,

I have checked the backups. The SUSDB are stored in c:\WSUS\SUSDB\UpdateServicesDBFiles\ and perhaps because of the inconsistency it never backed up the susdb.mdf and susdb_log.ldf files.....at least when I look at the backup it says none.... any suggestions ??

S Smith

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 09:35:46

That may not matter. Assuming that you have an earlier FULL backup on tape then it may be that if you now make a LOG backup you will get the whole of the log since the last Full backup.

However, if your database is NOT in FULL Recovery Model its a bit academic as you won't be able to restore to any point AFTER the last full backup (when you find one ...)

Using FULL Recovery Model you can recover from a FULL Backup, and then as many subsequent LOG Backups as you have / want to use - including to a specific point-in-time.

You can query the MSDB database to find when Backups were made.

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 09:38:26

Backup History query:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=54300#273265

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 09:47:36

quote:
Originally posted by STUARTSMITHZ

I have checked the backups. The SUSDB are stored in c:\WSUS\SUSDB\UpdateServicesDBFiles\ and perhaps because of the inconsistency it never backed up the susdb.mdf and susdb_log.ldf files.....at least when I look at the backup it says none.... any suggestions ??

I wasn't talking about file backups (which are useless in most cases). I was talking about database backups. You know, BACKUP DATABASE ... TO DISK = ...
The database files would be getting skipped for file backups because they're in use at the time, in use by SQL Server. Nothing to do with the corruption (which is a logical corruption)

I'm going to assume, from what you said, you've never taken any backups of this database. If that's the case, be grateful that the damage is just a few rows, not the entire DB.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-17 : 10:38:44

What's a few rows?

Is this a wall street trading app?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-17 : 12:42:51

Hi GilaMonster & Kristen, I have no specific SQL backups i only have Server Backup via Backupexec.
Given what i have read before, presume these are of no use...as the backup doesn't touch the SQL files.

What can we do in this instance?

S Smith

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-17 : 12:55:49

Take the DB into single user mode
Run CheckDB with the REPAIR_ALLOW_DATA_LOSS option. You will lose at least 3 rows from the tbXML table

Set up proper backups
Make sure you're running integrity checks regularly and acting on any errors immediatly
Do some root-cause analysis on your hardware

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-01-17 : 13:26:38

I suggest you backup to Disk Files, and then have Bacupexec backup those physical BAK files, and not allow it to try to backup the database nor the databases's [physical files.

Make a trial restore periodically (to a new, temporary, database) - preferably on a different machine - to prove that you can, and that the backup files are not damaged.

Run DBCC CHECKDB on the restored "temporary" database to check that it is not corrupted

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-26 : 10:58:40

HI All....
Just go back to this so sorry for the delay. I have made the SUSDB single user mode. I have ten tried to run the REPAIR_ALLOW_DATA_LOSS but it says that the database aleady has 1 open connection. I have searched to try to close the open connection without success so far ..... please can someone suggest the correct steps to do this and the correct syntax..

S Smith

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-26 : 11:19:44

Make sure you are using the DB, run ALTER DATABASE ... SET SINGLE_USER WITH ROLLBACK IMMEDIATE, then run CheckDB in that connection.

--
Gail Shaw
SQL Server MVP

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-27 : 04:59:20

Hi Gail ..
So at the Sql Manager window i should run a new query that says DB, run ALTER DATABASE

then run

SET SINGLE_USER WITH ROLLBACK IMMEDIATE
is that correct ?

S Smith

STUARTSMITHZ
Starting Member

12 Posts

Posted - 2012-01-27 : 05:01:36

I ran that and it says incorect syntax ..... HELP!!!

S Smith

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-27 : 05:07:51

Um, no. Go and look up the syntax of ALTER DATABASE. The ... indicates something left out.

--
Gail Shaw
SQL Server MVP

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources