Code You Would Hate To Live Without #tsql2sday

T SQL Tuesday

 

I finally found a #tsql2sday topic that I thought I could contribute to and wanted to make a quick post. T-SQL Tuesday is a writing prompt shared with the SQL community at large to talk about something related to your work (usually with SQL) that occurs on a monthly basis and is now up to #104.

This month it is being hosted by Bert Wagner ( b | t ) and he has challenged us to share some code (preferably that we’ve written) that we honestly would probably be making currently if we didn’t have it existing already.

My bit of code that I’m throwing into the foray this month is my sp_FileSpace stored procedure. I’ve been given a large number of databases through the years that can be referred to as VLDBs (Very Large DataBases) or at least VLDB hopefuls. Since they are in the VLDB arena one of the best practices is to break out the database data into multiple files and then someone has to monitor those multiple files to ensure they are staying consistent.

When I want to get the state of files in a database often they are spanned across multiple drives and often the easiest way to understand what is going on with them is with a grid created in T-SQL. I’ve created this script to show me different things but all in all typically how big each file is, how big each filegroup is, and if they are consistent.

My parameters in this stored procedure all have default values because I like to typically see things the same way but anytime I look at something and I’d like to see it changed for one reason of another I’ve started to throw it into a parameter so it’ll be more useful in the long run.

As Bert says, my code is not perfect but that’s why it’s not a compiled stored procedure. When I find something that needs changing I change it.

Now I’ve made sure to publish this at midnight (my time) on Tuesday. I’m hoping that gives someone else plenty of time to put something together.

Without further ado I introduce you to sp_FileSpace:

USE [master]
GO
IF OBJECT_ID('dbo.sp_FileSpace') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_FileSpace AS RETURN 0;');
GO
ALTER PROCEDURE [dbo].[sp_FileSpace]
@Drive [CHAR](1) = NULL
, @ShowRunningTotals [BIT] = 1
, @Filegroups [VARCHAR](50) = NULL
, @ShowGB [BIT] = 1
, @ShowMB [BIT] = 1
, @ShowKB [BIT] = 0
, @ShowExtents [BIT] = 0
, @ShowPercents [BIT] = 1
, @ShowGrowth [BIT] = 1
, @DBName [VARCHAR](256) = NULL
AS
BEGIN
PRINT 'Declaring Variables'
DECLARE @Select AS [VARCHAR](MAX)
DECLARE @From AS [VARCHAR](MAX)
DECLARE @Where AS [VARCHAR](MAX)
DECLARE @Order AS [VARCHAR](MAX)
DECLARE @sql AS [NVARCHAR](MAX)
DECLARE @Compat AS [SMALLINT]
PRINT 'Check Compatibility Level of database to use SUM windowing functions'
SELECT @Compat = [compatibility_level] FROM [sys].[databases] db WHERE [db].[name]=DB_Name()
PRINT 'Building Select Statement'
SET @Select = '
SELECT
[FileId] = [dbf].[file_id]
, [FileGroup] = dbf.data_space_id
, [FileGroupName] = COALESCE([fg].[name],''Log'')'
PRINT 'Deciding to put GB on values or not'
IF @ShowGB = 1
SET @Select = @Select + '
, [GBs] = [dbf].[size] / CAST(131072 AS FLOAT(2))
, [GBs Used] = FILEPROPERTY([dbf].[name],''SpaceUsed'') / CAST(131072 AS FLOAT(2))'
PRINT 'Deciding to put MB on values or not'
IF @ShowMB = 1
SET @Select = @Select + '
, [MBs] = [dbf].[size] / CAST(128 AS FLOAT(2))
, [MBs Used] = FILEPROPERTY([dbf].[name],''SpaceUsed'') / CAST(128 AS FLOAT(2))'
PRINT 'Deciding to put KB on values or not'
IF @ShowKB = 1
SET @Select = @Select + '
, [KBs] = CAST([dbf].[size] * CAST(8 AS FLOAT(2)) AS BIGINT)
, [KBs Used] = fileproperty([dbf].[name],''SpaceUsed'') * CAST(8 AS FLOAT(2))'
PRINT 'Deciding to put Extents on values or not'
IF @ShowExtents = 1
SET @Select = @Select + '
, [Total Extents] = [dbf].[size] / 8
, [Used Extents] = FILEPROPERTY([dbf].[name],''SpaceUsed'') / 8'
PRINT 'Deciding to put Percents on or not'
IF @ShowPercents = 1
SET @Select = @Select + '
, [Percent Used] = CAST(CAST((CAST(fileproperty([dbf].[name],''SpaceUsed'') AS NUMERIC(15,2)) / CAST([dbf].[size] AS NUMERIC(15,2))) * 100 AS NUMERIC(15,2)) AS VARCHAR(10)) + ''%'''
PRINT 'Adding file rows to select'
SET @Select = @Select + '
, [Logical File Name] = [dbf].[name]
, [Real File Name] = [dbf].[physical_name]'
PRINT 'Deciding to put Growth on or not'
IF @ShowGrowth = 1
SET @Select = @Select + '
, [Growth] = CASE [dbf].[is_percent_growth]
WHEN 0 THEN CAST([dbf].[growth] / 128 AS VARCHAR(8)) + '' MB''
WHEN 1 THEN CAST([dbf].[growth] AS VARCHAR(8)) + '' %''
ELSE ''New unknown growth pattern''
END'
PRINT 'Deciding to put Running Sums on or not'
IF @Compat > 110 AND @ShowGrowth = 1
SET @Select = @Select + '
, [Running Total GB Used FileGroup] = CAST(SUM(fileproperty(dbf.name,''SpaceUsed'') / CAST(131072 AS FLOAT(2))) OVER(PARTITION BY fg.name ORDER BY (dbf.name)) AS FLOAT(2))
, [Running Total GB FileGroup] = CAST(SUM(dbf.size / CAST(131072 AS FLOAT(2))) OVER(PARTITION BY fg.name ORDER BY (dbf.name)) AS FLOAT(2))'
PRINT @Select
PRINT @DBName
IF @DBName IS NULL
SELECT @DBName = DB_NAME();
PRINT 'Database we are looking at: ' + @DBName
SET @From = '
FROM
' + QUOTENAME(@DBName) + '.[sys].[database_files] [dbf]
LEFT JOIN ' + QUOTENAME(@DBName) + '.[sys].[filegroups] [fg]
ON [dbf].[data_space_id]=[fg].[data_space_id]'
SET @Where = '
WHERE
1=1'
IF @Filegroups IS NOT NULL
SET @Where = @Where + '
AND [dbf].[data_space_id] IN (' + @Filegroups + ')'
IF @Drive IS NOT NULL
SET @Where = @Where + '
AND LEFT([dbf].[physical_name],1)='''+ @Drive + ''
SET @Order = '
ORDER BY
[dbf].[data_space_id]
,[dbf].[name]
'
SET @sql = @Select + ' ' + @From + ' ' + @Where + ' ' + @Order
PRINT @sql
EXECUTE sp_executesql @sql
END;