SQL Server Performance

Databases disappear

Discussion in 'SQL Server 2005 General DBA Questions' started by DBA_Ashfaq, Dec 19, 2006.

  1. DBA_Ashfaq New Member



    Hello everyone;
    On one of my box I found Monday morrning that one of my database disappear; I restored it from last backup but I want to know who delete it or why it disappear. I checked server log which shows it started on Saturday night but did not found any entry for deletion. Is there any way to retrieve information about deletion of a database.
    or SQL server is built in a way not to record such information.

    any idea please..........


    Ashfaq
  2. MohammedU New Member

    I don't think there any way other than sql trace...
    In sql server 2005 sql runs backend trace and writes the files to C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG location so if you have those file you may get some info from them...otherwise you are out of luck...


    Mohammed U.
  3. Adriaan New Member

    Are you logging on with the same credentials?
  4. madhuottapalam New Member

    quote:Originally posted by MohammedU

    I don't think there any way other than sql trace...
    In sql server 2005 sql runs backend trace and writes the files to C:program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG location so if you have those file you may get some info from them...otherwise you are out of luck...


    Mohammed U.

    I think since it is SQL 2005, what you should do is create DDL Triggers on Server Level to track these kind(who/when created/deleted this DB/login etc) of activities.

    Madhu
  5. DBA_Ashfaq New Member

    i am logging with same "sa" credentials; when i check for this otherwise i am using different account. Trace you can use when you looking for something that will happen in future. and also you can't run trace continously; because it put extra burden and generate huge file in couple of minutes in heavy use boxes. any other idea please.......

    Ashfaq
  6. madhuottapalam New Member

    why don't u try DDL trigger? the sole purpose of DDL trigger is these kind of audit only

    Madhu
  7. satya Moderator

    For such things SQL server error log is the first and best to look out, that gives you high level information and if you are really worried about such things then you have to tighten the security on database.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  8. DBA_Ashfaq New Member

    i will try to come up with DDL triger but if you or anyone have any format or syntax please post it that we all get the advantage.

    Ashfaq
  9. satya Moderator

    Refer to the books online for SQL 2005 on code examples in this case.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  10. madhuottapalam New Member

    there is an another way, try Windows Management Instrumentation Alerts. define a WMI alert you need to know how to write WQL queries. if u can do r&d on this and share the knowledge it would be great

    Madhu
  11. satya Moderator

    [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] Even BOL has such section<a target="_blank" href=http://msdn2.microsoft.com/en-us/ms180524.aspx>http://msdn2.microsoft.com/en-us/ms180524.aspx</a> &<a target="_blank" href=http://msdn2.microsoft.com/en-us/ms186371.aspx>http://msdn2.microsoft.com/en-us/ms186371.aspx</a> as a warmup.<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Contributing Editor & Forums Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  12. madhuottapalam New Member

    Try this DDL trigger and AuditTrail procedure.......

    Step 1.... Create a AuditTrail table

    CREATE TABLE <DatabaseName>dbo.DDLAttemptLog
    (EventData xml NOT NULL,
    AttemptedOn datetime NOT NULL DEFAULT GETDATE(),
    AttemptedDBUser char(50) NOT NULL)

    Step 2.... Create Server Level DDL Trigger

    Alter TRIGGER srv_trg_AuditDatabaseActivity
    ON ALL SERVER
    FOR CREATE_Database,ALTER_Database,DROP_Database
    AS
    INSERT <DatabaseName>.dbo.DDLAttemptLog
    (EventData, AttemptedDBUser )
    VALUES (EVENTDATA(), USER)

    If u want to rollback, then do accordingly in the trigger body. Also replace the <DatabaseName> with your databasename.

    Madhu
  13. DBA_Ashfaq New Member

    i am tring to use your store procedure but encountering with some problems. first error is "Cannot find data type XML". this error is from ruinning first step.

    when i run second part it give me error 'Eventdata' is not a recognized function name.

    i am running it over SQL 2000. am i doing someting wrong or there is some problem in the above code?
    Also did you tried it at your test box.
    thanks for posting

    Ashfaq
  14. MohammedU New Member

  15. madhuottapalam New Member

    Hi Ashfaq,

    u posted this under SQL 2005 forum thats why i didn't mention that. This will work only 2005. DDL is one of the New feature in SQL 2005. as mohammed told, XML datatype and EVENTDATA() functiona also not available in 2000.

    Madhu

  16. DBA_Ashfaq New Member

    thank you all but if any one has any idea how we can get around this problem in SQL 2000 please e-mail me at dba.ashfaq@gmail.com
    thank you.


    Ashfaq
  17. satya Moderator

    THere is no such available as mentioned you have to depend upon the PROFILER (Server side trace) or use third party tool to audit the SQL Server.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  18. MohammedU New Member

    Most of the 3rd party tools also uses sql trace so why don't use create your own trace without paying<img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br /><br />Mohammed U.

Share This Page