Friday, January 27, 2012

Cycling SQL Server Error Log

I have a daily check list that I go through everyday. And checking the SQL Server error log is one item on the list. Depends on how your server is configured SQL Server logs informational and error messages to the log file. By default your server is configured to have 6 log files that it keeps and when the SQL Server Service starts it removes the oldest log file. In the meantime if your server is up for a long time the information in that log file just keeps growing and makes the log file hard to open from remote workstation.

In the environments that I support I change the configuration so that I keep more than 6 files. I set up to store 99 files (which is the max allowed files for the SQL Server error log). Then I create a job that runs every night right after midnight. This way when I come in every morning there is a brand new log file and since the daily log files are much smaller it makes the file easy to open and check. I use the script below to change the number of log files (note you can do this using SSMS GUI and just make a right click on SQL Server Logs under Management folder than click on Configure and change the value to what you need). After the change using second script I create SQL Server Agent Job that is scheduled to run right after midnight to cycle the error log. This make the daily task of checking the error logs easier (at least for me). Use the scripts below in a test environment and once you find out it could add some value to your daily tasks you could use it in your production environment.

HTH,
Bulent

Script 1:
This changes the number of log files stored to a value you set at the end and uses extended stored procedure to update the registry. Remember this can be done through GUI but it does executed the same in the background.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 60
GO



Script 2:
This script creates a job (make sure you sql server agent is running) and schedules it to run at 12:00:01 am everyday.

USE [msdb]
GO
/****** Object: Job [A DBA SQL Server Error Log Cycle] Script Date: 01/27/2012 14:01:38 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [Database Maintenance] Script Date: 01/27/2012 14:01:38 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'A DBA SQL Server Error Log Cycle',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'Cycle sql serve error log and create a new error log file',
@category_name=N'Database Maintenance',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Cycle Error Log] Script Date: 01/27/2012 14:01:38 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle Error Log',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC sp_cycle_errorlog ;
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Cycle Error Log',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20111013,
@active_end_date=99991231,
@active_start_time=1,
@active_end_time=235959

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Friday, January 20, 2012

SSRS 2008 Transport Error Code 0x800ccc15

Hello all,

I received and email stating that a user did not receive the report from SSRS that he was subscribed to in the last couple of days. When I start looking into the report in the subscription tab of the report I saw the message below.

Failure sending mail: The message could not be sent to the SMTP server. The transport error code was 0x800ccc15. The server response was not available

I tried to open the report and had no problem running the report manually. Then I update the schedule of the report to see if the scheduled sql server agent job is updated as well. Then I looked at the jobs in the server hosting ReportServer database and found the corresponding job with that timed subscription and verified that the execution time of the job is updated with what I did using SSRS portal (In the SQL Server Agent Jobs the name of the jobs for SSRS are given using a system generated GUID and if you don't know the job it's hard to locate since there may be many of them, in my case there were only 8 jobs and I knew which job I need to check once I made the change. However in future blog I will post about this in little more detail). I waited for couple of minutes and saw that the job executed in SQL Server Agent hosting ReportServer database. However still saw the same error message.

I remote into the SSRS server to look into to the problem locally. As soon as I logged in I saw a red icon on the task bar coming from McAfee. I remember that I had to change the configuration of the McAfee to allow database mail to work couple weeks ago. I opened the log and found that the log event below.
1/19/2012 2:30:30 PM Blocked by port blocking rule C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe Anti-virus Standard Protection:Prevent mass mailing worms from sending mail 192.168.162.162:250

That is when I knew what was wrong. The McAfee ePO policy change forced to server so that processes that need to send email has to be excluded in the Prevent Mass Mailing Worms section. I talked to sysadmin who administers the ePO policy to exclude the ReportingServicesService.exe and push the policy again to the server. Within a minute policy was in effect. I update the report subscription to 3 minutes later and waited for the mail to arrive with the report attached. After 3 minutes voila, I received the email and solved the problem. In my case it was as simple as adding the process to exclusion list to send SMTP emails.

HTH,
Bulent

Thursday, January 12, 2012

SQL Function QUOTENAME()

Hello all,

I support third party software databases at work. During the implementation of the application it needed to create a database to store the data. However the application created a database with a name that does not follow the rules for the format of identifiers. In my case we ended up with a database name '20120101_Survey'.

On the same server I have a custom built script that backups up all the databases at night. However, following the database creation my backup process failed and I got an alert about the process. Further looking into the problem I found out that there was a problem with the syntax. As soon as I see the statement I knew what the problem was but never thought about it while deploying the application and database. To keep the story short I used the SQL Server function called QUOTENAME() to fix the problem. Using the function in my custom script help me return the database names with delimiters so that backup process completes without error. Since then I have been using the function with all the object names. This simple function can prevent some headaches if a DBA needs to support 3rd party databases (SharePoint is also a good example because the SharePoint database names don't follow the rules of the identifier format). Here is the link to the msdn and below is simple statement that returns all the databases in your system with delimiters.

SELECT QUOTENAME(name) AS DelimitedDbName
FROM sys.databases

HTH,
Bulent

Enhanced by Zemanta