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!!
by Tuan » Sun, 31 Mar 2002 04:05:26
Thanks!!
by Tuan » Sun, 31 Mar 2002 05:16:39
Never mind!! I found the solution for it from TechnetQuote:>-----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!!
>.
by Shri Anandpura [M » Wed, 10 Apr 2002 01:28:08
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
3. shrinking data file of tempdb
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
9. TempDB can not be shrunk!!!
12. Shrinking Tempdb