How can I determine if a SQL Server ldf log file is in use on a server?

Question

I have a SQL Server ldf file that is a very large 190 gigabytes in size. I am pretty sure that it is just a remnant from an old SharePoint content database that is no longer on this database server, but I'd like to be absolutely sure before I go mucking with it. My server has several dozens of databases on it. How can I most easily verify whether this ldf file is somehow in use on this server? Am I relegated to using SSMS and manually checking each database?

Answer 1

Well, the non-recommended way would be to attempt to rename the .ldf file on the file system. If the log file is indeed being used to serve a currently operational SQL Server database then you will not be able to rename the file.

The recommended method would be to inspect the sys.database_files catalog view in order to identify/locate the log file in question.

Something along the following lines should do it.....

Select 
    file_id,
    name,
    state
From sys.database_files
Where Type = 1 and name=’logFileName’

See the following books online reference for details on the sys.database_files catalog view :

http://msdn.microsoft.com/en-us/library/ms174397.aspx

Answer 2

Here's a quick and dirty script that will loop through all of your databases and look for your file. Just replace the value of @FileToFind with the name of the file in question. If you get no results in the final resultset then that server's not using your file. If you get a result back from this then that's the database that is using the file.

DECLARE @CurrentDB sysname
DECLARE @SQL NVARCHAR(1000)
DECLARE @FileToFind NVARCHAR(260)

SET @FileToFind='master.mdf'

CREATE TABLE #TmpDBTable (
    DBName sysname,
    FileName NVARCHAR(260)
)

DECLARE ALLDBS CURSOR FOR
SELECT NAME FROM sys.databases
OPEN ALLDBS
FETCH NEXT FROM ALLDBS INTO @CurrentDB 
WHILE @@FETCH_STATUS=0
BEGIN
    SET @SQL='use ' + @CurrentDB + ';insert #TmpDBTable select '''+@CurrentDB+''',physical_name from sys.database_files where physical_name like ''%'+@FileToFind+'%'''
    EXEC sp_executesql @SQL
    FETCH NEXT FROM ALLDBS INTO @CurrentDB 
END
CLOSE ALLDBS
DEALLOCATE ALLDBS

SELECT * FROM #TmpDBTable

DROP TABLE #TmpDBTable

Answer 3

If you believe it's an inactive database, should the time stamp (that explorer puts on it) on said log file be out dated? Or is that grossly wrong information? I am an accidental dba, fyi