SQL Server Database File Size and Free Space Revisited

I recently had a comment posted to one of my first articles I wrote way back in 2012 concerning adding a column that detailed the used space as a %age. So without any further ado, here is an updated version of a script originally blogged about here.

select
        sf.FILEID AS [File ID],
        [File Size in MB] = convert(decimal(12,2),round(sf.size/128.000,2)),
        [Max Size in MB] = convert(decimal(12,2),round(sf.maxsize/128.000,2)),
        [Space Used in MB] = convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)),
        [Free Space in MB] = convert(decimal(12,2),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)) ,
        [Free Space in %] = convert(decimal(12,2),round(100*(sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)) ,
	    [Used Space in %] = convert(decimal(12,2),round(100*(fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)) ,
        [File Name] = left(sf.NAME,30),
        [File Location] = left(sf.FILENAME,100)
        from dbo.sysfiles sf
        order by fileid asc

Defaulting Data Compression on Creating Indexes

Here’s a theoretical situation;

  • you have a vendor database, that is used solely for staging very large tables, and the tables are created and dropped regularly.
  • The tables can exist for a few hours, or for days.
  • This database has only 1 type of table, with a few indexes created on it, none of which are compressed.
  • You cannot change the code.
  • However, when compressed, these tables/indexes can save up to 90% of space.
  • And with space being a commodity, the idea that we can allocate a disk of a few hundred gb as opposed to several tb is very appealing to all concerned
  • compression can possibly even make the process run faster.

Continue reading “Defaulting Data Compression on Creating Indexes”

Reports on Disk Space and Space in Files

Previously I have blogged about how to check for free space in a database at a file level; a file for a database can be very large, but the amount of data stored within the file can be very little. When initializing a file for SQL, it is generally best practice to create it to the size you think it is going to be so that auto-grow is not initialized as this can cause a loss in performance. Invariably however, databases do grow, and there is a finite amount of space to grow into, which is why in another blog I wrote bout the “xp_fixeddrives” stored proc which returns the space remaining on the disks which are attached to that instance of SQL.

So instead of checking these at random times, or finding out that we are running out of space when I am told we have run out of space, I have written a script that checks for the size of the remaining space on the disks on our SQL Servers, and if those disks hit a certain threshold then I send an email alert to the DBA Team that the space is running low. This also triggers another email that sends a file space report for each of the databases that are hosted on the disk that is reporting as running low on disk space. Some of the drives don’t host databases, but I still like to send an email as it hints that something is wrong with the server.

The job runs via SQL Agent every 15 minutes. The good thing about these reports is that, if at 2am I see that we are running low on disk space, then I just check the next email that shows how much free space is left, so then I can determine the urgency of the warning. This relies on me to know the workload of each of our databases, but at least I have an opportunity to do something about it before SQL reports errors.

I’m not going to go into how to setup database mail here, because there are plenty of good articles on how to do that on the web. If you don’t have it set up then I recommend it, as you’ll find that it is an effective way of reporting warnings to a team who need to know and can rectify and potential issues before  they actually occur.

You’ll need to make some changes to get it working (you’ll have to change the drive letters, or maybe you don’t have that many drives etc, and where it says ‘dbname’, you have to enter the name of your database) but all these are fairly straightforward.

If anyone has any suggestions to improve then let me know and I’ll update the script.

declare @N_MB_Free int
declare @O_MB_Free int
declare @M_MB_Free int
declare @L_MB_Free int
declare @B_MB_Free int
declare @C_MB_Free int
declare @E_MB_Free int
declare @FileSpaceReport bit = 0

Declare @FreeSpace table
( [Drive] char(1),
[MB_Free] int )

insert into @FreeSpace exec xp_fixeddrives

declare @reportCompletion Nvarchar(100)
SELECT @reportCompletion = GETDATE()

DECLARE
@BodyTxt03 VarChar(150),
@tableHTML NVARCHAR(MAX) ;

DECLARE

@BodyBackground NVARCHAR(50) ,
@BodyFont NVARCHAR(50),
@BodyFontColour NVARCHAR(50),
@BodyFontSize TINYINT,
@BodyText NVARCHAR(MAX),
@TableBorderWidth INT,
@TableBorderStyle NVARCHAR(20),
@TableBorderColour NVARCHAR(50),
@TablePadding INT,
@TableHeaderBackground NVARCHAR(50),
@TableHeaderFont NVARCHAR(50),
@TableHeaderFontColour NVARCHAR(50),
@TableHeaderFontSize TINYINT,
@TableDataBackground NVARCHAR(50),
@TableDataFont NVARCHAR(50),
@TableDataFontColour NVARCHAR(50) ,
@TableDataFontSize TINYINT,
@Subjecttext NVARCHAR(200)

--create table to send email

set @BodyBackground = 'light blue'
set @BodyFont = 'arial'
set @BodyFontColour = 'black'
set @BodyFontSize = 12
set @BodyText = NULL
set @TableBorderWidth= 1
set @TableBorderStyle = 'hidden'
set @TableBorderColour = 'black'
set @TablePadding = 1
set @TableHeaderBackground = 'gray'
set @TableHeaderFont = 'arial'
set @TableHeaderFontColour = 'Black'
set @TableHeaderFontSize = 12
set @TableDataBackground = 'light blue'
set @TableDataFont = 'arial'
set @TableDataFontColour = 'black'
set @TableDataFontSize = 12

SET @BodyTxt03 = 'The Report completed at '+ @reportCompletion +'. The following stored proc was executed to discover disk space: exec xp_fixeddrives'

SET @tableHTML =

N'<HEAD>'+
N'<STYLE type="text/css">'+
N'BODY { background-color:' + @BodyBackground + '; font-family:' + @BodyFont + '; font-size:' + CAST(@BodyFontSize AS NVARCHAR) + '; color:' + @BodyFontColour + ' }'+
N'TABLE { border-collapse:collapse }'+
N'TH { background-color:' + @TableHeaderBackground + '; font-size:' + CAST(@TableHeaderFontSize AS NVARCHAR) + '; color:' + @TableHeaderFontColour + ' }'+
N'TD { background-color:' + @TableDataBackground + '; font-size:' + CAST(@TableDataFontSize AS NVARCHAR) + '; color:' + @TableDataFontColour + ' }'+
N'TH, TD { border-width:' + CAST(@TableBorderWidth AS NVARCHAR) + '; border-style:' + @TableBorderStyle + '; border-color:' + @TableBorderColour + '; padding:' + CAST(@TablePadding AS NVARCHAR) + ' }'+
N'HTML { overflow: scroll }'+
N'</STYLE>'+
N'</HEAD>'+
N'<H2>Disk Size Database Report </H2>' +

N'<H4>'+ @BodyTxt03 +'</H4>' +

+ N'<table border="1" align = "Left">' +
N'<tr><th align = "left">Drive</th><th align = "left">MB Free</th>' +
CAST ( (select

td =[Drive],'',
td = [MB_Free],''

from @FreeSpace

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
+N'<table>'
+N'</tr>'
+N'</table>'
;

--check for each of the drives if the threshold has been hit. if so esend an email.
--If drive hosts adatabase we want to monitor then set the bit to 1, which triggers the 2nd part of this script
select @N_MB_Free = MB_Free from @FreeSpace where Drive = 'N'
if @N_MB_Free < 102400
BEGIN
set @Subjecttext = 'N Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @O_MB_Free = MB_Free from @FreeSpace where Drive = 'O'
if @O_MB_Free < 102400
BEGIN
set @Subjecttext = 'O Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @M_MB_Free = MB_Free from @FreeSpace where Drive = 'M'
if @M_MB_Free < 102400
BEGIN
set @Subjecttext = 'M Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @L_MB_Free = MB_Free from @FreeSpace where Drive = 'L'
if @L_MB_Free < 200000
BEGIN
set @Subjecttext = 'L Drive on dbname is running low on Disk Space'
set @FileSpaceReport = 1
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @B_MB_Free = MB_Free from @FreeSpace where Drive = 'B'
if @B_MB_Free < 20480
BEGIN
set @Subjecttext = 'B Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @C_MB_Free = MB_Free from @FreeSpace where Drive = 'C'
if @C_MB_Free < 10240
BEGIN
set @Subjecttext = 'C Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

select @E_MB_Free = MB_Free from @FreeSpace where Drive = 'E'
if @E_MB_Free < 20480
BEGIN
set @Subjecttext = 'E Drive on dbname is running low on Disk Space'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';
END

if @FileSpaceReport = 1
BEGIN
--We create table and send our dbname filegroup space report
--sending another email to show how much space we have left in the files
declare
@FileID int, @FileGroupName varchar (50), @FileSizeMB decimal(12,2), @MaxSizeMB decimal (12,2),
@SpaceUsedMB decimal(12,2), @FreeSpaceMB decimal(12,2), @FreeSpacePercentage decimal (12,2), @FileLocation nvarchar (80)

Declare @dbTableVar table(
[File ID] int
,[File Name] varchar(50)
,[File Size in MB] decimal(12,2)
,[Max Size in MB] decimal(12,2)
,[Space Used in MB] decimal(12,2)
,[Free Space in MB] decimal(12,2)
,[Free Space in %] decimal(12,2)
,[File Location] varchar (80)
)

DECLARE DB_cursor CURSOR
FOR SELECT fileID from dbname.dbo.sysfiles
OPEN DB_cursor
FETCH NEXT FROM DB_cursor into @fileID

WHILE @@FETCH_STATUS = 0
BEGIN
select @FileID = MAX (sf.FILEID)
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileGroupName = MAX(left(sf.NAME,50))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileSizeMB = MAX (convert(decimal(12,2),round(sf.size/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @MaxSizeMB = MAX (convert(decimal(12,2),round(sf.maxsize/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @SpaceUsedMB = MAX (convert(decimal(12,2),round(fileproperty(sf.name,'SpaceUsed')/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FreeSpaceMB = MAX (convert(decimal(12,2),round((sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FreeSpacePercentage = MAX (convert(decimal(12,2),round(100*(sf.size-fileproperty(sf.name,'SpaceUsed'))/128.000,2)/(sf.size/128.000)))
from dbname.dbo.sysfiles sf
where fileid = @FileID
select @FileLocation = MAX(left(sf.NAME,50))
from dbname.dbo.sysfiles sf
where fileid = @FileID

insert into @dbTableVar values (@FileID, @FileGroupName, @FileSizeMB, @MaxSizeMB, @SpaceUsedMB, @FreeSpaceMB, @FreeSpacePercentage, @FileLocation)

FETCH NEXT FROM db_cursor
INTO @fileID
END
CLOSE db_cursor
DEALLOCATE db_cursor

SELECT @reportCompletion = GETDATE()
DECLARE
@BodyTxt10 VarChar(150)

-- Send Email message stating processing status
--create table to send email

set @Subjecttext = 'Filegroup Report for dbname'

SET @BodyTxt10 = 'The Report completed at '+ @reportCompletion

SET @tableHTML =

N'<HEAD>'+
N'<STYLE type="text/css">'+
N'BODY { background-color:' + @BodyBackground + '; font-family:' + @BodyFont + '; font-size:' + CAST(@BodyFontSize AS NVARCHAR) + '; color:' + @BodyFontColour + ' }'+
N'TABLE { border-collapse:collapse }'+
N'TH { background-color:' + @TableHeaderBackground + '; font-size:' + CAST(@TableHeaderFontSize AS NVARCHAR) + '; color:' + @TableHeaderFontColour + ' }'+
N'TD { background-color:' + @TableDataBackground + '; font-size:' + CAST(@TableDataFontSize AS NVARCHAR) + '; color:' + @TableDataFontColour + ' }'+
N'TH, TD { border-width:' + CAST(@TableBorderWidth AS NVARCHAR) + '; border-style:' + @TableBorderStyle + '; border-color:' + @TableBorderColour + '; padding:' + CAST(@TablePadding AS NVARCHAR) + ' }'+
N'HTML { overflow: scroll }'+
N'</STYLE>'+
N'</HEAD>'+
N'<H2>File Size Database Report </H2>' +

N'<H4>'+ @BodyTxt10 +'</H4>' +

+N'<table border="1" align = "Left">' +
N'<tr>
<th align = "left">File ID</th>
<th align = "left">File Name </th>
<th align = "left">Total File Size (MB)</th>
<th align = "left">Max File Size (MB)</th>
<th align = "left">Space Used (MB)</th>
<th align = "left">Free Space (MB)</th>
<th align = "left">Free Space (%age)</th>
<th align = "left">File Location</th>' +
CAST ( (select
td = [File ID],'',
td = [File Name],'',
td = [File Size in MB],'',
td = [Max Size in MB],'',
td = [Space Used in MB],'',
td = [Free Space in MB],'',
td = [Free Space in %],'',
td = [File Location],''

from @dbTableVar

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
+N'<table>'
+N'</tr>'
+N'</table>'
;
if @tableHTML <> ''
begin
set @tableHTML = @tableHTML
end
else
begin
set @tableHTML = 'No batches processed'
end

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'enteremail@here',
@subject = @Subjecttext,
@body = @tableHTML,
@body_format = 'HTML';

END

List of Disks and Free Space

Recently I have been supporting our move from TFS from 2010 to TFS 2012. The database instances is stored on a server that I do not have log on rights to. During the upgrade, it became clear that one of the tables was growing at a rapid rate. This is because rows were being moved from one table to another, and there were a lot of rows to move: I came back from my Christmas break on the 31st and apparently it had been running from the 28th!

So I needed to know how much space the disk had left and whether we needed to increase it. But as I did not have access to the server I needed to check how much space we had before requesting a disk increase. By running the stored proc below I was able to check how much free space we had left on the disks.

EXEC master xp_fixeddrives

I was able to check which disk it was by running the query in my previous post

https://phoenixultd.wordpress.com/2012/12/24/sql-server-database-file-size-and-free-space/

Disabling Flight Recorder Logging in SSAS

Hello folks, I hope you all had a relaxing holiday break.

Welcome to a quick blog on SSAS optimisation. There’s a few quick wins that you can do w/r/t SSAS that can optimise the instance. One of these has to do with a feature called the FlightRecorder log. This log is by default set to ON, but really, unless you are trying to diagnose an issue, it doesn’t really provide any benefit to just having it switched on writing to the log location, particularly if the log location is on the same drive as the data location. To optimize your server, it’s safe to turn the FlightRecorder off and it is simple enough to turn back on and start collecting data again.

Continue reading “Disabling Flight Recorder Logging in SSAS”

SQL Server Database File Size and Free Space

(There is a more up to date version of this script here)
Hey folks, and welcome to my first proper blog post. One of the things that I like to monitor through my daily checks in the file size and the free space of each file within the databases. During a busy loading period, some databases can grow massively, so it’s important to keep an eye on the growth. Through the UI, SSMS only gives us the size of the files and what the max size is that they can grow to, which is clearly not very useful. Fortunately,  a quick query on the dbo.sysfiles of each databases that we want to monitor gives us some info:

utility_sys_files

but this isn’t entirely too useful. For starters the size is in KB. This makes sense as databases store data in 8kb sized files. Whilst it may be OK for a small database like this one, our data warehouses are far to big for us to find sizes in KB useful. Also, we can infer the remaining space, but again it’s not too helpful by having to figure this out for ourselves.

Continue reading “SQL Server Database File Size and Free Space”