file system. sql server

--==============================================================
--Script Author: Timothy Ford aka SQLAgentMan
-- http://thesqlagentman.com
-- Can be used for personal use; credit must be given to author
-- Code can not be used for commercial use, repackaged, resold without permission
--==============================================================

--==============================================================
--CREATE DATABASE iDBA IF IT DOES NOT ALREADY EXIST:
--==============================================================
/*
IF NOT EXISTS (SELECT name FROM master..sysdatabases WHERE name = 'iDBA')
BEGIN
CREATE DATABASE iDBA;
ALTER DATABASE [iDBA] SET AUTO_SHRINK OFF;
ALTER DATABASE [iDBA] SET RECOVERY SIMPLE WITH NO_WAIT;
ALTER DATABASE [iDBA] MODIFY FILE (NAME = N'iDBA', MAXSIZE = 1000MB , FILEGROWTH = 100MB);
ALTER DATABASE [iDBA] MODIFY FILE (NAME = N'iDBA_log', MAXSIZE = 200MB , FILEGROWTH = 100MB);
END

*/


--====================================================================
--CREATE THE STORED PROCEDURE AS USED FOR LOADING DBA REPOSITORY NIGHTLY
--====================================================================
USE [iDBA];

CREATE PROCEDURE [dbo].[usp_database_file_snapshot] AS
DECLARE @SQL VARCHAR(5000)
DECLARE @version smallint


--DETERMINE IF THE INSTANCE IS SQL 2005 OR LATER SO CATALOG VIEWS CAN BE USED
SELECT @version = CONVERT(smallint, LEFT(CONVERT(varchar(20),SERVERPROPERTY('ProductVersion')),CHARINDEX('.',CONVERT(varchar(20),SERVERPROPERTY('ProductVersion')))-1))

IF EXISTS (SELECT NAME FROM tempdb..sysobjects WHERE NAME = 'RESULTS_FILE_HISTORY')
BEGIN
DROP TABLE [tempdb].[dbo].[RESULTS_FILE_HISTORY]
END

CREATE TABLE [tempdb].[dbo].[RESULTS_FILE_HISTORY] ([Server] nvarchar(128), [DatabaseName] sysname, [Name] sysname, [Filename] NVARCHAR(260),
[FileType] varchar(4), [Size_In_Mb] int, [Available_Space_In_Mb] int, [Growth_Increments] int, [Growth_Units] varchar(2),
[Max_File_Size_In_Mb] int)

--IF THIS IS A SQL 2000 OR EARLIER INSTANCE YOU MUST USE THE SYSTEM TABLES
IF @version < 9
BEGIN
SELECT @SQL =
'USE [?] INSERT INTO [tempdb].[dbo].[RESULTS_FILE_HISTORY]([Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb], [Available_Space_In_Mb], [Growth_Increments],
[Growth_Units], [Max_File_Size_In_Mb])
SELECT @@servername, DB_NAME(),
[name] AS [Name],
[filename] AS [Filename],

[FileType] =
CASE (status & 0x40)
WHEN 0 THEN ''Data'''
+
'ELSE ''Log'''
+
'END,

