November 28, 2020 at 2:51 am
These days, the Tempdb of my one's SQL server increases crazily, sometimes only abount 5 mintues the MDF file increases 70GB, but lof file of tempdb is not big(Maybe about 2-3GB), I have some questions regarding Tempdb, thanks!
November 29, 2020 at 3:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
November 29, 2020 at 9:45 pm
Crazy increases in size are normally due to a query that has one or more "accidental Cross Joins" in the form of "Many-to-Many" results. They typically contain a DISTINCT or a GROUP BY in the code to try to over-come the duplicates that will form in the result set.
My recommendation would be to to do a "yabingooducklehoo" search for how to setup an alert when TempDB is going through such a nasty grow spurt and have that alert run some code to show you what the code is that's causing the issue.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 12:53 am
Crazy increases in size are normally due to a query that has one or more "accidental Cross Joins" in the form of "Many-to-Many" results. They typically contain a DISTINCT or a GROUP BY in the code to try to over-come the duplicates that will form in the result set.
My recommendation would be to to do a "yabingooducklehoo" search for how to setup an alert when TempDB is going through such a nasty grow spurt and have that alert run some code to show you what the code is that's causing the issue.
thank you!
I know some code like distinct ?group by?sort ?table variables ? temp table etc taking space of tempdb. it seems that there is no good way to control mdf file of tempdb crazy increase ?
November 30, 2020 at 1:08 am
The things you mention don't typically cause the crazy increase you speak of. And I just told you a "good way" to find it so someone can fix it. 😉 You cannot control something unless you find it and then have someone fix it. If I had to put numbers to it for most of the problems I've seen, 85% or more of such problems is caused by bad code, 5-10% of it can be due to insufficient hardware, and 5% can be because of poor programming (such as the FORMAT function) on the part of MS.
From what you describe, I'm thinking there's a 99% chance that the problem is being caused by bad code and the problem will continue until you find it and fix it because no amount of hardware is going to fix this type of problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2020 at 2:49 am
The things you mention don't typically cause the crazy increase you speak of. And I just told you a "good way" to find it so someone can fix it. 😉 You cannot control something unless you find it and then have someone fix it. If I had to put numbers to it for most of the problems I've seen, 85% or more of such problems is caused by bad code, 5-10% of it can be due to insufficient hardware, and 5% can be because of poor programming (such as the FORMAT function) on the part of MS.
From what you describe, I'm thinking there's a 99% chance that the problem is being caused by bad code and the problem will continue until you find it and fix it because no amount of hardware is going to fix this type of problem.
well noted,thank you Jeff Moden for your kind help!
November 30, 2020 at 7:50 pm
Jeff Moden wrote:The things you mention don't typically cause the crazy increase you speak of. And I just told you a "good way" to find it so someone can fix it. 😉 You cannot control something unless you find it and then have someone fix it. If I had to put numbers to it for most of the problems I've seen, 85% or more of such problems is caused by bad code, 5-10% of it can be due to insufficient hardware, and 5% can be because of poor programming (such as the FORMAT function) on the part of MS.
From what you describe, I'm thinking there's a 99% chance that the problem is being caused by bad code and the problem will continue until you find it and fix it because no amount of hardware is going to fix this type of problem.
well noted,thank you Jeff Moden for your kind help!
There is a way to 'control' the crazy growth...but should be used as a last resort after you have done everything you can to not only identify the offending code - but to identify the group/individual responsible for that code and have them address the issues.
You can set a max size on each file in tempdb (the same size for all files) - allowing for a modest increase in size (leaving extra space on the drive to allow for emergency growth - if needed) for normal operations and turn off auto grow. This will stop the files from growing once they reach that max size and any code that is running at that time will fail.
Now...there are some downsides that you need to be aware - for example, if you have enabled RCSI on any databases and tempdb fills up with an active transaction locking the space, then your system will stop allowing any updates. Another example is on a read-only secondary in an AG - if someone locks up tempdb and leaves that session open in SSMS - the transaction log on the production database(s) cannot be truncated and those will eventually fill up the drive in production and again...everything stops.
But - that would happen anyways if the processes are filling the drive to capacity.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 1, 2020 at 1:11 am
892717952 wrote:Jeff Moden wrote:The things you mention don't typically cause the crazy increase you speak of. And I just told you a "good way" to find it so someone can fix it. 😉 You cannot control something unless you find it and then have someone fix it. If I had to put numbers to it for most of the problems I've seen, 85% or more of such problems is caused by bad code, 5-10% of it can be due to insufficient hardware, and 5% can be because of poor programming (such as the FORMAT function) on the part of MS.
From what you describe, I'm thinking there's a 99% chance that the problem is being caused by bad code and the problem will continue until you find it and fix it because no amount of hardware is going to fix this type of problem.
well noted,thank you Jeff Moden for your kind help!
There is a way to 'control' the crazy growth...but should be used as a last resort after you have done everything you can to not only identify the offending code - but to identify the group/individual responsible for that code and have them address the issues.
You can set a max size on each file in tempdb (the same size for all files) - allowing for a modest increase in size (leaving extra space on the drive to allow for emergency growth - if needed) for normal operations and turn off auto grow. This will stop the files from growing once they reach that max size and any code that is running at that time will fail.
Now...there are some downsides that you need to be aware - for example, if you have enabled RCSI on any databases and tempdb fills up with an active transaction locking the space, then your system will stop allowing any updates. Another example is on a read-only secondary in an AG - if someone locks up tempdb and leaves that session open in SSMS - the transaction log on the production database(s) cannot be truncated and those will eventually fill up the drive in production and again...everything stops.
But - that would happen anyways if the processes are filling the drive to capacity.
Thank you Jeffrey Williams, setting the same max size for tempdb, but it can solve the problem thoroughly, maybe the best solution is to find the offending code. thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply