I’m having an issue with my maintenance plan that I need help with.
My environment is as follows:
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
Windows Server 2008 R2 Standard x64
I have two sub-plans in my maintenance task:
Subplan1 (starts at 6pm):
Subplan2 (starts at 8pm):
- Check Database Integrity Task (checks 1 database and has the option checked to include indexes)
- 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;
My results are below:
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.