Post reply

How To Clear the SQL Server Activity Log

  • November 8, 2006 at 6:28 pm

    #61941

    Hi, I'm a DBA in our company and we're using SQL Server 2000.

    Is there a way to clear the SQL Server Log without shutting the server down? I need to know if this is possible as I am not the only Database Administrator in our company. I need to find out how to clear the Activity Log or alter the log messages without deleting the files thru the windows file system. Also, can I create an audit trail or trigger to see if somebody did clear the activity log?

    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

  • November 8, 2006 at 7:55 pm

    #670748

    If you are talking about the SQL Server Error logs...

    sp_cycle_errorlog

    will do the job. That closes out the current one and opens a new one. The closed out one is archived. Depending on your settings you can have several archived logs. I think the default is six. There's no auditing to tell if someone 'cleared' them or deleted old ones.

    -SQLBill

  • November 8, 2006 at 9:13 pm

    #670759

    Thanks SQLBill!

    I actually saw this one also thru SQL Books Online. This is a problem for me where our SQL environment is governed by a lot of DBAs.

    This is also a problem for all DBAs when it comes to securing your data. I mean, Domain Admins are members of the Builtin Administrators Group. This means that they are members of the sysadmin role. So, any member of the Domain Admins can molest your SQL Server and use sp_cycle_log to clear you SQL Log. He can then delete the archived log file (ERRORLOG.1) and rename the others so the Error Logs appear complete. The blame/responsibility here goes to the DBA. =(

     

    Ronald San Juan | SQL DBA
    ID 710124 ~ Code "Northwind"

  • November 9, 2006 at 5:37 am

    #670808

    This is one of many reasons why I remove group Builtin Administrators group.  If you have a DBA you don't need your Sys Admin with that level of access.

    Regards,

    Jason P. Burnett
    Senior DBA

  • January 31, 2007 at 6:43 am

    #686815

    I agree.

    My Rule of thumb  --

    remove the builtin/administartors but remember to make the DBA groups sysadmins before that

  • October 18, 2007 at 7:38 am

    #743116

    SQLBill (11/8/2006)

    If you are talking about the SQL Server Error logs...

    sp_cycle_errorlog

    will do the job. That closes out the current one and opens a new one. The closed out one is archived. Depending on your settings you can have several archived logs. I think the default is six. There's no auditing to tell if someone 'cleared' them or deleted old ones.

    -SQLBill

    Thanks also. My boss doesn't like rebooting the database, and I needed a way to cycle my ten-gig log out of the way. Thanks for this help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply