| Author |
Topic |
|
Peace2007
Posting Yak Master
239 Posts |
 Posted - 2009-02-08 : 06:46:35
|
| Hi, Our tempdb file is 12GB. Does it indicates any problem with server, applications etc.? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-08 : 06:50:16
|
quote:
Originally posted by Peace2007
Hi, Our tempdb file is 12GB. Does it indicates any problem with server, applications etc.?
Is AutoGrow option Enabled? How much space is left for drive where TempDB resides? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-08 : 06:59:38
|
quote:
Originally posted by Peace2007
Our tempdb file is 12GB.
How long is a rope?To be honest, your question doesn't give as any useful information.For some of our databases, I would consider a log file less than 60 Gb something wrong.For other databases I would consider 20 Mb log file too large.The size of log file depends on many factors.For example, are there many inserts/updates/deletes?What is the database use?How often are there full backups and how often are there log backups?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-08 : 08:16:10
|
| Auto grow is enabled and 138 MB is left for the drive where TempDB resides. To be honest, your question doesn't give as any useful information.>> I was talking about system TempDB database which resides under system databases. We haven't created any table under TempDB database. So it's used by SQL system processes silently. The log file of tempDB is small but the data file is 12GB. There are many reads and writes on several databases that locates in the same SQLServer (the databases size are less than 1 GB). Besides, only full backup occurs daily. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-08 : 08:28:17
|
quote:
How often are there full backups and how often are there log backups?-- You can't backup TempDB.
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-08 : 08:33:39
|
| Peace2007,Remember SQL 2005 uses more TempDB than SQL 2000.You need to have more spaces for it to avoid like this scenerio.You can shrink the TempDB files but it has to be in Single-User mode. Restarting Services can give you Fresh TempDB. |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-08 : 08:55:41
|
| Thanks soDeep! full backups are taken for other databases not tempdbI have shrunk the tempdb but no difference were made on its data fileSo do you mean its natural to have such large tempdb? Is it preferred to restart services to move tempdb to another drive? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-08 : 09:15:06
|
| If you have activities that leads to usage of TempDB, You should consider using bigger drives or move TempDB file to Bigger drive. Yes ,you can find appropriate time and restart MSSQL services which will create fresh new TEMPDB but make sure nothing is using TEMPDB. You can spread TempDB files accross number of cores in your server to reduce I/O contention issues. If you are inserting lots of records to TEMP TABLE,I would suggest create index to save some space.Hope that explains everything. |
|
|
|
cshah1
Constraint Violating Yak Guru
347 Posts |
Posted - 2009-02-09 : 12:18:26
|
| I had the similar issue in past (our TempDB grew to 13 GB in Size) but look any of large tempdb cause mentioned in this article is helpful to you. It helped me. http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html |
|
|
|
jason.williams14
Starting Member
10 Posts |
Posted - 2009-02-09 : 20:25:54
|
| We had a problem where our tempdb was growing to over 60gb one time.I dug around for sometime, trying to discover what the heck was going on.When I started to look around, I noticed that one of the databases on the server was from a application piece that we use. I later found out that the version we were running had a bug in it that caused, you guessed it, tempdb to grow very very large. |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-11 : 01:09:25
|
| Thanks all for your useful comments :) |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-02-12 : 07:48:23
|
| is it not a good idea to have the tempdb located on a seperate hard drive to avoid it get full quickly |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-12 : 07:52:52
|
quote:
Originally posted by NeilG
is it not a good idea to have the tempdb located on a seperate hard drive to avoid it get full quickly
Yes it should be separate drive(RAID 10) with necessary spaces. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-12 : 07:55:20
|
In general it is a good idea to have userdatabase on one harddrive, tempdb on another harddrive and log on a third different harddrive!
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-14 : 01:30:12
|
| I've studied somewhere that tempdb should be in the same place as master and other system databases. is it correct? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-14 : 09:48:31
|
quote:
Originally posted by Peace2007
I've studied somewhere that tempdb should be in the same place as master and other system databases. is it correct?
Incorrect. They should be placed in different drive(Like RAID-10)for greater read,write performance and space. |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-08 : 16:19:15
|
quote:
Originally posted by Peso
In general it is a good idea to have userdatabase on one harddrive, tempdb on another harddrive and log on a third different harddrive!
<sarcasm>Yes, you should have the operating system installed on one fault tolerate volume, SQL server binaries installed on a second fault tolerate volume, system databases on a third RAID 1 volume, master log file on a fourth RAID 1 volume, msdb log on a fifth RAID 1 volume, tempdb on a RAID10 volume (RAID 10 requires at least four physical drives), your application database on a RAID5 volume, and the application database's log on a RAID 1 volume.So, a basic database server should have at least 19 drives.</sarcasm>Of course, you can't buy a server at HP's web site with 19 drives, so you'll have to stuff one with 16 of the cheapest drives and buy and external drive expansion cage. Expect to pay at least $10,000 before selecting processors and memory.All of the recommendations for splitting I/O are thrown around way too lightly and it is almost impossible to actually implement these recommendations on any except the largest 1% of the servers in the real world. The ultimate irony is that in many of these large organizations this storage is on the SAN and getting a single dedicated RAID 10 array may run as much as $20,000, regardless of size. Fiber channel drives are reallllllly expensive and the storage group will charge for the slots and I/O bandwidth as well. For example, if you need a RAID 10 array for performance reasons, then you obviously are going to be moving at least 400MB/s, so you'll need a dedicated pair of 4Gb/s FC HBAs just for this array. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-02-08 : 16:24:49
|
Our concern is that is you have access to a SAN, you are much more experienced than asking a question of a tempdb.What OP should be concerned about is that if something large has happened recently, such as changing datatype for a column in a very large table and so on.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
Jaime
Starting Member
19 Posts |
Posted - 2010-02-08 : 16:54:59
|
| My point is that this thread is full of recommendations that, if followed, will be very expensive. I thought my summary that the collection of all of the standard "general knowledge" of what belongs on a separate drive adds up to 19 drives was a good summary of that fact.In real life, no server ever follows all of these recommendations. If no one follows all of them, then they can't possibly be best practices. |
|
|
|
james_wells
Yak Posting Veteran
55 Posts |
Posted - 2010-02-09 : 12:00:32
|
| Not a simple answer to your problem as the space could be used again once recovered depending on what operations are taken plave on the database. For example Deleting large volumes of data from a table can cause the temp database to grow as with large updates as it needs the space to roll back the data. monitor the Temp DB usage withSELECTSUM (user_object_reserved_page_count)*8 as usr_obj_kb,SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,SUM (version_store_reserved_page_count)*8 as version_store_kb,SUM (unallocated_extent_page_count)*8 as freespace_kb,SUM (mixed_extent_page_count)*8 as mixedextent_kb,SUM (User_object_reserved_page_count + internal_object_reserved_page_count + version_store_reserved_page_count + unallocated_extent_page_count + mixed_extent_page_count) * 8 as total_space FROM sys.dm_db_file_space_usageExample of outputuser Internal Version Free Mixed Total Objects Store Space Extent SpaceKB KB KB KB KB KB960 52224 704 2245824 16192 2315904Once the space has been used the TEMP DB will not let the space gountil a restart of the Instance. Shrink operations do not shrink the version store or internal objects. This means that you will not recover space if the internal object or version store page needs to be moved.Shrink File operations are not recommended since these files will probably grow again. In addition, shrink operations cause data fragmentation. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 12:18:03
|
| (Notwithstanding that this thread is ancient ...)I thought shrinking TEMPDB (as opposed to normal User Databases) had significant chances of corrupting User Databases unless a very strict procedure was followed [setting TEMPDB to single user etc] - or has this been improved in SQL 2005 / SQL 2008?Much better to Stop/Start SQL and if that does not automatically shrink TEMPDB then follow procedure to force SQL to build an "empty" TEMPDB on restart. |
|
|
|
Next Page
|
Large tempDB size!!