Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Slow Transaction Log Deletes? Expand / Collapse
Author
Message
Posted Wednesday, January 2, 2013 9:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 174, Visits: 371
I have a sever that has around 230 databases ranging from 1 gig up to 300+ gigs. Running SQL 2008 R2. The server is way over spec'd for our environment and is not being maxed in any area's. Transaction logs are backed up every 15 minutes. Currently I am using a maintenance plan to delete all transaction logs older than 2 days. I've noticed however it is taking 5-10+ hours for the job to complete.

If I delete the transaction log files via something like Forfiles, it finishes in a normal time (less than 3 minutes). If I reboot the server the existing maintenance plan will work again (in under 5 minutes), but after a few months the job will start slowing down again and take hours. Any idea why the transaction log deletes are super fast when the server is rebooted and take longer and longer to complete as the machine stays up?

FYI I can have a folder open containing the transaction logs that are being deleted and it literally deletes 1 log per second when it is running slow (nothing else unusual on the machine during this time and it is in production). If I reboot the machine and run the exact same job, it will finish in under 5 minutes. But then slowly start to slow down for days and weeks until it begins taking hours again. Now If I let the job run for hours, it will eventually complete, but then I am running the job pretty much 24/7 and that seems odd.
Post #1401897
Posted Thursday, January 3, 2013 4:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
Do you experience any memory issues ?
have you checked the cache too? is it playing well ?
another thing , is you log files are sitting with tempdb files ?

Also , Check following events at perfmon

% Disk time= ?
% Disk read time =?
% Disk write time = ?
Avg disk queue lenght = ?
PLE = ?




-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1402293
Posted Thursday, January 3, 2013 7:01 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:25 PM
Points: 404, Visits: 2,479
I am interested in hearing if you find out the cause for this. Sorry to say, I am not sure how to tackle this issue.

230 databases on a production server seems strange to me. Anyway, please keep us posted as you find anything.

Thanks,
SueTons.


Regards,
SQLisAwe5oMe.
Post #1402347
Posted Thursday, January 3, 2013 7:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 174, Visits: 371
No memory issues (We have 172 GB on the server). Log files are not on the same volume as the temp db. I've worked closely with our Compellant SAN engineer and we are not having any disk issues or latency issues. During this slowness of deleting the logs the server is running at normal speed and within baseline values.

Bhuvnesh (1/3/2013)
Do you experience any memory issues ?
have you checked the cache too? is it playing well ?
another thing , is you log files are sitting with tempdb files ?

Also , Check following events at perfmon

% Disk time= ?
% Disk read time =?
% Disk write time = ?
Avg disk queue lenght = ?
PLE = ?


Post #1402372
Posted Thursday, January 3, 2013 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 174, Visits: 371
I work for a Law firm and each database is a case we are representing. Total we have 10+ TB of data (mdf + ldf size). Initially I thought the slowness was because of our increasing number of cases, but once I reboot the server it runs and the transaction log deletes at normal speeds (within 5 minutes). But then over months in slowly starts to slow down in regards to deleting the logs.

Performance is normal and values are always within acceptable baseline ranges, even when the deletes are slow.

Like mentioned using Forfiles (via TSQL job) always deletes the files without issue and super fast. It is only when using the maintenance plan and deleting .trn files that the slowness begins to occur.

SQLCrazyCertified (1/3/2013)
I am interested in hearing if you find out the cause for this. Sorry to say, I am not sure how to tackle this issue.

230 databases on a production server seems strange to me. Anyway, please keep us posted as you find anything.

Thanks,
SueTons.
Post #1402380
Posted Thursday, January 3, 2013 7:38 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 8:25 PM
Points: 404, Visits: 2,479
[quote]craig-404139 (1/3/2013)
No memory issues (We have 172 GB on the server). Log files are not on the same volume as the temp db. I've worked closely with our Compellant SAN engineer and we are not having any disk issues or latency issues. During this slowness of deleting the logs the server is running at normal speed and within baseline values.[quote]

Is the 172GB memory is allocated to SQL server, or is this the total RAM?

SueTons.


Regards,
SQLisAwe5oMe.
Post #1402381
Posted Thursday, January 3, 2013 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 174, Visits: 371
I have 166GB allocated for SQL. 172GB total.

SQLCrazyCertified (1/3/2013)
[quote]craig-404139 (1/3/2013)
No memory issues (We have 172 GB on the server). Log files are not on the same volume as the temp db. I've worked closely with our Compellant SAN engineer and we are not having any disk issues or latency issues. During this slowness of deleting the logs the server is running at normal speed and within baseline values.[quote]

Is the 172GB memory is allocated to SQL server, or is this the total RAM?

SueTons.
Post #1402385
Posted Thursday, January 3, 2013 6:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 22, 2014 11:33 PM
Points: 15, Visits: 131
Have you tried re-cycling SQL Services instead of System Reboot. This would differentiate if the problem is at the instance level or at the server level.
Post #1402628
Posted Thursday, January 3, 2013 8:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 9:28 PM
Points: 174, Visits: 371
The system reboots are only during scheduled downtime/upgrades which happen to be every few months. Are you suggesting it is not a SQL issue and maybe something with the O.S.? Can you expand on that? Is having SQL maintain transaction log backup deletes for 230+ databases that get transaction log backups every 15 minutes too much for it to handle? The job simply deletes .trn files older than 2 days and that is the only step.

I should add the transaction log backups themselves only take 3-5 minutes to complete and run every 15 minutes.
I have a different job that deletes the .bak files older than 2 days and it completes in acceptable times.

I don't reboot to fix the slow transaction log deletes as I use a different SQL job using Forfiles (http://www.mssqltips.com/sqlservertip/1618/using-the-forfiles-command-to-delete-sql-server-backups) to delete the transaction logs when the other maintenance plan job slows. I would like to find the source of the slowness and continue to use SQL maintenance to delete the transaction logs if possible.

Like I mentioned in a previous post if I open a folder containing the transaction log backups while the maintenance plan job is running it will literally delete one .trn file per second. This happens on small and/or large (max few hundred MB's each but on average a few MB's each) transaction log backups. If I were to reboot the server (or possibly just restart SQL services) the same exact job looking in a different folder will delete all the old logs almost immediately. I never have this problem using the Forfiles script in the above link as it uses EXEC xp_cmdshell and essentially a DOS command to delete the .trn files.

Please let me know if anyone needs clarification or additional info.

Rama Chandra Gowtham. Peddada (1/3/2013)
Have you tried re-cycling SQL Services instead of System Reboot. This would differentiate if the problem is at the instance level or at the server level.
Post #1402653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse