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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 19, 2002
I need to move Datafiles and logfiles to another Drive. How can this be done.
Thanks
View 1 Replies
View Related
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
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
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
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
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
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
Mar 8, 2002
many thanks!
View 1 Replies
View Related
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
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
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