Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server database maint plan is failing during integrity check

Posted on 2007-12-05
10
Medium Priority
?
855 Views
Last Modified: 2008-09-08
One of my maintenance plans is failing consistantly.  The db in question is on a drive with 175 GB of free space.  I have pasted the text from my log.  The failed job is the User DB integrity check.  

Any suggestions will be appreciated.    

Here is the info from my log file.....

[2] Database IMAGEDB: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table id: -391805569)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is corrupt. Check previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'IMAGEDB'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (IMAGEDB ).

    The following errors were found:

[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table id: -391805569)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKTABLE terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is corrupt. Check previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'IMAGEDB'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (IMAGEDB ).
    ** Execution Time: 1 hrs, 48 mins, 55 secs **
0
Comment
Question by:ispexpert
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
10 Comments
 
LVL 8

Expert Comment

by:i2mental
ID: 20412445
It looks like the filegroup for TEMPDB is full and not set to autogrow. Right click on the tempdb, select properties. In the files section, check the autogrowth column for the data file in the primary file group. Edit that for more space or to automatically grow.
0
 

Author Comment

by:ispexpert
ID: 20412461
It is set to autogrow by 10%.  Do I need to change that to a particular size in MB?
0
 
LVL 8

Expert Comment

by:i2mental
ID: 20412489
No, 10% should be fine. Is that file on the drive that has free space? You mentioned 175gb of space, but that's not necessarily where the tempdb is as well.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:ispexpert
ID: 20412546
Yes, tempdb is on the F: drive where there is 175 GB free.
0
 
LVL 8

Expert Comment

by:i2mental
ID: 20412683
Well the message is pretty specific. It's saying that you've run out of space in the tempdb. That can either be because the file is not set to grow, so the space within the file actually fills up or because the hard disk is out of space so when it tries to grow, there is no room left. If all of the files in the tempdb are set to autogrow and those file reside on a disk with plenty of space... then that's strange.

It's failing on a DBCC CHECKTABLE command, but I can't tell what the table name it's checking is.

You can run DBCC CHECKTABLE ('tablename') WITH ESTIMATEONLY to determine how much space this process will need in the tempdb and make sure that it can get that space. You'll have to determine which table is being processed or look at them all if there are not too many.

You can do the same thing with DBCC CHECKDB WITH ESTIMATEONLY
0
 

Author Comment

by:ispexpert
ID: 20412806
I'm not sure how to find out which table is being processed so I was going to run this command against all the tables in tempdb.  I get an error in query analyzer saying estimateonly is not a recognized option.  
0
 

Author Comment

by:ispexpert
ID: 20412897
I have looked in the application event log only to find that the events from 12/2/07 (the day of failure) have been overwritten.  
0
 
LVL 8

Accepted Solution

by:
i2mental earned 2000 total points
ID: 20413134
the dbcc commands are actually being run on the  on the IMAGEDB database so that's where you should run your estimates. The DBCC command just leverages the tempdb while it's working. It's running out of working space.

The command for sql server 2000 should be

USE IMAGEDB
DBCC CHECKDB WITH ESTIMATEONLY

or

USE IMAGEDB
DBCC CHECKTABLE ('yourtablename') WITH ESTIMATEONLY

0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question