Actual (sparse) file size on disk of a snapshot

What is the actual (sparse) file size on disk of a snapshot?

[sql]
--****************************************************************************************
-- What is the actual (sparse) file size on disk of a snapshot?
--****************************************************************************************
-- Versie: 1.0
-- Author: Theo Ekelmans
-- Date: 2008-11-01
--
--****************************************************************************************
SELECT
DB_NAME(mf.database_id) AS Database_Name
, mf.name AS Logical_Name
, mf.Physical_Name
, mf.FILE_ID
, CAST((vfs.size_on_disk_bytes)/(1024.00*1024) AS NUMERIC(18,2)) AS Size_on_Disk_MB
, CAST(mf.size/1024.00 AS NUMERIC(18,2)) AS Size_in_MB
, mf.max_size
, CAST(mf.growth/1024.00 AS NUMERIC(18,2)) AS Growth_in_MB
FROM sys.master_files mf
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
ON mf.database_id=vfs.database_id AND mf.FILE_ID=vfs.FILE_ID
WHERE mf.database_id = DB_ID('DbName_snapshot')
[/sql]

Determine the page life expectancy per NUMA node

Determine the page life expectancy per NUMA node (is it balanced?)

[sql]
--****************************************************************************************
-- Determine the page life expectancy per NUMA node (is it balanced?)
--****************************************************************************************
-- Versie: 1.0
-- Author: Theo Ekelmans
-- Date: 2014-01-22
--
--****************************************************************************************

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT ple.[NUMA]
, LTRIM(STR([PageLife_Sec] / 3600)) + ':' + REPLACE(STR([PageLife_Sec] % 3600 / 60, 2), SPACE(1), '0') + ':' + REPLACE(STR([PageLife_Sec] % 60, 2), SPACE(1), '0') AS 'PageLife'
, CONVERT(decimal(15, 0), opc.[DatabasePages] * 0.0078125) AS 'BufferPool_MB'
, CONVERT(decimal(15, 0), opc.[DatabasePages] * 0.0078125 / [PageLife_Sec]) AS 'BufferPool_Delta_MB_Sec'
FROM (
SELECT [instance_name] AS 'NUMA'
, [cntr_value] AS 'PageLife_Sec'
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Page life expectancy') AS ple
INNER JOIN (
SELECT [instance_name] AS 'NUMA'
, [cntr_value] AS 'DatabasePages'
FROM sys.dm_os_performance_counters
WHERE [counter_name] = 'Database pages')AS opc
ON ple.[NUMA] = opc.[NUMA]

[/sql]

Diskspace Free and Total overview

Displays the free space,free space percentage plus total drive size for a server

[table id=2 /]


[sql]
/*
Description : Displays the free space,free space percentage plus total drive size for a server
Author : Theo Ekelmans
Version/Date : 1.0 2013-08-23

--Preparation
exec sp_configure 'show advanced options', 1
reconfigure

exec sp_configure 'Ole Automation Procedures', 1
reconfigure

*/
SET NOCOUNT ON

DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive

DROP TABLE #drives

[/sql]