hi team, today i have execute DBCC CHECKDB, on one of my DB, but i was continously getting this
Msg 9002, Level 17, State 4, Line 1 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.database
How big is the tempdb log?
Have you left the default options on (autogrow 10%), or have you changed them?
How big is the disk that this log file resides on?
How much space is left on the disk?
What is the value of
It is telling you that the tempdb has no space in its log file. Something on your server has used it all up. You have several options:
- You can change the size of the file - making it bigger will let things continue
- You can add a new file so the tempdb has 2 log files - this will give the database room to work
- You can review what caused the problem and see if it should have and change it or allow for it when it happens next time - You can run a script like
answered Nov 18 '10 at 02:13 AM
Ramesh - the error speaks for itself. Your tempdb log file is full.
DBCC CHECKDB does a lot of work and uses tempdb to do it. If you are checking a large database/table/index then this can cause tempdb to grow.
I suggest you take a look at the autogrowth settings for tempdb, also check the drive tempdb is on - is it full? Also check what recovery mode is running for tempdb - simple should be fine to use here.
As stated in the error the following queery will give you some idea as to what is wrong:
When you cycle the instance (restart sql server) tempdb should be shrunk back to the initial size that is set in the database settings.
answered Nov 18 '10 at 02:18 AM