TempDB space issue

I selected properties for TempDB and it shows :

Currently Allocated space : 2.00 MB Available Free space : -795.13 MB (-39756%)

templog.ldf has grown to 13.9 GB currently.

Any reasons for the above ?

more ▼

asked Mar 08, 2010 at 06:04 AM in Default

Sagar Bhargava gravatar image

Sagar Bhargava
358 24 25 27

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

1 answer: sort voted first

Seems odd. It sounds like it's done a bunch of work, but hasn't checkpointed yet. That way, the size of the database in RAM could be larger than the database on disk. Also, having no checkpoint done would be seeing the log file grow, as it can't truncate.

One interesting thing about tempdb is that although lots of activity will still cause the log file to be written to, the temporary nature of the data should mean that it rarely needs to appear in the MDF file.

Make a connection to the tempdb database and run the command CHECKPOINT. Then have another look and see if my theories hold.

And yes, this is the kind of thing that makes me wish I had the time and money to do an MCM in SQL.

more ▼

answered Mar 08, 2010 at 06:13 AM

Rob Farley gravatar image

Rob Farley
5.7k 16 18 20

Did : use tempdb checkpoint

In the mean while Templog grew and I can see :

Available Free space : -942.88 MB (-47143%)
Mar 08, 2010 at 06:55 AM Sagar Bhargava
Ok. So check open transactions, to see if something is stopping the truncation happening.
Mar 08, 2010 at 08:21 AM Rob Farley
It has to be an open transaction. We were hitting this with a third party app. It kept leaving a transaction open, chewing up tempdb space.
Mar 08, 2010 at 09:54 AM Grant Fritchey ♦♦
(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



Answers and Comments

SQL Server Central

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



asked: Mar 08, 2010 at 06:04 AM

Seen: 1145 times

Last Updated: Mar 08, 2010 at 06:04 AM