January 2, 2013 at 9:06 am
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.
January 3, 2013 at 4:35 am
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;-)
January 3, 2013 at 7:01 am
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.
January 3, 2013 at 7:33 am
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 = ?
January 3, 2013 at 7:37 am
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.
January 3, 2013 at 7:38 am
craig-404139 (1/3/2013)
Is the 172GB memory is allocated to SQL server, or is this the total RAM?
SueTons.
Regards,
SQLisAwe5oMe.
January 3, 2013 at 7:41 am
I have 166GB allocated for SQL. 172GB total.
SQLCrazyCertified (1/3/2013)
craig-404139 (1/3/2013)
Is the 172GB memory is allocated to SQL server, or is this the total RAM?
SueTons.
January 3, 2013 at 6:12 pm
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.
January 3, 2013 at 8:53 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply