tempdb data and log on separate devices

tempdb data and log on separate devices

Post by Ken Ringdah » Wed, 29 Apr 1998 04:00:00



In an effort to simplify things I made a mistake.  I put my tempdb data and
log on separate devices and apparently came across a bug in the process.
What happens now that I have put the tempdb log on a separate device is that
everytime I add more space to the tempdb whether it be to the log or the
data device it gets appended on to the log device.  I need to get more space
to my data device and have tried just about everything to get there and have
been unsuccessful.  If anybody knows how to overcome this or knows of a
painless way to recreate the tempdb with data and log on the same device
please do tell.

Thank You,
Ken Ringdahl
Wildfire Communications, Inc.

 
 
 

tempdb data and log on separate devices

Post by Kalen Delane » Wed, 29 Apr 1998 04:00:00


Are you using the GUI or the ALTER DATABASE Statement? I believe this
problem only shows up with the GUI in Enterprise Manager, and you expand
your data area with ALTER DATABASE.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>In an effort to simplify things I made a mistake.  I put my tempdb data and
>log on separate devices and apparently came across a bug in the process.
>What happens now that I have put the tempdb log on a separate device is
that
>everytime I add more space to the tempdb whether it be to the log or the
>data device it gets appended on to the log device.  I need to get more
space
>to my data device and have tried just about everything to get there and
have
>been unsuccessful.  If anybody knows how to overcome this or knows of a
>painless way to recreate the tempdb with data and log on the same device
>please do tell.

>Thank You,
>Ken Ringdahl
>Wildfire Communications, Inc.


 
 
 

tempdb data and log on separate devices

Post by Ken Ringdah » Thu, 30 Apr 1998 04:00:00


I've actually been minimally successful by making a new default device and
try to assign space to the data device using ALTER DATABASE but SQL Server
kind of makes it's own decision how much of the space I specified it wants
to give to the data segment.  Is there a way to rebuild the tempdb database
without reinstalling SQL Server because that's not really much of an option.

-Ken


>Are you using the GUI or the ALTER DATABASE Statement? I believe this
>problem only shows up with the GUI in Enterprise Manager, and you expand
>your data area with ALTER DATABASE.

>HTH

>--
>Kalen Delaney
>MCSE, SQL Server MCT, MVP


>>In an effort to simplify things I made a mistake.  I put my tempdb data
and
>>log on separate devices and apparently came across a bug in the process.
>>What happens now that I have put the tempdb log on a separate device is
>that
>>everytime I add more space to the tempdb whether it be to the log or the
>>data device it gets appended on to the log device.  I need to get more
>space
>>to my data device and have tried just about everything to get there and
>have
>>been unsuccessful.  If anybody knows how to overcome this or knows of a
>>painless way to recreate the tempdb with data and log on the same device
>>please do tell.

>>Thank You,
>>Ken Ringdahl
>>Wildfire Communications, Inc.

 
 
 

tempdb data and log on separate devices

Post by Ken Ringdah » Thu, 30 Apr 1998 04:00:00


I've actually been minimally successful by making a new default device and
try to assign space to the data device using ALTER DATABASE but SQL Server
kind of makes it's own decision how much of the space I specified it wants
to give to the data segment.  Is there a way to rebuild the tempdb database
without reinstalling SQL Server because that's not really much of an option.

-Ken


>Are you using the GUI or the ALTER DATABASE Statement? I believe this
>problem only shows up with the GUI in Enterprise Manager, and you expand
>your data area with ALTER DATABASE.

>HTH

>--
>Kalen Delaney
>MCSE, SQL Server MCT, MVP


>>In an effort to simplify things I made a mistake.  I put my tempdb data
and
>>log on separate devices and apparently came across a bug in the process.
>>What happens now that I have put the tempdb log on a separate device is
>that
>>everytime I add more space to the tempdb whether it be to the log or the
>>data device it gets appended on to the log device.  I need to get more
>space
>>to my data device and have tried just about everything to get there and
>have
>>been unsuccessful.  If anybody knows how to overcome this or knows of a
>>painless way to recreate the tempdb with data and log on the same device
>>please do tell.

>>Thank You,
>>Ken Ringdahl
>>Wildfire Communications, Inc.

 
 
 

tempdb data and log on separate devices

Post by Kalen Delane » Thu, 30 Apr 1998 04:00:00


You can rebuild the tempdb database by running sp_configure to put tempdb in
RAM (it doesn't matter what size, 2 MB is fine), stop and start SQL Server,
and the sp_configure to take tempdb out of RAM (set value back to 0). You'll
end up with the default 2 MB tempdb on the master or some default device.
Then you can try again to expand it, without separating the data from the
log.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP


>I've actually been minimally successful by making a new default device and
>try to assign space to the data device using ALTER DATABASE but SQL Server
>kind of makes it's own decision how much of the space I specified it wants
>to give to the data segment.  Is there a way to rebuild the tempdb database
>without reinstalling SQL Server because that's not really much of an
option.

>-Ken


>>Are you using the GUI or the ALTER DATABASE Statement? I believe this
>>problem only shows up with the GUI in Enterprise Manager, and you expand
>>your data area with ALTER DATABASE.

>>HTH

>>--
>>Kalen Delaney
>>MCSE, SQL Server MCT, MVP


>>>In an effort to simplify things I made a mistake.  I put my tempdb data
>and
>>>log on separate devices and apparently came across a bug in the process.
>>>What happens now that I have put the tempdb log on a separate device is
>>that
>>>everytime I add more space to the tempdb whether it be to the log or the
>>>data device it gets appended on to the log device.  I need to get more
>>space
>>>to my data device and have tried just about everything to get there and
>>have
>>>been unsuccessful.  If anybody knows how to overcome this or knows of a
>>>painless way to recreate the tempdb with data and log on the same device
>>>please do tell.

>>>Thank You,
>>>Ken Ringdahl
>>>Wildfire Communications, Inc.

 
 
 

tempdb data and log on separate devices

Post by Kevin Idding » Wed, 06 May 1998 04:00:00


I have experienced the same problem in the past.  Try expanding both Tempdb
Data and Tempdb Log simultaniously.  That is, after you press [Expand],
select a data device and a log device both.

Hope this helps...

Kevin Iddings