SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Tempdb growth!
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:17:29  Show Profile  Visit aboulmak's Homepage
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

Sweden
30282 Posts

Posted - 09/18/2006 :  08:19:19  Show Profile  Visit SwePeso's Homepage
Do you clean up after you in your stored procedures?
Like

DROP TABLE #Temp

?

How fast do you reach 28 GB?



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/18/2006 :  08:22:48  Show Profile
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
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:24:34  Show Profile  Visit aboulmak's Homepage
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
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:27:10  Show Profile  Visit aboulmak's Homepage
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 09/18/2006 :  08:31:20  Show Profile  Visit SwePeso's Homepage
The batch jobs, are they import of txt files?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/18/2006 :  08:32:35  Show Profile
"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
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:32:37  Show Profile  Visit aboulmak's Homepage
xml files
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/18/2006 :  08:34:58  Show Profile
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
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:37:38  Show Profile  Visit aboulmak's Homepage
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!
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:40:46  Show Profile  Visit aboulmak's Homepage
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.

Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  08:49:19  Show Profile  Visit aboulmak's Homepage
so what should i do? to resolve this problem?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 09/18/2006 :  08:56:17  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/18/2006 09:00:09
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  09:08:59  Show Profile  Visit aboulmak's Homepage
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!
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/18/2006 :  09:27:39  Show Profile
"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 : Shrink

Check the "Space Free" - make sure you press CANCEL rather than OK !!

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 09/18/2006 :  09:47:47  Show Profile  Visit SwePeso's Homepage
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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 09/18/2006 09:50:58
Go to Top of Page

aboulmak
Starting Member

9 Posts

Posted - 09/18/2006 :  10:02:59  Show Profile  Visit aboulmak's Homepage
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!
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 09/18/2006 :  10:56:25  Show Profile
157MB + 7MB <> 28GB - am I misunderstanding something?

kristen
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 09/19/2006 :  01:02:52  Show Profile
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?

Edited by - snSQL on 09/19/2006 02:31:23
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 10/18/2006 :  15:53:38  Show Profile
There is a very good article on calculating and understanding tempdb space usage for SQL Server 2005 here
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000