question

ramesh 1 avatar image
ramesh 1 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
sql-server-2005dbcccheckdb
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
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'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thanks this worked so
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.