DBCC Shrinkfile - EmptyFile option
-
We currently have a 2TB database which has 16 data files spread over 6 disk drives. We have arrived at this situation with so many data files with growth over a 3 year period and no archive policy in place. Now that a archive policy is in place we have been tasked by the customer to reduce costs of running the programme.
There is sufficient headroom (free data space) to enable us to lose a couple of the data files which would in turn free up a disk drive which would reduce the cost of the annual storage price to the customer.
We thought we could run the DBCC SHRINKFILE with the EMPTYFILE option to clear out the file(s) and then eventually drop the files from the FILEGROUP. We set-up a test run on our DR kit (same spec as the production and exact copy of the DB), the file that the test would use was just over 120 GB in size (fixed) with approx 2 GB of free space, the majority of the data that is stored in the file will be BLOB (nText) data as the clustered index is stored within another FILEGROUP.
After 2 DAYS of running the command the free space had only grown by 4 GB giving a total of 6 GB of free space, we stopped the query at this point as we only had a limited time frame to use the DR kit, we knew it was doing something as the transaction log back-ups (every 5 min) were more than the minimal size and there was no other activity on the database.
My question are:
a) Has anybody else ever encountered the excessive time it takes on large data files to empty them or is this normal?
b) Can you run the EMPTYFILE option in blocks, i.e run it within downtime in one period and then run it again in another period (several weekends), if so would SQL server start filling up the file again between the downtime periods?
-
I'm planning to do the same very soon, my database is smaller (300 GB). I will test it this weekend in the test environment. Monday I will get back to you with my results.
-
I have little experience in doing this but I did this on 10 TB OLAP database. we had around 20 data files which are residing on 4 filegroups and I made them to 4 files and 4 filegroups.
As far as I know,there is no way to empty file in chunks
Recommendations:
1) Increase the initial size of file which you dont want to delete in the file group.
2) Aviod DBCC commands while doing this.
-
Has anybody else ever encountered the excessive time it takes on large data files to empty them or is this normal?
For moving around 600 GB file it took around 8 hours for me (It also depends on your hardware configuration). Ours is 64 bit sql server ,64 GB RAM,2 quad processor
-
I just finished running DBCC ShrinkFile with the EMPTYFILE option for a 300GB data file. I created 2 ndf datafiles where the data from the mdf file was moved. The whole process took approx 16 hours. At the end I got the following message:
"Msg 2555, Level 16, State 1, Line 1
Cannot move all contents of file "Sharepoint_FMOInside_CRM" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.".
I think this error is normal because the file I emptied was the mdf file.
-
Maybe this is the reason:
DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns
-
I am attempting the same thing. Here's my scenario:
SQL 2005, 32bit, Win2k3, 1.5 TB DB
In my Test environment, using a copy of my 1.5 TB production database, I am running a DBCC Shrinkfile with the Emptyfile option on a data file that I want to remove to reclaim 60 GB of space. My "hope" was that this operation would complete in a reasonable amount of time and that I could repeat this on four other 60 GB data files. Sadly, after more than ten hours, the first DBCC Shrinkfile command is STILL running.
KU
-
have you tried manually issuing checkpoints prior to the emptyfile?
It would probably also be helpful to have the database set to SIMPLE.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply