Managing Free Space

  • Comments posted to this topic are about the item Managing Free Space

  • Hello,

    Thanks for your script.

    I'm having a conversion error at the following line :

    update #Files

    set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,

    FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024

    "conversion error from varchar to numeric." (I translate it as I can, I'm using a French OS with SQL Server Express 2005 French ed)

    Do you have any idea on how to solve this ?

    Thanks by advance for your answer,

    Matthieu

  • Thanks for this contribution. Space on servers is something I try to manage pro actively and I definitely will try out your script.

    Cheers

  • I would like to firstly say, that I prefer to find T-SQL answers to SQL server administration but in this case you can get much better results using Microsoft tools which are freely available.

    One way to simplify the process of discovering the content of a drive is to use the Microsoft utility, LogParser, which has built-in functionality that allows the output to be directed to a SQL Table and will give much more granular information. I have the luxury of a monitoring server to use as my play ground, so I had the Systems guys install LogParser. If you have to be clandestine about your utilities, LogParser doesn't have much of an overhead and can be dropped some where in the PATH. Once you are familiar with the tool you may find yourself loading your Windows Event Logs to SQL tables to quickly scan for issues or simply grab a block of events based on time or source.

    Parsing a 2 TB drive may take a minute or two but it is not resource intensive, even when executed against a drive on another server.

    To use this effectively, it requires that the following Table and Proc be compiled on the same server that has LogParser.

    Unfortunately, the beautifully formatted code below will have all the excess white space removed.

    CREATE TABLE SysDBA.dbo.FileSystem(

    Path varchar(255) NULL,

    Name varchar(255) NULL,

    Size bigint NULL,

    Attributes varchar(255) NULL,

    CreationTime datetime NULL,

    LastAccessTime datetime NULL,

    LastWriteTime datetime NULL

    )

    --END TABLE CREATION

    GO

    if object_id('utl_GetFileSystemDetail') is not null

    begin

    print 'Dropping Proc utl_GetFileSystemDetail'

    drop proc utl_GetFileSystemDetail

    end

    else

    print 'Creating Proc utl_GetFileSystemDetail'

    go

    create proc utl_GetFileSystemDetail

    @ServerName sysname, --Name of server to catalog

    @DriveLetter varchar(5), --Drive to parse

    @DestServer sysname = 'LogServer', --Destination server for parsed drive

    @DestDatabase sysname = 'SysDBA', --Destination database

    @Report tinyint = 0, --Return canned results

    @SizeThreshold tinyint = 10, --Minimum GB size for files

    @ReportOnly int = 0, --Skips the LogParser step and reports

    @Debug tinyint = 0 --Returns the Logparser command

    AS

    ---------------------------------------------------------------------------------------------

    /*

    Object : utl_GetFileSystemDetail

    Purpose : Log every file and directory of a given server and drive

    Returns : 0

    Created By: Steven Woolheater

    Created On: 2009-08-27

    Modified :

    Owner :

    */

    ---------------------------------------------------------------------------------------------

    SET NOCOUNT ON

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @CMD varchar(500)

    if ( object_id('tempdb..#FSErrors') is not null )

    begin

    drop table #FSErrors

    end

    create table #FSErrors (

    RowID int identity(1,1) primary key,

    Result varchar(100) NULL

    )

    select @DriveLetter = replace(replace(@DriveLetter, ':', ''), '$', ''), --Remove common drive suffixes

    @CMD = 'cd "C:\Program Files (x86)\Log Parser 2.2\"' --Change PWD so that we can execute LogParser.exe

    + ' & ' --Allows for multiple CMD line executions

    + ' LogParser.exe "'

    + 'select Path, '

    + 'Name, '

    + 'Size, '

    + 'Attributes, '

    + 'CreationTime, '

    + 'LastAccessTime, '

    + 'LastWriteTime '

    + 'into FileSystem '

    + 'from \\' + @ServerName + '\' + @DriveLetter + '$\*.*"'

    + ' -i:FS -e:10 -o:SQL -server:' + @DestServer

    + ' -database:' + @DestDatabase + ' -clearTable:ON'

    if ( @Debug 0 )

    begin

    select @CMD

    return

    end

    if ( @ReportOnly = 0 )

    begin

    truncate table SysDBA.dbo.FileSystem

    insert into #FSErrors ( Result )

    exec xp_cmdshell @CMD

    end

    --Return the error but let it continue, some errors are acceptable.

    if exists ( select 1

    from #FSErrors

    where Result like '%error%'

    or Result like '%denied%'

    or Result like '%fail%'

    )

    begin

    select Result

    from #FSErrors

    end

    --If we are going to run the report or we skipped the populate process

    if ( @Report 0 or @ReportOnly 0 )

    begin

    select Path as 'FullPath',

    Name as 'FileName',

    convert(numeric(20,2), Size/(1024.0*1024*1024)) as 'GBSize',

    Attributes,

    CreationTime,

    LastAccessTime,

    LastWriteTime

    from SysDBA.dbo.FileSystem

    where (Size / (1024*1024*1024.0)) >= @SizeThreshold

    end

    go

    if object_id('utl_GetFileSystemDetail') is not null

    begin

    print 'Created Proc utl_GetFileSystemDetail'

    end

    else

    print 'Error Creating Proc utl_GetFileSystemDetail'

    --END PROC CREATION

    Example of execution:

    exec SysDBA.dbo.utl_GetFileSystemDetail @ServerName = 'WebAppSrv1', @DriveLetter = 'E$',

    @SizeThreshold = 20

    This command will scan the E: Drive on WebAppSrv1 and report any file larger than 20GB.

    After the proc has been executed against a drive, you can skip the scan by setting @ReportOnly to a non-zero number and it will do a select against the existing information. So if you wanted to change the GB Size to 5GB from 20GB.

    exec SysDBA.dbo.utl_GetFileSystemDetail @ServerName = 'WebAppSrv1', @DriveLetter = 'E$', @SizeThreshold = 5, @ReportOnly = 1

    To address the actual Free Space reporting, I use Powershell and WMI extensions

  • The last report (report 4) must be wrong, backup file names are not recorded in sysaltfiles

    -- REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'bak'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    I reckon replace the code with (seems to do the job)

    --REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FilePath,[FileName], FileSizeInMB, FileSizeInGB

    from #Files [/b]where REPLACE(FilePath + '\' + [FileName], ':\\', ':\')

    not in (SELECT

    physical_device_name

    FROM

    master..sysdatabases DB

    JOIN

    msdb..BACKUPSET BS

    ON DB.name = BS.database_name

    JOIN msdb..backupmediaset MS

    ON

    BS.media_set_id = MS.media_set_id

    JOIN msdb..backupmediafamily MF

    ON

    BS.media_set_id = MF.media_set_id

    JOIN

    (

    select max(backup_set_id)as backup_set_id, database_name

    FROM msdb.dbo.backupset BS

    JOIN msdb.dbo.backupmediaset MS

    ON

    BS.media_set_id = MS.media_set_id

    JOIN msdb.dbo.backupmediafamily MF

    ON

    BS.media_set_id = MF.media_set_id

    and type = 'D'

    GROUP BY database_name

    ) MaxBackup

    ON

    BS.backup_set_id >= MaxBackup.backup_set_id

    and bs.database_name = MaxBackup.database_name)

    and right(FileName,3) = 'bak'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc [/b]

  • In response to MattieuQ's reply;

    Amend the update statements to the following

    update #Files set [FileName] = substring (RawData, 40, 255) where FilePath is NULL

    update #Files set FileSize = convert(decimal(18, 2), replace(substring (RawData, 22, 17), ',', '')) where FilePath is NULL and isnumeric(substring (RawData, 22, 17)) = 1

    update #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULL

    update #Files set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,

    FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024

    where isnumeric (FileSize) = 1

  • Hi Guys.

    I only saw today that they have posted my article.

    At the bottom of this is my somewhat updated code. Please won't you try again and let me know where I can assist you. Run the Step 1 code, then each of the reports respectively in the Step 2 part and then Step 3 to clean up.

    The basic idea of the code is to search through the drives for files with certain file extentions and record them to a table and then to use that to identify:

    (a) unattached db files (which potentially wastes a lot of space) and

    (b) find backup files on the drive(s) - also potensially taking up a lot of space. (SSC-Enthusiastic, RE "The last report (report 4) must be wrong, backup file names are not recorded in sysaltfiles". You must have misunderstood me, I never meant to imply in any way that backup files are in SysAltFiles).

    SCC Rookie - RE: update #Files

    set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,

    FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024

    If you are still interested in getting this resolved, I'll need some info from you.

    As you can see I am trying to convert the FileSize to MB and GB. It may be that the French version of DOS list it in a different offset, so please pay special attention to the section "-- CLEAN UP #FILES" which translates the results from DOS into the SQL Table. You can change the offsets.

    -- THIS CODE FINDS POTENTIALLY UNWANTED FILES ON SQL SERVERS.

    -- WHICH INCLUDES: .PST, .JPG, .BAK, .UBAK, .MDF, .NDF, .LDF

    -- IT COMPARES THE DATABASE FILES ON THE DISKS WITH

    -- SYSALTFILES (WHICH IS THE LIST OF DB FILES IN USE BY THE INSTANCE)

    -- TO DETERMINE WHICH OF THE FILE ON THE DISK AREN'T USED BY THE INSTANCE.

    -- IF YOU'RE AWAKE YOU'D NOTICE THAT THE ONE FLAW IS THAT IF THE BOX CONTAINS > 1 INSTANCE,

    -- IT DOESN'T COMPARE THE DATABASE FILES ON THE DISKS TO ALL INSTANCES,

    -- BUT IT ONLY COMPARES AGAINST THE CURRENT INSTANCE

    -- THOUGH YOU CAN RUN THIS AGAINST A BOX WITH MULTIPLE INSTANCES, THE RESULTS WILL BE SKEWED

    -- BY THE FACT THAT WE ARE ONLY SAYING "WHICH FILES HAS THE DISKS GOT THAT ISN'T IN A PARTICULAR SQL INSTANCE"

    -- IDEALLY WE'D LIKE TO SAY "WHICH FILES HAS THE DISKS GOT THAT AREN'T USED BY ANY SQL INSTANCE"

    -- STILL IT IS HANDY ON SINGLE INSTANCE SERVERS

    -- PREREQUISITE: YOUR INSTANCE MUST HAVE XP_CMDSHELL MUST BE ENABLED

    -- IF IT IS SQL2000, XP_CMDSHELL IS INHERRINTLY ENABLED BY DEFAULT (UNLESS YOU PREVENTED IT)

    -- AND IS NOT EVEN LISTED IN THE SP_CONFIGURE OPTIONS

    -- WRITTEN BY: PAUL ELS

    -- VERSION : 5

    -- WANT THE LATEST CODE VERSION? OR SEND COMMENTS/QUESTIONS/SUGGESTIONS TO: paul_els@hotmail.com

    -- Step 1: CHECK PREREQUISITES

    DECLARE @sqlversion sql_variant

    SELECT @sqlversion = SERVERPROPERTY('productversion')

    IF LEFT(CONVERT(VARCHAR(255),@SQLVERSION),2) <> '8.'

    BEGIN

    -- START CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --

    CREATE TABLE #xp_cmdshell (OptionName varchar(255), minval int, maxval int, configval int, runval int)

    INSERT INTO #xp_cmdshell

    EXEC master..sp_configure

    declare @runval int

    select @runval = runval from #xp_cmdshell where OptionName = 'xp_cmdshell'

    drop table #xp_cmdshell

    if @runval is null

    begin

    RAISERROR ('enable "show advanced options" before you run this code', -- Message text.

    10, -- Severity,

    16 -- State,

    )

    GOTO EndOfStep1

    end

    if @runval = 1

    begin

    print '' -- The pre-requisites are enabled, so we can continue

    end

    else -- IF xp_cmdshell is NOT enabled

    begin

    RAISERROR ('enable xp_cmdshell before you run this code', -- Message text.

    10, -- Severity,

    16 -- State,

    )

    GOTO EndOfStep1

    end

    -- FINISH CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --

    END

    -- OBTAIN A LIST OF ALL THE DRIVES ON THE SERVER

    set nocount on

    CREATE TABLE #Drives (DriveLetter char(1), MBFree int)

    INSERT INTO #Drives

    EXEC master..xp_fixeddrives

    -- DECLARE VARIABLES

    DECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)

    -- FIND THE FIRST AND LAST DRIVES FOR THE LOOP

    SELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives

    -- CREATE THE TABLE TO HOST THE LIST OF FILES

    CREATE TABLE #Files (autono_id int NOT NULL IDENTITY (1, 1), RawData varchar(255), FilePath varchar(255), DriveLetter CHAR(1), [FileName] varchar(255), FileSize varchar(17), FileSizeInMB decimal(18,2), FileSizeInGB decimal(18,2))

    WHILE @CurrentDriveLetter <= @MaxDriveLetter

    BEGIN

    -- STORE THE FILES WE ARE LOOKING FOR IN THE #FILES TABLE

    -- PRINT STR('dir ' + STR(@CurrentDriveLetter) + ':\*.mdf;*.ndf;*.ldf /s')

    SELECT @EXECSTR = 'dir ' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.mdf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ndf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.pst;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.jpg;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ldf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ubak;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.BAK /s' -- string in the drive letter later

    INSERT INTO #Files (RawData)

    EXEC master..xp_cmdshell @EXECSTR

    -- PRINT @EXECSTR

    select @CurrentDriveLetter = MIN(DriveLetter) from #Drives where DriveLetter > @CurrentDriveLetter

    END

    -- CLEAN UP #FILES

    update #Files

    set FilePath = REPLACE(RawData,'Directory of ','')

    where RawData like '%Directory of %:%'

    update #Files

    set FilePath = SubString(FilePath, 2, 255)

    where FilePath is not null

    delete from #Files

    where RawData is NULL

    or RawData = 'File Not Found'

    or RawData like '%Volume%'

    or RawData like '%File(s)%'

    or RawData like '%Dir(s)%'

    or RawData like '%Total Files Listed:%'

    update #Files set [FileName] = substring (RawData, 40, 255) where FilePath is NULL

    update #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULL

    update #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULL

    update #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULL

    update #Files

    set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024,

    FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024

    DECLARE @autono_id int, @fp varchar(255), @drive char(1)

    select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter

    from #files F1

    where FilePath is not null

    and autono_id < (select max(autono_id) from #Files where FilePath is NULL)

    order by autono_id desc

    WHILE @autono_id IS NOT NULL

    BEGIN

    update #Files

    set [FilePath] = @fp, DriveLetter = @drive

    where autono_id > @autono_id and [FilePath] is NULL

    DELETE from #Files where [FileName] is null AND DriveLetter = @drive AND autono_id > @autono_id

    SELECT @autono_id = NULL, @fp = NULL, @drive = NULL -- RESET FLAGS

    select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter

    from #files F1

    where FilePath is not null

    and autono_id < (select max(autono_id) from #Files where FilePath is NULL)

    order by autono_id desc

    END

    delete from #Files where FileName is NULL or FilePath like '%i386%' or FilePath like '%ia64%'

    EndOfStep1:

    -- *******************************************************************************************

    -- Step 2: Run each of the reports respectively

    -- *******************************************************************************************

    -- REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME

    select 'DriveLetter' = Ltrim(rtrim(LEFT(DriveLetter,1))),

    'Potential Saving (in MB)' = LTRIM(STR(SUM(FileSizeInMB))),

    'Potential Saving (in GB)' = LTRIM(STR(SUM(FileSizeInGB)))

    from #Files

    where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

    group by Ltrim(rtrim(LEFT(DriveLetter,1)))

    order by Ltrim(rtrim(LEFT(DriveLetter,1)))

    -- REPORT 1b:

    EXEC xp_fixeddrives

    -- REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME

    select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),

    'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

    and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by Ltrim(rtrim(LEFT(DriveLetter,1))) asc, 3 desc

    -- REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE

    select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),

    'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

    and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by 3 desc

    -- REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'bak'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    -- REPORT 5: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .PST FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'pst'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    -- REPORT 6: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .jpg FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'jpg'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    -- *******************************************************************************************

    -- Step 3: CLEAUP

    -- *******************************************************************************************

    drop table #Files

    drop table #Drives

  • -- HERE IS VERSION 6

    -- IT FIXES THE SIZE CONVERSION ISSUE FOR WINDOWS 2008 SERVERS

    -- THIS CODE FINDS POTENTIALLY UNWANTED FILES ON SQL SERVERS.

    -- WHICH INCLUDES: .PST, .JPG, .BAK, .UBAK, .MDF, .NDF, .LDF

    -- IT COMPARES THE DATABASE FILES ON THE DISKS WITH

    -- SYSALTFILES (WHICH IS THE LIST OF DB FILES IN USE BY THE INSTANCE)

    -- TO DETERMINE WHICH OF THE FILE ON THE DISK AREN'T USED BY THE INSTANCE.

    -- IF YOU'RE AWAKE YOU'D NOTICE THAT THE ONE FLAW IS THAT IF THE BOX CONTAINS > 1 INSTANCE,

    -- IT DOESN'T COMPARE THE DATABASE FILES ON THE DISKS TO ALL INSTANCES,

    -- BUT IT ONLY COMPARES AGAINST THE CURRENT INSTANCE

    -- THOUGH YOU CAN RUN THIS AGAINST A BOX WITH MULTIPLE INSTANCES, THE RESULTS WILL BE SKEWED

    -- BY THE FACT THAT WE ARE ONLY SAYING "WHICH FILES HAS THE DISKS GOT THAT ISN'T IN A PARTICULAR SQL INSTANCE"

    -- IDEALLY WE'D LIKE TO SAY "WHICH FILES HAS THE DISKS GOT THAT AREN'T USED BY ANY SQL INSTANCE"

    -- STILL IT IS HANDY ON SINGLE INSTANCE SERVERS

    -- PREREQUISITE: YOUR INSTANCE MUST HAVE XP_CMDSHELL MUST BE ENABLED

    -- IF IT IS SQL2000, XP_CMDSHELL IS INHERRINTLY ENABLED BY DEFAULT (UNLESS YOU PREVENTED IT)

    -- AND IS NOT EVEN LISTED IN THE SP_CONFIGURE OPTIONS

    -- WRITTEN BY: PAUL ELS

    -- VERSION : 6

    -- WANT THE LATEST CODE VERSION? OR SEND COMMENTS/QUESTIONS/SUGGESTIONS TO: paul_els@hotmail.com

    -- Step 1: CHECK PREREQUISITES

    DECLARE @sqlversion sql_variant

    SELECT @sqlversion = SERVERPROPERTY('productversion')

    IF LEFT(CONVERT(VARCHAR(255),@SQLVERSION),2) <> '8.'

    BEGIN

    -- START CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --

    CREATE TABLE #xp_cmdshell (OptionName varchar(255), minval int, maxval int, configval int, runval int)

    INSERT INTO #xp_cmdshell

    EXEC master..sp_configure

    declare @runval int

    select @runval = runval from #xp_cmdshell where OptionName = 'xp_cmdshell'

    drop table #xp_cmdshell

    if @runval is null

    begin

    RAISERROR ('enable "show advanced options" before you run this code', -- Message text.

    10, -- Severity,

    16 -- State,

    )

    GOTO EndOfStep1

    end

    if @runval = 1

    begin

    print '' -- The pre-requisites are enabled, so we can continue

    end

    else -- IF xp_cmdshell is NOT enabled

    begin

    RAISERROR ('enable xp_cmdshell before you run this code', -- Message text.

    10, -- Severity,

    16 -- State,

    )

    GOTO EndOfStep1

    end

    -- FINISH CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --

    END

    -- OBTAIN A LIST OF ALL THE DRIVES ON THE SERVER

    set nocount on

    CREATE TABLE #Drives (DriveLetter char(1), MBFree int)

    INSERT INTO #Drives

    EXEC master..xp_fixeddrives

    -- DECLARE VARIABLES

    DECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)

    -- FIND THE FIRST AND LAST DRIVES FOR THE LOOP

    SELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives

    -- CREATE THE TABLE TO HOST THE LIST OF FILES

    CREATE TABLE #Files (autono_id int NOT NULL IDENTITY (1, 1), RawData varchar(255), FilePath varchar(255), DriveLetter CHAR(1), [FileName] varchar(255), FileSize varchar(17), FileSizeInMB decimal(18,2), FileSizeInGB decimal(18,2))

    WHILE @CurrentDriveLetter <= @MaxDriveLetter

    BEGIN

    -- STORE THE FILES WE ARE LOOKING FOR IN THE #FILES TABLE

    -- PRINT STR('dir ' + STR(@CurrentDriveLetter) + ':\*.mdf;*.ndf;*.ldf /s')

    SELECT @EXECSTR = 'dir ' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.mdf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ndf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.pst;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.jpg;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ldf;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.ubak;' + CONVERT(VARCHAR(1),@CurrentDriveLetter) + ':\*.BAK /s' -- string in the drive letter later

    INSERT INTO #Files (RawData)

    EXEC master..xp_cmdshell @EXECSTR

    -- PRINT @EXECSTR

    select @CurrentDriveLetter = MIN(DriveLetter) from #Drives where DriveLetter > @CurrentDriveLetter

    END

    -- CLEAN UP #FILES

    update #Files

    set FilePath = REPLACE(RawData,'Directory of ','')

    where RawData like '%Directory of %:%'

    update #Files

    set FilePath = SubString(FilePath, 2, 255)

    where FilePath is not null

    delete from #Files

    where RawData is NULL

    or RawData = 'File Not Found'

    or RawData like '%Volume%'

    or RawData like '%File(s)%'

    or RawData like '%Dir(s)%'

    or RawData like '%Total Files Listed:%'

    update #Files set [FileName] = substring (RawData, 40, 255) where FilePath is NULL

    update #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULL

    update #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULL

    update #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULL

    update #Files

    set FileSizeInMB = CONVERT(decimal(18,2), replace(FileSize,CHAR(160),'')) / 1024 / 1024,

    FileSizeInGB = CONVERT(decimal(18,2), replace(FileSize,CHAR(160),'')) / 1024 / 1024 / 1024

    DECLARE @autono_id int, @fp varchar(255), @drive char(1)

    select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter

    from #files F1

    where FilePath is not null

    and autono_id < (select max(autono_id) from #Files where FilePath is NULL)

    order by autono_id desc

    WHILE @autono_id IS NOT NULL

    BEGIN

    update #Files

    set [FilePath] = @fp, DriveLetter = @drive

    where autono_id > @autono_id and [FilePath] is NULL

    DELETE from #Files where [FileName] is null AND DriveLetter = @drive AND autono_id > @autono_id

    SELECT @autono_id = NULL, @fp = NULL, @drive = NULL -- RESET FLAGS

    select top 1 @autono_id = autono_id, @fp = [FilePath], @drive = DriveLetter

    from #files F1

    where FilePath is not null

    and autono_id < (select max(autono_id) from #Files where FilePath is NULL)

    order by autono_id desc

    END

    delete from #Files where FileName is NULL or FilePath like '%i386%' or FilePath like '%ia64%'

    EndOfStep1:

    -- *******************************************************************************************

    -- Step 2: Run each of the reports respectively

    -- *******************************************************************************************

    -- REPORT 1: POTENTIAL SPACE SAVING (SUMMARY) PER VOLUME

    select 'DriveLetter' = Ltrim(rtrim(LEFT(DriveLetter,1))),

    'Potential Saving (in MB)' = LTRIM(STR(SUM(FileSizeInMB))),

    'Potential Saving (in GB)' = LTRIM(STR(SUM(FileSizeInGB)))

    from #Files

    where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

    group by Ltrim(rtrim(LEFT(DriveLetter,1)))

    order by Ltrim(rtrim(LEFT(DriveLetter,1)))

    -- REPORT 1b:

    EXEC xp_fixeddrives

    -- REPORT 2: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) PER DRIVE/VOLUME

    select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),

    'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

    and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by Ltrim(rtrim(LEFT(DriveLetter,1))) asc, 3 desc

    -- REPORT 3: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR DATABASE FILES) DESC BY WASTAGE

    select 'Drive' = Ltrim(rtrim(LEFT(DriveLetter,1))),

    'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where REPLACE(FilePath + '\' + [FileName], ':\\', ':\') not in (select Ltrim(rtrim(filename)) from master.dbo.sysaltfiles)

    and right(FileName,3) <> 'bak' -- EXCLUDE .BAK/.UBAK FILES

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by 3 desc

    -- REPORT 4: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .BAK/.UBAK FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'bak'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    -- REPORT 5: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .PST FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'pst'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    -- REPORT 6: POTENTIAL SPACE SAVING (DETAILED) REPORT (FOR .jpg FILES), FROM BIGGEST TO SMALLEST

    select DriveLetter, 'FileName' = REPLACE(FilePath + '\' + [FileName], ':\\', ':\'), FileSizeInMB, FileSizeInGB

    from #Files

    where FilePath + '\' + FileName not in (select filename from master.dbo.sysaltfiles)

    and right(FileName,3) = 'jpg'

    and FileSizeInMB > 0 -- YOU CAN STIPULATE YOU ARE LOOKING FOR FILES LARGER THAN X MB

    order by FileSizeInMB desc, REPLACE(FilePath + '\' + [FileName], ':\\', ':\') asc

    -- *******************************************************************************************

    -- Step 3: CLEAUP

    -- *******************************************************************************************

    drop table #Files

    drop table #Drives

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply