SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Paul Els / Managing Free Space / Latest Posts

RE: Managing Free Space

Paul Els — Thu, 26 Nov 2009 23:24:33 GMT

-- 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 PREREQUISITESDECLARE @sqlversion sql_variantSELECT @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 SERVERset nocount onCREATE TABLE #Drives (DriveLetter char(1), MBFree int)INSERT INTO #DrivesEXEC master..xp_fixeddrives-- DECLARE VARIABLESDECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)-- FIND THE FIRST AND LAST DRIVES FOR THE LOOPSELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives-- CREATE THE TABLE TO HOST THE LIST OF FILESCREATE 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 <= @MaxDriveLetterBEGIN -- 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 > @CurrentDriveLetterEND-- CLEAN UP #FILESupdate #Files set FilePath = REPLACE(RawData,'Directory of ','') where RawData like '%Directory of %:%'update #Files set FilePath = SubString(FilePath, 2, 255) where FilePath is not nulldelete 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 NULLupdate #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULLupdate #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULLupdate #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULLupdate #Files set FileSizeInMB = CONVERT(decimal(18,2), replace(FileSize,CHAR(160),'')) / 1024 / 1024, FileSizeInGB = CONVERT(decimal(18,2), replace(FileSize,CHAR(160),'')) / 1024 / 1024 / 1024DECLARE @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 descWHILE @autono_id IS NOT NULLBEGIN 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 descENDdelete 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 VOLUMEselect '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/VOLUMEselect '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 WASTAGEselect '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 SMALLESTselect 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 SMALLESTselect 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 SMALLESTselect 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 #Filesdrop table #Drives

RE: Managing Free Space

Paul Els — Tue, 17 Nov 2009 06:58:56 GMT

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 / 1024If 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 PREREQUISITESDECLARE @sqlversion sql_variantSELECT @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 SERVERset nocount onCREATE TABLE #Drives (DriveLetter char(1), MBFree int)INSERT INTO #DrivesEXEC master..xp_fixeddrives-- DECLARE VARIABLESDECLARE @CurrentDriveLetter CHAR(1), @MaxDriveLetter CHAR(1), @EXECSTR varchar(1024)-- FIND THE FIRST AND LAST DRIVES FOR THE LOOPSELECT @CurrentDriveLetter = Min(DriveLetter), @MaxDriveLetter = Max(DriveLetter) from #Drives-- CREATE THE TABLE TO HOST THE LIST OF FILESCREATE 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 <= @MaxDriveLetterBEGIN -- 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 > @CurrentDriveLetterEND-- CLEAN UP #FILESupdate #Files set FilePath = REPLACE(RawData,'Directory of ','') where RawData like '%Directory of %:%'update #Files set FilePath = SubString(FilePath, 2, 255) where FilePath is not nulldelete 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 NULLupdate #Files set FileSize = substring (RawData, 22, 17) where FilePath is NULLupdate #Files set FileSize = replace(substring (RawData, 22, 17),',','') where FilePath is NULLupdate #Files set DriveLetter = substring(FilePath, 1, 1) where FilePath is not NULLupdate #Files set FileSizeInMB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024, FileSizeInGB = CONVERT(decimal(18,2), FileSize) / 1024 / 1024 / 1024DECLARE @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 descWHILE @autono_id IS NOT NULLBEGIN 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 descENDdelete 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 VOLUMEselect '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/VOLUMEselect '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 WASTAGEselect '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 SMALLESTselect 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 SMALLESTselect 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 SMALLESTselect 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 #Filesdrop table #Drives

RE: Managing Free Space

Fishbarnriots — Thu, 17 Sep 2009 04:44:25 GMT

In response to MattieuQ's reply;Amend the update statements to the followingupdate #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)) = 1update #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

RE: Managing Free Space

Fishbarnriots — Thu, 17 Sep 2009 04:38:18 GMT

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_nameFROM 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]

RE: Managing Free Space

Steven Woolheater-421675 — Thu, 10 Sep 2009 06:25:20 GMT

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 CREATIONGOif object_id('utl_GetFileSystemDetail') is not null begin print 'Dropping Proc utl_GetFileSystemDetail' drop proc utl_GetFileSystemDetail endelse print 'Creating Proc utl_GetFileSystemDetail'gocreate 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 driveReturns : 0 Created By: Steven WoolheaterCreated On: 2009-08-27Modified : 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 endcreate 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 endif ( @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 processif ( @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 endgoif object_id('utl_GetFileSystemDetail') is not null begin print 'Created Proc utl_GetFileSystemDetail' endelse print 'Error Creating Proc utl_GetFileSystemDetail'--END PROC CREATIONExample of execution:exec SysDBA.dbo.utl_GetFileSystemDetail @ServerName = 'WebAppSrv1', @DriveLetter = 'E$', @SizeThreshold = 20This 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 = 1To address the actual Free Space reporting, I use Powershell and WMI extensions

RE: Managing Free Space

liebesiech — Thu, 10 Sep 2009 04:48:51 GMT

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

RE: Managing Free Space

MatthieuQ — Thu, 10 Sep 2009 01:56:33 GMT

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

Managing Free Space

Paul Els — Thu, 10 Sep 2009 00:29:53 GMT

Comments posted to this topic are about the item [B]Managing Free Space[/B]