Post reply

DBCC shrinkfile error - Msg 8985

  • April 14, 2004 at 8:26 am

    #66360

    Hi

    I am getting following error when I try to shrink the transaction log:

    DBCC SHRINKFILE (wslogdb50_Log, 2000)

    Server: Msg 8985, Level 16, State 1, Line 1

    Could not locate file 'wslogdb50_Log' in sysfiles.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Any ideas?

    Regards

    Chandu

     

  • April 14, 2004 at 8:46 am

    #502746

    Check that you are using the database that you wish to shrink the file of.

    What's the output from SELECT NAME FROM SYSFILES ?


    Shamless self promotion - read my blog http://sirsql.net

  • April 14, 2004 at 8:56 am

    #502751

    Hi

    Thanks for the prompt response.

    I get following output for SELECT NAME FROM SYSFILES:

    wslogdb50

    wslogdb50_Log.LDF

    I am getting "Incorrect Syntax error" when  i include .ldf in the DBCC shrinkfile command.

    Regards

    Chandu

  • April 14, 2004 at 9:07 am

    #502752

    Try shrinking it using the fileid (SELECT NAME, FILEID FROM SYSFILES), use the same syntax, just replace the filename with the fileid.


    Shamless self promotion - read my blog http://sirsql.net

  • April 14, 2004 at 9:17 am

    #502755

    Hi

    The statement seems to have completed without any error. But, I am getting following message:

    Cannot shrink log file 2 (wslogdb50_Log.ldf) because all logical log files are in use.

    (1 row(s) affected)

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Current Size 1257512 Used pages 1257512

    The log file increased to 10GB. I need to reduce it ASAP as we are running out of space. Any other alternative? Starting and stopping sql server?

    Regards

    Chandu

  • April 14, 2004 at 9:47 am

    #502768

    Run the following, replace DBNAME with your database name and FILEID with the file id.

    BACKUP LOG DBNAME WITH NO_LOG

    GO

    USE DBNAME

    GO

    DBCC SHRINKFILE (FILEID, 128, TRUNCATEONLY)

    GO


    Shamless self promotion - read my blog http://sirsql.net

  • April 14, 2004 at 9:53 am

    #502770

    Thanks once again for your help.

    What is 128?

    I have already run following statement:

    backup log wslogdb50 with truncate_only

    Regards

    Chandu

  • April 14, 2004 at 10:12 am

    #502775

    128 is the number of pages to shrink the file to. I usually use that if I want to shrink the file but still leave some space in it.


    Shamless self promotion - read my blog http://sirsql.net

  • April 14, 2004 at 10:27 am

    #502781

    Excellent !! It worked.

    Thanks very much for your help.

    Regards

    Chandu

     

  • July 30, 2007 at 10:10 am

    #723352

    HI Nicholas,

    I am facing the same problem.

    I am unable to understand the what your talking about.

    Try shrinking it using the fileid (SELECT NAME, FILEID FROM SYSFILES), use the same syntax, just replace the filename with the fileid.

    Please let me know how can i reduce the file size of the database log.

    Thanks

    Bapi.

  • July 30, 2007 at 10:35 am

    #723356

    Bapi run

    SELECT NAME, FILEID FROM SYSFILES

    to get the fileID of the log file, then use that in the DBCC SHRINKFILE script


    Shamless self promotion - read my blog http://sirsql.net

  • July 30, 2007 at 10:35 am

    #723355

    Chandu and Bapi, now that you fellas have the transaction log shrunk, you may want to start asking yourselves some more questions...

    • Why was the log file so big in the first place?
    • Do I have the correct recovery model? (Full, bulk-logged, simple)
    • Are transaction log backups being taken?... how often?...

    Books online should be a great resource for you on disaster recovery and transaction log management if you don't have much experience in these areas.

    Regards,
    Rubes

  • October 22, 2012 at 9:03 am

    #1551417

    Hi Chandu,

    How to check/know the file ID. is it the same as SPID? i do have the same error..please help.

    thanks

    anthony

  • October 22, 2012 at 9:16 am

    #1551433

    Please note: 8 year old thread. Chandu has not logged in in 7 years.

    Please don't post the same problem in multiple places.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 24, 2013 at 5:05 am

    #1618175

    1)SELECT name, size

    FROM sys.master_files

    WHERE database_id = DB_ID(N'tempdb');

    2)Try Make database recovery mode to Simple.

    3) Right Click Datbase =>Tasks =>Shrinks =>Files => Log => select shrink action => Realease unused space.

    select shrink action => select in MB

    4) SELECT name, size

    FROM sys.master_files

    WHERE database_id = DB_ID(N'tempdb');

    check size...

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

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