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
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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:Ramesh Babu Vavilla
ID: 37809978
use database
go
sp_helpdb 'database_Name'
then copy the log file
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
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 video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

707 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