January 2, 2016 at 7:19 pm
How to reduse the size of tempdb and there are no of open transation .i already restart services but after some time tempdb again consume space .
January 3, 2016 at 1:44 pm
When you restart the services, Tempdb is dropped and created fresh, based on its default settings. Have you verified the size of tempdb immediately after the restart? If it was 143GB at that time, then someone must have told SQL Server to use that as the default size for tempdb. If that is the case, then see https://support.microsoft.com/en-us/kb/307487 for how to fix it.
Second, look into the properties of the tempdb data file(s) - specifically their autogrow setting. It is unlikely, but possible that the initial size is decent but the autogrow setting is ridiculous - e.g. 3GB initial and 140GB autogrow. In such a case, tempdb will start at 3GB, but as soon as even a single byte extra is needed, a full 140GB extra is allocated. You can just change this from the database properties dialog (use the script as function to see the actual code generated for you)
More likely is that one or more processes running on your server actually require that amount of tempdb storage. In that case, there is nothing you can do to prevent SQL Server from constantly growing tempdb to that size. (Well, okay, actually there is a way but then you'd get critical errors and downtime when it runs out of tempdb space - not fun).
If you need to identify the culprit, look at the recorded data in whatever monitoring tool you uses to monitor the servers. Go back in history until you find the time when tempdb started growing, and then look at what queries and processes were running at that time. Perhaps a stored procedure is using a temporary table or a table variable with an insane amount of data in it? Or perhaps a query is using an internal worktable (note that you can only see this by looking at the execution plan or by looking at the SET STATISTICS IO output) that grows incredibly large? Or a hash match operator or sort operator (again, visibile in execution plan only) are spilling to tempdb?
Once you managed to identify the culprit, there are two options. Either you rewrite the code to need less tempdb space (if possible), or you understand why this is really needed and cannot be worked around. In the latter case, you then want to change the tempdb default size to be large enough -at least 143GB, perhaps a bit more for safety-. Autogrow is great when you need it, but it's also a bit expensive - I consider it as similar to safetybelts and airbags in cars, or fire insurance: things you are very happy to have when you ever do need them, but you still try to arrange your life so that you will never need them at all.
January 3, 2016 at 2:06 pm
January 4, 2016 at 7:12 pm
Thanks Hugo Kornelis
January 4, 2016 at 7:45 pm
gaurav.jadon99 (1/4/2016)
Thanks Hugo Kornelis
Did you find the initial size or autogrow was setting the database size? I honestly hope you did because that's the easiest of the possibilities.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply