How Do I Move TempDB To It's Own Device?
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
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
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
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 31, 2000
Is it possible to relocate the master database device to another drive?
View 3 Replies
View Related
Apr 9, 1999
I need to remove log segment of one database from current position to another drive. The current log segment is used by two databases. This was created by mistake. Now we need to fix the problem and create another log segment for the other database. If we keep the log setment as is, we will have problems deleting one of the databases in the future.
Thanks for your help,
Ana.
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
Dec 23, 2003
Hi there,
Does anyone know how to move tempdb (mdf and ldf files) to another physical disk of already instaled SQL Server 2000? (The idea is to move the tempdb to another dedicated disk in order to improve the performance.)
Regards,
Azv
View 1 Replies
View Related
Jul 6, 2006
I have to move my sql server to new SAN. Tempdb is on old san. If I move the tempdb to c: like below and reboot then move the server, and then after move to new server, start it up, and move again, would that be correct process
i was going to use below to move tempdb to c from SAN
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'c: empdbloc empdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'c: empdbloc emplog.ldf')
Go
View 1 Replies
View Related
Jun 24, 2004
I want to move the tempdb SQLServer database from the default install location (c:program filesMicrosoft...)
to d:mydb
How can I do that ?
View 5 Replies
View Related
Oct 1, 2007
One of my boxes was built with all of the system databases on the C: drive, and tempdb is up to 2 GB and there is no room left on the drive
It should have been installed on the D: drive
I've never had to do this before, mostly because I never install sql serevr on the c partition
Any war stories or suggestions
View 10 Replies
View Related
Jul 20, 2005
I have a system configured with Windows 2000 and SQL Server 2000Clustering (active/passive). Both SQL Servers have a local E: drivewith the same directory structure. I would like to move the tempdboff of the shared SAN and on to the E: drive. I don't foresee anyissues with this but wanted to know if anyone else has encounteredproblems with this.
View 1 Replies
View Related
Apr 5, 2007
I attempted to move tempdb using detach/reattach with SQL Server 2005 started with the -T3608 flag. I am now in a situation where tempdb is not listed in sysdatabases, but it is in sysaltfiles. If I try to detach it, it says the db doesn't exist. If I try to reattach it, it says it already exists. Since I can't alter the sys tables, is there anyway to get SQL to create a new tempdb, or to reattach the old? Thanks.
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
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
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
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
Jun 25, 1999
hi, is there a way to find out what which database resides in which Database device.....
thanks
Ali
View 1 Replies
View Related
Dec 1, 1998
I am trying to drop a device that as no associated file in Windows NT Explorer(.dat) that shows up in my Server Manager Windows
How can I delete them?
Thanks
View 2 Replies
View Related
Jul 24, 2000
Hi,
I am trying to take a dump on to a disk with one of device A..its a transaction log dump every 15 minuter with no init and from the
same device which is connected to my standy server i am doing the load, the time diffrence is 15 minutes between standy and
production in terms of dump and load i am getting this error very frequently the device is offline the message is
"Can't open dump device 'j:ABC.DAT', device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)"
Now can this error come when the dump and load is happing at the same time tru the same device...???????
Can their be a way to control all this...??
Replies will be appreciated
View 2 Replies
View Related
Jul 12, 2001
Is there any advantage to putting the index device on a separate drive from the data & log
devices? Or is this even possible with w2k?
View 1 Replies
View Related
Feb 23, 2004
Hi all,
I want to store bakup file on the computer on netword, so make a backup device on the other computer, it ok. after that i make an full backup, there is an error : "Can not open the bakup device " .
please show me,
best regard.
View 2 Replies
View Related
Mar 15, 2004
Hi
is there a stored procedure which allows me to query if there is an existing backup device? or which gives me a listing of existing backup devices?
Thanks!
View 2 Replies
View Related
Mar 26, 2004
Hi,
request you all to clear mine one doubt that,
Can creating a backup device for sql backup reduce the backup time comparing to giving full backup path.
Cheers!!
View 1 Replies
View Related
Jul 20, 2005
Hi,I am slightly confused about the meaning of a 'device cal'(specifically for sql server but I guess the same applies to other mslicences)In the examples about device cals, there is a picture of a number ofwindows desktops connected to a database server. each desktop canpotentially be used by a number of users and requires one device cal.All well and good.Now, say I have a web application hosted hosted on a different machineand connecting to SQL server. the SQL server licencing FAQ says"A device CAL covers multiple users' access to server software from asingle, shared device."is my web server a shared device? so does the web server require asingle device CAL no matter how many users use it?Andy
View 9 Replies
View Related
Jan 30, 2002
Hello Everybody ..
Can anyone help me with some information or procedure as to how to mirror database devices in SQL Server 7.0 & SQL 2000 servers ?
SQL Server 6.5 has mirror utility built in the Enterprise manager .
Is there any way to do it on SQL 7.0 and SQL 2000 Servers ?
Any help will be appreciated .
Many thanks
Yatin.
View 1 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
