Does this indicate a problem with a Database? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Administration (2000)

Does this indicate a problem with a Database?

Author

Topic

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-16 : 08:38:24

Hi there,

We are running Backup Exec 8.6 (with SQL agent) and I have it carry out a consistency check before and after backups of our DBs. However, yesterday I received the following error in the event log:
---------------------------
Source: Backup Exec
Category: None
Event ID: 57859
Description: An error occured while executing the following query: "DBCC CHECKDB ([BIDGroup])".
On server: "BID-WIN2KSERVER".
SQL error number: "09E8"
SQL error message: "DBCC results for 'BIDGroup' ".
---------------------------
Does this indicate a corruption in the named DB, and if so, is there a general set of tools etc that I can use to 'check/repair' the DB offline (i.e. as per eseutil, isinteg etc for MS Exchange)? I have checked the Veritas support site and cannot find any reference to this particular error.

Lastly, I am a complete beginner with regards to SQL admin

Any help would be very much appreciated. Many thanks in advance.

Edit: I should have added, our databases are all running fine and ne no-one has reported any issues at all.

Edit2: I've just realised...should this have been in the Corruption forum? If so, my apologies, can a mod move it there please?

-ras

mr_mist
Grunnio

1870 Posts

Posted - 2006-06-16 : 08:45:40

Log on to the server yourself with query analyzer and run

DBCC CHECKDB ([BIDGroup])

And see what results you get.

-------
Moo. :)

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-16 : 09:18:04

quote:
Originally posted by mr_mist

Log on to the server yourself with query analyzer and run

DBCC CHECKDB ([BIDGroup])

And see what results you get.

-------
Moo. :)

Hiya Moo, how do I use query analyzer? Also, can this be run while the database is online? Sorry for the noob questions

many thanks mate, appreciate your help

Edit: Found the Query Analyzer tool in the program list. However, is it safe to run this while users are logged onto (using) the databases?

cheers

ras

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-18 : 08:22:47

Here are the results of the DBCC check against the DB in question. It reports no errors? Very strange >>>

DBCC results for 'BIDGroup'.
DBCC results for 'sysobjects'.
There are 493 rows in 10 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 1146 rows in 81 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 2121 rows in 50 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 273 rows in 25 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 174 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 15 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 770 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 30 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 0 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'Time Sheet'.
There are 7806 rows in 134 pages for object 'Time Sheet'.
DBCC results for 'Sale Source'.
There are 15 rows in 1 pages for object 'Sale Source'.
DBCC results for 'Employee'.
There are 265 rows in 11 pages for object 'Employee'.
DBCC results for 'Labour Rate'.
There are 41 rows in 1 pages for object 'Labour Rate'.
DBCC results for 'Job Costing'.
There are 47358 rows in 877 pages for object 'Job Costing'.
DBCC results for 'Stock Part'.
There are 2 rows in 1 pages for object 'Stock Part'.
DBCC results for 'Cost Price'.
There are 3314 rows in 90 pages for object 'Cost Price'.
DBCC results for 'Item Part Name'.
There are 193 rows in 1 pages for object 'Item Part Name'.
DBCC results for 'Credit Card Payment'.
There are 2 rows in 1 pages for object 'Credit Card Payment'.
DBCC results for 'Part Name'.
There are 267 rows in 2 pages for object 'Part Name'.
DBCC results for 'Product Parts'.
There are 2 rows in 1 pages for object 'Product Parts'.
DBCC results for 'Staff'.
There are 530 rows in 3 pages for object 'Staff'.
DBCC results for 'Default Unit Rate'.
There are 345 rows in 3 pages for object 'Default Unit Rate'.
DBCC results for 'Service Type'.
There are 3 rows in 1 pages for object 'Service Type'.
DBCC results for 'dtproperties'.
There are 7 rows in 1 pages for object 'dtproperties'.
DBCC results for 'Purchase Invoice Line'.
There are 4447 rows in 13 pages for object 'Purchase Invoice Line'.
DBCC results for 'Service Contract Line'.
There are 308 rows in 9 pages for object 'Service Contract Line'.
DBCC results for 'Purchase Status'.
There are 7 rows in 1 pages for object 'Purchase Status'.
DBCC results for 'Activity'.
There are 464036 rows in 8589 pages for object 'Activity'.
DBCC results for 'Billing Address'.
There are 3882 rows in 272 pages for object 'Billing Address'.
DBCC results for 'Part Finish'.
There are 33 rows in 1 pages for object 'Part Finish'.
DBCC results for 'Part Colour'.
There are 200 rows in 2 pages for object 'Part Colour'.
DBCC results for 'Service Contract'.
There are 264 rows in 21 pages for object 'Service Contract'.
DBCC results for 'Industry Sector'.
There are 17 rows in 1 pages for object 'Industry Sector'.
DBCC results for 'Payment Terms'.
There are 23 rows in 1 pages for object 'Payment Terms'.
DBCC results for 'Department'.
There are 10 rows in 1 pages for object 'Department'.
DBCC results for 'Sales Quote Status'.
There are 11 rows in 1 pages for object 'Sales Quote Status'.
DBCC results for 'Sales Quote No'.
There are 1 rows in 1 pages for object 'Sales Quote No'.
DBCC results for 'Part Material'.
There are 57 rows in 1 pages for object 'Part Material'.
DBCC results for 'Pay Adjustments'.
There are 2445 rows in 20 pages for object 'Pay Adjustments'.
DBCC results for 'Purchase Posting Status'.
There are 10 rows in 1 pages for object 'Purchase Posting Status'.
DBCC results for 'Budgets'.
There are 1038 rows in 20 pages for object 'Budgets'.
DBCC results for 'Customer Appointments'.
There are 2011 rows in 62 pages for object 'Customer Appointments'.
DBCC results for 'Adjust Type'.
There are 23 rows in 1 pages for object 'Adjust Type'.
DBCC results for 'Stock Item Location'.
There are 2 rows in 1 pages for object 'Stock Item Location'.
DBCC results for 'Accounts Payable'.
There are 2 rows in 1 pages for object 'Accounts Payable'.
DBCC results for 'Customer Calls'.
There are 85 rows in 3 pages for object 'Customer Calls'.
DBCC results for 'Product Name'.
There are 21 rows in 1 pages for object 'Product Name'.
DBCC results for 'Customer Re-Calls'.
There are 349 rows in 11 pages for object 'Customer Re-Calls'.
DBCC results for 'Site Address'.
There are 5867 rows in 225 pages for object 'Site Address'.
DBCC results for 'Supplier Address'.
There are 943 rows in 54 pages for object 'Supplier Address'.
DBCC results for 'Sale Type'.
There are 40 rows in 1 pages for object 'Sale Type'.
DBCC results for 'Hourly Rate'.
There are 0 rows in 0 pages for object 'Hourly Rate'.
DBCC results for 'Location Address'.
There are 138 rows in 4 pages for object 'Location Address'.
DBCC results for 'Labour Unit Type'.
There are 19 rows in 1 pages for object 'Labour Unit Type'.
DBCC results for 'Company Account'.
There are 3621 rows in 12 pages for object 'Company Account'.
DBCC results for 'Job Time'.
There are 0 rows in 0 pages for object 'Job Time'.
DBCC results for 'Stock Issued Type'.
There are 7 rows in 1 pages for object 'Stock Issued Type'.
DBCC results for 'Customer Address'.
There are 7837 rows in 603 pages for object 'Customer Address'.
DBCC results for 'Stock Issued'.
There are 2 rows in 1 pages for object 'Stock Issued'.
DBCC results for 'Sales Region'.
There are 2 rows in 1 pages for object 'Sales Region'.
DBCC results for 'Product Assembly'.
There are 125 rows in 2 pages for object 'Product Assembly'.
DBCC results for 'Labour'.
There are 72165 rows in 2301 pages for object 'Labour'.
DBCC results for 'Sales Zone'.
There are 124 rows in 2 pages for object 'Sales Zone'.
DBCC results for 'Service Sheet'.
There are 75 rows in 4 pages for object 'Service Sheet'.
DBCC results for 'Purchase Order'.
There are 5547 rows in 621 pages for object 'Purchase Order'.
DBCC results for 'Cost'.
There are 22703 rows in 269 pages for object 'Cost'.
DBCC results for 'Product Index'.
There are 7097 rows in 462 pages for object 'Product Index'.
DBCC results for 'Customer Contact'.
There are 8809 rows in 183 pages for object 'Customer Contact'.
DBCC results for 'Sales Quote'.
There are 378 rows in 18 pages for object 'Sales Quote'.
DBCC results for 'Account Contact'.
There are 3351 rows in 56 pages for object 'Account Contact'.
DBCC results for 'Site Contact'.
There are 6636 rows in 105 pages for object 'Site Contact'.
DBCC results for 'Supplier Contact'.
There are 961 rows in 16 pages for object 'Supplier Contact'.
DBCC results for 'Profit Centre'.
There are 23 rows in 1 pages for object 'Profit Centre'.
DBCC results for 'Purchase Invoice'.
There are 4406 rows in 143 pages for object 'Purchase Invoice'.
DBCC results for 'Work Type'.
There are 7 rows in 1 pages for object 'Work Type'.
DBCC results for 'Position'.
There are 4 rows in 1 pages for object 'Position'.
DBCC results for 'Time Period'.
There are 7 rows in 1 pages for object 'Time Period'.
DBCC results for 'Employee Type'.
There are 56 rows in 1 pages for object 'Employee Type'.
DBCC results for 'Report Access'.
There are 166 rows in 1 pages for object 'Report Access'.
DBCC results for 'Labour Unit'.
There are 83385 rows in 1428 pages for object 'Labour Unit'.
DBCC results for 'Company Supplier'.
There are 946 rows in 4 pages for object 'Company Supplier'.
DBCC results for 'Division Unit Rate'.
There are 1520 rows in 16 pages for object 'Division Unit Rate'.
DBCC results for 'Main Supplier'.
There are 8 rows in 1 pages for object 'Main Supplier'.
DBCC results for 'Invoice_No'.
There are 1 rows in 1 pages for object 'Invoice_No'.
DBCC results for 'Quality Approval'.
There are 39 rows in 1 pages for object 'Quality Approval'.
DBCC results for 'Job_No'.
There are 1 rows in 1 pages for object 'Job_No'.
DBCC results for 'Orders'.
There are 33317 rows in 3481 pages for object 'Orders'.
DBCC results for 'Order Type'.
There are 7 rows in 1 pages for object 'Order Type'.
DBCC results for 'Quality Record'.
There are 116 rows in 1 pages for object 'Quality Record'.
DBCC results for 'Sales_Order_No'.
There are 1 rows in 1 pages for object 'Sales_Order_No'.
DBCC results for 'Parts'.
There are 5717 rows in 240 pages for object 'Parts'.
DBCC results for 'Order Status'.
There are 8 rows in 1 pages for object 'Order Status'.
DBCC results for 'Supply Type'.
There are 2 rows in 1 pages for object 'Supply Type'.
DBCC results for 'Accounts Receivable'.
There are 2 rows in 1 pages for object 'Accounts Receivable'.
DBCC results for 'Sale Price'.
There are 5111 rows in 40 pages for object 'Sale Price'.
DBCC results for 'Month'.
There are 12 rows in 1 pages for object 'Month'.
DBCC results for 'Delivery Address'.
There are 168 rows in 11 pages for object 'Delivery Address'.
DBCC results for 'Sale Potential'.
There are 5 rows in 1 pages for object 'Sale Potential'.
DBCC results for 'Labour Type'.
There are 3 rows in 1 pages for object 'Labour Type'.
DBCC results for 'Manufacturer'.
There are 49 rows in 1 pages for object 'Manufacturer'.
DBCC results for 'Service Contract No'.
There are 1 rows in 1 pages for object 'Service Contract No'.
DBCC results for 'Contact Status'.
There are 3 rows in 1 pages for object 'Contact Status'.
DBCC results for 'Unit Type'.
There are 8 rows in 1 pages for object 'Unit Type'.
DBCC results for 'Items'.
There are 3016 rows in 82 pages for object 'Items'.
DBCC results for 'Product'.
There are 25 rows in 1 pages for object 'Product'.
DBCC results for 'Account Address'.
There are 3597 rows in 171 pages for object 'Account Address'.
DBCC results for 'Goods Received'.
There are 0 rows in 0 pages for object 'Goods Received'.
DBCC results for 'Stock Storage Rack'.
There are 18 rows in 1 pages for object 'Stock Storage Rack'.
DBCC results for 'Call Type'.
There are 14 rows in 1 pages for object 'Call Type'.
DBCC results for 'Purchase Delivery'.
There are 0 rows in 0 pages for object 'Purchase Delivery'.
DBCC results for 'Stock Storage Item'.
There are 134 rows in 1 pages for object 'Stock Storage Item'.
DBCC results for 'Assembly Option'.
There are 3 rows in 1 pages for object 'Assembly Option'.
DBCC results for 'Purchase Type'.
There are 310 rows in 5 pages for object 'Purchase Type'.
DBCC results for 'Stock Storage Area'.
There are 8 rows in 1 pages for object 'Stock Storage Area'.
DBCC results for 'Purchase Line'.
There are 9427 rows in 138 pages for object 'Purchase Line'.
DBCC results for 'Purchase Order No'.
There are 1 rows in 1 pages for object 'Purchase Order No'.
DBCC results for 'Cost Type'.
There are 2 rows in 1 pages for object 'Cost Type'.
DBCC results for 'Stock Storage Location'.
There are 1 rows in 1 pages for object 'Stock Storage Location'.
DBCC results for 'Cost Item'.
There are 10 rows in 1 pages for object 'Cost Item'.
DBCC results for 'Stock Storage Bay'.
There are 144 rows in 1 pages for object 'Stock Storage Bay'.
DBCC results for 'Nominal Name'.
There are 83 rows in 1 pages for object 'Nominal Name'.
DBCC results for 'Job Type'.
There are 2 rows in 1 pages for object 'Job Type'.
DBCC results for 'Company'.
There are 2 rows in 1 pages for object 'Company'.
DBCC results for 'Item Name'.
There are 165 rows in 3 pages for object 'Item Name'.
DBCC results for 'Group'.
There are 2 rows in 1 pages for object 'Group'.
DBCC results for 'Order Line'.
There are 41192 rows in 1374 pages for object 'Order Line'.
DBCC results for 'Group Nominal'.
There are 10 rows in 1 pages for object 'Group Nominal'.
DBCC results for 'Test Company'.
There are 2 rows in 1 pages for object 'Test Company'.
DBCC results for 'Report'.
There are 114 rows in 3 pages for object 'Report'.
DBCC results for 'Contact Type'.
There are 4 rows in 1 pages for object 'Contact Type'.
DBCC results for 'Purchase Line Status'.
There are 8 rows in 1 pages for object 'Purchase Line Status'.
DBCC results for 'Sales Targets'.
There are 6 rows in 1 pages for object 'Sales Targets'.
DBCC results for 'Assemblies'.
There are 139 rows in 1 pages for object 'Assemblies'.
DBCC results for 'Call Category'.
There are 3 rows in 1 pages for object 'Call Category'.
DBCC results for 'Job'.
There are 8 rows in 1 pages for object 'Job'.
DBCC results for 'Asset No'.
There are 1 rows in 1 pages for object 'Asset No'.
DBCC results for 'Mark Up'.
There are 468 rows in 3 pages for object 'Mark Up'.
DBCC results for 'Customer Unit Rate'.
There are 6201 rows in 56 pages for object 'Customer Unit Rate'.
DBCC results for 'Delivery Contact'.
There are 27 rows in 1 pages for object 'Delivery Contact'.
DBCC results for 'VAT'.
There are 3 rows in 1 pages for object 'VAT'.
DBCC results for 'Default Mark Up'.
There are 6 rows in 1 pages for object 'Default Mark Up'.
DBCC results for 'Division'.
There are 11 rows in 1 pages for object 'Division'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BIDGroup'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Weird huh?

Cheers

ras

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-18 : 13:31:20

The original error message you posted didn't really report what the error was.

It is possible that it was something like a deadlock. I sometimes get deadlocks when running a DBCC CHECKDB against a database that is active.

You could ask Backup Exec tech support if they can explain what it was. I found this link on the Veritas support site:
http://forums.veritas.com/discussions/thread.jspa?threadID=51339

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-06-18 : 13:36:54

My recommendation would be to have SQL Server generate backups to local files, and THEN have Tape Backup copy them to tape.

Getting 3rd party software to directly backup databases to tape seems to be the cause of alarming threads here!

Kristen

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-18 : 14:12:49

Michael: Cheers, I will check that out
Kristen: Yeah, I already have regular backups to HDD via Enterprise Manager. These are then in turn backed up each night to tape.

One more thing, running the DBCC query - Is this comprehensive enough to check for most errors or discrepancies, or is there a more 'thorough' method? just want to be sure that there are indeed 'no' errors within the DB. The strange thing is that this error only occurs on backing up that particular DB. The other's get backed up fine with no error in my backup log.

Cheers for the speedy repsonses dudes :)

ras

Kristen
Test

22859 Posts

Posted - 2006-06-18 : 16:02:51

"I already have regular backups to HDD via Enterprise Manager"

So why is Backup Exec trying to do a consistency check?

"Is this comprehensive enough to check for most errors or discrepancies"

Yes

Kristen

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-18 : 18:06:56

quote:
Originally posted by Kristen

"I already have regular backups to HDD via Enterprise Manager"

So why is Backup Exec trying to do a consistency check?

"Is this comprehensive enough to check for most errors or discrepancies"

Yes

Kristen

Kristen, we have nightly backups via our Backup Exec software. This uses a 'special' agent for backup of the DBs, it's designed especially for backup of SQL DBs. I also have a more 'frequent' backup job in place that backs up our main database every hour to a seperate drive. We only have around 25 users on the network and the database is only around 250MB...this does not incur any degredation in performance whatsoever.

I'm intrigued as to what the error in the backup log could be now

cheers

ras

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-18 : 20:37:01

I don't see much point in using a direct to tape backup for such a small database. It's much less complicated to just backup to disk, and then backup the disk backups to tape.

We do a full backup to disk once per day, and do transaction log backups to disk every 5 to 15 minutes. That all gets backed up to tape on a daily basis.

More on the subject here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51150

CODO ERGO SUM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-18 : 21:52:11

does the backupExec agent has the permission to run dbcc?

also, based on experience i am a bit cynical on using backupExec, I can't seem to get the restoration to work

without using SA

--------------------
keeping it simple...

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-19 : 04:56:57

Hi Jen

I assume so - It is the built in 'option' within the Backup Exec..I just 'check' it and it checks all DBs (I assume using DBCC). I suppose I could leave these consistency checks disabled and simply setup some form of batch file to run DBCC manually against our DBs. Would this be feasible and, if so, could I achieve this via the Enterprise Manager Query Analyzer?

many thanks

ras

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-19 : 05:06:36

you can run the dbcc via a job but i don't know how you will pass the value to backupExec...

in EM, use the backup maintenance wizard with consistency check for the file

in QA, create a backup job with a consistency check prior to the backup, no backup is created if the consistency check shows up an error, sends an email to notify what just occured so you can take appropriate actions

HTH

--------------------
keeping it simple...

Kristen
Test

22859 Posts

Posted - 2006-06-19 : 06:01:09

If you a "novice" with SQL Server then I recommend using the Maintenance Plan Wizard - you can just choose what checks you want, and when/how often you would like them scheduled. It will schedule your backups too.

Then I would change Backup Exec so it has no interaction with the database at all.

Tell Backup Exec to IGNORE the folder(s) where your database files are, and to include anything else you need it to put on tape.

(And make sure you do a fire-drill recovery test!)

Kristen

ras2a
Yak Posting Veteran

66 Posts

Posted - 2006-06-19 : 06:54:59

Cheers Jen / Kristen,

Jen, I don't mean pass the job to Backup Exec. I would just run the consistency checks via DBCC in the evenings before the backups are carried out. But it doesn't matter, if I can achieve this through the Mainenance Plan Wizard, that's great!

I assumed that the 'sepcial' SQL Agent we purchased for Backup Exec would work fine with SQL - it has done for the last 3 years - lol. It's only now that I've experienced this problem. But, as seen above, running DBCC via QA shows no errors.

Anyway's no probs, I will set the Maintenance Wizard to schedule consistency checks along with backups.

thanks again dudes

ras

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources