• -- 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