Database 101

I had an interesting phone call this morning from our professional services team.  They are carrying out what I can only call extreme testing on one of our products to see just how much data we can process in a given time window.  The issue they ran into was a dramatic reduction in available free space on one of the SQL Server data drives, which is when I was called.  The person who called had deleted some large tables and wanted to SHRINK the data file to reclaim the free space so the process would not fail.

Now my usual response here would be to take the DBA high ground, tell them NO, briefly justify myself if asked why and then hang up.  I would then proceed to curse them, their ignorance and generally have a laugh about it with my fellow DBA’s.  But I didn’t, for some reason a blog post I read (I’m sure Paul Randal tweeted about it but I can’t find it anywhere) about showing some humility towards others who are new to SQL Server or less knowledgeable than you.  I remember it plain as day but for the life of me can’t find it, basically it explains how we as DBA’s all started off with no knowledge of SQL Server and have learned from our peers, the SQL community and through our mistakes over the years.  If I hadn’t had the opportunity from my employers or support from my colleagues and the SQL community I wouldn’t be as knowledgeable as I am today (trust me there is far more I want to learn).

So what did I do?  Well I started off by saying “No” but then went on to explain “we are not going to shrink the file, what we will do is assess what it is you are trying to achieve and look at all the contributing factors before making an informed decision which best supports your task”.

The result of which was that we temporarily expand the volume to accommodate this growth which was for a “one off” test (we’ll see).  Surprisingly though what would normally have irritated me all day, put me in a foul mood and required some serious venting, turned out to be quite satisfying.  The person I spoke to after explaining in quite some depth why we don’t want to shrink the file got very excited and is arranging for me to travel to their office to spend some time with the team to educate them about SQL Server in order to help them improve the services they provide, RESULT!!!

Now this post has been far too nice so let me at least RANT about something.

Although happy that someone is excited about getting advice and support I’m still not happy that a member of the Database Team was not consulted about this testing as we could have avoided me and the storage team being interrupted and asked to drop their current work to help out.  After all proper planning prevents poor performance.

So the next time you take a call or are dealing with a request from someone which to you may be menial remember something that is second nature to you, is not second nature to everyone.  Don’t jump on the high horse, show them some support and pass on your knowledge after all knowledge is power ;)

Cheers

Chris

Venting Frustration

We as DBA’s face an ever growing list of things to do and services to support.  We work in high pressure environments where time is of the essence and there is little to no room for error.  The very nature of our roles mean we have to work unsociable hours and in the majority of cases have some sort of formal on call process for 24x7 systems.  I and certainly the vast majority of DBA’s I have worked with over the years take great pride and care in the work we do, often spending hours of additional work to make sure a solution is as bullet proof as possible.  I enjoy going beyond the call of duty because I realise in the long run the additional work will save me time, effort and reduce stress.

It is, well certainly for me, infuriating when working with other “professionals” who do not seem to share this same mind set.  Everything is reactive as opposed to proactive and they frown when asked to put in extra hours.  I can’t stress how much this gets up my nose, no one is perfect I understand that but if you are only as strong as your weakest link then surely we are doomed as a team to fail?!

It is then of course up to us as DBA’s to go even further behind enemy lines and single-handedly win the war against incompetence and downright laziness.  I’m starting to rant so I’m going to get back to the subject of the post venting frustration now I have hopefully set the scene.

Rest and Relaxation are an important part of being a DBA without which we wouldn’t be able to function, well not without copious amounts of coffee anyway.  I enjoy being able to switch off, disengage from work and SQL Server and spend time with my family.  My wife is not a technical person so the answer to “How was your day?” is different for her than it would be for a fellow techie.  Trust me on this I’ve tried, I can see her eyes glaze over as I lose her with a technical response.

I do also like being able to vent my frustration to fellow DBA’s, just as much as I enjoy them venting their frustrations to me.  Not only do I feel a sense of release (Panders I know you are smirking at that!) but the DBA in me commits certain parts of this venting process to memory automatically, to learn from so I can evolve environments based on the mistakes and misfortunes of others.

I am lucky in this respect that I have a number of DBA friends who I can vent frustration at.  I have also found blogging very therapeutic, since I started this blog I have found another source of satisfaction, which I hope continues.

The SQL Server community is strong and generally very supportive.  Get involved and you too will start to reap the rewards.

Cheers

Chris

A Script a Day

In February I will be starting a series of Blog Posts I'm calling a "A Script a Day".  Each day throughout February I will post a script which I have used in my various DBA roles and a description of what it is and how / where I have used it.  I will keep them varied so I can cover as many areas of SQL Server as possible however, if anyone has anything in particular they would like me to cover then get in touch ckwmcgowan@gmail.com and I will do my best to include them.


Keep Your Eyes Peeled! 


Chris

Determining Active Directory Group Membership

As discussed in my previous post I prefer to use Windows authentication over SQL authentication on my SQL Servers.  There are however two things that I would recommend controlling which is Active Directory Group membership and correct Active Directory design.

Let’s say you have a tiered Active directory group structure comprising of three Global Security groups Development Team 1, Development Team 2 and Development Team 3.  Development Team 1 whose members are your most senior, respected and trusted developers and as such have elevated permissions on some SQL Servers and restricted access to others which lesser groups (Development Team 2 and Development Team 3) do not.

The issues arise because you are not responsible for assigning group membership or the Active Directory design this is controlled by the IT Operations team.  As a result there is nothing stopping a manager requesting membership in Development Team 1 for a developer who should not have this level of access.  Now in this scenario the request for membership in the group was not made for access to certain SQL Servers or for elevated permissions on certain SQL Servers it was made for access let’s say to a network resource.

The two points here are;

1 – Separate Windows and SQL Server permissions.

Take the Development Team tiered Active Directory structure, these groups make granting access to resources easier but and it’s a big BUT can also be misused whether intentionally or unintentionally.  What you should have here is two sets of tiered groups one for SQL Server and one for network resources this allows you to separate the permissions and prevent unnecessary access as in the above scenario, example groups below;

SQL – Development Team 1

SQL – Development Team 2

SQL – Development Team 3

Windows – Development Team 1

Windows – Development Team 2

Windows – Development Team 3

2 – Active Directory group membership visibility

I have had many scenarios over the years where I have required the need to obtain information from Active Directory as part of a routine and have used both CSVDE and LDAP, but there is a much easier method to view Active Directory group membership.  Now I can’t remember which site / blog I first saw this procedure on so I apologise.  The procedure in question is xp_logininfo which returns information about Windows users and Windows groups.

One use for this would be to create a routine which runs every x hours or days and reports changes since the last run.  This would give a more granular audit of who has access to your SQL Servers.  I will look at creating such a routine when time allows and post here.  In the meantime if anyone creates such a routine and would like to share it by all means get in touch and I will post it ckwmcgowan@gmail.com.

Cheers

Chris

Security - SQL vs Windows

Security is a very important part of every DBA’s role.   If you are not clued up on security best practices or don’t secure your SQL Servers, you should be concerned as it could end up costing you your job!

I have worked in organisations where the painstaking task of managing SQL Server security was my responsibility and others where thankfully we have made use of Windows Authentication.  This in my opinion is a much better approach as it reduces the administrative overhead weather the task be carried out by an IT Operations team or in most cases by you as DBA’s.

There are many factors to consider when deciding whether to use SQL or Windows authentication and in some cases even both, the biggest win for me however is the administrative overhead as I mentioned before and I will show why below;

Take the following example of a small development team comprising 9 developers as below; 

DevUser1

DevUser2

DevUser3

DevUser4

DevUser5

DevUser6

DevUser7

DevUser8

DevUser9

The developers are split into 3 teams as below;

Development Team 1

            DevUser1

DevUser2

DevUser3

Development Team 2

DevUser4

DevUser5

DevUser6

Development Team 3

DevUser7

DevUser8

DevUser9

Development Team 1 comprises of the most experienced and trusted developers, Development Team 2 comprises of competent developers who have some experience but not as much as those in Development Team 1 and Development Team 3 comprises of new or junior developers who are still learning the ropes.

There are 3 SQL Servers in the company as below;

A Live SQL Server - LIVESQL1

A UAT SQL Server - UATSQL1

A Dev SQL Server – DEVSQL1

Each SQL Server has 3 databases as below;

DBX

DBY

DBZ

The company requires the developers to have the below access;

Development Team 1

            DEVSQL1 – membership in the db_owner fixed database role for every user database.

UATSQL1 – membership in the db_datareader and db_datawriter fixed database roles for every user database.

LIVESQL1 – membership in the db_datareader fixed database role.

Development Team 2

            DEVSQL1 – membership in the db_owner fixed database role for every user database.

UATSQL1 – membership in the db_datareader and db_datawriter fixed database roles for every user database.

LIVESQL1 – NO ACCESS

Development Team 3

DEVSQL1 – membership in the db_datareader and db_datawriter fixed database role for every user database.

UATSQL1 – NO ACCESS

LIVESQL1 – NO ACCESS

Now let’s consider what would be required for SQL Authentication;

DEVSQL1

9 Logins

27 Users

36 Role memberships

UATSQL1

            6 Logins

            18 Users

36 Role memberships

LIVESQL1

            3 Logins

            9 Users

            9 Role memberships

TOTAL

            18 Logins

            54 Users

            81 Role memberships

And for Windows Authentication;

We create a Global Security Group in Active Directory for each of the Development Teams as below;

Development Team 1

Development Team 2

Development Team 3

We then add each of the developers to their respective groups.  Once this is done we would require;

DEVSQL1

3 Logins

9 Users

12 role memberships

UATSQL1

            2 Logins

            6 Users

12 role memberships

LIVESQL1

            1 Logins

            3 Users

            6 role memberships

TOTAL

            6 Logins

            18 Users

            30 Role memberships

