"Abraham" <binu_ca@yahoo.com> wrote in message
news:eADnDxBQDHA.1556@TK2MSFTNGP10.phx.gbl...
> Is there currently a way to cleanup the tempdb?
> Tried dbcc shrinkfile , but no luck.
> Can't restart the server ,because it's on production.
> What makes tempDB big ?
> and tempDB is in Simple mode , but not truncating it .
>
> Thanks
>
>

"Edgardo Valdez" <edgardovaldez@hotmail.com> wrote in message
news:034c01c34024$5c7eccc0$a501280a@phx.gbl...
> Could be using a lot of cursors in your stored procedures?
> If so, you might need to consider not relying so heavily
> on them. These take a lot of resources in TEMPDB. Also,
> make sure you DEALLOCATE them after you are done with them.
>
> Are you using a lot of TEMP tables, particular large ones
> holding SELECT * recordsets? A huge query loading a temp
> table with millions of records can fill up TEMPDB quickly.
> You always want to RESTRICT the size of recordsets in TEMP
> tables. Only include the columns and rows you actually
> need in the table, no more. To help speed these queries,
> be sure the AUTOSTATS database option is turned on, and
> then create one or more indexes on these temp tables that
> can be used by your query. In many cases, you will find
> that this can substantially speed up your application. But
> like many performance tips, be sure you test this one to
> see if it actually helps in your particular situation.
> Also, do not use SELECT INTO to create your temp table, as
> it places locks on system objects. Instead, create the
> table using using standard DDL statements, and then use
> INSERT INTO to populate the table. You should consider
> using a clustered and non-clustered indexes on your temp
> tables, especially for very large temp tables. You will
> have to test to see if indexes help or hurt overall
> performance. Also make sure you DROP your TEMP tables when
> you are finished them as well.
>
> If the temdb growing persists, I would consider putting
> tempdb database on its own dedicated disk or array.By
> isolating the tempdb database on its own disk, disk
> contention is reduced and performance is increased. Since
> the tempdb database does not need to be backed up, the
> tempdb database can be located on a single disk, or for
> best performance a RAID 0 array.
>
>
> >-----Original Message-----
> > Is there currently a way to cleanup the tempdb?
> >Tried dbcc shrinkfile , but no luck.
> >Can't restart the server ,because it's on production.
> >What makes tempDB big ?
> >and tempDB is in Simple mode , but not truncating it .
> >
> >Thanks
> >
> >
> >.
> >