Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Clear the SQL Server Activity Log Expand / Collapse
Author
Message
Posted Wednesday, November 8, 2006 6:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2008 2:57 PM
Points: 149, Visits: 155

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"
Post #321518
Posted Wednesday, November 8, 2006 7:55 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 8:11 AM
Points: 3,244, Visits: 529
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



Post #321528
Posted Wednesday, November 8, 2006 9:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2008 2:57 PM
Points: 149, Visits: 155

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"
Post #321541
Posted Thursday, November 9, 2006 5:37 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 4, 2013 6:04 AM
Points: 747, Visits: 1,432
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
Post #321605
Posted Wednesday, January 31, 2007 6:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 7, 2010 4:39 AM
Points: 35, Visits: 26

I agree.

My Rule of thumb  --

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

Post #341282
Posted Thursday, October 18, 2007 7:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 18, 2007 7:24 AM
Points: 1, Visits: 0
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.
Post #412203
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse