SQL 6.5 Resizing Log Device
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
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
Nov 27, 2000
The size of log is around 600MB and only 20MB is used...
I tried truncating the log using dbcc shrinkfile..but it doesn't truncate
Any help would be appreciated....
Thanks.
View 3 Replies
View Related
Jun 25, 1998
We are looking into sql 7.0 and would like to know how it handles excessively sized databases. If we restore a 6.5 database into a db on 7.0 will it automatically resize to free up disk space or will it only work for new databases which have install scripts run on it.
View 2 Replies
View Related
May 25, 1999
My transaction log has grown to 2 Gig. But when I do a truncate transaction log. It is only 18Mb. How can I resize the physical disk space of the transaction log. I want it only to be 100 Mb. It is just wasting the disk space. Please help!
View 1 Replies
View Related
Dec 7, 2007
i have a report with several rectangles. How do I make the size of the rectangles fixed so that they do not resize automatically?
They appear fixed until i put a table inside. once I do that it automatically resizes
View 4 Replies
View Related
Jul 14, 2000
Here's my question, hopefully one of you can help me:
When you allow your log file to grow on its own, eventually it uses up all your diskspace . . so you truncate it. However, when you truncate it, it doesn't *resize* the log file, so your still out of disk space, it simply clears up room within the allocated "block" or what-not so that the db can continue to make log file entries.
So, does anyone know of a way to resize that log file without losing it's data? For example, we were thinking about truncating it, backing it up, dropping the original, and then restore the log file from the "truncated backup." Anyways, there must be an easier solution (if that one even works!) . . so please, if you have any hints, let me know.
Thanks,
David P.
View 3 Replies
View Related
Sep 2, 1998
I have a database which is quickly filling up its allotted data space (the database is 500MB, and the device is also 500MB). I wanted to know whether it was an acceptable procedure to:
-Dump the database
-Create a new 600MB (or more) device
-Create a 600MB database for load on that device
-Restore the dump to it.
Are there data integrity/corruption/fragmenting issues with this method? The alternatives are simply expanding the database across another device (or on the same device using DISK RESIZE), or using scripts and bcp to recreate the database from scratch. These do not seem ideal. Any opinions or comments would be greatly appreciated.
MR
View 2 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
May 3, 1999
Is there a way to shrink database devices in SQL Server 6.5 Other than dropping and restoring?
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
Jul 9, 2015
I want to control the size of ldf files and mdf files of several databases on SQL Server 2008 in my organization (manual increase), but i have a question:
What would be the best practices (best methods) for provisioning a ldf file and mdf file? Exists any generic formula?
With this i want to avoid the shrink operation and the autogrow of sql server databases...
View 6 Replies
View Related
Oct 5, 2015
We have a vendor created database with 9000+ tables, one of which has about 6 billion rows. The vendor redesigned the database recently and ever since we've had terrible performance.
What the vendor did was increase any and all varchar columns (tens of thousands of columns) to 256.
Before the upgrade we had no problems creating an index on the 6billion row table, it would take 2 hours.
Now after the upgrade we've let the index creation command run for 5 days and killed it because it was consuming terabytes of logspace.
The previous design had combined column width of 1049 to what is now over 4000. The primary key itself is 1283 characters (SQL limit is 900).
There is no additional data, just wider columns. Why we are unable to create the index?
What is happening inside SQL Server? Does SQL make "room" in memory for the index for the entire width of the potential max row length?
View 3 Replies
View Related
May 1, 2007
I got an Assertion failed error while resizing a vector in a plug-in algorithm.
In order to isolate the problem I created a simple model class in Navigator.h file as shown below:
//========================= begin code =======================
class CStateStats
{
public:
DOUBLE m_dblSum;
DOUBLE m_dblSqrSum;
public:
CStateStats()
{
m_dblSum = 0.0;
m_dblSqrSum = 0.0;
}
};
class CAttStats : public DMHALLOC
{
public:
dmh_vector<CStateStats> vstatestats;
public:
CAttStats() : vstatestats (*this)
{
}
};
//========================= end code =======================
The access to DMHALLOC is provided in that class and in Navigator class as shown below:
//========================= begin code =======================
class ATL_NO_VTABLE NAVIGATOR :
public DMHALLOC,
public CComObjectRootEx<CComMultiThreadModel>,
public CComCoClass<NAVIGATOR, &CLSID_NAVIGATOR>,
public ISupportErrorInfo,
public IDMAlgorithmNavigation
{
public:
NAVIGATOR() : _viAttributeOutput(*this), _vCAttStats(*this)
//========================= end code =======================
I succeded making room for _vCAttStats vector, but when I tried providing room for the vectors of the vector I got an Assertion failed error (file dmhallocator.h Line:56 Expression assert(_dmhalloc._spidmmemoryallocator != NULL)). Please, see the code below, included in NAVIGATOR::GetNodeArrayProperty function:
//========================= begin code =======================
_vCAttStats.resize (2); // <<<<< succeeded here!
// make space for the states
_vCAttStats[0].vstatestats.resize(ulStates); // <<<<<<< assertion failed here!
//========================= end code =======================
I tried using a vector-of-vector approach and I also succeeded.
But I have to use that kind of structure: a vector of class with a vector inside.
I think I must provide a similar approach of vector-of-vector existing in DmhVector.h but I don't know how to do it.
I would apreciate any help.
View 3 Replies
View Related
May 15, 2007
I have a report that (for purposes of this example) displays States and the Cities in a state.
Now the State is the first group which I want to drill down into by expanding and then show the Cities as the detail line.
What I would expect to happen (and what does happen if I use the wizard instead of creating the report manually) is that I would click on a State and then the page would expand to make room for all the cities listed below.
(That is to say if I am showing only states, not expanded, then all 50 states fit on a single page. Then if I expand California all the states below that shift onto other pages to make room for the 400+ cities in California)
However, what happens is that when I preview the report instead of being 1 page long it is many pages in length with empty white space between the States (white space that would be occupied by the cities if I were to expand the state).
Can someone tell me what setting I need to adjust to get the report to dynamically resize when I expand/collapse groups?
Thank you.
View 1 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
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
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
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
Jun 28, 2000
Hi All
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.
Thanks
Steve
sfarmer@edfman.com
View 2 Replies
View Related
