SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 PLEASE HELP:Log Filled up and cannot truncate logs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

olinmds
Starting Member

USA
25 Posts

Posted - 03/03/2014 :  12:10:56  Show Profile  Reply with Quote
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'XXXXX_Log' for database 'XXXXX' in sys.database_files. The file either does not exist, or was dropped.

New to 2012. Logs filled up and get error when try to shrink file.


THANKS!!!!!

olinmds
Starting Member

USA
25 Posts

Posted - 03/03/2014 :  12:43:24  Show Profile  Reply with Quote
Ran to select * from sys.master_files and found that the 'name' value is different from logical log filename on DB. It is missing one character. So DB properties show 'XXXX_log' and logical name and masterfiles show 'XXXX_lo' (missing the g). Not run up against this one before.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 03/03/2014 :  12:47:24  Show Profile  Visit tkizer's Homepage  Reply with Quote
Backup the log and then you can do the shrink. How often do you backup the tlogs? Our schedule is every 15 minutes.

Regarding the master_files names, no biggie for the database as it's just metadata but it does get annoying when running commands that reference those names. You can fix via ALTER DATABASE.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

olinmds
Starting Member

USA
25 Posts

Posted - 03/03/2014 :  14:06:50  Show Profile  Reply with Quote
Wondering when the disconnect happened as we changed the logical filename during a restore.
Go to Top of Page

olinmds
Starting Member

USA
25 Posts

Posted - 03/04/2014 :  09:27:13  Show Profile  Reply with Quote
Good morning Tara,

Please know I apprectiate your assistance. Alter Database will not let me modify the files to what I want. I would like the database_files value with XXXX_Log to be the true value but get error of XXXX_Lo (Value in master_file) does not exist when trying to modify. Would like to understand how the disconnect happened also as we successdfully altered the value during the restore.

Thanks again!!!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37167 Posts

Posted - 03/04/2014 :  11:04:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
ALTER DATABASE is the correct command for this. Show us what you tried.

The disconnect happened during the restore. Someone had a typo.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

olinmds
Starting Member

USA
25 Posts

Posted - 03/05/2014 :  11:08:45  Show Profile  Reply with Quote
Hi Tara. Thanks for your help. Had to change the logical name to something different and then name it back with our original command. Everything looks great.

Please know I appreciate your help.

Thanks
olinmds
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000