January 23, 2014 at 7:48 am
We are running MS System Center Operations Manager (SCOM) with 2 nodes, always on, with SQL Server 2012. Our transaction logs are growing and filling up the the disk to capacity. The DBs are in full recovery mode, obviously. I can't switch the DBs to simple without removing them from the availability group. How else can I shrink the tlogs?
Thanks.
Joe
January 23, 2014 at 8:03 am
Joe.Spitler (1/23/2014)
We are running MS System Center Operations Manager (SCOM) with 2 nodes, always on, with SQL Server 2012. Our transaction logs are growing and filling up the the disk to capacity. The DBs are in full recovery mode, obviously. I can't switch the DBs to simple without removing them from the availability group. How else can I shrink the tlogs?Thanks.
Joe
Are you taking transaction log backups?
If so, What frequency are the backups occuring (e.g. every 15 mins, every 30 mins, etc)?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 23, 2014 at 8:06 am
Joe.Spitler (1/23/2014)
We are running MS System Center Operations Manager (SCOM) with 2 nodes, always on, with SQL Server 2012. Our transaction logs are growing and filling up the the disk to capacity. The DBs are in full recovery mode, obviously. I can't switch the DBs to simple without removing them from the availability group. How else can I shrink the tlogs?Thanks.
Joe
If I had a nickel for every time I have seen this on a forum... 😀
This is almost always caused by failing to do some form of TLOG backup. FULL backups do NOT flush committed transactions from the tlog, and it will grow indefinitely until it fills up a disk.
Since you have mirroring in place a potential cause is that the mirroring is backed WAY up for some reason. You can see this using DMVs or using the Always On monitor stuff.
Once in a blue moon I see this caused by someone accidentally keeping a transaction open for days/weeks/months. sp_whoisactive can help there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2014 at 8:10 am
I have tried to due a full backup, then tlog backup, then shrink. NO errors, it just doesn't do it.
January 23, 2014 at 8:15 am
DBCC LOGINFO and you will see your last virtual log status is 2. I guess you need to make it 0 and then shrink....
January 23, 2014 at 8:15 am
Shrinking a tlog will not necessarily shrink it, or shrink it to a size you want (which should NOT be a tiny size, btw). It depends on where the log physically is amongst the various Virtual Log Files inside the actual file. As transactions are added eventually it will cycle back around to the first VLF at the beginning of the physical file and then it should shrink nicely. This does assume there aren't any open (or unsent across to the secondary) transactions later in the file.
This may provide some helpful information about your VLF picture:
DBCC LOGINFO
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 23, 2014 at 8:30 am
Thanks, everyone, but we already know all that. As it turns out we discovered what the problem was. In our backup preferences it was set to the default setting, which was "Prefer Replica'. I change it to 'Primary'. Then after the tlog backup the logs shrank just fine.
Thanks, all.
January 23, 2014 at 9:01 am
you might want to find out what is causing the log to blow in the first place 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply