Code You Would Hate To Live Without #tsql2sday

T SQL Tuesday
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;

Advertisements

4 thoughts on “Code You Would Hate To Live Without #tsql2sday”

  1. Hi there Josh !
    First I want to thank you for sharing your code
    It would be nice if one could be able to choose fom getting the output of only one database or all the databases from the instance just running your Sp once, something like:
    sp_FileSpace @DBName = ‘ALL’
    Im not by all means asking you to re think your code, Im just sharing my thouthts about your script
    And again thank you for sharing!!!
    By the way this is my first reply on a post ever!

    Like

    1. Thank you Mauro. I’ve often thought about revisiting this code to make it capable of bringing back the data from all databases. With your support I think I could make it a reality. Besides, it’s always good to look at our “old” code every once in a while and benefit it from the new knowledge we have aquired. Look for something in the future.

      Thanks again.

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s