April 9, 2014 at 10:17 am
Hello,
I use dbcc sqlperf ('logspace') to keep an eye on the logs for our databases. I like that it tells me how big the log file is and how much space is being used. I can compare values from different dates to see how fast they are growing.
Used along side EXEC master..xp_fixeddrives, I can monitor disk space to make sure we don't run out of space.
Is there a comparable SQL query that is used to watch over the data files (mdf)? I would like to see how big the file is and much space is actually being used. It would allow me to see how fast it is growing.
Thanks for any suggestions.
April 9, 2014 at 10:28 am
Here is a query from Glenn Berry's Diagnostic DMVs[/url] that gives you the individual files sizes and the space available:
-- Individual File Sizes and space available for current database (Query 33) (File Sizes and Space)
SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
cast(CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) as SpaceUsed,
CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE); edit: I made a minor change to include SpaceUsed
April 9, 2014 at 10:38 am
defyant_2004 (4/9/2014)
Hello,I use dbcc sqlperf ('logspace') to keep an eye on the logs for our databases. I like that it tells me how big the log file is and how much space is being used. I can compare values from different dates to see how fast they are growing.
Used along side EXEC master..xp_fixeddrives, I can monitor disk space to make sure we don't run out of space.
Is there a comparable SQL query that is used to watch over the data files (mdf)? I would like to see how big the file is and much space is actually being used. It would allow me to see how fast it is growing.
Thanks for any suggestions.
Yes, you can query allocated size, percent growth, state, backup status, etc. from data management views.
sys.databases - databases
sys.master_files - files (both mdf and ldf)
sys.dm_os_volume_stats - disks
I use the query below to more of a high level view. It aggregates at the server and disk level, also grouping by data space, log space, and tempdb.
SELECT
cast((select max(local_net_address) from sys.dm_exec_connections) as char(15))Server_IP,
cast(@@SERVERNAME as char(25)) as Server_Name,
cast(case when physical_name like '%tempdb.mdf' or physical_name like '%templog.ldf' then name else '*' end as char(20)) name,
cast(type_desc as char(10)) as Data_Desc,
cast( cast((sum(cast(size as bigint))*8060)/(1024*1024*1024*1.0) as numeric(9,1)) as char(12))Data_GB,
cast(volume_mount_point as char(12)) as Volume_Name,
cast(cast(max(cast(total_bytes as bigint))/(1024*1024*1024*1.0) as numeric(9,1)) as char(18))Drive_Total_GB,
cast(cast(max(cast(available_bytes as bigint))/(1024*1024*1024*1.0) as numeric(9,1)) as char(18))Drive_Free_GB
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID)
group by volume_mount_point, type_desc, case when physical_name like '%tempdb.mdf' or physical_name like '%templog.ldf' then name else '*' end
order by volume_mount_point, case when physical_name like '%tempdb.mdf' or physical_name like '%templog.ldf' then name else '*' end, type_desc;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 9, 2014 at 12:15 pm
I am trying both these queries out, thank you.
It appears I have to run the first query individually for each database, unlike the dbcc sqlperf ('logspace') which gives all databases in one result set.
I do receive an error message for the second query:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_volume_stats'.
Do you know if there is a dbcc command to check all the database mdf files like the dbcc sqlperf ('logspace')?
April 9, 2014 at 12:43 pm
defyant_2004 (4/9/2014)
I am trying both these queries out, thank you.It appears I have to run the first query individually for each database, unlike the dbcc sqlperf ('logspace') which gives all databases in one result set.
I do receive an error message for the second query:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.dm_os_volume_stats'.
Do you know if there is a dbcc command to check all the database mdf files like the dbcc sqlperf ('logspace')?
It looks like the view sys.dm_os_volumne_stats was introduced in SQL Server 2008 R2.
The procedure sp_spaceused, when not supplied with table name, will return database_name, database_size, and unallocated_space for whatever database has current context.
To make this call for all databases, you can leverage the undocumented procedure sp_MSForEachDB. The way it works is, you supply a sql string as input parameter, and then it executes that sql dynamically for each database. You use "?" symbol to substitute database name, so you can supply "Use ?;" to change context for each iteration. Follow that by the call to sp_spaceused.
The following example should give you what you're asking.
EXEC sp_MSForEachDB 'Use ?; exec sp_spaceused;';
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 9, 2014 at 2:22 pm
Thanks, this will help me out.
I am surprised there is nothing similar to dbcc sqlperf('logspace') like a dbcc sqlperf('dataspace')
April 9, 2014 at 2:27 pm
defyant_2004 (4/9/2014)
Thanks, this will help me out.I am surprised there is nothing similar to dbcc sqlperf('logspace') like a dbcc sqlperf('dataspace')
Look around on the website http://www.sqlskills.com/blogs/paul/ for articles by Paul Randal or Kimberly Tripp. Paul was the original developer of some DBCC commands, and if there are any undocumented parameters to sqlperf, it will be mentioned in one of the numerous blog posts there.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply