Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DBCC Shrinkfile - EmptyFile option Expand / Collapse
Author
Message
Posted Thursday, December 10, 2009 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 14, 2012 8:54 AM
Points: 1, 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?
Post #832100
Posted Thursday, December 10, 2009 8:28 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
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
Post #832280
Posted Thursday, December 10, 2009 1:29 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:18 AM
Points: 77, Visits: 326
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.


Post #832566
Posted Thursday, December 10, 2009 1:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:18 AM
Points: 77, Visits: 326
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
Post #832570
Posted Sunday, December 13, 2009 3:29 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, January 24, 2014 7:02 AM
Points: 3,066, Visits: 1,413
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
Post #833450
Posted Friday, February 12, 2010 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:24 PM
Points: 9, Visits: 113
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
Post #864695
Posted Sunday, March 27, 2011 4:19 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 35, Visits: 851
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
Post #1084658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse