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


ADVERTISEMENT

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

Database On Multiple Devices

Jun 9, 1999

Hello All,

I want to know if it's possible for a database on multiple devices to put specific tables on specific devices?? and if this is possible how to do it??
Thanks for help

Mohamed hani

View 1 Replies View Related

System Database Log And Data On Different Devices

Sep 17, 2001

I am new to SS7. We are planning to rearrange our system databases so that the logs are on one drive
and the data files are on a different drive. I found a really good white paper on this but just want to
verify that there is not a problem with doing this. In sybase there were some restrictions on doing this.
Any info you have would be appreciated.

View 3 Replies View Related

Multiple Devices For A Database - Advantages?

Nov 6, 1999

Just wondering - on a straight RAID 5 system is there any advantage, performance or otherwise, to splitting a SQL 6.5 database over multiple database devices? Or for simplicity's sake am I just further off creating a single device and manually extending it as the database grows?

Thanks
RM

View 1 Replies View Related

DataBase Connection For VS2005 C++ For Devices

Jun 27, 2006

Dear all:

Is there a way to connect to a SQL Server mobile using native code C++?

I don't want to use the Compact Framework to access Data.

Does anybody knows how to connect to an SQLite database from VS2005 c++ for devices?

Thanks.

Alfredo Mendiola Loyola

Lima PerĂş

View 4 Replies View Related

Database On Database Devices On Diff. Drives ?

Oct 22, 1999

I got a quick question.
My database size has reached the limit of the drive, one option is to
add a new database device on a different drive, then expand in across
2 drives.
Anything I have to concern for a database on 2 database device on 2 logical drives (recoverability or ...?) compared to maybe another option:

reformat the whole disk and allocated more space to the original drive and recover (since in my case, all disk space have been formated) from backup....

Tony

View 1 Replies View Related

Restore A Backup Into A New Database With Different Path On The Devices

Mar 11, 1999

Hi, I've to restore a bk into a new DB, because the old one has a lot of devices created on different places and phisical disks.
Im not sure to do that, cause Ive heard some problems with this action.

The old Db has 10 devices of data and 10 devices of log, and I want to create only one device of data (sum of all the data devices) and only one device of log (sum of all log devices)
Is it possible or not?

Tnx a lot!

View 2 Replies View Related

Shrinking Database

Apr 13, 2008

Hi all,I've deleted a lot of albums but the size of personal.mdf isn't shrinking - how do i go about acheiving this? I've tried to shrink it in sql management express but the file is read only..
please help!
thanks

View 5 Replies View Related

Shrinking Database

Jan 8, 2001

I have a 1.2 gig db which has truncate checkpoint set and over 850 meg of free
space. WHen I shrink it through either DBCC or EM I see no change. ANy
ideas?
Thanks

View 2 Replies View Related

Shrinking Of Database

Dec 30, 1999

i havae a database size of 1000mb with used as 507.50 and unused as 492.50. i tried with commands like dbcc shrink file and dbcc shrink database but i could not get the result as what i need. I need to shrink in such a way that it should have 25% of the used space as free space. could any one help

View 4 Replies View Related

Help For Shrinking Database

Jun 20, 2006

Hi guys.I have a huge database file. When I run sp_spaceused this are theresults:db_size 1337,31 Mbunallocated 14,62 Mbreserved 1088456 Kbdata 258992 Kbindex_size 6224 Kbunused 823240 KbNow, when I run dbcc shrinkfile(database_name, 50) or whatever value ofpercent, it says there is not enough free space in db.Please advice how to shrink the size of that file and why is it so big?Thanks in advanceZvonko

View 3 Replies View Related

Notification After Shrinking Database

Mar 26, 2001

Hi All,

I am working on a client product that shrinks the database in MS SqlServer. After shrinking the database files, it does not send any email notification to the users.

My question is: Is there any way in SqlServer to sense that the database has been shrunk and send a notification to the users (may be through an Alert or a Job or something). Please let me know the steps to do that.

Thanks
Sri

View 1 Replies View Related

Shrinking Database Problem

Jun 4, 2003

I have a SQL 2000 database that I would like to shrink.

Recovery Model = Simple
Data file space allocated = 78 Gig
Space free = 41 Gig

