Voorjaarsschoonmaak

Het is voorjaar en dus tijd voor een digitale schoonmaak! Een mooi moment om je SQL server eens na te lopen op onnodige bestanden. Een kandidaat hiervoor zijn de SQL datafiles die niet gekoppeld zijn aan een database. Een item wat vooral op ontwikkel omgevingen aan de orde is. Dit script spoort ze op.

warning.png
 Wees zeer voorzichting als je meerdere instances op een server hebt draaien; in dat geval zal dit script op instance A alle datafiles van instance B rapporteren (en omgekeerd)
De logica achter het script is vrij eenvoudig: verzamel alle SQL datafiles die op de drives staan en toon alleen die datafiles die niet in de administratie van SQL voorkomen.

Verzamel alle bestanden

Met het SQL commando xp_fixeddrives kunnen alle driveletters getoond worden. Vervolgens wordt met een cursor het DOS commando DIR uitgevoerd. Dit doen we met behulp van XP_CMDSHELL.
We zoeken alleen naar de bestanden met de extensie .mdf, .ndf en .ldf (in een keer zoeken naar *.?df lijkt een handige optie, maar geeft een lijst met bijvoorbeeld veel .pdf bestanden ...)
reddingsboek.jpg
 XP_CMDSHELL mag standaard niet uitgevoerd worden i.v.m. beveiligingsproblemen. Klik op de link aan de rechterzijde hoe je XP_CMDSHELL kunt activeren

Uitzonderingen

We willen niet de inhoud van de prullenbak zien. Ook willen we de mssqlsystemresource database niet zien in de lijst, want die verbergt SQL ook voor ons.

Het resultaat

Om het resultaat te tonen gebruiken we de functie EXCEPT. We tonen alles in de gevonden lijst BEHALVE de lijst van datafiles uit de master database. Deze laatste lijst is op te vragen met de view sys.master_files
warning.png
 Gooi de bestanden die door dit script worden opgespoord niet zomaar weg, maar ga voor ieder gevonden bestand na af deze bewaard moet worden.
set nocount on
go
declare @drive char(1)
declare @DOScmd varchar(128)
create table #drivelist(drive char(1), MBFree bigint)
create table #datafiles(fileloc varchar(256))
insert into #drivelist exec ('xp_fixeddrives')
declare c_lus cursor for
select drive from #drivelist
open c_lus
fetch next from c_lus into @drive
while @@fetch_status = 0
begin
set @DOSCmd = 'dir "' + @drive + ':\*.mdf" /s/b'
insert into #datafiles exec xp_cmdshell @DOSCmd
set @DOSCmd = 'dir "' + @drive + ':\*.ndf" /s/b'
insert into #datafiles exec xp_cmdshell @DOSCmd
set @DOSCmd = 'dir "' + @drive + ':\*.ldf" /s/b'
insert into #datafiles exec xp_cmdshell @DOSCmd
fetch next from c_lus into @drive
end
close c_lus
deallocate c_lus
delete from #datafiles where fileloc like '%\RECYCLER\%'
delete from #datafiles where fileloc like '%mssqlsystemresource%'
select fileloc from #datafiles
except
select physical_name from master.sys.master_files
order by 1
drop table #datafiles
drop table #drivelist