First of all lets use sp_helpfile on tempdb database to get the list of all the tempdb files there are.

-- Shows all tempdb files
USE tempdb
GO
EXEC sp_helpfile
GO

Once we know all the tempdb files we can use the following query to move the files to new location. Remember tempdb gets created every time SQL Server is restarted so you won’t see tempdb files in new location until SQL Server has been restarted.

-- Move to C:\ 
USE master
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'C:\tempdb2005.mdf')
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'C:\tempdb2005.ldf')
GO
ALTER DATABASE temp2 
MODIFY FILE (NAME = temp2, FILENAME = 'C:\tempdb2005.ndf')
GO

The following script can be used to enable/disable Change Tracking on a particular database.

-- Enable
ALTER DATABASE database_name 
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

-- Disable 
ALTER DATABASE database_name
SET CHANGE_TRACKING = OFF 

To enable/disable change tracking on individual table(s)

-- Enable
ALTER TABLE table_name
 Enable Change_tracking;

-- Disable
ALTER TABLE table_name
 Disable Change_tracking;

If you wish to enable change tracking on all tables within a database in one go, you can use the following dynamic query to generate “enable change tracking” script.

Use database_name;
go
EXEC sp_MSforeachtable
 'PRINT ''ALTER TABLE ? ENABLE Change_tracking;''';

To disable change tracking on tables within a database in one go, use the following query

Use database;
GO

DECLARE @SQL NVARCHAR(MAX) = '';
SELECT @SQL = @SQL + 'ALTER TABLE ' + s.name + '.' + t.name + 
             ' Disable Change_tracking;' + CHAR(10)
FROM sys.change_tracking_tables ct JOIN sys.tables t 
 ON ct.object_id= t.object_id JOIN sys.schemas s
 ON t.schema_id= s.schema_id;
PRINT @SQL;
-- EXEC sp_executesql @SQL;

Running the script below will show you the last access date and time of all databases since the day sql server was rebooted

-- Get Last Restart time
SELECT 
crdate as 'Last Rebooted On'
FROM
sysdatabases 
WHERE name = 'tempdb'
go

-- Get last database access time (Null - no access since last reboot)
SELECT name, last_access =(SELECT X1= max(LA.xx)
FROM ( SELECT xx =
MAX(last_user_seek)
WHERE MAX(last_user_seek)is not null
UNION ALL
SELECT xx = max(last_user_scan)
where max(last_user_scan)is not null
UNION ALL
SELECT xx = max(last_user_lookup)
WHERE MAX(last_user_lookup) is not null
UNION ALL
SELECT xx =max(last_user_update)
WHERE MAX(last_user_update) is not null) LA)
FROM master.dbo.sysdatabases sd 
LEFT OUTER JOIN sys.dm_db_index_usage_stats s 
on sd.dbid= s.database_id 
GROUP BY sd.name
ORDER BY name

Using this script you will be able to see various details related to databases like name, state, physical file paths, collation, state etc.

SELECT 
d.name, 
m.type_desc, 
d.compatibility_level, 
d.collation_name, 
d.user_access_desc, 
d.state_desc, 
d.recovery_model_desc, 
m.physical_name, 
(m.size * 8 / 1024.0) AS FileSizeMB, 
(m.size * 8 / 1024.0 /1024.0) AS FileSizeGB,
m.growth
FROM sys.databases d inner join sys.master_files m
ON d.database_id = m.database_id
--where d.state_desc = 'OFFLINE'   
ORDER BY d.state_desc

Here is a query to generate a script to detach all user databases on your SQL Server.

Once you have run the query, copy the output and run it in SSMS to detach all your user databases. This can be really handy during migration activity when you have lot of databases to detach and move around.

DECLARE @dbName varchar(255); -- To store database name 
DECLARE DBCURSOR CURSOR FOR
	SELECT name 
	FROM sys.databases 
	WHERE len(owner_sid)>1; -- All user databases

