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 |
|
Kristen
Test
22859 Posts |
 Posted - 2013-12-19 : 12:09:14
|
| I'm reviewing some code and it has a [permanent] table which one SProc TRUNCATES and adds some rows, and another process then "processes" those rows.Does the TRUNCATE TABLE and INSERT INTO, to repopulate it, use much log space (lets assume it has millions of rows in it)?(The table will recreate each day with a similar number of rows, but almost certainly with different PKeys, and likely in very different sequences)Would a ##TEMP table be a better choice? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-19 : 13:08:58
|
| Yes it uses log space, especially the insert. The truncate is "less" logged than a delete in that the page deallocations are logged.I don't see any reason to switch to a global temp table (##). The current method is fine. Switching is fine too though.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-19 : 13:35:47
|
| I was thinking that ##TEMP would move the logging to TEMPDB (thus SIMPLE mode), rather than FULL logging on the APP DB.I'm also asking myself what happens if two invocations of the SProc run overlapping - one truncating the data the first has just added for processing. I suppose in practice it won't happen (its supposed to be launched by a once-a-day scheduled job). BUT ...... I hate those sorts of "could happen and we forgot to allow for it" situations. Impossible to diagnose when they do happen.Not that that has anything to do with ##TEMP being better/worse than a permanent table. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-19 : 13:42:07
|
| The logging is the same between SIMPLE and FULL. What's different is what happens after the transaction completes (clears from the log or has to wait for backup log).If two invocations of the stored proc overlap, either solution will have an issue since a regular table and a global temp table can be seen by all sessions. Now a regular temp table does not have this issue since it can only be seen by the scope of the session.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-19 : 19:08:51
|
Yup, it was the log backup size that I was thinking about. I don't think we are likely to have a problem with this, single, INSERT transaction being logged in the LDF itself - plenty of space there  But we do have a problem with the amount of log that this application generates, and thus the daily log backup size. Vendor is currently moving parts of it to another database, set to SIMPLE, to reduce the amount of Log that has to be backed up (daily log backup is 100x the size of the database), so anything that relieves pressure on that will help(The issue with the Log Backup is that we are now at the point where we cannot fit the retention-period of Log backups onto tape, so we either have to reduce the retention period, or compress them [and then we don't have the benefit of existing automatic housekeeping of stale log backup files], or upgrade to Enterprise/etc. license to get compressed backups) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-20 : 13:03:21
|
| I'm spoiled. We use Enterprise edition for all systems so compression is there for 2008+. For 2005, we have Red Gate. We certainly would have a storage issue without compressing backups, plus the runtime and load on the system would be a problem.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Log space used by TRUNCATE TABLE