Placing objects, making changes, users etc. within the model database will place these within each new user database you create on the server.

Right click on object in EM to script...

remember dts runs as an external application and running lots of them together may shrink the available resource on the server.

If you're moving data across the network then that may...

I'd really recommend you don't use main plans and use t-sql within a scheduled job.

I think there's already been a thread on shrinking databases, but I generally don't do this...

there are a number of articles from ms about things which cause procedure recompiles during execution, #temp tables are one of them  see  Q243586

select into can cause all sorts of problems with...

sp_who2 active will list only active spids.

I used a proc that killed spids based upon the last batch run time ( just to explain there was an app which opened...

as I understand it both #temp tables and @table vars may be created in memory or disk -depends on size and how much memory you have available I guess.

Using...

You can still develop the code in QA for a proc and results/plan will be the same ( for the same data )

To really see what is happening you...

It was such a simple question  

The upshot was as I suspected that on w2k ( adv server ) the 8 cpu limit would not allow...

It was such a simple question  

The upshot was as I suspected that on w2k ( adv server ) the 8 cpu limit would not allow...

Yes I should have made clear that changing recovery model will loose the transaction log - you need to make a full backup before / after such a change.

It's one of the downsides of a 24x7 that the housekeeping becomes difficult. Careful design ( of database structures ) to minimise fragmentation will allow the use of online defrag...

Yes I'm aware of possible issues with HT,  but to date ( nearly 2 years using zeon 4's ) I've not encountered any problems.

Yes I agree w2003 is a...

a profiler trace against tempdb will get that info. Generally sorts ( order by ) dictincts, group by, temp table creation will hit tempdb.

tempdb use is generally quite normal,...

generally your experience goes against what I'd normally expect.

I assume you're running with sql2k ?

All I know states that if you replace parameters with local variables the proc will run...

Thanks - as I say in a strongly controlled environment you can't just roll out changes globally !!

The problem may well have been code - well it's always the database...