Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dave Watts
Starting Member
4 Posts |
 Posted - 2008-11-20 : 13:04:52
|
| I have an issue on a database where the transaction log suddenly grows from a couple of Mb up to a maximum setting of 35 Gb.The database has a 50Gb data file and a 20Gb Log file. The Log File has an autogrow specified which grows in 5Gb chunks to a maximum of 35Gb. The Log file remains at around 1 - 2Mb in size for 99% of the time. However, every weekend the logfile fills to 35Gb and I'm having difficulty in pin pointing what is causing this to happen.The database is DB Mirrored for HA and we have Log Shipping switched on for DR. The Log Shipping Frequency is every 15 minutes. The database has a full backup taken on a Saturday and Differential backups throughout the week.The only thing that I am aware of that runs on a weekend on this database other than backups , log shipping etc are maintenace plans which have been set up through the wizard to carry out DB integrity Checks, Updating of Stats and Maintenance on the backup files to delete those we no longer require.Any ideas on how I can identify what is filling the log are very much appreciated. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-20 : 13:36:39
|
| 1)This is issue that arises when Mirror database is far behind than Principal database which causes log file to grow in principal2)How long your job run for DB maintenance(index)?You are rebuilding index with maintenance plan. Instead use smart script to find fragmentation level and do it.3) If this is serious issue, You need to truncate log file and shrink file.4)Look at Database Mirroring Best practices whitepaper from Microsoft. |
 |
|
|
Dave Watts
Starting Member
4 Posts |
Posted - 2008-11-20 : 13:48:25
|
| Thanks for the reply. We have stopped the rebuild Index part of the maintenance plan and we still have the same problem.Do you have a smart script that we can test as you mentioned in your post. Preapred to try anything if it will help resolve the problem.I aim to carry out defragmentation work in the very near future to see if this also helps.Could update stats have an affect as we have one table taking an excessive amount of time it has near to 30Million records and 21 indexes 1 clustered and the remainder non clustered. I have started to try to do this sampling at 30% instead of a FULLSCAN. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-20 : 14:10:41
|
| You can find that smart script in Tara's Blog.We have Autocreate and Autoupdate statistics on which will determine. Instead of Maintenance plan , you can use script to use sp_updatestats which will only update out-of-date stats instead of Update statistics.Its better to schedule that daily.While in database mirroring, the main point is you need keep up the speed of mirror database with principal otherwise will result in huge log file . |
|
|
|
Dave Watts
Starting Member
4 Posts |
Posted - 2008-11-21 : 11:44:47
|
| Many thanks for your comments Sodeep, This issue was actually occuring prior to setting up Database Mirroring and Log Shipping. We first had a maintenance plan that covered all the none system database which checked the database integrity, reorg indexes, updated stats and cleared down old backup files ususal stuff.... However, we would encounter excessive log activity in this one database. We then broken the database into their own maintenance plans.I feel that the Database Mirroring may be having a detremental affect but the fact this problem was there prior to switching on the Lg Shipping or DB Mirroring is a concern.Do you know what DB activities apart from Insert, Update, Delete would cause a log file to go from a few Mb to 50Gb? I have concentrated my thoughts on it being the maintenance tasks that are causing the issue as it is these that are run over the weekend.However, could The database ingrity checks or update stats really cause the database log to grow so excessively ? I don't believe so but more than willing to listen to reason as to why. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-21 : 12:50:51
|
| Do you Bulk insert or SSIS task schedule in Weekend? Or Set Server side trace and schedule in Weekend to track down what is the issue? Choose Log Growth Events in Server side trace or SQL Profiler. Update stats won't increase log growth. |
|
|
|
|
|
|
|
|
Finding the cause of very excessive log growth