Home > Microsoft, Politics, SQL, Standards > Rolling SQL Server Error Logs WITHOUT Rebooting

Rolling SQL Server Error Logs WITHOUT Rebooting

April 17, 2008

This is a bit in reverse as the code will be above the rant today.😉

To roll SQL Server Error Logs, in 2000 and higher, a reboot isn’t necessary. 

Simply open up the Management Console (or Query Analyzer) and attach to the Master database.  From there, execute the built-in stored procedure ‘sp_cycle_errorlog’.

exec sp_cycle_errorlog
GO

This will roll your current ERRORLOG file along, according to the configuration settings you’ve specified.  The current log will be renamed ERRORLOG.#.

<rant>

We had a… discussion… earlier today between the department I work in and another regarding the virtues of uptime and keeping servers available. 

The paradigm is to reboot a server when something happens—no matter what.  Troubleshoot? Nah.  Diagnose?  No way.  Just kick it and pray it works.  Why?  Because that’s how Windows works.

Now, I agree with that 100%.  That is how Windows works.  I do that at home.  If my home computer starts being stupid, I’ll reboot it as a first step and go from there.

But, my home computer isn’t in a production environment with thousands of users (that I’m aware of).

There’s a big different there.

</rant>

 

Categories: Microsoft, Politics, SQL, Standards
  1. Mr Penny
    October 15, 2008 at 9:36 am

    I agree that you shouldn’t have to reboot your production machine, especially when you run a 24/7 command center that requires the use of your production SQL Server to do their job. Thanks for this quick way to cycle the error logs. My production server had been running for several months without a reboot. When it came time that I actually needed to look at the error log, I couldn’t because it had grown to over 200 GB and Enterprise Manager pretty much crapped its pants when I tried to open it. Now I’m going to put this command in with my monthly maintenance plan to keep the logs a manageable size.

    Thanks again!

  1. No trackbacks yet.
Comments are closed.
%d bloggers like this: