November 18, 2014 at 7:32 pm
Hi There,
i have been looking on internet and so many people has same issue still i can't come to a conclusion why i am seeing this on one of my production server.
log file is 12GB
DBCC SQLPERF(LOGSPACE) 0.017 % used
t-log backup runs every hour successfully
sys.databases ->log_reuse_wait_desc LOG_BACKUP
is_published 0
is_subscribed 0
is_merge_published 0
is_distributor 0
no open transactions, shrink doesn't work
DBCC LOGINFO returns 539 rows with status 0
no idea what is the root cause. please advise how to fix it
November 19, 2014 at 4:33 am
Do you use mirroring?
November 19, 2014 at 4:37 am
DBA_AUS (11/18/2014)
sys.databases ->log_reuse_wait_desc LOG_BACKUP
The log is not getting cleared because there's a log backup needed.
DBCC SQLPERF(LOGSPACE) 0.017 % used
However the log is almost entirely empty and is available to be reused, so the log definitely is getting emptied
From what you've said, I can't see a problem, it looks fine. What are you concerned about?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 19, 2014 at 6:04 am
no we don't use database mirroring. alway on as configured which was later disabled.
my concern is why size of log file doesn't reduce?
November 19, 2014 at 6:47 am
The log file size is not supposed to decrease. That would be incredibly inefficient if SQL had to keep growing and shrinking the file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 20, 2014 at 3:26 pm
thanks that make sense. I was under the impression that after t-log backup if space is not in use it should be released to OS.
if this is the case than tempdb does shrink itself. different mechanism for tempdb?
November 20, 2014 at 6:06 pm
DBA_AUS (11/20/2014)
thanks that make sense. I was under the impression that after t-log backup if space is not in use it should be released to OS.if this is the case than tempdb does shrink itself. different mechanism for tempdb?
tempdb does not shrink itself after a transaction completes without manual intervention or somebody having written a process to periodically shrink it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 20, 2014 at 6:40 pm
on one of our server tempd db grows upto 150GB and beyond. so we moved it onto a seperate disk. i have seen it goes upto 150 + and than nextday or so it was 50GB. no one restart the instance. confused how it happened?
November 21, 2014 at 7:47 am
DBA_AUS (11/20/2014)
on one of our server tempd db grows upto 150GB and beyond. so we moved it onto a seperate disk. i have seen it goes upto 150 + and than nextday or so it was 50GB. no one restart the instance. confused how it happened?
Then somebody is shrinking it or somebody implemented a process (and forgot about it) to shrink tempdb.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply