DBA Checklist with Policy Based Management 2.0

In my last blog post I talked about how easy it is to use Policy Based Management for your daily DBA Checklist.
And it gets even better when you use the PBM in combination with a Central Management Server.
I check my customers servers in no time 😀

However, while using it for a few weeks now, I noticed that there were some improvements possible.
The first improvement was the policy where I check the free disk space of the server.
The initial version was going to check if the amount of free space of the volume that contains the data file was more than 10 GB.
See this screenshot below


But what about the disks where the log files are stored? They are not checked.
Or what happens if your disk is less than 10 GB like for example the system databases LUN. The policy will always fail.
I had to find a way that could show the free disk space in percentage for every disk in my server.
I’ve checked all the Facets in the PBM but I couldn’t find one that was useful. If you can find one, please let me know.

I had to come up with a script that could do the trick.
In the old days, you had to build a script that was using the xp_fixeddrives stored procedure. Problem is that it this procedure can’t check mount points.
The best way nowadays is use powershell. But then again, some old servers didn’t had powershell installed and the script failed again.
I found some useful scripts on this site http://www.mssqltips.com/sqlservertip/2444/script-to-get-available-and-free-disk-space-for-sql-server/ , combined and tuned a few of them.

@Free_Bytes BIGINT,
@TotalBytes BIGINT,
@Drive Char(1),
@sql varchar(1000),
@svrName varchar(255)
set @svrName = convert(varchar(255),SERVERPROPERTY('MachineName'))
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity+''%''+$_.freespace+''*''}"'
--creating a temporary tables
CREATE TABLE #output (line varchar(255))
CREATE TABLE #DiskSpace ( Drive Char(1), Total_Space Bigint, Free_Space Bigint,Percentage_Free as (Free_Space*100 / Total_Space ) )
CREATE TABLE #Fixeddrives ( Drive Char(1), Free_Space Bigint)
CREATE TABLE #fsutil ( ID INT IDENTITY , Drivedata Varchar(2000))
--inserting disk name, total space and free space value in to temporary table
insert #output
EXEC xp_cmdshell @sql
--select * from #output
if (select count(1) from #output where line like '%powershell.exe%')=0
	insert into #DiskSpace
	select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -3))) as drivename
		  (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'TotalSpace'
		  (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'FreeSpace'
	from #output
	where line like '[A-Z][:]%' and LEN(line)>6
	order by drivename
	Insert into #Fixeddrives exec master.dbo.xp_fixeddrives
	insert into #DiskSpace ( Drive)
	select Drive from #Fixeddrives

	SELECT Drive from #DiskSpace
	OPEN Drive_cursor
	FETCH NEXT FROM Drive_cursor INTO @Drive


	select @sql = 'insert into #fsutil exec master.dbo.xp_cmdshell ''fsutil volume diskfree '+ @Drive+':'''

	select @Free_Bytes = substring (Drivedata, charIndex (':', Drivedata, 0)+ 1 , 1000)
	from #fsutil where Drivedata like '%Total # of free bytes%'

	select @TotalBytes =substring (Drivedata, charIndex (':', Drivedata, 0)+ 1 , 1000)
	from #fsutil where Drivedata like '%Total # of bytes%'

	Update #DiskSpace set Total_Space = @TotalBytes, Free_Space=@Free_Bytes
	WHERE Drive = @Drive

	FETCH NEXT FROM Drive_cursor INTO @Drive

	CLOSE Drive_cursor
	DEALLOCATE Drive_cursor


select * from #DiskSpace
order by Drive

--Drop the temporary tables
drop table #output
drop table #DiskSpace
drop table #Fixeddrives
drop table #fsutil

You can use this script in the condition of your policy by using the ExecutSql option


Second Issue was the full backup check. Some big databases, like data warehouses, are too big to perform a full backup every day.
Instead of a full backup, a daily differential backup is taken and a full backup is taken every week.
With that backup strategy my full backup policy was failing, so I also had to cover the differential backups in my condition.


You can download the new version of my policies from my SkyDrive and import them into your Central Management Server.
If you should have any suggestion on improving the policies, do not hesitate to contact me!


DBA Checklist with Policy Based Management

In my job as a SQL Server Database Consultant, I have my “recurring” customers. This means that every week, I’m at the customer for 1 day to do day-to-day DBA tasks.
Since those customers don’t have a DBA, I have to perform the daily DBA checklist on a weekly basis J… Initially, I’ve started with executing some T-SQL scripts to verify the SQL Servers. But as more servers were installed, my DBA checklist became really time consuming.

So, I had to find a way to perform the same checks in a more efficient way. I’ve started playing with the policy based management feature and I’ve created my “DBA Checklist” policies that I’m checking against all the SQL Servers at once.
Less time consuming and it gives you a nice overview of failed checks.

The first step that you need to do is creating a Central Management Server (CMS) and register all your servers under that CMS.
I’m not going into detail on this but you can find more information on http://msdn.microsoft.com/en-us/library/bb895144.aspx

I’ve created the following DBA Checklist

  • Verify if there were any failed SQL Agent Jobs during the last week
  • Verify if all the SQL Server Services are running
  • Verify if all databases are in a “Normal” state (not suspect, restoring, offline, …)
  • Verify free disk space
  • Verify free space in the data files
  • Verify if a full backup was taken of the database in the last 24 hours
  • Verify if a transaction log backup was taken of the database in the last hour
  • Verify if there were errors in the error log with a severity > 17 for the last 7 days

This checklist is pretty basic, I know, but it gives me a good idea if my SQL Servers are fine or not. Feel free to give me any suggestion of checks that I can add to the list.

You can download the policies from my SkyDrive and import them into your Central Management Server.
Please note that the policies will not work against a SQL Server 2000 or lower.

Connect to your CMS – Go to Management – Policy Management – Policies
Right click Policies and select “Import Policy”


Select the xml –files you just have downloaded as the “Files to import” and click OK.


If all went fine, you should get a result like the one below


Now let’s do a check against all my servers.
Right click on your CMS and select “Evaluate Policies”

As a source, select again the CMS server, because that’s the one that where you have just imported the policies

Select the policies that you want to verify. You can easily find my policies because I’ve put them in the category “DBA Checklist”

Now, just click the Evaluate button. As a result, all the selected policies will be checked against the servers that are registered under your CSM.
When the checks are finished, you should see this kind of result

Below, in the target details area you can see which checks are failed. Click on the “View” link to see more details and correct them.
In the end, you want to see all you’re policy checks green 😀 !

If you should have suggestions or you find any improvements, please let me know so that I can adapt them.

Have fun!