OPEN DBCURSOR
FETCH Next from DBCURSOR INTO @dbName
WHILE @@FETCH_STATUS = 0 
BEGIN
	PRINT 'EXEC sp_detach_db ' + @dbName + CHAR(10) -- CHAR(10) for newline 
	+ 'GO' + CHAR(10) 
	+ 'Print ''Detach of ' + @dbName + ' database completed successfully''' 
	+ CHAR(10) + 'GO'
	FETCH NEXT FROM DBCURSOR INTO @dbName
END

CLOSE DBCURSOR    
DEALLOCATE DBCURSOR

Sample output:

I have two databases TestDB and Sample. Running the above query gives the following output.

EXEC sp_detach_db TestDB
GO
Print 'Detach of TestDB database completed successfully'
GO
EXEC sp_detach_db Sample
GO
Print 'Detach of Sample database completed successfully'
GO

Some requirements when creating a primary key constraint:
1. A table can have only one primary key constraint. Creating more than one primary key on a table will fail.
2. The column or columns that you want to be primary key cannot allow NULL values.
3. If there is any data in table, the column which you want to make primary key must have unique values. If there is any duplicates the ALTER table statement will fail.

You can add a primary key to an existing table by using the ALTER TABLE command as shown below. Replace <table_name> and <primary_key_column> with the actual table and column name.

ALTER TABLE <table_name>
    ADD CONSTRAINT PK_<table_name> PRIMARY KEY(<primary_key_column>);

When you create primary key, SQL Server enforces the constraint by creating a unique constraint behind the scene. It also creates a clustered index on the column and the values of this column are used as the keys of the index.

Run the following query to get a list of primary key constraints in a database

SELECT *
FROM sys.key_constraints
WHERE type = 'PK';

Also you can find the unique index that SQL Server uses to enforce a primary key constraint by querying sys.indexes.

It is absolutely important for anyone working with Structured Query Language (SQL) in Microsoft SQL Server to understand how a query gets logically interpreted by SQL Server engine. The most fundamental thing to understand is that the order in which the query is written/typed is different than the order in which it will get logically interpreted. For instance a query is usually typed in the following order:

SELECT DISTINCT TOP <TOP_spec>  <select_list>
FROM <table_operators: JOIN, APPLY, PIVOT, UNPIVOT>
WHERE <predicate>
GROUP BY <definition_of_grouping_sets>
HAVING <predicate>
ORDER BY <order_by_list>
OFFSET <offset_spec> FETCH <fetch_spec>

However, the same query is interpreted in a different order by SQL Server.

FROM <table_operators: JOIN, APPLY, PIVOT, UNPIVOT>
WHERE <predicate>
GROUP BY <definition_of_grouping_sets>
HAVING <predicate>
SELECT <select_list>
ORDER BY <order_by_list>
OFFSET <offset_spec> FETCH <fetch_spec> TOP

Notice the first clause that gets interpreted by SQL Server is the FROM clause and not the SELECT clause. The SELECT clause comes fifth in the order.

Let us see how understanding the order of interpretation can be useful when writing t-sql queries. Below is a query to get details of employees whose LoginID is ‘adventure-works\frank3’.

SELECT top 5 LoginID as 'ID', JobTitle, BirthDate, Gender
FROM [HumanResources].[Employee]
WHERE ID = 'adventure-works\frank3'
ORDER BY ID

If you look at the query it seems perfectly alright with no syntax errors, but when you execute it throws the following error:

Msg 207, Level 16, State 1, Line 3 Invalid column name ‘ID’.

Error: Invalid Column name ‘ID’. Can you guess why? The answer is SELECT comes after WHERE clause so it does not recognize a column with the name ID (An alias to LoginID).

And notice there is no error for the ORDER BY clause because ORDER BY clause comes after SELECT so it knows column name ID is referring to LoginID column.

So we can see understanding the order in which the query is interpreted is very important. I hope it was helpful.

