I have a DBs where the log file is 90GB now. It grew to be 90 because the log backup job was paused for couple of hours. So I ran the log backup (which ran successfully and took about 20+ minutes) and then I tried shrinking the log file size to 20480 (because that's what ideal for this DBs) and it's not shrinking. There is no open transaction so I ran the log backup again (which ran successfully and took about 7 seconds) , tried shrinking the log file again to 20480 but it is staying at 90 GB. I went to DB->tasks->shrink->log and on the property, it says the available free space is 0% so I can't shrink the log file. The server has plenty of space available. I mean there is activity on the database but nothing seems out of normal. What am I missing here?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Sorry, found a solution. We have a DAG and someone was working on DR server so DBs is out of sync and that's what causing the issue.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 5, 2020 at 10:15 am
Hi,
if you got this error next time, just take a look at this query:
SELECT
name AS DatabaseName,
log_reuse_wait_desc AS LogBlocking,
recovery_model_desc AS RecoveryModel
FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb') AND log_reuse_wait_desc NOT LIKE 'NOTHING'
With this code, you are able to look, why the LOG won't shrink.
Kind regards,
Andreas
May 5, 2020 at 12:13 pm
Hi,
if you got this error next time, just take a look at this query:
SELECT
name AS DatabaseName,
log_reuse_wait_desc AS LogBlocking,
recovery_model_desc AS RecoveryModel
FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb') AND log_reuse_wait_desc NOT LIKE 'NOTHING'With this code, you are able to look, why the LOG won't shrink.
Kind regards,
Andreas
I did and that's when I knew that the DR server was getting worked on and was offline. As soon as it came back online, all DBs were in sync, reran the log backup, shrunk the log file and that's about it.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply