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?
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 .
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.
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?
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.
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 . !!
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.
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???
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????
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.
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.
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...
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...???????
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?
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.
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
I have taken ownership of a SQL 7 Server that has many DB's with each DB's devices spread all over the 6 disks that exists in the Server.
e.g.
DB - Reference has
Data devices on D (20mb), E(40mb) , F(60mb) drives
and Logs having a similar spread.
Now what I want to do is to restore a full backup of the database into a new db but with only one data device and one log. So that basically the server is tidied up. The reason the devices are spread is not due to performance reasons.
The QUESTION IS : How do I restore a full back up of a db with many devices in to a NEW db with only 1 data device and 1 log device.
I have a transaction log which is backed up 5 times a day to a device. The Transaction log is growing quite quickly in between each backup. The backup device is also growing at a much quicker rate. realistically i would like to only keep 2 weeks worth of transaction log backups in the device.
for the transaction log backup i have tried the following transact-Sql 'Backup Log [imsv23] to [imsv23_TL_2] with RetainDays = 7, Init, NoUnload, Name = N'imsv23 TL V2', NoSkip,Stats=10,Noformat'
but due to all of the contents in the backup set not having expired the backup fails.
i need to reduce the size of the backup device. all of the backup sets have expiry dates but none of them are being deleted from device.
so my problem is 1 i need to remove the excessive backups from the device 2 i need to set up the automatic backups so that these datasets are removed when the expiry date is reached.
I am having trouble increasing the size of the log device on a SQL 6.5 database. When I use SQL Enterprise Manager I get an error saying that the device has 0 MBs available. When I use the ALTER DB statement I get an error saying that there is not enough space on the disk, but I know this not to be the case. Has anyone any suggestions? Thanks in advance, Michael lawlor
Suppose I am appending to my transaction log dump device every half hour,thus adding 48 log dumps per day. How can I purge my transaction dump device to only keep the last 1 week's worth of these logs. I do not wanted to issue an INIT in the command, because this will wipe it completely. I noticed an EXPIREDATE and RETAINDAYS parameter for the DUMP command. Can I use these to selectively purge the backup device or will these allow the device to be wiped clean also?
Can anyone suggest a method to reduce the size of one our log devices. The DB was set up initially at 500Mb with a log size of 1 Gb (typo by the client). We would like to reduce it to 100Mb. if possible. Our environment is SQL Server 6.5 with service pack 3.
I`m familiar with the sp_help_revdatabase stored proc which gives info on which devices a database is stored on. But I thought I`d heard of another sproc (or even a query of certain system tables) which could be used to determine the order of device creation for an entire SQL Server. The sysdevices table is fine, but doesn`t show the virtual device number, or whatever number indicates which devices were created first, second, etc.
