Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 814
  • Last Modified:

TEMPDB missing sp

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
TRACEYMARY
Asked:
TRACEYMARY
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
ptjcbCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
TRACEYMARYAuthor Commented:
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
 
TRACEYMARYAuthor Commented:
Should i create a job to run each night to shrink the log and mdf of tempdb

Thanks
0
 
TRACEYMARYAuthor Commented:
Oh ...i have the system sp......it just took 30 minutes to display them.........

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>db_checkinobject
you don't need those, they are not really system, but come from Visual Studio (.net)

0
 
TRACEYMARYAuthor Commented:
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
 
TRACEYMARYAuthor Commented:
I believe the checkpoint is not working ..isn't the checkpoint supposed to clear the tempdb
>
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now