January 4, 2012 at 12:15 pm
I was using database mirroring with a witness to move a database from a server to a cluster. The principal state was on the sever, when the firewall between the 2 networks crashed. I wasn’t able to backup the database on the server because it was disconnected.
I physically moved the server to the other network & connected it. The database on the server was still disconnected, I assume because the IP address had been in a different VLAN. I changed the IP address so that I could access the server in the new LAN. Then I killed mirroring on the server:
Alter database x set witness off
Alter database x set partner off
Restore database x with recovery
Then I noticed my web servers were connecting to the database in the cluster. I thought, well this is ok; that’s what I wanted anyway.
I created maintenance plans on the cluster & checked to make sure all was well.
I’ve a problem that I don’t know how to resolve. The data portion of the database is 9 GB, but the log is 28 GB. This happened on the server, since it was low on freespace & no one caught the full backup failure over the weekend. Now that the database is on the cluster I’m stuck with a log file that won’t go below 28 GB. After a transaction log backup, I’m still unable to shrink the log file; the full backup is 38 GB, the log backups are 28 GB+ . I tried changing the recovery model from full to simple & back to full, but I still couldn’t shrink the log file. How do I shrink the log file? I need to do something soon, because I don’t have enough freespace to keep too many backups this size.
Thanks in advance
January 4, 2012 at 12:43 pm
Sounds like you have a stuck transaction in the log file, or a LOT of VLOG files. Are you familiar with the t-log physical structure? http://msdn.microsoft.com/en-us/library/ms179355.aspx
There's data here on things that can delay truncation: http://msdn.microsoft.com/en-us/library/ms345414.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2012 at 2:24 pm
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
January 5, 2012 at 3:04 pm
Yes I am familiar with the log file structure. The good new is that my log backups are permitting some of the entries from being deleted & the space being reused. Also, good is that there is ample freespace on the drive with the logs file, so that it can grow.
Before I got involved in this escapade, an earlier attempt was made to replicate the database from the 2005 instance to the 2008 instance. I believe there were some errors & questions about that strategy.
The next step was to mirror from 2005 to 2008. There were problems with that, especially failing back.
Then I got involved & upgraded the 2005 instance to 2008 by mirroring to the new server. Then I created the mirror from the old data center to the new data center. When the network connection between the 2 data centers crashed & I killed the mirror was when I noticed the issues.
Now when I run the query to determine the cause of the stuck transaction log, I see that Replication is the cause. And further investigation shows a Local Publication for the database on the new server, which I cannot delete.
The replication between the original server & the subscriber server is gone - i.e. the virtual server (subscriber) has been deleted. I do have the 'push' server.
I'm still at a loss as to how to free the records from the transaction log. Any advice is truly appreciated.
January 5, 2012 at 4:16 pm
Why can't you delete the local publication?
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
January 6, 2012 at 8:59 am
Error: Invalid column name 'originator_id'. (Microsot SQL Server, Error: 207)
January 6, 2012 at 9:10 am
Based on information I found in Gila Monster's initial respone - I followed the link to blogs.msdn..... 'Size of the Transaction Log Increasing and cannot be truncated or Shrinked due to Snapshot Replication'
I then did the following tasks:
Created a new backup
ran the following command: sp_repldone null, null, 0,0,1
changed from Full to Simple recovery
Shrink the log file
I've re-enabled Full recovery & backups look good.
Thanks for your suggestions - I've learned a few new things because of you assistance & thank both of you.
Big Sam
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply