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