Shrink database file with EMPTYFILE option

Question

Shrink database file with EMPTYFILE option

writearjun53

Ten Centuries

Points: 1060
More actions
July 29, 2013 at 11:13 pm

#296191

I want to know that can we shrink database file with EMPTYFILE option while currently accepting data or we just need to do some other thing to shrink database file with EMPTYFILE option..

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

saran.dharsh Right there with Babe Points: 723 More actions · Answer 1

NO.. Because will never allow this operation, it leads to data loss.

Confirm whether your is database is partitioned or not. If database is partition we can empty the file while shrinking the db.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

writearjun53 (7/29/2013)
I want to know that can we shrink database file with EMPTYFILE option while currently accepting data or we just need to do some other thing to shrink database file with EMPTYFILE option..

Sure you can, there's nothing about that option that requires any portion of the database to be read only or quiesced to do a shrinkfile.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

saran.dharsh (7/30/2013)
NO.. Because will never allow this operation, it leads to data loss.

Err.. what? Shrinking a database doesn't lose data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

writearjun53 Ten Centuries Points: 1060 More actions · Answer 4

@gilamonster..Thanks for your reply, Now i just need to confirm that what option we should use for shrink database file with EMPTYFILE option?

1. Shirnk database file with EMPTYFILE option while currently data accepting

2. first we change database mode from multiple user mode to single user mode then shirnk database file.

Which one is the best way to do this activity 1 or 2 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

There is no requirement that a DB be in single user mode or quiesced to do a shrink file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Perry Whittle SSC Guru Points: 233900 More actions · Answer 6

When you specify the EMPTYFILE clause to DBCC SHRINKFILE the database engine stops writing new data into the file and all existing data is moved out.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

writearjun53 Ten Centuries Points: 1060 More actions · Answer 7

writearjun53

Ten Centuries

Points: 1060

July 30, 2013 at 6:53 am

#1636956

Thanks all of you for clearing my doubts

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 8

GilaMonster (7/30/2013)
There is no requirement that a DB be in single user mode or quiesced to do a shrink file.

That's true but it makes it a heck of a lot easier to "rebuild" the log file (change the VLFs) by shrinking it to 0 and regrowing it. Of course, you either have to do a full or diff backup to get back to Point-in-time backups if they were being taken.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Jeff Moden (7/30/2013)
GilaMonster (7/30/2013)
There is no requirement that a DB be in single user mode or quiesced to do a shrink file.
That's true but it makes it a heck of a lot easier to "rebuild" the log file (change the VLFs) by shrinking it to 0 and regrowing it.

Which is kinda unrelated to the OP's question on shrinking a data file

Of course, you either have to do a full or diff backup to get back to Point-in-time backups if they were being taken.

Err, no. Shrinks on the log file don't break the log chain.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 10

GilaMonster (7/30/2013)
Jeff Moden (7/30/2013)
GilaMonster (7/30/2013)
There is no requirement that a DB be in single user mode or quiesced to do a shrink file.
That's true but it makes it a heck of a lot easier to "rebuild" the log file (change the VLFs) by shrinking it to 0 and regrowing it.
Which is kinda unrelated to the OP's question on shrinking a data file
Of course, you either have to do a full or diff backup to get back to Point-in-time backups if they were being taken.
Err, no. Shrinks on the log file don't break the log chain.

Ok, I'm embarrassed. For some reason I looked at "single user mode" and saw the word "simple" as in "simple recovery mode". My apologies for the interuption.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)