October 14, 2008 at 2:44 am
I have a database using replication (snapshot) and the log file for the database is very very large. I can shrink it if I delete the replication setup back down to a sensible size, however the second I put replication back on, it creates a really large log file again.
Is there anything that can be done to reduce the file size of this log, or any settings in replication regarding what its actually logging.
October 14, 2008 at 3:33 am
What recovery model? Do you have log backups?
What type of replication? If transactional, is the log reader agent running properly?
What does the following return for the DB in question?
SELECT name, recovery_model_desc, log_reuse_wait_desc from sys.databases
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
October 14, 2008 at 3:39 am
The response to your query is SIMPLE and NOTHING
It runs a simple snapshot replication once a night.
October 14, 2008 at 3:56 am
Then the log file size isn't related to replication. Snapshot doesn't use the tran log. It just copies the entire DB to the subscriber. Since the DB's in simple recovery model, the transactions won't stay in the log long and since it's not transactional there's no log impact there
The loading of the snaphot could cause the log to grow if it's a lot of info that has to be added. Is the problem with the publisher or the subscriber
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
October 14, 2008 at 4:29 am
The problem is with the publisher. But is only probably 50 or so records that are updated a day, so it shouldnt be alot
October 14, 2008 at 5:34 am
A snapshot doesn't just move the updated rows, it will copy the entire database to the subscriber every day.
Still, since it's not transactional it shouldn't have a big (if any) impact on the tran log. What do you mean by large? How big's the DB in question?
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
October 14, 2008 at 6:23 am
database is 190MB
Logfile is about 2GIG
October 14, 2008 at 6:41 am
Ok, that is odd.
What does this return?
DBCC SQLPERF(logspace)
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