New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


How do I monitor the size of a Microsoft SQL Server Database?

Votes:

1

Your Vote:

Up

Down

I have a Microsoft SQL Server 2008 that is used for Sharepoint. I would like to monitor the size of the database. How can I accomplish this?

I do not want to monitor the file size using WMI, because UNC paths are not allowed and that would be a lot of mapped drives to monitor all the SQL Server databases I am responsible for.

Thanks in Advance

custom-query database ms-sql prtg query sql sql-queries sql-server

Created on Apr 5, 2011 3:37:21 PM by  John D Johnson (1) 1

Last change on Nov 30, 2016 3:41:23 PM by  Martina Wittmann [Paessler Support]



Best Answer

Accepted Answer

Votes:

0

Your Vote:

Up

Down

This article applies to PRTG Network Monitor 16 or later

Monitoring a Microsoft SQL Database

PRTG Network Monitor comes with a built-in Microsoft SQL v2 Sensor that executes a defined query and shows you several types of query execution times, the number of addressed rows and also defined values of the monitored MS SQL Database. Refer to the manual for more detailed information.

If you are interested in monitoring the current file size, used space, and free space (in bytes and percent), all you need to do is provide a suitable query for the Microsoft SQL v2 Sensor. Include the query we offer in this article in your monitoring with PRTG Network Monitor and check out the size of your MS SQL database.

MS SQL Monitoring with PRTG Click here to enlarge.

Follow the steps below to setup your MS SQL Database sensor and enjoy the results!

Requirements

  • MSSQL sensors in general need to work in your PRTG installation against the actual target host.
  • Make sure that your database autogrowth feature is disabled. If not, you will need to configure the limits accordingly in your PRTG settings.

The MS SQL Query

-- ___ ___ _____ ___
--| _ \ _ \_   _/ __|
--|  _/   / | || (_ |
--|_| |_|_\ |_| \___|
--    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;   

Version History

DateVersionNotes
November 30rd, 20161.0Initial Release

Sensor Setup

  1. Save the above query as PRTG-DatabaseSize.sql in %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
  2. In your PRTG installation, go to your MS SQL device that you want to monitor and add a new Microsoft SQL v2 Sensor.
  3. In the sensor settings, configure the sensor in the following way:
Parameter
DatabaseThe database you want to check
Data ProcessingProcess Data Table
Select Channel Value byColumn Name
Channel #1
Channel NameFile Size
Column NameFILESIZE
ModeAbsolute
UnitBytesDisk
Channel #2
Channel NameUsed Space
Column NameUSEDSPACE
ModeAbsolute
UnitBytesDisk
Channel #3
Channel NameFree Space
Column NameFREESPACE
ModeAbsolute
UnitBytesDisk
Channel #4
Channel NameFree Space in %
Column NameFREESPACE_%
ModeAbsolute
UnitPercent

Save your settings and start monitoring!

If you need any further customizations for your environment, feel free to implement them. If you encounter any bugs, feel free to share them :)


Note: The query is provided as is and may or may not work with your installation. Please understand that we cannot provide in-depth technical support for custom scripts.


More

Created on Nov 30, 2016 3:34:55 PM by  Martina Wittmann [Paessler Support]



17 Replies

Votes:

1

Your Vote:

Up

Down

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.

Created on Apr 5, 2011 7:28:59 PM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,805) 3 4

Last change on Apr 28, 2011 3:39:54 PM by  Torsten Lindner [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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

Created on Apr 5, 2011 8:15:59 PM by  John D Johnson (1) 1



Votes:

0

Your Vote:

Up

Down

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

Created on Apr 6, 2011 12:09:46 PM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,805) 3 4



Votes:

0

Your Vote:

Up

Down

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.

Created on Apr 6, 2011 2:03:28 PM by  John D Johnson (1) 1



Votes:

0

Your Vote:

Up

Down

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)

Created on Apr 28, 2011 3:01:24 PM by  AGriese (0) 1



Votes:

0

Your Vote:

Up

Down

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.

Created on Apr 28, 2011 3:26:44 PM by  PRTGToolsFamily [prtgtoolsfamily.com] (12,805) 3 4



Votes:

0

Your Vote:

Up

Down

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.

Created on May 4, 2011 1:43:35 PM by  AGriese (0) 1



Votes:

0

Your Vote:

Up

Down

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

Created on Apr 9, 2013 5:59:09 PM by  Michael Hogan (0) 1

Last change on Apr 9, 2013 6:23:36 PM by  Konstantin Wolff [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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

Created on Apr 23, 2013 10:44:07 AM by  Gennady (60) 2 1



Votes:

0

Your Vote:

Up

Down

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.

Created on Jun 14, 2013 2:54:02 PM by  Gerald Schoch [Paessler Support]

Last change on Nov 30, 2016 3:38:05 PM by  Martina Wittmann [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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

Thanks

Created on Oct 10, 2016 6:31:51 AM by  Torsten Schöne (0)



Votes:

0

Your Vote:

Up

Down

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

Created on Oct 10, 2016 10:51:46 AM by  Arne Seifert [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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?

Created on Oct 11, 2016 3:14:08 AM by  italert (0)



Votes:

0

Your Vote:

Up

Down

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

Created on Oct 11, 2016 11:00:11 AM by  Arne Seifert [Paessler Support]



Accepted Answer

Votes:

0

Your Vote:

Up

Down

This article applies to PRTG Network Monitor 16 or later

Monitoring a Microsoft SQL Database

PRTG Network Monitor comes with a built-in Microsoft SQL v2 Sensor that executes a defined query and shows you several types of query execution times, the number of addressed rows and also defined values of the monitored MS SQL Database. Refer to the manual for more detailed information.

If you are interested in monitoring the current file size, used space, and free space (in bytes and percent), all you need to do is provide a suitable query for the Microsoft SQL v2 Sensor. Include the query we offer in this article in your monitoring with PRTG Network Monitor and check out the size of your MS SQL database.

MS SQL Monitoring with PRTG Click here to enlarge.

Follow the steps below to setup your MS SQL Database sensor and enjoy the results!

Requirements

  • MSSQL sensors in general need to work in your PRTG installation against the actual target host.
  • Make sure that your database autogrowth feature is disabled. If not, you will need to configure the limits accordingly in your PRTG settings.

The MS SQL Query

-- ___ ___ _____ ___
--| _ \ _ \_   _/ __|
--|  _/   / | || (_ |
--|_| |_|_\ |_| \___|
--    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;   

Version History

DateVersionNotes
November 30rd, 20161.0Initial Release

Sensor Setup

  1. Save the above query as PRTG-DatabaseSize.sql in %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
  2. In your PRTG installation, go to your MS SQL device that you want to monitor and add a new Microsoft SQL v2 Sensor.
  3. In the sensor settings, configure the sensor in the following way:
Parameter
DatabaseThe database you want to check
Data ProcessingProcess Data Table
Select Channel Value byColumn Name
Channel #1
Channel NameFile Size
Column NameFILESIZE
ModeAbsolute
UnitBytesDisk
Channel #2
Channel NameUsed Space
Column NameUSEDSPACE
ModeAbsolute
UnitBytesDisk
Channel #3
Channel NameFree Space
Column NameFREESPACE
ModeAbsolute
UnitBytesDisk
Channel #4
Channel NameFree Space in %
Column NameFREESPACE_%
ModeAbsolute
UnitPercent

Save your settings and start monitoring!

If you need any further customizations for your environment, feel free to implement them. If you encounter any bugs, feel free to share them :)


Note: The query is provided as is and may or may not work with your installation. Please understand that we cannot provide in-depth technical support for custom scripts.


More

Created on Nov 30, 2016 3:34:55 PM by  Martina Wittmann [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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?

Created on Dec 25, 2016 2:27:39 PM by  tomer (0) 1



Votes:

0

Your Vote:

Up

Down

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)\".

Created on Dec 26, 2016 2:13:34 PM by  Dariusz Gorka [Paessler Support]



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.