You may want to consider just setting the Recovery Model for tempdb to Simple
. That should get you around the problem you are having as it will reclaim all of the space used in the transaction log for tempdb. The MSDN documentation also suggests setting the Recovery Model for tempdb to Simple for performance reasons.
MSDN: http://msdn.microsoft.com/en-us/library/ms175527.aspx
EDIT
According to the documentation, for SQL Server 2005 - 2008 R2, tempdb cannot be backed up and should always be set to the Simple
recovery model. If for some reason you are not using the Simple
recovery model for tempdb, I would suggest switching to it.
EDIT 2
Just to be certain that the recovery model for tempdb is set to Simple, execute the following stored procedure:
sp_helpdb
Check the results for tempdb
, and make sure the status
column for tempdb
shows something like:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, ...
If it does, then your problem may be related to the operations you are performing. Are you trying to transactionally write to a temp table? Do you have open transactions that are not getting closed? Is there one operation in particular that is crashing, or does everything not work? You may want to check the answers to this similar question on serverfault for some more ideas. Long story short, if you're still getting this error after setting the Recovery Model to Simple I would start looking at the operations you're trying to perform as a possible culprit.
EDIT 3
From the output of DBCC SQLPERF (LOGSPACE)
it appears that your tempdb log file is only 1.24 MB, which seems unnecessarily small to me. You can increase the size of the log file by using Enterprise Manager, or you should be able to use the following SQL (although you might need to look up the log file name by checking the files on tempdb):
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 50MB)
You could try increasing the log file size to see if that helps.
It is possible that your databases were set up this way. To find out you can run this query:
SELECT DATABASE_NAME(database_id) DbName,* FROM sys.master_files ORDER BY 1;
It lists all files that belong to each of your databases.
If the databases in question list only one file each, then something outside of SQL Server is creating these copies.
Otherwise each database is just split into multiple files and actually taking up that space. In that case you can really only either delete databases or increase the disk size.
It can have performance benefits to split a database into multiple data files. However, splitting the log into multiple files is usually unnecessary as SQL Server is writing always only to one of them at a time.
Best Answer
MS SQL Server is an ACID compliant RDBMS. One of the critical tools involved in providing ACID compliance is a concept of a transaction log. The transaction log is where the pages modified is saved, so it can revert them and put them back in the previous state if you need to rollback a transaction. There is much to consider here but you want to ensure you size your VLFs and manage it in advance.
TempDB is a temporary area used by MS SQL Server to store objects like temp tables, join results, some caching, etc. Unless you have performance issues, you generally don't want to worry about this, don't even back it up. It get's rebuilt on startup. It is good practice to create at least 2 or maybe up to 4 physical files for TempDB so you don't get PAGELATCH contention.
Paul Randall (An authority on MS SQL Server) recommends letting your transaction log be at whatever size it grows to after a full weeks activity after reindexing. Really, unless it grows a lot, don't worry too much about it. Just make sure to avoid growths.