October 20, 2014 at 12:21 am
db is in simple recovery model, log file is around 80GB,there is not much freespace to shrink file, trying to truncate but its not release the space.
SELECT [name], recovery_model_desc, log_reuse_wait_desc FROM sys.databases;---it showing replication.
so removed the replication sp_removedbreplication 'mydbname' and disabled replication also , but still 'log_reuse_wait_desc' is showing as 'replication'.
any suggestions to shrink my log file.
October 20, 2014 at 1:18 am
Is it simple model? Simple model is without log file.
Otherwise backup log file just before srinking.
Jacek
October 20, 2014 at 2:19 am
Jacek Falkiewicz (10/20/2014)
Is it simple model? Simple model is without log file.
Nope. Simple recovery uses the log just like full/bulk logged does.
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 20, 2014 at 2:20 am
charipg (10/20/2014)
db is in simple recovery model, log file is around 80GB,there is not much freespace to shrink file, trying to truncate but its not release the space.SELECT [name], recovery_model_desc, log_reuse_wait_desc FROM sys.databases;---it showing replication.
so removed the replication sp_removedbreplication 'mydbname' and disabled replication also , but still 'log_reuse_wait_desc' is showing as 'replication'.
Run CHECKPOINT, look at the log reuse wait again. What is it now?
Do you have CDC running/configured against this 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
October 20, 2014 at 2:55 am
GilaMonster (10/20/2014)
Jacek Falkiewicz (10/20/2014)
Is it simple model? Simple model is without log file.Nope. Simple recovery uses the log just like full/bulk logged does.
You are right GilaMonster. The difference is in simple mode we can not backup log file.
Charipg, had you tried example B from http://msdn.microsoft.com/en-gb/library/ms189493.aspx?
Maybe it is worthy to try opposite scenario: change to full, full backup, log backup and shrink at the end.
October 20, 2014 at 3:36 am
By the way, I found shrinking against SSMS always failed in my case.
I do not why, but following script always works for me (it shrinks data file, too):
declare @dbname nvarchar(120)
set @dbname=DB_NAME()
exec sp_helpdb @dbname-- check size of LOG file before changes
BACKUP database @dbname to disk='d:\temp\db_backup.bak' -- backup database
BACKUP LOG @dbname -- backup log file
DBCC SHRINKFILE ( file_log, 1)-- shrink log file
DBCC SHRINKFILE ( file_data, 1)-- shrink data file
DBCC SHRINKFILE ( file_log, 1)-- shrink log file
exec sp_helpdb @dbname-- check size of LOG file after changes
Please remember to change file_log and file_data with names from your database.
Jacek
October 20, 2014 at 3:39 am
Jacek Falkiewicz (10/20/2014)
Maybe it is worthy to try opposite scenario: change to full, full backup, log backup and shrink at the end.
Completely pointless and a waste of time.
The reason the log can't be reused is either replication (transactional) or change data capture. Nothing to do with a log backup, which you can't do and don't need to do in simple recovery model. The OP has another thread on CDC job failures, so assuming this is the same database, the log is not being reused because the CDC job isn't running. The fix would be to get the CDC job working again or to remove CDC.
Edit: Shrinking a data file is not in any way required to shrink a log, and is a generally very counter-productive. Firstly the shrink of the data file is logged, and so may well make the log file grow, second it causes massive index fragmentation which, when fixed with an index rebuild, will likely grow the log and data file.
The log should not be shrunk to 0, that's just going to force SQL to grow it again. If a log has grown beyond what it needs to be for some unusual reason (failed CDC job, broken replication, long-running transaction, etc) then after the root cause is fixed the log should just be shrunk to the size it needs to be for regular activity.
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 20, 2014 at 3:49 am
Use dbcc sqlperf(logspace) to check the free space available in the log file and shrink accordingly.
You cannot take a log backup on a database which is in simple recovery.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply