Mdthbs

Has JSON.serialize suppressApexObjectNulls ever worked?

What did the 8086 (and 8088) do upon encountering an illegal instruction?

Is fission/fusion to iron the most efficient way to convert mass to energy?

Realistic, logical way for men with medieval-era weaponry to compete with much larger and physically stronger foes

Optimising matrix generation time

Why are backslashes included in this shell script?

Past vs. present tense when referring to a fictional character

typeid("") != typeid(const char*)

Do Veracrypt encrypted volumes have any kind of brute force protection?

Nth term of Van Eck Sequence

How can this shape perfectly cover a cube?

Why is it bad to use your whole foot in rock climbing

ISP is not hashing the password I log in with online. Should I take any action?

Can a 40amp breaker be used safely and without issue with a 40amp device on 6AWG wire?

Approach sick days in feedback meeting

What do I need to do, tax-wise, for a sudden windfall?

Why is gun control associated with the socially liberal Democratic party?

Short story about psychologist analyzing demon

What are the advantages of using TLRs to rangefinders?

How to represent jealousy in a cute way?

Any gotchas in buying second-hand sanitary ware?

How effective would a full set of plate armor be against wild animals found in temperate regions (bears, snakes, wolves)?

A flower's head or heart?

Lightning Web Component (LWC) not evaluating if:true from test

DBCC SHRINKFILE on the distribution database

Is it possible to shrink an .MDF file on a drive with low free space?Shrinking the log file does not reduce sizeStrange behaviour DBCC ShrinkfileShrink the database only up to its initial size which is set after creating databaseCannot drop distribution database in SQL Server 2012How to shrink the .ldf(log) file of the particular live production database?Need assistance shrinking MDF FileWhy did DBCC SHRINKFILE never complete but shrink successfully after cancellation?DBCC ShrinkFile not workingWhat is DBCC SHRINKFILE actually doing?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

4

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.?

sql-server replication transactional-replication shrink

share|improve this question

edited 9 hours ago

Ramakant Dadhichi

1,117320

asked 10 hours ago

user9516827user9516827

4581211

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  There would not be any data loss.
  
  – Shanky
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Shanky : This is the distribution database i am talking about and does this affect transactional replication i have in place?
  
  – user9516827
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Shrinking may slow things but would not cause any loss. Do it when load is relatively very less
  
  – Shanky
  10 hours ago
  

  
  
  
  

add a comment | 

4

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.?

sql-server replication transactional-replication shrink

share|improve this question

edited 9 hours ago

Ramakant Dadhichi

1,117320

asked 10 hours ago

user9516827user9516827

4581211

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  There would not be any data loss.
  
  – Shanky
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Shanky : This is the distribution database i am talking about and does this affect transactional replication i have in place?
  
  – user9516827
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Shrinking may slow things but would not cause any loss. Do it when load is relatively very less
  
  – Shanky
  10 hours ago
  

  
  
  
  

add a comment | 

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.?

sql-server replication transactional-replication shrink

share|improve this question

edited 9 hours ago

Ramakant Dadhichi

1,117320

asked 10 hours ago

user9516827user9516827

4581211

share|improve this question

edited 9 hours ago

Ramakant Dadhichi

1,117320

asked 10 hours ago

user9516827user9516827

4581211

share|improve this question

edited 9 hours ago

Ramakant Dadhichi

1,117320

asked 10 hours ago

user9516827user9516827

4581211

edited 9 hours ago

Ramakant Dadhichi

1,117320

edited 9 hours ago

Ramakant Dadhichi

1,117320

asked 10 hours ago

user9516827user9516827

4581211

asked 10 hours ago

user9516827user9516827

4581211

2 Answers
2

active

oldest

votes

2

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.

share|improve this answer

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.?
  
  – user9516827
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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.
  
  – Ramakant Dadhichi
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – user9516827
  9 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  sp_MSdistribution_cleanup is more of a retention thing. So it deletes things which have been replicated (distributed) to subscriber.
  
  – Ramakant Dadhichi
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

3

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.

share|improve this answer

answered 10 hours ago

dbamexdbamex

1516

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f240384%2fdbcc-shrinkfile-on-the-distribution-database%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

2

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.

share|improve this answer

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.?
  
  – user9516827
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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.
  
  – Ramakant Dadhichi
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – user9516827
  9 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  sp_MSdistribution_cleanup is more of a retention thing. So it deletes things which have been replicated (distributed) to subscriber.
  
  – Ramakant Dadhichi
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

2

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.

share|improve this answer

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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.?
  
  – user9516827
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  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.
  
  – Ramakant Dadhichi
  9 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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?
  
  – user9516827
  9 hours ago
  
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  sp_MSdistribution_cleanup is more of a retention thing. So it deletes things which have been replicated (distributed) to subscriber.
  
  – Ramakant Dadhichi
  8 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

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

  
  
  
  

add a comment | 

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.

share|improve this answer

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

share|improve this answer

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

answered 9 hours ago

Ramakant DadhichiRamakant Dadhichi

1,117320

3

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.

share|improve this answer

answered 10 hours ago

dbamexdbamex

1516

add a comment | 

3

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.

share|improve this answer

answered 10 hours ago

dbamexdbamex

1516

add a comment | 

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.

share|improve this answer

answered 10 hours ago

dbamexdbamex

1516

share|improve this answer

answered 10 hours ago

dbamexdbamex

1516

answered 10 hours ago

dbamexdbamex

1516

answered 10 hours ago

dbamexdbamex

1516