TempDB issues

Dear All

If we recieve a error message in Event log ” The log file for database ‘tempdb’ is full. Back up the transaction log for the database to free up some log space”

First thing we should check for disk space on which tempdb database database files are present .

If the disk space is enough ( not full or less)

Right click on Tempdb Database and in properties tab set the Space allocated to higer value for LOG file and see to it that it is given option to auto grow and recovery model is set to simple.
Actually when we restart SQL server Tempdb database is created from scratch but for that much time tranasactions will be stopped ( SQL server is stoped )

If we want to check for the oldest transaction taking tempdb space then we can use a technique to know the transaction ( SPID ) and we can kill it

DO the following steps in sequence

1) Open SQL query analyzer   ( type following commands )

<strong>Use tempdb</strong>

<strong>DBCC OPENTRAN(’tempdb’)</strong>
            Check the oldest transaction (if it returns any), and see who the SPID is (there will be a line starting with ‘SPID (Server Process ID) : <number>’).
Use that <number> in the following:

<strong>DBCC INPUTBUFFER(SPID number)</strong>

             This will tell you at least a portion of the last SQL command executed by this SPID, and will help you determine
If you want to end this process with:

<strong>KILL SPID number </strong>

Thanks

Prashant Deshpande

Advertisements

Leave a comment

Filed under Database Server, Server Application, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s