January 27, 2009 at 10:49 am
Hi,
I have a situation where the database is 800Mb and the log file is nearly 13gig. I am thinking it should not be this large and I should clear it out and reduce the size. We currently do a full backup nightly and backup the transaction log daily between 8am and 9pm every 1 hour. Last night after the full backup was run I backed up the transaction log and it still showed 99% used??? My situation is similar to http://www.sqlservercentral.com/Forums/Topic585284-357-1.aspx, so i have included some details:
running dbcc sqlperf(logspace) gives me:
DbName LogSize (MB) LogSpaceUsed(%) Status
myDB 12452.18 99.395 0
running dbcc opentran gives me:
Transaction information for database 'myDB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (3182:374:1)
running dbcc loginfo shows me that I have 465 rows and all have a status of 2
running SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc from sys.databases gives me:
myDB FULL6REPLICATION
A couple other notes. The initial size for the log file reads 12509, so it looks like when the db was created it was set that size from the start???. It was also originally set to auto growth by 10% which i just changed to a set mb amount. The database recovery model is set to full. There is replication setup. There is snapshot replication where this db is the publisher and publishes one table (has 1600 rows) nightly to 2 subscribers nightly. Could some please help me to understand how I can free up the log space, reduce the size and keep the replication if possible.
Thanks for any assistance
-rusty
January 28, 2009 at 1:31 pm
I was able to fix my issue. Mainly due to the other post and responses from Gail gave to it.
- I removed the snapshot replication that I had setup where the database in question was the publisher. immediately after doing so the logspace used went from 99% to roughly 1%
- I then backed up the log file to clear it out (not sure if this was necessary)
- I then shrunk the log file to the desired size since it was originally set at 12 gig or so.
- I then ran a full backup to start up the log chain
- I then set the snapshot replication back up and this did not affect the logspace
So after I was finished i re-ran by queries and got :
running dbcc sqlperf(logspace) gives me:
DbName LogSize (MB) LogSpaceUsed(%) Status
myDB 51.99219 15.1343 0
running dbcc loginfo shows me that I have 106 rows and only one has a status of 2
running SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc from sys.databases gives me:
myDB FULL 0 NOTHING
I hope this possibly helps someone else.
January 28, 2009 at 1:36 pm
Basically what happened there is that you had transactional replication set up (somehow), but the log reader wasn't running and never had run. Hence tran log entries that were marked 'to be replicated' never were, and log records that need to be replicated cannot be removed from the transaction log by a backup
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 28, 2009 at 7:41 pm
It is very possible that transactional replication was setup at some point in time. I just took over the db recently and the only replication that was setup was the snapshot that I created. Thank you very much for the explanation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply