Tuesday, July 9, 2013

Find out orphan database file on disk

Sometimes you detach databases,  or you delete a database which is in restoring status,  the data and log file will not be removed from local disk. if you forget the files,  It might cause issues
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.





5 comments:

  1. Excellent Work!!
    While 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.

    ReplyDelete
    Replies
    1. looks like the getting default sql file path is failed.

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

      Delete
  2. DefaultFile ''
    DefaultLog - 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

    ReplyDelete
    Replies
    1. 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
  3. Good Blog, well descrided, Thanks for sharing this information.
    Big Data and Hadoop Online Training

    ReplyDelete