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
435 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

733 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