Managing Free Space

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul Els » Managing Free Space

Managing Free Space

Rate Topic

Display Mode

Topic Options

Author

Message

Paul Els

Posted Thursday, September 10, 2009 12:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 7:42 AM
Points: 8, Visits: 83

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

Post #785529

MatthieuQ

Posted Thursday, September 10, 2009 1:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 13, 2013 5:14 AM
Points: 46, Visits: 71

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

Post #785556

liebesiech

Posted Thursday, September 10, 2009 4:48 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:55 AM
Points: 126, Visits: 751

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

Post #785607

Steven Woolheater-421675

Posted Thursday, September 10, 2009 6:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 8:46 AM
Points: 3, Visits: 318

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

Post #785646

Fishbarnriots

Posted Thursday, September 17, 2009 4:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:58 AM
Points: 237, Visits: 1,753

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]

Post #789522

Fishbarnriots

Posted Thursday, September 17, 2009 4:44 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:58 AM
Points: 237, Visits: 1,753

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

Post #789527

Paul Els

Posted Tuesday, November 17, 2009 6:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 7:42 AM
Points: 8, Visits: 83

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

Post #820020

Paul Els

Posted Thursday, November 26, 2009 11:24 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 7:42 AM
Points: 8, Visits: 83

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

Post #825458

« Prev Topic | Next Topic »

Permissions