Post reply

How to switch log file

  • 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

  • July 4, 2005 at 12:50 am

    #570586

    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/

  • July 5, 2005 at 1:38 am

    #570801

    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

     

     

  • July 9, 2005 at 6:32 am

    #572690

    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

  • July 10, 2005 at 10:16 pm

    #572786

    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

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

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