As you can see from these figures making use Windows Authentication means we have 65% less administration to deliver a security solution for our SQL Servers, based on this data we decide to go with Windows Authentication.  Moving forward as part of the IT new user process for developers we inform the IT Operations team that they are to add new developers to the Development Team 3 Active Directory group.  This means that they automatically have permissions on SQL server. 

Now consider the following scenario; over the next 6 months the company’s size explodes and the number of developers rises from 9 to 99.  There are no new servers or databases required.  Since we decided to go with Windows Authentication there is zero administrative overhead for us as DBA’s providing the Teams / Active Directory structure remain the same (which for the purpose of this post assume they have). 

But what if we would have gone with SQL Server authentication?  Well let’s look at the figures, 90 additional users equate to an additional….

DEVSQL1

90 Logins

270 Users

360 Role memberships

UATSQL1

            60 Logins

            180 Users

360 Role memberships

LIVESQL1

            30 Logins

            90 Users

            90 Role memberships

TOTAL

            180 Logins

            540 Users

            810 Role memberships

This is a lot of additional work which could be reduced by using scripts yes, but is still additional work.  I am currently defining standards for my current employer and will be using these statistics as one reason of many reasons why we should use Windows authentication over SQL authentication!

Cheers

Chris

Database uplevel Restores Follow On

Today's post is a follow on from one earlier this month Database uplevel Restores.  I got round to testing restoring a SQL Server 2000 backup to a SQL Server 2008 R2 server and it worked!  Details Below;

-- Restore command

RESTORE DATABASE [SQL2000Test] FROM DISK = 'D:\SQL2000TestBackup.bak'

WITH REPLACE, MOVE 'SQL2000Test_Data' TO 'D:\Test\SQL2000Test_Data.mdf',

MOVE 'SQL2000Test_Log' TO 'D:\Test\SQL2000Test_Log.ldf';

Message Output;

Processed 856 pages for database 'SQL2000Test', file 'SQL2000Test_Data' on file 1.

Processed 1 pages for database 'SQL2000Test', file 'SQL2000Test_Log' on file 1.

Converting database 'SQL2000Test' from version 539 to the current version 661.

Database 'SQL2000Test' running the upgrade step from version 539 to version 551.

Database 'SQL2000Test' running the upgrade step from version 551 to version 552.

Database 'SQL2000Test' running the upgrade step from version 552 to version 611.

Database 'SQL2000Test' running the upgrade step from version 611 to version 621.

Database 'SQL2000Test' running the upgrade step from version 621 to version 622.

Database 'SQL2000Test' running the upgrade step from version 622 to version 625.

Database 'SQL2000Test' running the upgrade step from version 625 to version 626.

Database 'SQL2000Test' running the upgrade step from version 626 to version 627.

Database 'SQL2000Test' running the upgrade step from version 627 to version 628.

Database 'SQL2000Test' running the upgrade step from version 628 to version 629.

Database 'SQL2000Test' running the upgrade step from version 629 to version 630.

Database 'SQL2000Test' running the upgrade step from version 630 to version 631.

Database 'SQL2000Test' running the upgrade step from version 631 to version 632.

Database 'SQL2000Test' running the upgrade step from version 632 to version 633.

Database 'SQL2000Test' running the upgrade step from version 633 to version 634.

Database 'SQL2000Test' running the upgrade step from version 634 to version 635.

Database 'SQL2000Test' running the upgrade step from version 635 to version 636.

Database 'SQL2000Test' running the upgrade step from version 636 to version 637.

Database 'SQL2000Test' running the upgrade step from version 637 to version 638.

Database 'SQL2000Test' running the upgrade step from version 638 to version 639.

Database 'SQL2000Test' running the upgrade step from version 639 to version 640.

Database 'SQL2000Test' running the upgrade step from version 640 to version 641.

Database 'SQL2000Test' running the upgrade step from version 641 to version 642.

Database 'SQL2000Test' running the upgrade step from version 642 to version 643.

Database 'SQL2000Test' running the upgrade step from version 643 to version 644.

Database 'SQL2000Test' running the upgrade step from version 644 to version 645.

Database 'SQL2000Test' running the upgrade step from version 645 to version 646.

Database 'SQL2000Test' running the upgrade step from version 646 to version 647.

Database 'SQL2000Test' running the upgrade step from version 647 to version 648.

Database 'SQL2000Test' running the upgrade step from version 648 to version 649.

Database 'SQL2000Test' running the upgrade step from version 649 to version 650.

Database 'SQL2000Test' running the upgrade step from version 650 to version 651.

Database 'SQL2000Test' running the upgrade step from version 651 to version 652.

Database 'SQL2000Test' running the upgrade step from version 652 to version 653.

Database 'SQL2000Test' running the upgrade step from version 653 to version 654.

Database 'SQL2000Test' running the upgrade step from version 654 to version 655.

Database 'SQL2000Test' running the upgrade step from version 655 to version 660.

Database 'SQL2000Test' running the upgrade step from version 660 to version 661.

RESTORE DATABASE successfully processed 857 pages in 0.262 seconds (25.528 MB/sec).

Cheers

Chris

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