August 11, 2021 at 3:23 am
I have execute sql task which is running on a daily basis before the master package. It is suppose to clean up temp db with following script:
use tempdb;
go
checkpoint;
go
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
DBCC FREESYSTEMCACHE('ALL');
DBCC FREESESSIONCACHE;
GO
DBCC SHRINKFILE(TEMPDEV,8);
GO
DBCC SHRINKFILE(TEMPLOG,8);
GO
Most days it finishes within minutes but some days it hangs and keeps running for 8-10 hrs.
Any reason why it keeps running for such a long time and how can we fix it?
August 11, 2021 at 7:29 pm
Probably a silly question but is there a reason you are wanting to drop ALL clean buffers and free ALL the caches before you do your steps? And why you want to shrink the database and log files?
IF the master package is blowing away all of the data and populating it with fresh data, or at least changing a substantial amount of the data, then clearing the caches I can see being beneficial. But in most cases, I wouldn't recommend clearing caches or dropping buffers and I actually never run those on my prod systems.
As for shrinking tempdb prior to loading the data, that feels like a mistake to me. You are shrinking it pretty small which means it will have hardly any free space and then you are doing stuff on the system that will cause tempdb to grow. Shrinking tempdb doesn't "clean up" anything except maybe get you some additional disk space back at the cost of things growing again as soon as you start using tempdb.
Now as to why it is running for a long time some days, I would check logs and the database usage during the long runs. I would not be surprised if it is caused by blocking which won't be in the logs, but would be visible if you checked database usage during that time. But what you may want to do to determine why it runs fast some days and slow others is to break that step up into several smaller steps. That way when it hits the slow operation, you will know which one of the steps is being slow. I expect it is the shrinking as the other operations are very fast every time if I remember right and shrinking can be fast or slow depending on what SQL needs to do and how large the file is before the shrink and where the "free" space is physically in the database.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply