SQL Server Performance Forum – Threads Archive
Transaction log grows
I have 6 G database (full recovery model)Transaction log is growing like 4-6 G per night
I just scheduled truncate/shrink job after transaction log backup.
But still wondering what would cause such a big grows .
I was watching activity at server there was no users at all during 2 days!!!
Why transactio log would grow?
Please refrain from duplicating a post at two or more places. I have deleted the other post.
Gaurav
Moderator – SQL-Server-Performance.com
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Are there any jobs run in the night that do a mass insert / delete? If there are try deleting / insering in batches. This requires less log space. Gaurav
Moderator – SQL-Server-Performance.com
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
there is jobs but such a huge grows?
id database option transaction log is set to automatically grow file by percentage .
Is that okey or i would better change it to grow by MG will that help?
Changing auto grow options won’t help. How many rows are affected when the job is run? Do you take backup before / after the job is run? Gaurav
Moderator – SQL-Server-Performance.com
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Check whether you have any schedule task which does reinddxing on database. If you have reindexing then that can be cause for such a big log everyday. Nilay
Do you use replication? If so, in your replication may be something is going wrong and because of that, your transaction log grows everyday.
no replication is not happening
Why do not you put in a sql profiler trace based on the jobs executed if you can afford to have that amount of overhead on your server to trace out the exact series of operation, or else if u can save performance monitor logs during the execution of jobs, u would have a very clear idea about the exact nature of jobs getting exected, which could eventually help u in getting to the root cause of the problem. Bhushan
What kind of maint.plans are on the database? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Actually maintenance plan is the problem here.
This server has 815 databases(!)Half of them are simple recovery model and half full.
There was one recovery plan for all user databases which would do everything :
optimization,integrity chechk,backing up transaction log and the database by itself
All this staff is done every night.
But because transaction log cannot be backed up for simple recovery model dbs,
sql could not clean old files:because transaction log backup was reporting errors.
So first thing i did separated that maintanence plan in two peices
1.for simple recovery model(optimization,integrity chechk,backing up )
2.for full recovery model (optimization,integrity chechk,backing up and transaction log backup) Optimization every night!
What kind f optimization queries are run on the server? Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Optimization job will have impact on Tlog to increase the space.
For which database you have the problem of Tlog space bulge & its recovery model/Tlog backup schedule? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
1.DBB4C3D_Audit , Updateability=READ_WRITE
UserAccess=MULTI_USER
Recovery=FULL
Version=539Collation=SQL_Latin1_General_CP1_CI_ASSQLSortOrder=52IsTornPageDetectionEnabledIsAutoCreateStatisticsIsAutoUpdateStatistics 80DBB4C3D_Audit 4880.38 MBsa432Sep 12 200280
Sorry i forgot :
Tlog is being backed up every night
So why not schedule Tlog backup every hour or so and see the difference use WITH INIT clause while backup of Tlog. Also run DBCC SHOWFILESTATS and produce the results. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I will try it :
schedule Tlog backup every hour
I have to wait until tomorrow thought:have to ask manager
Should i truncate transaction log every hour also after backing log?.
Last time when i backed up transaction log at the middle of the day it
didn’t truncate transaction log emediately
Backing up the transaction log automatically reduces the %used transaction log. So there is no need to truncate the transaction Log. Did you check the %transaction log used at that instance? If it didn’t get reduces, there could be an open transaction in the database towards the end of the transcation log. Also the transaction log backup will not reduce the size of log. It will simply free up the space. For reducing / shrinking the transaction log, see other posts on this site. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
True, also refer to books online also for DBCC SHRINKFILE about shrinking Tlog file.
If the optimization jobs take on Tlog, then there will be no use of running DBCC SHRINKFILE every time. Better to define file size values for Tlog after assessing the jobs usage. HTH _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
]]>