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
416 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:ScottPletcher
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:ScottPletcher
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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:
ScottPletcher 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

21 Experts available now in Live!

Get 1:1 Help Now