Could not locate file 'SAML' for database 'SAML' in sys.database_files

  • Good Morning -

    I know this topic has been beaten to death over the years but I can't shrink a large log file.

    I have run this:

    USE SAML;

    GO

    DBCC ShrinkFile(SAML_log, 3000)

    It returned: Msg 8985, Level 16, State 1, Line 1

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

    I ran: select [name], type_desc, Physical_name from sys.master_files where database_id = DB_ID('SAML')

    It returned: SPGSAMLLog.ldf

    I ran: SELECT df.name FROM sys.database_files AS df WHERE df.type_desc = 'log'

    It returned: SPGSAMLLog.ldf

    Logical name is SPGSAMLLog.ldf - correct?

    I ran:

    I have run this:

    USE SAML;

    GO

    DBCC ShrinkFile(SPGSAMLLog_log, 3000)

    It returned: It returned: Msg 8985, Level 16, State 1, Line 1

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

    I tried many different variations of 'SPGSAMLLog' with the same results.

    What am I doing wrong?

    Thanks for any help.

  • No, that's the physical name of the file. You need the logical name for shrink. It's the name column in sys.database_files.

    p.s. Why are you shrinking the log?

    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
  • Thanks for responding. I'm back on this...

    I ran: SELECT [name] AS logical_file FROM sys.database_files df

    It returned:

    logical_file

    SPGSAMLDat.mdf

    SPGSAMLLog.ldf

    USE SAML;

    GO

    DBCC ShrinkFile(SPGSAMLLog_log, 3000)

    Msg 8985, Level 16, State 1, Line 1

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

  • Are you sure that query returned the logical name, not the physical? If so, then you need to use what it returned (ie 'SPGSAMLLog.ldf') in the shrink command.

    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
  • ran this: SELECT name FROM sys.master_files WHERE database_id = db_id() AND type = 1

    it returned: SPGSAMLLog.ldf

    tried:

    USE SAML;

    GO

    DBCC ShrinkFile(SPGSAMLLog.ldf, 3000)

    it returned:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '.'.

  • Use quotes.

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

    USE SAML;

    GO

    DBCC ShrinkFile("SPGSAMLLog.ldf", 3000)

    returned:

    Msg 8985, Level 16, State 1, Line 1

    Could not locate file 'SPGSAMLLog.ldf' for database 'SAML' in sys.database_files. The file either does not exist, or was dropped.

    UGH

  • disregard previous post - use quotes worked.

    THANK YOU.

Viewing 8 posts - 1 through 7 (of 7 total)

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