Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2007-12-13 : 08:13:36
|
| Hi,I've been having problems with my tempdb filling up, and causing all databases on the server to stop functioning properly. I've been removing alot of data lately (millions of rows), and I think this is the reason why my tempdb log is going thru an unusual load.Whats the best way to make sure the tempdb doesnt fill up causing me major problems? I had temporarily turned off backups while I was having a new HD put in. Am I right in thinking that when a DB is backed up, the tempdb log is reduced in size? Should maintaining a daily backup solution help keep things under control ? Thanks very much for any tips!mike123 |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2007-12-13 : 10:19:33
|
| So far as I know, even with Recovery Model in Simple, SQL Server still records DELETE's in the transaction log. (by design) The log will grow and eventually discard the DELETE transactions. However, you need to shrink your log file should it be taking up uncessary disk space. Perhaps consider using a script as shown below, test and incorporate into a schedule agent job.BACKUP LOG <TempDB> WITH TRUNCATE_ONLYGOdbcc shrinkfile([TempDB_log],100) |
 |
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2007-12-13 : 10:23:42
|
| Moreover, try TRUNCATE instead of DELETE where possible. TRUNCATE is not a logged operation where DELETE is |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-12-13 : 11:10:38
|
| since your problem is tempdb... you should try to schedule batch deletes instead...or add more disk space where your tempdb is or transfer it to another disk with more space if expansion is not possible--------------------keeping it simple... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-12-14 : 03:29:45
|
quote:
Originally posted by Analyzer
Moreover, try TRUNCATE instead of DELETE where possible. TRUNCATE is not a logged operation where DELETE is
Truncate is minimally logged operationMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
tempdb filling up - mass deletes