x

Transaction log file increasing rapidly ?

HI All,

I have a database on the mdf file is not increasing but the ldf file increasing rapidly. I have truncated the log file and make it to 1 MB in size but in just one day it baecame 50 GB in size, i take T log twice a day from the maintanece plan. I think from maintanence plan the log file do not truncate while taking backup.

Do i need to take more frequently backup..

Thanks

Basit Khan
more ▼

asked Aug 11 '12 at 06:59 PM in Default

basit 1 gravatar image

basit 1
449 49 61 81

Do you have any index maintenance jobs configured against the database? This can cause a great deal of log activity.
Aug 15 '12 at 02:19 PM Shawn_Melton
Did you get this fixed?
Oct 03 '12 at 04:21 PM Scotty
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
You must have a great deal of transactions updating and or changing data consistently. You should do much more frequent backups. I would start with either 15 or 30 minutes. If you are already doing twice a day but it is growing to 50 GB in size, you need much more frequent backups. You should also read Kimberly's blog on virtual log files. You are probably having problems with tons of virtual log files and aren't even aware of it.
more ▼

answered Aug 11 '12 at 07:06 PM

Tim gravatar image

Tim
35.5k 32 40 138

What about an uncommitted transaction?
Aug 12 '12 at 09:43 AM ThomasRushton ♦
Basit, did this response help? Have you made any changes?
Aug 13 '12 at 08:58 PM Tim
You should also consider if you are doing nightly maintenance that could be growing the log as well.
Aug 15 '12 at 02:12 PM Tim

Especially with a "Drag&Drop" Maintenance Plan. Starting with a checkdb, running backups, shrinking files and then running index maintenance.... But I'd still also investigate what is causing the log to grow. If by the end of the day SQL Server grew the file to 50GB again, then the server potentially needed the space.

Alltogether an overall look can help to understand the cogs involved

Aug 15 '12 at 02:23 PM DirkHondong
(comments are locked)
10|1200 characters needed characters left

Hi Basit,

I think you should start some investigation of what is happening. Gail Shaw wrote a great article on ssc. Take a look: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

You should keep in mind that shrinking the log isn't best practice at all and should only be the last solution if there's no other way to solve a space problem.

Regards Dirk
more ▼

answered Aug 15 '12 at 12:35 PM

DirkHondong gravatar image

DirkHondong
1.3k 14 17 19

(comments are locked)
10|1200 characters needed characters left

It's hard to tell if 50GB transaction log is "large" or "small". It depends on your size of disks, your speed of disks, size of the data file(s) etc.

Use DBCC SQLPERF(logspace) to find out how much of the transaction log is actually used. If the transaction log is nearly full, you won't be able to shrink it either. (But you probably don't want to do that anyway)

If what's happening is that you have a large transaction log, back it up, shrink it and then it grows back to 50GB again in one day, I'd say you should either back it up more frequently or simply let it be 50GB. Otherwise your queries will perform poorly because the transaction log needs to auto grow all the time. (Plus you'll get a lot of virtual files, like Tim said).

more ▼

answered Aug 15 '12 at 01:13 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
15.9k 15 19 32

(comments are locked)
10|1200 characters needed characters left

check the recovery model of your database by using this command in ssms

select recovery_model_desc,name from sys.databases

for best practive to shrink a database, please cehck my blog http://sqlservr.blog.com/2012/06/26/best-practice-to-shrink-in-logldf-file-in-sql-server/

if your databse is in simple recovery mode then use

USE [dbname] GO DBCC SHRINKFILE (N'dbname_log' , 0)

to check for open transaction use dbcc opentran
more ▼

answered Aug 13 '12 at 07:12 AM

ramesh 1 gravatar image

ramesh 1
2.1k 59 67 69

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x186
x63

asked: Aug 11 '12 at 06:59 PM

Seen: 1178 times

Last Updated: Oct 03 '12 at 04:21 PM