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. I
have 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 2gb
out of 4g, however I would prefer to have 10 full datafiles and 10
empty. (or better yet 5 full 8GB datafiles and 15 empty)

Can someone point me in the right direction on how to move the data
around so that dont have 20 partially filled datafiles?

I have noticed that I can shrink a single file and use the "empty the
file option (and move data to other files in the group)." option. I
have already done this to the last 2 datafiles as a test but not sure
how to do this on a large scale. I have also set the 1st 10 datafiles
to be able to grow to 8 GB.

For lack of a better way to say this, Is there a way to defrag or
reorganize the data ables so everything "moves to the front".

BTW, I have already run a maintenance plan to reorganize the data and
index pages.

View 2 Replies


ADVERTISEMENT

Restoring Database From Copy Of Datafiles

Jun 29, 2004

Hi,

I need to set up a Standby database using a copy of datafiles of the primery database. (An SQL backup of the database is not an option).

Is there any option to restore a database from datafiles?
Or is there an option to attach database but into an standby mode?

Please advice,
Zvi Gilinsky

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

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

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

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

SQL2005 Cannot Backup A Restored SQL2000 Database With Unknow Database Full-text Catalog Database

Nov 15, 2007



We replicate a SQL2000 database (DataBaseA) to a SQL2000 database (DataBaseB) by using the Restore function and hasn't change its logical name but only the physical data path and file name. It is running fine for a year. We use the same way to migrate the DataBaseB to a new SQL2005 server with the Restore function and the daily operation is running perfect. However, when we do the Backup of DatabaseB in the SQL2005, it just prompt the error message


System.Data.SqlClient.SqlError: The backup of full-text catalog 'DataBaseA' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data. (Microsoft.SqlServer.Smo)


Please note we left the DataBaseA in the old SQL2000 server.


Please help on how we can delete the Full-text catalog from DatabaseB so we can do a backup


Many Thanks

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

Splitting Datafiles

Jan 13, 2004

I founded couple thread about splitting but not find same situation what i have.
I have server with Raid10 with big storage massive, and Test server with two 30Gb HardDrives (not into a Raid or Somethink). I have situation when in real server database grown over 30Gb and now i can not restore database copy into a test server because i have one big Data File.

Can i split somehow 35Gb Data file when i restore to test server to 25Gb and 10Gb ???

Or can u recomend some solutions

At the moment cant to do hardware upgrate.

View 1 Replies View Related

How Sparse Are My Datafiles?

Feb 21, 2008

Hi!
Is there a way to see how sparse my datafiles are?
I would like to know on forehand if running dbcc shrinkfile will make sense...
/Thanks

View 1 Replies View Related

Moving Datafiles And Logfiles.

Mar 19, 2002

I need to move Datafiles and logfiles to another Drive. How can this be done.
Thanks

View 1 Replies View Related

How To Create A New Serverinstance But With Old Datafiles?

Jul 23, 2005

Hi groupI want to make a new serverinstance, but based on some old files from aprevious instance I once had from a SQL I scrapped.I only know how to use the SQL Server enterprise manager console, and Ihaven't found any tools in there to do that.Any suggestion will be more than welcome.Adrian

View 1 Replies View Related

Size Of Datafiles In A MSSQL2000

Apr 4, 2006

Hi,

I have a datadabase with 1 datafile from 60Gb. Is it a good thing(preformance) to split up this datafile in smaller datafiles from 6Gb each?
I don't have separete diskslices so a can't spread my datafiles on my disks but i only need to know if a datafile from 60Gb sin't too big for MSSQL2000.

Thanks,

Ewoud,

View 3 Replies View Related

Datafiles For Report Services

Jan 16, 2008


Good morning,

When I install a database I set up datafiles to another volume with greater capacity. I would like to know about the report service, there is need for datafiles be placed in another volume, or it may be placed in the same volume of the operating system?

Thank you,

Best Regards,

Ralph Nogueira Haddad

View 5 Replies View Related

Migrating From Sql2000 MSDE Database To Sql2005 Express - Attach Database Errors

Apr 23, 2006

I have a medical records system, SoapWare v4.90, that uses MSDE (SQL2000) databases. Due to the 2gb limitation, I am trying to migrate over to SQL 2005 (Standard or Express) which I have heard works fine. The SoapWare has a datamanager that allows me to log in to the MSDE instance, detach the SoapWare databases from msde (as well as do backups, etc) which I can confirm are detached.

Then I log back into a SQL2005 database instance using the datamanager and try to attach the database. This is what their pictured instructions demonstrate. However, I get the following error:


Database 'sw_charts' cannot be upgraded because it is read-only or has read-only files. Make the database or files writeable, and rerun recovery.


Of course, some of the entries will be read only, since doctors have to sign off the charts and are not allowed to subsequently change them.  But I should still be able to switch over to sql 2005?!?!?!?


Or... is there a way to attach the databases to SQLExpress manually?

Help pls?

View 1 Replies View Related

Moving The Location Of By Datafiles From One Drive To Another

Feb 9, 2007

Hey guys i want to relocate my database datafile and transaction logs from C: drive to D:

From what i have in mind , correct me if i am wrong: First I will create the same folder on D drive as they are on C drive then copy the datafile from C to D , then come back and change the paths on the database files to point on D.

View 5 Replies View Related

Restore From Phisical Copy Of Disk (datafiles?)

Oct 19, 2004

Hi,
I have formatted my server because of serious problem and i did not backup my database. I have only a phisical copy of the disk containing data on another disk. :( How I can recover my db? Thank you in advance.

View 6 Replies View Related

Can I Restore SQL2000 Database To SQL 7.0?

Mar 8, 2002

many thanks!

View 1 Replies View Related

Restore From SQL2000 Database

Aug 2, 2007

Hi all

We have a lot of SQL2000 databases and would like just backup and restore to SQL2005.

Is there anything that I need to look/aware at it apart of DTS stuff (we are not using much DTS).

Does the Level80 sets on SQL2005 for each database will have backward compatibility with SQL2000?

Thanks

View 15 Replies View Related

Moving A SQL7 Database To SQL2000

Dec 27, 2002

Can anyone tell me of an EASY way to migrate a a database from a SQL 7 server to a SQL 2000 server (two different machines)?

Everything that I have found is only for upgrading SQL 7 to SQL 2000 and this isn't what I need.

Two groups in our company merged into one. The first group has a SQL 7 server that the lease is about to expire on and the second group (mine) has a newer SQL 2000 server that we need to move all of the databases to.

I have tried DTS. It failed. I tried backing up the 7 server and restoring to the new server. It failed.

Are there any other suggestions?

Thanks,
Steve

View 4 Replies View Related

Restoring SQL7 Database To SQL2000

Feb 17, 2004

Hi all

just a quick question for you. has anyone had any problems restoring a SQL7 database to a SQL server 2000?

is there anything special you have to do to get it working?



thanks for your time.



Andrew.

View 1 Replies View Related







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