Last Backup Occurred

This script will show when the last backup occurred as well as the backup type for each database. This covers all the backup types available and pivots the output into a simple and easily readable format.
It will state the reason; if any, for log reuse wait and the database recovery model in use.
Depending on your requirements you may also wish to alter the script to exclude copy only backups. This can be done by adding the following:

SELECT [database_name],[backup_finish_date],[type]
FROM msdb..backupset
WHERE [is_copy_only] <> 1

--http://jongurgul.com/blog/last-backup-occurred/
SELECT
 QUOTENAME(d.[name]) [DatabaseName]
,SUSER_SNAME(d.[owner_sid]) [DatabaseOwner]
,d.[compatibility_level] [Compatibility]
,d.[collation_name] [CollationName]
,d.[is_read_only] [IsReadOnly]
,d.[is_auto_close_on] [IsAutoClose]
,d.[is_auto_shrink_on] [IsAutoShrink]
,d.[recovery_model_desc] [RecoveryModel]
,d.[page_verify_option_desc] [PageVerify]
,d.[state_desc] [State]
,d.[log_reuse_wait_desc] [LogReuse]
,pivbus.[D] [Database]
,pivbus.[I] [DifferentialDatabase]
,pivbus.[L] [Log]
,pivbus.[F] [FileOrFilegroup]
,pivbus.[G] [DifferentialFile]
,pivbus.[P] [Partial]
,pivbus.[Q] [DifferentialPartial]
FROM sys.databases d
LEFT OUTER JOIN
(
SELECT
piv.[database_name],[D],[I],[L],[F],[G],[P],[Q]
FROM
(
SELECT [database_name],[backup_finish_date],[type]
FROM msdb..backupset
) bus PIVOT
(MAX([backup_finish_date]) FOR [type] IN ([D],[I],[L],[F],[G],[P],[Q])) piv
) pivbus ON d.[name] = pivbus.[database_name]

Get-VolumeFreeSpace

Here is an example function to return free space remaining on a volume with associated details. Updated 27/04/2012 to fix mount points.

Usage Examples:
Get-VolumeFreeSpace|ft
Get-VolumeFreeSpace|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft
Get-VolumeFreeSpace|Where-Object {!$_.SystemVolume}|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft
Get-VolumeFreeSpace “ComputerA”,”ComputerB” |ft

#http://jongurgul.com/blog/get-volumefreespace/
Function Get-VolumeFreeSpace{ 
    Param([String[]]$Computers) 
    If (!$Computers) {$Computers = $ENV:ComputerName} 
    $Base = New-Object PSObject; 
    $Base | Add-Member Noteproperty ComputerName -Value $Null; 
    $Base | Add-Member Noteproperty DeviceID -Value $Null; 
    $Base | Add-Member Noteproperty SystemVolume -Value $Null; 
    $Base | Add-Member Noteproperty DriveType -Value $Null; 
    $Base | Add-Member Noteproperty Name -Value $Null; 
    $Base | Add-Member Noteproperty MountPoint -Value $Null; 
    $Base | Add-Member Noteproperty FreeSpaceGiB -Value $Null; 
    $Results =  New-Object System.Collections.Generic.List[System.Object]; 
 
    ForEach ($Computer in $Computers){ 
        $Volume = Get-WmiObject -Class "Win32_Volume" -ComputerName $Computer; 
        $MountPoint = Get-WmiObject -Class "Win32_MountPoint" -ComputerName $Computer | Select @{Name="DeviceID";Expression={$_.Volume.ToString().Substring($_.Volume.ToString().IndexOf("`"")).Replace("`"","").Replace("\\","\")}},@{Name="MountPoint";Expression={$_.Directory.ToString().Substring($_.Volume.ToString().IndexOf("`"")).Replace("`"","").Replace("\\","\")}};   
        [String[]]$Mounts = $MountPoint|%{$_.DeviceID} 
        $Volume |%{ 
        $Entry = $Base | Select-Object * 
        $Entry.ComputerName = $Computer; 
        $Entry.DeviceID =  $_.DeviceID; 
        $Entry.SystemVolume =  $_.SystemVolume; 
        $Entry.DriveType = $_.DriveType; 
        $Entry.Name = $_.Name; 
        $Entry.FreeSpaceGiB = [Math]::Round($_.FreeSpace/1GB,3); 
        $DeviceID = $_.DeviceID;         
        $MountPoint| Where-Object{$DeviceID -contains $_.DeviceID}|%{
        $Local = $Entry | Select-Object *
        $Local.MountPoint = $_.MountPoint;
        [Void]$Results.Add($Local);
        }; 
        $_|Where-Object{$Mounts -notcontains $_.DeviceID}|%{[Void]$Results.Add($Entry);}; 
        } 
    }     
    $Results 
}

Dell Service Tag

 

The Dell soap API now returns a HTTP Error 503. The service is unavailable so the code listed below no longer functions.

Dell provide an alternative method to retrieve the information via a REST API. Please search for Dell Support Services APIs for more details.

A simple call can be made via the following link: (Note that you will need to register with Dell to get an apikey to use this service.)

https://api.dell.com/support/v2/assetinfo/header/tags.xml?svctags={svctags}&apikey={apikey}

Here is an example of how to call the Dell Service Tag GetAssetInformation web service so that you can retrieve information about your server. It simply returns the results in an xml format, however if anyone has any ideas or requests please feel free to ask.

updated:
http://xserv.dell.com/jigsawwebservices/AssetService.asmx?WSDL changed to http://xserv.dell.com/services/AssetService.asmx?WSDL

Usage Examples:
Get-DellAssetInformation
Get-DellAssetInformation “MyTag”



Function Get-DellAssetInformation{
Param([String]$ServiceTag = $(Get-WmiObject -Class "Win32_Bios").SerialNumber);
	Try{
	$AssetService = New-WebServiceProxy -Uri "http://xserv.dell.com/services/AssetService.asmx?WSDL";
	$ApplicationName = "AssetService";
	$Guid = [Guid]::NewGuid();
	$Asset = $AssetService.GetAssetInformation($Guid,$ApplicationName,$ServiceTag);
	$Writer = New-Object "System.IO.StringWriter";
	$XmlSerializer = New-Object System.Xml.Serialization.XmlSerializer($Asset.GetType());
	$XmlSerializer.Serialize($Writer,$Asset);
	[String]$Result = $Writer.ToString();
	$Writer.Flush();
	$Writer.Close();
	Return $Result;
	}
	Catch{
	Write-Host $($_.Exception.Message);	
	}
}

 

 

First day of the week

Given a particular date how do you determine the start date of that week? But more importantly what is the start of the week?

The first column returned in the script below will return the start of the week as determined by the @@DATEFIRST setting. Using a default setup with the connections made using us_english the @@DATEFIRST value is 7, which means that the start of week is Sunday.
If the language was set to Italian the value would be 1 which is Monday. Alternatively this setting can be changed using SET DATEFIRST 1, which would give the same result.

A better solution is to make sure that the query is not altered by these settings and instead returns the same results regardless.

--http://jongurgul.com/blog/first-day-week/
DECLARE @dt DATETIME
SET @dt = DATEADD(DAY,0,DATEDIFF(DAY,0,CURRENT_TIMESTAMP))   
SELECT @dt [dt]
,DATEADD(DAY,-DATEPART(dw,@dt)+1,@dt) [First Day Of Week (SET DATEFIRST X Dependent)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-2,@dt)))%7),@dt) [First Day Of Week (Monday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-3,@dt)))%7),@dt) [First Day Of Week (Tuesday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-4,@dt)))%7),@dt) [First Day Of Week (Wednesday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-5,@dt)))%7),@dt) [First Day Of Week (Thursday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-6,@dt)))%7),@dt) [First Day Of Week (Friday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,@dt))%7),@dt) [First Day Of Week (Saturday)]
,DATEADD(DAY,-((@@DATEFIRST+DATEPART(dw,DATEADD(DAY,-1,@dt)))%7),@dt) [First Day Of Week (Sunday)]

ttp://gallery.technet.microsoft.com/scriptcenter/First-Day-Of-The-Week-a7e1dd8e

Get-DatabaseSizes

Simple example using SMO to get database storage information. You will need SMO installed for this script to work.

Note Size relates to the file size on disk, where as space refers to the storage used or available.

Usage Examples:

Get-DatabaseSizes
Get-DatabaseSizes . | Select *
Get-DatabaseSizes . | Select DatabaseName,LogicalName,FileName,Size_MiB
Get-DatabaseSizes . | Select FileName,Size_MiB | Format-Table
Get-DatabaseSizes . | Where-Object {$_.DatabaseName -like “m*”}

#http://jongurgul.com/blog/get-databasesizes/
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
Function Get-DatabaseSizes([String] $SQLInstanceName = $Env:COMPUTERNAME)
{
 $SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLInstanceName;
 $Results =  New-Object System.Collections.Generic.List[System.Object];
 ForEach ($db in $SQLInstance.Databases)
 {
  ForEach ($fg in $db.FileGroups)
  {
  $Results += $fg.Files | Select-Object @{Name="DatabaseName";Expression={$db.Name}},
  @{Name="FileGroupName";Expression={$fg.Name}},
  @{Name="SpaceUsed_MiB";Expression={([Math]::Round($_.UsedSpace/1KB,3))}},
  @{Name="AvailableSpace_MiB";Expression={([Math]::Round(($_.Size-$_.UsedSpace)/1KB,3))}},
  @{Name="Size_KiB";Expression={$_.Size}},
  @{Name="Size_MiB";Expression={([Math]::Round($_.Size/1KB,3))}},
  @{Name="Size_GiB";Expression={([Math]::Round($_.Size/1MB,3))}},
  @{Name="Size_bytes";Expression={($_.Size*1024)}},
  @{Name="AutoGrowth";Expression={if($_.GrowthType-eq "Percent"){$_.Growth.ToString()+" %"}else{([Math]::Round($_.Growth/1KB,3)).ToString()+" MiB"}}},
  @{Name="MaxSize";Expression={($_.MaxSize)}},
  @{Name="FileType";Expression={"ROWS"}},
  @{Name="IsOffline";Expression={($_.IsOffline)}},
  @{Name="IsReadOnly";Expression={($_.IsReadOnly)}},
  @{Name="LogicalName";Expression={($_.Name)}},
  @{Name="FileID";Expression={($_.ID)}},
  @{Name="FileName";Expression={($_.FileName.Substring($_.FileName.LastIndexOf("\")+1))}},
  @{Name="Path";Expression={($_.FileName)}}
  }
  $Results += $db.LogFiles| Select-Object @{Name="DatabaseName";Expression={$db.Name}},
  @{Name="FileGroupName";Expression={$null}},
  @{Name="SpaceUsed_MiB";Expression={([Math]::Round($_.UsedSpace/1KB,3))}},
  @{Name="AvailableSpace_MiB";Expression={([Math]::Round(($_.Size-$_.UsedSpace)/1KB,3))}},
  @{Name="Size_KiB";Expression={$_.Size}},
  @{Name="Size_MiB";Expression={([Math]::Round($_.Size/1KB,3))}},
  @{Name="Size_GiB";Expression={([Math]::Round($_.Size/1MB,3))}},
  @{Name="Size_bytes";Expression={($_.Size*1024)}},
  @{Name="AutoGrowth";Expression={if($_.GrowthType-eq "Percent"){$_.Growth.ToString()+" %"}else{([Math]::Round($_.Growth/1KB,3)).ToString()+" MiB"}}},
  @{Name="MaxSize";Expression={($_.MaxSize)}},
  @{Name="FileType";Expression={"LOG"}},
  @{Name="IsOffline";Expression={($_.IsOffline)}},
  @{Name="IsReadOnly";Expression={($_.IsReadOnly)}},
  @{Name="LogicalName";Expression={($_.Name)}},
  @{Name="FileID";Expression={($_.ID)}},
  @{Name="FileName";Expression={($_.FileName.Substring($_.FileName.LastIndexOf("\")+1))}},
  @{Name="Path";Expression={($_.FileName)}}
 }
 RETURN $Results
}

http://gallery.technet.microsoft.com/scriptcenter/da2de04e-4f3d-4a84-8e2a-7950b562cb84

Transaction Log Usage By Session ID

A script to show the log usage on a per session basis, which can be useful when determining the potential impact of a query. It can be good to know for example if a session has generated a lot of log entries, and therefore killing that sessions would take a substantial amount of time. I bring this up because sometimes a session may have been running for a long time, but it might not have actual done anything that requires rollback.

--http://jongurgul.com/blog/transaction-log-usage-session-id/
SELECT 
 DB_NAME(tdt.[database_id]) [DatabaseName]
,d.[recovery_model_desc] [RecoveryModel]
,d.[log_reuse_wait_desc] [LogReuseWait]
,es.[original_login_name] [OriginalLoginName]
,es.[program_name] [ProgramName]
,es.[session_id] [SessionID]
,er.[blocking_session_id] [BlockingSessionId]
,er.[wait_type] [WaitType]
,er.[last_wait_type] [LastWaitType]
,er.[status] [Status]
,tat.[transaction_id] [TransactionID]
,tat.[transaction_begin_time] [TransactionBeginTime]
,tdt.[database_transaction_begin_time] [DatabaseTransactionBeginTime]
--,tst.[open_transaction_count] [OpenTransactionCount] --Not present in SQL 2005
,CASE tdt.[database_transaction_state]
 WHEN 1 THEN 'The transaction has not been initialized.'
 WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
 WHEN 4 THEN 'The transaction has generated log records.'
 WHEN 5 THEN 'The transaction has been prepared.'
 WHEN 10 THEN 'The transaction has been committed.'
 WHEN 11 THEN 'The transaction has been rolled back.'
 WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.'
 ELSE NULL --http://msdn.microsoft.com/en-us/library/ms186957.aspx
 END [DatabaseTransactionStateDesc]
,est.text [StatementText]
,tdt.[database_transaction_log_record_count] [DatabaseTransactionLogRecordCount]
,tdt.[database_transaction_log_bytes_used] [DatabaseTransactionLogBytesUsed]
,tdt.[database_transaction_log_bytes_reserved] [DatabaseTransactionLogBytesReserved]
,tdt.[database_transaction_log_bytes_used_system] [DatabaseTransactionLogBytesUsedSystem]
,tdt.[database_transaction_log_bytes_reserved_system] [DatabaseTransactionLogBytesReservedSystem]
,tdt.[database_transaction_begin_lsn] [DatabaseTransactionBeginLsn]
,tdt.[database_transaction_last_lsn] [DatabaseTransactionLastLsn]
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions tst ON es.[session_id] = tst.[session_id]
INNER JOIN sys.dm_tran_database_transactions tdt ON tst.[transaction_id] = tdt.[transaction_id]
INNER JOIN sys.dm_tran_active_transactions tat ON tat.[transaction_id] = tdt.[transaction_id]
INNER JOIN sys.databases d ON d.[database_id] = tdt.[database_id]
LEFT OUTER JOIN sys.dm_exec_requests er ON es.[session_id] = er.[session_id]
LEFT OUTER JOIN sys.dm_exec_connections ec ON ec.[session_id] = es.[session_id]
--AND ec.[most_recent_sql_handle] <> 0x
OUTER APPLY sys.dm_exec_sql_text(ec.[most_recent_sql_handle]) est
--WHERE tdt.[database_transaction_state] >= 4
ORDER BY tdt.[database_transaction_begin_lsn]

Coalesce vs ISNULL

The difference between ISNULL and COALESCE?

It is quicker to type ISNULL. Plus COALESCE always takes me a few goes to spell correctly, luckily there is intellisense and I can wait for the color of the text to change.

Well yes quicker to type, but probably going to throw the odd issue because you neglected to read the tiny writing with regards to return type. Stepping over the fact that ISNULL is not ANSI standard syntax, it more importantly uses the data type of the check value.

Compare a VARCHAR(3)  with a VARCHAR(5) and you get? ut oh…

DECLARE @a VARCHAR(3),@b VARCHAR(5)
SET @a = NULL
SET @b = '12345'

SELECT COALESCE(@a,@b) --Returns 12345
SELECT ISNULL(@a,@b) --Returns 123

ISNULL returns a truncated value because it uses the data type of the check value.

COALESCE uses data type precedence, is ANSI standard and can compare multiple values.

Which one should you use?

Extracts from Books Online:

ISNULL (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms184325.aspx

ISNULL ( check_expression , replacement_value )

Return Types
Returns the same type as check_expression.
If a literal NULL is provided as check_expression, returns the datatype of the replacement_value.
If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

COALESCE (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms190349.aspx

COALESCE ( expression [ ,…n ] )

Return Types
Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

SQL Events 2013 Part 2

Just to make everyone aware that SQL Relay is back in November this year. These are all day events, and well worth attending.

The link you will need is: http://www.sqlrelay.co.uk/register.html

The dates are:

WEEK 1
Mon 11 Nov Reading
Tue 12 Nov Southampton
Wed 13 Nov Cardiff
Thu 14 Nov Birmingham
Fri 15 Nov Hertfordshire

WEEK 2
Mon 25 Nov Newcastle
Tue 26 Nov Manchester
Wed 27 Nov Norwich
Thu 28 Nov Bristol
Fri 29 Nov London

 

Suppress messages in SQL log

Previously we had looked at how to configure alerts to log the messages that we are interested in picking up. In this previous post I very briefly showed how to add logging for messages that were not normally logged such as a permission denied.

SELECT * FROM sys.messages WHERE [message_id] = 229
The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.

sp_altermessage (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms175094.aspx

EXEC msdb.dbo.sp_altermessage 229,'WITH_LOG','true';

But just as there are extra messages you want to see there are also those that perhaps you do not. You can see what is, and is not logged by viewing sys.messages.

The example below will suppress the message.

EXEC msdb.dbo.sp_altermessage 229,'WITH_LOG','false';
GO
SELECT * FROM sys.messages WHERE [message_id] = 229;

Previously the example above suppressed message id 3197 and 3198 which are generated by Data Protection Manager (DPM). However it would appear that these can not be suppressed. The most likely reason being that the messages when generated are called WITH_LOG meaning that they always log regardless.

Finally for those who want to suppress successful backup messages you can use trace flag 3226.

Trace Flags (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms188396.aspx

By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic.

With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries.

Configure Alerts SQL Server

As a base set of alerts for monitoring SQL Server I tend to use the following out of the box categories, with the obvious exception of syntax errors. This will cover all of the really bad alerts that you never want to see, as well as those that can help troubleshoot and head off potential issues.

The only additional work needed is to confirm that you have appropriate alerting setup which in most cases will be database mail.

I have included the syntax to reset the counters if you would like to periodically zero them.

Finally included is a script which shows how to add logging for messages that are not by default raised.

--http://jongurgul.com/blog/configure-alerts-sql-server
DECLARE @OperatorName NVARCHAR(1000),@OperatorNameEmail NVARCHAR(1000),@AlertName NVARCHAR(4000),@AlertSeverity INT
SET @OperatorName = N'SQLALERTS'

IF  NOT EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'SQLALERTS')
BEGIN
EXEC msdb.dbo.sp_add_operator @name=@OperatorName,@enabled=1,@email_address=N'sqlalerts@yourdomain.com'
END

DECLARE c CURSOR LOCAL FAST_FORWARD
FOR SELECT DISTINCT [severity] FROM sys.messages WHERE [severity] IN (14,16,17,18,19,20,21,22,23,24) UNION ALL SELECT 25 ORDER BY [severity]		
OPEN c;
FETCH NEXT FROM c INTO @AlertSeverity;			
WHILE @@FETCH_STATUS = 0
BEGIN
	BEGIN TRY
		SET @AlertName = N'Severity ' + RIGHT('000'+CAST(@AlertSeverity AS VARCHAR(3)),3)
		IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @AlertName)
		BEGIN
			PRINT 'Deleting Alert: '+@AlertName
			EXEC msdb.dbo.sp_delete_alert @name=@AlertName
		END
			PRINT 'Creating Alert: '+@AlertName
			EXEC msdb.dbo.sp_add_alert @name=@AlertName, 
					@message_id=0, 
					@severity=@AlertSeverity, 
					@enabled=1, 
					@delay_between_responses=0, 
					@include_event_description_in=1, 
					@category_name=N'[Uncategorized]', 
					@job_id=N'00000000-0000-0000-0000-000000000000'
			PRINT 'Adding Alert: '+@AlertName + ' Notification ' + @OperatorName
			EXEC msdb.dbo.sp_add_notification @alert_name=@AlertName,@operator_name=@OperatorName,@notification_method = 1
	END TRY
	BEGIN CATCH
		PRINT ERROR_MESSAGE();
	END CATCH
	FETCH NEXT FROM c INTO @AlertSeverity;
END			
CLOSE c;
DEALLOCATE c;

/*
--Reset counters
DECLARE @curr_date INT,@curr_time INT,@dt DATETIME
SET @dt = CURRENT_TIMESTAMP 
SELECT @curr_date = CAST(CONVERT(CHAR, @dt,112) AS INT),@curr_time=(DATEPART(hh,@dt)*10000)+(DATEPART(mi,@dt)*100)+DATEPART(ss,@dt)
   
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 014',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 016',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 017',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 018',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 019',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 020',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 021',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 022',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 023',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 024',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
EXECUTE msdb.dbo.sp_update_alert @name = N'Severity 025',@count_reset_date=@curr_date,@count_reset_time=@curr_time,@occurrence_count=0
*/
/*
Now not all alerts are raise to the log, therefore in some cases you may wish to change this behaviour. For example permissions issue could be raise if we tell sql to log them.

SELECT * FROM sys.messages WHERE [message_id] = 229
The %ls permission was denied on the object '%.*ls', database '%.*ls', schema '%.*ls'.
sp_altermessage (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms175094.aspx
EXEC msdb.dbo.sp_altermessage 229,'WITH_LOG','true';
*/

http://gallery.technet.microsoft.com/scriptcenter/Configure-Alerts-SQL-Server-b4b2a14d