Find log files not associated DBs


  • Hi I am on sharepoint DBs server and found bunch of DBs sitting on the instance. When I check Log file folder found timestamp was few year back. I need to clarify old time stamp associated  log files are not in use or still associate active DBs.

    I check with

    use master
    SELECT * FROM SYSALTFILES where filename like '%SP%'

    How do I 100% ensure log file not in use or not associated with any DBs before physically delete

    many thanks

    • Edited by ashwan Wednesday, July 4, 2018 1:29 PM
    Wednesday, July 4, 2018 1:28 PM

All replies

  • Did you check against sys.database_files?


    SELECT name, physical_name, type_desc
    FROM   sys.database_files

    to see if files are listed against any db

    This needs to be iterated for each db

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Edited by Visakh16MVP Wednesday, July 4, 2018 1:36 PM
    Wednesday, July 4, 2018 1:36 PM

    That's an old system view and not very reliable, don't use it. Use this query to get all registered log files

    select *
    from sys.master_files
    where type = 1

    P.S.: All online log files are in use and can't be changed; when you can rename a log file, then it is not in use.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 4, 2018 1:50 PM

  • How do I 100% ensure log file not in use or not associated with any DBs before physically delete

    I see two questions.

    1) Do you want to know if the LDF files that are out there belong to any active DBs that are sitting on the instance? If this is what you want, then a quick dirty way is to try renaming the files and if they're being used by an active DB, they fail to change their names. 

    2) Do you want to know if a DB is in use or not because the "last modified" timestamp in windows explorer showed an old date? If so, then don't rely on the windows timestamp as a basis for deciding if a DB is in use or not. The windows timestamp changes as a result of certain events (such as DB is closed, auto-growth etc) and not when data is inserted or updated frequently. SQL Server and windows explorer timestamp have no direct relation. 

    That said since the timestamp is pretty old, I would think the DB is not in use but again, a much better way would be to set up some sort of auditing to be 100% sure that the DBs are not in use.

    Hope this helps!

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Wednesday, July 4, 2018 2:06 PM
  • IF the files are not present on sys.master_files of this instance and in any other instance than these files are not currently associated with any database hosted on SQL Server instances.

    If you decide to delete these files, before you do it, I strongly recommend attaching them and taking a FULL backup.

    Wednesday, July 4, 2018 3:34 PM