It's a known fact that an ideal Production DBA is like a parent to 100's of children. By children I mean database servers, and yes, they are like babies to DBAs. No matter how unpleasant your child is you have to take care of each of them. As a DBA, it's your responsibility to keep an eye on each and every activity that is taking place on your database servers. It could be the creation of new tables, databases, logins, jobs, maintenance plans and so on.
In this article I will be focusing on database monitoring activities (listed below), which I am supposed to carry out on day to day basis in my organization and I believe many other DBAs in their organizations.
- Ensuring jobs have run successfully; also check if any job is running in endless loop, check if any new job has been created.
- Check whether the backups have happened.
- Ensure that the backups are deleted as per the retention policy from the disk and there are no unnecessary files lying on the disk which could cause backup failure.
- Keep an eye on disk space.
- Check if any new database has been created.
- Check if there is drastic increase in size of any of the databases.
This article is targeted at production DBAs.
I used to carry out these activities in various manners, which also involved manual interventions. I somehow wanted to automate this activity and get all the required information in well formatted (HTML) e-mail.
This mail is divided into six sections as below,
Section 1 [Server Info]
This section features generic information like server name, SQL Server version, service pack, whether the server is in single mode, clustered, licensing information, etc.
How it works?
I have written appropriate comments at appropriate places inside the stored procedure; here I will focus only on the code that actually fetches the data from various system objects. I have declared a variable at the beginning of the stored procedure, @TableHTML, which gets built and then executed at the end before it sends an e-mail.
Here is the code sinppet for section 1, Server Info.
---------------------------- Section A ------------------------------
SET @TableHTML =
'<font face="Verdana" size="4">Server Info</font>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="27%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td>
<td width="39%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
<td width="90%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td>
</tr>
---------------------------- Section B ------------------------------
<tr>
<td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td>
<td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>
<td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>
</tr>
</table>
Server IP | Server Name | Project/Client |
| '+@ServerIP+' | ' + @OriServer +' | '+@Project+' |
You will notice lots of HTML tags which have been used to make the e-mail look well formatted, however detailed discussion on these HTML tags is beyond the scope of this article. In a nutshell, these HTML tags are divided in to two sections, Section A, which builds the header information (Refer Fig1), and Section B that contains actual information fetched form various system objects.
If you are interested in learning HTML you can get help from any web designer or you can use any HTML editor like Microsoft FrontPage, which is the nice and easy to use application where you just have to drag and drop components, format, and align things. Then you can copy the HTML script generated by Frontpage and paste it in the stored procedure. I believe that you will not need to make any change to this code as it contains all the critical information an Ideal DBA desires.
The actual part to look over here is last couple of lines in Section B of the code where you will see variables concatenated with HTML script; this is the place where the actual data gets populated.
In case you need to remove any information you think is irrelevant to you, here is what you do. Let's say you want to exclude the ServerIP from this section of the e-mail. All you have to do is to knock of the lines of code which I have highlighted in bold Section A and B. Similarly, if you want to add more information you can copy paste the highlighted lines in whichever sequence you want. For example, if you want to add service name in this section of e-mail, the code after change will look like this (refer the code highlighted in bold)
---------------------------- Section 1 ------------------------------
SET @TableHTML =
'<font face="Verdana" size="4">Server Info</font>
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="47%" id="AutoNumber1" height="50">
<tr>
<td width="27%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server IP</font></b></td>
<font face="Verdana" size="2" color="#FFFFFF">ServiceName</font></b></td>
<td width="39%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Server Name</font></b></td>
<td width="90%" height="22" bgcolor="#000080"><b>
<font face="Verdana" size="2" color="#FFFFFF">Project/Client</font></b></td>
</tr>
---------------------------- Section 2 ------------------------------
<tr>
<td width="27%" height="27"><font face="Verdana" size="2">'+@ServerIP+'</font></td>
<td width="27%" height="27"><font face="Verdana" size="2">'+@@ServiceName+'</font></td>
<td width="39%" height="27"><font face="Verdana" size="2">' + @OriServer +'</font></td>
<td width="90%" height="27"><font face="Verdana" size="2">'+@Project+'</font></td>
</tr>
</table>
| Server IP | ServiceName | Server Name | Project/Client |
| '+@ServerIP+' | '+@@ServiceName+' | ' + @OriServer +' | '+@Project+' |
You can refer to the stored procedure I have attached (EmailSQLServerHealth.sql) with this article to see from where I got values for these variables, for example to get version information, I used system function @@version in this way.
SELECT @Version = @@version
For edition I used the system function serverproperty as:
SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition'))
How It Looks?
Figure 1
Section 2 [Job Status]
This section features job status, whether they have executed successfully or have failed, and how long the execution took. Try to run this email on the server having jobs that fail, and also have disabled jobs. In such a scenario the disabled jobs are highlighted in a different color and failed jobs are highlighted in red with links (Refer Fig2). When you click the link on failed jobs, it will open your e-mail editor where you can send the failure information to the concerned person. You can put this as step in job itself, however consider the case of a database backup job. Whenever it fails it sends an email to group of people. Now you realize that the cause for failure is insufficient disk space and the only solution is to add more disk. You also know that disk procurement process may take some time depending upon the organization's policy. If this takes a really long time then users will be irritated with the mails, and this is the reason why I have added this optional mailing feature.
How it works?
As many of you must be aware of the system procedure sp_help_job in the msdb database, which returns details pertaining to SQL Server jobs, I have made use of this SP to get the desired information. First I created a temp table (#jobs_status) with exact number of columns returned by this SP and then populated the temp table as below
INSERT #jobs_status EXEC msdb.dbo.sp_help_job
I then manipulate the information in the temp table along with joins to sysjobs and sysactivity system tables in the msdb database. Again the process remains the same; first build the @TableHTML variable with the header information and then the actual information from the temp table. Below is the code to fetch the job information from the temp table. The only difference here is that the result set will have single row for each job on the server and that's the reason I have fethed the information from temp table (#jobs_status) instead of using variables. You will see case statements along with HTML script, which are used to highlight the rows depending upon the last execution status of the job.
SELECT
@TableHTML = @TableHTML + '<tr><td><font face="Verdana" size="1">' +
ISNULL(CONVERT(VARCHAR(100), A.name), '') +'</font></td>' +
CASE enabled
WHEN 0 THEN '<td bgcolor="#FFCC99"><b><font face="Verdana" size="1">False</font></b></td>'
WHEN 1 THEN '<td><font face="Verdana" size="1">True</font></td>'
ELSE '<td><font face="Verdana" size="1">Unknown</font></td>'
END +
CASE last_run_outcome
WHEN 0 THEN '<td bgColor="#ff0000"><b><blink><font face="Verdana" size="2">
<a href="mailto:servicedesk@mycompany.com?subject=Job failure - ' + @Oriserver + '(' + @ServerIP + ') '+ CONVERT(VARCHAR(15), GETDATE(), 101) +'&cc=db.support@mycompany.com&body = SD please log this call to DB support,' + '%0A %0A' + '<<' + ISNULL(CONVERT(VARCHAR(100), name),'''') + '>> Job Failed on ' + @OriServer + '(' + @ServerIP + ')'+ '.' +'%0A%0A Regards,'+'">Failed</a></font></blink></b></td>'
WHEN 1 THEN '<td><font face="Verdana" size="1">Success</font></td>'
WHEN 3 THEN '<td><font face="Verdana" size="1">Cancelled</font></td>'
WHEN 5 THEN '<td><font face="Verdana" size="1">Unknown</font></td>'
ELSE '<td><font face="Verdana" size="1">Other</font></td>'
END +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), A.category),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), A.last_run_date),'') + '</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), X.execution_time_minutes),'') +'</font></td> </tr>'
FROM
#jobs_status A
inner join (
select
A.job_id,
datediff(mi, A.last_executed_step_date, A.stop_execution_date) execution_time_minutes
from
msdb..sysjobactivity A
inner join (
select
max(session_id) sessionid,
job_id
from
msdb..sysjobactivity
group by
job_id
) B on a.job_id = B.job_id and a.session_id = b.sessionid
inner join (
select
distinct name,
job_id
from
msdb..sysjobs
) C on A.job_id = c.job_id
) X on A.job_id = X.job_id
ORDER BY
last_run_date DESC
As discussed above the failed job will have link that will take you to email editior with prefilled values. If you wish to change any of these values have a look at the code highlighted in bold.
How It Looks?
Section 3 [Databases]
This section will contain a list of databases, with some useful attributes like creation date, database size, state (online, offline...) and recovery model. Simple yet powerful information, this will help a DBA to check if any new database has been created without his knowledge (the newly added database may require to be included in the backup strategy) or, if there is a drastic increase in database size.
How it works?
As far as process of building @TableHTML goes it remains same throughout. I have made use of catalog views sys.databases and sys.sysaltfiles (BOL is the best place to get in depth knowledge on these catalog views ).
select
@TableHTML = @TableHTML +
'<tr><td><font face="Verdana" size="1">' + ISNULL(name, '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + CONVERT(VARCHAR(2), DATEPART(dd, create_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm,create_date)) + '-' + CONVERT(VARCHAR(4),DATEPART(yy,create_date)) +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), AA.[Total Size GB]), '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(state_desc, '') +'</font></td>' +
'<td><font face="Verdana" size="1">' + ISNULL(recovery_model_desc, '') +'</font></td></tr>'
from
sys.databases MST
inner join (select b.name [LOG_DBNAME],
CONVERT(DECIMAL(10,2),sum(CONVERT(DECIMAL(10,2),(a.size * 8)) /1024)/1024) [Total Size GB]
from sys.sysaltfiles A
inner join sys.databases B on A.dbid = B.database_id
group by b.name)AA on AA.[LOG_DBNAME] = MST.name
order by
MST.name
How It Looks?
Section 4 [Disk Stats]
In this section you will get to know how much free space is left on individual disks.
How it works?
Over here I have made use of the undocumented extended stored procedure xp_fixeddrives, populated the temp table with the output, and manipulated in same way as discussed in the above sections.
INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives
How It Looks?
Section 5 [SQL Server Database Backup Stats]
This is another essential section and a must for any DBA. This will help you know which databases were backed up, the backup filename, backup type (full, differential, log, etc.), start time, end time, and backup size. This information will be incremental and will contain a months worth of data.
How it works?
Another catalog view, backupsets, served the purpose out here.
How It Looks?
Section 6 [Physical backup files]
This is yet another interesting aspect that is often overlooked. As far as standard backup process goes, backups are created on local or remote disks and subsequently copied to tape. If the backup size is in terabytes (which is the case with me), the tape backup will take a significant amount of time. During this period the backup file will be locked and your retention policy may not be able to delete the file from disk. If your retention policy says that there should be only 3 full backup files on disk at any given point in time, I need to ensure that the old files are deleted timely, otherwise, it could lead to backup failure, and this is the reason why I added this section. It shows me list of physical files residing on the server, and if there are more files than I expect, I will go and delete the files manually and find out the root cause and subsequently inform the concerned person.
How it works?
As many of us are aware that SQL server logs each and every detail that is exposed by catalog views. Even for backup, information like backup type, filename, backup start and end time, etc., are logged and can be fetched from backupset and backupmediafamily catalog views. For this section, I have fetched the physical file name and path of the backup from backupmediafamily catalog view and populated it into a temp table. As this catalog view returns the backp filename along with the path. With the use of CHARINDEX function I got rid of the filename inorder to get only the backup path.
For example, from the catalog view I got: D:\DBBAckups\FullDBBackups\TESTDB.BAK. By using CHARINDEX, I removed the file name to get the backup path: D:\DBBAckups\FullDBBackups\. I browsed thorugh this path with the use of DOS command DIR and the options /B/O:D, to get list of all the physical files existing under the given path. The code iterates to get the files names under each distinct path.
Here is the explanation of the options used with DIR command
- /B - Uses bare format (no heading information or summary).
- /O - List by files in sorted order.
- /D - Same as wide but files are list sorted by column.
The code snippet is shown below
INSERT #url
SELECT DISTINCT
SUBSTRING(BMF.physical_device_name, 1, len(BMF.physical_device_name) - CHARINDEX('\', REVERSE(BMF.physical_device_name), 0))
from
backupset MST
inner join backupmediafamily BMF ON BMF.media_set_id = MST.media_set_id
where
MST.backup_start_date BETWEEN @startdate AND @enddate select @Cnt = COUNT(*) FROM #url WHILE @Cnt >0
BEGIN SELECT @URL = url FROM #url WHERE idd = @Cnt
SELECT @Str = 'EXEC master.dbo.xp_cmdshell ''dir "' + @URL +'" /B/O:D''' INSERT #dirpaths SELECT 'PATH: ' + @URL
INSERT #dirpaths
EXEC (@Str)
INSERT #dirpaths values('') SET @Cnt = @Cnt - 1
end DELETE FROM #dirpaths WHERE files IS NULL
How It Looks?
Prerequisites
The following are required for my solution to work.
- SQL Server 2005.
- Valid Database Mail Profile.
- Sysadmin privilege as this procedure refers to lots of system objects in msdb and master database.
- xp_cmdshell should be enabled as the code executes DIR command.
What does this E- mail contain?
This mail gives me the bird's eye view of database server on daily basis; you can schedule this mail multiple times as per your requirement.I have scheduled this mail early morning after all the backup tasks and other off-peak hour's activities are done. Here is how to setup this automated health check e-mail.
Step 1: Run the attached script (EmailSQLServerHealth.sql) inside the msdb database. This will create a procedure uspEmailSQLServerHealth. This procedure accepts 5 input parameters:
- @ServerIP - SQL Server 2005 Database Server IP Address
- @Project - Name of project or client
- @Recipients - Recipient(s) of this email (; separated in case of multiple recipients).
- @MailProfile - Mail profile name which exists on the target database server
- @Owner - Owner, basically name/email of the DBA responsible for the server
Step 2: Schedule a job as per your requirement with a call to this stored procedure. For example:
exec EmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile', 'My Self'
Why should I use it?
As I mentioned, an ideal production DBA is responsible for looking after a considerable amount of database servers. It is very handy to have such an e-mail scheduled on each server, given the benefit and the time taken to set this up instead of managing the admin tasks in a different manner on each server. It also saves great amount of time on monitoring activity, I have set up this e-mail on 20+ servers. Earlier I use to spend about 45 minutes on average looking at each server. All you need to do is create the stored procedure inside msdb database and schedule a job.
About Me:
Microsoft Certified DBA (SQL Server 2005)
Certification: MCTS, MCITP
Experience: 5+ years as a DBA