| Author |
Topic |
|
aboulmak
Starting Member
9 Posts |
 Posted - 2006-09-18 : 08:17:29
|
my tempdb is growing so fast it sometimes hit the 28GB .. which is enourmouss and iam running out of space which cozing my website to stop (IIS). what do you recommend me to do in order to stop this mess! anyone can help me out with this please |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-18 : 08:19:19
|
| Do you clean up after you in your stored procedures?LikeDROP TABLE #Temp?How fast do you reach 28 GB?Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-18 : 08:22:48
|
| tempdb will be used as required for temporary storage.presumably you have a query somewhere which is generating that amount of temporarily stored data, so really you need to hunt down that query and optimise it.If it turns out that you do need that amount work "working storage" then you should set the initial size of TEMPDB to be somewhere round about that amount (so that contiguous disk space is allocated at startup) and set the Growth to a sensible amount - 10% is going to generate 2.8GB of expansion, which will probably bring your server to its knees for several minutes whilst that happens. 50MB expansion (i.e. rather than a percentage) is probably adequate, but the default is 1MB I think which is way too little!!!Do NOT attempt to Shrink TEMPDB - TEMPDB is a special case in that regard and you could quite easily corrupt your database.If the tempdb does NOT shrink back down when you restart SQL Server please ask again - because that would be a different problem!Kristen |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 08:24:34
|
| Hello Peter, well am a newbie in this stuff, all i know is that we installed a new db server and we have batches runing on it now which uses sql ... on the old server we didnt have any problem .. now we receive that we are runing out of space! we checked the databases and find out that tempdb is hiting the 28!and yes in the batches we use storedprocedures but i dont know what you mentioned about dropping table #temp ?? u have any examples or anything? thx |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 08:27:10
|
| Kristen, i cant restart my server everytime i get this huge size! its killing me and my clients! how can i hunt down that query .. and when i find it how do i optimise ?S.O.S |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-18 : 08:31:20
|
| The batch jobs, are they import of txt files?Peter LarssonHelsingborg, Sweden |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-18 : 08:32:35
|
| "i cant restart my server everytime i get this huge size!"Does that confirm that you know that it shrinks back to zero on restart and then grows to 28GB (or bigger or somewhat smaller)?Because if not this might be a one-off occurrence that a restart is going to fix.You don't need to restart your server, just stop/start the SQL Service (still disruptive, but quicker than a full reboot)Also need the answer to Peter's question "How fast do you reach 28 GB? [after starting SQL or rebooting]" and then we can advise on what the next steps are.Kristen |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 08:32:37
|
| xml files |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-18 : 08:34:58
|
| How are you importing the XML files? (DTS, the SQLBulkXML COM object, or something else?) Large XML files can be very resource hungry if not handled carefully ....But if you were doing that before the new server, and not much else has changed, then we need to look for a different cause.Kristen |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 08:37:38
|
| i know when i restart it it shrinks back to 3MB and then start growing up! well lets say in about 1 hour it reaches 28GB if that batch is working! frankly it depends sometimes it get stable ... sometimes not! |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 08:40:46
|
| sorry Kristen , Peso... am not importing xml to db .. let me be more clearer ..my batch listens to xml files into a specific directory it parses it and do data manipulation ( insert , selects, sp... ) on my db then post variables to my web applications. sorry if i missled you with my previous post.so we noticed that when this batch run the tempdb increase in size. |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 08:49:19
|
| so what should i do? to resolve this problem? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-18 : 08:56:17
|
| Your TEMPDB database grows from 3 MB to 28 GB in an hour? That is more than 475 MB per minute...And with the help of XML files you do DML of some tables with millions or records in?Are you really really sure that it is the TEMPDB growing and not the LOG files growing?Peter LarssonHelsingborg, Sweden |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 09:08:59
|
| Peso, sometimes yes in 1 hour sometimes take much longer ! but it happened so far 3 times it reached 27-28GB! yes i have a big ammount of Data that i do DML on.yes Tempdb reachs that size not the DB iam working on.iam so stuck! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-18 : 09:27:39
|
"sorry if i missled you with my previous post."No problem  If you can force it to happen by using that batch process you could perhaps monitor what is going on with SQL Profiler whilst the batch process is running.Are you just using dynamic SQL from your application (which parses the XML) or are you calling SProcs in the DB?If its SProcs I would look at them carefully to see if they are doing some operation that is creating large temp tables.You could probably also look at the objects in TEMPDB to see what's there - maybe something is leaving something behind?If you haven't already done so it would be sensible to set the Growth of TEMPDB to be, say, 50MB rather than 10%You could also check the slack space in TEMPDB (Enterprise Manager : Right click TEMPDB : All tasks : ShrinkCheck the "Space Free" - make sure you press CANCEL rather than OK !!Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-18 : 09:47:47
|
| I am still curious of how DML increases the TEMPDB size. I would image the LOGs are growing due to heavy DML.Do you have your log files on the same drive as TEMPDB?Peter LarssonHelsingborg, Sweden |
|
|
|
aboulmak
Starting Member
9 Posts |
Posted - 2006-09-18 : 10:02:59
|
| Peso, tempdb allocated space 157MB ( on d:\...\tempdb.mdf) while log file 7MB (on D:\...\tempdb.ldf)also my database is located on the same drive yes! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-18 : 10:56:25
|
| 157MB + 7MB <> 28GB - am I misunderstanding something?kristen |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-09-19 : 01:02:52
|
| Have you got the ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT database options turned on? If so, row versions are stored in the tempdb database and that might be the cause of the growth you are seeing?Also, are you using Notification Services - it can cause a lot of tempdb growth too? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-10-18 : 15:53:38
|
| There is a very good article on calculating and understanding tempdb space usage for SQL Server 2005 herehttp://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx |
|
|
|
|
Tempdb growth!