Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 1st, 2003, 01:38 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default TempDB Size factor - Cleanup procedure

Hi All there,

Here is my case.

The size of TempDB grew up suddenly to around 4 GB and was not clearing off. We were worried about insufficient disk space in case it still grows up more. It needed sql service restart for the clearing unused space. I assume, as it grows by some percent as set as default, there should be a way that it gets cleared off which can also be set somewhere. But in my case it grew up and was not clearing off, I had to either do a shrink operation or sql service restart. Since we had a maintenance work scheduled, for other reasons, we had a chance of restarting the sql service and got rid of it, where the downtime was max to 2 mins. Now the size of it is only 200+ MB. But in future we donot want to have this kind of downtime. So please suggest, is there a way other than shrink or sql service restart to clean up the TempDB periodically, so that we dont have to bother much on this.

Thanks much in advance

Regards,
Vijay G
__________________
- Vijay G
 
Old October 7th, 2003, 12:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi All there,

Is anyone there to suggest on this???

Thanks,
Vijay G
 
Old October 7th, 2003, 02:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Is anyone trying to atleast look at this?

 
Old October 7th, 2003, 03:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

TempDB is recreated every time the server is (re)started. Since it is never backed up, there is no capability to autoshrink it.

The only way I know to reclaim space if the file in which tempdb is located grows above its allocated size is to recreate the tempdb datbase, and the only way to do that is a restart...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
 
Old October 7th, 2003, 03:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Jeff,

Thanks much for the reply.

I thought it would be ideal to set a job that runs periodically, say monthly once, to shrink it to some percent of its original size then.

Does a "Dump Tran" on this have any effect on this database in anyways?

-Vijay
 
Old October 7th, 2003, 03:30 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by happygv
 Does a "Dump Tran" on this have any effect on this database in anyways?

Not that I know of.

Since TempDB is temporary and thus never needs to be recovered, dumping the transactions would do no good (there is nothing to restore).

You might want to look instead into why it gets so big. You are creating very large temporary tables, doing some very large sorts, or creating some very large server side cursors.

If you really need to do this stuff that is so big, then you really need to do it, so you will need the temporary space when you do do it, so you really can't avoid the growth. So, the question is, do you really need to do it? :)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
tempdb problem aldwinenriquez SQL Server 2000 2 December 21st, 2005 04:24 PM
Tempdb is full koushikroy2001 SQL Server 2000 0 August 5th, 2005 05:42 AM
RESOLVED - cleanup after launching Excel App cliffd64 VB.NET 2002/2003 Basics 2 June 7th, 2005 02:52 PM
Merge Replication Metadata cleanup MSDE? dhay1999 SQL Server 2000 0 February 14th, 2005 02:52 PM
Fill Factor and Pad Index bmains SQL Server 2000 0 February 3rd, 2004 02:15 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.