, , , ,

Today I started my day with TempDB Disk Full alerts in my inbox for one of the SQL Server 2000 Production databases. Only 5GB space left on the disk where TempDB files reside. As it is one of the crucial server so I did not want to restart the SQL Server Instance as it is one of the easiest approach to free up the TempDB size as TempDB is created every time SQL Server Instance is started. But due to the business need restarting the SQL Server had to be the last option.

As it was SQL Server 2000 instance so the available options to us is little limited. So at first I wanted to SHRINK the log file that was around 98% FULL. I used the following command:

USE tempdb
DBCC SHRINKFILE (‘templog’, 1024)

The command ran successfully. But there was no change in database size.

So then my next step was to find any uncommitted transactions, which is not letting me to shrink the log.. for that I use the following command:

DBCC OPENTRAN (‘tempdb’)

There was one SPID that was running for last two days. Now I had to see what that SPID is doing. For that, I used the following query:


It was a SELECT query that was running for last 2 days. It was using SORT operation on the result set. By that time TempDB log was around 99% full.

I used the following command to KILL the culprit SPID:


After this, again I tried to SHRINK the database log file with the following command:

DBCC SHRINKFILE (‘templog’, 1024)

Bang. It was done. Now the log file was around 2% full.

This is a solution for emergency situations. For long-term solution, try to use the Proper file sizes and add log files if needed. Please create right indexes on tables.

I can go for a break now.


Subhro Saha