SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Metadata about truncation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dmilam
Posting Yak Master

185 Posts

Posted - 09/07/2012 :  13:12:00  Show Profile  Reply with Quote
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

Pakistan
885 Posts

Posted - 09/07/2012 :  13:15:13  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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/

Edited by - lionofdezert on 09/07/2012 13:16:20
Go to Top of Page

dmilam
Posting Yak Master

185 Posts

Posted - 09/07/2012 :  13:56:15  Show Profile  Reply with Quote
OK, thanks!
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 09/09/2012 :  00:47:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.aspx

You'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.
Go to Top of Page

dmilam
Posting Yak Master

185 Posts

Posted - 09/10/2012 :  13:18:27  Show Profile  Reply with Quote
Thanks very much, Rob.
Go to Top of Page

dmilam
Posting Yak Master

185 Posts

Posted - 11/02/2012 :  18:07:22  Show Profile  Reply with Quote
Here's a recent article on logging and truncation

http://www.sqlservercentral.com/blogs/ctrl-alt-geek/2012/10/31/dont-believe-everything-you-read-truncate-table-is-not-logged/
Go to Top of Page

dmilam
Posting Yak Master

185 Posts

Posted - 10/17/2013 :  16:09:28  Show Profile  Reply with Quote
Another article by Paul Randal

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1930-truncate-table-is-non-logged/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000