Prashanth Jayaram

Multi-server Script to Find Orphaned Data Files using PowerShell

August 23, 2017 by

Having worked in busy dev-test environments, it isn’t uncommon to come across situations where someone detached a database from an SQL server, but forgot to reattach it, or drop it from the server. The next task we are then required to undertake, upon coming across such a situation, is to clean up the traces. This task can be complicated if the detached databases get deregistered from the SQL Server metadata catalog.

Of course, there’s a way to track these using T-SQL. All we need to do is enable extended stored procedure (XP) and list all the files, and then compare them with the database files. Handling files using T-SQL is not the ideal approach, though. Also, running multi-database scripts can be cumbersome using T-SQL. So, how about using a PowerShell script to find out the detached databases on the local or remote drives of any database instance of any server?

Introduction

In this article, we take the sledgehammer approach in searching for MDFs and then comparing them against files which are available on the listed drive. The idea behind this is to generate a report of detached database files across multiple servers. That’s one way to keep our databases compliant in terms of meeting SLAs, and ensuring optimal performance. Running such reports periodically helps us to achieve compliance in terms of file auditing.

At times, finding files seems like a necessary evil for IT pros; like finding a needing in a haystack. Imagine tens or hundreds of computers being the haystack! Is Get-ChildItem a good option? Sure, it works, but it could be considerably resource-intensive when working on an entire hard drive.

Okay, so, the other way to go about it is to use the CIM_Datafile class of the WMI. This can be done using PowerShell 3.0 or higher. And as far as I know, WMI has the files registered as well. We would have to go about querying all the instances of the CIM_Datafile class. But it is as time-consuming as Get-ChildItem. If used wisely, though—like being specific with the query, with respect to the properties you look for—it can become an efficient and effective solution.

Let’s now look at the highlights of the script we would write to handle the task of finding such detached files:

  • Works across multiple servers
  • Finds any detached file across multiple all instances on each server
  • Customized code to scan the drives
  • Use of credentials
  • Detailing the file details such as path, name and size
  • And more…

Getting started

Step 1: Input parameter declaration

The parameters Inputfile, logfile, filtering parameter file-extension are defined and declared. The path of the log file is verified and recreated, if it exists.

The input file should contain the list of the SQL instances which we’d like the report for. For example, the input file should look something like this:

SQL1
SQL2
SQL3

The following PowerShell script takes a text file with server names as input, and iterates through the list. It first tests of the server is reachable, and then lists out the active database files. The script also logs its progress in a separate log file. Database file extension and drive letters are used for filtering.

The script takes in the username and the password, and stores these values in $Credentials. This variable is then passed as a parameter for Get-WmiObject.


Step 2: Loading SMO libraries

If you know the full assembly name, use the. Load() method.

PS:>[reflection.assembly]::Load(“Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”)

or

PS:>Add-Type -Assembly “Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91”

Note: The LoadWithPartialName has been deprecated. The recommended solution now is to use the Add-Type or load a specific library.

Step 3: Looping through each server to list active data files

For each server, the connection is opened, and all the active data files are retrieved. The result set is prepared for step 4.

Step 4: List and compare the mdf files

This steps list the mdf files from the listed drives. The generated files are compared with the active data files.

It helps if we customize the drives. This way we can significantly reduce the traversing time of the query.

Step 5: Write the output to a log file and to the console

The complete PowerShell script is given in Appendix A.

Demonstration

For the purpose of demonstration, we’ve created a new database called sqlShackDemo on two servers. After creating the databases, we’ll run a series of SQL statements to get the file locations, and detach the databases. SQLCMD is used to perform series of SQL operations.

The output shows that the database SQLShackDemo is created. The sp_helpdb system stored procedure is used to generate the metadata for the database. sp_detachdb is used to detach the database from database engine.

Input file

The input file should have the names of the two database instances.

There are, in fact, two ways to run the script:

  1. Modify the script, and save the file
  2. Create a function and manually call the function by passing the required arguments

The output shows the detached files from the system.

Conclusion

Orphaned database files eat up a lot of space in our environments.. Finding those file can be labor intensive. In this article, we discussed the different ways to find and handle such orphaned data files. One of the ways we’ve focused on is using a multi-server PowerShell script, which is a very efficient way of finding out the unattended files from the entire environment. Periodic generation of such detached reports comes in handy when cleaning up the system. This way, we can prevent many space-related problems on the servers. The script and the report it generates also helps us keep an eye on the detached files on each of the database instances we list in the input file, thereby ensuring that we’re compliant in terms of resource utilization, apart from maintaining space effectively.

See more

Consider these free tools for SQL Server that improve database developer productivity.

References

Appendix A


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram
SQL Database development

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram

378 Views