Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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

share|improve this answer

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
share|improve this answer

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

share|improve this answer
    
It would be a little flaky in that to my knowledge the time stamp (modified date) will contain the details of either the last time that the physical file was either created or increased in size or was initially accessed by SQL Server. The file system does not differentiate further writes from the SQL Server Service once the initial access has been granted. So a log file could potentially still be in use even with a modified date that is several months old, provided that the SQL Server Service has remained up during this time and the database is online. –  John Sansom Aug 17 '09 at 20:53
    
I never go by that field for anything... Way too many things can touch it unrelated to the task at hand. –  squillman Aug 17 '09 at 20:56
    
@John, thanks for the info! –  RateControl Aug 18 '09 at 12:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.