The database size can be obtained with system stored procedure sp_helpdb that is included in SQL 2008 and 2005 (not sure about 2000). We cannot use this stored procedure directly with PRTG as it returns to many rows and fields, so here is what we have to do:

Step 1

Create a new stored procedure using the following code

CREATE PROCEDURE [dbo].[spSpaceUsed]
	@name nvarchar(24)

AS
BEGIN
	SET NOCOUNT ON
	-- Create a tempory table
	DECLARE @SpaceUsed TABLE
	 (
	  name nvarchar(24), 
	  db_size nvarchar(13),
	  owner nvarchar(24),
	  dbid smallint,
	  created char(11),
	  status varchar(340),
	  compatibility_level tinyint
	  )

	--Populate the table
	INSERT INTO @SpaceUsed EXEC sp_helpdb

	--Select the size of the specified table
	SELECT
	  CONVERT (DECIMAL, REPLACE (db_size, 'MB', ''))
	  AS [size]
	  FROM @SpaceUsed
	  WHERE name = @name
END

Step 2

Add a new MSQL sensor

On your PRTG server, add a new MSQL sensor. Fill in the required fields and in the "SQL Expression" field enter

exec dbo.spSpaceUsed YOUR_DATABASE_NAME

In the channels tab select the value channel and enter MB as unit.

I found an easier way of performing this request after a bit of wrestling. In the SQL Query field I used

select (convert(float,Size)*(1024*8))/1024/1024 from DatabaseName.dbo.sysfiles where GroupId = 1

Just replace "DatabaseName" with the database you are wanting to query.

Thanks for the reply

You are right, that is an easier way, but it will also include the log file size and not only the database size.

You are correct, I need to sit down and decide exactly what it is that i am wanting to monitor. Thanks for pointing that out.

If i use your procedure and your sql expression, i receive following error message: "Could not perform query: Database Server Error: Zeichenfolgen- oder Binärdaten würden abgeschnitten." (i'm not sure if my translation would be the same as yours - so it's german)

In that case the system stored procedure sp_helpdb returns “lager” data than the tempory table @SpaceUsed is set up for.

Run sp_helpdb in a new query window, see what the output is and adjust the field sizes of the tempory table accordingly.

After adjusting the field sizes (name is up to 60 digits!) the error was removed. But the value field is '0' (no error but nothing in). When i run the sp_helpdp everything seems okay and all values correspond with the size of the db files.

SELECT SUM (row_count)
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID('myTableName')   
AND (index_id=0 or index_id=1);

Just checked answer from first reply on SQL Server 2008 and it doesn't work. After some RTFM'ing change some type of fields in SP. Working code below:

CREATE PROCEDURE [dbo].[spSpaceUsed]
	@name nvarchar(24)

AS
BEGIN
	SET NOCOUNT ON
	-- Create a tempory table
	DECLARE @SpaceUsed TABLE
	 (
	  name sysname, 
	  db_size nvarchar(13),
	  owner sysname,
	  dbid smallint,
	  created nvarchar(11),
	  status nvarchar(600),
	  compatibility_level tinyint
	  )

	--Populate the table
	INSERT INTO @SpaceUsed EXEC sp_helpdb

	--Select the size of the specified table
	SELECT
	  CONVERT (DECIMAL, REPLACE (db_size, 'MB', ''))
	  AS [size]
	  FROM @SpaceUsed
	  WHERE name = @name
END

Note: This article is deprecated! Please refer to the answer posted on November 30th, 2016.

This article applies to PRTG Network Monitor 13.2 or later

Monitoring the Size of a Microsoft SQL Database

With PRTG, you can retrieve the size of a MS SQL database with the native Microsoft SQL Sensor. It is possible to request the overall size of your database, used space, and free space by providing according SQL queries in the Microsoft SQL sensor settings.

Steps to Go

For each of the attributes size, used space, and free space add a Microsoft SQL sensor in PRTG. Provide the credentials for your database in section Database in the add sensor dialog and paste the SQL queries as given below. You can create all three sensors, or only those you need depending on your requirements.

• Monitoring the size of your DB: Create a sensor called DB-FileSize. In section SQL-Expression, provide the following query: select convert(decimal(12,2),round(a.size/128.000,2)) as FileSizeMB from dbo.sysfiles a where fileid = 1;

• Monitoring the used space of your DB: Create a sensor called DB-SpaceUsed. In section SQL-Expression, provide the following query: select convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB from dbo.sysfiles a where fileid = 1;

• Monitoring the free space of your DB: Create a sensor called DB-SpaceFree. In section SQL-Expression, provide the following query: select convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as FreeSpaceMB from dbo.sysfiles a where fileid = 1;

• For each sensor, choose Process numerical result in section Postprocessing in the add sensor dialog.

The shown result in PRTG corresponds to the database size, respectively utilization, in megabyte.

Notes

As mentioned above, it is sufficient to create, for example, only the DB-UsedSpace sensor to monitor the size of your database. This sensor retrieves only the amount of data (perhaps relevant for a DB dump).

However, consider that depending on your DB setting for autogrowth the value of the physical size of the database can be relevant, too, for example, regarding a file backup. Imagine that you have a database of 1TB and the default setting for autogrowth is 10%. Then the used disk space will increase by 100GB immediately due to enlarging of the files when the defined threshold is reached. Thus, we additionally recommend to add the DB-SpaceFree sensor (of course, depending on your needs).

Note: In contrast to the solution with stored procedures posted on April 5th, 2011, the solution as described above works agentless.

Can you tell me the way for the SQL V2 sensor?

Thanks

It is quite similar for the SQL v2 sensor, the SQL statement now has to be provided in a file.

Hi,

I had try using the SQL v2 sensor but not success. The SQL Query file I use is:

select convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)) as SpaceUsedMB from dbo.sysfiles a where fileid = 1;

Could you help to provide more explanation?

Please note that our support does not cover the actual SQL statement. If you get a PRTG-related error message, please contact [email protected]. Please include screenshots of the sensor in question. Helpful screenshots would cover the overview tab, settings tab, and logs tab.

Hi,

I've tried the method from Martina here but I'm getting the error: Column "FILESIZE" could not be found in the returned 1 datatables.

Although when I runs this query I do see the column "FILESIZE" listed. What is wrong?

Hi there,

Please make sure that you have set all parameters and settings properly as described in the guide of Martina. Should this be set properly, activate the "Write sensor result to disk (Filename: "Result of Sensor [ID].txt")" in the sensor settings and show us the logs located on the according remote probe under "C:\ProgramData\Paessler\PRTG Network Monitor\Logs (Sensors)\".

Hi,

I started using this script in PRTG and its working for getting database size info, the output in mssms shows the info for database size and log size. How do i get both row info into PRTG? i need to monitor both log and database size and i don't want to create another sensor for this.

LOG xyzdb_log NULL E:\Log\xyzdblog.ldf 158976.00 383.77 158592.23 99.76 By 2048 MB - Restricted to 2048 GB ROWS xyxdb PRIMARY D:\Data\xyzdb.mdf 1261568.00 1217775.63 43792.38 3.47 By 4096 MB - Unrestricted

-- ___ ___ _____ ___ --| _ \ _ \_ _/ __| --| _/ / | || (_ | --|_| |_|_\ |_| \___| -- NETWORK MONITOR --------------------- -- [SQL] Database Size monitoring --------------------- -- Description: This query will show the current size information for the given database --------------------- -- Code courtesy of Tri Effendi SS of stack exchange -- http://dba.stackexchange.com/a/7921 -- Original post: http://dba.stackexchange.com/a/7921 SELECT [DESCRIPTION] = A.TYPE_DESC ,[FILE_Name] = A.name ,[FILEGROUP_NAME] = fg.name ,[File_Location] = A.PHYSICAL_NAME ,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024 ,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024 ,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024 ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id WHERE A.type_desc LIKE 'ROWS' order by A.TYPE desc, A.NAME;

Thanks Gilles

Hi there,

Unfortunately, we can't really support the script, we just adopted it from the following source:
http://dba.stackexchange.com/a/7921

With a bit of altering it should be possible though to give out both results in columns and process them in the SQL Sensor.

Best regards.

Your output does not exactly mirror the script you posted - I wondered at first why I only get ROWS until I looked again and saw the WHERE filter that caused this...

How ever - you face a few challenges with this - you possibly could have multiple files - not just two - per database - e.g. multiple log-file files.. this can be pretty challenging..

How ever - I like challenges... look at the script below - I think that would help you...

USE [master]

SELECT 
	MYROWS.DATABASENAME, 
	SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.[FREESPACE_%]) AS DATA_FREESPACE_PERCENT,
	SUM(MYLOGS.FILESIZE) AS LOG_FILESIZE, SUM(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, SUM(MYLOGS.FREESPACE) AS LOG_FREESPACE, SUM(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT 
FROM
	(
		SELECT 
			DB_NAME() AS DATABASENAME
			,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
			,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
			,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
			,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
		FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
		WHERE A.type_desc LIKE 'ROWS'
	) MYROWS 
	LEFT JOIN 
	(
		SELECT 
			DB_NAME() AS DATABASENAME
			,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024
			,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024
			,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024
			,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
		FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id 
		WHERE A.type_desc LIKE 'LOG'
	) MYLOGS
	ON MYROWS.DATABASENAME = MYROWS.DATABASENAME
GROUP BY 
	MYROWS.DATABASENAME

This first statement USE [master] - you could replace it with USE @PRTG (I think @prtg was the parameter injection for SQL but I might be wrong) and inject the DATABASE NAME as a parameter to the SQL script directly from PRTG...

what you get is this:

Hope this helps... regards

Florian Rossmark

www.it-admins.com

Can i create query not to specific database but just to SQL-server, which will show just the status of the DataBases? For example i know master database store status of any other databases on the server.

Hey everyone, I used the query from Florian (thx to you) but I need to tune it, for some databases with more than one file, the free space in percent was not displayed correctly.

Please check out this

SELECT MYROWS.DATABASENAME, SUM(MYROWS.FILESIZE) AS DATA_FILESIZE, SUM(MYROWS.USEDSPACE) AS DATA_USEDSPACE, SUM(MYROWS.FREESPACE) AS DATA_FREESPACE, SUM(MYROWS.FREESPACE)/SUM(MYROWS.FILESIZE)*100 AS DATA_FREESPACE_PERCENT, AVG(MYLOGS.FILESIZE) AS LOG_FILESIZE, AVG(MYLOGS.USEDSPACE) AS LOG_USEDSPACE, AVG(MYLOGS.FREESPACE) AS LOG_FREESPACE, AVG(MYLOGS.[FREESPACE_%]) AS LOG_FREESPACE_PERCENT FROM ( SELECT DB_NAME() AS DATABASENAME ,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024 ,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024 ,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024 ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id WHERE A.type_desc LIKE 'ROWS' ) MYROWS FULL JOIN ( SELECT DB_NAME() AS DATABASENAME ,[FILESIZE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) * 1024 * 1024 ,[USEDSPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) * 1024 * 1024 ,[FREESPACE] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) * 1024 * 1024 ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id WHERE A.type_desc LIKE 'LOG' ) MYLOGS ON MYROWS.DATABASENAME = MYROWS.DATABASENAME GROUP BY MYROWS.DATABASENAME