By user9516827

2019-06-12 13:35:48 8 Comments

I have an .mdf file from distribution database which is growing. I know the advantages and disadvantages of shrinking database/files. Is it okay to shrink the .mdf file to some a smaller size. Is there any data loss during this time and does this affect the replication.?


@Ramakant Dadhichi 2019-06-12 13:59:26

You need to check WHY the distribution database is growing rather than trying to shrink. If there is no available free space inside the file then you will not be able to shrink it anyway.

Check for the retention period and any other reasons why the distibution database would be growing e.g. Replication is erroring out.Below blog would give some areas you can look into :

Distribution database growth

Answering your question exactly : Yes you can shrink the file ,given it has free space available.

@user9516827 2019-06-12 14:12:26

I have error in replication monitor in one of publisher/subscriber.The issue is on Distributor to subscriber.So i am trying to figure it out.Meanwhile can i turn off the Distribution agent so that the MSRepl_Commands don't get filled.?

@Ramakant Dadhichi 2019-06-12 14:49:20

If you Stop Distribution agent, the log reader agent will still keep pushing data to for Distribution. You should stop the replication all together and check out for the actual issue. Once found,fix and re-enable.

@user9516827 2019-06-12 14:56:45

I understand what you are saying.I checked and MSRepl_Commands have 422 millions records.If i run the sp EXEC dbo.sp_MSdistribution_cleanup @min_distretention = x, @max_distretention = y and if it removes the commands from the MsRepl_Commands table,then i would loose the changes that needs to be applied to subsciber since i have error in replication.Am i right?

@Ramakant Dadhichi 2019-06-12 15:14:23

sp_MSdistribution_cleanup is more of a retention thing. So it deletes things which have been replicated (distributed) to subscriber.

@user9516827 2019-06-12 15:18:43

One final question: If i disable the replication and enable it after a day ,do i loose all those transactions in between.?

@Ramakant Dadhichi 2019-06-13 07:31:56

Yes unless you re-initialize it.

@dbamex 2019-06-12 13:39:49

It is ok to shink the datafile, there WON'T be any data loss.

The "issues" when shrinking could be performance, during the rellocation of data pages. Also if you know that your DF will grow again to the same size, shrink is not recommended. If it was because a one time only operation, or other stuff where the DF won't grow at the same big size again, it is ok to perform your shrink operation.

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] What is DBCC SHRINKFILE actually doing?

  • 2019-04-04 07:50:18
  • nobodyofnaught
  • 264 View
  • 5 Score
  • 1 Answer
  • Tags:   sql-server shrink

6 Answered Questions

[SOLVED] Shrinking the log file does not reduce size

3 Answered Questions

1 Answered Questions

[SOLVED] Cannot drop distribution database in SQL Server 2012

1 Answered Questions

[SOLVED] Strange behaviour DBCC Shrinkfile

5 Answered Questions

Sponsored Content