shrinking tempdb without tempdb existing

shrinking tempdb without tempdb existing

Post by <nntp> » Fri, 13 Jun 2003 20:20:13



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

 
 
 

shrinking tempdb without tempdb existing

Post by Aaron Bertrand - MV » Fri, 13 Jun 2003 20:51:46


I wouldn't rely on this method... in other words, anytime you use it, be
prepared to rebuild your server.  Having enough disk space for the default
allocation size of tempdb would be a good place to start, rather than
hand-messing system tables.


> 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


 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Fri, 13 Jun 2003 21:37:10


Hmm, we're talking about a very large tempdb here, 100+GB.   That penalty
seems rather extreme for something that is clearly a simple configuration in
sysaltfiles.



> I wouldn't rely on this method... in other words, anytime you use it, be
> prepared to rebuild your server.  Having enough disk space for the default
> allocation size of tempdb would be a good place to start, rather than
> hand-messing system tables.


> > 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

 
 
 

shrinking tempdb without tempdb existing

Post by Aaron Bertrand - MV » Fri, 13 Jun 2003 22:08:29


Your tempdb starts at 100gb?  That's extremely odd...


> Hmm, we're talking about a very large tempdb here, 100+GB.   That penalty
> seems rather extreme for something that is clearly a simple configuration
in
> sysaltfiles.

 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Fri, 13 Jun 2003 22:24:24


Well, it's either that or suffer the pain of growing it after every restart.



> Your tempdb starts at 100gb?  That's extremely odd...


> > Hmm, we're talking about a very large tempdb here, 100+GB.   That
penalty
> > seems rather extreme for something that is clearly a simple
configuration
> in
> > sysaltfiles.

 
 
 

shrinking tempdb without tempdb existing

Post by Aaron Bertrand - MV » Fri, 13 Jun 2003 22:27:27


I'm just curious why you (think you) need 100 gb of tempdb space.  That
seems like either overly cautious or a system that relies far too heavily on
tempdb...


> Well, it's either that or suffer the pain of growing it after every

restart.
 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Sat, 14 Jun 2003 00:00:05


Aaron, it's a data warehouse - 100GB tempdb is required because that's the
size of sorts a variety of queries need to do.   It has nothing to do with
"an application's" use of tempdb, the space used in TEMPDB is dominated by
sql server using it to perform sorts for very very very large queries (like
1B rows)



> I'm just curious why you (think you) need 100 gb of tempdb space.  That
> seems like either overly cautious or a system that relies far too heavily
on
> tempdb...


> > Well, it's either that or suffer the pain of growing it after every
> restart.

 
 
 

shrinking tempdb without tempdb existing

Post by Dinesh.T. » Sat, 14 Jun 2003 00:05:51


Hi,

In that case, some denormalization would help reduce the number of joins and
thus minimise tempdb usage.

--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com


> Aaron, it's a data warehouse - 100GB tempdb is required because that's the
> size of sorts a variety of queries need to do.   It has nothing to do with
> "an application's" use of tempdb, the space used in TEMPDB is dominated by
> sql server using it to perform sorts for very very very large queries
(like
> 1B rows)



> > I'm just curious why you (think you) need 100 gb of tempdb space.  That
> > seems like either overly cautious or a system that relies far too
heavily
> on
> > tempdb...


> > > Well, it's either that or suffer the pain of growing it after every
> > restart.

 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Sat, 14 Jun 2003 00:11:28


trees... forest.  The tempdb size is not the problem to solve.  Pick any
size of tempdb.

I'm really just looking for a solution to the original problem, that is,
shrinking tempdb while the volume is unavailable.

I'm thinking using alter file to change the location, stoppping it, and then
restarting sqlservr.exe -c -f  to force it back to 1 MB, then doing alter
file... that MIGHT work, I'll do some testing.  That would at least be a
supported option.


> Hi,

> In that case, some denormalization would help reduce the number of joins
and
> thus minimise tempdb usage.

> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com


> > Aaron, it's a data warehouse - 100GB tempdb is required because that's
the
> > size of sorts a variety of queries need to do.   It has nothing to do
with
> > "an application's" use of tempdb, the space used in TEMPDB is dominated
by
> > sql server using it to perform sorts for very very very large queries
> (like
> > 1B rows)



