Post reply

Transaction log

  • October 22, 2003 at 11:56 am

    #176808

    I have just been put in charge of the companys SQL server. I am a programmer and have had limited experience administering SQL. The probem I see is that on our main web database the transaction file is huge compared to the data. What do I need to do to slim that file up..? Please remember I havent done much on the administration side so please try to point me in the right direction.

  • October 22, 2003 at 12:07 pm

    #479156

    You really need to back it up. Backing up the transaction log also 'shrinks' it.

    You should be doing full backups also.

    With the install of SQL Server, you should also have the Books OnLine (BOL) installed (Start>Programs>Microsoft SQL Server>Books OnLine).

    Refer to the BOL, use the Index tab and enter BACKUP DATABASE. That will give you alot of info on doing backups. Also read, BACKUP LOG.

    Also, look through the BACKUP forum (This one) for more information.

    -SQLBill

  • October 22, 2003 at 12:09 pm

    #479157

    Hi Bill,

    Yes I looked there and have done backups.. both full and log backup.. But the actual data file stays the same size almost 10 gig.. I am thinking there is an option i didnt check or something along those lines

  • October 22, 2003 at 12:26 pm

    #479161

    quote:

    Hi Bill,

    Yes I looked there and have done backups.. both full and log backup.. But the actual data file stays the same size almost 10 gig.. I am thinking there is an option i didnt check or something along those lines

    Backing up the tran log truncates it, but does not shrink it. It has autogrown to 10GB and will stay at that size (although mostly empty) until you shrink the file. Look up "shrinking transaction logs" in BOL.

    --Jonathan



    --Jonathan

  • October 22, 2003 at 2:47 pm

    #479187

    Thanks

  • October 23, 2003 at 3:52 am

    #479243

    Provide the DATABASE_NAME, following statements will do the rest.

    backup log DATABASE_NAME with truncate_only

    go

    DBCC SHRINKDATABASE (DATABASE_NAME, 10)

    go

  • October 23, 2003 at 10:49 am

    #479337

    You'll want to address the cause of this growth. Its possible youre loading lots of data at a time. You can manage the growth of the transaction Log while loading data by changing the recovery mode to simple or bulk logged when doing massive imports.

    Cheers,

    Isaiah


    -Isaiah

Viewing 7 posts - 1 through 6 (of 6 total)

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