Unable to shrink transaction log - could not locate files

  • Hello folks,

    OK so I have done as much digging as I can on the net and haven't found a solution to this problem so I thought it was time for a post.

    I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:

    use test

    DBCC SHRINKFILE(test_log, TRUNCATEONLY)

    --or

    use

    DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)

    I get the following message:

    Msg 8985, Level 16, State 1, Line 1

    Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.

    I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.

    I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.

    I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.

    Any ideas/suggestions would be much appreciated.

    Thanks

    Jess

  • Verify the logical file names are what you expect them to be.

    USE <your_db_name>

    EXEC sp_helpfile

    --Or: EXEC <your_db_name>.dbo.sp_helpfile

    The first column will show the logical file name, which, of course, must match what you put in the SHRINKFILE command.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Name in the first column in 'test_log'

  • Ok.

    See what is in the sys.master_files "table" for that db:

    SELECT *

    FROM sys.master_files

    WHERE

    database_id = DB_ID(N'test')

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Can you give us the result you have when executing :

    SELECT * FROM test.dbo.sysfiles

  • Given that this is SQL 2008 (or SQL 2005 at least), you mean:

    SELECT *

    FROM test.sys.database_files

    right? 🙂

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Running SELECT * FROM test.dbo.sysfiles I got the following

    [/code

    fileidgroupidsizemaxsizegrowthstatusperfnamefilename

    116705152-1020test_dataJ:\SQLdata\test_data.mdf

    201280415-164000660test_logH:\SQLLog\test_log.ldf

    32823280-16400020test_indexG:\SQLIndex\test_index.mdf

    411363672-16400020test_data1P:\SQLData\test_data1.ndf

  • jpomfret7 (1/10/2012)


    Running SELECT * FROM test.dbo.sysfiles I got the following

    [/code

    fileidgroupidsizemaxsizegrowthstatusperfnamefilename

    116705152-1020test_dataJ:\SQLdata\test_data.mdf

    201280415-164000660test_logH:\SQLLog\test_log.ldf

    32823280-16400020test_indexG:\SQLIndex\test_index.mdf

    411363672-16400020test_data1P:\SQLData\test_data1.ndf

    And does that drive letter and file path still exist? I have to ask... 🙂

    Jared
    CE - Microsoft

  • Running SELECT * FROM test.dbo.sysfiles I got the following

    To be safe, you really should run against test.sys.database_files, since that is what SQL is actually looking at ... notice the error message that you got originally.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Yes I can physically see the file there

  • what is the recovery model of your database?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • ScottPletcher (1/10/2012)


    Given that this is SQL 2008 (or SQL 2005 at least), you mean:

    SELECT *

    FROM test.sys.database_files

    right? 🙂

    Yes of course 🙂

  • I would not expect it to give the error you see, but anyway the truncateonly option has no effect when applied to a log shrink

    ---------------------------------------------------------------------

  • when the database is in full recovery model and you delete a log file, it will still list in sys.database_files and sys.master_files.

    You must take a transaction log backup and the file will be removed from the catalogs mentioned above.

    For databases in simple recovery the file will go immediately!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • To be safe, you really should run against test.sys.database_files

    The file name and info matches in sys.database_files, sys.master_files, sys.sysaltfiles and dbo.sysfiles...

    what is the recovery model of your database?

    We are in simple recovery and I ran a checkpoint before attempting to shrink the logs

    I would not expect it to give the error you see, but anyway the truncateonly option has no effect when applied to a log shrink

    I have used this script on other servers and been able to free up log space, what should be used?

    It's a puzzle, thanks for all your help so far 🙂

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply