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