DDL (Data Definition Language): Used to define data structure in SQL Server. DDL commands are:
CREATE
ALTER
DROP

DML (Data Manipulation Language): Used to retrieve and work with data in SQL Server. DML Commands are:
SELECT
INSERT
DELETE
UPDATE
MERGE
TRUNCATE

DCL (Data Control Language): Deals with permissions and includes commands like:
GRANT
REVOKE

TCL (Transaction Control Language): Used to manage transaction in a database like:
COMMIT
ROLLBACK
SAVEPOINT

It is not a good practice to keep Autoshrink on because it will cause indexes to become fragmented. If you want to reclaim free space then a better approach is to manually shrink the files and then rebuild the indexes.

By default AutoShrink feature is turned off in SQL Server

Anyone using SQL Server Express editions would have noticed that there is no way to schedule either jobs or maintenance plans because the SQL Server Agent is by default not included in these editions.

But what if those using Express editions would like to schedule and automate backups. Is there a way?

Yes, please read this article by Microsoft

Here is a script to configure Deadlock Alert using t-sql

USE msdb
GO

DECLARE @perfcond NVARCHAR (100);
DECLARE @sqlversion TINYINT ;

-- get the major version of sql running
SELECT  @sqlversion = ca2.Ver
FROM    (SELECT CONVERT(VARCHAR (20),
                    SERVERPROPERTY('ProductVersion' )) AS Ver) dt1
        CROSS APPLY (SELECT CHARINDEX( '.', dt1 .Ver) AS Pos ) ca1
        CROSS APPLY (SELECT SUBSTRING( dt1.Ver , 1, ca1.Pos -1) AS Ver ) ca2;

-- handle the performance condition depending on the version of sql running
-- and whether this is a named instance or a default instance.

SELECT  @perfcond =
        CASE WHEN @sqlversion >= 11 THEN ''
        ELSE ISNULL (N'MSSQL$' +
                CONVERT(sysname , SERVERPROPERTY ('InstanceName')), N'SQLServer') + N':'
        END +
        N'Locks|Number of Deadlocks/sec|_Total|>|0' ;

EXEC msdb. dbo.sp_add_alert
    @name= N'Deadlock Alert',
    @message_id =0,
    @severity =0,
    @enabled =1,
    @delay_between_responses =0,
    @include_event_description_in =0,
    @category_name =N'[Uncategorized]',
    @performance_condition =@perfcond,
    --@job_name=N'Job to run when a deadlock happens, if applicable'
    -- or
    @job_id =N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb. dbo.sp_add_notification
    @alert_name = N'Deadlock Alert',
    @notification_method = 1, --email
    @operator_name = N'<operatorName>' ; -- name of the operator to notify
GO

Creating operators using SSMS could be tiresome at times, especially if you have configure many of them. So here is a simple script to create operator using t-sql.

USE [msdb]
GO
EXEC msdb. dbo.sp_add_operator @name= N'<operatorName>',
              @enabled =1,
              @pager_days =0,
              @email_address =N'<yourEmailAddress>'
GO

Here is a dynamic SQL query, which will generate a script that you can use to create new alert(s) for each error number you specify.

SET NOCOUNT ON

-- Let's create a temporary table to store your target error numbers
DECLARE @errorCodes TABLE ( ErrorNumber VARCHAR (6) )
INSERT INTO @errorCodes
 VALUES ( '35273'),( '35274' ),( '35275'),( '35254'),('35279' ),
('35262' ),( '35276' )


PRINT 'USE [msdb]'
PRINT 'GO'
PRINT '/* -------------------------------------------------------- */ '

-- We will create a cursor to go over each error number
DECLARE  @ErrNum VARCHAR (6)

DECLARE  cursor_Errors CURSOR LOCAL FAST_FORWARD
FOR SELECT ErrorNumber FROM @errorCodes

OPEN  cursor_Errors

FETCH NEXT FROM cursor_Errors INTO @ErrNum
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT
  'EXEC msdb.dbo.sp_add_alert @name=N''AlwaysOn Error - ' 
  + @ErrNum + ''',
  @message_id=' + @ErrNum + ',
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N''00000000-0000-0000-0000-000000000000''
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N''AlwaysOn Error - ' 
	+ @ErrNum + ''',
    @operator_name=N''<operatorName>'', @notification_method = 1
  GO '
 PRINT '/* -------------------------------------------------------- */ '
 FETCH NEXT FROM cursor_Errors INTO @ErrNum
END

CLOSE  cursor_Errors
DEALLOCATE cursor_Errors

Below is the result of running the above script. You will need to replace the <operatorName> with real operator configured in your SQL Server

USE [msdb]
GO
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35273',
  @message_id=35273,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35273',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35274',
  @message_id=35274,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35274',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35275',
  @message_id=35275,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35275',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35254',
  @message_id=35254,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35254',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35279',
  @message_id=35279,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35279',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35262',
  @message_id=35262,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35262',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 
EXEC msdb.dbo.sp_add_alert @name=N'AlwaysOn Error - 35276',
  @message_id=35276,
  @severity=0,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
  GO
  EXEC msdb.dbo.sp_add_notification @alert_name=N'AlwaysOn Error - 35276',
    @operator_name=N'<operatorName>', @notification_method = 1
  GO 
/* -------------------------------------------------------- */ 

Wouldn’t it be cool if we could display query results in the form of a beautiful looking animated charts like below, using simple t-sql and jquery

Here is the script. Feel free to modify as per your requirements. I am using jquery charts from canvasjs.com

IF OBJECT_ID('tempdb..#DBSize') IS NOT NULL DROP TABLE #DBSize;
CREATE TABLE #DBSize
(
	DBName varchar(50) NULL,
	DBSize varchar(50),
	Type_desc varchar(10)
)
INSERT INTO #DBSize
	SELECT name, size/1024 AS size_mb , Type_desc  FROM sys.master_files 

DECLARE @DataChart VARCHAR(MAX) = ''
SELECT @DataChart = @DataChart 
+ '{label: "' + DBName +'", y: '+ DBSize+ '},' 
FROM #DBSize  WHERE Type_desc = 'ROWS' 

-- Remove the last , from string @DataChart
SELECT @DataChart = LEFT(@DataChart,DATALENGTH(@DataChart)-1) 

DECLARE @LogChart VARCHAR(MAX) = ''
SELECT @LogChart = @LogChart + '{label: "' + DBName +'", y: '+ DBSize+ '},' 
FROM #DBSize WHERE Type_desc = 'LOG'  

-- Remove the last , FROM @LogChart
SELECT @LogChart = LEFT(@LogChart,DATALENGTH(@LogChart)-1) 

DECLARE @DatabaseSizeChart VARCHAR(MAX) = '
		var chart1 = new CanvasJS.Chart("chartContainer1",
		{
			theme: "theme1",
			animationEnabled: true,
			title:{
				text: "Database Data and Logfile Size"
			},
			/*toolTip: {
				shared: true
			},		*/	
			axisY: {
				title: "Size in MB"
			},
          	axisX: {
				title: "Databases"
			},
			data: [ 
			{
				type: "column",	/*see canvasjs.com for chart types*/
				name: "Data",
				legendText: "Data",
				showInLegend: true, 
				dataPoints:[' + @DataChart+ ']
			},
			{
				type: "column",	
				name: "Log",
				legendText: "Log",
				axisYType: "secondary",
				showInLegend: true,
				dataPoints:['+@LogChart+']
			}
			
			],
			legend:{
			cursor:"pointer",
			itemclick: function(e){
				if (typeof(e.dataSeries.visible) === "undefined" 
				|| e.dataSeries.visible) {
              	e.dataSeries.visible = false;
				}
				else {
				e.dataSeries.visible = true;
				}
            	chart.render();
			}
			},
		});

chart1.render();'

-- To display disk drive free space 
IF OBJECT_ID('tempdb..#drive') IS NOT NULL 
	DROP TABLE #drive;
CREATE TABLE #drive
(
	drive varchar(2) NULL,
	Free_mb varchar(50)
)
INSERT INTO #drive (drive, free_mb) 
	EXEC MASTER..xp_fixeddrives; 

DECLARE @EmailBody VARCHAR(MAX)
DECLARE @tag VARCHAR(MAX) = ''
SELECT @tag = @tag + '{label: "' + drive+'", y: '+ Free_mb+ '},' 
FROM #drive   
-- remove the last letter FROM @tag
SELECT @tag = LEFT(@tag,DATALENGTH(@tag)-1)
--SELECT @tag


DECLARE @DiskSizeChart VARCHAR(MAX) = '
var chart2 = new CanvasJS.Chart("chartContainer2",
	{
		title:{
		text: "Drive Sizes Free MB"    
		},
		animationEnabled: true,
		axisY: {
		title: "Size"
		},
		axisX: {
		title: "Drives"
		},
		legend: {
		verticalAlign: "bottom",
		horizontalAlign: "center"
		},
		theme: "theme3",
		data: [

		{        
		type: "pie",  
		dataPoints: ['+@tag+']
		}   
		]
	});
	chart2.render();'

SET @EmailBody =     
'<!DOCTYPE html>
<html><head>
<script type="text/javascript" 
	src="http://canvasjs.com/assets/script/canvasjs.min.js">
</script>
<script type="text/javascript">
window.onload = function () {
'+@DatabaseSizeChart+'
'+@DiskSizeChart+'
}
</script>
</head>
<body>
<div align=left id="chartContainer1" style="height: 300px; width: 50%;"></div>
<div align=right id="chartContainer2" style="height: 300px; width: 30%;"></div>
</body></html>
'
print @EmailBody

If you wish to automate creating html file with the generated html code then use sqlcmd or bcp tool. Here is the example of using sqlcmd

sqlcmd 
-S <sql_server_instance_name> 
-i <name_of_the_above_script.sql> 
-o coolChart.html

You should never backup your databases on the same drive where the database files resides because:

1. It eats up the drive space ultimately leaving your database at risk.
2. If something were to go wrong with the drive you will not only lose your database files, but also your backups.

In short, it’s a serious to have backups on the same drive. So don’t do it.

Use this script to see failed SQL Agent jobs

SET NOCOUNT ON ;
DECLARE @Value [varchar] (2048)
    ,@JobName [varchar] (2048 )
    ,@PreviousDate [datetime]
    ,@Year [varchar] (4 )
    ,@Month [varchar] (2 )
    ,@MonthPre [varchar] (2 )
    ,@Day [varchar] (2 )
    ,@DayPre [varchar] (2 )
    ,@FinalDate [int]
-- Declaring Table variable
DECLARE @FailedJobs TABLE ([JobName] [varchar]( 200))
-- Initialize Variables
SET @PreviousDate = DATEADD( dd, - 1, GETDATE())
SET @Year = DATEPART( yyyy, @PreviousDate )
SELECT @MonthPre = CONVERT( [varchar](2 ), DATEPART (mm, @PreviousDate))
SELECT @Month = RIGHT(CONVERT ([varchar], (@MonthPre + 1000000000)), 2)
SELECT @DayPre = CONVERT( [varchar](2 ), DATEPART (dd, @PreviousDate))
SELECT @Day = RIGHT(CONVERT ([varchar], (@DayPre + 1000000000)), 2)
SET @FinalDate = CAST( @Year + @Month + @Day AS [int])
-- Final Logic
INSERT INTO @FailedJobs
SELECT DISTINCT j.[name]
FROM [msdb]. [dbo].[sysjobhistory] h
INNER JOIN [msdb].[dbo] .[sysjobs] j
    ON h .[job_id] = j.[job_id]
INNER JOIN [msdb].[dbo] .[sysjobsteps] s
    ON j .[job_id] = s.[job_id]
        AND h. [step_id] = s .[step_id]
WHERE h. [run_status] = 0
    AND h .[run_date]  > @FinalDate
SELECT @JobName = COALESCE( @JobName + ', ', '') + '[' + [JobName] + ']'
FROM @FailedJobs
SELECT @Value = 'Failed SQL Agent job(s) found: ' + @JobName + '. '
IF @Value IS NULL
BEGIN
    SET @Value = 'None.'
END
SELECT @Value

Use this script to create SQL Server operator using t-sql

Before running the script replace the <OperatorName> and <OperatorEmail> parameter with the actual operator’s name and email that you want to create.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name= N'<OperatorName>', -- Operator name
@enabled =1,
@pager_days =0,
@email_address =N'<OperatorEmail>' -- Email address of the operator
GO

Use this script to create alerts for disk I/O errors.

Before running the script replace the <OperatorName> parameter with the actual operator that you would like to alert.

/*
PARAMETERS:
<OperatorName,sysname,Alerts> - Name of the Operator/Alias to alert.
@notification_method 1 - Bitmap of notification types/options: 1 = email, 
2 = pager, 4 = netsend
*/

USE msdb
GO

EXEC msdb. dbo.sp_add_alert @name = N'823 - Read/Write Failure',
    @message_id = 823,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name=N'823 - Read/Write Failure' ,
        @operator_name =N'<OperatorName>' ,
        @notification_method = 1;   -- 1 for email

EXEC msdb. dbo.sp_add_alert @name = N'824 - Page Error',
    @message_id = 824,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name=N'824 - Page Error' ,
        @operator_name =N'<OperatorName>' ,
        @notification_method = 1;   -- 1 for email

EXEC msdb. dbo.sp_add_alert @name = N'825 - Read-Retry Required',
    @message_id = 825,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name=N'825 - Read-Retry Required' ,
        @operator_name =N'<OperatorName>' ,
        @notification_method = 1;   -- 1 for email

Use this script to create alerts in SQL Server for severity 17-25 errors.

Before running the script replace the <OperatorName> parameter with the actual operator that you would like to alert.

/*
PARAMETERS:
<OperatorName,sysname,Alerts> - Name of the Operator/Alias to alert.
@notification_method 1 - Bitmap of notification types/options: 1 = email, 
2 = pager, 4 = netsend
*/

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 017' ,
        @message_id=0 ,
        @severity=17 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ; 
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 017' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;  -- 1 for email
GO

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 018' ,
        @message_id=0 ,
        @severity=18 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 018' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 019' ,
        @message_id=0 ,
        @severity=19 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 019' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 020' ,
        @message_id=0 ,
        @severity=20 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 020' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 021' ,
        @message_id=0 ,
        @severity=21 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 021' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 022' ,
        @message_id=0 ,
        @severity=22 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 022' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1; -- 1 for email
GO

EXEC msdb.dbo.sp_add_alert
        @name=N'Severity 023' ,
        @message_id=0 ,
        @severity=23 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 023' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_alert @name =N'Severity 024',
        @message_id=0 ,
        @severity=24 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 024' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

EXEC msdb.dbo.sp_add_alert @name =N'Severity 025',
        @message_id=0 ,
        @severity=25 ,
        @enabled=1 ,
        @delay_between_responses=60 ,
        @include_event_description_in=1 ;
GO
EXEC msdb.dbo.sp_add_notification
        @alert_name=N'Severity 025' ,
        @operator_name=N'<OperatorName,sysname,Alerts>' ,
        @notification_method = 1;
GO

Use the following query to see the IP addresses of the connection to SQL Server and since how long they have been connected.

SELECT session_id, client_net_address, connect_time 
FROM sys.dm_exec_connections;