Re: Tempdb after restart

From: Geoff N. Hiten (SRDBA_at_Careerbuilder.com)
Date: 02/03/05 

Date: Wed, 2 Feb 2005 19:33:20 -0500

You are better off manually growing tempdb to an appropriate size and
leaving it alone. If SQL has to auto-grow tempdb there are severe
performance penalties. 

-- 
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jabm" <Jabm@discussions.microsoft.com> wrote in message
news:996C69EA-8DAE-4DD5-98F8-076F7EA7F55C@microsoft.com...
>
> I'm going thruu a similar problem. I recently started msdtc on one my
> servers. The server neves was started before. Since it is running tempdb
> grown faster ( from 8mb to 14.2gb) . Is this normal?
> I know tempdb is critical for a good performance but.. Can I stop the
> services in oprder to recreate the table and then limit the table to a
> smaller size?
>
>
> "Geoff N. Hiten" wrote:
>
> > If you already have the data files in-place, it isn't a problem since
SQL
> > just overwrites the system tables and GAM/SGAM pages.  Kinda like a
quick
> > disk format.  If you shrink the files, which reqiures a server
stop/start,
> > then the server will be slower if tempdb autogrows back to that size due
to
> > application data allocations.
> >
> > -- 
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> > I support the Professional Association for SQL Server
> > www.sqlpass.org
> >
> > "David Bobko" <DavidBobko@discussions.microsoft.com> wrote in message
> > news:AC7C7090-1CB2-433E-9552-898EEA322774@microsoft.com...
> > > Documentation states that the tempdb is recreated each time the server
is
> > > started. If I had the tempdb set to 10gigs initial size, would it
cause
> > the
> > > server to be slower after the restart while it grew that database to
the
> > > 10gigs in size?
> >
> >
> >

Relevant Pages

  • Re: sql service starts then stops after master restore
    ... Maybe the folders where SQL Server tries to create the tempdb files do not ... Delete old file after restarting SQL ... > after I restore master db on another server (msg pops up and says restore ...
    (microsoft.public.sqlserver.server)
  • Re: Tempdb Log
    ... databases (including tempdb) using the simple recovery model on our SQL ... Server 2000 sp3 on Windows 2003. ...
    (microsoft.public.sqlserver.server)
  • JDBC & Tempdb file growth
    ... Yesterday our tempdb exceded the remaining space on its drive, and our SQL ... server instance became incapacitated. ... I understand that this file grows as needed, and when the SQL server ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: User deleted
    ... tempdb, because the tempdb database is completely recreated every time SQL ... Server is started. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • RE: Fulltext failure on a 2 node cluster
    ... Server full-text search resource online: "SQL Cluster Resource 'Full Text' ...
    (microsoft.public.sqlserver.clustering)
Loading