Don’t Be a Balloon!

I had a problem just before the festive period where one of our production SQL Servers was randomly grinding to a halt, queries which usually returned in milliseconds where running for minutes.  It took 4 days of performance monitoring / troubleshooting and some help from the SQL Community (when I though all was lost) to find the cause of the problem and get it resolved!

To give a little bit of background information about this particular environment we run multiple SQL Server clusters in various geographical locations across the globe which we replicate to from a central SQL Server cluster.  Each of these ‘child’ SQL Servers also replicates certain transactional articles back to the central environment.  Each server runs SQL Server 2005 Standard Edition SP3 x64 on Windows Server 2003, they have different CPU / Memory allocations designed to support their respective workloads.

The server encountering the problem had 50% more memory than another server and was running half the load.  The problem first came to light when a couple of overnight jobs which normally take a few minutes were running for several hours before I had to stop them.  I started investigating the issue looking at the usual DMV’s with no joy.

Below are a few of the DMV’s I used during my investigations. 

sys.dm_os_wait_stats

sys.dm_os_waiting_tasks

sys.dm_os_performance_counters

sys.dm_os_memory_allocations

sys.dm_os_memory_allocations

sys.dm_io_virtual_file_stats

sys.dm_os_schedulers

sys.dm_os_sys_memory

I recently read a fantastic book – Performance Tuning with SQL Server Dynamic Management Views by Louis Davidson and Tim Ford which although did not lead me to the problem allowed me to dismiss several possible causes.

I then used SQL Sentry Performance Advisor to monitor the server without much joy.  One thing I did notice is that the buffer cache would intermittently drop from its normal size of 6GB to 0?  I used perfmon to monitor SQLServer:Buffer Manager / Buffer Cache Hit Ratio / Page Life Expectancy / Database pages and just as SQL Sentry highlighted these would intermittently drop to 0, then build back up.

I tried finding a correlation between any jobs / processes and the dramatic degradation in performance again with no joy.  I then found errors in the error log similar to the below;

Source                  spid1s

Message

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 115227 seconds. Working set (KB): 1635000, committed (KB): 6453712, memory utilization: 25%.

Why was SQL Server releasing memory?  I checked the server and the locked pages in memory privilege was not granted to the SQL Server service account so I added this and restarted the service after repointing the applications to another server only to find that it didn’t make any difference.  Whilst trawling the super information b road (sorry running joke I had at a previous employer in the days when the ‘super information highway’ was reeeaaalllly slow) I found that on 64bit systems locked pages in memory is not supported on SQL Server 2005 Standard Edition unless you are at SP3 CU4, great (or so I thought) lets plan deploying CU4 and with the locked pages in memory privilege and we should be ok.

Me being me, and I think this is a general DBA thing (maybe not, it might just be my inner geek) I wasn’t happy at just applying the CU, something somewhere was causing SQL Server to release memory, it had never been a problem before, it had only recently started happening.

I stumbled across an excellent article from Jonathan Kehayias about locked pages in memory, it was the Considerations for Virtual environments section that gave me my big break.  Jonathan describes a scenario called memory overcommit as below;

“Memory overcommit is a scenario where the memory allocated to the virtual machines running on the host exceeds the total amount of physical RAM available in the server.

When memory overcommit occurs, one of the first ways that the hypervisor reacts is to make use of a special driver, known as a balloon driver, which is installed in the VM as part of the VM tools. In essence, the hypervisor sets the balloon driver the task of reducing memory consumption in the VM to a target level, and the balloon driver responds by acquiring memory in the VM.”

Now I will hold my hand up here, I haven’t been in my new job long (2^nd month at the time of the issue) and am still learning the environments to this day but that is no excuse.  This server is one of several which run my companies services for the whole of the UK and at the time I didn’t know that it was a Virtual Cluster which I should have.  Another issue is that I didn’t and still don’t have access to the VCentre for this server so I couldn’t see if memory over commit and or memory ballooning was a problem (or so I thought). 

Resorting to Twitter under the #sqlhelp hashtag, Jonathan came to my rescue and eventually after a few questions for further info he told me about some perf counters.  If the VM Tools are installed on a VM then some VM Counters are exposed, you can see where I’m going with this one can’t you?!

Adding this counter I was shocked, all 6GB of the buffer cache had been ‘ballooned’ to disk on the ESX host!  This explained the dramatic degradation in performance, after a call to the team who manage the VMware environment we moved the cluster to other hosts which had not overcommitted memory and performance was restored to normal levels.  Now I have always been sceptical about virtual infrastructure and SAN storage being used for my database servers.  However with careful design to make sure there is no contention / adequate resource with room for growth so environments can scale I have never had a problem of this magnitude before.

