Log file is not shrinking
SQL Server 2008 Enterprise (I know! #wink. I am working on a migration plan) production server, in FULL recovery mode. I have a database that is used as a source for SQL replication. Daily Full backups and 2-hours incrementals.
I have a log file that has grown a lot (123Go) due to a one-time operation, and that I can't seem to shrink back down to a more normal level.
- log_reuse_wait_desc = 'NOTHING'
- DBCC Loginfo show a single line with the status = 2
- DBCC SQLPERF(LOGSPACE) shows 0.9% used space
- sys.sysprocesses WHERE open_tran = 1 shows the SLEEPING reader agents
- DBCC OPENTRAN shows the oldest non-distributed as (0:0:0)
When I try to run a small increment shrink, DBCC SHRINKFILE (N'X_log', 120360)
I get:
Cannot shrink log file 2 (X_log) because requested size is larger than the start of the last logical log file.
What else can I try to get what transaction is blocked in the LOG? I will not switch to SIMPLE, I will not backup to NUL, or truncate the LOG, or break my backup chain unless there are clearly no other solution. All suggestions I found seem to somehow assume it is a viable method to break the chain or reboot the server.
What diagnostic command can I still try at this point? I would very much like to debug that one without resorting to Kill-the-spider-with-a-flamethrower actions.
Thanks
sql-server sql-server-2008 replication shrink edited Nov 16 '18 at 17:58
marc_s
570k12811031251
asked Nov 16 '18 at 15:59
Philippe
162
add a comment |
SQL Server 2008 Enterprise (I know! #wink. I am working on a migration plan) production server, in FULL recovery mode. I have a database that is used as a source for SQL replication. Daily Full backups and 2-hours incrementals.
I have a log file that has grown a lot (123Go) due to a one-time operation, and that I can't seem to shrink back down to a more normal level.
- log_reuse_wait_desc = 'NOTHING'
- DBCC Loginfo show a single line with the status = 2
- DBCC SQLPERF(LOGSPACE) shows 0.9% used space
- sys.sysprocesses WHERE open_tran = 1 shows the SLEEPING reader agents
- DBCC OPENTRAN shows the oldest non-distributed as (0:0:0)
When I try to run a small increment shrink, DBCC SHRINKFILE (N'X_log', 120360)
I get:
Cannot shrink log file 2 (X_log) because requested size is larger than the start of the last logical log file.
What else can I try to get what transaction is blocked in the LOG? I will not switch to SIMPLE, I will not backup to NUL, or truncate the LOG, or break my backup chain unless there are clearly no other solution. All suggestions I found seem to somehow assume it is a viable method to break the chain or reboot the server.
What diagnostic command can I still try at this point? I would very much like to debug that one without resorting to Kill-the-spider-with-a-flamethrower actions.
Thanks
sql-server sql-server-2008 replication shrink edited Nov 16 '18 at 17:58
marc_s
570k12811031251
asked Nov 16 '18 at 15:59
Philippe
162
Maybe this could shed some light: What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions? - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery"
– Peter B
Nov 16 '18 at 16:24
But taking a transaction log backup truncates the log! If you do not want to truncate the log, do you perform transaction log backups? :) Post the results ofDBCC LogInfo;here, please! It may help diagnosing the issue.
– Andrey Nikolov
Nov 17 '18 at 8:41
@AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain.
– Philippe
Nov 19 '18 at 13:35
add a comment |
SQL Server 2008 Enterprise (I know! #wink. I am working on a migration plan) production server, in FULL recovery mode. I have a database that is used as a source for SQL replication. Daily Full backups and 2-hours incrementals.
I have a log file that has grown a lot (123Go) due to a one-time operation, and that I can't seem to shrink back down to a more normal level.
- log_reuse_wait_desc = 'NOTHING'
- DBCC Loginfo show a single line with the status = 2
- DBCC SQLPERF(LOGSPACE) shows 0.9% used space
- sys.sysprocesses WHERE open_tran = 1 shows the SLEEPING reader agents
- DBCC OPENTRAN shows the oldest non-distributed as (0:0:0)
When I try to run a small increment shrink, DBCC SHRINKFILE (N'X_log', 120360)
I get:
Cannot shrink log file 2 (X_log) because requested size is larger than the start of the last logical log file.
What else can I try to get what transaction is blocked in the LOG? I will not switch to SIMPLE, I will not backup to NUL, or truncate the LOG, or break my backup chain unless there are clearly no other solution. All suggestions I found seem to somehow assume it is a viable method to break the chain or reboot the server.
What diagnostic command can I still try at this point? I would very much like to debug that one without resorting to Kill-the-spider-with-a-flamethrower actions.
Thanks
sql-server sql-server-2008 replication shrink edited Nov 16 '18 at 17:58
marc_s
570k12811031251
asked Nov 16 '18 at 15:59
Philippe
162
SQL Server 2008 Enterprise (I know! #wink. I am working on a migration plan) production server, in FULL recovery mode. I have a database that is used as a source for SQL replication. Daily Full backups and 2-hours incrementals.
I have a log file that has grown a lot (123Go) due to a one-time operation, and that I can't seem to shrink back down to a more normal level.
- log_reuse_wait_desc = 'NOTHING'
- DBCC Loginfo show a single line with the status = 2
- DBCC SQLPERF(LOGSPACE) shows 0.9% used space
- sys.sysprocesses WHERE open_tran = 1 shows the SLEEPING reader agents
- DBCC OPENTRAN shows the oldest non-distributed as (0:0:0)
When I try to run a small increment shrink, DBCC SHRINKFILE (N'X_log', 120360)
I get:
Cannot shrink log file 2 (X_log) because requested size is larger than the start of the last logical log file.
What else can I try to get what transaction is blocked in the LOG? I will not switch to SIMPLE, I will not backup to NUL, or truncate the LOG, or break my backup chain unless there are clearly no other solution. All suggestions I found seem to somehow assume it is a viable method to break the chain or reboot the server.
What diagnostic command can I still try at this point? I would very much like to debug that one without resorting to Kill-the-spider-with-a-flamethrower actions.
Thanks
sql-server sql-server-2008 replication shrink sql-server sql-server-2008 replication shrink edited Nov 16 '18 at 17:58
marc_s
570k12811031251
asked Nov 16 '18 at 15:59
Philippe
162
edited Nov 16 '18 at 17:58
marc_s
570k12811031251
asked Nov 16 '18 at 15:59
Philippe
162
edited Nov 16 '18 at 17:58
marc_s
570k12811031251
edited Nov 16 '18 at 17:58
marc_s
570k12811031251
edited Nov 16 '18 at 17:58
marc_s
570k12811031251
570k12811031251
asked Nov 16 '18 at 15:59
Philippe
162
asked Nov 16 '18 at 15:59
Philippe
162
asked Nov 16 '18 at 15:59
Philippe
162
162
Maybe this could shed some light: What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions? - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery"
– Peter B
Nov 16 '18 at 16:24
But taking a transaction log backup truncates the log! If you do not want to truncate the log, do you perform transaction log backups? :) Post the results ofDBCC LogInfo;here, please! It may help diagnosing the issue.
– Andrey Nikolov
Nov 17 '18 at 8:41
@AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain.
– Philippe
Nov 19 '18 at 13:35
add a comment |
Maybe this could shed some light: What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions? - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery"
– Peter B
Nov 16 '18 at 16:24
But taking a transaction log backup truncates the log! If you do not want to truncate the log, do you perform transaction log backups? :) Post the results ofDBCC LogInfo;here, please! It may help diagnosing the issue.
– Andrey Nikolov
Nov 17 '18 at 8:41
@AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain.
– Philippe
Nov 19 '18 at 13:35
Maybe this could shed some light: What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions? - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery"
– Peter B
Nov 16 '18 at 16:24
Maybe this could shed some light: What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions? - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery"
– Peter B
Nov 16 '18 at 16:24
But taking a transaction log backup truncates the log! If you do not want to truncate the log, do you perform transaction log backups? :) Post the results of
DBCC LogInfo; here, please! It may help diagnosing the issue.– Andrey Nikolov
Nov 17 '18 at 8:41
But taking a transaction log backup truncates the log! If you do not want to truncate the log, do you perform transaction log backups? :) Post the results of
DBCC LogInfo; here, please! It may help diagnosing the issue.– Andrey Nikolov
Nov 17 '18 at 8:41
@AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain.
– Philippe
Nov 19 '18 at 13:35
@AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain.
– Philippe
Nov 19 '18 at 13:35
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f53341407%2flog-file-is-not-shrinking%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- 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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2fstackoverflow.com%2fquestions%2f53341407%2flog-file-is-not-shrinking%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
Maybe this could shed some light: What does it mean when DBCC LOGINFO returns many rows with status=2 and DBCC OPENTRAN returns no active open transactions? - Possible line of interest: "Only a LOG backup will free the VLF's for reuse under FULL recovery"
– Peter B
Nov 16 '18 at 16:24
But taking a transaction log backup truncates the log! If you do not want to truncate the log, do you perform transaction log backups? :) Post the results of
DBCC LogInfo;here, please! It may help diagnosing the issue.– Andrey Nikolov
Nov 17 '18 at 8:41
@AndreyNikolov I mean I do not want to do log backups or truncates outside of the regular backup schedule. We are using a 3rd party tool to handle backups (CommVault) so I don't want to risk breaking the backup chain.
– Philippe
Nov 19 '18 at 13:35