Log file bigger than data file
-
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
-
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
-
I have transaction job occurs every 4 hours
-
Can you post the T-SQL for your full backup & t-log backups ?
-
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?
-
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"'
-
You need to look in the maintenance plan and be sure this database log is being backed up.
-
My .bak is 20.1 GB, my last for today morning .trn 655 KB
-
Yes,trans log backed up
-
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.
-
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
-
is it dbcc shrink
-
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
-
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.
-
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 33 total)
You must be logged in to reply to this topic. Login to reply