July 16, 2015 at 8:01 am
I am in a pretty big situation here. My company has replication setup but it failed a couple of days past and now the .ldf file is massive and I am unable to shrink it, the file is currently 120 gig, it is set in simple but with replication running it looks to have disregarded the 20 gig limit that it was set to. I have tried DBCC shrinkfile to no avail as well as Tasks Shrink file. This has resulted in not being able to write to the database and having several backups that are massive. There are old backups, back to Monday before the issue started. Is there anyway that I can get a new .ldf file without losing information or am I going to have to go back to Monday's information and load that while losing information between then and now?
Thank you in advance
July 16, 2015 at 8:04 am
You'll need to remove the replication before you can do anything with the log. While the database is published, the log can't be made reusable until the log records are replicated. If the replication's broken, that won't be happening.
Add a new log file on a drive that has space (or expand the existing drive, or remove other things on the drive), remove the replication, then shrink the log back to a sensible size.
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
July 16, 2015 at 8:07 am
Thank you I appreciate the quick reply. Will be doing this asap.
July 16, 2015 at 8:59 am
To create a new .ldf file will I have to detach the database and then re-attach the database with a new .ldf file? This is 2005 server.
July 16, 2015 at 9:14 am
No. I didn't say destroy your database by deleting the log file. I said add a new log 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
July 16, 2015 at 9:19 am
Sorry, thank you.
July 16, 2015 at 10:14 am
Thank you again for your help. Everything looks as if it is back to normal and I was able to shrink the log file after removing the publication. For future reference is there anyway to stop the logging related to replication when the replication fails? Is there anyway to prioritize the database scanning and alarming transactions over replication? We would like to have our operational piece to always have priority across all server functions.
Thanks in advance
July 16, 2015 at 10:21 am
It's not logging related to replication.
When you have transactional replication, the space in the database's transaction log cannot be marked reusable until the log records have been replicated. Hence if the replication is broken the space in the log can't be reused. Hence the log will grow until it fills the disk.
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply