Introduction

So here we are again, talking about some people’s penchant for throwing out the baby with the bathwater. Gotta database to drop? No problem! Easy script. Easier to do in the GUI. Except how many people actually check to make sure nothing else is going to break before the database dies its not-so-dramatic death?

Some of those checks (as discussed in Tracking the Database’s Dependents Part 1), are fairly easy. Most people know how to search sys.objects. But there are other places to check for database references as well. Today, I’m going to show you how to check jobs, linked servers, server logins, SSIS packages, and SSIS XML config files.

The Disclaimer

I’ve said this before, but it bears mentioning again. I'm using the MSDB system database in my code for two reasons. One, everyone has MSDB on their instances. Two, I'm only doing SELECTS at this point. But, you know, dropping system databases is a really bad idea. So make sure to do a mass find-and-replace to change MSDB to the user database of your choice before running any of this yourself. Otherwise, the results you get back won't be the ones you expect.

The Quick & Dirty

The code for searching linked servers is short and sweet. It’s just a matter of grabbing the right catalog views and putting them together for context. Please note, though, that this only grabs linked server information where the catalog property actually has a value. If you have linked servers that don’t specify catalogs (the database), you’ll want to search the entire instance for other objects using the doomed databases’s name. Again, see Part 1 for details.

SELECT ss.name AS LinkedServer, ss.data_source AS DataSource, ss.catalog, sp.name AS LocalPrincipalName, sll.remote_name AS RemotePrincipalName
FROM sys.servers ss
INNER JOIN sys.linked_logins sll
ON ss.server_id = sll.server_id
LEFT OUTER JOIN sys.server_principals sp
ON sll.local_principal_id = sp.principal_id
WHERE ss.catalog = 'msdb';
--Find linked server details pointing to the database in question

In addition to linked servers, you’ll want to check the server logins to verify none of them use the database as their default database. I can’t count the number of times we’ve dropped a user database only for a login to completely stop functioning until we discover that it needs to be updated with a new default database. Fortunately, it’s only ever happened to me in a non-production environment. Be sure to fix these before dropping the database, not after, if you want everything to continue working smoothly.

SELECT @@ServerName, 'msdb' AS DatabaseName, sp.name AS ServerLogin, dp.name AS DatabaseLogin 
FROM sys.server_principals sp
INNER JOIN msdb.sys.database_principals dp
ON sp.SID = dp.SID
AND default_database_name = 'msdb';
--Find what server level logins have a catalog of the database in question

Job Hunting

Searching for jobs is harder when we’re looking in real life for a new one because we messed up our database drop. Fortunately, this isn’t that kind of a hunt. Searching MSDB for job information isn’t that difficult. The reason I don’t list this as a quick & dirty option is because there are actually two queries (one with two filters) I use to do this. The first query searches job steps text and the job step database property. The second query actually looks at jobs targeting other servers.

SELECT @@SERVERNAME, 'msdb', sj.Name, 'Job', 'Step Name - ' + sjs.step_name, sjs.Command
FROM msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE sjs.command LIKE '%msdb%' OR sjs.database_name = 'msdb';
--To discover job steps containing the database name or running against the database name

This second query is really only necessary to run on instances that do not have your target database on them. Remember, jobs have the ability to target servers other than the server they are sitting on. So use this if you have jobs someplace else (another server, another data center, etc.) that could possibly point back across the network to the database you will be deleting.

SELECT ss.name AS ServerName, sj.name AS JobName, sjs.server_id, sjs.last_run_outcome, sjs.last_run_date, sjs.last_run_time, sjs.last_run_duration, sjs.last_outcome_message 
FROM msdb..sysjobservers sjs
INNER JOIN msdb.sys.servers ss
ON sjs.server_id = ss.server_id
INNER JOIN msdb..sysjobs sj
ON sjs.job_id = sj.job_id
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE sjs.server_id <> 0 --0 is the server_id of the current server
 AND sjs.command LIKE '%msdb%'; --Do database & linked server searches here
--This query discovers jobs running against a different server

It Configures

SSIS package configurations are probably going to be the most difficult thing to query. There are so many different places to hide the configuration information. If it’s just in a SQL Server table, I’m sure there’s an easy way to query for the database. I’m not so sure on the environment variables options, though.

