• winmansoft (1/15/2013)


    So log backup only reuse the space. But shrinking the log file will increase fragmentation ryt? So is it good habit to shrink the log file?

    LOL.. got to love Gail's short answers, I guess the hint is to go and read the article. But here let me try and help with hopefully, a simple example:

    Consider the following scenario:

    1) You create a database and assign 100MB for data file and 100MB for log file.

    2) You perform a large data import and some complex data manipulation on the database.

    3) When you check the size of your files you find that the data file has grown to 1GB and the log file 2GB!

    4) A week later you do another big job on the database which requires 3GB worth of transaction log information to be recorded. Suddenly your log file is 5GB in total size

    You can see where this is going... as more stuff happens in your database, the log file will just keep growing and growing (assuming auto growth is enabled). Either way, you would soon find out about it when your machine runs out of disk space or SQL can't write to the log file!

    So how to solve this?! Shrink or backup log file you say?

    a) Log backup will basically allow SQL Server to re-use the space that was occupied by the backed up transactions. The actual size of the file will remain the same (until you perform another massive work load which might increase the size of the log from 5GB to say 7GB

    b) Shrinking the file will give back the space the OS but if you know you're going to perform more large data manipulation then it's just going to grow again anyway.

    Hope this helps.

    ---------------------------------------------------------

    It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
    David Edwards - Media lens[/url]

    Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
    Howard Zinn