DBCC shrinkfile error - Msg 8985

  • 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

     

  • 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

  • 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

  • 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

  • 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

  • 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

  • Thanks once again for your help.

    What is 128?

    I have already run following statement:

    backup log wslogdb50 with truncate_only

    Regards

    Chandu

  • 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

  • Excellent !! It worked.

    Thanks very much for your help.

    Regards

    Chandu

     

  • 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.

  • 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

  • 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

  • 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

  • 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
  • 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