March 11, 2014 at 6:35 am
I have a database (Recovery Model - Simple) on SQLServer-2012. This database is replicated with Push subscription using the Snapshot Replication. The log file is 150GB whereas the database is 10GB. I tried various methods to truncate/shrink the logfile/database however none worked.
-- I took the full backup in Simple Recovery model and tried to truncate
-- I changed the model from Simple to Full took both Full as well as Transaction log backup then tried to truncate the log
Please suggest how I can reduce the size of the Logfile.
R
March 11, 2014 at 7:05 am
Take a read through this: http://qa.sqlservercentral.com/articles/Transaction+Logs/72488/
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
March 11, 2014 at 7:09 am
First u can check, is there any long running transactions ? and is there any blocking in db ?
March 11, 2014 at 8:44 am
search web for: log reuse wait desc replication sql server
Replication is an obvious possibility here. Can't flush out stuff if replication isn't satisfied for tlog'd stuff. Could also be long-running transaction like someone else suggested.
SELECT log_reuse_wait_desc, * FROM MASTER.sys.databases
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 11, 2014 at 8:48 am
SELECT log_reuse_wait_desc, * FROM MASTER.sys.databases
Not sure how many databases you have on your server so you might want to limit the results that are coming back to only the database in question like so:select log_reuse_wait_desc from sys.databases where database_id = db_id('DATABASENAME')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply