How to switch log file

Question

How to switch log file

Mohammad Sufian

Ten Centuries

Points: 1292
More actions
July 1, 2005 at 8:19 am

#164292

Hi,
I have 3 log file . can anybody tell me how to know the current log file in use by the database.
secondaly can we switch the log file with newly created log file.
I request to those who suggest me to read books online pls dont reply.
and also pls do not give answer for shrink.
I cannot do shrink so pls no shrink command.
fromn sufian

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

So what do you want then? AFAIK, there is no way to see which log file is currently being written to. Why is it important for you to know that? And creating a new log file will not set SQL Server to use that log file directly, it will continue where it is and sometime later come around to the new file.

I do not really see what you want to accomplish? You have three log files, and now you want to add a fourth and use that file instead of the others? Then why do you want to keep the others??

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 2

Hi Hedgate,

Ur answer makes me laugh. It is good to reply to a questation but pls pls if i asked u that which logfile is active then there should be some reason.

Ok buddy just try this and u will come to know which logfile is active ok

dbcc loginfo[database name]

u should not ask what i want to do i am doing all this from last 3 years.

Who says that u cannot switch logfile u can switch logfile and u can force sql server 2 use that logfile immidiately.I am doing this because shrink command is not the bestway to shrink logfile.

from

sufian

Muhammed Sajid Hussain SSC Veteran Points: 290 More actions · Answer 3

Hi All,

Today I received this script from this group to shrik the log file, I test it on my backup server which works excellent. before It was my log file size 10-GB after shirnks it's became 3.7-GB only.

My solute to them who send this scripts.

Best Regards

This script shrinks the log file for all the databases in the instance

use master

DECLARE @Statement varchar (2000)

SELECT @Statement = ''

SELECT @Statement = @Statement + 'USE ?; '

SELECT @Statement = @Statement + 'SELECT ''?''; '

SELECT @Statement = @Statement + 'DECLARE @Log_Logical_FileName varchar (30); '

SELECT @Statement = @Statement + 'SELECT @Log_Logical_FileName = rtrim(name) FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '

SELECT @Statement = @Statement + 'dbcc shrinkfile (@Log_Logical_FileName, 30,truncateonly); '

SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '

SELECT @Statement

EXEC sp_MSforeachdb @command1=@Statement

GO

Mohammad Sufian Ten Centuries Points: 1292 More actions · Answer 4

Hi,

Iwill suggest u that before u execute shrink command on ur production server test it with inserting large amount of data into ur database.

Secondaly i will suggest that u create one more logfile on diffrent drive and then use the shrink command.

to the information related to logfile

see loginfo(database name)

hope u will find something better

from

muhammad sufian