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

ramesh 1 gravatar image

ramesh 1
2.2k 63 67 69

(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

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

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

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(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

WilliamD gravatar image

WilliamD
25.9k 17 19 41

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1949
x47
x12

asked: Nov 18, 2010 at 01:55 AM

Seen: 1129 times

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