[Size_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END,
[Available_Space_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
END,

[Growth_Increments] =
CASE (status & 0x100000)
WHEN 0 THEN CAST(CEILING([growth]/1024*8) AS int)'
+
'ELSE [growth]'
+
'END,

[Growth_Units] =
CASE (status & 0x100000)
WHEN 0 THEN ''Mb'''
+
'ELSE ''%'''
+
'END,
[Max_File_Size_In_Mb] =
CASE [maxsize]
WHEN -1 THEN NULL
WHEN 268435456 THEN NULL
ELSE [maxsize]/1024*8
END
FROM dbo.sysfiles
ORDER BY [fileid]'
END
ELSE --IF THIS IS A SQL 2005 OR LATER INSTANCE YOU WILL USE THE CATALOG VIEWS
BEGIN
SELECT @SQL =
'USE [?] INSERT INTO [tempdb].[dbo].[RESULTS_FILE_HISTORY]([Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb], [Available_Space_In_Mb], [Growth_Increments],
[Growth_Units], [Max_File_Size_In_Mb])
SELECT @@servername, DB_NAME(),
[name] AS [Name],
physical_name AS [Filename],
[FileType] =
CASE type
WHEN 0 THEN ''Data'''
+
'WHEN 1 THEN ''Log'''
+
'END,
[Size_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN 1
ELSE ceiling([size]/128)
END,
[Available_Space_In_Mb] =
CASE ceiling([size]/128)
WHEN 0 THEN (1 - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
ELSE (([size]/128) - CAST(FILEPROPERTY([name], ''SpaceUsed''' + ') as int) /128)
END,

[Growth_Increments] =
CASE [is_percent_growth]
WHEN 0 THEN [growth]'
+
'ELSE CAST(CEILING([growth]/1024*8) AS int)'
+
'END,
[Growth_Units] =
CASE [is_percent_growth]
WHEN 1 THEN''%'''
+
'ELSE ''Mb'''
+
'END,
[Max_File_Size_In_Mb] =
CASE [max_size]
WHEN -1 THEN NULL
WHEN 268435456 THEN NULL
ELSE [max_size]/1024*8
END
FROM sys.database_files
ORDER BY [FileType], [file_id]'
END

--Run the command against each database
EXEC sp_MSforeachdb @SQL

--You can uncomment the following statement in order to see the results returned in SSMS:
/*
SELECT [Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb], [Available_Space_In_Mb],
CEILING(CAST([Available_Space_In_Mb] AS decimal(10,1)) / [Size_In_Mb]*100) AS [Free Space %],
[Growth_Increments], [Growth_Units], [Max_File_Size_In_Mb]
FROM [tempdb].[dbo].[RESULTS_FILE_HISTORY];
*/

GO
-------------------------------------------------------------------------------

--==============================================================
--CODE TO PERSIST THE DATA (Daily In This Case)
--==============================================================
--==============================================================
--Script Author: Timothy Ford aka SQLAgentMan
-- http://thesqlagentman.com
-- Can be used for personal use; credit must be given to author
-- Code can not be used for commercial use, repackaged, resold without permission
--==============================================================

/*
AUTHORS NOTE:
This runs via an SSIS process I've instituted to collect metadata from all instances I monitor.
If you're interested in doing so I suggest reading Rodney Landrum's DBA Toolbox book
from Red Gate Press.

The code here only runs locally against non-persisted data - just the temp table

Use Ctl+Shift+M to replace the template parameter for threshold with a value of your choosing. Default is 85%
*/

EXEC iDBA.dbo.usp_database_file_snapshot;

--==============================================================
-- Relies upon previously creating the iDBA.dbo.usp_database_file_snapshot stored procedure
--==============================================================
SELECT [Server], [DatabaseName], [Name], [Filename],
[FileType], [Size_In_Mb] AS [File Size (Mb)], [Available_Space_In_Mb] AS [Free Space (Mb)],
[Max_File_Size_In_Mb] AS [Max File Size (Mb)], ([Size_In_Mb] - [Available_Space_In_Mb]) AS [Space Used (Mb)],
CAST([Growth_Increments] AS varchar(10)) + ' ' + [Growth_Units] AS [Growth Increment],
CAST((([Size_In_Mb] - [Available_Space_In_Mb])*1.0) / [Size_In_Mb] AS decimal(5,2)) AS [% Consumed],
[date_stamp]
FROM [tempdb].[dbo].[RESULTS_FILE_HISTORY]
WHERE (([Size_In_Mb] - [Available_Space_In_Mb])*1.0) / [Size_In_Mb] >= 0.85
ORDER BY [Server], [DatabaseName];