In my workplace, we use XML config files. While you could try to use Windows Explorer to do a general text search (this never works for me) or you could open up the files en masse in a text program to search that way (how much of your day would you like to waste on this?), I discovered a quick PowerShell script on Microsoft’s "Hey Scripting Guy" technet blog that, with a little adaption, enabled me to quick scan and output only the names of the config files that met my specific criteria.

So open up a PowerShell prompt or ISE window and copy the following. Make sure to change your path name as well as the pattern on this one. Then hit the execute button and watch the magic happen!

--Powershell script for searching config files
Select-String -Path \\MyServer\MyFolder\*.dtsConfig -Pattern msdb | group path | select name

Packages, Packages, Packages

I’ll be honest. XML is not my favorite language when I’m trying to query data. But once I figure out how to find what I’m looking for, I get really excited about the code. It took me a week and a lot of Googling to figure this one out. I initially did it for this article, but my employer actually is sunsetting a database (and related client software) in the next two years. So, bonus, I get to use this for real as we ramp up our investigation of all the system’s touchpoints!

Now fair is fair. For all that this code is altered, I got the base idea (and most of the code) from Arvind Shyamsundar who kindly allowed me to use it for this article. You can see more about what I was reading by checking his specific article here. And while I was not able to track down dev_etter from Stack Exchange, DE’s response to someone else’s question helped me put my own problems in perspective and eventually come across a solution.

First let me share a bit of code that allowed me to find my SSIS package XML hierarchy. If you don’t know the hierarchy, run this first and click on the XML link in the final column. It will open up your SSIS package in the SSMS GUI and you can navigate around to see the various nodes, subnodes, etc. You can even do a FIND on this to locate a specific keyword. But I don’t recommend using this code to read all your packages. It will take too much time.

SELECT ssp.name AS PackageName,
ssp.createdate AS PackageCreated,
sspf.foldername AS PackageFolder,
CAST(CAST(ssp.[packagedata] AS VARBINARY(MAX)) AS XML) AS PackageDataXml
  FROM msdb.dbo.sysssispackages ssp --Grab base package info
  JOIN msdb.dbo.sysssispackagefolders sspf --Grab package folder info
   on ssp.folderid = sspf.folderid 

Even though Microsoft is phasing out the image data type, in SQL 2012 column packagedata still uses the image data type. Before the data can actually be read in XML format, it needs to be cast as VARBINARY then recast as XML. Hence the double CAST here.

Below is the code for searching SSIS connection managers. Yes, you could search the configuration data. In fact, we just did. But what do you do when a package has no configuration file or table or environment variable? Also, packages, when they are created, store their own object information inside the packages. So a connection manager might point to a development instance that went away in the last upgrade, but no one noticed because the config file is pointing it to a production instance. If the package losses its connection to the config file, this happens, suddenly it’s trying to use old instance data to run against.

So, yeah. Check your packages as well. Don’t rely on the configuration data to be the only answer to your question.

Now, if you read the code below closely, you might notice something. The code above? It’s the subquery that my XML code is built off of, which makes it a great code section to kick off any and all troubleshooting efforts.

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS) --, 'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask)
SELECT 
  PackageName, PackageCreated, PackageFolder,
  PkgData.PkgDetails.value('@DTS:ObjectName','VARCHAR(1000)') AS ConnMgrName,
  PkgData.PkgDetails.value('@DTS:CreationName[1]','VARCHAR(5)') AS ConnMgrType,
  ConnMgr.ObjData.value ('@DTS:ConnectionString[1]', 'VARCHAR(1000)') AS ConnectionStr,
  PackageDataXml
FROM 
( SELECT ssp.name AS PackageName,
ssp.createdate AS PackageCreated,
sspf.foldername AS PackageFolder,
CAST(CAST(ssp.[packagedata] AS VARBINARY(MAX)) AS XML) AS PackageDataXml
  FROM msdb.dbo.sysssispackages ssp --Grab base package info
  JOIN msdb.dbo.sysssispackagefolders sspf --Grab package folder info
   on ssp.folderid = sspf.folderid 
  ) AS SSISPackages
CROSS APPLY SSISPackages.PackageDataXml.nodes('/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager') PkgData(PkgDetails)
  --Cross Apply against the ConnectionManager nodes to get base package info
CROSS APPLY PkgData.PkgDetails.nodes('./DTS:ObjectData/DTS:ConnectionManager') ConnMgr(ObjData)
  --Cross Apply further down the nodes to get ConnMgr's ConnectionString info
WHERE PkgData.PkgDetails.value('@DTS:CreationName[1]','VARCHAR(5)')='OLEDB'
 AND ConnMgr.ObjData.value ('@DTS:ConnectionString[1]', 'VARCHAR(1000)') LIKE '%MSDB%' --Change this to your database name
ORDER BY PackageName, PkgData.PkgDetails.value('@DTS:ObjectName','VARCHAR(1000)');
/*NOTE: Click on the link in the PackageDataXml column to see the package XML
   You may have to update the cross applied nodes location depending on
  whether or not your connection manager actually follows the same nested node arrange. */

Connection manager info is not the only thing to check for in SSIS, though. Script tasks and Execute T-SQL tasks should be reviewed as well. By tweaking the node structure a little, we can easily access that information too.

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
SELECT 
  PackageName, PackageCreated, PackageFolder,
  PkgData.PkgDetails.value('@DTS:ObjectName','VARCHAR(1000)') AS TaskName,
  PkgData.PkgDetails.value('@DTS:ExecutableType','VARCHAR(3000)') AS TaskType,
  ExSQLTask.ObjData.value ('@SQLTask:SqlStatementSource', 'VARCHAR(1000)') AS SQLSource,
  PackageDataXml
FROM 
( SELECT ssp.name AS PackageName,
ssp.createdate AS PackageCreated,
sspf.foldername AS PackageFolder,
CAST(CAST(ssp.[packagedata] AS VARBINARY(MAX)) AS XML) AS PackageDataXml
  FROM msdb.dbo.sysssispackages ssp --Grab base package info
  JOIN msdb.dbo.sysssispackagefolders sspf --Grab package folder info
   on ssp.folderid = sspf.folderid 
  ) AS SSISPackages
CROSS APPLY SSISPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable/DTS:Executables/DTS:Executable') PkgData(PkgDetails)
  --Cross Apply against the ConnectionManager nodes to get base package info
CROSS APPLY PkgData.PkgDetails.nodes('./DTS:ObjectData/SQLTask:SqlTaskData') ExSQLTask(ObjData)
  --Cross Apply further down the nodes to get task’s text info info
