Very Computer

> 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

> 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

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

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

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

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

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

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

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

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

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

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

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

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

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