Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT instance_name AS 'Database',
[Data File(s) Size (KB)]/1024 AS [Data file (MB)],
[Log File(s) Size (KB)]/1024 AS [Log file (MB)],
[Log File(s) Used Size (KB)]/1024 AS [Log file space used (MB)]
FROM (SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name IN
('Data File(s) Size (KB)',
'Log File(s) Size (KB)',
'Log File(s) Used Size (KB)')
AND instance_name = 'tempdb') AS A
PIVOT
(MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)],
[LOG File(s) Size (KB)],
[Log File(s) Used Size (KB)])) AS B
GO
--SELECT SUM(size)/128 AS [Total database size (MB)]
--FROM tempdb.sys.database_files
SELECT convert(varchar, getdate(), 108) AS 'CURRENT_TIME'
, cntr_value/1024 AS [Data_Free_MB]
INTO tempdb..Data_Free
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Data File(s) Size (KB)'
AND instance_name = 'tempdb'
WHILE 1 = 1
BEGIN
WAITFOR DELAY '00:05' -- 5 minutes
INSERT tempdb..Data_Free
SELECT convert(varchar, getdate(), 108) AS 'CURRENT_TIME'
, cntr_value/1024 AS [Data_Free_MB]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Data File(s) Size (KB)'
AND instance_name = 'tempdb'
END
SELECT * FROM tempdb..Data_Free
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.