Sql-server – How to determine if a SQL Server ldf log file is in use on a server

sql server

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?

Best Answer

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

Related Topic