Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow Transaction Log Deletes?


Slow Transaction Log Deletes?

Author
Message
craig-404139
craig-404139
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 436
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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;-)
SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
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.
craig-404139
craig-404139
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 436
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 = ?



craig-404139
craig-404139
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 436
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.

SQLisAwE5OmE
SQLisAwE5OmE
Mr or Mrs. 500
Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)Mr or Mrs. 500 (558 reputation)

Group: General Forum Members
Points: 558 Visits: 3062
[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.
craig-404139
craig-404139
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 436
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.

Rama Chandra Gowtham. Peddada
Rama Chandra Gowtham. Peddada
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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.
craig-404139
craig-404139
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 436
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search