Solved

TEMPDB missing sp

Posted on 2006-07-20
9
794 Views
Last Modified: 2012-08-13
After shrinking my tempdb that was 118 gig ...to 512 today its at 16 gigs...
Im looking at em on tempdb and look at sp...there are no sp there.

How to check why and put them back?
0
Comment
Question by:TRACEYMARY
9 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 300 total points
ID: 17145962
did you create stored procedures into the tempdb????
you should not do that, because tempdb gets recreated on each restart of sql server, which will clear EVERYTHING you put in there.
no way to recover, unless you have the scripts
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 17145974
There are so many things in SQL server which uses TempDb
1. Temp tables (both local and global)
2. table varriables (initially it will be created in Ram, then as the size grows this will be moved to tempdb )
3. Operations involving cursor
4, Group by, order by etc

and tempdb will be recreated after every restart of sql server service

0
 
LVL 27

Assisted Solution

by:ptjcb
ptjcb earned 100 total points
ID: 17146022
1) Shrinking tempdb should do nothing to whatever is active there. It is just reclaiming unused space.

2) I agree with angelIII - tempdb should not have user stored procedures. It is a scratch pad for the entire server and will grow because you have queries, create temporary tables, etc. It is recreated every time SQL Server restarts (it is one sure way to shrink tempdb) based on the size of your model db. Tempdb is always a simple recovery model. Stored procedures and functions should not be added to tempdb (unless you want to run the script to recreate them each time).
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17146163
No i did not create any sp I know best not to ....sorry i did not make clear what i was writing...

I mean there are no System SP there  I look on another SQL and i see
db_checkinobject for example but not on SQL2

Im getting
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space..
in all error logs but its not got no limit restrictions its on unlimited growth.

its the temp mdf thats grewing it was 1869 now its 16738 in a space of one day.



0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17146300
Should i create a job to run each night to shrink the log and mdf of tempdb

Thanks
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17146308
Oh ...i have the system sp......it just took 30 minutes to display them.........

0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 300 total points
ID: 17146380
>db_checkinobject
you don't need those, they are not really system, but come from Visual Studio (.net)

0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17146726
So what the best thing i can do here...

How can i really determine what the temp db has or was doing ...and while it grewing like it is.....?

Should i write the script to shrink the mdf and ldf every night...?
(I know i should not have to..)

So the only way tempdb recreates itself is shut down (which is not an option).

Cheers
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 17150706
I believe the checkpoint is not working ..isn't the checkpoint supposed to clear the tempdb
>
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now