|
This code determines which DB Files aren’t attached to the instance.
-- Step 1:
-- THIS CODE COMPARES THE DATABASE FILES ON THE DISKS WITH SYSALTFILES
-- TO DETERMINE WHICH OF THOSE FILES 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 SOME SERVERS
-- PREREQUISITE: YOUR INSTANCE MUST HAVE XP_CMDSHELL MUST BE ENABLED
-- IF IT IS SQL2000, XP_CMDSHELL IS INHERRINTLY ENABLED BY DEFAULT
-- AND IS NOT EVEN LISTED IN THE SP_CONFIGURE OPTIONS
set nocount on
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,
)
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,
)
end
-- FINISH CHECKING SP_CONFIGURE FOR XP_CMDSHELL OPTION --
END
-- OBTAIN A LIST OF ALL THE DRIVES ON THE SERVER
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) + ':\*.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%'
-- *******************************************************************************************
-- 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 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
-- *******************************************************************************************
-- Step 3: CLEANUP
-- *******************************************************************************************
drop table #Files
drop table #Drives
Author Credits
This wiki article was adapted from a script by Paul Els.
Paul is a MCDBA and MCITP who worked with Microsoft SQL Server since 1995 (version 4.21). His passion is TSQL, SQL Admin and SQL Performance tuning, sharing knowledge about SQL Server with other DBAs, spending time with friends and family and he loves animals. Initially he worked as a System/Network Admin and later moved into Software Development and even did a bit of Project Management. Since 2005 he performed the role of Dev & Prod DBA at companies like Ernst & Young (3 years), SA Home Loans and he is currently 1 of 4 Prod DBAs situated at the Johannesburg Stock Exchange (JSE). He works through a consulting firm called Belay (www.belay.co.za). He also presented Schema Auditing (DDL Triggers) at Microsoft's monthly DBA meeting.
|
