Solved

sql server 2008 log file ending in_1 instead of _log, why, can I change back to log?

Posted on 2012-04-04
6
423 Views
Last Modified: 2012-04-05
I was trying to truncate log file using the following which has worked in the past:

ALTER DATABASE "CUSTOMER" SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(N'CUSTOMER_log', 1)
ALTER DATABASE "CUSTOMER" SET RECOVERY FULL WITH NO_WAIT

But I find some of the databases log files end in _log and other end in _1.  How did the _1 get assigned.  When I change the file name in the DBCC command to _1 it does not work.

Can I rename the log file from _1 to _log? and change this in the Properties->File for the database?   Does this cause any other issues?

Need better understanding of why this is happening and how to correct?
Thanks, Bill
0
Comment
Question by:wmlbill
  • 3
  • 2
6 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37807527
>> Can I rename the log file from _1 to _log?  Does this cause any other issues? <<
Yes, you can change the logical file name.  No, it does not cause any other issues.

>> change this in the Properties->File for the database? <<
I wouldn't.  I would use SQL statements so you can easily generate them and do them all at once any time you need to.

This should generate the commands you need to rename all _1 logical log file names to the new names.
Copy the results of this command into a query window and run it -- after verifying the cmds look right, of course :-) .


SELECT
    'ALTER DATABASE [' + DB_NAME(database_id) + '] ' +
    'MODIFY FILE ( NAME = [' + name + '], NEWNAME = [' + LEFT(RTRIM(name), LEN(name) - 2) + 'log] ); '
FROM sys.master_files mf
WHERE
    type_desc = 'LOG' AND
    name LIKE '%[_]1'
0
 

Author Comment

by:wmlbill
ID: 37807713
Thanks for the query,  I really need to understand how and why this happened?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 37807864
Likely the default name was somehow changed; look at the model db and see what its logical log file name is.

Or someone built commands to create new dbs and they used "_1" instead of "_log".

Or someone restored dbs and they had a different name.

The logical name really doesn't mean anything.  You really shouldn't use that to control processing anyway.  It can be changed at any time.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:wmlbill
ID: 37808595
OK,
so why did my dbcc with _1 at the end work?

What should I use instead of the logical name to control processing?
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 37808641
You can use the file_id (file#) in the DBCC command:


DECLARE @file_id int

SELECT @file_id = file_id
FROM sys.master_files
WHERE
    database_id = DB_ID(N'CUSTOMER') AND
    type_desc = 'LOG'

DBCC SHRINKFILE(@file_id, 1)
0
 
LVL 10

Expert Comment

by:sqlservr
ID: 37809978
use database
go
sp_helpdb 'database_Name'
then copy the log file
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now