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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query 107 59
SQL 2008 R2 syntax 11 29
Please help with the below query - SQL Server 11 17
Problem with SQL Script - Cannot call methods on char 2 21
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

786 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