> > > I'm just curious why you (think you) need 100 gb of tempdb space.
That
> > > seems like either overly cautious or a system that relies far too
> heavily
> > on
> > > tempdb...


> > > > Well, it's either that or suffer the pain of growing it after every
> > > restart.

 
 
 

shrinking tempdb without tempdb existing

Post by Dinesh.T. » Sat, 14 Jun 2003 00:20:44


Hi,

 ..If 100GB tempdb size is okay with you, then its fine :-).I didnt track
the thread all the way back and thus I missed the original request.

You can get more details about alter file command in this article :

HOW TO: Shrink the Tempdb Database in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;307487

--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com


> trees... forest.  The tempdb size is not the problem to solve.  Pick any
> size of tempdb.

> I'm really just looking for a solution to the original problem, that is,
> shrinking tempdb while the volume is unavailable.

> I'm thinking using alter file to change the location, stoppping it, and
then
> restarting sqlservr.exe -c -f  to force it back to 1 MB, then doing alter
> file... that MIGHT work, I'll do some testing.  That would at least be a
> supported option.



> > Hi,

> > In that case, some denormalization would help reduce the number of joins
> and
> > thus minimise tempdb usage.

> > --
> > Dinesh.
> > SQL Server FAQ at
> > http://www.tkdinesh.com


> > > Aaron, it's a data warehouse - 100GB tempdb is required because that's
> the
> > > size of sorts a variety of queries need to do.   It has nothing to do
> with
> > > "an application's" use of tempdb, the space used in TEMPDB is
dominated
> by
> > > sql server using it to perform sorts for very very very large queries
> > (like
> > > 1B rows)



> > > > I'm just curious why you (think you) need 100 gb of tempdb space.
> That
> > > > seems like either overly cautious or a system that relies far too
> > heavily
> > > on
> > > > tempdb...




- Show quoted text -

Quote:> > > > > Well, it's either that or suffer the pain of growing it after
every
> > > > restart.

 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Sat, 14 Jun 2003 00:44:23


Oops, last part was:


> Hi,

>  ..If 100GB tempdb size is okay with you, then its fine :-).I didnt track
> the thread all the way back and thus I missed the original request.

> You can get more details about alter file command in this article :

> HOW TO: Shrink the Tempdb Database in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;307487

> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com


> > trees... forest.  The tempdb size is not the problem to solve.  Pick any
> > size of tempdb.

> > I'm really just looking for a solution to the original problem, that is,
> > shrinking tempdb while the volume is unavailable.

> > I'm thinking using alter file to change the location, stoppping it, and
> then
> > restarting sqlservr.exe -c -f  to force it back to 1 MB, then doing
alter
> > file... that MIGHT work, I'll do some testing.  That would at least be a
> > supported option.



> > > Hi,

> > > In that case, some denormalization would help reduce the number of
joins
> > and
> > > thus minimise tempdb usage.

> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com




- Show quoted text -

> > > > Aaron, it's a data warehouse - 100GB tempdb is required because
that's
> > the
> > > > size of sorts a variety of queries need to do.   It has nothing to
do
> > with
> > > > "an application's" use of tempdb, the space used in TEMPDB is
> dominated
> > by
> > > > sql server using it to perform sorts for very very very large
queries
> > > (like
> > > > 1B rows)



> > > > > I'm just curious why you (think you) need 100 gb of tempdb space.
> > That
> > > > > seems like either overly cautious or a system that relies far too
> > > heavily
> > > > on
> > > > > tempdb...



> > > > > > Well, it's either that or suffer the pain of growing it after
> every
> > > > > restart.

 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Sat, 14 Jun 2003 00:45:13


Gosh I hate outlook express...

Last part was to modify sysaltfiles...

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

But I'm thinking of stopping and restarting sql with -c -m and THEN using
modify file, that might work


> Hi,

>  ..If 100GB tempdb size is okay with you, then its fine :-).I didnt track
> the thread all the way back and thus I missed the original request.

> You can get more details about alter file command in this article :

> HOW TO: Shrink the Tempdb Database in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;307487

> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com


> > trees... forest.  The tempdb size is not the problem to solve.  Pick any
> > size of tempdb.

> > I'm really just looking for a solution to the original problem, that is,
> > shrinking tempdb while the volume is unavailable.

> > I'm thinking using alter file to change the location, stoppping it, and
> then
> > restarting sqlservr.exe -c -f  to force it back to 1 MB, then doing
alter
> > file... that MIGHT work, I'll do some testing.  That would at least be a
> > supported option.



> > > Hi,

> > > In that case, some denormalization would help reduce the number of
joins
> > and
> > > thus minimise tempdb usage.

> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com




- Show quoted text -

> > > > Aaron, it's a data warehouse - 100GB tempdb is required because
that's
> > the
> > > > size of sorts a variety of queries need to do.   It has nothing to
do
> > with
> > > > "an application's" use of tempdb, the space used in TEMPDB is
> dominated
> > by
> > > > sql server using it to perform sorts for very very very large
queries
> > > (like
> > > > 1B rows)



> > > > > I'm just curious why you (think you) need 100 gb of tempdb space.
> > That
> > > > > seems like either overly cautious or a system that relies far too
> > > heavily
> > > > on
> > > > > tempdb...



> > > > > > Well, it's either that or suffer the pain of growing it after
> every
> > > > > restart.

 
 
 

shrinking tempdb without tempdb existing

Post by <nntp> » Sat, 14 Jun 2003 00:43:46


Ah, ok, then the original problem is thus:

I need to shrink it when the volume tempdb resides on is not available, so
my original solution was:

A) start sql
E:\MSSQL\Binn>sqlservr.exe -f -T3608

B) Change location
use master
go
Alter database tempdb
modify file (name = tempdev, filename = 'e:\mssql\data\tempdb.mdf')
go

Alter database tempdb
modify file (name = templog, filename = 'f:\mssql\data\templog.ldf')<NOTE:
HERE YOU CANNOT SHRINK IT BECAUSE ALTER FILE WILL NOT LET YOU....>lastly:


> Hi,

>  ..If 100GB tempdb size is okay with you, then its fine :-).I didnt track
> the thread all the way back and thus I missed the original request.

> You can get more details about alter file command in this article :

> HOW TO: Shrink the Tempdb Database in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;307487

> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com


> > trees... forest.  The tempdb size is not the problem to solve.  Pick any
> > size of tempdb.

> > I'm really just looking for a solution to the original problem, that is,
> > shrinking tempdb while the volume is unavailable.

> > I'm thinking using alter file to change the location, stoppping it, and
> then
> > restarting sqlservr.exe -c -f  to force it back to 1 MB, then doing
alter
> > file... that MIGHT work, I'll do some testing.  That would at least be a
> > supported option.



> > > Hi,

> > > In that case, some denormalization would help reduce the number of
joins
> > and
> > > thus minimise tempdb usage.

> > > --
> > > Dinesh.
> > > SQL Server FAQ at
> > > http://www.tkdinesh.com




- Show quoted text -

> > > > Aaron, it's a data warehouse - 100GB tempdb is required because
that's
> > the
> > > > size of sorts a variety of queries need to do.   It has nothing to
do
> > with
> > > > "an application's" use of tempdb, the space used in TEMPDB is
> dominated
> > by
> > > > sql server using it to perform sorts for very very very large
queries
> > > (like
> > > > 1B rows)



> > > > > I'm just curious why you (think you) need 100 gb of tempdb space.
> > That
> > > > > seems like either overly cautious or a system that relies far too
> > > heavily
> > > > on
> > > > > tempdb...



> > > > > > Well, it's either that or suffer the pain of growing it after
> every
> > > > > restart.

 
 
 

1. moving tempdb and starting db when tempdb is lost

I have increased the size of my tempdb database to accomodate a large number
of users.  I created a device file for the new portion and expanded tempdb
onto it.  Now I have two questions and was wondering if anyone could help
1) How do I start SQL Server if I lose the second device file.  (I removed it
on purpose from my test database and it will not start)

2) How can I completely move the tempdb database off of the master device so
it resides completely on its own device

Any help would be appreciated

Damian Yenzi
Federated Investors

2. Unify

3. Performance/TempDB Config Issue: Sorting Queries and TempDB

4. Red Brick and Datastage

5. Optimizing tempdb Performance, Using filegroups with tempdb

6. Preparing for Interview

7. Shrink tempdb

8. shrink tempdb

9. Bug? Expanding and shrinking tempdb

10. Shrink TempDB