1. The files waste disk space
2. If you restore the database back to the original place, the restore will be failed because of the existing files.
so here is a powershell script which can find out orphan database files on disk. the script can
1. Get the database default file path from registry
2. Search all mdf, ndf and ldf files
3. Check the sql server system table to see if all files are attached.
4. Print out the file name if the file is not attached to any database.
you can download the script from here
https://docs.google.com/file/d/0B4Xde9z-OME1WWozY0hyWVpnTkU/edit?usp=sharing
and run the script as below:
here, 2 orphan db files(my.mdf and my.ldf) are found in instance sqlexpress.
Excellent Work!!
ReplyDeleteWhile running your script, I'm ended up by getting this error.
PS F:\PowerSQL> .\CheckOrphan.PS1 ddesdp01
Start Checking server ddesdp01
Unable to index into an object of type System.Management.Automation.PSObject.
At F:\PowerSQL\CheckOrphan.PS1:175 char:21
+ $DefaultFile=$rows[ <<<< 0].DefaultFile
+ CategoryInfo : InvalidOperation: (0:Int32) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : CannotIndex
Can you please check your script once?
Thanks for sharing.
looks like the getting default sql file path is failed.
Deletecould you please try to run the sql below on your sql instance,
---------------------
declare @Arg sysname
declare @Param sysname
declare @MasterPath nvarchar(512)
declare @LogPath nvarchar(512)
declare @n int
select @n=0
select @Param='dummy'
while(not @Param is null)
begin
select @Param=null
select @Arg='SqlArg'+convert(nvarchar,@n)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters', @Arg, @Param OUTPUT
if(@Param like '-d%')
begin
select @Param=substring(@Param, 3, 255)
select @MasterPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
else if(@Param like '-l%')
begin
select @Param=substring(@Param, 3, 255)
select @LogPath=substring(@Param, 1, len(@Param) - charindex('\', reverse(@Param)))
end
select @n=@n+1
end
declare @SmoRoot nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'SQLPath', @SmoRoot OUTPUT
declare @SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @SmoDefaultFile OUTPUT
declare @SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @SmoDefaultLog OUTPUT
SELECT
ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],
ISNULL(@SmoDefaultLog,N'') AS [DefaultLog],
@LogPath AS [MasterDBLogPath],
@MasterPath AS [MasterDBPath]
-------------------------
what is the return?
DefaultFile ''
ReplyDeleteDefaultLog - G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data
MasterDBLogPath - F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
MasterDBPat-
F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
sorry for the delay, I already made a change in the code on 12th, but forgot reply you. I am so sorry about it. please try to download the new code from the link in the original post.
Delete