Log file bigger than data file

  • Krasavita

    SSC-Insane

    Points: 23136

    Mady data is 32,084MB and Log is 42,084 MB. I know log should be smaller, how can I clean it or do I need to?

    Thank you

  • Tristan Chiappisi

    Mr or Mrs. 500

    Points: 568

    Are you doing transaction log backups? Does your database need point in time recovery, or do you depend on full backups? If you're in FULL recovery mode your database log will continue to grow unless you backup the transaction log. If you don't need point in time recovery, you may want to switch to SIMPLE.

    This article explains the recovery models in SQL Server: http://msdn.microsoft.com/en-us/library/aa173531%28SQL.80%29.aspx

  • Krasavita

    SSC-Insane

    Points: 23136

    I have transaction job occurs every 4 hours

  • homebrew01

    SSC Guru

    Points: 55137

    Can you post the T-SQL for your full backup & t-log backups ?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    It's entirely possible that your log needs to be bigger. If you are changing lots of data (as opposed to adding it), the transaction load could be high.

    My guess is your log grew too large at some point before log backups were running. how large are the log backups?

  • Krasavita

    SSC-Insane

    Points: 23136

    this is for transaction jobs:EXECUTE master.dbo.xp_sqlmaint N'-PlanID D3176901-93DB-4538-8A12-35D337C7C70D -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "F:\MSSQL\BACKUP" -DelBkUps 1DAYS -CrBkSubDir -BkExt "TRN"'

    this is for full backup:

    EXECUTE master.dbo.xp_sqlmaint N'-PlanID D3176901-93DB-4538-8A12-35D337C7C70D -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "F:\MSSQL\BACKUP" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

    You need to look in the maintenance plan and be sure this database log is being backed up.

  • Krasavita

    SSC-Insane

    Points: 23136

    My .bak is 20.1 GB, my last for today morning .trn 655 KB

  • Krasavita

    SSC-Insane

    Points: 23136

    Yes,trans log backed up

  • Tristan Chiappisi

    Mr or Mrs. 500

    Points: 568

    Maybe try to do a manual transaction log backup and see if it shrinks down. If it does, you might have an issue with your plan.

  • José.Cruz

    Right there with Babe

    Points: 722

    Hi there,

    The transaction log backup doesn't reducers the phisical size of the transaction log file, it just removes entries from the log already commited to the database. In order to reduce the size of the file you should shrink the file.

    José Cruz

  • Krasavita

    SSC-Insane

    Points: 23136

    is it dbcc shrink

  • José.Cruz

    Right there with Babe

    Points: 722

    Hi there,

    It's DBCC SHRINKFILE, or you can use SSMS to do it. Make sure taht you back up your transaction log before shrink the file, otherwise it may not shrink.

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

    José Cruz

  • Ignacio A. Salom Rangel

    SSC-Insane

    Points: 20442

    Hi Krasavita,

    If you are using sql 2005 or higher, you wil be able to see the space used by the transaction log. You can check it by using the disk usage standard report. In the report you can see how much space of the total space its been used by the transaction log.

  • yup

    SSCarpal Tunnel

    Points: 4490

    CAn you do a dbcc sqlperf ('logspace') and check log space used & occupied & then try shrinking like

    USE [YourDB]

    DBCC SHRINKFILE ('YouDBlogFIle' , 0, TRUNCATEONLY)

    If this fails to do much (may be there is a virtual log blocking your shrink) take a Tlog backup & do it again.

Viewing 15 posts - 1 through 15 (of 34 total)

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