Is rebooting server only way to clear tempdb

I am currently doing some development work on SQL Server 2000, and am
one of only four people using the server. However, the server had to
be restarted because the tempdb was over 11GB in size (the main
databases were a little over 200MB). Having searched through Google,
the only way I can see to reduce the size of tempdb is to stop and
restart the server.
Is this really what is happening in large organisations around the
world. After all, I am talking about a development server with 4
users, not a large organisation with hundreds or even thousands of
users.I'm running several servers in the benelux and never had to restart one
because of an oversized tempdb.
I think you should try to find out the reason why the tempdb has increased
that much.
Restarting your server recreates your tempdb from the model database.
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Sean Wilson" <sean.wilson@.dsdni.gov.uk> wrote in message
news:8f31c2cd.0402270156.151ef203@.posting.google.com...
> I am currently doing some development work on SQL Server 2000, and am
> one of only four people using the server. However, the server had to
> be restarted because the tempdb was over 11GB in size (the main
> databases were a little over 200MB). Having searched through Google,
> the only way I can see to reduce the size of tempdb is to stop and
> restart the server.
> Is this really what is happening in large organisations around the
> world. After all, I am talking about a development server with 4
> users, not a large organisation with hundreds or even thousands of
> users.|||Hey,
I can think of a couple of things to try, depending on what part is 11Gb in
size.
If the Log file is 11Gb, then check that the Recovery Model is set to Simple
, which should be the default value. TempDB doesn't pick this value up from
the 'model' DB at start-up.
The second thing to do is check the length of the longest transaction, becau
se if some process is holding it open, this can stop the transaction log fro
m being truncated when a check point occurs.
If the problem is the data file, the you can schedual a shinking of it.
Let us know what you find as the cause.|||I would suggest that U set up/ ask your DBA to set up an alert notfication t
hat monitors TempDB threshold . . . .in order to temporarily reduce the size
of tempdb you could just run dbcc shrinkdatabase with truncateonly option(C
heck BOL) depending you ind
icate the target percentage will determine the final size of the datafiles a
nd database at the end of this process, all unused extents are removed (to t
he target percentage specified) and the space recovered is returned to the
operating system.|||Simply restarting the SQL services will recreate your tempdb.
Ray Higdon MCSE, MCDBA, CCNA
--
"Sean Wilson" <sean.wilson@.dsdni.gov.uk> wrote in message
news:8f31c2cd.0402270156.151ef203@.posting.google.com...
> I am currently doing some development work on SQL Server 2000, and am
> one of only four people using the server. However, the server had to
> be restarted because the tempdb was over 11GB in size (the main
> databases were a little over 200MB). Having searched through Google,
> the only way I can see to reduce the size of tempdb is to stop and
> restart the server.
> Is this really what is happening in large organisations around the
> world. After all, I am talking about a development server with 4
> users, not a large organisation with hundreds or even thousands of
> users.|||You may want to check out:
http://support.microsoft.com/defaul...b;en-us;Q307487
John
"Sean Wilson" <sean.wilson@.dsdni.gov.uk> wrote in message
news:8f31c2cd.0402270156.151ef203@.posting.google.com...
> I am currently doing some development work on SQL Server 2000, and am
> one of only four people using the server. However, the server had to
> be restarted because the tempdb was over 11GB in size (the main
> databases were a little over 200MB). Having searched through Google,
> the only way I can see to reduce the size of tempdb is to stop and
> restart the server.
> Is this really what is happening in large organisations around the
> world. After all, I am talking about a development server with 4
> users, not a large organisation with hundreds or even thousands of
> users.|||Thanks for all the replies.
John - I had already come across this one from another posting and
noted it, thanks.
Ray - This is what I had considered suggesting to the 'powers above'
as a scheduled task.
Al - Recovery mode is Simple (should have stated this in original
post). Will have to check on the transactions.