February 10, 2008 at 9:29 pm
I have been trying to figure out how my tempDB ballooned to over 18G. I think I know why but I want to get expert opinion on my thoughts.
The tempdb database is re-created every time SQL Server starts. On my server SQL Server is a Automatic service. Since the server is never turned off SQL never restarted even though the application is closed every night. Since the tempdb is set to automatically grow it ballooned because the server was never restarted.
That sound possible?
February 11, 2008 at 12:41 am
It's purely depend on how application is design and what kind of activity is running on the server.
Check the following link for more detail:
http://www.sqlservercentral.com/Forums/Post.aspx?SessionID=yazjum55xw4k3y55heaxpc45
Hope this will help.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 11, 2008 at 12:44 am
Oops.... 😛 by mistake wrong link has been posted:
here is correct one:-
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
February 11, 2008 at 12:05 pm
Very good article and site. Thank you.
February 11, 2008 at 6:53 pm
Can some one clarify this For me. If the tempdb database is re-created every time SQL Server starts and the server is never turned off. When does SQL refresh the DB? Since the service is always running.
February 11, 2008 at 7:41 pm
In theory, if the SQL service is never restarted, and the server never gets rebooted, then tempdb is never recreated.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 11, 2008 at 8:52 pm
Thanks. Just wanted to make sure my logic was ok.
February 12, 2008 at 2:55 am
There is no desperate need to reinitialise tempdb.
Unless your applications do non-standard things that leave objects permanently in tempdb, the space will get re-used automatically. If your applications do leave garbage in tempdb, you can always set up a daily job to delete objects older than (say) 1 day.
The most likely reason your tempdb is 18 GB is that your applications do enough work to need all 10 GB at some point in the week. You should always assume that tempdb needs to be the size it is until you can prove the growth was due to a 1-off task. If your apps do need tempdb to be 18 GB, then you need to plan to have tempdb set to 18 Gb whenever SQL Server restarts, and make disk space allowance for tempdb growth as your business grows.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 12, 2008 at 7:19 am
I am new to SQL Server so I don’t think I am doing anything out of the ordinary. I am creating and deleting a lot of tables and # tables learning how to do things. And some tables are large. I will keep an eye on it.
How do I set up a daily job to delete objects as you suggested?
February 12, 2008 at 10:24 am
Objects in tempdb disappear when the connections are dropped. Unless you are using global temp tables (start with ##). In that case, write a script to delete those objects and schedule it as a SQL Agent Job.
February 12, 2008 at 12:23 pm
Now it all makes sense!
1. tempdb is recreated at it’s initial size when SQL starts.
2. Objects in tempdb disappear when the connections are dropped. Unless they are global temp tables (start with ##).
I have not seen Item 2 documented any where. Where can I find it in BOL or some other source?
February 12, 2008 at 2:06 pm
global temporary tables (##) DO get dropped automatically when ALL the connections that reference them are CLOSED.
You can find that here
For convenience this is the relevant part:
...
Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
...
* Noel
February 13, 2008 at 11:32 am
Actually, if you have an 18GB tempdb and you never changed the default settings on the server, you now have roughly 18THOUSAND 1MB file fragments scattered all over your drive, which can't be good for performance. And if you bounced the server I am betting tempdb would quickly grow (and fragment) all over again. Set the initial size and growth increments of tempdb and it's log (actually ALL databases and logs) from the get-go!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 13, 2008 at 12:18 pm
kirk (2/10/2008)
I have been trying to figure out how my tempDB ballooned to over 18G. I think I know why but I want to get expert opinion on my thoughts.The tempdb database is re-created every time SQL Server starts. On my server SQL Server is a Automatic service. Since the server is never turned off SQL never restarted even though the application is closed every night. Since the tempdb is set to automatically grow it ballooned because the server was never restarted.
That sound possible?
This is an excellent link on the subject:
"Working with tempdb in SQL Server 2005":
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
In sql 2005, the following types of objects can occupy tempdb space:
• Internal objects
• Version stores
• User objects
Your large tempdb size may be due to poorly optimized queries ("internal objects"), unusually long transactions ("version store") or very large temporary objects (temp tables etc.).
Read the above link carefully. It will give you all the info you need to find out not only which type of objects is contributing to your tempdb size, but also which query/sproc is responsible!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply