Find free space in database files in SQL Server


 

—– Find free space (file size) for a particular Database—–

USE Northwind

 SELECT

     a.FILEID,

     CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [FILESIZEINMB] ,

     CONVERT(decimal(12,2),ROUND(fileproperty(a.name,’SpaceUsed’)/128.000,2)) as [SPACEUSEDINMB],

     CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,’SpaceUsed’))/128.000,2)) as [FREESPACEINMB],

     a.name as [DATABASENAME],

     a.FILENAME as [FILENAME]

 FROM

     dbo.sysfiles a

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

EXEC sp_spaceused
SELECT fileid, name, filename, size, growth, status, maxsize
FROM dbo.sysfiles WHERE (status & 0x40) <>0
DBCC sqlperf(logspace)
DBCC showfilestats

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

/***************************************************************************

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FileDetails]')
      and OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE dbo.FileDetails (    
FileId int
, FileGroupId int
, TotalExtents int
, UsedExtents int
, Name nvarchar( 128 )
, FileName nvarchar( 500 )
, TotalSize AS ( ( TotalExtents * 64.0 ) / 1024 )
, UsedSize AS ( ( UsedExtents * 64.0 ) / 1024 )
)   
END
----------------------------------------------------------------------------------------------------

 

exec master..sp_MSforeachdb 'USE ?
INSERT INTO <<mydb>>.dbo.FileDetails (FileId, FileGroupId, TotalExtents, UsedExtents, Name, Filename)
EXEC(''DBCC showfilestats'')'
------------------------------------------------------------------------------------------------------ SELECT * FROM <<mydb>>.dbo.FileDetails
***************************************************************************************************/

USE master 
GO 
 
sp_configure ‘show advanced options’, 1; 
GO 
RECONFIGURE; 
GO 
 
EXEC sp_configure ‘Ole Automation Procedures’; 
GO 
 
sp_configure ‘Ole Automation Procedures’, 1; 
GO 
RECONFIGURE; 
GO 
 
 
sp_configure ‘show advanced options’, 0; 
GO 
RECONFIGURE; 
GO 
——————————————————– 
— create work tables 
——————————————————– 
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = ‘tb_Databases’) 
        BEGIN 
                DROP TABLE DB_UTILS.dbo.tb_Databases 
                PRINT ‘DROPPED tb_Databases From DB_UTILS’ 
        END 
CREATE TABLE    DB_UTILS.dbo.tb_Databases 
        ( 
        row_id                  INT     IDENTITY(1,1), 
        dbname                  SYSNAME, 
        db_size                 INT,                            — Size of database, in kilobytes. 
        remarks                 VARCHAR(254) 
        ) 
 
———————————————————- 
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = ‘tb_DBShowFileStats’) 
        BEGIN 
                DROP TABLE DB_UTILS.dbo.tb_DBShowFileStats 
                PRINT ‘DROPPED tb_DBShowFileStats From DB_UTILS’ 
        END 
 
CREATE TABLE    DB_UTILS.dbo.tb_DBShowFileStats 
        ( 
        row_id                  INT     IDENTITY(1,1), 
        dbname                  NVARCHAR(255), 
        fileid                  INT, 
        filegroup               INT,     
        totalextents            INT, 
        usedextents             INT, 
        name                    VARCHAR(255), 
        filename                VARCHAR(255) 
        ) 
 
———————————————————- 
IF EXISTS (SELECT * FROM DB_UTILS.dbo.sysobjects WHERE name = ‘tb_FixedDriveSpace’) 
        BEGIN 
                DROP TABLE DB_UTILS.dbo.tb_FixedDriveSpace  
                PRINT ‘DROPPED tb_FixedDriveSpace From DB_UTILS’ 
        END 
CREATE TABLE    DB_UTILS.dbo.tb_FixedDriveSpace  
        ( 
        drive_name      CHAR(1) PRIMARY KEY,  
        free_space      DECIMAL(10,3) NULL, 
        total_size      DECIMAL(10,3) NULL 
        )  
         
——————————————————– 
— create views 
——————————————————– 
USE DB_UTILS 
GO 
 
IF  EXISTS (SELECT * FROM DB_UTILS.sys.views WHERE name = ‘vw_FixedDriveSpace’) 
        BEGIN 
                DROP VIEW vw_FixedDriveSpace 
                PRINT ‘DROPPING View vw_FixedDriveSpace’ 
        END 
GO 
 
CREATE VIEW vw_FixedDriveSpace  AS 
 
SELECT  TOP 100 PERCENT  
        a.drive_name + ‘:\’                                     as [Drive], 
         STR(SUM(a.total_size), 10, 2)                          as [Drive_Size], 
         STR(SUM((a.total_size – a.free_space)), 10, 2)         as [Space_Used], 
         STR(SUM(a.free_space), 10, 2)                          as [Space_Free], 
         STR((a.free_space * 100 / a.total_size), 10, 2)        as [Pct_Free] 
FROM     DB_UTILS.dbo.tb_FixedDriveSpace   as a 
GROUP BY a.drive_name, a.free_space, a.total_size 
ORDER BY (a.free_space * 100 / a.total_size), a.drive_name 
GO 
———————————————————- 
IF  EXISTS (SELECT * FROM DB_UTILS.sys.views WHERE name = ‘vw_DBFreeSpace’) 
        BEGIN 
                DROP VIEW vw_DBFreeSpace 
                PRINT ‘DROPPING View vw_DBFreeSpace’ 
        END 
GO 
 
CREATE VIEW vw_DBFreeSpace      AS 
 
SELECT  TOP 100 PERCENT  
        SUBSTRING(a.dbname, 1, 26)                                                      as [Name], 
         SUBSTRING(b.name, 1, 26)                                                       as [FileName], 
         LEFT(b.filename, 3)                                                            as [Drive], 
         STR(SUM((b.totalextents * 64.0) / 1024.0), 10, 2)                              as [DB_File_Size], 
         STR(SUM((b.usedextents  * 64.0) / 1024.0), 10, 2)                              as [Space_Used], 
         STR(SUM((b.totalextents – b.usedextents)  * 64.0 / 1024.0), 10, 2)             as [Space_Free], 
         STR(SUM((((b.totalextents – b.usedextents)  * 64.0) / 1024.0 * 100.0 /  
         ((b.totalextents * 64.0) / 1024.0))), 10, 2)                                  as [Pct_Free] 
FROM     DB_UTILS.dbo.tb_Databases                           as a 
         INNER JOIN DB_UTILS.dbo.tb_DBShowFileStats       as b on a.dbname = b.dbname 
GROUP BY a.dbname, b.name, b.filename, b.totalextents, b.usedextents 
ORDER BY (((b.totalextents – b.usedextents)  * 64.0) / 1024.0 * 100.0 / ((b.totalextents * 64.0) / 1024.0)), 
         a.dbname, 
         b.name 
GO 
 
 
USE DB_UTILS 
GO 
 
IF  EXISTS (SELECT * FROM DB_UTILS.sys.objects WHERE name = ‘bp_DBandServerSpaceReport’ AND type in (N’P’, N’PC’)) 
        BEGIN 
                DROP PROCEDURE bp_DBandServerSpaceReport 
                PRINT ‘DROPPING bp_DBandServerSpaceReport’ 
        END 
GO 
 
CREATE PROCEDURE bp_DBandServerSpaceReport AS 
 
SET NOCOUNT ON 
— work variables 
—————– 
DECLARE @SQLCmd                 NVARCHAR(MAX), 
        @Result                 INT, 
        @FSO                    INT, 
        @DriveNameOut           INT, 
        @TotalSizeOut           VARCHAR(20),  
        @MB                     NUMERIC 
 
SET     @MB = 1048576 
 
— clear work tables 
——————– 
TRUNCATE TABLE DB_UTILS.dbo.tb_Databases 
TRUNCATE TABLE DB_UTILS.dbo.tb_DBShowFileStats 
TRUNCATE TABLE DB_UTILS.dbo.tb_FixedDriveSpace 
———————————————————- 
— load database table with database names 
———————————————————– 
SET             @SQLCmd = ‘master..sp_databases’ 
INSERT INTO     DB_UTILS.dbo.tb_Databases (dbname, db_size, remarks) EXEC sp_executesql @SQLCmd 
 
 
— loop through databases and load file stats table with information for each database 
————————————————————————————– 
DECLARE         @dbname VARCHAR(200) 
SET             @dbname = ” 
 
WHILE   @dbname IS NOT NULL 
        BEGIN 
                SELECT  @dbname = MIN(dbname) 
                FROM    DB_UTILS.dbo.tb_Databases 
                WHERE   dbname > @dbname 
 
                IF      @dbname IS NOT NULL 
                        BEGIN 
                                SELECT          @SQLCmd = ‘USE [‘ + @dbname + ‘]; DBCC SHOWFILESTATS’ 
                                INSERT INTO     DB_UTILS.dbo.tb_DBShowFileStats (fileid, filegroup, totalextents, usedextents, name, filename) EXEC sp_executesql @SQLCmd 
 
                                UPDATE  DB_UTILS.dbo.tb_DBShowFileStats 
                                SET     dbname = @dbname 
                                WHERE   dbname IS NULL 
                        END 
        END 
 
— loop through databases and load file stats table with information for each database 
————————————————————————————– 
INSERT  DB_UTILS.dbo.tb_FixedDriveSpace(drive_name, free_space) EXEC master.dbo.xp_fixeddrives  
 
EXEC    @Result = sp_OACreate ‘Scripting.FileSystemObject’, @FSO OUT  
 
IF @Result <> 0  
        BEGIN 
                EXEC sp_OAGetErrorInfo  @FSO 
        END 
 
— loop through and get drive metadata for each drive on the server 
——————————————————————- 
DECLARE         @drive_name VARCHAR(50) 
SET             @drive_name = ” 
 
WHILE   @drive_name IS NOT NULL 
        BEGIN 
                SELECT  @drive_name = MIN(drive_name) 
                FROM    DB_UTILS.dbo.tb_FixedDriveSpace  
                WHERE   drive_name > @drive_name 
 
                IF      @drive_name IS NOT NULL 
                        BEGIN 
                                — get drive information 
                                ———————— 
                                EXEC    @Result = sp_OAMethod @FSO, ‘GetDrive’, @DriveNameOut OUT, @drive_name 
 
                                — error handling 
                                —————– 
                                IF      @Result <> 0  
                                        BEGIN 
                                                EXEC sp_OAGetErrorInfo @FSO  
                                        END 
 
                                — get drive size 
                                —————– 
                                EXEC    @Result = sp_OAGetProperty  @DriveNameOut, ‘TotalSize’, @TotalSizeOut OUT  
 
                                — error handling 
                                —————– 
                                IF      @Result <> 0  
                                        BEGIN 
                                                EXEC sp_OAGetErrorInfo  @DriveNameOut  
                                        END 
 
                                — update temp table with values 
                                ——————————– 
                                UPDATE  DB_UTILS.dbo.tb_FixedDriveSpace  
                                SET     total_size      = @TotalSizeOut / @MB 
                                WHERE   drive_name      = @drive_name  
                        END 
        END 
 
— destroy the fso 
—————— 
EXEC    @Result = sp_OADestroy @FSO  
 
— error handling 
—————– 
IF      @Result <> 0  
        BEGIN 
                EXEC sp_OAGetErrorInfo  @FSO 
        END 
GO 
 
 
— create procedure that will send mail 
————————————— 
IF  EXISTS (SELECT * FROM DB_UTILS.sys.objects WHERE name = ‘bp_DBandServerSpaceReport_SendEmail’ AND type in (N’P’, N’PC’)) 
        BEGIN 
                DROP PROCEDURE bp_DBandServerSpaceReport_SendEmail 
                PRINT ‘DROPPING bp_DBandServerSpaceReport_SendEmail’ 
        END 
GO 
 
CREATE PROCEDURE bp_DBandServerSpaceReport_SendEmail AS 
 
— send report 
————– 
 
— email variables 
—————— 
DECLARE @EmailAddress           VARCHAR(30), 
        @EmailSubject           VARCHAR(200), 
        @EmailImportance        VARCHAR(10), 
        @EmailQuery             VARCHAR(4000), 
        @EmailMessage           VARCHAR(500), 
        @EmailFormat            VARCHAR(20), 
        @EmailResultsWidth      INT 
 
— drive space query 
——————– 
SELECT  @EmailAddress           = ‘NEED TO ENTER YOUR EMAIL ADDRESS HERE’, 
        @EmailSubject           = ‘Database Size Report – ‘ + @@SERVERNAME, 
        @EmailMessage           = ‘The System Drive Space Results Are As Follows:’      + CHAR(10) +  
                                  ‘———————————————-‘, 
        @EmailQuery             =  
                                ‘SET NOCOUNT ON;’                                       + CHAR(10) +  
                                ‘PRINT   ””’                                          + CHAR(10) +  
                                ‘SELECT  Drive              as [Drive],’                + CHAR(10) +  
                                ‘        Drive_Size         as [Drive Size (MB)],’      + CHAR(10) +  
                                ‘        Space_Used         as [Space Used (MB)],’      + CHAR(10) +  
                                ‘        Space_Free         as [Space Free (MB)],’      + CHAR(10) +  
                                ‘        Pct_Free           as [Pct. Free]’             + CHAR(10) +  
                                ‘FROM    DB_UTILS.dbo.vw_FixedDriveSpace’               + CHAR(10) +  
                                ‘ORDER BY Pct_Free, Drive’                              + CHAR(10) +  
                                ‘PRINT   ””’                                          + CHAR(10) +  
                                ‘PRINT   ””’                                          + CHAR(10) +  
                                ‘SELECT  ”                                         ” as ”The Database Space Results Are As Follows:”’               + CHAR(10) +  
                                ‘SELECT  Name            as [DB Name],’                 + CHAR(10) +  
                                ‘        FileName        as [DB File Name],’            + CHAR(10) +  
                                ‘        Drive           as [Drive],’                   + CHAR(10) +  
                                ‘        DB_File_Size    as [DB File Size],’            + CHAR(10) +  
                                ‘        Space_Used      as [Space Used (MB)],’         + CHAR(10) +  
                                ‘        Space_Free      as [Space Free (MB)],’         + CHAR(10) +  
                                ‘        Pct_Free        as [Pct. Free]’                + CHAR(10) +  
                                ‘FROM    DB_UTILS.dbo.vw_DBFreeSpace’                   + CHAR(10) +  
                                ‘ORDER BY Pct_Free, Name, FileName’, 
 
        @EmailFormat            = ‘TEXT’, 
        @EmailImportance        = ‘NORMAL’, 
        @EmailResultsWidth      = 150 
 
— Send Mail 
———— 
        EXEC msdb..sp_send_dbmail 
                @profile_name           = @@SERVERNAME, 
                @recipients             = @EmailAddress, 
                @subject                = @EmailSubject, 
                @body                   = @EmailMessage, 
                @query                  = @EmailQuery, 
                @body_format            = @EmailFormat, 
                @query_result_width     = @EmailResultsWidth, 
                @importance             = @EmailImportance 
GO 
 
 
— create SQL job 
—————— 
USE [msdb] 
GO 
 
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N’DB_UTILS – Server and DB Space Check’) 
        EXEC msdb.dbo.sp_delete_job @job_name = N’DB_UTILS – Server and DB Space Check’, @delete_unused_schedule=1 
GO 
 
BEGIN TRANSACTION 
DECLARE @ReturnCode INT 
SELECT  @ReturnCode = 0 
 
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N’Database Maintenance’ AND category_class=1) 
BEGIN 
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N’Database Maintenance’ 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 
END 
 
DECLARE @jobId BINARY(16) 
EXEC    @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’DB_UTILS – Server and DB Space Check’,  
        @enabled=1,  
        @notify_level_eventlog=2,  
        @notify_level_email=2,  
        @notify_level_netsend=0,  
        @notify_level_page=0,  
        @delete_level=0,  
        @description=N’Compiles A Size Report On All Drives And All Databases Reporting Space In-Use, Free Space, And Total Space.’,  
        @category_name=N’Database Maintenance’,  
        @owner_login_name=N’sa’,  
        @notify_email_operator_name=N’SqlAdmin’,  
        @job_id = @jobId OUTPUT 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 
EXEC    @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Run DB Space Report’,  
        @step_id=1,  
        @cmdexec_success_code=0,  
        @on_success_action=3,  
        @on_success_step_id=0,  
        @on_fail_action=2,  
        @on_fail_step_id=0,  
        @retry_attempts=0,  
        @retry_interval=1,  
        @os_run_priority=0, @subsystem=N’TSQL’,  
        @command=N’EXEC DB_UTILS.dbo.bp_DBandServerSpaceReport’,  
        @database_name=N’DB_UTILS’,  
        @flags=0 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
 
EXEC    @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Email DB Space Report’,  
        @step_id=2,  
        @cmdexec_success_code=0,  
        @on_success_action=1,  
        @on_success_step_id=0,  
        @on_fail_action=2,  
        @on_fail_step_id=0,  
        @retry_attempts=0,  
        @retry_interval=1,  
        @os_run_priority=0, @subsystem=N’TSQL’,  
        @command=N’EXEC DB_UTILS.dbo.bp_DBandServerSpaceReport_SendEmail’,  
        @database_name=N’DB_UTILS’,  
        @flags=0 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
        EXEC    @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
EXEC    @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Weekly’,  
        @enabled=1,  
        @freq_type=8,  
        @freq_interval=1,  
        @freq_subday_type=1,  
        @freq_subday_interval=0,  
        @freq_relative_interval=0,  
        @freq_recurrence_factor=1,  
        @active_start_date=20011115,  
        @active_end_date=99991231,  
        @active_start_time=41500,  
        @active_end_time=235959 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
        EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’ 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
        COMMIT TRANSACTION 
        GOTO EndSave 
QuitWithRollback: 
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave:

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: