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 |
|
dmilam
Posting Yak Master
185 Posts |
 Posted - 2012-09-07 : 13:12:00
|
| Does SQL Server expose any metadata regarding table truncation? I recall that it doesn't, but perhaps in a log file if not a T-SQL solution? That is, how to determine if a table was truncated and records (re)inserted? |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-09-07 : 13:15:13
|
| As TRUNCATE TABLE is a DDL, so log is not maintained for records that are truncated, resultantly. Only way to bring them back is Point in Time Recovery.--------------------------http://connectsql.blogspot.com/ |
 |
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2012-09-07 : 13:56:15
|
| OK, thanks! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-09-09 : 00:47:39
|
| TRUNCATE TABLE is logged but in a minimal fashion. You can detect it using the fn_dblog function, it's an undocumented function but there's details about it here:http://sqlskills.com/BLOGS/PAUL/post/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspxYou'd probably have to experiment with fn_dblog to differentiate between a DELETE and a TRUNCATE TABLE.A better option may be to set up a SQL trace for TRUNCATE TABLE operations and leave it running. As long as you're not performing many TRUNCATE TABLE the trace file should remain small. None of these will help you recover lost data, you'd have to restore from backups. |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2012-09-10 : 13:18:27
|
| Thanks very much, Rob. |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2012-11-02 : 18:07:22
|
| Here's a recent article on logging and truncationhttp://www.sqlservercentral.com/blogs/ctrl-alt-geek/2012/10/31/dont-believe-everything-you-read-truncate-table-is-not-logged/ |
|
|
|
dmilam
Posting Yak Master
185 Posts |
Posted - 2013-10-17 : 16:09:28
|
| Another article by Paul Randalhttp://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1930-truncate-table-is-non-logged/ |
|
|
|
|
|
|
Metadata about truncation