There are a couple of things I have learned from this problem;

1 – Make sure you have access to all the hardware which all the servers you manage run on (SAN / VMware etc. etc.) this makes life so much easier when troubleshooting!

2 – Make sure you are involved in every aspect of design and are part of the core decision making team for new environments!  Or if you inherit environments when you move jobs or from acquisitions check and double check everything!

And last and most importantly of all!

3 – Trust no one!  I learned this from a very early stage in my SQL Server career (thank you Mr David C. Riley).  Everyone starts off equal, equally useless (to be polite) until they prove themselves otherwise!

I really hope this helps someone with the same problem or prevents someone encountering the same issue.

Chris

Moving Database Files and Replication

Today’s post is a quick one as I am a bit under the cosh.

There are many ways to move database files, detach and attach, backup and restore, alter database.  But what can you do if the files you want to move belong to a database being used in replication?  Here is one solution I used recently;

USE master

GO

-- set database to restricted user mode

ALTER DATABASE databasename SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

GO

-- set database offline

ALTER DATABASE databasename SET OFFLINE;

GO

-- modify database files

ALTER DATABASE databasename

MODIFY FILE ( NAME = logicalfilename, FILENAME = 'new file path' );

ALTER DATABASE databasename

MODIFY FILE ( NAME = logicalfilename, FILENAME = 'new file path' );

GO

-- move the files to the new drive / directory

-- set the database online

ALTER DATABASE databasename SET ONLINE;

GO

-- set the database to multi user

ALTER DATABASE databasename SET MULTI_USER;

GO

As always there are the usual precautions to take - never do this on a production system without testing it first!!!

Enjoy!

Chris

System Stored Procedures

I've been using 4 alerts for corruption now for about 2 years for Errors 823, 824, 825 and 9100.  Basically I would create an operator via SSMS, the four alerts via T-SQL then manually add the operator to the response through SSMS.  Now given that I only did this during the initial server setup I guess I was just lazy and didn't script the whole process.

So I thought being in a new job and wanting to get these alerts set-up across the estate I would create a script to do just that.  In doing so I couldn't remember the table containing the operators was it sys. or dbo. or operators (IntelliSense was also playing up) at this point I gave up and got the text of the system stored procedure msdb.dbo.sp_add_operator from which I found that the table was msdb.dbo.sysoperators.  

This is something I do quite a lot, there are some very knowledgeable people at Microsoft who spend a lot of time writing some fantastic system procedures.  These are worth a look, this time I only learned a table name I was looking for but who knows what I will next time.  The next time you get a few minutes have a nosey you just never know what you will find.

Below is the script I created, the Operator @name, @email_address and Notification @operator_name variables need updating accordingly along with the IF NOT EXISTS check but that is it.  

NOTE - This script is provided as is and has been tested on SQL Server 2005 and SQL Server 2008.  I always recommend running scripts on a non production environment first.

USE [msdb] ;

GO

-- Create the Operator

IF NOT EXISTS ( SELECT  1

                FROM    msdb.dbo.sysoperators

                WHERE   name = '** Operator Here ***' )

    BEGIN

        EXEC msdb.dbo.sp_add_operator @name = N'*** Operator Here ***',

            @enabled = 1, @email_address = N'*** Email Address Here ***'

    END ;

GO

-- Alert 823 - Hard I/O Corruption

IF EXISTS ( SELECT  name

            FROM    msdb.dbo.sysalerts

            WHERE   name = N'823 - Hard I/O Corruption' )

    EXEC msdb.dbo.sp_delete_alert @name = N'823 - Hard I/O Corruption' ;

GO

EXEC msdb.dbo.sp_add_alert @name = N'823 - Hard I/O Corruption',

    @message_id = 823, @severity = 0, @enabled = 1,

    @delay_between_responses = 0, @include_event_description_in = 5,

    @notification_message = N'This is where SQL Server has asked the OS to read the page but it just cant',

    @category_name = N'[Uncategorized]',

    @job_id = N'00000000-0000-0000-0000-000000000000' ;

GO

-- Add Notification

EXEC msdb.dbo.sp_add_notification @alert_name = N'823 - Hard I/O Corruption',

    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;

GO

-- Alert [824 - Soft I/O Corruption]

IF EXISTS ( SELECT  name

            FROM    msdb.dbo.sysalerts

            WHERE   name = N'824 - Soft I/O Corruption' )

    EXEC msdb.dbo.sp_delete_alert @name = N'824 - Soft I/O Corruption' ;

GO

EXEC msdb.dbo.sp_add_alert @name = N'824 - Soft I/O Corruption',

    @message_id = 824, @severity = 0, @enabled = 1,

    @delay_between_responses = 0, @include_event_description_in = 5,

    @notification_message = N'This is where the OS could read the page but SQL Server decided that the page was corrupt - for example with a page checksum failure',

    @category_name = N'[Uncategorized]',

    @job_id = N'00000000-0000-0000-0000-000000000000' ;

GO

-- Add Notification

EXEC msdb.dbo.sp_add_notification @alert_name = N'824 - Soft I/O Corruption',

    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;

GO

-- Alert Corruption: Read/Retry 825

IF EXISTS ( SELECT  name

            FROM    msdb.dbo.sysalerts

            WHERE   name = N'Corruption: Read/Retry 825' )

    EXEC msdb.dbo.sp_delete_alert @name = N'Corruption: Read/Retry 825' ;

GO

EXEC msdb.dbo.sp_add_alert @name = N'Corruption: Read/Retry 825',

    @message_id = 825, @severity = 0, @enabled = 1,

    @delay_between_responses = 600, @include_event_description_in = 5,

    @notification_message = N'This is where either an 823 or 824 occured, SQL server retried the IO automatically and it succeeded. This error is written to the errorlog only - you need to be aware of these as they''re a sign of your IO subsystem going awry. There''s no way to turn off read-retry and force SQL Server to ''fail-fast'' - whether this behavior is a good or bad thing can be argued both ways - personally I don''t like it',

    @category_name = N'[Uncategorized]',

    @job_id = N'00000000-0000-0000-0000-000000000000' ;

GO

-- Add Notification

EXEC msdb.dbo.sp_add_notification @alert_name = N'Corruption: Read/Retry 825',

    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;

GO

-- Alert Error - 9100 (Index Corruption)

IF EXISTS ( SELECT  name

            FROM    msdb.dbo.sysalerts

            WHERE   name = N'Error - 9100 (Index Corruption)' )

    EXEC msdb.dbo.sp_delete_alert @name = N'Error - 9100 (Index Corruption)' ;

GO

EXEC msdb.dbo.sp_add_alert @name = N'Error - 9100 (Index Corruption)',

    @message_id = 9100, @severity = 0, @enabled = 1,

    @delay_between_responses = 180, @include_event_description_in = 7,

    @category_name = N'[Uncategorized]',

    @job_id = N'00000000-0000-0000-0000-000000000000' ;

GO

-- Add Notification

EXEC msdb.dbo.sp_add_notification @alert_name = N'Error - 9100 (Index Corruption)',

    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;

GO

Enjoy! 

Chris

Server Name and SQL Server Name

I have, in the past had the need to rename a server, either because the server has been named incorrectly or because it has been created as a clone from an image.  There is a documented process which I have used in the past to rename SQL Server once the server itself has been renamed.  The script which I use is below;

-- Get current servername

SELECT @@SERVERNAME;

-- Drop current servername

sp_dropserver 'ServerB\INST01';

GO

-- Add new servername

sp_addserver 'ServerA\INST01', LOCAL;

GO

-- Restart SQL Server services

-- Check the change

SELECT @@SERVERNAME;

NOTE - There are the usual precautions to take here, never do this on a production system, test the solution first and carry out regression on any applications which connect to SQL Server.

I have used this process with default instances on SQL Server 2005 and 2008 without any issues, this is mainly because the servers I have renamed where development / UAT / production servers that were still being built.  I have not however had to do this for a named instance.

Today I came across the need to again use this process but this time on a named instance and it came to light in a different way.  I was subscribing to a transactional publication on a server from ServerA\INST01 when I got the below error;

At first I thought it was because of the named instance so I created an alias of ServerA pointing to ServerA\INST01 but again got the same error.  Looking at the error more closely the server name was in fact ServerB\INST01.  I checked the server name in server manager and it was ServerA, I checked VMWare and the server name was ServerA???  Next I connected to ServerA\INST01 using SSMS and ran the below;

-- Get current servername

SELECT @@SERVERNAME;

The result was ServerB\INST01.

Following the process above allowed me to rename SQL Server and create my subscription, problem solved.

Enjoy!

Chris

Database uplevel Restores

Today I've been reading Paul Randal's collection of Blogs from a series called A SQL Server DBA myth a day. If you haven't yet read it I recommend it there are a couple of misconceptions in there that I have fallen foul of in the past.  You can also find a link to the SQL Skills site in the SQL Resources section of my Blog.

Perhaps the most obvious misconception (obvious looking back and thinking about it) is listed by Paul as the below;

24x) you can restore a backup to any uplevel version of SQL Server

No. You can only restore a database from two versions back (i.e. you cannot directly restore
a SQL Server 7.0 database to SQL Server 2008).

This isn't something that I knew, I mean sure I've restored / upgraded plenty of databases in my time but never really given the number of uplevel versions a second thought. I imagine this is more a matter of luck rather than judgement on my part.

I don't have any SQL Server 7 databases to support any more but still have the odd SQL Server 2000 database. Going off this I shouldn't be able to restore a SQL Server 2000 database to a SQL Server 2008 R2 server right? I will give this a try and let you all know.

Chris

SQL Server Error Logs

I read a blog post this morning on SQLServerCentral by Tim Radney about SQL Server Error Logs.  Tim talks about increasing the number of error logs in SQL Server.  One thing that came to mind to take this one step further and is just as simple to do is to ‘Cycle’ the error log. 

On all of the SQL servers I manage I have a job (the definition for which is below) that cycles the error log at 00:00 daily. 

USE [msdb]

GO

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'(00:00) Maintenance - CycleErrorLogs')

EXEC msdb.dbo.sp_delete_job @job_name= N'(00:00) Maintenance - CycleErrorLogs', @delete_unused_schedule=1

GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'(00:00) Maintenance - CycleErrorLogs',

            @enabled=1,

            @notify_level_eventlog=2,

            @notify_level_email=0,

            @notify_level_netsend=0,

            @notify_level_page=0,

            @delete_level=0,

            @description=N'No description available.',

            @category_name=N'[Uncategorized (Local)]',

            @owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'CycleLogs',

            @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=1,

            @os_run_priority=0, @subsystem=N'TSQL',

            @command=N'EXEC sp_cycle_errorlog;

GO',

            @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'Daily',

            @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=20060922,

            @active_end_date=99991231,

            @active_start_time=0,

            @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

It allows me to easily view just one day’s worth of information as and when required.  This is something I have done for years now since I was once unable to view SSMS 2005 because of the sheer size and number of records in it.

Which takes me onto another quick tip and one which I picked up from an old colleague and good friend Chris Taylor http://www.chrisjarrintaylor.co.uk with regard to job names.  The eagle eyed amongst you will have noticed this already but I include the time the job runs in the job name.  I find this incredibly useful as it gives you the ability to see in SSMS when the job runs without viewing the job properties.

In this case I can see that the cycle error log job (00:00) Maintenance – CycleErrorLogs runs at midnight.  This can be easily adapted to (Every 3 Hours) (01:00 Sat) etc. etc. It has helped me and others I work with and have worked with in the past, give it a go and let me know what you think!

Enjoy

Chris

Dropping a Database

I came across an issue on a server this morning where a drive had run out of free space.  Now given I still haven't got a full list of servers which I should be managing I'm not going to kick myself over this.


The issue came to light by the fact the previous DBA had set-up an email notification to an operator on a job failure.  Anyway I started to investigate the issue and found that a maintenence job had failed to create a backup file throwing the infamous "Operating system error 112(error not found)" error.  Now in my experience this error has, when investigated further led to a drive being out of space.  Low and behold this was indeed the case!


Now the server had two databases which where offline one called Surfcontrol_webfilter (an internet access control product) and one called solarwinds (a network / server monitoring product) which in total where about 40GB in size.  I spoke to the IT Operations guys who owned these products and both explained these where the repositories for old installations of the products which we did indeed have backups on tape of the last SQL Server backup and that they where no longer needed.


To reclaim some space and ensure the availability of the ONLINE databases I dropped the two unused offline databases and checked the drives free space using;


EXEC master.dbo.xp_fixeddrives;


To my initial surprise the drive still only had 20MB of free space?  Then the penny dropped and a quick check, see the below link;

http://msdn.microsoft.com/en-us/library/ms178613.aspx

confirmed that;

"Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer."

So I logged onto the server and deleted the database files in question, another run of;

EXEC master.dbo.xp_fixeddrives;

confirmed that the space had been reclaimed and I now had 40GB of free space on the server.

OK not something that I learned as such, just something I had overlooked and needed confirmation of!

Chris