Similar problems with Trans logs sometimes

As I understand it, I can't shrink the database below the allocated size. How do I get that space back ?

Any extra insights and explanations about this topic in general would be appreciated .... Thanks in advance.

View 3 Replies View Related

Error While Shrinking Database

Oct 13, 2005

Hi,

When I am trying to shrink a database, I got the following error.

Executed as user: NT AUTHORITYSYSTEM. ...he PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491894). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491893). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491892). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491891). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491890). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:491889). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8909) Table error: Object ID 0, index ID 0, page ID (1:490615). The PageId in the page header = (0:0). [SQLSTATE 42000] (Error 8...

Please help me to resolve this issue

Thanks & Regards,
Eldho Abraham

View 7 Replies View Related

Shrinking Database Files

Jul 23, 2005

I have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?

View 6 Replies View Related

Shrinking Database Log Used For Mirroring

Nov 22, 2006

Can anyone offer some advice on how to reduce the size of a databaselog. The database is mirrored on another server using the logs. Thanks

View 5 Replies View Related

Problem Shrinking A Database

Jul 20, 2005

Hi all.I'm having trouble shrinking a database. In short, using the SQL QueryAnalyzer,I enter the following commands:DBCC SHRINKDATABASE (database, 80)GOThe command returns the expected 'DBCC execution completed.' response, butthe databaseremains the same size when I run 'sp_helpdb database'. Am I missingsomething?Thanks in advance,Jaeger

View 2 Replies View Related

Shrinking The Master Database Logfile

Feb 9, 2001

Hello

Can anyone help, I have a master database on my server which has an extremely large log file size. I need to shrink this down and so I have tried using dbcc shrinkfile (master, 50, truncateonly) and dbcc shrinkdatabase although it does not seem to reduce in size

Please can anyone help

View 1 Replies View Related

Will Database Shrinking Affect The Data?

Aug 6, 2004

Hi all,

does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away?

Thanx

View 1 Replies View Related

SQL 2012 :: Shrinking Database For Dev Environment?

Nov 26, 2014

I've read lots about why you shouldn't normally shrink databases in posts such as this:

[URL]

But we have a situation where we are required to copy the live db to various non production environments for testing. Part of this process involves truncating a number of tables with masses of blob data. So we're freeing up quite a lot of space. The question is how to reclaim this? The database is peculiar in that it's got no clustered indexes so I can't rebuild indexes on another filegroup with drop to move and rebuild.

I've tried dbcc shrinkfile specifying a size. I've tried to shrink the file in increments. The problem is I'm just not getting much space released. I get maybe 2-3%. I suspect this is because we're dealing with heaps with some tables that have sparsely populated blob / image data.

Is there an alternative to shrinking? Should I recreate all the db objects in a new database? It doesn't matter if the process takes a while or if it has to be done manually.

View 7 Replies View Related

Shrinking Database Files In Production

May 15, 2008

Hi,

We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.

thanks

View 5 Replies View Related

Shrinking SQL2000 Database W 20 Datafiles.

Jul 23, 2005

Hello,I am trying to clean up a database I inherited.I have an 80GB SQL 2000 database with 20 datafiles each 4096 in size. Ihave been able to remove unneeded data and am now trying to clean up.If I do a Shrink on each datafile would able to recover on average 2gbout of 4g, however I would prefer to have 10 full datafiles and 10empty. (or better yet 5 full 8GB datafiles and 15 empty)Can someone point me in the right direction on how to move the dataaround so that dont have 20 partially filled datafiles?I have noticed that I can shrink a single file and use the "empty thefile option (and move data to other files in the group)." option. Ihave already done this to the last 2 datafiles as a test but not surehow to do this on a large scale. I have also set the 1st 10 datafilesto be able to grow to 8 GB.For lack of a better way to say this, Is there a way to defrag orreorganize the data ables so everything "moves to the front".BTW, I have already run a maintenance plan to reorganize the data andindex pages.

View 2 Replies View Related

DB Engine :: Database Files Shrinking

Jul 6, 2015

I have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?

View 7 Replies View Related

Shrinking Database File After Removing Data

Apr 20, 2007

Hi there,

I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.

I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.

Kind Regards

View 2 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

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 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

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







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