Query to report disk space allocation and used space












24















We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage).



I'd like to write a query (report) for a single database indicating the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio.



Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left. Can these fields be accessed via a T-SQL query? If so, what would the query look like?



Shrink Database Dialog










share|improve this question





























    24















    We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage).



    I'd like to write a query (report) for a single database indicating the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio.



    Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left. Can these fields be accessed via a T-SQL query? If so, what would the query look like?



    Shrink Database Dialog










    share|improve this question



























      24












      24








      24


      11






      We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage).



      I'd like to write a query (report) for a single database indicating the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio.



      Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left. Can these fields be accessed via a T-SQL query? If so, what would the query look like?



      Shrink Database Dialog










      share|improve this question
















      We are using 6 databases in total for an application, and we can only share 4TB of space among all 6 auto-grow databases (via SAN storage).



      I'd like to write a query (report) for a single database indicating the "Currently allocated space" and "Available free space" attributes under the Tasks > Shrink > Database option in SQL Server Management Studio.



      Then I'd like to convert those numbers to TB and total each database to get a rough estimate of how much space we have left. Can these fields be accessed via a T-SQL query? If so, what would the query look like?



      Shrink Database Dialog







      sql-server sql-server-2008 disk-space






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Aug 27 '15 at 10:48









      Paul White

      49.5k14261415




      49.5k14261415










      asked Nov 29 '12 at 19:07









      MacGyverMacGyver

      956102750




      956102750






















          7 Answers
          7






          active

          oldest

          votes


















          33














          Here is the query Management Studio uses to populate those numbers:



          SELECT
          (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
          WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
          SUM(a.total_pages) AS [SpaceUsed],
          (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
          WHERE df.type in (1, 3)) AS [LogSize]
          FROM
          sys.partitions p join sys.allocation_units a
          on p.partition_id = a.container_id
          left join sys.internal_tables it
          on p.object_id = it.object_id


          You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:



          SELECT
          (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
          FROM sys.database_files AS df
          WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
          CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
          (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
          FROM sys.database_files AS df
          WHERE df.type in (1, 3)) AS [LogSize]
          FROM sys.partitions p join sys.allocation_units a
          on p.partition_id = a.container_id;


          With this update, assuming your database doesn't change in the meantime, it should yield:



          753475.94    744030.07    2900.00


          Doing some simple math, and isolating just the three numbers you want:



          ;WITH t(s) AS
          (
          SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
          FROM sys.database_files
          WHERE [type] % 2 = 0
          ),
          d(s) AS
          (
          SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
          FROM sys.partitions AS p
          INNER JOIN sys.allocation_units AS a
          ON p.[partition_id] = a.container_id
          )
          SELECT
          Allocated_Space = t.s,
          Available_Space = t.s - d.s,
          [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
          FROM t CROSS APPLY d;





          share|improve this answer

































            20














            Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries

            (you would need to change the math for TBs):



            -- Individual File Sizes and space available for current database
            -- (Query 36) (File Sizes and Space)
            SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
            CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
            CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
            AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
            FROM sys.database_files AS f WITH (NOLOCK)
            LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
            ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);





            share|improve this answer

































              7














              Here's some SQL that might work for you.




              Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL,
              filName varchar(520) NULL, sizeMg decimal(10,2) null,
              usedMg decimal(10,2) null, freeMg decimal(10,2) null,
              pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
              Declare @sSql varchar(1000)
              Set @sSql = 'Use [?];
              Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
              Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)),
              Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
              From dbo.sysfiles Order By groupId Desc;'
              Exec sp_MSforeachdb @sSql
              Update #dbInfo Set
              freeMg = sizeMg - usedMg,
              pcntUsed = (usedMg/sizeMg)*100,
              pcntFree = ((sizeMg-usedMg)/sizeMg)*100

              select * from #dbInfo compute sum(sizeMG), sum(FreeMg)
              drop table #dbInfo


              It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.






              share|improve this answer





















              • 1





                Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                – Senior Systems Engineer
                Jul 23 '14 at 0:04











              • There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                – Tobb
                Sep 9 '16 at 12:18






              • 1





                The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                – Bruce
                Sep 9 '16 at 23:26



















              5














              After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users.



              This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated. A user would need to change the calculation for 'Size_On_Disk_Bytes' if the disk is formatted using an allocation size different from the default (4096 bytes).



              SELECT fg.data_space_id AS FGID,
              (f.file_id) AS File_Id,
              -- As provided by OP, size on disk in bytes.
              REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, CAST(f.size AS FLOAT) * 8.00 * 1024), 1), '.00','') AS Size_On_Disk_Bytes,
              ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
              ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
              ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
              ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
              f.name,
              f.physical_name
              FROM sys.database_files f
              LEFT JOIN sys.filegroups fg
              ON f.data_space_id = fg.data_space_id


              In addition, this query will return information about the space on the volume drive that hosts the files. The user will need to have access to sys.master_files.



              SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
              dovs.volume_mount_point AS Drive,
              CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
              FROM sys.master_files mf
              CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
              ORDER BY FreeSpaceInMB ASC





              share|improve this answer

































                0














                USE [db]
                GO

                /****** Object: StoredProcedure [dbo].[GetDriveSpace] Script Date: 6/29/2018 9:22:12 AM ******/
                SET ANSI_NULLS ON
                GO

                SET QUOTED_IDENTIFIER ON
                GO





                CREATE PROCEDURE [dbo].[GetDriveSpace]

                @DaysToRetain SMALLINT = 90

                AS

                SET NOCOUNT ON

                -- Persisted work table

                IF OBJECT_ID('<db>.dbo.DriveSpace') IS NULL
                BEGIN
                Create TABLE dbo.DriveSpace
                (
                PK INT IDENTITY CONSTRAINT PK_DriveSpace PRIMARY KEY,
                DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                AvailableMB BIGINT NOT NULL,
                TotalDriveSpaceMB BIGINT NOT NULL,
                [TotalSpaceGB] AS ([TotalDriveSpaceMB]/(1024.00)),
                [FreeSpaceGB] AS ([AvailableMB]/(1024.00)),
                PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDriveSpaceMB) * 100),
                EntryDate DATETIME NOT NULL
                ) ON [PRIMARY]
                CREATE NONCLUSTERED INDEX NCnd_EntryDate
                ON dbo.DriveSpace (EntryDate)
                END

                -- Vars
                DECLARE

                @AgeOutDate DATETIME, -- To prune the [DriveSpace] table
                @BATCHDATE DATETIME, -- Constant
                @DriveSize BIGINT, -- Drive size in bytes
                @DriveLetter VARCHAR(2), -- FSO drive letter
                @ErrorMessage VARCHAR(500), -- Error message
                @FSO INT, -- File System Object
                @AvailableMB INT, -- Available space MB
                @NO BIT, -- Constant
                @oDrive INT, -- FSO drive
                @OLE_AUTOMATION_PROCEDURES INT, -- Constant
                @PK TINYINT, -- Work-table primary key
                @RawFree VARCHAR(20), -- Available space bytes
                @RetVal INT, -- Return value
                @SQL NVARCHAR(255), -- Dynamic T-SQL
                @TwoToTheTwentieth INT, -- Convert bytes to MB
                @ValueInUse SQL_VARIANT, -- To test configuration settings
                @VolumeName VARCHAR(32), -- FSO volume name
                @YES BIT -- Constant

                -- Constants
                SET @BATCHDATE = GETDATE()
                SET @OLE_AUTOMATION_PROCEDURES = 16388 -- SELECT configuration_id FROM master.sys.configurations
                SET @NO = 0
                SET @TwoToTheTwentieth = POWER(2, 20)
                SET @YES = 1

                -- Create temp tables for disk space info
                IF OBJECT_ID('tempdb.dbo.#Space') IS NOT NULL DROP TABLE #Space
                CREATE TABLE #Space
                (
                PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
                DriveLetter VARCHAR(2) NOT NULL,
                VolumeName VARCHAR(32) NULL,
                DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
                AvailableMB BIGINT NOT NULL,
                Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
                )

                -- We need OLE automation enabled to succeed.

                BEGIN TRY
                -- Enable advanced options
                EXEC sp_configure 'show advanced option', '1';
                RECONFIGURE;

                -- Ensure OLE Automation enabled
                EXEC sp_configure 'OLE Automation Procedures', @YES;
                RECONFIGURE

                -- Test...
                SELECT @ValueInUse = Value_In_Use
                FROM master.sys.configurations
                WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES

                IF CONVERT(INT, @ValueInUse) <> @YES
                BEGIN
                SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''OLE Automation Procedures'' server option, this option is still disabled.' + CHAR(13)
                + 'This script depends upon this extended stored procedure''s availability. Please investigate.'
                + CHAR(13) + 'NOTE: ''OLE Automation Procedures'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

                RAISERROR(@ErrorMessage, 16, 1)
                END

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                -- 1) Remove records older than user-provided number of days

                BEGIN TRY

                -- By default, I'd like to keep 2 years of data
                SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())

                DELETE dbo.DriveSpace
                WHERE EntryDate < @AgeOutDate

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                -- 2) Get free space on drives

                BEGIN TRY
                -- Populate temp table; this system proc gives us space in MB
                INSERT INTO #Space (DriveLetter, AvailableMB)
                EXEC master.dbo.xp_fixeddrives

                -- 3) Loop through drives and get various attributes using OLE automation

                WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)

                BEGIN

                SELECT TOP 1
                @PK = PK,
                @DriveLetter = DriveLetter,
                @AvailableMB = AvailableMB,
                @DriveSize = DriveSize
                FROM #Space
                WHERE Processed = @No

                BEGIN TRY
                EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                BEGIN TRY

                -- Get disk info
                SET @DriveLetter = @DriveLetter + ':'
                EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
                EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
                EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
                EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                BEGIN TRY
                -- Update table
                UPDATE #Space
                SET
                VolumeName =
                CASE LOWER(@DriveLetter)
                WHEN 'c:' THEN 'System'
                ELSE @VolumeName
                END,
                -- Convert bytes to megabytes
                DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
                -- Convert bytes to megabytes
                AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
                WHERE PK = @PK;

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                BEGIN TRY

                -- Destroy oDrive
                EXEC master.dbo.sp_OADestroy @oDrive

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                UPDATE #Space
                SET Processed = @YES
                WHERE PK = @PK

                END

                BEGIN TRY

                -- Destroy FSO
                EXEC master.dbo.sp_OADestroy @FSO

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                -- 4) Load results in table

                -- Return disk-space info
                INSERT dbo.DriveSpace
                (
                DriveLetter,
                VolumeName,
                AvailableMB,
                TotalDiskSpaceMB,
                EntryDate
                )

                SELECT
                DriveLetter DriveLetter,
                CASE
                WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
                ELSE VolumeName
                END VolumeName,
                AvailableMB AvailableMB,
                DriveSize TotalDriveSpaceMB,
                @BATCHDATE LoadDate
                FROM #Space

                END TRY

                BEGIN CATCH
                SELECT
                ERROR_NUMBER() AS ErrorNumber
                ,ERROR_MESSAGE() AS ErrorMessage
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_PROCEDURE() AS ErrorProcedure

                GOTO Cleanup
                END CATCH

                Cleanup:

                -- Ensure xp_cmdshell's off
                EXEC sp_configure 'OLE Automation Procedures', @NO;
                RECONFIGURE



                GO





                share|improve this answer


























                • You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                  – Kin
                  Jun 29 '18 at 15:49











                • This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                  – EOA
                  Jun 29 '18 at 16:59



















                0














                Something I put together. You NEED POWERSHELL on your SQL Server to run this. Not sure of the version. This will take your drive sizes and report back the file sizes and free space as well as free drive space.



                It's not 100% original and parts of it I found elsewhere on the internet and put the whole thing together. It was a PITA to get it to fit into the right margins so you might have to play with the parentheses



                --drop table ##DB_FILE_INFO
                --alter procedure super_drive_info3

                --as

                if exists ( select * from tempdb.dbo.sysobjects o
                where o.xtype in ('U') and o.name in ('##DB_FILE_INFO' ))
                drop table ##DB_FILE_INFO

                if exists ( select * from tempdb.dbo.sysobjects o
                where o.xtype in ('U') and o.name in ('##output'))
                drop table ##output

                create table ##DB_FILE_INFO (
                [server_Name] varchar(255) not null,
                [database_name] varchar(255) not null,
                [File_ID] int not null,
                [File_Type] int not null ,
                [Drive] varchar(255) not null ,
                [Logical_Name] varchar(255) not null ,
                [Physical_Name] varchar(255) not null ,
                [FILE_SIZE_MB] int not null ,
                [SPace_USED_MB] int not null ,
                [Free_space] int not null ,
                [Max_SIZE] int not null ,
                [Percent_Log_growth_enabled] int not null ,
                [growth_rate] int not null ,
                [current_date] datetime not null
                )
                --go
                declare @sql nvarchar(4000)
                set @sql =
                'use ['+'?'+']
                --if db_name() <> N''?'' goto Error_Exit
                insert into ##DB_FILE_INFO
                (
                [server_Name],
                [database_name],
                [File_ID],
                [File_Type],
                [Drive],
                [Logical_Name],
                [Physical_Name],
                [FILE_SIZE_MB],
                [SPace_USED_MB],
                [Free_space],
                [Max_SIZE],
                [Percent_Log_growth_enabled],
                [growth_rate],
                [current_date]
                )
                SELECT
                @@servername as [Server_Name],
                db_name() as database_name,
                [file_id] AS [File_ID],
                [type] AS [File_Type],
                substring([physical_name],1,1) AS [Drive],
                [name] AS [Logical_Name],
                [physical_name] AS [Physical_Name],
                CAST([size] as DECIMAL(38,0))/128. AS [File Size MB],
                CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS
                [Space_Used_MB],
                (CAST([size] AS DECIMAL(38,0))/128) -
                (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free
                Space],
                [max_size] AS [Max_Size],
                [is_percent_growth] AS [Percent_Growth_Enabled],
                [growth] AS [Growth Rate],
                getdate() AS [Current_Date]
                FROM sys.database_files'
                exec sp_msforeachdb @sql
                declare @svrName varchar(255)
                declare @sql2 varchar(400)
                --by default it will take the current server name, we can the set the server
                name as well
                set @svrName = @@SERVERNAME
                set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' +
                QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' |
                select name,capacity,freespace |

                foreach{$_.name+''|
                ''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
                --creating a temporary table
                CREATE TABLE ##output
                (line varchar(255))
                --inserting in to temporary table
                insert ##output
                EXEC xp_cmdshell @sql2;

                with Output2
                --(drivename, capacity(gb),freespace(gb), always_on_status)
                as
                (
                select @@servername as server_name,
                rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename
                ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
                (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as
                'capacityGB'
                ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
                (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as
                'freespaceGB'
                ,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN
                (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS
                ALWAYS_ON_STATUS
                --into #output2
                from ##output
                where line like '[A-Z][:]%'
                --order by drivename
                ),
                DB_FILE_INFO2 as
                (
                select server_Name,
                database_name,
                File_ID,
                File_Type,
                Drive,
                Logical_Name,
                Physical_Name,
                FILE_SIZE_MB,
                SPace_USED_MB,
                Free_space,
                Max_SIZE,
                Percent_Log_growth_enabled,
                growth_rate
                --current_date
                from ##DB_FILE_INFO
                --inner join #output b on a.drive = b.drivename and a.server_Name =
                b.server_name
                )
                select
                getdate() as Today_Date,
                a.server_Name,
                a.database_name,
                a.Drive,
                a.Logical_Name,
                a.Physical_Name,
                a.FILE_SIZE_MB,
                a.Space_Used_MB,
                --sum(a.SPace_USED_MB) as hg,
                a.Free_space as Free_Space_in_File,
                --Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),
                b.capacitygb as Total_Drive_capacity,
                b.freespacegb as Total_Free_Space,
                c.total_log_size_mb,
                c.active_log_size_mb
                --,Percentage_free_space = ((cast(Free_space as decimal))/(cast(FILE_SIZE_MB
                as decimal)) * 100)

                from DB_FILE_INFO2 a
                inner join output2 b on a.server_Name = b.server_name and a.Drive =
                b.drivename
                cross apply sys.dm_db_log_stats (db_id(a.database_name)) c
                order by a.Drive, a.database_name

                --drop table ##DB_FILE_INFO
                --drop table #output





                share|improve this answer































                  0














                  How about asking M$ for some really simple view that you can "select * from"?



                  Instead everyone is doing some extra functins, extra selects, extra tables and so on...



                  If M$ would build cars, you would have to bring your own wheels, because M$ isn't able to see, that they would be very useful for the users....






                  share|improve this answer








                  New contributor




                  Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.




















                    Your Answer








                    StackExchange.ready(function() {
                    var channelOptions = {
                    tags: "".split(" "),
                    id: "182"
                    };
                    initTagRenderer("".split(" "), "".split(" "), channelOptions);

                    StackExchange.using("externalEditor", function() {
                    // Have to fire editor after snippets, if snippets enabled
                    if (StackExchange.settings.snippets.snippetsEnabled) {
                    StackExchange.using("snippets", function() {
                    createEditor();
                    });
                    }
                    else {
                    createEditor();
                    }
                    });

                    function createEditor() {
                    StackExchange.prepareEditor({
                    heartbeatType: 'answer',
                    autoActivateHeartbeat: false,
                    convertImagesToLinks: false,
                    noModals: true,
                    showLowRepImageUploadWarning: true,
                    reputationToPostImages: null,
                    bindNavPrevention: true,
                    postfix: "",
                    imageUploader: {
                    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
                    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
                    allowUrls: true
                    },
                    onDemand: true,
                    discardSelector: ".discard-answer"
                    ,immediatelyShowMarkdownHelp:true
                    });


                    }
                    });














                    draft saved

                    draft discarded


















                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f29543%2fquery-to-report-disk-space-allocation-and-used-space%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown

























                    7 Answers
                    7






                    active

                    oldest

                    votes








                    7 Answers
                    7






                    active

                    oldest

                    votes









                    active

                    oldest

                    votes






                    active

                    oldest

                    votes









                    33














                    Here is the query Management Studio uses to populate those numbers:



                    SELECT
                    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                    WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                    SUM(a.total_pages) AS [SpaceUsed],
                    (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                    WHERE df.type in (1, 3)) AS [LogSize]
                    FROM
                    sys.partitions p join sys.allocation_units a
                    on p.partition_id = a.container_id
                    left join sys.internal_tables it
                    on p.object_id = it.object_id


                    You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:



                    SELECT
                    (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                    FROM sys.database_files AS df
                    WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                    CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
                    (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                    FROM sys.database_files AS df
                    WHERE df.type in (1, 3)) AS [LogSize]
                    FROM sys.partitions p join sys.allocation_units a
                    on p.partition_id = a.container_id;


                    With this update, assuming your database doesn't change in the meantime, it should yield:



                    753475.94    744030.07    2900.00


                    Doing some simple math, and isolating just the three numbers you want:



                    ;WITH t(s) AS
                    (
                    SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
                    FROM sys.database_files
                    WHERE [type] % 2 = 0
                    ),
                    d(s) AS
                    (
                    SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
                    FROM sys.partitions AS p
                    INNER JOIN sys.allocation_units AS a
                    ON p.[partition_id] = a.container_id
                    )
                    SELECT
                    Allocated_Space = t.s,
                    Available_Space = t.s - d.s,
                    [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
                    FROM t CROSS APPLY d;





                    share|improve this answer






























                      33














                      Here is the query Management Studio uses to populate those numbers:



                      SELECT
                      (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                      WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                      SUM(a.total_pages) AS [SpaceUsed],
                      (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                      WHERE df.type in (1, 3)) AS [LogSize]
                      FROM
                      sys.partitions p join sys.allocation_units a
                      on p.partition_id = a.container_id
                      left join sys.internal_tables it
                      on p.object_id = it.object_id


                      You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:



                      SELECT
                      (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                      FROM sys.database_files AS df
                      WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                      CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
                      (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                      FROM sys.database_files AS df
                      WHERE df.type in (1, 3)) AS [LogSize]
                      FROM sys.partitions p join sys.allocation_units a
                      on p.partition_id = a.container_id;


                      With this update, assuming your database doesn't change in the meantime, it should yield:



                      753475.94    744030.07    2900.00


                      Doing some simple math, and isolating just the three numbers you want:



                      ;WITH t(s) AS
                      (
                      SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
                      FROM sys.database_files
                      WHERE [type] % 2 = 0
                      ),
                      d(s) AS
                      (
                      SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
                      FROM sys.partitions AS p
                      INNER JOIN sys.allocation_units AS a
                      ON p.[partition_id] = a.container_id
                      )
                      SELECT
                      Allocated_Space = t.s,
                      Available_Space = t.s - d.s,
                      [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
                      FROM t CROSS APPLY d;





                      share|improve this answer




























                        33












                        33








                        33







                        Here is the query Management Studio uses to populate those numbers:



                        SELECT
                        (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                        WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                        SUM(a.total_pages) AS [SpaceUsed],
                        (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                        WHERE df.type in (1, 3)) AS [LogSize]
                        FROM
                        sys.partitions p join sys.allocation_units a
                        on p.partition_id = a.container_id
                        left join sys.internal_tables it
                        on p.object_id = it.object_id


                        You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:



                        SELECT
                        (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                        FROM sys.database_files AS df
                        WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                        CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
                        (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                        FROM sys.database_files AS df
                        WHERE df.type in (1, 3)) AS [LogSize]
                        FROM sys.partitions p join sys.allocation_units a
                        on p.partition_id = a.container_id;


                        With this update, assuming your database doesn't change in the meantime, it should yield:



                        753475.94    744030.07    2900.00


                        Doing some simple math, and isolating just the three numbers you want:



                        ;WITH t(s) AS
                        (
                        SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
                        FROM sys.database_files
                        WHERE [type] % 2 = 0
                        ),
                        d(s) AS
                        (
                        SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
                        FROM sys.partitions AS p
                        INNER JOIN sys.allocation_units AS a
                        ON p.[partition_id] = a.container_id
                        )
                        SELECT
                        Allocated_Space = t.s,
                        Available_Space = t.s - d.s,
                        [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
                        FROM t CROSS APPLY d;





                        share|improve this answer















                        Here is the query Management Studio uses to populate those numbers:



                        SELECT
                        (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                        WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                        SUM(a.total_pages) AS [SpaceUsed],
                        (SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df
                        WHERE df.type in (1, 3)) AS [LogSize]
                        FROM
                        sys.partitions p join sys.allocation_units a
                        on p.partition_id = a.container_id
                        left join sys.internal_tables it
                        on p.object_id = it.object_id


                        You need to perform math here just like Management Studio does in order to get the same numbers. Also, the left join to sys.internal_tables seems superfluous at best. So adjusting that query to match your ideal output:



                        SELECT
                        (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                        FROM sys.database_files AS df
                        WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
                        CONVERT(DECIMAL(18,2), SUM(a.total_pages)*8/1024.0) AS [SpaceUsed],
                        (SELECT CONVERT(DECIMAL(18,2), SUM(CAST(df.size as float))*8/1024.0)
                        FROM sys.database_files AS df
                        WHERE df.type in (1, 3)) AS [LogSize]
                        FROM sys.partitions p join sys.allocation_units a
                        on p.partition_id = a.container_id;


                        With this update, assuming your database doesn't change in the meantime, it should yield:



                        753475.94    744030.07    2900.00


                        Doing some simple math, and isolating just the three numbers you want:



                        ;WITH t(s) AS
                        (
                        SELECT CONVERT(DECIMAL(18,2), SUM(size)*8/1024.0)
                        FROM sys.database_files
                        WHERE [type] % 2 = 0
                        ),
                        d(s) AS
                        (
                        SELECT CONVERT(DECIMAL(18,2), SUM(total_pages)*8/1024.0)
                        FROM sys.partitions AS p
                        INNER JOIN sys.allocation_units AS a
                        ON p.[partition_id] = a.container_id
                        )
                        SELECT
                        Allocated_Space = t.s,
                        Available_Space = t.s - d.s,
                        [Available_%] = CONVERT(DECIMAL(5,2), (t.s - d.s)*100.0/t.s)
                        FROM t CROSS APPLY d;






                        share|improve this answer














                        share|improve this answer



                        share|improve this answer








                        edited Nov 30 '12 at 23:49

























                        answered Nov 29 '12 at 19:22









                        Aaron BertrandAaron Bertrand

                        150k18284482




                        150k18284482

























                            20














                            Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries

                            (you would need to change the math for TBs):



                            -- Individual File Sizes and space available for current database
                            -- (Query 36) (File Sizes and Space)
                            SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
                            CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
                            CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
                            AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
                            FROM sys.database_files AS f WITH (NOLOCK)
                            LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
                            ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);





                            share|improve this answer






























                              20














                              Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries

                              (you would need to change the math for TBs):



                              -- Individual File Sizes and space available for current database
                              -- (Query 36) (File Sizes and Space)
                              SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
                              CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
                              CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
                              AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
                              FROM sys.database_files AS f WITH (NOLOCK)
                              LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
                              ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);





                              share|improve this answer




























                                20












                                20








                                20







                                Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries

                                (you would need to change the math for TBs):



                                -- Individual File Sizes and space available for current database
                                -- (Query 36) (File Sizes and Space)
                                SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
                                CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
                                CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
                                AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
                                FROM sys.database_files AS f WITH (NOLOCK)
                                LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
                                ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);





                                share|improve this answer















                                Aaron's query is good, but as an alternative, I use this query from Glenn Berry's DMV Queries

                                (you would need to change the math for TBs):



                                -- Individual File Sizes and space available for current database
                                -- (Query 36) (File Sizes and Space)
                                SELECT f.name AS [File Name] , f.physical_name AS [Physical Name],
                                CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
                                CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2))
                                AS [Available Space In MB], [file_id], fg.name AS [Filegroup Name]
                                FROM sys.database_files AS f WITH (NOLOCK)
                                LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
                                ON f.data_space_id = fg.data_space_id OPTION (RECOMPILE);






                                share|improve this answer














                                share|improve this answer



                                share|improve this answer








                                edited Aug 27 '15 at 10:53









                                Paul White

                                49.5k14261415




                                49.5k14261415










                                answered Nov 29 '12 at 20:56









                                Mike FalMike Fal

                                10.4k13253




                                10.4k13253























                                    7














                                    Here's some SQL that might work for you.




                                    Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL,
                                    filName varchar(520) NULL, sizeMg decimal(10,2) null,
                                    usedMg decimal(10,2) null, freeMg decimal(10,2) null,
                                    pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
                                    Declare @sSql varchar(1000)
                                    Set @sSql = 'Use [?];
                                    Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
                                    Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)),
                                    Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
                                    From dbo.sysfiles Order By groupId Desc;'
                                    Exec sp_MSforeachdb @sSql
                                    Update #dbInfo Set
                                    freeMg = sizeMg - usedMg,
                                    pcntUsed = (usedMg/sizeMg)*100,
                                    pcntFree = ((sizeMg-usedMg)/sizeMg)*100

                                    select * from #dbInfo compute sum(sizeMG), sum(FreeMg)
                                    drop table #dbInfo


                                    It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.






                                    share|improve this answer





















                                    • 1





                                      Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                                      – Senior Systems Engineer
                                      Jul 23 '14 at 0:04











                                    • There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                                      – Tobb
                                      Sep 9 '16 at 12:18






                                    • 1





                                      The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                                      – Bruce
                                      Sep 9 '16 at 23:26
















                                    7














                                    Here's some SQL that might work for you.




                                    Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL,
                                    filName varchar(520) NULL, sizeMg decimal(10,2) null,
                                    usedMg decimal(10,2) null, freeMg decimal(10,2) null,
                                    pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
                                    Declare @sSql varchar(1000)
                                    Set @sSql = 'Use [?];
                                    Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
                                    Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)),
                                    Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
                                    From dbo.sysfiles Order By groupId Desc;'
                                    Exec sp_MSforeachdb @sSql
                                    Update #dbInfo Set
                                    freeMg = sizeMg - usedMg,
                                    pcntUsed = (usedMg/sizeMg)*100,
                                    pcntFree = ((sizeMg-usedMg)/sizeMg)*100

                                    select * from #dbInfo compute sum(sizeMG), sum(FreeMg)
                                    drop table #dbInfo


                                    It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.






                                    share|improve this answer





















                                    • 1





                                      Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                                      – Senior Systems Engineer
                                      Jul 23 '14 at 0:04











                                    • There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                                      – Tobb
                                      Sep 9 '16 at 12:18






                                    • 1





                                      The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                                      – Bruce
                                      Sep 9 '16 at 23:26














                                    7












                                    7








                                    7







                                    Here's some SQL that might work for you.




                                    Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL,
                                    filName varchar(520) NULL, sizeMg decimal(10,2) null,
                                    usedMg decimal(10,2) null, freeMg decimal(10,2) null,
                                    pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
                                    Declare @sSql varchar(1000)
                                    Set @sSql = 'Use [?];
                                    Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
                                    Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)),
                                    Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
                                    From dbo.sysfiles Order By groupId Desc;'
                                    Exec sp_MSforeachdb @sSql
                                    Update #dbInfo Set
                                    freeMg = sizeMg - usedMg,
                                    pcntUsed = (usedMg/sizeMg)*100,
                                    pcntFree = ((sizeMg-usedMg)/sizeMg)*100

                                    select * from #dbInfo compute sum(sizeMG), sum(FreeMg)
                                    drop table #dbInfo


                                    It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.






                                    share|improve this answer















                                    Here's some SQL that might work for you.




                                    Create Table #dbInfo (dId smallint, dbName sysname, gId smallint NULL, segName varchar(256) NULL,
                                    filName varchar(520) NULL, sizeMg decimal(10,2) null,
                                    usedMg decimal(10,2) null, freeMg decimal(10,2) null,
                                    pcntUsed decimal(10,2) null, pcntFree decimal(10,2) null)
                                    Declare @sSql varchar(1000)
                                    Set @sSql = 'Use [?];
                                    Insert #dbInfo (dId, dbName, gid, segName, filName, sizeMg, usedMg)
                                    Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)),
                                    Cast(Fileproperty(name, ''SpaceUsed'')/128.0 As Decimal(10,2))
                                    From dbo.sysfiles Order By groupId Desc;'
                                    Exec sp_MSforeachdb @sSql
                                    Update #dbInfo Set
                                    freeMg = sizeMg - usedMg,
                                    pcntUsed = (usedMg/sizeMg)*100,
                                    pcntFree = ((sizeMg-usedMg)/sizeMg)*100

                                    select * from #dbInfo compute sum(sizeMG), sum(FreeMg)
                                    drop table #dbInfo


                                    It's old SQL but still works. One of these days I'm going to rewrite it to use the sys.database_files like the answer from Aaron.







                                    share|improve this answer














                                    share|improve this answer



                                    share|improve this answer








                                    edited Nov 29 '12 at 19:57

























                                    answered Nov 29 '12 at 19:31









                                    BruceBruce

                                    66768




                                    66768








                                    • 1





                                      Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                                      – Senior Systems Engineer
                                      Jul 23 '14 at 0:04











                                    • There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                                      – Tobb
                                      Sep 9 '16 at 12:18






                                    • 1





                                      The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                                      – Bruce
                                      Sep 9 '16 at 23:26














                                    • 1





                                      Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                                      – Senior Systems Engineer
                                      Jul 23 '14 at 0:04











                                    • There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                                      – Tobb
                                      Sep 9 '16 at 12:18






                                    • 1





                                      The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                                      – Bruce
                                      Sep 9 '16 at 23:26








                                    1




                                    1





                                    Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                                    – Senior Systems Engineer
                                    Jul 23 '14 at 0:04





                                    Msg 156, Level 15, State 1, Line 17 Incorrect syntax near the keyword 'compute'.

                                    – Senior Systems Engineer
                                    Jul 23 '14 at 0:04













                                    There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                                    – Tobb
                                    Sep 9 '16 at 12:18





                                    There a syntax error at compute, but +1 for query that lists all databases when the server has multiple.

                                    – Tobb
                                    Sep 9 '16 at 12:18




                                    1




                                    1





                                    The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                                    – Bruce
                                    Sep 9 '16 at 23:26





                                    The compute wasn't part of my original answer but was added by someone else. I've suggested that instead of editing my answer they post one of their own. But thanks for the up-vote.

                                    – Bruce
                                    Sep 9 '16 at 23:26











                                    5














                                    After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users.



                                    This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated. A user would need to change the calculation for 'Size_On_Disk_Bytes' if the disk is formatted using an allocation size different from the default (4096 bytes).



                                    SELECT fg.data_space_id AS FGID,
                                    (f.file_id) AS File_Id,
                                    -- As provided by OP, size on disk in bytes.
                                    REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, CAST(f.size AS FLOAT) * 8.00 * 1024), 1), '.00','') AS Size_On_Disk_Bytes,
                                    ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
                                    ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
                                    ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
                                    ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
                                    f.name,
                                    f.physical_name
                                    FROM sys.database_files f
                                    LEFT JOIN sys.filegroups fg
                                    ON f.data_space_id = fg.data_space_id


                                    In addition, this query will return information about the space on the volume drive that hosts the files. The user will need to have access to sys.master_files.



                                    SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
                                    dovs.volume_mount_point AS Drive,
                                    CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
                                    FROM sys.master_files mf
                                    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
                                    ORDER BY FreeSpaceInMB ASC





                                    share|improve this answer






























                                      5














                                      After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users.



                                      This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated. A user would need to change the calculation for 'Size_On_Disk_Bytes' if the disk is formatted using an allocation size different from the default (4096 bytes).



                                      SELECT fg.data_space_id AS FGID,
                                      (f.file_id) AS File_Id,
                                      -- As provided by OP, size on disk in bytes.
                                      REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, CAST(f.size AS FLOAT) * 8.00 * 1024), 1), '.00','') AS Size_On_Disk_Bytes,
                                      ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
                                      ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
                                      ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
                                      ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
                                      f.name,
                                      f.physical_name
                                      FROM sys.database_files f
                                      LEFT JOIN sys.filegroups fg
                                      ON f.data_space_id = fg.data_space_id


                                      In addition, this query will return information about the space on the volume drive that hosts the files. The user will need to have access to sys.master_files.



                                      SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
                                      dovs.volume_mount_point AS Drive,
                                      CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
                                      FROM sys.master_files mf
                                      CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
                                      ORDER BY FreeSpaceInMB ASC





                                      share|improve this answer




























                                        5












                                        5








                                        5







                                        After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users.



                                        This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated. A user would need to change the calculation for 'Size_On_Disk_Bytes' if the disk is formatted using an allocation size different from the default (4096 bytes).



                                        SELECT fg.data_space_id AS FGID,
                                        (f.file_id) AS File_Id,
                                        -- As provided by OP, size on disk in bytes.
                                        REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, CAST(f.size AS FLOAT) * 8.00 * 1024), 1), '.00','') AS Size_On_Disk_Bytes,
                                        ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
                                        ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
                                        ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
                                        ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
                                        f.name,
                                        f.physical_name
                                        FROM sys.database_files f
                                        LEFT JOIN sys.filegroups fg
                                        ON f.data_space_id = fg.data_space_id


                                        In addition, this query will return information about the space on the volume drive that hosts the files. The user will need to have access to sys.master_files.



                                        SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
                                        dovs.volume_mount_point AS Drive,
                                        CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
                                        FROM sys.master_files mf
                                        CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
                                        ORDER BY FreeSpaceInMB ASC





                                        share|improve this answer















                                        After working on a similar question (Querying True Physical Database File Sizes), I think a query that display the different file sizes, separated, e.g. by log and database, and the size of the file on disk would be more helpful to most users.



                                        This includes the system file size and how much space it's taking on disk, as well as the SQL file size, SQL space used, and by extension SQL free space as well. It includes the full path of the file being evaluated. A user would need to change the calculation for 'Size_On_Disk_Bytes' if the disk is formatted using an allocation size different from the default (4096 bytes).



                                        SELECT fg.data_space_id AS FGID,
                                        (f.file_id) AS File_Id,
                                        -- As provided by OP, size on disk in bytes.
                                        REPLACE(CONVERT(VARCHAR,CONVERT(MONEY, CAST(f.size AS FLOAT) * 8.00 * 1024), 1), '.00','') AS Size_On_Disk_Bytes,
                                        ROUND((CAST(f.size AS FLOAT) * 8.00/1024)/1024,3) AS Actual_File_Size,
                                        ROUND(CAST((f.size) AS FLOAT)/128,2) AS Reserved_MB,
                                        ROUND(CAST((FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
                                        ROUND((CAST((f.size) AS FLOAT)/128)-(CAST((FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB,
                                        f.name,
                                        f.physical_name
                                        FROM sys.database_files f
                                        LEFT JOIN sys.filegroups fg
                                        ON f.data_space_id = fg.data_space_id


                                        In addition, this query will return information about the space on the volume drive that hosts the files. The user will need to have access to sys.master_files.



                                        SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
                                        dovs.volume_mount_point AS Drive,
                                        CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
                                        FROM sys.master_files mf
                                        CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
                                        ORDER BY FreeSpaceInMB ASC






                                        share|improve this answer














                                        share|improve this answer



                                        share|improve this answer








                                        edited Apr 13 '17 at 12:42









                                        Community

                                        1




                                        1










                                        answered Jun 27 '16 at 16:19









                                        JohnJohn

                                        502312




                                        502312























                                            0














                                            USE [db]
                                            GO

                                            /****** Object: StoredProcedure [dbo].[GetDriveSpace] Script Date: 6/29/2018 9:22:12 AM ******/
                                            SET ANSI_NULLS ON
                                            GO

                                            SET QUOTED_IDENTIFIER ON
                                            GO





                                            CREATE PROCEDURE [dbo].[GetDriveSpace]

                                            @DaysToRetain SMALLINT = 90

                                            AS

                                            SET NOCOUNT ON

                                            -- Persisted work table

                                            IF OBJECT_ID('<db>.dbo.DriveSpace') IS NULL
                                            BEGIN
                                            Create TABLE dbo.DriveSpace
                                            (
                                            PK INT IDENTITY CONSTRAINT PK_DriveSpace PRIMARY KEY,
                                            DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            AvailableMB BIGINT NOT NULL,
                                            TotalDriveSpaceMB BIGINT NOT NULL,
                                            [TotalSpaceGB] AS ([TotalDriveSpaceMB]/(1024.00)),
                                            [FreeSpaceGB] AS ([AvailableMB]/(1024.00)),
                                            PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDriveSpaceMB) * 100),
                                            EntryDate DATETIME NOT NULL
                                            ) ON [PRIMARY]
                                            CREATE NONCLUSTERED INDEX NCnd_EntryDate
                                            ON dbo.DriveSpace (EntryDate)
                                            END

                                            -- Vars
                                            DECLARE

                                            @AgeOutDate DATETIME, -- To prune the [DriveSpace] table
                                            @BATCHDATE DATETIME, -- Constant
                                            @DriveSize BIGINT, -- Drive size in bytes
                                            @DriveLetter VARCHAR(2), -- FSO drive letter
                                            @ErrorMessage VARCHAR(500), -- Error message
                                            @FSO INT, -- File System Object
                                            @AvailableMB INT, -- Available space MB
                                            @NO BIT, -- Constant
                                            @oDrive INT, -- FSO drive
                                            @OLE_AUTOMATION_PROCEDURES INT, -- Constant
                                            @PK TINYINT, -- Work-table primary key
                                            @RawFree VARCHAR(20), -- Available space bytes
                                            @RetVal INT, -- Return value
                                            @SQL NVARCHAR(255), -- Dynamic T-SQL
                                            @TwoToTheTwentieth INT, -- Convert bytes to MB
                                            @ValueInUse SQL_VARIANT, -- To test configuration settings
                                            @VolumeName VARCHAR(32), -- FSO volume name
                                            @YES BIT -- Constant

                                            -- Constants
                                            SET @BATCHDATE = GETDATE()
                                            SET @OLE_AUTOMATION_PROCEDURES = 16388 -- SELECT configuration_id FROM master.sys.configurations
                                            SET @NO = 0
                                            SET @TwoToTheTwentieth = POWER(2, 20)
                                            SET @YES = 1

                                            -- Create temp tables for disk space info
                                            IF OBJECT_ID('tempdb.dbo.#Space') IS NOT NULL DROP TABLE #Space
                                            CREATE TABLE #Space
                                            (
                                            PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
                                            DriveLetter VARCHAR(2) NOT NULL,
                                            VolumeName VARCHAR(32) NULL,
                                            DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
                                            AvailableMB BIGINT NOT NULL,
                                            Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
                                            )

                                            -- We need OLE automation enabled to succeed.

                                            BEGIN TRY
                                            -- Enable advanced options
                                            EXEC sp_configure 'show advanced option', '1';
                                            RECONFIGURE;

                                            -- Ensure OLE Automation enabled
                                            EXEC sp_configure 'OLE Automation Procedures', @YES;
                                            RECONFIGURE

                                            -- Test...
                                            SELECT @ValueInUse = Value_In_Use
                                            FROM master.sys.configurations
                                            WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES

                                            IF CONVERT(INT, @ValueInUse) <> @YES
                                            BEGIN
                                            SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''OLE Automation Procedures'' server option, this option is still disabled.' + CHAR(13)
                                            + 'This script depends upon this extended stored procedure''s availability. Please investigate.'
                                            + CHAR(13) + 'NOTE: ''OLE Automation Procedures'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

                                            RAISERROR(@ErrorMessage, 16, 1)
                                            END

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 1) Remove records older than user-provided number of days

                                            BEGIN TRY

                                            -- By default, I'd like to keep 2 years of data
                                            SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())

                                            DELETE dbo.DriveSpace
                                            WHERE EntryDate < @AgeOutDate

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 2) Get free space on drives

                                            BEGIN TRY
                                            -- Populate temp table; this system proc gives us space in MB
                                            INSERT INTO #Space (DriveLetter, AvailableMB)
                                            EXEC master.dbo.xp_fixeddrives

                                            -- 3) Loop through drives and get various attributes using OLE automation

                                            WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)

                                            BEGIN

                                            SELECT TOP 1
                                            @PK = PK,
                                            @DriveLetter = DriveLetter,
                                            @AvailableMB = AvailableMB,
                                            @DriveSize = DriveSize
                                            FROM #Space
                                            WHERE Processed = @No

                                            BEGIN TRY
                                            EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Get disk info
                                            SET @DriveLetter = @DriveLetter + ':'
                                            EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
                                            EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY
                                            -- Update table
                                            UPDATE #Space
                                            SET
                                            VolumeName =
                                            CASE LOWER(@DriveLetter)
                                            WHEN 'c:' THEN 'System'
                                            ELSE @VolumeName
                                            END,
                                            -- Convert bytes to megabytes
                                            DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
                                            -- Convert bytes to megabytes
                                            AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
                                            WHERE PK = @PK;

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Destroy oDrive
                                            EXEC master.dbo.sp_OADestroy @oDrive

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            UPDATE #Space
                                            SET Processed = @YES
                                            WHERE PK = @PK

                                            END

                                            BEGIN TRY

                                            -- Destroy FSO
                                            EXEC master.dbo.sp_OADestroy @FSO

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 4) Load results in table

                                            -- Return disk-space info
                                            INSERT dbo.DriveSpace
                                            (
                                            DriveLetter,
                                            VolumeName,
                                            AvailableMB,
                                            TotalDiskSpaceMB,
                                            EntryDate
                                            )

                                            SELECT
                                            DriveLetter DriveLetter,
                                            CASE
                                            WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
                                            ELSE VolumeName
                                            END VolumeName,
                                            AvailableMB AvailableMB,
                                            DriveSize TotalDriveSpaceMB,
                                            @BATCHDATE LoadDate
                                            FROM #Space

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            Cleanup:

                                            -- Ensure xp_cmdshell's off
                                            EXEC sp_configure 'OLE Automation Procedures', @NO;
                                            RECONFIGURE



                                            GO





                                            share|improve this answer


























                                            • You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                                              – Kin
                                              Jun 29 '18 at 15:49











                                            • This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                                              – EOA
                                              Jun 29 '18 at 16:59
















                                            0














                                            USE [db]
                                            GO

                                            /****** Object: StoredProcedure [dbo].[GetDriveSpace] Script Date: 6/29/2018 9:22:12 AM ******/
                                            SET ANSI_NULLS ON
                                            GO

                                            SET QUOTED_IDENTIFIER ON
                                            GO





                                            CREATE PROCEDURE [dbo].[GetDriveSpace]

                                            @DaysToRetain SMALLINT = 90

                                            AS

                                            SET NOCOUNT ON

                                            -- Persisted work table

                                            IF OBJECT_ID('<db>.dbo.DriveSpace') IS NULL
                                            BEGIN
                                            Create TABLE dbo.DriveSpace
                                            (
                                            PK INT IDENTITY CONSTRAINT PK_DriveSpace PRIMARY KEY,
                                            DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            AvailableMB BIGINT NOT NULL,
                                            TotalDriveSpaceMB BIGINT NOT NULL,
                                            [TotalSpaceGB] AS ([TotalDriveSpaceMB]/(1024.00)),
                                            [FreeSpaceGB] AS ([AvailableMB]/(1024.00)),
                                            PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDriveSpaceMB) * 100),
                                            EntryDate DATETIME NOT NULL
                                            ) ON [PRIMARY]
                                            CREATE NONCLUSTERED INDEX NCnd_EntryDate
                                            ON dbo.DriveSpace (EntryDate)
                                            END

                                            -- Vars
                                            DECLARE

                                            @AgeOutDate DATETIME, -- To prune the [DriveSpace] table
                                            @BATCHDATE DATETIME, -- Constant
                                            @DriveSize BIGINT, -- Drive size in bytes
                                            @DriveLetter VARCHAR(2), -- FSO drive letter
                                            @ErrorMessage VARCHAR(500), -- Error message
                                            @FSO INT, -- File System Object
                                            @AvailableMB INT, -- Available space MB
                                            @NO BIT, -- Constant
                                            @oDrive INT, -- FSO drive
                                            @OLE_AUTOMATION_PROCEDURES INT, -- Constant
                                            @PK TINYINT, -- Work-table primary key
                                            @RawFree VARCHAR(20), -- Available space bytes
                                            @RetVal INT, -- Return value
                                            @SQL NVARCHAR(255), -- Dynamic T-SQL
                                            @TwoToTheTwentieth INT, -- Convert bytes to MB
                                            @ValueInUse SQL_VARIANT, -- To test configuration settings
                                            @VolumeName VARCHAR(32), -- FSO volume name
                                            @YES BIT -- Constant

                                            -- Constants
                                            SET @BATCHDATE = GETDATE()
                                            SET @OLE_AUTOMATION_PROCEDURES = 16388 -- SELECT configuration_id FROM master.sys.configurations
                                            SET @NO = 0
                                            SET @TwoToTheTwentieth = POWER(2, 20)
                                            SET @YES = 1

                                            -- Create temp tables for disk space info
                                            IF OBJECT_ID('tempdb.dbo.#Space') IS NOT NULL DROP TABLE #Space
                                            CREATE TABLE #Space
                                            (
                                            PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
                                            DriveLetter VARCHAR(2) NOT NULL,
                                            VolumeName VARCHAR(32) NULL,
                                            DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
                                            AvailableMB BIGINT NOT NULL,
                                            Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
                                            )

                                            -- We need OLE automation enabled to succeed.

                                            BEGIN TRY
                                            -- Enable advanced options
                                            EXEC sp_configure 'show advanced option', '1';
                                            RECONFIGURE;

                                            -- Ensure OLE Automation enabled
                                            EXEC sp_configure 'OLE Automation Procedures', @YES;
                                            RECONFIGURE

                                            -- Test...
                                            SELECT @ValueInUse = Value_In_Use
                                            FROM master.sys.configurations
                                            WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES

                                            IF CONVERT(INT, @ValueInUse) <> @YES
                                            BEGIN
                                            SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''OLE Automation Procedures'' server option, this option is still disabled.' + CHAR(13)
                                            + 'This script depends upon this extended stored procedure''s availability. Please investigate.'
                                            + CHAR(13) + 'NOTE: ''OLE Automation Procedures'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

                                            RAISERROR(@ErrorMessage, 16, 1)
                                            END

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 1) Remove records older than user-provided number of days

                                            BEGIN TRY

                                            -- By default, I'd like to keep 2 years of data
                                            SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())

                                            DELETE dbo.DriveSpace
                                            WHERE EntryDate < @AgeOutDate

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 2) Get free space on drives

                                            BEGIN TRY
                                            -- Populate temp table; this system proc gives us space in MB
                                            INSERT INTO #Space (DriveLetter, AvailableMB)
                                            EXEC master.dbo.xp_fixeddrives

                                            -- 3) Loop through drives and get various attributes using OLE automation

                                            WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)

                                            BEGIN

                                            SELECT TOP 1
                                            @PK = PK,
                                            @DriveLetter = DriveLetter,
                                            @AvailableMB = AvailableMB,
                                            @DriveSize = DriveSize
                                            FROM #Space
                                            WHERE Processed = @No

                                            BEGIN TRY
                                            EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Get disk info
                                            SET @DriveLetter = @DriveLetter + ':'
                                            EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
                                            EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY
                                            -- Update table
                                            UPDATE #Space
                                            SET
                                            VolumeName =
                                            CASE LOWER(@DriveLetter)
                                            WHEN 'c:' THEN 'System'
                                            ELSE @VolumeName
                                            END,
                                            -- Convert bytes to megabytes
                                            DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
                                            -- Convert bytes to megabytes
                                            AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
                                            WHERE PK = @PK;

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Destroy oDrive
                                            EXEC master.dbo.sp_OADestroy @oDrive

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            UPDATE #Space
                                            SET Processed = @YES
                                            WHERE PK = @PK

                                            END

                                            BEGIN TRY

                                            -- Destroy FSO
                                            EXEC master.dbo.sp_OADestroy @FSO

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 4) Load results in table

                                            -- Return disk-space info
                                            INSERT dbo.DriveSpace
                                            (
                                            DriveLetter,
                                            VolumeName,
                                            AvailableMB,
                                            TotalDiskSpaceMB,
                                            EntryDate
                                            )

                                            SELECT
                                            DriveLetter DriveLetter,
                                            CASE
                                            WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
                                            ELSE VolumeName
                                            END VolumeName,
                                            AvailableMB AvailableMB,
                                            DriveSize TotalDriveSpaceMB,
                                            @BATCHDATE LoadDate
                                            FROM #Space

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            Cleanup:

                                            -- Ensure xp_cmdshell's off
                                            EXEC sp_configure 'OLE Automation Procedures', @NO;
                                            RECONFIGURE



                                            GO





                                            share|improve this answer


























                                            • You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                                              – Kin
                                              Jun 29 '18 at 15:49











                                            • This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                                              – EOA
                                              Jun 29 '18 at 16:59














                                            0












                                            0








                                            0







                                            USE [db]
                                            GO

                                            /****** Object: StoredProcedure [dbo].[GetDriveSpace] Script Date: 6/29/2018 9:22:12 AM ******/
                                            SET ANSI_NULLS ON
                                            GO

                                            SET QUOTED_IDENTIFIER ON
                                            GO





                                            CREATE PROCEDURE [dbo].[GetDriveSpace]

                                            @DaysToRetain SMALLINT = 90

                                            AS

                                            SET NOCOUNT ON

                                            -- Persisted work table

                                            IF OBJECT_ID('<db>.dbo.DriveSpace') IS NULL
                                            BEGIN
                                            Create TABLE dbo.DriveSpace
                                            (
                                            PK INT IDENTITY CONSTRAINT PK_DriveSpace PRIMARY KEY,
                                            DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            AvailableMB BIGINT NOT NULL,
                                            TotalDriveSpaceMB BIGINT NOT NULL,
                                            [TotalSpaceGB] AS ([TotalDriveSpaceMB]/(1024.00)),
                                            [FreeSpaceGB] AS ([AvailableMB]/(1024.00)),
                                            PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDriveSpaceMB) * 100),
                                            EntryDate DATETIME NOT NULL
                                            ) ON [PRIMARY]
                                            CREATE NONCLUSTERED INDEX NCnd_EntryDate
                                            ON dbo.DriveSpace (EntryDate)
                                            END

                                            -- Vars
                                            DECLARE

                                            @AgeOutDate DATETIME, -- To prune the [DriveSpace] table
                                            @BATCHDATE DATETIME, -- Constant
                                            @DriveSize BIGINT, -- Drive size in bytes
                                            @DriveLetter VARCHAR(2), -- FSO drive letter
                                            @ErrorMessage VARCHAR(500), -- Error message
                                            @FSO INT, -- File System Object
                                            @AvailableMB INT, -- Available space MB
                                            @NO BIT, -- Constant
                                            @oDrive INT, -- FSO drive
                                            @OLE_AUTOMATION_PROCEDURES INT, -- Constant
                                            @PK TINYINT, -- Work-table primary key
                                            @RawFree VARCHAR(20), -- Available space bytes
                                            @RetVal INT, -- Return value
                                            @SQL NVARCHAR(255), -- Dynamic T-SQL
                                            @TwoToTheTwentieth INT, -- Convert bytes to MB
                                            @ValueInUse SQL_VARIANT, -- To test configuration settings
                                            @VolumeName VARCHAR(32), -- FSO volume name
                                            @YES BIT -- Constant

                                            -- Constants
                                            SET @BATCHDATE = GETDATE()
                                            SET @OLE_AUTOMATION_PROCEDURES = 16388 -- SELECT configuration_id FROM master.sys.configurations
                                            SET @NO = 0
                                            SET @TwoToTheTwentieth = POWER(2, 20)
                                            SET @YES = 1

                                            -- Create temp tables for disk space info
                                            IF OBJECT_ID('tempdb.dbo.#Space') IS NOT NULL DROP TABLE #Space
                                            CREATE TABLE #Space
                                            (
                                            PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
                                            DriveLetter VARCHAR(2) NOT NULL,
                                            VolumeName VARCHAR(32) NULL,
                                            DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
                                            AvailableMB BIGINT NOT NULL,
                                            Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
                                            )

                                            -- We need OLE automation enabled to succeed.

                                            BEGIN TRY
                                            -- Enable advanced options
                                            EXEC sp_configure 'show advanced option', '1';
                                            RECONFIGURE;

                                            -- Ensure OLE Automation enabled
                                            EXEC sp_configure 'OLE Automation Procedures', @YES;
                                            RECONFIGURE

                                            -- Test...
                                            SELECT @ValueInUse = Value_In_Use
                                            FROM master.sys.configurations
                                            WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES

                                            IF CONVERT(INT, @ValueInUse) <> @YES
                                            BEGIN
                                            SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''OLE Automation Procedures'' server option, this option is still disabled.' + CHAR(13)
                                            + 'This script depends upon this extended stored procedure''s availability. Please investigate.'
                                            + CHAR(13) + 'NOTE: ''OLE Automation Procedures'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

                                            RAISERROR(@ErrorMessage, 16, 1)
                                            END

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 1) Remove records older than user-provided number of days

                                            BEGIN TRY

                                            -- By default, I'd like to keep 2 years of data
                                            SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())

                                            DELETE dbo.DriveSpace
                                            WHERE EntryDate < @AgeOutDate

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 2) Get free space on drives

                                            BEGIN TRY
                                            -- Populate temp table; this system proc gives us space in MB
                                            INSERT INTO #Space (DriveLetter, AvailableMB)
                                            EXEC master.dbo.xp_fixeddrives

                                            -- 3) Loop through drives and get various attributes using OLE automation

                                            WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)

                                            BEGIN

                                            SELECT TOP 1
                                            @PK = PK,
                                            @DriveLetter = DriveLetter,
                                            @AvailableMB = AvailableMB,
                                            @DriveSize = DriveSize
                                            FROM #Space
                                            WHERE Processed = @No

                                            BEGIN TRY
                                            EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Get disk info
                                            SET @DriveLetter = @DriveLetter + ':'
                                            EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
                                            EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY
                                            -- Update table
                                            UPDATE #Space
                                            SET
                                            VolumeName =
                                            CASE LOWER(@DriveLetter)
                                            WHEN 'c:' THEN 'System'
                                            ELSE @VolumeName
                                            END,
                                            -- Convert bytes to megabytes
                                            DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
                                            -- Convert bytes to megabytes
                                            AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
                                            WHERE PK = @PK;

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Destroy oDrive
                                            EXEC master.dbo.sp_OADestroy @oDrive

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            UPDATE #Space
                                            SET Processed = @YES
                                            WHERE PK = @PK

                                            END

                                            BEGIN TRY

                                            -- Destroy FSO
                                            EXEC master.dbo.sp_OADestroy @FSO

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 4) Load results in table

                                            -- Return disk-space info
                                            INSERT dbo.DriveSpace
                                            (
                                            DriveLetter,
                                            VolumeName,
                                            AvailableMB,
                                            TotalDiskSpaceMB,
                                            EntryDate
                                            )

                                            SELECT
                                            DriveLetter DriveLetter,
                                            CASE
                                            WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
                                            ELSE VolumeName
                                            END VolumeName,
                                            AvailableMB AvailableMB,
                                            DriveSize TotalDriveSpaceMB,
                                            @BATCHDATE LoadDate
                                            FROM #Space

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            Cleanup:

                                            -- Ensure xp_cmdshell's off
                                            EXEC sp_configure 'OLE Automation Procedures', @NO;
                                            RECONFIGURE



                                            GO





                                            share|improve this answer















                                            USE [db]
                                            GO

                                            /****** Object: StoredProcedure [dbo].[GetDriveSpace] Script Date: 6/29/2018 9:22:12 AM ******/
                                            SET ANSI_NULLS ON
                                            GO

                                            SET QUOTED_IDENTIFIER ON
                                            GO





                                            CREATE PROCEDURE [dbo].[GetDriveSpace]

                                            @DaysToRetain SMALLINT = 90

                                            AS

                                            SET NOCOUNT ON

                                            -- Persisted work table

                                            IF OBJECT_ID('<db>.dbo.DriveSpace') IS NULL
                                            BEGIN
                                            Create TABLE dbo.DriveSpace
                                            (
                                            PK INT IDENTITY CONSTRAINT PK_DriveSpace PRIMARY KEY,
                                            DriveLetter CHAR(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            VolumeName VARCHAR(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                                            AvailableMB BIGINT NOT NULL,
                                            TotalDriveSpaceMB BIGINT NOT NULL,
                                            [TotalSpaceGB] AS ([TotalDriveSpaceMB]/(1024.00)),
                                            [FreeSpaceGB] AS ([AvailableMB]/(1024.00)),
                                            PercentFree AS (CONVERT(DECIMAL(15, 4), AvailableMB) / CONVERT(DECIMAL(15, 4), TotalDriveSpaceMB) * 100),
                                            EntryDate DATETIME NOT NULL
                                            ) ON [PRIMARY]
                                            CREATE NONCLUSTERED INDEX NCnd_EntryDate
                                            ON dbo.DriveSpace (EntryDate)
                                            END

                                            -- Vars
                                            DECLARE

                                            @AgeOutDate DATETIME, -- To prune the [DriveSpace] table
                                            @BATCHDATE DATETIME, -- Constant
                                            @DriveSize BIGINT, -- Drive size in bytes
                                            @DriveLetter VARCHAR(2), -- FSO drive letter
                                            @ErrorMessage VARCHAR(500), -- Error message
                                            @FSO INT, -- File System Object
                                            @AvailableMB INT, -- Available space MB
                                            @NO BIT, -- Constant
                                            @oDrive INT, -- FSO drive
                                            @OLE_AUTOMATION_PROCEDURES INT, -- Constant
                                            @PK TINYINT, -- Work-table primary key
                                            @RawFree VARCHAR(20), -- Available space bytes
                                            @RetVal INT, -- Return value
                                            @SQL NVARCHAR(255), -- Dynamic T-SQL
                                            @TwoToTheTwentieth INT, -- Convert bytes to MB
                                            @ValueInUse SQL_VARIANT, -- To test configuration settings
                                            @VolumeName VARCHAR(32), -- FSO volume name
                                            @YES BIT -- Constant

                                            -- Constants
                                            SET @BATCHDATE = GETDATE()
                                            SET @OLE_AUTOMATION_PROCEDURES = 16388 -- SELECT configuration_id FROM master.sys.configurations
                                            SET @NO = 0
                                            SET @TwoToTheTwentieth = POWER(2, 20)
                                            SET @YES = 1

                                            -- Create temp tables for disk space info
                                            IF OBJECT_ID('tempdb.dbo.#Space') IS NOT NULL DROP TABLE #Space
                                            CREATE TABLE #Space
                                            (
                                            PK TINYINT IDENTITY CONSTRAINT Space_PK PRIMARY KEY,
                                            DriveLetter VARCHAR(2) NOT NULL,
                                            VolumeName VARCHAR(32) NULL,
                                            DriveSize BIGINT CONSTRAINT DF_Space_DriveSize DEFAULT(0),
                                            AvailableMB BIGINT NOT NULL,
                                            Processed BIT CONSTRAINT DF_Space_Processed DEFAULT (0)
                                            )

                                            -- We need OLE automation enabled to succeed.

                                            BEGIN TRY
                                            -- Enable advanced options
                                            EXEC sp_configure 'show advanced option', '1';
                                            RECONFIGURE;

                                            -- Ensure OLE Automation enabled
                                            EXEC sp_configure 'OLE Automation Procedures', @YES;
                                            RECONFIGURE

                                            -- Test...
                                            SELECT @ValueInUse = Value_In_Use
                                            FROM master.sys.configurations
                                            WHERE configuration_id = @OLE_AUTOMATION_PROCEDURES

                                            IF CONVERT(INT, @ValueInUse) <> @YES
                                            BEGIN
                                            SET @ErrorMessage = CHAR(13) + 'Although we attempted to enable the ''OLE Automation Procedures'' server option, this option is still disabled.' + CHAR(13)
                                            + 'This script depends upon this extended stored procedure''s availability. Please investigate.'
                                            + CHAR(13) + 'NOTE: ''OLE Automation Procedures'' execution requires ''CONTROL SERVER'' permission (See SQL Server Books Online for more information).'

                                            RAISERROR(@ErrorMessage, 16, 1)
                                            END

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 1) Remove records older than user-provided number of days

                                            BEGIN TRY

                                            -- By default, I'd like to keep 2 years of data
                                            SET @AgeOutDate = DATEADD(dd, (@DaysToRetain * -1), GETDATE())

                                            DELETE dbo.DriveSpace
                                            WHERE EntryDate < @AgeOutDate

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 2) Get free space on drives

                                            BEGIN TRY
                                            -- Populate temp table; this system proc gives us space in MB
                                            INSERT INTO #Space (DriveLetter, AvailableMB)
                                            EXEC master.dbo.xp_fixeddrives

                                            -- 3) Loop through drives and get various attributes using OLE automation

                                            WHILE EXISTS (SELECT TOP 1 1 FROM #Space WHERE Processed = @No)

                                            BEGIN

                                            SELECT TOP 1
                                            @PK = PK,
                                            @DriveLetter = DriveLetter,
                                            @AvailableMB = AvailableMB,
                                            @DriveSize = DriveSize
                                            FROM #Space
                                            WHERE Processed = @No

                                            BEGIN TRY
                                            EXEC master.dbo.sp_OACreate 'Scripting.FileSystemObject', @FSO OUT
                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Get disk info
                                            SET @DriveLetter = @DriveLetter + ':'
                                            EXEC master.dbo.sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @DriveLetter
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'TotalSize', @DriveSize OUT
                                            EXEC master.dbo.sp_OAMethod @oDrive, 'AvailableSpace', @RawFree OUT
                                            EXEC master.dbo.sp_OAGetProperty @oDrive, 'VolumeName', @VolumeName OUT

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY
                                            -- Update table
                                            UPDATE #Space
                                            SET
                                            VolumeName =
                                            CASE LOWER(@DriveLetter)
                                            WHEN 'c:' THEN 'System'
                                            ELSE @VolumeName
                                            END,
                                            -- Convert bytes to megabytes
                                            DriveSize = CONVERT(BIGINT, @DriveSize) / @TwoToTheTwentieth,
                                            -- Convert bytes to megabytes
                                            AvailableMB = CONVERT(BIGINT, @RawFree) / @TwoToTheTwentieth
                                            WHERE PK = @PK;

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            BEGIN TRY

                                            -- Destroy oDrive
                                            EXEC master.dbo.sp_OADestroy @oDrive

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            UPDATE #Space
                                            SET Processed = @YES
                                            WHERE PK = @PK

                                            END

                                            BEGIN TRY

                                            -- Destroy FSO
                                            EXEC master.dbo.sp_OADestroy @FSO

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            -- 4) Load results in table

                                            -- Return disk-space info
                                            INSERT dbo.DriveSpace
                                            (
                                            DriveLetter,
                                            VolumeName,
                                            AvailableMB,
                                            TotalDiskSpaceMB,
                                            EntryDate
                                            )

                                            SELECT
                                            DriveLetter DriveLetter,
                                            CASE
                                            WHEN LEN(RTRIM(LTRIM(VolumeName))) = 0 THEN 'Unknown'
                                            ELSE VolumeName
                                            END VolumeName,
                                            AvailableMB AvailableMB,
                                            DriveSize TotalDriveSpaceMB,
                                            @BATCHDATE LoadDate
                                            FROM #Space

                                            END TRY

                                            BEGIN CATCH
                                            SELECT
                                            ERROR_NUMBER() AS ErrorNumber
                                            ,ERROR_MESSAGE() AS ErrorMessage
                                            ,ERROR_SEVERITY() AS ErrorSeverity
                                            ,ERROR_STATE() AS ErrorState
                                            ,ERROR_LINE() AS ErrorLine
                                            ,ERROR_PROCEDURE() AS ErrorProcedure

                                            GOTO Cleanup
                                            END CATCH

                                            Cleanup:

                                            -- Ensure xp_cmdshell's off
                                            EXEC sp_configure 'OLE Automation Procedures', @NO;
                                            RECONFIGURE



                                            GO






                                            share|improve this answer














                                            share|improve this answer



                                            share|improve this answer








                                            edited Jun 29 '18 at 15:48









                                            Kin

                                            53.2k480188




                                            53.2k480188










                                            answered Jun 29 '18 at 14:37









                                            EOAEOA

                                            412




                                            412













                                            • You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                                              – Kin
                                              Jun 29 '18 at 15:49











                                            • This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                                              – EOA
                                              Jun 29 '18 at 16:59



















                                            • You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                                              – Kin
                                              Jun 29 '18 at 15:49











                                            • This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                                              – EOA
                                              Jun 29 '18 at 16:59

















                                            You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                                            – Kin
                                            Jun 29 '18 at 15:49





                                            You should explain what your code does and provide any references - if this code is from any site. Also mention what sp_configure changes are needed as a caution.

                                            – Kin
                                            Jun 29 '18 at 15:49













                                            This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                                            – EOA
                                            Jun 29 '18 at 16:59





                                            This code provide available disk space on sql server and log the information into a table. This script is by Mark Holahan.heydba.net/2011/04/… but you have to edit it to fit your environment.

                                            – EOA
                                            Jun 29 '18 at 16:59











                                            0














                                            Something I put together. You NEED POWERSHELL on your SQL Server to run this. Not sure of the version. This will take your drive sizes and report back the file sizes and free space as well as free drive space.



                                            It's not 100% original and parts of it I found elsewhere on the internet and put the whole thing together. It was a PITA to get it to fit into the right margins so you might have to play with the parentheses



                                            --drop table ##DB_FILE_INFO
                                            --alter procedure super_drive_info3

                                            --as

                                            if exists ( select * from tempdb.dbo.sysobjects o
                                            where o.xtype in ('U') and o.name in ('##DB_FILE_INFO' ))
                                            drop table ##DB_FILE_INFO

                                            if exists ( select * from tempdb.dbo.sysobjects o
                                            where o.xtype in ('U') and o.name in ('##output'))
                                            drop table ##output

                                            create table ##DB_FILE_INFO (
                                            [server_Name] varchar(255) not null,
                                            [database_name] varchar(255) not null,
                                            [File_ID] int not null,
                                            [File_Type] int not null ,
                                            [Drive] varchar(255) not null ,
                                            [Logical_Name] varchar(255) not null ,
                                            [Physical_Name] varchar(255) not null ,
                                            [FILE_SIZE_MB] int not null ,
                                            [SPace_USED_MB] int not null ,
                                            [Free_space] int not null ,
                                            [Max_SIZE] int not null ,
                                            [Percent_Log_growth_enabled] int not null ,
                                            [growth_rate] int not null ,
                                            [current_date] datetime not null
                                            )
                                            --go
                                            declare @sql nvarchar(4000)
                                            set @sql =
                                            'use ['+'?'+']
                                            --if db_name() <> N''?'' goto Error_Exit
                                            insert into ##DB_FILE_INFO
                                            (
                                            [server_Name],
                                            [database_name],
                                            [File_ID],
                                            [File_Type],
                                            [Drive],
                                            [Logical_Name],
                                            [Physical_Name],
                                            [FILE_SIZE_MB],
                                            [SPace_USED_MB],
                                            [Free_space],
                                            [Max_SIZE],
                                            [Percent_Log_growth_enabled],
                                            [growth_rate],
                                            [current_date]
                                            )
                                            SELECT
                                            @@servername as [Server_Name],
                                            db_name() as database_name,
                                            [file_id] AS [File_ID],
                                            [type] AS [File_Type],
                                            substring([physical_name],1,1) AS [Drive],
                                            [name] AS [Logical_Name],
                                            [physical_name] AS [Physical_Name],
                                            CAST([size] as DECIMAL(38,0))/128. AS [File Size MB],
                                            CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS
                                            [Space_Used_MB],
                                            (CAST([size] AS DECIMAL(38,0))/128) -
                                            (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free
                                            Space],
                                            [max_size] AS [Max_Size],
                                            [is_percent_growth] AS [Percent_Growth_Enabled],
                                            [growth] AS [Growth Rate],
                                            getdate() AS [Current_Date]
                                            FROM sys.database_files'
                                            exec sp_msforeachdb @sql
                                            declare @svrName varchar(255)
                                            declare @sql2 varchar(400)
                                            --by default it will take the current server name, we can the set the server
                                            name as well
                                            set @svrName = @@SERVERNAME
                                            set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' +
                                            QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' |
                                            select name,capacity,freespace |

                                            foreach{$_.name+''|
                                            ''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
                                            --creating a temporary table
                                            CREATE TABLE ##output
                                            (line varchar(255))
                                            --inserting in to temporary table
                                            insert ##output
                                            EXEC xp_cmdshell @sql2;

                                            with Output2
                                            --(drivename, capacity(gb),freespace(gb), always_on_status)
                                            as
                                            (
                                            select @@servername as server_name,
                                            rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename
                                            ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
                                            (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as
                                            'capacityGB'
                                            ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
                                            (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as
                                            'freespaceGB'
                                            ,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN
                                            (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS
                                            ALWAYS_ON_STATUS
                                            --into #output2
                                            from ##output
                                            where line like '[A-Z][:]%'
                                            --order by drivename
                                            ),
                                            DB_FILE_INFO2 as
                                            (
                                            select server_Name,
                                            database_name,
                                            File_ID,
                                            File_Type,
                                            Drive,
                                            Logical_Name,
                                            Physical_Name,
                                            FILE_SIZE_MB,
                                            SPace_USED_MB,
                                            Free_space,
                                            Max_SIZE,
                                            Percent_Log_growth_enabled,
                                            growth_rate
                                            --current_date
                                            from ##DB_FILE_INFO
                                            --inner join #output b on a.drive = b.drivename and a.server_Name =
                                            b.server_name
                                            )
                                            select
                                            getdate() as Today_Date,
                                            a.server_Name,
                                            a.database_name,
                                            a.Drive,
                                            a.Logical_Name,
                                            a.Physical_Name,
                                            a.FILE_SIZE_MB,
                                            a.Space_Used_MB,
                                            --sum(a.SPace_USED_MB) as hg,
                                            a.Free_space as Free_Space_in_File,
                                            --Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),
                                            b.capacitygb as Total_Drive_capacity,
                                            b.freespacegb as Total_Free_Space,
                                            c.total_log_size_mb,
                                            c.active_log_size_mb
                                            --,Percentage_free_space = ((cast(Free_space as decimal))/(cast(FILE_SIZE_MB
                                            as decimal)) * 100)

                                            from DB_FILE_INFO2 a
                                            inner join output2 b on a.server_Name = b.server_name and a.Drive =
                                            b.drivename
                                            cross apply sys.dm_db_log_stats (db_id(a.database_name)) c
                                            order by a.Drive, a.database_name

                                            --drop table ##DB_FILE_INFO
                                            --drop table #output





                                            share|improve this answer




























                                              0














                                              Something I put together. You NEED POWERSHELL on your SQL Server to run this. Not sure of the version. This will take your drive sizes and report back the file sizes and free space as well as free drive space.



                                              It's not 100% original and parts of it I found elsewhere on the internet and put the whole thing together. It was a PITA to get it to fit into the right margins so you might have to play with the parentheses



                                              --drop table ##DB_FILE_INFO
                                              --alter procedure super_drive_info3

                                              --as

                                              if exists ( select * from tempdb.dbo.sysobjects o
                                              where o.xtype in ('U') and o.name in ('##DB_FILE_INFO' ))
                                              drop table ##DB_FILE_INFO

                                              if exists ( select * from tempdb.dbo.sysobjects o
                                              where o.xtype in ('U') and o.name in ('##output'))
                                              drop table ##output

                                              create table ##DB_FILE_INFO (
                                              [server_Name] varchar(255) not null,
                                              [database_name] varchar(255) not null,
                                              [File_ID] int not null,
                                              [File_Type] int not null ,
                                              [Drive] varchar(255) not null ,
                                              [Logical_Name] varchar(255) not null ,
                                              [Physical_Name] varchar(255) not null ,
                                              [FILE_SIZE_MB] int not null ,
                                              [SPace_USED_MB] int not null ,
                                              [Free_space] int not null ,
                                              [Max_SIZE] int not null ,
                                              [Percent_Log_growth_enabled] int not null ,
                                              [growth_rate] int not null ,
                                              [current_date] datetime not null
                                              )
                                              --go
                                              declare @sql nvarchar(4000)
                                              set @sql =
                                              'use ['+'?'+']
                                              --if db_name() <> N''?'' goto Error_Exit
                                              insert into ##DB_FILE_INFO
                                              (
                                              [server_Name],
                                              [database_name],
                                              [File_ID],
                                              [File_Type],
                                              [Drive],
                                              [Logical_Name],
                                              [Physical_Name],
                                              [FILE_SIZE_MB],
                                              [SPace_USED_MB],
                                              [Free_space],
                                              [Max_SIZE],
                                              [Percent_Log_growth_enabled],
                                              [growth_rate],
                                              [current_date]
                                              )
                                              SELECT
                                              @@servername as [Server_Name],
                                              db_name() as database_name,
                                              [file_id] AS [File_ID],
                                              [type] AS [File_Type],
                                              substring([physical_name],1,1) AS [Drive],
                                              [name] AS [Logical_Name],
                                              [physical_name] AS [Physical_Name],
                                              CAST([size] as DECIMAL(38,0))/128. AS [File Size MB],
                                              CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS
                                              [Space_Used_MB],
                                              (CAST([size] AS DECIMAL(38,0))/128) -
                                              (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free
                                              Space],
                                              [max_size] AS [Max_Size],
                                              [is_percent_growth] AS [Percent_Growth_Enabled],
                                              [growth] AS [Growth Rate],
                                              getdate() AS [Current_Date]
                                              FROM sys.database_files'
                                              exec sp_msforeachdb @sql
                                              declare @svrName varchar(255)
                                              declare @sql2 varchar(400)
                                              --by default it will take the current server name, we can the set the server
                                              name as well
                                              set @svrName = @@SERVERNAME
                                              set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' +
                                              QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' |
                                              select name,capacity,freespace |

                                              foreach{$_.name+''|
                                              ''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
                                              --creating a temporary table
                                              CREATE TABLE ##output
                                              (line varchar(255))
                                              --inserting in to temporary table
                                              insert ##output
                                              EXEC xp_cmdshell @sql2;

                                              with Output2
                                              --(drivename, capacity(gb),freespace(gb), always_on_status)
                                              as
                                              (
                                              select @@servername as server_name,
                                              rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename
                                              ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
                                              (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as
                                              'capacityGB'
                                              ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
                                              (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as
                                              'freespaceGB'
                                              ,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN
                                              (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS
                                              ALWAYS_ON_STATUS
                                              --into #output2
                                              from ##output
                                              where line like '[A-Z][:]%'
                                              --order by drivename
                                              ),
                                              DB_FILE_INFO2 as
                                              (
                                              select server_Name,
                                              database_name,
                                              File_ID,
                                              File_Type,
                                              Drive,
                                              Logical_Name,
                                              Physical_Name,
                                              FILE_SIZE_MB,
                                              SPace_USED_MB,
                                              Free_space,
                                              Max_SIZE,
                                              Percent_Log_growth_enabled,
                                              growth_rate
                                              --current_date
                                              from ##DB_FILE_INFO
                                              --inner join #output b on a.drive = b.drivename and a.server_Name =
                                              b.server_name
                                              )
                                              select
                                              getdate() as Today_Date,
                                              a.server_Name,
                                              a.database_name,
                                              a.Drive,
                                              a.Logical_Name,
                                              a.Physical_Name,
                                              a.FILE_SIZE_MB,
                                              a.Space_Used_MB,
                                              --sum(a.SPace_USED_MB) as hg,
                                              a.Free_space as Free_Space_in_File,
                                              --Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),
                                              b.capacitygb as Total_Drive_capacity,
                                              b.freespacegb as Total_Free_Space,
                                              c.total_log_size_mb,
                                              c.active_log_size_mb
                                              --,Percentage_free_space = ((cast(Free_space as decimal))/(cast(FILE_SIZE_MB
                                              as decimal)) * 100)

                                              from DB_FILE_INFO2 a
                                              inner join output2 b on a.server_Name = b.server_name and a.Drive =
                                              b.drivename
                                              cross apply sys.dm_db_log_stats (db_id(a.database_name)) c
                                              order by a.Drive, a.database_name

                                              --drop table ##DB_FILE_INFO
                                              --drop table #output





                                              share|improve this answer


























                                                0












                                                0








                                                0







                                                Something I put together. You NEED POWERSHELL on your SQL Server to run this. Not sure of the version. This will take your drive sizes and report back the file sizes and free space as well as free drive space.



                                                It's not 100% original and parts of it I found elsewhere on the internet and put the whole thing together. It was a PITA to get it to fit into the right margins so you might have to play with the parentheses



                                                --drop table ##DB_FILE_INFO
                                                --alter procedure super_drive_info3

                                                --as

                                                if exists ( select * from tempdb.dbo.sysobjects o
                                                where o.xtype in ('U') and o.name in ('##DB_FILE_INFO' ))
                                                drop table ##DB_FILE_INFO

                                                if exists ( select * from tempdb.dbo.sysobjects o
                                                where o.xtype in ('U') and o.name in ('##output'))
                                                drop table ##output

                                                create table ##DB_FILE_INFO (
                                                [server_Name] varchar(255) not null,
                                                [database_name] varchar(255) not null,
                                                [File_ID] int not null,
                                                [File_Type] int not null ,
                                                [Drive] varchar(255) not null ,
                                                [Logical_Name] varchar(255) not null ,
                                                [Physical_Name] varchar(255) not null ,
                                                [FILE_SIZE_MB] int not null ,
                                                [SPace_USED_MB] int not null ,
                                                [Free_space] int not null ,
                                                [Max_SIZE] int not null ,
                                                [Percent_Log_growth_enabled] int not null ,
                                                [growth_rate] int not null ,
                                                [current_date] datetime not null
                                                )
                                                --go
                                                declare @sql nvarchar(4000)
                                                set @sql =
                                                'use ['+'?'+']
                                                --if db_name() <> N''?'' goto Error_Exit
                                                insert into ##DB_FILE_INFO
                                                (
                                                [server_Name],
                                                [database_name],
                                                [File_ID],
                                                [File_Type],
                                                [Drive],
                                                [Logical_Name],
                                                [Physical_Name],
                                                [FILE_SIZE_MB],
                                                [SPace_USED_MB],
                                                [Free_space],
                                                [Max_SIZE],
                                                [Percent_Log_growth_enabled],
                                                [growth_rate],
                                                [current_date]
                                                )
                                                SELECT
                                                @@servername as [Server_Name],
                                                db_name() as database_name,
                                                [file_id] AS [File_ID],
                                                [type] AS [File_Type],
                                                substring([physical_name],1,1) AS [Drive],
                                                [name] AS [Logical_Name],
                                                [physical_name] AS [Physical_Name],
                                                CAST([size] as DECIMAL(38,0))/128. AS [File Size MB],
                                                CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS
                                                [Space_Used_MB],
                                                (CAST([size] AS DECIMAL(38,0))/128) -
                                                (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free
                                                Space],
                                                [max_size] AS [Max_Size],
                                                [is_percent_growth] AS [Percent_Growth_Enabled],
                                                [growth] AS [Growth Rate],
                                                getdate() AS [Current_Date]
                                                FROM sys.database_files'
                                                exec sp_msforeachdb @sql
                                                declare @svrName varchar(255)
                                                declare @sql2 varchar(400)
                                                --by default it will take the current server name, we can the set the server
                                                name as well
                                                set @svrName = @@SERVERNAME
                                                set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' +
                                                QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' |
                                                select name,capacity,freespace |

                                                foreach{$_.name+''|
                                                ''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
                                                --creating a temporary table
                                                CREATE TABLE ##output
                                                (line varchar(255))
                                                --inserting in to temporary table
                                                insert ##output
                                                EXEC xp_cmdshell @sql2;

                                                with Output2
                                                --(drivename, capacity(gb),freespace(gb), always_on_status)
                                                as
                                                (
                                                select @@servername as server_name,
                                                rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename
                                                ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
                                                (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as
                                                'capacityGB'
                                                ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
                                                (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as
                                                'freespaceGB'
                                                ,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN
                                                (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS
                                                ALWAYS_ON_STATUS
                                                --into #output2
                                                from ##output
                                                where line like '[A-Z][:]%'
                                                --order by drivename
                                                ),
                                                DB_FILE_INFO2 as
                                                (
                                                select server_Name,
                                                database_name,
                                                File_ID,
                                                File_Type,
                                                Drive,
                                                Logical_Name,
                                                Physical_Name,
                                                FILE_SIZE_MB,
                                                SPace_USED_MB,
                                                Free_space,
                                                Max_SIZE,
                                                Percent_Log_growth_enabled,
                                                growth_rate
                                                --current_date
                                                from ##DB_FILE_INFO
                                                --inner join #output b on a.drive = b.drivename and a.server_Name =
                                                b.server_name
                                                )
                                                select
                                                getdate() as Today_Date,
                                                a.server_Name,
                                                a.database_name,
                                                a.Drive,
                                                a.Logical_Name,
                                                a.Physical_Name,
                                                a.FILE_SIZE_MB,
                                                a.Space_Used_MB,
                                                --sum(a.SPace_USED_MB) as hg,
                                                a.Free_space as Free_Space_in_File,
                                                --Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),
                                                b.capacitygb as Total_Drive_capacity,
                                                b.freespacegb as Total_Free_Space,
                                                c.total_log_size_mb,
                                                c.active_log_size_mb
                                                --,Percentage_free_space = ((cast(Free_space as decimal))/(cast(FILE_SIZE_MB
                                                as decimal)) * 100)

                                                from DB_FILE_INFO2 a
                                                inner join output2 b on a.server_Name = b.server_name and a.Drive =
                                                b.drivename
                                                cross apply sys.dm_db_log_stats (db_id(a.database_name)) c
                                                order by a.Drive, a.database_name

                                                --drop table ##DB_FILE_INFO
                                                --drop table #output





                                                share|improve this answer













                                                Something I put together. You NEED POWERSHELL on your SQL Server to run this. Not sure of the version. This will take your drive sizes and report back the file sizes and free space as well as free drive space.



                                                It's not 100% original and parts of it I found elsewhere on the internet and put the whole thing together. It was a PITA to get it to fit into the right margins so you might have to play with the parentheses



                                                --drop table ##DB_FILE_INFO
                                                --alter procedure super_drive_info3

                                                --as

                                                if exists ( select * from tempdb.dbo.sysobjects o
                                                where o.xtype in ('U') and o.name in ('##DB_FILE_INFO' ))
                                                drop table ##DB_FILE_INFO

                                                if exists ( select * from tempdb.dbo.sysobjects o
                                                where o.xtype in ('U') and o.name in ('##output'))
                                                drop table ##output

                                                create table ##DB_FILE_INFO (
                                                [server_Name] varchar(255) not null,
                                                [database_name] varchar(255) not null,
                                                [File_ID] int not null,
                                                [File_Type] int not null ,
                                                [Drive] varchar(255) not null ,
                                                [Logical_Name] varchar(255) not null ,
                                                [Physical_Name] varchar(255) not null ,
                                                [FILE_SIZE_MB] int not null ,
                                                [SPace_USED_MB] int not null ,
                                                [Free_space] int not null ,
                                                [Max_SIZE] int not null ,
                                                [Percent_Log_growth_enabled] int not null ,
                                                [growth_rate] int not null ,
                                                [current_date] datetime not null
                                                )
                                                --go
                                                declare @sql nvarchar(4000)
                                                set @sql =
                                                'use ['+'?'+']
                                                --if db_name() <> N''?'' goto Error_Exit
                                                insert into ##DB_FILE_INFO
                                                (
                                                [server_Name],
                                                [database_name],
                                                [File_ID],
                                                [File_Type],
                                                [Drive],
                                                [Logical_Name],
                                                [Physical_Name],
                                                [FILE_SIZE_MB],
                                                [SPace_USED_MB],
                                                [Free_space],
                                                [Max_SIZE],
                                                [Percent_Log_growth_enabled],
                                                [growth_rate],
                                                [current_date]
                                                )
                                                SELECT
                                                @@servername as [Server_Name],
                                                db_name() as database_name,
                                                [file_id] AS [File_ID],
                                                [type] AS [File_Type],
                                                substring([physical_name],1,1) AS [Drive],
                                                [name] AS [Logical_Name],
                                                [physical_name] AS [Physical_Name],
                                                CAST([size] as DECIMAL(38,0))/128. AS [File Size MB],
                                                CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128. AS
                                                [Space_Used_MB],
                                                (CAST([size] AS DECIMAL(38,0))/128) -
                                                (CAST(FILEPROPERTY([name],''SpaceUsed'') AS DECIMAL(38,0))/128.) AS [Free
                                                Space],
                                                [max_size] AS [Max_Size],
                                                [is_percent_growth] AS [Percent_Growth_Enabled],
                                                [growth] AS [Growth Rate],
                                                getdate() AS [Current_Date]
                                                FROM sys.database_files'
                                                exec sp_msforeachdb @sql
                                                declare @svrName varchar(255)
                                                declare @sql2 varchar(400)
                                                --by default it will take the current server name, we can the set the server
                                                name as well
                                                set @svrName = @@SERVERNAME
                                                set @sql2 = 'powershell.exe -c "Get-WmiObject -ComputerName ' +
                                                QUOTENAME(@svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' |
                                                select name,capacity,freespace |

                                                foreach{$_.name+''|
                                                ''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'
                                                --creating a temporary table
                                                CREATE TABLE ##output
                                                (line varchar(255))
                                                --inserting in to temporary table
                                                insert ##output
                                                EXEC xp_cmdshell @sql2;

                                                with Output2
                                                --(drivename, capacity(gb),freespace(gb), always_on_status)
                                                as
                                                (
                                                select @@servername as server_name,
                                                rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(':',line) -1))) as drivename
                                                ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
                                                (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as float)/1024,2) as
                                                'capacityGB'
                                                ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
                                                (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as float) /1024 ,2)as
                                                'freespaceGB'
                                                ,CASE WHEN (SERVERPROPERTY ('IsHadrEnabled')=1) THEN 'YES' WHEN
                                                (SERVERPROPERTY ('IsHadrEnabled')=0) THEN 'NO'ELSE 'NOT AVAILABLE' END AS
                                                ALWAYS_ON_STATUS
                                                --into #output2
                                                from ##output
                                                where line like '[A-Z][:]%'
                                                --order by drivename
                                                ),
                                                DB_FILE_INFO2 as
                                                (
                                                select server_Name,
                                                database_name,
                                                File_ID,
                                                File_Type,
                                                Drive,
                                                Logical_Name,
                                                Physical_Name,
                                                FILE_SIZE_MB,
                                                SPace_USED_MB,
                                                Free_space,
                                                Max_SIZE,
                                                Percent_Log_growth_enabled,
                                                growth_rate
                                                --current_date
                                                from ##DB_FILE_INFO
                                                --inner join #output b on a.drive = b.drivename and a.server_Name =
                                                b.server_name
                                                )
                                                select
                                                getdate() as Today_Date,
                                                a.server_Name,
                                                a.database_name,
                                                a.Drive,
                                                a.Logical_Name,
                                                a.Physical_Name,
                                                a.FILE_SIZE_MB,
                                                a.Space_Used_MB,
                                                --sum(a.SPace_USED_MB) as hg,
                                                a.Free_space as Free_Space_in_File,
                                                --Percentage_file_free = (a.Space_Used_MB/a.FILE_SIZE_MB),
                                                b.capacitygb as Total_Drive_capacity,
                                                b.freespacegb as Total_Free_Space,
                                                c.total_log_size_mb,
                                                c.active_log_size_mb
                                                --,Percentage_free_space = ((cast(Free_space as decimal))/(cast(FILE_SIZE_MB
                                                as decimal)) * 100)

                                                from DB_FILE_INFO2 a
                                                inner join output2 b on a.server_Name = b.server_name and a.Drive =
                                                b.drivename
                                                cross apply sys.dm_db_log_stats (db_id(a.database_name)) c
                                                order by a.Drive, a.database_name

                                                --drop table ##DB_FILE_INFO
                                                --drop table #output






                                                share|improve this answer












                                                share|improve this answer



                                                share|improve this answer










                                                answered Jun 29 '18 at 16:32









                                                AlenAlen

                                                38816




                                                38816























                                                    0














                                                    How about asking M$ for some really simple view that you can "select * from"?



                                                    Instead everyone is doing some extra functins, extra selects, extra tables and so on...



                                                    If M$ would build cars, you would have to bring your own wheels, because M$ isn't able to see, that they would be very useful for the users....






                                                    share|improve this answer








                                                    New contributor




                                                    Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                    Check out our Code of Conduct.

























                                                      0














                                                      How about asking M$ for some really simple view that you can "select * from"?



                                                      Instead everyone is doing some extra functins, extra selects, extra tables and so on...



                                                      If M$ would build cars, you would have to bring your own wheels, because M$ isn't able to see, that they would be very useful for the users....






                                                      share|improve this answer








                                                      New contributor




                                                      Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                      Check out our Code of Conduct.























                                                        0












                                                        0








                                                        0







                                                        How about asking M$ for some really simple view that you can "select * from"?



                                                        Instead everyone is doing some extra functins, extra selects, extra tables and so on...



                                                        If M$ would build cars, you would have to bring your own wheels, because M$ isn't able to see, that they would be very useful for the users....






                                                        share|improve this answer








                                                        New contributor




                                                        Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                        Check out our Code of Conduct.










                                                        How about asking M$ for some really simple view that you can "select * from"?



                                                        Instead everyone is doing some extra functins, extra selects, extra tables and so on...



                                                        If M$ would build cars, you would have to bring your own wheels, because M$ isn't able to see, that they would be very useful for the users....







                                                        share|improve this answer








                                                        New contributor




                                                        Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                        Check out our Code of Conduct.









                                                        share|improve this answer



                                                        share|improve this answer






                                                        New contributor




                                                        Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                        Check out our Code of Conduct.









                                                        answered 15 mins ago









                                                        MichaelMichael

                                                        1




                                                        1




                                                        New contributor




                                                        Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                        Check out our Code of Conduct.





                                                        New contributor





                                                        Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                        Check out our Code of Conduct.






                                                        Michael is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                                                        Check out our Code of Conduct.






























                                                            draft saved

                                                            draft discarded




















































                                                            Thanks for contributing an answer to Database Administrators Stack Exchange!


                                                            • Please be sure to answer the question. Provide details and share your research!

                                                            But avoid



                                                            • Asking for help, clarification, or responding to other answers.

                                                            • Making statements based on opinion; back them up with references or personal experience.


                                                            To learn more, see our tips on writing great answers.




                                                            draft saved


                                                            draft discarded














                                                            StackExchange.ready(
                                                            function () {
                                                            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f29543%2fquery-to-report-disk-space-allocation-and-used-space%23new-answer', 'question_page');
                                                            }
                                                            );

                                                            Post as a guest















                                                            Required, but never shown





















































                                                            Required, but never shown














                                                            Required, but never shown












                                                            Required, but never shown







                                                            Required, but never shown

































                                                            Required, but never shown














                                                            Required, but never shown












                                                            Required, but never shown







                                                            Required, but never shown







                                                            Popular posts from this blog

                                                            الفوسفات في المغرب

                                                            Four equal circles intersect: What is the area of the small shaded portion and its height

                                                            بطل الاتحاد السوفيتي