Unexpected growth Transaction log file 100 GB which is part of Transactional Replication
I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.
Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.
While database under testing mode...
Subscriber of replication is also primary replica of Always On Availability Group.
How to reduce the size of transaction log file?
I had taken log backup and in full recovery model.
SELECT name, log_reuse_wait_desc FROM sys.databases;
...returns LOG_BACKUP.
Replication is running successfully. Subscriber is receiving changes from publisher.
sql-server replication sql-server-2014 transaction-log transactional-replication
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.
Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.
While database under testing mode...
Subscriber of replication is also primary replica of Always On Availability Group.
How to reduce the size of transaction log file?
I had taken log backup and in full recovery model.
SELECT name, log_reuse_wait_desc FROM sys.databases;
...returns LOG_BACKUP.
Replication is running successfully. Subscriber is receiving changes from publisher.
sql-server replication sql-server-2014 transaction-log transactional-replication
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Please can you post the output of sp_replcounters on the publisher, thanks.
– T.H.
Feb 15 '17 at 9:09
add a comment |
I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.
Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.
While database under testing mode...
Subscriber of replication is also primary replica of Always On Availability Group.
How to reduce the size of transaction log file?
I had taken log backup and in full recovery model.
SELECT name, log_reuse_wait_desc FROM sys.databases;
...returns LOG_BACKUP.
Replication is running successfully. Subscriber is receiving changes from publisher.
sql-server replication sql-server-2014 transaction-log transactional-replication
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.
Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.
While database under testing mode...
Subscriber of replication is also primary replica of Always On Availability Group.
How to reduce the size of transaction log file?
I had taken log backup and in full recovery model.
SELECT name, log_reuse_wait_desc FROM sys.databases;
...returns LOG_BACKUP.
Replication is running successfully. Subscriber is receiving changes from publisher.
sql-server replication sql-server-2014 transaction-log transactional-replication
sql-server replication sql-server-2014 transaction-log transactional-replication
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
edited Jul 4 '17 at 13:04
Paul White♦
50.7k14277447
50.7k14277447
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
asked Feb 14 '17 at 7:07
Gulrez KhanGulrez Khan
116114
116114
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 4 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
Please can you post the output of sp_replcounters on the publisher, thanks.
– T.H.
Feb 15 '17 at 9:09
add a comment |
Please can you post the output of sp_replcounters on the publisher, thanks.
– T.H.
Feb 15 '17 at 9:09
Please can you post the output of sp_replcounters on the publisher, thanks.
– T.H.
Feb 15 '17 at 9:09
Please can you post the output of sp_replcounters on the publisher, thanks.
– T.H.
Feb 15 '17 at 9:09
add a comment |
1 Answer
1
active
oldest
votes
You may run below scripts to check the log reuse wait of the database(s):
SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
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
});
}
});
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f164243%2funexpected-growth-transaction-log-file-100-gb-which-is-part-of-transactional-rep%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You may run below scripts to check the log reuse wait of the database(s):
SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
add a comment |
You may run below scripts to check the log reuse wait of the database(s):
SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
add a comment |
You may run below scripts to check the log reuse wait of the database(s):
SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
You may run below scripts to check the log reuse wait of the database(s):
SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
answered Feb 14 '17 at 7:31
Anson DaiAnson Dai
112
112
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f164243%2funexpected-growth-transaction-log-file-100-gb-which-is-part-of-transactional-rep%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please can you post the output of sp_replcounters on the publisher, thanks.
– T.H.
Feb 15 '17 at 9:09