WHERE (PkgData.PkgDetails.value('@DTS:ExecutableType', 'VARCHAR(200)') LIKE 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask%'
  OR PkgData.PkgDetails.value('@DTS:ExecutableType', 'VARCHAR(200)') LIKE 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask%')
AND ExSQLTask.ObjData.value ('@SQLTask:SqlStatementSource', 'VARCHAR(1000)') LIKE '%MSDB%' --Change this to your database name
ORDER BY PackageName, PkgData.PkgDetails.value('@DTS:ObjectName','VARCHAR(1000)');

This is really as far as I’ve gone with reading my SSIS packages, because we really don’t use a lot of package objects that aren’t directly tied to these objects. You can tweak this code to read package variables or other objects, including custom objects, with just a little work. As I said above, it’s a matter of knowing what the node hierarchy is and where your objects are hiding in it.

SSRS

Lest we forget, reports also have queries and data sources pointing to different databases. There are a variety of ways to search reports for the database connections and several people have already blogged about the issue. Since I don’t have much to add or change about that previously-posted code, I’m just going to point out the URLs for your own investigations.

Mark Vaillancourt has a short and sweet bit of T-SQL that can list out everything in the Report Server catalog here: http://markvsql.com/2009/11/find-all-deployed-ssrs-reports-that-reference-a-particular-field/. Just change the WHERE clause filter from “” to “” and you’re all set. I do actually recommend adding left side of the WHERE clause filter to the SELECT list, but that’s because I like seeing more information in my results than just a report name.

MVP Olaf Helper has another bit of code that actually reads the data sources. Just uncomment out the WHERE clause and change “TFS” to your database name, and you’re all set.

If you want to get fancy and have report (or reports) that show you all the individual queries and stats, try checking out this old SSC chesnut by Michael Davis. This has to be one of the coolest things I’ve seen. Create an SSRS solution in Visual Studio, then create a new Shared DataSource called ds_ ReportServer (call it the same thing on both levels, name and data source) that points to your ReportServer database, then import all the reports attached to the article.

Be careful on the diagnostic reports, though. The ReportQueries report doesn’t work if you have illegal xml characters in your reports. The error I get is “Msg 9420, Level 16, State 1, Line 4 XML parsing: line 101, character 10, illegal xml character” and I haven’t figured out which report is causing it or why. But many of the other reports work just fine. I think I’ll keep this one.

Lastly, if you want to go complicated, Bret Stateham has a nice article about querying report XML at http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/. Again, if you have illegal xml characters in your report (like spaces), it’ll give you the above error message. I did, however, get a nice result set despite that error. And when I added “AND CONVERT(varbinary(max),Content) LIKE '%msdb%'“ in the first CTE right after the Type filter, it worked like a charm. Apparently my illegal character problem doesn’t exist in any reports that reference that specific database.

SSAS

Ah, Analysis Services. What wouldn’t I give for a good solution to the problem of querying the project data sources. Unfortunately, I cannot seem to locate a good way to mass-query SSAS. The sources are viewable as XML within Visual Studio, but alas, locating where the information is stored inside of SQL Server itself has proven itself an impossible task for me, especially as my department doesn’t actually use SSAS.

So, please, if you have a solution to this conundrum, let everyone know. I invite you to write a part three to this series around SSAS. Or post the script to SSC, let me know, and I’ll link to the script right here in this article.

The Miscellaneous

There are a few other things to keep in mind when searching for dependencies. Not everything is in SQL or easily searchable by a DBA with limited access. You may need to draft assistance to help search instances, client code bases, FTP/SFTP scripts and anything else your company uses that you don’t have access to.

In my workplace, the developers have their own locations (usually on an app server or a web server) where they store .dlls and .config files. Those files could have references to the database in question or, worse, the devs could have hardcoded the references right into their client or webpage applications. Get someone to take responsibility for searching all that .Net code, PowerShell scripts, etc. for these references.

Don’t forget to search other database systems you might have—Access, Oracle, Excel macros—which might have an external hook (ODBC) into your SQL database. The last thing anyone wants is the boss on high to come thundering down because those Oracle reports aren’t working because they got a chunk of data from the “missing” database.

This code is multi-purpose! With a little tweaking, you can use it to search for specific table, view, column, or other object references. YAY!

Another note from my very helpful betas… All of the code I have provided has the potential to throw out false positives. If you document as much as I do (and I comment everything), you’ll find yourself pulling up procs with comments that refer to the database name but have no actual database connection. If your database name search is a string of characters that can be found in everyday words (such as DB name “aract,” which can be found in the word “character”) or SQL Server keywords, then you’re going to get a lot of noise in your results. There may be additional filtering after the initial searches are done, examining each object carefully then including or excluding it from your “affected objects” list.

There is one piece of advice I cannot stress enough. This is a project worth doing thoroughly and carefully. Don’t get distracted, don’t skim the information. Look through each and every result to ensure every “breakpoint” is found. It will save a lot of heartache on or after Database Funeral Day.

Conclusion

When preparing for the death of a database, there are a lot of places to look for references so something vital doesn’t get orphaned. There’s no need to panic, though. Just make a list of the items in your environment that could possibly be affected, refer to the code in parts 1 and 2 of this article, then run through the list one at a time. Once the dependents are verified, remediation can be done, the database can be dropped and, if anyone had a personal connection to the dearly departed DB, a proper wake and will-reading can occur.

For the record, cake, soda, and music go a long way toward soothing away the tragedy of a database taken away from its dependents.

Author’s Notes: First, I need (and want) to thank to all my beta readers: Thom A, Hugo Kornelis, Lynn Pettis, and Sean Lange. Their input made these articles much better than originally written, even if I didn’t take every suggestion offered. That said, all mistakes in this article are mine and mine alone.

Second, I work in a case-insensitive environment and tend to code that way. My beta-readers caught that problem, so I re-wrote everything for case-sensitive databases. If I missed something, I apologize for any headaches it may cause.