Hi,

If there are non comited transactions the log file of the tempdb can't be
shrunk. It grows as tranactions are being performed on the other db's
(think of it like the 'working' space for all databases). Any heavy sorts
on/for other db's increase the tempdb. To shrink the tmpdb you might wonna
force a checkpoint (on all databases) and then a dbcc shrinkdatabase
truncateonly. Check Books on Line for precise synthax for DBCC
Shrinkdatabase/file and the options for the truncate.

Btw: the tempdb is recreated after your model database. What is the size of
your model database?

Hope this helps,

Met Vriendelijke Groet/With kind Regards,
Marcel J Schillemans




"Sherman, Bill"
<[Email Address Removed] To: "Bill King" <[Email Address Removed] "Deepak Nair"
com> <Deepak.[Email Address Removed] <[Email Address Removed]
cc:
19-06-02 23:54 Subject: RE: Problem with tempdb on MS SQL 7
Please respond to
"Sherman, Bill"





Try sp_detach_db , rename the log file then sp_attach_Db.

If you leave the logfile name off of the attach it will create a new one.

-----Original Message-----
From: Bill King [mailto:[Email Address Removed] Wednesday, June 19, 2002 2:35 PM
To: Deepak Nair; [Email Address Removed] RE: Problem with tempdb on MS SQL 7

No, currently there are only system tables in there.

The properties of tempdb show this:
size: 16928.42 MB
space available: 16924.83 MB

IE it is all empty space, and the shrink database doesn't really change it.

- BILL -

-----Original Message-----
From: Deepak Nair [mailto:Deepak.[Email Address Removed] Wednesday, June 19, 2002 3:16 PM
To: '[Email Address Removed] Deepak Nair; [Email Address Removed] RE: Problem with tempdb on MS SQL 7



These are temp tables created in tempdb.if you give

select * into #tmp from <Table Name>

this will create a #tmp table in Tempdb.

Deepak
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Wednesday, June 19, 2002 12:38 PM
To: Deepak Nair; [Email Address Removed] RE: Problem with tempdb on MS SQL 7


I don't recognize that. How would I check? In the mean time I will look
up
# tables.

- BILL -

-----Original Message-----
From: Deepak Nair [mailto:Deepak.[Email Address Removed] Wednesday, June 19, 2002 12:39 PM
To: '[Email Address Removed] RE: Problem with tempdb on MS SQL 7


Are there any processes running which puts rows into # or ## tables.Please
check the usage of # tables in queries.

Deepak

-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] Wednesday, June 19, 2002 8:04 AM
To: [Email Address Removed] Problem with tempdb on MS SQL 7


hello all.

This is my first posting.

My problem is this: My tempdb is 17 Gig and growing. When SQL restarts it
does not seem to "start over" with a fresh temp db.

I have tried shrinking the database and it successfully goes down about 100
MG (sometimes.)

I have had the hosting company stop the service remove the files and
restart. It doesn't take long to go right back up to 10 Gig+

Any thoughts? Anything I can check on or look for that might be causing
this?

- Bill King -


---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page