I recently had a need to setup a process to periodically capture the size of the database files, along with the used (or unused) space. And not just the data files, the log files as well. Years ago, when I was doing more DBA work, I had a script to do just that. Unfortunately, it was for a much older version of SQL Server, and it no longer worked.
No problem, just find the current DMV's that provide the data. It turns out, it's not as straight forward as I would have liked. Sure there a many procedures, DMV's and DBCC commands that provide some of the data, but not all of the data. And I could have just used the profiler, and then pulled up the requisite form in SSMS, but that seems less informative than reading what the community had to offer.
sp_spaceused, sp_databases, DBCC SQLPERF(logspace) Oh my!
Looking over the posts, it seems there were endless ways to get some of the data. But ideally, I wanted a single SELECT that returned just the data I wanted, with out all of the wild SQL CTE, inline-query or other such code.
After reviewing the posts, many, perhaps all of the procedures and commands I found provided valuable information. Just not what I wanted. We have:
sp_spaceused, but it returns data in two data sets. Not ideal. Next we have...
sp_databases, but it just returns a list of databases and the size. Not ideal. Or use...
DBCC showfilestats, Ok, but still incomplete. And, since it is not listed on the main DBCC page at MSDN, its most likely deprecated.
And speaking of DBCC, there are other commands to consider. But what I was really hoping to find was a simple way to use the
sys.database_files DMV. It is very close, but not quite complete.
select * from sys.database_files;
In time, I found a post by Glenn Berry of SQLSkills. His query used a simple SELECT against the sys.database_files DMV, but with just a few extra additions. He used FILEPROPERTY.
To fit my needs, all I needed was a few extra bits added to the query provided by Glenn Berry. I now had the ideal database size monitoring statement. A query that told me all I needed to know to monitor the database sizes over time.
SELECT
@@SERVERNAME as ServerName
, db_Name() as DatabaseName
, cast(CURRENT_TIMESTAMP as date) as RunDate
, cast(CURRENT_TIMESTAMP as time) as Runtime
, name AS LogicalFileName
, physical_name AS PhysicalFileName
, CAST(size/128 AS DECIMAL(10,2)) AS TotalSizeMB
, CAST(size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed')
AS int)/128.0 AS decimal(10,2)) AS AvailSpaceMB
, case when (size/128) != 0 then
CAST(100 - (((size/128.0
- CAST(FILEPROPERTY(name, 'SpaceUsed') AS int
)/128.0 )/(size/128))*100) AS DECIMAL(10,2))
else 0 end as PercentUsed
, type, type_desc,state, state_desc,size, max_size ,file_id, file_guid, data_space_id
, growth, is_media_read_only, is_read_only, is_sparse, is_percent_growth
, is_name_reserved, create_lsn, drop_lsn, read_only_lsn, read_write_lsn
, differential_base_lsn, differential_base_guid, differential_base_time
, redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid
, backup_lsn
FROM sys.database_files;
There was just one problem. The query was database specific. And I needed the information for all databases.
sp_MSforeachdb to the rescue
All that was required was to repackage the query and run it using sp_MSforeachdb. And you'll notice I push all of this into a table using a procedure.
if exists(select * from sys.objects
where object_id = OBJECT_ID('dbo.usp_database_file_info') and type = N'P')
DROP PROCEDURE dbo.usp_database_file_info;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.usp_database_file_info
AS
begin
declare @myCommand Nvarchar(4000);
select @myCommand =
' USE ? '
+ 'insert into test.dbo.database_file_info ( ServerName, DatabaseName, RunDate, Runtime, LogicalFileName '
+ ', PhysicalFileName, TotalSizeMB, AvailSpaceMB, PercentUsed, type, type_desc, state, state_desc, size, max_size '
+ ', file_id, file_guid, data_space_id, growth, is_media_read_only, is_read_only, is_sparse, is_percent_growth'
+ ', is_name_reserved, create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid'
+ ', differential_base_time, redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid, backup_lsn ) '
+ ' SELECT @@SERVERNAME as ServerName'
+ ', db_Name() as DatabaseName'
+ ', cast(CURRENT_TIMESTAMP as date) as RunDate'
+ ', cast(CURRENT_TIMESTAMP as time) as Runtime'
+ ', name AS LogicalFileName'
+ ', physical_name AS PhysicalFileName'
+ ', CAST(size/128 AS DECIMAL(10,2)) AS TotalSizeMB'
+ ', CAST(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 AS decimal(10,2)) AS AvailSpaceMB'
+ ', Case when (size/128) != 0 then'
+ ' CAST(100 - (((size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0 )/(size/128))*100) AS DECIMAL(10,2))'
+ ' else 0 end as PercentUsed'
+ ', type, type_desc,state, state_desc,size, max_size ,file_id, file_guid, data_space_id'
+ ', growth, is_media_read_only, is_read_only, is_sparse, is_percent_growth, is_name_reserved'
+ ', create_lsn, drop_lsn, read_only_lsn, read_write_lsn, differential_base_lsn, differential_base_guid, differential_base_time'
+ ', redo_start_lsn, redo_start_fork_guid, redo_target_lsn, redo_target_fork_guid, backup_lsn'
+ ' FROM sys.database_files;';
-- EXEC (@myCommand);
EXEC sp_MSforeachdb @myCommand ;
end
All we need is the table create statement:
if exists( select * from sys.objects where object_id = OBJECT_ID('dbo.database_file_info') )
DROP TABLE dbo.database_file_info;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[database_file_info](
tblID bigint identity(1,1) not null,
[ServerName] [nvarchar](128) NULL,
[DatabaseName] [nvarchar](128) NULL,
[RunDate] [date] NULL,
[Runtime] [time](7) NULL,
[LogicalFileName] [sysname] NOT NULL,
[PhysicalFileName] [nvarchar](260) NOT NULL,
[TotalSizeMB] [decimal](10, 2) NULL,
[AvailSpaceMB] [decimal](10, 2) NULL,
[PercentUsed] [decimal](10, 2) NULL,
[type] [tinyint] NOT NULL,
[type_desc] [nvarchar](60) NULL,
[state] [tinyint] NULL,
[state_desc] [nvarchar](60) NULL,
[size] [int] NOT NULL,
[max_size] [int] NOT NULL,
[file_id] [int] NOT NULL,
[file_guid] [uniqueidentifier] NULL,
[data_space_id] [int] NOT NULL,
[growth] [int] NOT NULL,
[is_media_read_only] [bit] NOT NULL,
[is_read_only] [bit] NOT NULL,
[is_sparse] [bit] NOT NULL,
[is_percent_growth] [bit] NOT NULL,
[is_name_reserved] [bit] NOT NULL,
[create_lsn] [numeric](25, 0) NULL,
[drop_lsn] [numeric](25, 0) NULL,
[read_only_lsn] [numeric](25, 0) NULL,
[read_write_lsn] [numeric](25, 0) NULL,
[differential_base_lsn] [numeric](25, 0) NULL,
[differential_base_guid] [uniqueidentifier] NULL,
[differential_base_time] [datetime] NULL,
[redo_start_lsn] [numeric](25, 0) NULL,
[redo_start_fork_guid] [uniqueidentifier] NULL,
[redo_target_lsn] [numeric](25, 0) NULL,
[redo_target_fork_guid] [uniqueidentifier] NULL,
[backup_lsn] [numeric](25, 0) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[database_file_info] ADD CONSTRAINT
[PK_database_file_info] PRIMARY KEY CLUSTERED ( [tblID] ASC )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF
, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
You can find Glenn Berry's original posted reply on MSDN at:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e3f1d036-bfe7-42a3-a43e-7ae7577c3849/dbcc-command-for-data-file-size?forum=sqldatabaseengine.
