Up and Running

Congratulations to Vidmantas Matelis of VidasSoft Systems in Thornhill, Ontario, Canada, and Terry Nolte of Cox Health Systems in Springfield, Missouri. Vidmantas won first prize of $100 for the best solution to the June Reader Challenge, "Up and Running." Terry won second prize of $50. Watch SQL Server Magazine UPDATE for next month's Reader Challenge. In the meantime, here's the solution to June's Reader Challenge.

PROBLEM

Herb's SQL Server systems are getting progressively better uptime, and he's anxious to see whether Windows 2000 can make his systems even more reliable. Herb restarts his SQL Server only when he has to make system configuration changes, which are very rare. This continuous uptime results in excellent availability. One side effect of not restarting SQL Server is that the errorlog file can grow quite large (hundreds of megabytes or more). SQL Server keeps six previous generations of this file, which is recreated each time SQL Server starts. But if Herb doesn’t restart SQL Server for several months, the errorlog file grows very large, which makes it difficult to view in an editor, handle archiving, and so on. Help Herb avoid overly large errorlog files.

SOLUTION

With SQL Server 7.0, Microsoft provides a system stored procedure, sp_cycle_errorlog, that you can use to cycle the errorlog file (i.e., save the previous file and begin a new one) without restarting SQL Server. Herb can use a T-SQL SQLServerAgent job to schedule execution of this procedure at regular intervals:

EXEC master..sp_cycle_errorlog

Unfortunately, this procedure has a bug, which Microsoft article Q241636 documents. Instead of verifying that the login executing the procedure is a member of the sysadmin server role, the logic checks that the login is using the sa login id. In most cases, this difference causes SQL Server to prohibit execution of sp_cycle_errorlog through SQLServerAgent, which uses an NT login by default. To work around this problem, Herb has three options:

Microsoft recommends Option 1 because editing system stored procedures (the second option) can lead to problems if the altered code introduces bugs in the procedures. Option 3 requires you to hard-code login id and password in your job definitions, which isn't a good idea. Herb can also add some surrounding logic to the file cycling. For example, he can specify cycling the file only if it's larger than a set threshold value. (He can check the file size through xp_cmdshell and DIR or xp_getfiledetails.)