none
Database Shrink Error: 8985

    Question

  • Hi All,

    Version: Microsoft SQL Server 2008 (SP3) - Enterprise Edition.

    While performing the DB Shrink, I get the following error on both the editions Enterprise and Developer. Recently I have upgraded the Service pack from SP2 to SP3. From then this issue has started.

    I have verified the log logical name and physical path which is available and its correct.

    The Command that I use to Shrink is below:

    USE XXX
    GO
    DBCC SHRINKFILE (XXX_Log, 0);
    GO

    Error Message: Could not locate file 'XXX_Log' for database 'XXX' in sys.database_files. The file either does not exist, or was dropped. [SQLSTATE 42000] (Error 8985).  The step failed.

    Please suggest or help me to sort out this issue.

    Monday, July 08, 2013 5:57 AM

All replies

  • Hi All,

    Version: Microsoft SQL Server 2008 (SP3) - Enterprise Edition.

    While performing the DB Shrink, I get the following error on both the editions Enterprise and Developer. Recently I have upgraded the Service pack from SP2 to SP3. From then this issue has started.

    I have verified the log logical name and physical path which is available and its correct.

    The Command that I use to Shrink is below:

    USE XXX
    GO
    DBCC SHRINKFILE (XXX_Log, 0);
    GO

    Error Message: Could not locate file 'XXX_Log' for database 'XXX' in sys.database_files. The file either does not exist, or was dropped. [SQLSTATE 42000] (Error 8985).  The step failed.

    Please suggest or help me to sort out this issue.

    Firstly, make 100% sure that the log file name is correct. Once you have confirmed that then run a DBCC Consistency check against your database and then re-run the shrink command.

    I've had the exact same issue, after running a full DBCC against the database, the problem was resolved.

    • Marked as answer by MunnaAltaf Monday, July 08, 2013 7:40 AM
    • Unmarked as answer by MunnaAltaf Wednesday, July 10, 2013 4:49 AM
    Monday, July 08, 2013 6:18 AM
  • can you please scan the errorlogs & eventlogs did you see any issues with your Drives where the DB log files resides?.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, July 08, 2013 6:20 AM
  • Thank you for your fix. Now the job is successful. Can you please explain why we need to use DBCC CheckDB command before performing the Shrink activity. Is this activity need to be perform on regular basis or its once to fix this type of issue.

    Any how, I'll monitor the job for couple of days and get back to you for more clarifications.

    Regards,

    MunnaAltaf

    Monday, July 08, 2013 7:03 AM
  • Thank you for your fix. Now the job is successful. Can you please explain why we need to use DBCC CheckDB command before performing the Shrink activity. Is this activity need to be perform on regular basis or its once to fix this type of issue.

    Any how, I'll monitor the job for couple of days and get back to you for more clarifications.

    Regards,

    MunnaAltaf

    Hi Munna

    I've asked the same question before and have yet to get to the bottom of it.

    However, I haven't seen the same issue on SQL 2008 R2.

    Please mark reply as answer

    Monday, July 08, 2013 7:25 AM
  • Usually its good practise to run CHECKDB after upgrading of sp's as it updated to latest one. for shrinking and checkDB I dont think so relationship.

    until if everything goes smoothly.

    Also 2008 has some issues in it.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    • Edited by Rama Udaya Monday, July 08, 2013 7:48 AM updated
    Monday, July 08, 2013 7:48 AM
  • Hi,

    This is not fixed the issue, still the issue persist. Please give an alternate suggestion.

    Regards,

    MunnaAltaf

    Tuesday, July 09, 2013 11:18 AM
  • Hi,

    This is not fixed the issue, still the issue persist. Please give an alternate suggestion.

    Regards,

    MunnaAltaf

    Tuesday, July 09, 2013 11:39 AM
  • This fixed the issue...thanks
    Wednesday, March 19, 2014 11:28 PM