June 2, 2005 at 8:24 am
Does a truncate statement invalidate your logs or can you still backup to a point in time? Say you are using full recovery model and taking log backups every hour and a full backup once a day. If you have an application that does a truncate once in a while to reload a table, can you still restore your entire db to a point in time or does the one Truncate statement break this.
If this is the case, I guess using a Truncate statement should not be used in any production system, at least in the code or SP's. The only time I could see it being useful is when you are developing or admin work. Is this correct?
Any infomation will be appreciated.
June 2, 2005 at 8:46 am
From my understanding, I would say it invalidates your logs since TRUNCATE is minimally logged. Only the deallocation of the pages is recorded.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 2, 2005 at 2:00 pm
Can someone confirm Franks statement for sure.
Thanks
June 2, 2005 at 2:13 pm
I think that Frank is correct (as normal). Look up point-in-time in Books Online. There is a how-to that refers to restoring transaction logs to accomplish a point-in-time recovery. If you've truncated the log (without backing it up), and didn't subsequently perform a FULL backup, you won't have a transaction log backup to restore from.
June 2, 2005 at 2:16 pm
I am not talking about Truncating the log file, I am asking if you use the Truncate statement to Truncate a table, does it break your point-in-time recovery.
I realize that Truncating your log file will not allow you to restore to point-in-time after you do it.
Any other comments?
June 2, 2005 at 2:23 pm
Sorry, I didn't realize that you were referring to a TRUNCATE TABLE command vs. a truncate log command.
I don't know that a truncate table command would invalidate a point in time recovery. Here's a scenario (and you should definitely test it out using a test db):
1. Truncate table occurs at 12:00pm.
2. You decide to do a point-in-time recovery. Restore the database file(s) (you do have your user data in a seperate data filegroup than your system tables, etc. right?). Then you recover up to 11:59am. The truncate statement in the log as of 12:00pm would never get run (in recovery).
Definitely test it out.
June 2, 2005 at 2:28 pm
I agree with you, but if you wanted to do a point-in-time restore at 12:03, would the table be truncated or would the log not restore because Truncates are minimally logged.
I know a test would answer this, I was just wondering if someone knew off hand.
I figured it would be a common question, must not be.
June 2, 2005 at 2:30 pm
I think that the table would be there as of 11:59am, but when the recovery process gets to 12:00pm, it would truncate the table again and continue on. Therefore, if you wanted to retain the table data, you would need to stop as of 11:59am. The restore process would complete correctly, just not include that table's data.
June 2, 2005 at 2:35 pm
Alright, so if that is the case, there is no harm in using Truncate over Delete to clear out a large table that needs to be reloaded.
That's what I wanted to know.
Thanks
June 2, 2005 at 2:47 pm
Actually you can use TRUNCATE TABLE within a transaction, so that it can be rolled-back, the pages are allocated again and nothing ever happened. However, when you need to recover, you should do this as soon as possible after the TRUNCATE was fired, because SQL Server might reallocate the freed space of those pages to other objects, so that the "original pages" are overwritten.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply