x

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

more ▼

asked Nov 18, 2010 at 01:55 AM in Default

avatar image

ramesh 1
2.2k 66 69 73

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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'
more ▼

answered Nov 18, 2010 at 02:13 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

thanks this worked so

Nov 18, 2010 at 09:05 PM ramesh 1
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Nov 18, 2010 at 02:13 AM

avatar image

Fatherjack ♦♦
43.7k 79 98 117

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Nov 18, 2010 at 02:18 AM

avatar image

WilliamD
26.2k 18 34 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2018
x52
x15

asked: Nov 18, 2010 at 01:55 AM

Seen: 1291 times

Last Updated: Nov 18, 2010 at 03:24 AM

Copyright 2016 Redgate Software. Privacy Policy