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.
SET NOCOUNT ON Declare @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 BEGIN insert into #DiskSpace select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -3))) as drivename ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1, (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'TotalSpace' ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1, (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'FreeSpace' from #output where line like '[A-Z][:]%' and LEN(line)>6 order by drivename END ELSE BEGIN Insert into #Fixeddrives exec master.dbo.xp_fixeddrives insert into #DiskSpace ( Drive) select Drive from #Fixeddrives DECLARE Drive_cursor CURSOR FOR SELECT Drive from #DiskSpace OPEN Drive_cursor FETCH NEXT FROM Drive_cursor INTO @Drive WHILE @@FETCH_STATUS = 0 BEGIN select @sql = 'insert into #fsutil exec master.dbo.xp_cmdshell ''fsutil volume diskfree '+ @Drive+':''' exec(@sql) 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 TRUNCATE TABLE #fsutil FETCH NEXT FROM Drive_cursor INTO @Drive END CLOSE Drive_cursor DEALLOCATE Drive_cursor END 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!
Pieter