Moving Second Tempdb Device?
Apr 24, 2000
Hello:
We are supporting an mssql 6.5, sp 4 application. My associate recently
increased tempdb from 3 gb to 15 gb by adding a 12gb device on a second drive.
The original tempdb device was 3 gb on an h drive and the 12gb addition was added to the f drive limiting the disk space for the data dat file to be expanded. So now I would like to move that second tempdb device to a different drive to free up disk space on the f drive.
1) Can I move the second tempdb device?
2) Or should I try to shrink it and then expand it on a differetn drive?
3) Any other suggestions?
Any information you can provide will be greatly appreciated.
THanks.
David
View 1 Replies
ADVERTISEMENT
Nov 17, 1998
I have a database in development in SQL Server 6.5 that needs to be occasionally deleted and rebuilt from a script when table structures are changed. I found that when very complex queries were performed, the 2 MB default size of tempdb filled up and returned errors, so I went to the Enterprise Manager to expand tempdb, learned that I had to first expand a device to expand tempdb into, and foolishly chose to expand tempdb into the same device space used by my application, instead of into one of the system databases. Now when I try to delete the device in preparation for its rebuild, the Enterprise Manager responds with an error message saying the device can't be deleted because it contains system tables. Is there any way to get the expanded portion of tempdb out of my application device so that the device can be deleted, without reinstalling SQL Server?
View 1 Replies
View Related
Mar 5, 2001
I would like to free up some space on a SQL 6.5 server. My tempdb is on a second device (other than master) that is 500M. I don't need tempdb to be this large anymore. What would happen if I shut down SQL, renamed this device so that SQL Server wouldn't use it, and restarted SQL Server. Could I then create a new, much smaller device for tempdb? Would this work?
TIA!
JJ
View 1 Replies
View Related
Jun 18, 1999
I want to duplicate the environment of another existing server where tempdb
resides on it's own device (200mb in size). I can create the devices (data & log). What do
I have to do to physically move tempdb to the new devices I create? I've seen such
articles on Swynk.com, but today the links are not working. Thanks very much!
Mark Blackburn o `"._ _ M onterey
mark@mbari.org o / _ |||;._/ ) B ay
Science at its Best! ~ _/@ @ /// ( ~ A quarium
(831) 775-1880 ( (`__, ,`| R esearch
http://www.mbari.org/ '.\_/ |\_.' I nstitute
Database Administrator
MBARI Personal Web Page: http://www.mbari.org/~mark/
View 1 Replies
View Related
Apr 9, 2002
Hi,
I have tempdb data device size default 2 MB, which has completely filled up. I am trying to expand data device to it.
I created new device tempdb_data_ext (250 MB) and tried to expand tempdb data device. But everytime I do it, it ends up adding space to tempdb log device. How can I expand tempdb data device?
It's extremely urgent.
Thanks
View 1 Replies
View Related
Oct 13, 2000
We have a 6.5 database that is comprised of 4 device files. As the database grows over time a new device is created for the space expansion. The last one was created on the wrong partition and the database was expanded onto it. I now need to move the device to the correct partition/directory. My question is; what is the easiest way to accomplish this move? Is it possible to move the device file and manually modify sysdevices? If so, what order of events should be followed?
View 1 Replies
View Related
Jan 26, 2004
I am using 6.5 .
Here is the error that i get
Think that tempdb is small how do i change that or what is this error about'
--------------------------
AIMSMan
---------------------------
Application-defined or object-defined error 40002
37000: [Microsoft][ODBC SQL Server Driver][SQL Server]Can't allocate space for object '##RevByNetSALIMJUMMA' in database 'tempdb' because the 'default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.( 1105)
ODBC
I only have data and log device how do i increase the tempdb device
let me know
SEJ
View 4 Replies
View Related
Feb 1, 2002
Hello all..
I need your precious expertise in resolving one problem . I have transaction log file devices created on on two drives i.e c and d drives .
I want to move the the log device from d drive to c drive or vice versa .
How can i do it . Can somebody help with detailed steps pl .
What are the things necessary to check after the device has been moved to either of the locations , to ensure that everythingh works well .
I have SQL Server 7.0 with SP 3 .
Will the database be marked suspect ? I that case what should i do ?
Any kind of help on the issue will help me a lot .
Thank you all in advance
Annalina .
View 1 Replies
View Related
Jul 31, 1999
If I have a CustomerDB on ServerA, is it possible to
move/expand the transaction log, .Dat or backup files to
ServerB?
View 2 Replies
View Related
Mar 20, 2002
Can someone tell me how I can move the temp database. I know it get's recreated on startup so there must be somewhere that it's referencing.. Is it the model database it uses?
Thanks!
View 2 Replies
View Related
Jun 18, 2002
Hi All,
I need to move the tempdb onto a different drive. Can you give me the sql statement to do so.
Thanks
Paul
View 1 Replies
View Related
Oct 17, 2001
Can someone tell me how to move my tempdb to a different drive?
Thanks,
Dianne
View 4 Replies
View Related
Feb 20, 2002
How do I move the location of the tempdb. I wish to place it on a separated
disk to to increase performance.
Parg
View 3 Replies
View Related
Aug 14, 2002
Hello ,
I want to move my tempdb database to another drive by moving the mdf and ldf files . Is it possible to shift the tempdb once it is created ?
I am shifting the database since there is no space on the current drive and the database might grow in the near furure .
Any ideas ???
Thanks .
View 1 Replies
View Related
Aug 22, 2002
I want to move my TEMPDB from C:MSSQL7DATA to another (less critical) drive in the event it grows too much/fast. I've reviewed the process from MS using ALTER DATABASE, etc., but wanted some feedback from the real world before I commit. Are there any issues I should be aware of? I know it must reside on a local drive on the server. Do I need to make any other adjustments in SQL after the move? Your help is much appreciated. - Rob
View 1 Replies
View Related
Nov 10, 1999
Hi,
My tempdb size on the hard disk at the moment is 1GB.
We have recently put in an additional 1GB of RAM to add to our existing 1GB RAM on the SQL server m/c, making it 2GB now.
SQL server is configured to use 928MB of memory. So, I put the TEMPDB (1GB) on the spare (1GB) memory in RAM.
The problem is, it doesn't allow me this saying not enough memory available.
I have also set the WORK SET SIZE parm to 1, to reserve memory for SQL. Still, it will not allow me. The maximum value it allows me for TEMPDB in RAM is 384MB, beyond which the server doesn't start.
What could be the reason for not getting enough memory, though the 1GB of RAM was put in additionally just for this.
Please advise.
Thanks
Satish
View 1 Replies
View Related
Jul 23, 2005
Right now both of these reside on my C:, but templog is over 3 1/2 GB.1) What do I need to do to move both of these to another drive on thesame system?2) Is there an easy way to purge or compact templog, or just set a sizelimit for it?Thanks,Scott
View 1 Replies
View Related
Mar 13, 2001
I have a situation where I would like to move the physical location of the Model, tempdb and MSDB database files. Does anyone know of a way to do this?
View 2 Replies
View Related
Jul 16, 2001
Is it possible to move (detach and attach) the physical data and log file (.mdf &.ldf) to another location?
View 1 Replies
View Related
Feb 27, 2006
Hello all ...
is there a standard procedure or document that explains how to:
Expand tempdb onto a faster drive ... making it larger
then ...
Remove the small portion of tempdb from my c: drive to reduce contention?
Thanks!
Doug
View 3 Replies
View Related
Nov 29, 2000
does anyone know if tempdb can be physically moved to a different partition on a disk drive on SQL Server 7.0? Since it can't be backed up I'm hesitant to use the sp_detach/sp_attach procedure because I don't want to crash it. If nothing else is available, I can attempt moving it this way at the end of the day and then just reboot to get tempdb back up again if the server fails, but I'd really appreciate a suggestion from someone who has more know-how than I do about system table operations. Thanks again
View 1 Replies
View Related
Jul 20, 2005
I am interested to hear if people think it would be a good idea to movethe Master & TempDB to a different HD.Here is my DB Server's set up:1. Processor: (1) AMD XP 28002. 1st HD (IDE 0) is the system & boot drive3. (3) SCSI HD make up a hardware RAID level 0 (striped withoutparity)solution - these striped drives are just for my working DBs4. (1) SCSI HD that's not doing anything.I want to put the Master & TempDB on the SCSI HD that's not doinganything. Would that be the best place for it for maximum performance orshould I put in the striped array. I am leaning more towards putting onthe SCSI HD that's not doing anything. What do you all think?Ed*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Oct 23, 2015
Have a SQL2008R2 instance on a VM where the single .mdf for the tempDb database is located on a high contention disk. I've managed to get another 60GB disk and thought it would be a good time to move the .mdf and also increase it's size and number of files.
The server has 12 cores and after a bit of reading I've decided that it would be best just to have four files for this database as the 1 file per core (-1) seems to be disputed.
-- Move the existing file to the new disk and rename it.
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', FILENAME='E:SQLData empdb0.mdf');
-- Change the size to 1GB
ALTER DATABASE tempdb MODIFY FILE (NAME='tempdev', SIZE= 1048576KB, FILEGROWTH=5%);
-- Add three new files, all with the same size & growth
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'E:SQLData empdb1.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'E:SQLData empdb2.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'E:SQLData empdb3.mdf' , SIZE = 1048576KB , FILEGROWTH = 5%)
-- Now restart the instance.
Also, what are peoples thoughts on percentage growth for tempDb? I've read that it's not recommend and yet it seems to be the norm.
View 4 Replies
View Related
Sep 11, 2014
We are seeing very high Average Disk Queue Length numbers in one of our clusters (both nodes of the cluster are Virtual, but have their own dedicated virtual environments). Our main data drive also houses TempDB, which I would like to move.
Each node in the Active/Passive cluster are running Windows Server 2012 Standard 64bit and SQL Server 2012 Enterprise 64bit. There is a separate drive for Log files and data files.
The data files also have TempDB on them as previously mentioned. I am reading that you can set up a local disk on each node of the cluster, with the same drive letter and path and then move tempdb as you would with a stand alone SQL Server.
View 4 Replies
View Related
Jun 19, 2006
Basically I've been using Visual Studio 2005 for a few weeks now moving a Pocket PC project from 2003 to 2005. When I hit the Start Debugging Button every time until today the project would rebuild and deploy to my pocket PC allowing me to debug etc but now I get
The remote connection to the device has been lost.
Please verify the device conection and restart debugging.
I used to get this problem in VS2003 sometimes and just like the numerous posts on different sites that I've looked at the problem eventually goes away and I'm none the wiser. One guy said that he found that if he went to bed the problem was resolved when he came back!
My PDA running Windows 2003 2nd Edition is directly connected to my PC via a USB port. I've rebooted my PC and done a soft reset on the PDA but it didn't help. I'm using ActiveSync 4.1.
Does anyone know how to resolve this problem?
View 54 Replies
View Related
Apr 14, 2006
Hi to All
when i m trying to execute following code
backup database web
to disk = 'c:inetpubwwwrootackupmybakup.bak'
with format
I m Getting Error like :
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'c:inetpubwwwrootackupmybakup.bak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
This error is Generated only when i m trying to access folders within "wwwroot" but not in any other folders , even command runs success fully for "wwwroot" folder . !!
but not for any subfolders of wwwroot.
Can Any One Help Me ??
View 6 Replies
View Related
Jul 14, 2004
Has anyone seen the SQL Server error:
"tempdb is skipped. You cannot run a query that requires tempdb"?
We're running a .Net web application with a SQL Server 2000 backend, and we get the error intermittently. Restarting the SQL Server service seems to fix it, as it causes tempdb to be rebuilt, but this isn't a long term solution. Any direction or hints would be greatly appreciated. Thanks!
- Mike
View 11 Replies
View Related
Nov 10, 2015
I trying to get the moving total (juts as moving average). It always sum up the current record plus previous two records as well and grouped by EmpId.For example, attaching a image of excel calculation.
View 3 Replies
View Related
Aug 30, 2000
Hi,
On my test database I have log device size grown to 4 GB from 2 GB. Is there anyway to reduce it? I have truncate log on checkpoint option on.
I tried dbcc shrinkdatafile. Didn't help..
Any suggestions???
View 2 Replies
View Related
Jul 31, 2000
I have a Device of 6GB and a Transaction logof device of 1.5Gb , the database of the associated device had gone suspect.so i dropped the
database , now i a m trying to delete the device it says cannot delete as its in use by some database , i have checked all the
Dbs butits not associted with anything what shold i do????
Regards
View 1 Replies
View Related
Nov 26, 2000
Hi all, hope someone can give me some guidence on this.
We have a database on SQL 6.5 sp5a that is 25GB in size. The transaction log is 10GB in size. We do transaction log dumps every hour throughout the day. On a very heavy day the transaction log backup device will be just over 2GB in size. Around 8GB wasted because no-one knew how much space would be used.
Is there an easy way to resize the transaction log so we can reclaim some space. I was thinking of creating a new smaller database device, then using sp_logdevice to use the smaller device as the log and get rid of the 10GB device.
Any thoughts, ideas would be great.
Thanks
Phill
View 1 Replies
View Related
Aug 6, 1998
I want to expand a log device and am having a problem. When in Edit Database Device, the `Change Now` button is disabled and the size of the device is shown as a negative number.
View 1 Replies
View Related
Sep 28, 1998
On occasion, I notice that some of my backup devices have become "offline" and the scheduled tasks associated with them will no longer run.
I get this error:
Can`t open dump device `d:MSSQLBACKUPdvbk_AFISlog.DAT`, device error or device off line. Please consult the SQL Server error log for more details. (Message 3201)
Does anyone know how to get them back online without having to create a new device? Thanks in advance...
View 1 Replies
View Related
