July 9, 2009 at 9:18 am
I have a replicated database I built back in May. The database is now 13Gb, but the log file is at 31Gb. I have 14Gb left on the drive. I've tried shrinking the file and backing up with truncate only, but it doesn't seem to help. This is a replicated database if that makes any difference. How can I get that log file back down to a reasonable size?
July 9, 2009 at 9:20 am
Query sys.databases. What's the value of log_reuse_wait_descr for that database?
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 9, 2009 at 9:56 am
reuse is at 6; description reads REPLICATION
Uh, sorry for the multiple posts, guys. I was getting an error that the post failed. Guess I should have checked to make sure it really did fail.
July 9, 2009 at 10:19 am
Ok, if the reason is replication then it means that the log reader either isn't running or isn't keeping up with the changes. The log records can't be discarded while they are pending replication (transactional replication only)
Can you confirm that the log reader is running for this publication and that the replicated changes are getting to the subscriber?
Can you run this in the DB in question and post the output.
DBCC OPENTRAN
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 9, 2009 at 10:36 am
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (2181:899:1)
July 9, 2009 at 10:44 am
That's saying that the transactional replication has never replicated a single transaction.
Is the replication on this server transactional?
If it is, are you seeing transactions replicating to the subscriber?
Is the log reader running?
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 9, 2009 at 12:01 pm
GilaMonster (7/9/2009)
That's saying that the transactional replication has never replicated a single transaction.Is the replication on this server transactional?
If it is, are you seeing transactions replicating to the subscriber?
Is the log reader running?
If I remember correctly, the replication occurs once a day.
How can I tell if the log reader is running?
July 9, 2009 at 12:14 pm
doug turner (7/9/2009)
If I remember correctly, the replication occurs once a day.
Transactional replication is constant. What kind of replication are you using?
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 9, 2009 at 12:27 pm
Ok, bear with me here. We're not doing transactional replication. We're actually doing a nightly replication to another database, then extracting data from that database. BUT, just in case, where can I go to see what kind of replication I have going on?
July 9, 2009 at 12:49 pm
Run sp_helppublication in the published database. In the results, the replication frequency will tell you what it is
Books Online
replication frequencyType of replication frequency:
0 = Transactional
1 = Snapshot
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 9, 2009 at 1:07 pm
replication frequency is set to 1
July 9, 2009 at 1:16 pm
In that case, somehow you've got a half-set up transactional publication. I really wish I knew how this happened.
The fix is fairly simple. Create a transactional replication publication. Add a single table to that publication. It can be any table. Finish the wizard, but don't create the snapshot, there's no need. Once that's completed, delete the publication. (yes, I know it sounds strange. It works)
Once you've done that, run DBCC OPENTRAN and query sys.databases again.
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 9, 2009 at 1:39 pm
Holy cr.....!!!! That worked like a charm! After doing all that, I ran the backup with truncate_only, then the dbcc_shrink and it went down to 104K!
I owe you a beer or lunch or dinner or something!
Uh, am I set, or will I need to do this little trick again?
July 9, 2009 at 2:02 pm
doug turner (7/9/2009)
After doing all that, I ran the backup with truncate_only, then the dbcc_shrink and it went down to 104K!
Now do a full backup, because you've just broken the log chain and will not be able to do log backups until a full backup is taken. Once you've done that, size the log for something reasonable based on the workload and frequency of log backups
Uh, am I set, or will I need to do this little trick again?
All set. You shouldn't need this again
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 10, 2009 at 5:17 am
Thanks a bazillion for all the help!
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply