Monitor Dataspaces

The procedure Monitor Dataspaces creates an overview of all datafiles that are part of the databases. File size is monitored as well as free space within the files. For every file the growth is compared to the size. If a growth rate is not compliant with the rules, the file is reported.  The rules can be defined in the settings. The defaults are:

  • If the file is smaller than 5GB (Lower Limit) the growth should be at least 100MB (Small Growth).
  • If the file is between 5GB and 10GB the growth should be at least 500MB (Medium Growth)
  • If the file is lager than 10GB (Upper Limit) the growth should be at least 1000MB (Large Growth)
  • If the growth is a percentage it should be at least 10% (Minumum Percentage Growth)

The Minimum Percentage Free Space can be set but is not yet used. This setting will be ignored.
Tip: If you don’t want any percentage growth, set the Minimum Percentage Growth to a value larger than 10, so all databases with default settings will show.

The following properties are in the report:

  • LinkedServer
  • DatabaseName
  • LogicalName: The logical name of the file in SQL Server.
  • FileID: The fileID of the file in the database.
  • File_Size_MB: the file size on disk.
  • Space_Used_MB: The amount of space in the file used by data.
  • Free_Space_MB: Free space in the datafile. (MB).
  • Free_Space_Prc: Free space in the datafile. (percentage).
  • Growth: The growth set in the properties for the file.
  • Perc: 1 = growth in percent / 0 = growth in MB.
  • PercGrowth: The growth percentage compared to the filesize.
  • FileName: The physical file name.
  • LogDate: The date this procedure ran.
  • CorrectCommand: The SQL statement needed to correct the settings for this datafile. this statement can be executed over a Linked Server connection. This includes a minimum size of 100 MB for each datafile.

How to use it

The procedure usp_Report_Dataspaces will crawl all Linked Servers and retrieve information on the file size of all database files it finds. Information for these files is stored in the table tbl_Dataspaces. This concerns information about file size and datasize within these files.

Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:

DECLARE @RC int
DECLARE @Recipient varchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)

SET @Recipient = ‘yourname@yourdomain.com’
SET @ExceptionList = ‘ServerNotToProcess’
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’

EXECUTE @RC = [dbo].[usp_Report_DataSpaces] 
   @Recipient
  ,@ExceptionList
  ,@MailStats
  ,@SqlVersion
  ,@Separator
;

  • The recipient is the emailaddress where to send the results to.
  • The ExceptionList is a comma separated list of servers that should not be monitored. 
  • Mailstats = 1 means that you will recieve an email if there is no useful information te report, telling you exactly this. If you set this to 0 then no email is sent unless there is something to report.
  • SqlVersion = 0 means report all (possible) Sql Server Verions. If you set this to 11, then only Sql Server 2012 will be monitored and reported.
  • Seperator is the separator character for the comma separated list. You can choose any single character for this.

We recommend that you schedule this procedure on a daily basis after the Monitor Servers procedure has run. Any servers not found by the Monitor Servers will be skipped, saving time.

Collected Information

The report contains all collected information

  • LinkedServer
  • DatabaseName
  • LogicalName: The logical name of the file in SQL Server.
  • FileID: The fileID of the file in the database.
  • File_Size_MB: the file size on disk.
  • Space_Used_MB: The amount of space in the file used by data.
  • Free_Space_MB: Free space in the datafile. (MB).
  • Free_Space_Prc: Free space in the datafile. (percentage).
  • Growth: The growth set in the properties for the file.
  • Perc: 1 = growth in percent / 0 = growth in MB.
  • PercGrowth: The growth percentage compared to the filesize.
  • FileName: The physical file name.
  • LogDate: The date this procedure ran.