SQL Server Performance

Identifying the cause of a table emptying

Discussion in 'SQL Server 2005 General DBA Questions' started by ivanmca, Apr 10, 2007.

Page 1 of 2

  1. ivanmca New Member

    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?
    ivanmca, Apr 10, 2007
    #2

  2. satya Moderator

    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.
    satya, Apr 10, 2007
    #2

  3. ivanmca New Member

    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.
    ivanmca, Apr 10, 2007
    #2

  4. MohammedU New Member

    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.
    MohammedU, Apr 10, 2007
    #2

  5. satya Moderator

    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.
    satya, Apr 10, 2007
    #2

  6. MichaelB Member

    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
    MichaelB, Apr 10, 2007
    #2

  7. MohammedU New Member

    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.
    MohammedU, Apr 10, 2007
    #2

  8. ivanmca New Member

    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.
    ivanmca, Apr 10, 2007
    #2

  9. MohammedU New Member

    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.
    MohammedU, Apr 10, 2007
    #2

  10. satya Moderator

    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.
    satya, Apr 11, 2007
    #2

  11. MohammedU New Member

    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.
    MohammedU, Apr 11, 2007
    #2

  12. satya Moderator

    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.
    satya, Apr 11, 2007
    #2

  13. MohammedU New Member

    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, Apr 11, 2007
    #2

  14. MichaelB Member

    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
    MichaelB, Apr 12, 2007
    #2

  15. MohammedU New Member

    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.
    MohammedU, Apr 12, 2007
    #2

  16. ivanmca New Member

    Where exactly are these trace files? Server hasn't yet been restarted.
    ivanmca, Apr 12, 2007
    #2

  17. MichaelB Member

    - 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
    MichaelB, Apr 12, 2007
    #2

  18. ivanmca New Member

    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.
    ivanmca, Apr 12, 2007
    #2

  19. MichaelB Member

    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
    MichaelB, Apr 12, 2007
    #2

  20. MohammedU New Member

    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.
    MohammedU, Apr 12, 2007
    #2

  21. alzdba Member

    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)
    alzdba, Apr 13, 2007
    #2

  22. MichaelB Member

    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
    MichaelB, Apr 13, 2007
    #2

  23. MohammedU New Member

    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.
    MohammedU, Apr 13, 2007
    #2

  24. satya Moderator

    satya, Apr 14, 2007
    #2

  25. MohammedU New Member

    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.
    MohammedU, Apr 14, 2007
    #2

  26. MichaelB Member

    MichaelB, Apr 16, 2007
    #2

  27. MohammedU New Member

    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.
    MohammedU, Apr 16, 2007
    #2

  28. satya Moderator

    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.
    satya, Apr 16, 2007
    #2
Page 1 of 2

Share This Page