shrinking data file of tempdb

shrinking data file of tempdb

Post by Aviel Ilu » Sat, 28 Oct 2000 17:56:55

my tempdb database data file is very large although the space used for data
is very small. (2GB vs. 8MB).
i tried to use DBCC SHRINKFILE and DBCC SHRINKDATABASE with and without
nothing changed. the file is still very large.
i tried to shutdown the server, move the tempdb.mdf file to another disk,
and restart the server again, and still i got the same size of tempdb.mdf.
what can i do to shrink it or to rebuild the tempdb database with small
datafile (tempdb.mdf)

Aviel Iluz, DBA
Bashan Systems
Jerusalem, Israel


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


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. tell me the method to access database?

3. Is it save to shrink the TEMPDB files??

4. Is there any public domain ISAM package available?

5. TEMPDB Shrinking while selecting data via MFC CRecordset

6. SQL Server 7.0 machine changed to workgroup.

7. Shrinking data file and log file

8. Growth of Tempdb database

9. Database file shrink if auto-shrink off

10. Shrink Data File

11. Failed to shrink data file in sql server

12. Can I shrink a Primary Data File?

13. Can't Shrink Data File