November 4, 2008 at 11:28 am
Hello, I have a database being replciated to different places. The log is getting huge, but I ma afraid of truncating the log and screw up replication. Can I safely truncate the log? By stopping replication, truncate the log, and then run the snapshot again and reinitialize replication.
Is this safe? :w00t:
November 4, 2008 at 11:42 am
You can, but unless you figure out why the log is growing, it's just going to get back to that size in time.
What recovery model?
If full or bulk-logged, how often are you backing up the transaction log?
Is your 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
Oh, and you may want to give this a read - http://www.sqlservercentral.com/articles/64582/
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
November 4, 2008 at 11:42 am
How about increasing the frequency of your tlog backups? And make sure your logreader is working, otherwise your log space won't be cleared if logreader isn't running even after you do a log backup.
_____________
Donn Policarpio
November 4, 2008 at 11:53 am
Thanks.
Log reader is running fine. I just stareted with this company and inherited a spaghetti soup. No consistency on configuration, backups are all over the place, some databases are full mode, some others simple, even though theya re mission critical. A mess!
This particular in full recovery, but no transaction log backup was set. I created a job to run logs every hour on the hour. I have been monitoring the size. Since I started the log job, the size has not increased much. I just want to regain the space as the transaction log backup as well asn the nightly full back covers me of any issue. I also scripted all the replication sets and can recreated them very easy.
I can do all that. disable replication, get control of the database with no possibility of replication issues, truncate the log, recreate the whole set of replication and start fresh.
Just wanted to know if is safe to truncate it as it is today, just stopping replication (or not) and move forward.
Any suggestion is very welcome. Thank you
November 4, 2008 at 12:00 pm
Fernando (11/4/2008)
Since I started the log job, the size has not increased much. I just want to regain the space as the transaction log backup as well asn the nightly full back covers me of any issue.
If you're doing regular log backups now, then the used space in the log should be low. Check that with DBCC SQLPERF(LogSpace). If there's a lot of space free, you can just shrink the file down to a reasonable size. No need to truncate it at all, doing so will mess up your log backups, though not the replication.
Just shrink the log file, not the entire database.
I just stareted with this company and inherited a spaghetti soup. No consistency on configuration, backups are all over the place, some databases are full mode, some others simple, even though theya re mission critical. A mess!
😀 Welcome to a DBA's life. Spaghetti soup seems to be the norm in many places.
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
November 4, 2008 at 12:05 pm
Thanks. I will use DBCC and then shrink it a normal size. :w00t:
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply