SQL Server Issue with Database Integrity Task

Good Afternoon,

I’m having an issue with my maintenance plan that I need help with.

My environment is as follows:
SQL Version: Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
OS Version: Windows Server 2008 R2 Standard x64

I have two sub-plans in my maintenance task:

Subplan1 (starts at 6pm):
  • Check Database Integrity Task (checks 1 database and has the option checked to include indexes)
  • Maintenance Cleanup Task

Subplan2 (starts at 8pm):
  • Back Up Database (Full) (The database being backed up is about 126 Gbs in size)

Upon checking one of our SQL servers the other day, I noticed that both tasks in subplan1 were disabled. Only subplan2 was enabled. The backup was functioning properly from what I could see.

I checked with my colleagues to see why these tasks in Subplan1 were disabled but no one knew, so thinking nothing of it, I enabled both tasks in Subplan1.

The next morning I was greeted with an error with subplan1 regarding the failure of the “Check Database Integrity Task”. This error below:

Error Number: -1073548784
Error Message: Executing the query "DBCC CHECKDB(N'MYDATABASE')  WITH NO_INFOMSGS
" failed with the following error: "The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I also noticed that the drive where the database is stored only had 6MB free (Total Drive Size: 249GB, free space before this task was run was about 150GB). I noticed that the “tempdb.mdf” was 139GBs and the “templog.ldf” was 1.7 Gbs, these two files obviously eating all the space on my drive.

I read somewhere that if I restart the SQL server service that these tempdb files would be recreated. So this is what I did and both tempdb.mdf and tempdb.ldf were reduced to 8Mb (tempdb.mdf) and 1MB (templog.ldf) freeing up space on my drive back to around 150GB.

So the following night I monitored subplan1 and noticed the same thing happened.

I googled about this error and did find a few things on it, but still don’t really understand why it’s happening.

I found an article that instructed me to run the below query to identify what could be the cause of this, but the results don’t have anything meaningful that I can work with.

SELECT name,log_reuse_wait_desc FROM sys.databases;

Open in new window


My results are below:

Log_Reuse_Results
I’ve read that running a repair on the database should be a last resort as it will delete data especially if it finds it to be corrupt, so I don’t think I have the courage to attempt this. There must be something I can try.

Can someone let me know how to resolve this issue please (step by step if possible), my knowledge on SQL Server is not very good, so I would appreciate some assistance on getting this issue resolved.

Kind Regards,
N
LVL 1
KevinAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Paul Randal owned/wrote the DBCC procedure. You will see some suggestions saying to run it with estimate only but that was broken in 2008R2 at one point...

Paul tries to explain in this older document (still applies) : https://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-why-would-checkdb-run-out-of-space/

Would be inclined to change the sequence. Always get your backup first, then do any integrity checks afterwards. Make very sure there are no other jobs running (if possible) and fear not, you dont have to run DBCC everyday. Is it possible that the 2 jobs are colliding ? DBCC was disastrously slow at one point. Another good reason to run after backups.

For further reading see the estimate only post : https://www.sqlskills.com/blogs/paul/how-does-dbcc-checkdb-with-estimateonly-work/ and note the use of trace flag....
0
 
ste5anSenior DeveloperCommented:
Well, when your tempdb grows regularly to this size, then you should not shrink it. Cause auto-growth during SQL statement execution can slow down them drastically. Here you should try to evaluate why you need that much space. And when it is necessary, then you need to place it on larger drives.

DBCC in the default mode uses a snapshot to keep the database online during it's check. Thus it maybe the reason for your problem.. So deactivate your subplan 1 and restarting the server. Examine the tempdb space after two or three days.

When it is caused by DBCC, then you may consider using DBCC WITH TABLOCKX, this will not require that much tempdb space, but it will lock the database for normal transactional use.

Any other reason for tempdb usage, or when you need to keep your database online during DBCC, you need to increase your tempdb space.

btw, I hope you have at least four tempdb data files.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you notice what file grow to occupy most of the disk space?
It's also time to organize better your databases. tempdb database files should be stored in a separate disk so it won't take disk space from user databases.
0
 
KevinAuthor Commented:
Thank you and apologies for the late response.

Kind Regards,
N
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.