| Author |
Topic |
|
ssrikrish
Yak Posting Veteran
69 Posts |
 Posted - 2005-12-14 : 20:51:03
|
| I moved a 10 million row table from the original db to an archive db. The database size before and after is still 26 GB. I tried to use the shrink database feature of enterprise manager and it wouldn't shrink the db. Apparently, the size of backup after the table migration is 8 GB. I am hoping to shrink the db to the same size. I also tried DBCC SHRINKDATABASE statement and it is taking forever and then it fails. I don't the reason for why. Please help.Thanks,Sri |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-14 : 21:13:10
|
| Try using DBCC SHRINKFILE to shrink individual database files in smaller chunks, say 50 MB at a time.DBCC SHRINKFILE (DataFil1, 25000)goDBCC SHRINKFILE (DataFil1, 25950)goDBCC SHRINKFILE (DataFil1, 25900)go...and so on...goDBCC SHRINKFILE (DataFil1, 9000)CODO ERGO SUM |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-12-15 : 02:29:53
|
| Do you need to shrink the database, or will that space get reused fairly soon? (if so leave it alone!)Kristen |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2005-12-15 : 12:27:20
|
| Yes, I need to shrink the database to release some space on disk. The size of the db will grow but not as fast as before as the huge chunk of data is going to be in the archive db going forward.Thanks,Sri |
|
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-12-15 : 19:49:18
|
| Do you get any error message from shrink when it fails? What version+SP are you on?Paul RandalLead Program Manager, Microsoft SQL Server Storage Engine(Legalese: This posting is provided "AS IS" with no warranties, and confers no rights.) |
|
|
|
gkrishn
Starting Member
16 Posts |
Posted - 2005-12-16 : 09:56:19
|
| Check which datafile you had data, and shrink that file.Or as Michael said, shrink all datafiles where there is less activity.Let us know the result.--Rajiv |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2005-12-16 : 19:08:35
|
| I tried SHRINKFILE to shrink the data file and it was running for more than 3 mins and I stopped it as I wasn't sure if it was going to error out. Does it usually take a while to shrink the file?I am trying this on a static development db and if it works fine, I will have to schedule it as a sql agent job on the prod db to run at 2 AM or something when there is least activity.Please advise.Thanks,Sri |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-12-17 : 00:05:49
|
| Were you using SHRINKFILE as I suggested to shrink the file 50 MB at a time?It will probably take quite some time to shrink the file, and that is why you should do it in small steps. Plan on it taking several hours to complete all the steps. It should be OK to run while the database is active. It is possible that you will get deadlocks or other errors on some steps, but it is nothing to worry about. Just continue running.You have to realize that the SHRINKFILE is not just removing empty space. It is moving the data for database objects from the areas it is trying to release, so it is doing a large amount of work. It will also generate a lot of data in the transaction logs, so make sure you are running transaction log backups at short intervals.CODO ERGO SUM |
|
|
|
ssrikrish
Yak Posting Veteran
69 Posts |
Posted - 2005-12-17 : 15:53:32
|
| Michael,Your suggestion worked. Thanks. I brought the data file down to 12 GB now.Thanks again.Sri |
|
|
|
|
shrink database