July 21, 2008 at 1:24 pm
How can we handle the situation if the tempdb space was full?
reply with more than 2 diff answers?
July 21, 2008 at 1:43 pm
you need to alter the tempdb database in this case(means need to increase the size of DB) from the query analysir, in this situation you might see all the DBs would disappear. But dont worry it would come back once temp db is normal.
July 21, 2008 at 6:11 pm
The real problem is that you need to identify why it is full, first.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 22, 2008 at 12:40 am
rames.net (7/21/2008)
reply with more than 2 diff answers?
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2008 at 12:56 am
TEMP DB FULL:
first know the reason why it is full then shrink the
temp db,or u can add one more temp file to that from
MMS.:P
July 22, 2008 at 6:18 am
We also need to know what you think "full" is... how many bytes is your TempDB?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2008 at 4:50 am
Is tempdb set to autogrow? If not, that might be the issue as well. One application I support makes liberal use of temp tables. A guy I knew at another company using the same app was complaining about tempdb filling up. It turned out the DBA's at that company restricted tempdb to 50MB, and refused to let it autogrow, giving some specious reason. It took months to get htem to autogrow tempdv, at which point, the issues all went away.
Rebooting the servers on a regular schedule helps as well, as tempdb gets cleaned out during the startup process.
July 23, 2008 at 6:16 am
Heh... 50MB for a TempDB... we start our TempbDB up at 12 Gig!
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2008 at 6:29 am
The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.
July 23, 2008 at 6:37 am
Sounds to me like an exam question or homework. Otherwise why ask for 2 different answers. I would suggest that the OP read their course material and find the answers that way... They will likely learn more that way.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 23, 2008 at 6:43 am
Jeff Moden (7/21/2008)
The real problem is that you need to identify why it is full, first.
As Jeff said, this is key. I've seen adhoc queries fill tempdb and bring the system to a halt. The genius that intalled SQL when that incident occurred, put tempdb on the c:\ drive and it ultimately filled the disk. When I assumed the role of DBA, things got moved to appropriate physical partitions with plenty of room for growth and setup appropriate monitoring of the system. There are plenty of processes that utilize tempdb, index rebuilds being but one. I don't think we can give solid advice unless we know what/how it filled up. Restarting the SQL service will recreate tempdb but that's a short term fix as I'm sure whatever filled it up once will execute again, putting you in the exact same situation.
Edit - All my tempdb databases are minimum of 5GB
-- You can't be late until you show up.
July 23, 2008 at 6:43 am
Ross McMicken (7/23/2008)
The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.
On my main system, TempDB is 6 files of 8 GB each.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2008 at 6:45 am
Ross McMicken (7/23/2008)
The DBA's that specified the 50MB were of the opinion that aproperly designed application doesn't need more tempdb. I was glad I didn't have to deal with them. Our tempdb's typically run 3-6GB.
Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2008 at 6:48 am
GilaMonster (7/23/2008)
On my main system, TempDB is 6 files of 8 GB each.
Now THAT sounds like a fun system to be on and that the DBA (you, Gail?) might actually have his/her ducks lined up.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 23, 2008 at 6:48 am
Jeff Moden (7/23/2008)
Wow... That's amazing, Ross... I haven't heard anything like that in a long time... please tell me those DBA's weren't actually certified...
Depends. Do you mean certified as in qualified or certified as in insane?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 57 total)
You must be logged in to reply to this topic. Login to reply