Tempdb Size help

[GJP55]

How do I reduce the size of my Tempdb database ?. I know that stopping and starting SQL will automatically reset this but this is not practicle during office hours.

We have a reporting system that is used throughout the day which creates temp tables and then drops them.

With multiple users running reports throughout the day, this quickly gets full up. Its size is 1200MB at the moment but what I could do with is resetting this every 30 minutes are so. I have a shrink Database job but this doesnt seem to make any difference to the indicated size when I run this.

Many thanks.

 

[SQLBill]

Shrinking a database (or log) is not instantaneous. It depends on where the active portion is located at. There's a good description in the BOL (refer to Transaction Log). With the TEMPDB, you would probably benefit with truncating the log and then shrinking it. (BACKUP LOG TempDB WITH TRUNCATE_ONLY).

Refer to the BOL for more information.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[GJP55]

SQLBill,

Thanks for your advice and reply. I have ran the BACKUP LOG TempDB WITH TRUNCATE_ONLY and then ran the Shrink Database option. When I look at the database size again, it is still the same size (slightly less by 500kb in fact). How do I determine where the active protion is located?.
My DBA skills on SQL are not really that developed at the moment.

Thanks