Identifying the cause of a table emptying | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Identifying the cause of a table emptying

We recently had an issue where a table powering our website was completely emptied (was ~9600 records, then 0. Table still there) Table emptying happened some time between 02:00 and 06:30 or so Sunday morning (when we first noticed.) It’s a strange time for it to happen as there is low load on the site at this time, and developers would not be working on the site at that time (e.g. unlikely a dev typo.) Restored from the 02:00 backup so no permanent damage but I would be keen to try to work out why this happened with a view to it not reoccuring. Can I extract information on what exactly caused this from the transaction logs (e.g. exact time, statements involved, user account involved, anything else?) Would this tool help:http://www.sql-server-performance.com/log_explorer_spotlight.asp – cost $995? Any alternatives costing less?
If you are worry about that particular time then why not take help of PROFILER to see whats that ghost process truncating the table. Do you have any scheduled jobs during that time> Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Hi satya, it was a once-off, had never happened before and don’t expect it to reoccur. Unfortunately profiler wasn’t running so really need some other way to diagnose what happened. We had moved servers that weekend and running reads off the subscriber, writes going back to the publisher. Lots of problems with the server move but may not be related at all to the db issue.
I don’t think you get anything cheaper than this but you can try trial version of AudiDB which new product from lumigent… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
If this is not occuring again after your monitoring since the last occurence, then there is no chance to keepup on SQL server to see what was happened during that time. As a pre-cautionary measure you can take help of Operating system audit or SQL audit tools. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I wish we could use DDL triggers like oracle can to capture a truncate before it completes… Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
I believe you can use DDL trigger and rollback the transaction but is the truncate table is DDL OR DML? http://www.developer.com/db/article.php/3552096
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I doubt it’s an actual TRUNCATE as the table is replicated, so truncates being non-logged are not allowed. I would be interested in knowing what exactly happened, e.g. was it a DELETE without a WHERE clause, or 9600 individual deletes, what user account, etc. Any more info like this might help me work out what happened so that I can prevent a re-occurence.
If you have trace/profiler running during the delete…you may get from out of it…
If you want to figure it out from tlog backup you have to take the help of third party tools like AUDIT DB ect… Other than the above there is no other choice… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
As referred above it is not possible if it has happened earlier as SQL doesn’t record such operations anywhere unless in Transaction log, so you need to get third party tools to read the log file during that period. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
SQL server 2005 runs default trace in the backround and keeps only last 10 or 20 files…to see these trace files…check the log folder and trace file will be 20 mb each…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I guess that will not happen until unless you start services with a trace flag? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
No I don’t think you don’t need to add trace flag by default it is on… it is out of the box…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
MohammedU,<br /><br />Hey, I totally forgot about that one! Good catch. The trace is great unless he has rebooted since. We backup the trc files just in case too<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Michael <br />MCDBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7
Yes, you can create a job to copy the trace files to different location and load them into a table…but I don’t remember what are the events it captures…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Where exactly are these trace files? Server hasn’t yet been restarted.
– these tracefiles can be found in the …Log folder.
– [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.***MSSQLServer
key "DefaultLog"="D:\MSSQL.1\MSSQL\Log" points to the default log location.
Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
Thanks to all of you, found those trace files! Unfortunately too late though, they only go back to yesterday. Good to know though for the future.
I learned about them like you.. in the heat of battle when it was too late. We back them up that directory so we can always get them now if we need to. Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
Make sure your log folder drive has enough space otherwise sql will stop writing trace file…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
quote:Originally posted by MohammedU SQL server 2005 runs default trace in the backround and keeps only last 10 or 20 files…to see these trace files…check the log folder and trace file will be 20 mb each..
MohammedU.

Do you know how to set the number of default trace rolloverfiles SQLServer will keep [?] (default appears to be 5)
no idea.. maybe the other guys will know!<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Michael <br />MCDBA<br /><br />"The fear of the Lord is the beginning of knowledge,<br />but fools despise wisdom and instruction." Proverbs 1:7
I don’t think you can increase the number of files…
You have to copy the files to different location if you want to maintain them…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
see this http://msdn2.microsoft.com/en-us/library/ms189034.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
quote:Originally posted by satya see this http://msdn2.microsoft.com/en-us/library/ms189034.aspx Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

The mentioned url is related to regular trace not for default trace which is running in the backround out of the box…
And I don’t think there is any way we can change but there may reg. key… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
You can turn off the default trace if that is what you mean.. http://msdn2.microsoft.com/en-us/library/ms175513.aspx Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
Yep, you need to use sp_configure procedure to turn on/off…
I am still trying to figure it out to increase the no. of files…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Why don’t you put up a feedback on MS Connect site in this regard? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
]]>