Is it save to shrink the TEMPDB files??

Is it save to shrink the TEMPDB files??

Post by Tuan » Sun, 31 Mar 2002 04:05:26



My tempdb MDF and LDF files are going so big now.  
Especially the tempdb.mdf is now at 13GB.  I am going out
of space very soon.  Is there a save way to shrink the
tempdb.mdf file?

Thanks!!

 
 
 

Is it save to shrink the TEMPDB files??

Post by Tuan » Sun, 31 Mar 2002 05:16:39


Quote:>-----Original Message-----
>My tempdb MDF and LDF files are going so big now.  
>Especially the tempdb.mdf is now at 13GB.  I am going out
>of space very soon.  Is there a save way to shrink the
>tempdb.mdf file?

>Thanks!!
>.

Never mind!!  I found the solution for it from Technet
already.  

 
 
 

Is it save to shrink the TEMPDB files??

Post by Shri Anandpura [M » Wed, 10 Apr 2002 01:28:08


Please refer to the following link for more information

Shrink TEMPDB
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q307487

Thanks
Shri Anandpura

This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.

--------------------
| Content-Class: urn:content-classes:message



| Subject: Is it save to shrink the TEMPDB files??
| Date: Fri, 29 Mar 2002 12:16:39 -0800
| Lines: 12

| MIME-Version: 1.0
| Content-Type: text/plain;
|       charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcHXXqIj3tOkU+0FSiCy2t3WDSbRaA==
| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa07
| Xref: cpmsftngxa07 microsoft.public.sqlserver.server:204416
| NNTP-Posting-Host: TKMSFTNGXA03 10.201.232.162
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
|

Quote:| >-----Original Message-----
| >My tempdb MDF and LDF files are going so big now.  
| >Especially the tempdb.mdf is now at 13GB.  I am going out
| >of space very soon.  Is there a save way to shrink the
| >tempdb.mdf file?
| >
| >Thanks!!
| >.
| >
| Never mind!!  I found the solution for it from Technet
| already.  
|

 
 
 

1. shrinking tempdb without tempdb existing

We have a recovery scenario we are documenting where the volume that tempdb
exists on becomes unavailable.

We have supported ways to startup sql in the norecovery mode, and use alter
file to move the tempdb files, however, in some cases we may not have enough
disk space to let SQL recreate tempdb on the new volumes, and alter file
will not allow you to specify a new, smaller file, so I've developed a
procedure to modify sysaltfiles.

I understand this may be a bit risky, but I'm looking for feedback from
MVP's or micrsoft on the wether or not this is viable.  I did test it and it
seems to work fine.

Here is the script.  Any feedback would be appreciated
exec sp_configure 'allow updates', 1

reconfigure with override

go

update master.dbo.sysaltfiles

set size = 64000 -- (500 MB, size in 8KB pages)

where name = 'tempdev'

and dbid = db_id('tempdb')

exec sp_configure 'allow updates', 0

reconfigure with override

2. DBGrid Multi-Select Errors

3. shrinking data file of tempdb

4. Error indexing pdf files

5. Database file shrink if auto-shrink off

6. Help

7. Shrinking Veritas QIO files for Oracle backups or migrating QIO files to reg files

8. Handling Terminals

9. TempDB can not be shrunk!!!

10. tempdb wont't shrink

11. How to Shrink the tempdb?

12. Shrinking Tempdb

13. How to shrink the tempdb