| Author |
Topic |
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
 Posted - 2007-12-03 : 11:42:08
|
| Currently the transaction log is getting backed up for a while now every 4 hours between 10AM and 11.59PM , even then the log is bigger than the mdf file, below are the sizes:mdf file: 11.5 GBldf file: 20.1 GBI am wondering why the ldf file is increasing, and I do'nt think ldf file should never be more than mdf file correct?Is it because it's getting backedup everey 4 hrs? Is it good if I take log backup more often like every 2 hrs? How can I prevent the ldf file growing more than mdf file. Please let me know your thoughts. Thx. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-12-03 : 11:52:13
|
| Are you doing a full backup everynight? Is it successful? |
 |
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-12-03 : 12:09:59
|
quote:
Originally posted by RickD
Are you doing a full backup everynight? Is it successful?
The db backup has been created using the maintainence plan to run every night at 9PM and I looked at the properties of the maint plan,under complete backup tab of the plan, the following is checked:1. Backup the db as part of the maintenance plan2. Verify the integrity of teh backup upon completion.By looking at this, I think the database is getting fully backed up and also the job is running successfully.Thanks for the help! |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-12-03 : 12:51:55
|
| Full backup won't affect the tr log.>> I do'nt think ldf file should never be more than mdf file correct?no.Have a look at http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.htmlAn open transaction will stop the tr log from releasing space and so keep growing.You can use dbcc loginfo to see the active log files - will have status 2 I think.Is your log file still growing? If not it could be that is a good size for it unless you change the way the database is being used. If it is growing a lot then look for sometgnig that may be preventing entries being released.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-03 : 13:54:23
|
quote:
Originally posted by sqlserverdeveloper
I am wondering why the ldf file is increasing, and I do'nt think ldf file should never be more than mdf file correct?Is it because it's getting backedup everey 4 hrs? Is it good if I take log backup more often like every 2 hrs? How can I prevent the ldf file growing more than mdf file. Please let me know your
If you've setup an optimizations maintenance plan, then this is most likely the issue. This process will typically cause your LDF file to swell bigger than the MDF. I've see it be 150-200% bigger. And don't shrink it down! It'll just consume it again.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-12-03 : 15:17:34
|
quote:
Originally posted by tkizer
quote:
Originally posted by sqlserverdeveloper
I am wondering why the ldf file is increasing, and I do'nt think ldf file should never be more than mdf file correct?Is it because it's getting backedup everey 4 hrs? Is it good if I take log backup more often like every 2 hrs? How can I prevent the ldf file growing more than mdf file. Please let me know your
If you've setup an optimizations maintenance plan, then this is most likely the issue. This process will typically cause your LDF file to swell bigger than the MDF. I've see it be 150-200% bigger. And don't shrink it down! It'll just consume it again.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
The optimizations maint plan is set up to run every sunday PM, so if this is causing the ldf file to increase, then shall I disable this optimizations job? Then if I disable the optimization maint plan, what other method I can use to reorganiza data and index pages? And also shall I change the freq of log backups to every 2 hrs instaed of every 4 hrs? Thx. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-03 : 15:21:08
|
| You should not disable this job unless you have other code to replace it. I've only got 2005 code for it: http://weblogs.sqlteam.com/tarad/archive/2007/11/27/60415.aspxWe backup our transaction logs every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-12-03 : 17:20:21
|
quote:
Originally posted by nr
Full backup won't affect the tr log.>> I do'nt think ldf file should never be more than mdf file correct?no.Have a look at http://www.nigelrivett.net/SQLAdmin/TransactionLogFileGrows_1.htmlAn open transaction will stop the tr log from releasing space and so keep growing.You can use dbcc loginfo to see the active log files - will have status 2 I think.Is your log file still growing? If not it could be that is a good size for it unless you change the way the database is being used. If it is growing a lot then look for sometgnig that may be preventing entries being released.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
I ran dbcc loginfo and it returned 4 rows with status 2, how do I find what entry's are preventing the release?Thanks for the article, that was good one, I found the below one in the article, but if I change to simple per mentioned below, I can't take log backup's right? I have tran log backup's scheduled every few hrs and also I need to take log backup's. Thx.Stopping the transaction log file (.ldf) from growingIf the log file has grown do to being in full recovery mode then set it to simple before going any further. This should immediately stop the log from growing.Enterprise managerRight click on the database, properties, Options, set model to simple, OK.t-sqlsp_dboption [dbname], 'trunc. log on chkpt.', 'true' |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-03 : 17:26:11
|
| That is correct about not being able to take transaction log backups when using SIMPLE recovery model. What are your data recovery requirements? This is what determines which recovery model to use. The size of the LDF file should not influence your answer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sqlserverdeveloper
Posting Yak Master
243 Posts |
Posted - 2007-12-03 : 17:27:56
|
quote:
Originally posted by tkizer
That is correct about not being able to take transaction log backups when using SIMPLE recovery model. What are your data recovery requirements? This is what determines which recovery model to use. The size of the LDF file should not influence your answer.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
This db is used by our call center 24x7, so we need to backup the transaction log every few hrs. Thx. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-03 : 17:30:43
|
| Then you need to backup your transaction log more frequently in order to keep the LDF file in a more manageable size. But your optimizations job is most likely the one that is causing the file to swell. Just add more disk space to your server so you don't have to worry about this. This behavior is very normal for SQL Server 2000.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-12-04 : 05:14:10
|
| If it is the optimisation that is causing the issue then you can split the procedures up and do log backups between them. It'll mean finding out which procedures cause the log growth.Otherwise you need to find out what is causing it before you can do anything about it.You should also consider whether you should worry about it at all. Maybe this is a good size for your log. If you try to economise on space you could be storing up trouble for the future.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Transaction log backup question