SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DBCC Shrinkfile - EmptyFile option


DBCC Shrinkfile - EmptyFile option

Author
Message
barry.duncan
barry.duncan
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 31
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?
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4416 Visits: 1439
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.




My blog

Sarwan
Sarwan
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 367
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.
Sarwan
Sarwan
SSC Veteran
SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)SSC Veteran (227 reputation)

Group: General Forum Members
Points: 227 Visits: 367
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
Ignacio A. Salom Rangel
Ignacio A. Salom Rangel
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4416 Visits: 1439
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.




My blog

frk888
frk888
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 121
Maybe this is the reason:

DBCC SHRINKFILE and SHRINKDATABASE commands may not work because of sparsely populated text, ntext, or image columns
http://support.microsoft.com/kb/324432
SequelDBA
SequelDBA
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 1063
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
Jon.Morisi
Jon.Morisi
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1142
have you tried manually issuing checkpoints prior to the emptyfile?
It would probably also be helpful to have the database set to SIMPLE.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search