shrink tempdb...

[unknown]

What is the best way to shorten an existing tempdb which is
5 Gb ? I plan to create two tempdbs (default tempdb with 3
Gb ) and additional tempdb with 2 Gb, but first i need to
shrink existing 5Gb tempdb to 3Gb.

[m***@peppler.org]

Post by unknown
What is the best way to shorten an existing tempdb which
is 5 Gb ? I plan to create two tempdbs (default tempdb
with 3 Gb ) and additional tempdb with 2 Gb, but first i
need to shrink existing 5Gb tempdb to 3Gb.

Assuming that tempdb is on filesystem device(s), I would
update the sysdevices and sysusages tables, then shut down
the dataserver, truncate the tempdb devices to free up the
space, then restart the data server.

Make sure that any updates to the system tables are done
within a transaction, and make sure that the update really
only affects the rows that you want to update before
committing...

Michael

[A.M.]

Post by unknown
What is the best way to shorten an existing tempdb which is
5 Gb ? I plan to create two tempdbs (default tempdb with 3
Gb ) and additional tempdb with 2 Gb, but first i need to
shrink existing 5Gb tempdb to 3Gb.

I have this script available -
http://www.peppler.org/~am/sos/sp_reset_tempdb.sql

It may not work correctly if you have an odd tempdb
configuration but the idea is fairly trivial. All you
need to do is ensure that the initial fragment(s) of
tempdb on the master device are active then remove the
other fragments in master..sysusages and bounce the
server. After you restart it, it should appear as a
default tempdb setup. Then rejig tempdb the way you
want it.

-am © MMVI

[unknown]

Post by A.M.

Post by unknown
What is the best way to shorten an existing tempdb which
is 5 Gb ? I plan to create two tempdbs (default tempdb
with 3 Gb ) and additional tempdb with 2 Gb, but first i
need to shrink existing 5Gb tempdb to 3Gb.

I have this script available -
http://www.peppler.org/~am/sos/sp_reset_tempdb.sql
It may not work correctly if you have an odd tempdb
configuration but the idea is fairly trivial. All you
need to do is ensure that the initial fragment(s) of
tempdb on the master device are active then remove the
other fragments in master..sysusages and bounce the
server. After you restart it, it should appear as a
default tempdb setup. Then rejig tempdb the way you
want it.
-am © MMVI

Also you will need to make sure that model db is <=
default tempdb.

Dat