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

Expanding Tempdb In A Device Prevents The Device From Being Deleted

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

Resizing The Log

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

Resizing

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

Resizing Transaction Log

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

Rectangle Keeps Resizing

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

MSSQL 7 Log File Resizing

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

Resizing A Full Database

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

Visual Studio 2005 The Remote Connection To The Device Has Been Lost. Please Verify The Device Connection And Restart Debugging.

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

Device Error Or Device Off-line ???

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

Resizing/ Shrinking Database Devices

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

TempDb - Moving / Adding Files And Resizing

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

DB Design :: Resizing LDF And MDF Files Of Server Databases

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

DB Design :: Unable To Create Index After Column Resizing

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

Assertion Failed Error While Resizing A Vector In A Plug-in Algorithm

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

Drilldown: Collapsed Detail Lines Still Take Up White Space Instead Of Resizing On Page

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

Log Device--please Help

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

Cannot Delete Device

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

Moving SQL 6.5 Device

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

Expanding Log Device

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

Device Offline

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

Database Device And DB

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

Deleting A Device

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

Device Problem

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

Tempdb Device In 6.5

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

Index Device

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

Backup Device??

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

Backup Device

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

SQL Backup Device

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

Device CALs

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

Database Device Mirroring !!!

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

Moving Device To Another Location . Help !!

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

Restoring DB With Many Device Files

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







Copyrights 2005-15 www.BigResource.com, All rights reserved