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 )
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>