I wanted a more granular level of detail when looking at disk space usage of our databases. I wanted to see the disk usage of each table. Since we use partitioning pretty heavily I also wanted to see how that usage was broken out by partition and filegroup. In our implementation we have a separate filegroup for each table partition but in a configuration that has multiple parititons on a partition this script can tell you usage for either metric.
Normally I’ll take this query and take the results over to excel and pivot the data so that I can filter and sum data in whatever way I want. I also have this running in a SQL Agent job and inserting into a table so that I connect to that table and do growth trend analysis.
Read more…
Here’s a quick script to find the recent history of file auto growth on a MSSQL system. I work with a large OLTP database that’s partitioned monthly. If we get files auto-growing toward the end of a month when the partition is already upwards of 60GB, timeouts start to crop up. Sometimes finding out where those timeouts are coming from aren’t readily apparent. This script helped us out when we were searching for recent file growth.
SET NOCOUNT ON
DECLARE @path VARCHAR(max), @cmd VARCHAR(4000);
SELECT @path = CONVERT(varchar(max),value)
FROM fn_trace_getinfo(DEFAULT) WHERE property = 2
SET @cmd = 'dir /b "' + @path + '"'
-- PRINT @cmd
DECLARE @files TABLE
(
fn VARCHAR(64)
)
INSERT @files
EXEC master..xp_cmdshell @cmd
DELETE @files
WHERE fn IS NULL
SELECT
e.DatabaseName
, e.[FileName]
, e.SPID
, e.Duration
, e.StartTime
, e.EndTime
, CASE e.EventClass
WHEN 92 THEN 'Data'
WHEN 93 THEN 'Log'
END AS 'FileType'
, f.fn AS 'TraceFile'
, te.name
FROM @files f
CROSS APPLY fn_trace_gettable(@path, DEFAULT) e
JOIN sys.trace_events te
ON e.EventClass = te.trace_event_id
WHERE
e.EventClass IN (92,93)
ORDER BY
e.StartTime DESC