Post reply

How to increase logical log files

  • December 1, 2010 at 6:01 pm

    #388350

    Hi All,

    I am posting this issue in the general section of 2008, although it's not limited to 2008 itself.

    I want to increase the number of logical log files of a database in SQL Server (version 2005 & 2008). The reason, I want to do this is, I have a database whose total size is 1284 MBs. Out of this, the data size is 5120 KB :hehe: and the log file size is 1279 MB :w00t: . When I try to Shrink this log file with DBCC SHRINKFILE, it says,

    "Cannot shrink log file 2 (AdCenter_KSPBI_Log) because total number of logical log files cannot be fewer than 2."

    Is it really possible somehow to increase the no of logical log files ?

    Ahh... One more thing. I cant detach this DB as it is used by another client application utilizing it.

    Thanks for your help.

  • December 1, 2010 at 6:16 pm

    #1257695

    A couple of things to note.

    First, what's a Virtual Log file? It's a logical separation of the file internal to it. Read more here:

    http://msdn.microsoft.com/en-us/library/aa933049(SQL.80).aspx

    And a quick discussion of how many it builds per increase:

    http://sqlserver-training.com/vlf-virtual-log-file-sql-server-performance/-

    So, it sounds like this log file was grown, shrunk, grown, shrunk... until it ran out of options.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 2, 2010 at 2:01 am

    #1257746

    Thanks for the reply.

    I have the understanding about the logical files. I just wanted to find out if there is a way to increase them or not. or may be a workaround to help me decrese the log file size.

  • December 2, 2010 at 10:28 am

    #1257997

    abhijeet.send2me (12/2/2010)

    Thanks for the reply.

    I have the understanding about the logical files. I just wanted to find out if there is a way to increase them or not. or may be a workaround to help me decrese the log file size.

    Having never ran into this issue, you might try creating a second log file at the same location, and then removing the first one. I'm not sure how easily the server swaps to the second file from the first, but it would give you a properly built log file if it works.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

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

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