What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

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

Votes:

2

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 it would mean 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

Last change on Dec 9, 2020 12:22:43 PM by  Brandy Greger [Paessler Support]



Best Answer

Accepted Answer

Votes:

2

This article applies as of PRTG 22

Monitoring a Microsoft SQL database

PRTG comes with a Microsoft SQL v2 sensor. It executes a defined query, shows you several types of query execution times, the number of addressed rows, and defined values of the monitored Microsoft SQL database.

If you want to monitor the current file size, used space, and free space (in bytes and percent), you need to provide the query below for the Microsoft SQL v2 sensor.

Microsoft SQL monitoring Click to enlarge.

Follow the steps below to set up your Microsoft SQL v2 sensor:

Requirements

  • Microsoft SQL sensors in a PRTG installation generally need to be able to work against the actual target host.
  • Make sure that your database's autogrowth feature is disabled. Otherwise, you need to configure the limits accordingly in your PRTG settings.

The Microsoft 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 30th, 20161.0Initial Release

Sensor setup

  1. Save the query above as PRTG-DatabaseSize.sql in %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
  2. In your PRTG installation, go to the Microsoft 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 encounter any bugs, feel free to share them.

Note: The query is provided as is and may or may not work with your installation. 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]

Last change on Jan 4, 2023 9:22:47 AM by  Brandy Greger [Paessler Support]



22 Replies

Votes:

1

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

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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



Votes:

0

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

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



Votes:

0

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



Votes:

0

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

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

Thanks

Created on Oct 10, 2016 6:31:51 AM



Votes:

0

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

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



Votes:

0

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.

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



Accepted Answer

Votes:

2

This article applies as of PRTG 22

Monitoring a Microsoft SQL database

PRTG comes with a Microsoft SQL v2 sensor. It executes a defined query, shows you several types of query execution times, the number of addressed rows, and defined values of the monitored Microsoft SQL database.

If you want to monitor the current file size, used space, and free space (in bytes and percent), you need to provide the query below for the Microsoft SQL v2 sensor.

Microsoft SQL monitoring Click to enlarge.

Follow the steps below to set up your Microsoft SQL v2 sensor:

Requirements

  • Microsoft SQL sensors in a PRTG installation generally need to be able to work against the actual target host.
  • Make sure that your database's autogrowth feature is disabled. Otherwise, you need to configure the limits accordingly in your PRTG settings.

The Microsoft 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 30th, 20161.0Initial Release

Sensor setup

  1. Save the query above as PRTG-DatabaseSize.sql in %Program Files (x86)%\PRTG Network Monitor\Custom Sensors\SQL\mssql\.
  2. In your PRTG installation, go to the Microsoft 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 encounter any bugs, feel free to share them.

Note: The query is provided as is and may or may not work with your installation. 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]

Last change on Jan 4, 2023 9:22:47 AM by  Brandy Greger [Paessler Support]



Votes:

0

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



Votes:

0

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]



Votes:

0

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

Created on May 17, 2019 9:14:37 AM



Votes:

0

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.

Created on May 21, 2019 7:30:01 AM by  Dariusz Gorka [Paessler Support]



Votes:

1

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:

DATABASENAMEDATA_FILESIZEDATA_USEDSPACEDATA_FREESPACEDATA_FREESPACE_PERCENTLOG_FILESIZELOG_USEDSPACELOG_FREESPACELOG_FREESPACE_PERCENT
1master4194304.003019898.881184890.8828.13786432.00398458.88398458.8850.00

Hope this helps... regards

Florian Rossmark

www.it-admins.com

Created on May 21, 2019 2:03:58 PM



Votes:

0

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.

Created on Jul 25, 2019 6:19:26 AM



Votes:

0

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

Created on Oct 9, 2021 11:23:05 AM




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.