Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How To Clear the SQL Server Activity Log


How To Clear the SQL Server Activity Log

Author
Message
Ronald San Juan
Ronald San Juan
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
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"
SQLBill
SQLBill
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3451 Visits: 1015
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



Ronald San Juan
Ronald San Juan
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
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"
Jason P. Burnett
Jason P. Burnett
Right there with Babe
Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)Right there with Babe (747 reputation)

Group: General Forum Members
Points: 747 Visits: 1432
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
vineet Raut
vineet Raut
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 26

I agree.

My Rule of thumb  --

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


David.Hartman
David.Hartman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search