Setting Up Alerts for All SQL Server Agent Jobs

Some times clients ask to enable alerts for the sql agent jobs, it will be difficult when we have more jobs, because we need to goto each and every job and enable the notifications.

Here is the script for enabling for the jobs,

Steps :

1. Create Mail Profile

2. Enable the mail profile for agent (SQL Server Agent Properties –> Alert System –>select Enable Mail profile & Select profile)

3. Create Operator with Mail ID. (Ex : DBA)

4. Run below script for enabling the alerts for all the jobs.

We need to decide when we want email to be trigger (i.e  after Complete, Failed, Success) below table will give the details.

Value Description
0 Never
1 On Success
2 On Failure
3 Always

In the below script i assume that , Alert is sent on 2 (On Failure) and alert will be sent to DBA (Operator).

use msdb

DECLARE @operator varchar(50)
SET @operator = ‘DBA’

SELECT ‘EXEC msdb.dbo.sp_update_job @job_ID = ”’ + convert(varchar(50),job_id) + ”’ ,@notify_level_email = 2, @notify_email_operator_name = ”’ + @operator + ”’ ;’ FROM sysjobs


Execute the script which is generated by above script.





Windows Disk Space Alert From SQL SERVER JOB

In SQL server when the auto growth option is set to true and when we have defined the autogorwth in MB, some times we may face the issue like windows disk space is full, during that time SQL Server cannot increase the auto growth size of the mdf or ldf files.

As Best practice to have the alert for the drives free space. We can create a job for the below script. schedule it for every 15 or 30 minutes based on your application usage / load.

Types of SQL Server Backup -1 : FULL Backup

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup.

A full backup can be completed either using T-SQL or by using SSMS

Create a full backup of the AdventureWorks database to one disk file


BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'

SQL Server Management Studio

  • Right click on the database name
  • Select Tasks > Backup
  • Select “Full” as the backup type
  • Select “Disk” as the destination
  • Click on “Add…” to add a backup file and type “C:\AdventureWorks.BAK” an click “OK”
  • Click “OK” again to create the backup

SQL Server – Hide system objects in Object Explorer – SQL Server Management Studio

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on.

SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use.

To enable this option follow below steps.

Step1: Go to Tools > Options


Step2: Navigate to Environment > Startup


Step3: Check Hide system objects in Object Explorer and click OK:


Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue:



Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in Object Explorer:

What is hidden by this setting:

1. System databases – This is not hidden for SQL Server 2012
2. System Stored Procedures
3. System Tables
4. System Views


How to Find SQL Server Database Size

if convert(varchar(20),SERVERPROPERTY(‘productversion’)) like ‘8%’

SELECT [name], fileid, filename, [size]/128 AS ‘Total Size in MB’,
[size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’,
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’,
(100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’
FROM sysfiles
SELECT [name], file_id, physical_name, [size]/128 AS ‘Total Size in MB’,
[size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’,
CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’,
(100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’
FROM sys.database_files



SQL SERVER Performance bottlenecks & Trouble shooting

To start troubleshooting, you have to define the symptoms first. The most common SQL Server performance symptoms are CPU, memory, network, and I/O bottlenecks, and slow running queries

CPU bottlenecks are caused by insufficient hardware resources. Troubleshooting starts with identifying the biggest CPU resource users. Occasional peaks in processor usage can be ignored, but if the processor is constantly under pressure, investigation is needed. Adding additional processors or using a more powerful one might not fix the problem, as badly designed processes can always use all CPU time. Query tuning, improving execution plans, and system reconfiguration can help. To avoid bottlenecks, it’s recommended to have a dedicated server that will run only SQL Server, and to remove all other software to another machine

Memory bottlenecks can result in slow application responsiveness, overall system slowdown, or even application crashing. It’s recommended to identify when the system runs with insufficient memory, what applications use most of memory resources, whether there are bottlenecks for other system resources. Reviewing and tuning queries, memory reconfiguration, and adding more physical memory can help

Network bottlenecks might not be instantly recognized, as they can at a first glance be considered as SQL Server performance issues caused by other resources. For example, a delay of data sent over a network can look like SQL Server slow response

I/O bottlenecks are caused by excessive reading and writing of database pages from and onto disk. A bottleneck is manifested through long response times, application slowdowns and tasks time-outs. If other applications use disk resources excessively, SQL Server might not get enough disk resources for its normal operation and would have to wait to be able to read and write to disk

Slow running queries can be a result of missing indexes, poor execution plans, bad application and schema design, etc.


Index Maintenance Query, Rebuid (>40)or Reorganize (>10)

Below query will generated list of indexes to be re organized or rebuild based on 10-40 Rule.

>=40 % Rebuild

>=10 and <40 ReOrganize

— This query is tested on working fine.



  SELECT ‘ALTER INDEX [‘ + + ‘] ON [‘ + + ‘].[‘ + + ‘] ‘ +

       CASE WHEN ps.avg_fragmentation_in_percent >= 40 THEN ‘REBUILD’ ELSE ‘REORGANIZE’ END +

       CASE WHEN pc.partition_count > 1 THEN ‘ PARTITION = ‘ + cast(ps.partition_number as nvarchar(max)) ELSE ” END

FROM   sys.indexes AS ix


INNER JOIN sys.tables t  ON t.object_id = ix.object_id

       INNER JOIN sys.schemas s     ON t.schema_id = s.schema_id

       INNER JOIN(SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number

                   FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)) ps

           ON t.object_id = ps.object_id AND ix.index_id = ps.index_id

       INNER JOIN(SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count

                   FROM sys.partitions

                   GROUP BY object_id, index_id) pc

           ON t.object_id = pc.object_id AND ix.index_id = pc.index_id

WHERE  ps.avg_fragmentation_in_percent >= 10 AND IS NOT NULL



Query to find space occupied by each database in Data cache

Every database in SQL Server which is getting read by the user or application is retrieved from the buffer cache. If the pages which are not in buffer cache are read by the user/application, those pages will be loaded into buffer cache first and then retrieved to the user/application.


Database Pages which occupied in the cache are retrieved by the following query:

 SELECT count(*)*8/1024 AS 'Cached Size (MB)', 
CASE database_id  WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)  END AS 'Database'  
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id 
ORDER BY 'Cached Size (MB)' DESC