posted 2/20/2012 12:01:50 PM by Ritesh Medhe - Views: [25008]
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 some of the 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.
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,
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>
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 ------------------------------ f <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>
You can refer to the stored procedure code at end of the post (EmailSQLServerHealth.sql) 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?
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.
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.
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.
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
In this section you will get to know how much free space is left on individual disks.
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
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.
Another catalog view, backupsets, served the purpose out here.
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.
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
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
The following are required for my solution to work.
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 EmailSQLServerHealth.sql (at the end of the post) inside the msdb database. This will create a procedure uspEmailSQLServerHealth. This procedure accepts 5 input parameters:
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.
Resource (EmailSQLServerHealth.SQL)
USE [msdb] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].uspEmailSQLServerHealth ( @ServerIP VARCHAR(100), -- SQL Server 2005 Database Server IP Address @Project VARCHAR(100), -- Name of project or cleint @Recepients VARCHAR(2000), -- Recepient(s) of this email (; separated in case of multiple recepients). @MailProfile VARCHAR(100), -- Mail profile name which exists on the target database server @Owner VARCHAR(200) -- Owner, basically name/email of the DBA responsible for the server ) /* exec EmailSQLServerHealth '10.10.10.10', 'MYProject', 'myself@mycompany.com', 'TestMailProfile', 'My Self' */ AS BEGIN SET NOCOUNT ON /* Drop all the temp tables(not necessary at all as local temp tables get dropped as soon as session is released, however, good to follow this practice). */ IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#jobs_status') BEGIN DROP TABLE #jobs_status END IF EXISTS (SELECT 1 FROM sysobjects WHERE name = '#diskspace') BEGIN DROP TABLE #diskspace END IF EXISTS (SELECT NAME FROM sysobjects WHERE name = '#url') BEGIN DROP TABLE #url END IF EXISTS (SELECT NAME FROM sysobjects WHERE name = '#dirpaths') BEGIN DROP TABLE #dirpaths END -- Create the temp tables which will be used to hold the data. CREATE TABLE #url ( idd INT IDENTITY (1,1), url VARCHAR(1000) ) CREATE TABLE #dirpaths ( files VARCHAR(2000) ) CREATE TABLE #diskspace ( drive VARCHAR(200), diskspace INT ) -- This table will hold data from sp_help_job (System sp in MSDB database) CREATE TABLE #jobs_status ( job_id UNIQUEIDENTIFIER, originating_server NVARCHAR(30), name SYSNAME, enabled TINYINT, description NVARCHAR(512), start_step_id INT, category SYSNAME, owner SYSNAME, notify_level_eventlog INT, notify_level_email INT, notify_level_netsend INT, notify_level_page INT, notify_email_operator SYSNAME, notify_netsend_operator SYSNAME, notify_page_operator SYSNAME, delete_level INT, date_created DATETIME, date_modified DATETIME, version_number INT, last_run_date INT, last_run_time INT, last_run_outcome INT, next_run_date INT, next_run_time INT, next_run_schedule_id INT, current_execution_status INT, current_execution_step SYSNAME, current_retry_attempt INT, has_step INT, has_schedule INT, has_target INT, type INT ) -- To insert data in couple of temp tables created above. INSERT #diskspace(drive, diskspace) EXEC xp_fixeddrives INSERT #jobs_status EXEC msdb.dbo.sp_help_job -- Variable declaration DECLARE @TableHTML VARCHAR(MAX), @StrSubject VARCHAR(100), @Oriserver VARCHAR(100), @Version VARCHAR(250), @Edition VARCHAR(100), @ISClustered VARCHAR(100), @SP VARCHAR(100), @ServerCollation VARCHAR(100), @SingleUser VARCHAR(5), @LicenseType VARCHAR(100), @StartDate DATETIME, @EndDate DATETIME, @Cnt int, @URL varchar(1000), @Str varchar(1000) -- Variable Assignment SELECT @Version = @@version SELECT @Edition = CONVERT(VARCHAR(100), serverproperty('Edition')) SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME) SELECT @StartDate = @StartDate - 1 SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME) SET @Cnt = 0 IF serverproperty('IsClustered') = 0 BEGIN SELECT @ISClustered = 'No' END ELSE BEGIN SELECT @ISClustered = 'YES' END SELECT @SP = CONVERT(VARCHAR(100), SERVERPROPERTY ('productlevel')) SELECT @ServerCollation = CONVERT(VARCHAR(100), SERVERPROPERTY ('Collation')) SELECT @LicenseType = CONVERT(VARCHAR(100), SERVERPROPERTY ('LicenseType')) SELECT @SingleUser = CASE SERVERPROPERTY ('IsSingleUser') WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE 'null' END SELECT @OriServer = CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) SELECT @strSubject = 'DB Server Daily Health Checks ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')' /* Along with refrences to SQL Server System objects, You will also see lots of HTML code however do not worry, Even though I am a primarily a SQL Server DBA, I am little fond of HTML, so thought to show some of my HTML skills here :), trust me you would love to see the end product.... */ 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> <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> <table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1"> <tr> <td width="50%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">Version</font></b></td> <td width="17%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">Edition</font></b></td> <td width="18%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">Service Pack</font></b></td> <td width="93%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">Collation</font></b></td> <td width="93%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">LicenseType</font></b></td> <td width="30%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">SingleUser</font></b></td> <td width="93%" bgColor="#000080" height="15"><b> <font face="Verdana" color="#ffffff" size="1">Clustered</font></b></td> </tr> <tr> <td width="50%" height="27"><font face="Verdana" size="1">'+@version +'</font></td> <td width="17%" height="27"><font face="Verdana" size="1">'+@edition+'</font></td> <td width="18%" height="27"><font face="Verdana" size="1">'+@SP+'</font></td> <td width="17%" height="27"><font face="Verdana" size="1">'+@ServerCollation+'</font></td> <td width="25%" height="27"><font face="Verdana" size="1">'+@LicenseType+'</font></td> <td width="25%" height="27"><font face="Verdana" size="1">'+@SingleUser+'</font></td> <td width="93%" height="27"><font face="Verdana" size="1">'+@isclustered+'</font></td> </tr> </table> <p style="margin-top: 0; margin-bottom: 0"> </p>' + '<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1"> <tr> <th align="left" width="432" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Job Name</font></th> <th align="left" width="91" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Enabled</font></th> <th align="left" width="85" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Last Run</font></th> <th align="left" width="183" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Category</font></th> <th align="left" width="136" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Last Run Date</font></th> <th align="left" width="136" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Execution Time (Mi)</font></th> </tr> <font face="Verdana" size="4">Job Status</font>' 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 SELECT @TableHTML = @TableHTML + '<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="933" border="1"> <tr> <td width="35%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">Name</font></b></td> <td width="23%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">CreatedDate</font></b></td> <td width="23%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">DB Size(GB)</font></b></td> <td width="30%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">State</font></b></td> <td width="50%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">RecoveryModel</font></b></td> </tr> <p style="margin-top: 1; margin-bottom: 0"> </p> <font face="Verdana" size="4">Databases</font>' 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 SELECT @TableHTML = @TableHTML + '<table id="AutoNumber1" style="BORDER-COLLAPSE: collapse" borderColor="#111111" height="40" cellSpacing="0" cellPadding="0" width="24%" border="1"> <tr> <td width="27%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">Disk</font></b></td> <td width="59%" bgColor="#000080" height="15"><b> <font face="Verdana" size="1" color="#FFFFFF">Free Space (GB)</font></b></td> </tr> <p style="margin-top: 1; margin-bottom: 0"> </p> <p><font face="Verdana" size="4">Disk Stats</font></p>' SELECT @TableHTML = @TableHTML + '<tr><td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), drive), '') +'</font></td>' + '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), ISNULL(CAST(CAST(diskspace AS DECIMAL(10,2))/1024 AS DECIMAL(10,2)), 0)),'') +'</font></td></tr>' FROM #diskspace SELECT @TableHTML = @TableHTML + '</table>' -- Code for SQL Server Database Backup Stats SELECT @TableHTML = @TableHTML + '<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1"> <tr> <th align="left" width="91" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Date</font></th> <th align="left" width="105" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Database</font></th> <th align="left" width="165" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">File Name</font></th> <th align="left" width="75" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Type</font></th> <th align="left" width="165" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Start Time</font></th> <th align="left" width="165" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">End Time</font></th> <th align="left" width="136" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Size(GB)</font></th> </tr> <p style="margin-top: 1; margin-bottom: 0"> </p> <p><font face="Verdana" size="4">SQL SERVER Database Backup Stats</font></p>' SELECT @TableHTML = @TableHTML + '<tr> <td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(2), DATEPART(dd,MST.backup_start_date)) + '-' + CONVERT(VARCHAR(3),DATENAME(mm, MST.backup_start_date)) + '-' + CONVERT(VARCHAR(4), DATEPART(yyyy, MST.backup_start_date)),'') +'</font></td>' + '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.database_name), '') +'</font></td>' + '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(100), MST.name), '') +'</font></td>' + CASE Type WHEN 'D' THEN '<td><font face="Verdana" size="1">' + 'Full' +'</font></td>' WHEN 'I' THEN '<td><font face="Verdana" size="1">' + 'Differential' +'</font></td>' WHEN 'L' THEN '<td><font face="Verdana" size="1">' + 'Log' +'</font></td>' WHEN 'F' THEN '<td><font face="Verdana" size="1">' + 'File or Filegroup' +'</font></td>' WHEN 'G' THEN '<td><font face="Verdana" size="1">' + 'File Differential' +'</font></td>' WHEN 'P' THEN '<td><font face="Verdana" size="1">' + 'Partial' +'</font></td>' WHEN 'Q' THEN '<td><font face="Verdana" size="1">' + 'Partial Differential' +'</font></td>' ELSE '<td><font face="Verdana" size="1">' + 'Unknown' +'</font></td>' END + '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_start_date), '') +'</font></td>' + '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(50), MST.backup_finish_date), '') +'</font></td>' + '<td><font face="Verdana" size="1">' + ISNULL(CONVERT(VARCHAR(10), CAST((MST.backup_size/1024)/1024/1024 AS DECIMAL(10,2))), '') +'</font></td>' + '</tr>' FROM backupset MST WHERE MST.backup_start_date BETWEEN @StartDate AND @EndDate ORDER BY MST.backup_start_date DESC SELECT @TableHTML = @TableHTML + '</table>' -- Code for physical database backup file present on disk 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 select @TableHTML = @TableHTML + '<table style="BORDER-COLLAPSE: collapse" borderColor="#111111" cellPadding="0" width="933" bgColor="#ffffff" borderColorLight="#000000" border="1"> <tr> <th align="left" width="91" bgColor="#000080"> <font face="Verdana" size="1" color="#FFFFFF">Physical Files</font></th> </tr> <p style="margin-top: 1; margin-bottom: 0"> </p> <p><font face="Verdana" size="4">Physical Backup Files</font></p>' SELECT @TableHTML = @TableHTML + '<tr>' + CASE SUBSTRING(files, 1, 5) WHEN 'PATH:' THEN '<td bgcolor = "#D7D7D7"><b><font face="Verdana" size="1">' + files + '</font><b></td>' ELSE '<td><font face="Verdana" size="1">' + files + '</font></td>' END + '</tr>' FROM #dirpaths SELECT @TableHTML = @TableHTML + '</table>' + '<p style="margin-top: 0; margin-bottom: 0"> </p> <hr color="#000000" size="1"> <p><font face="Verdana" size="2"><b>Server Owner:</b> '+@owner+'</font></p> <p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">Thanks and Regards,</font></p> <p style="margin-top: 0; margin-bottom: 0"><font face="Verdana" size="2">DB Support Team</font></p> <p> </p>' EXEC msdb.dbo.sp_send_dbmail @profile_name = @MailProfile, @recipients=@Recepients, @subject = @strSubject, @body = @TableHTML, @body_format = 'HTML' ; SET NOCOUNT OFF END
Ritesh Medhe (Member since: 3/31/2011 11:34:25 AM)
View Ritesh Medhe 's profile
When i try to execute the procedure , it gives me an error saying
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72An INSERT EXEC statement cannot be nested.can u plz help me with this
Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 72
An INSERT EXEC statement cannot be nested.
can u plz help me with this
Ritesh,
Thanks very much. This is very handy script to monitor the servers.
Hi Mujahid,
Even i got the same error when i tried to execute the stored procedure. In order to fix that i made a change to the code while inserting the stored prodeure execution results data into a temp table and the error didnt occur again.Please see below what i have modified.
I modified this "INSERT #jobs_status EXEC msdb.dbo.sp_help_job" code with the code below and it worked.
#jobs_status
EXEC
msdb.dbo.sp_help_job
INSERT #jobs_status SELECT
INSERT
SELECT
Please correct me if i am wrong here.
Thanks,
Abdul
I am new to SQL Server - coming from DB2 Mainframe background and this was a life saver
Thank you so much, works perfectly for 2008 R 2
Leave a comment