asked
SQL Server 2005 Error
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 select log_reuse_wait_desc from sys.databases where name = 'tempdb'
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 `SELECT name, log_reuse_wait_desc FROM sys.databases` as the error message suggests and see what it returns and let that guide you
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: SELECT name,log_reuse_wait_desc FROM sys.databases D When you cycle the instance (restart sql server) tempdb should be shrunk back to the initial size that is set in the database settings.
