April 3, 2009 at 6:08 am
Hi All,
I have a database with simple recobery model with snapshot replication setup.Need to shrink the log as it taken all the disk space. I tried all the possible way but it gives me the following error message:
Cannot shrink log file 2 (mylog.Log) because of minimum log space required.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
1. Tried to truncate the log followed dbcc shrink file command
2. Changed the DB model from Simple to Full took a full DB backup to a diff netwrok location and then tried to shrink still it gives me the same error.
3.I had already reintilized the replication and both the publisher and subscriber made them in sync
But still I receive the same error.
Please help me for this issue.
"More Green More Oxygen !! Plant a tree today"
April 3, 2009 at 6:29 am
You cannot shrink the log file size lesser than the initial setting.
SELECT * FROM sys.databases
Look for log_reuse_wait. What does it say?
April 3, 2009 at 7:47 am
REPLICATION for the specified DB. I know snapshot replication have been setup.
also dbss sqlperf(logspace) gives me 100 % space used for the same DB.
I dont have disk space. But need to shrink it.
What will be my next step?
"More Green More Oxygen !! Plant a tree today"
April 3, 2009 at 7:51 am
truncate the log manually with backup log
or reinitialize replication. i've had times when replication would not truncate the log after the commands were replicated and had to reinitialize some publications
April 3, 2009 at 8:12 am
I had reintilized the replication and but not able to truncate it as it gives file in use error message.
The error message which I had mentioned in the post above.
"More Green More Oxygen !! Plant a tree today"
April 3, 2009 at 12:53 pm
You can try a few steps, in simple recovery model the t-log automatically truncates after each checkpoint, this tells me that your disk space is not sufficent, should try getting more disk space.
Goto, Full recovery model, then take full db backup, then a t-log backup & if you need to shrink the t-log , use backup log with truncate_only command. This will break your LSN chain, so immediately take a full backup.
Move the t-log to a different drive with space.
Detach the db & then attach only the .mdf file , SQL will create new log file for you.
Check BOL for details on how to do above steps.
April 6, 2009 at 9:25 am
THe following steps helped me to solved the issue.:-)
SELECT [name], [log_reuse_wait], [log_reuse_wait_desc]
FROM sys.databases
My result was:
name log_reuse_wait log_reuse_wait_desc
dbname 6 REPLICATION
The transaction marked for replication that has not yet replicated is considered the same as an uncommitted transaction.
By running the below script I was able to truncate & shrink my Tlog.
EXEC sp_repldone @xactid = NULL
, @xact_segno = NULL
, @numtrans = 0
, @time = 0
, @reset = 1
"More Green More Oxygen !! Plant a tree today"
April 7, 2009 at 3:19 am
Forcing a log truncation is never a good idea, except in an emergency situation.
Did you find out why replication had not procesed some of your work? If not, then a good idea is to start a new thread about your replication problem, to try to get a solution that will stop it happening again.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 7, 2009 at 3:37 am
I agree with Ed, you should never truncate your transaction logs unless an extreme emergency.
More importantly, you need to understand why you have queued replication commands outstanding, as the problem will more than likely surface again. I suspect you have your distribution database on same storage volume, which may be contributing factor?
Thanks,
Phillip Cox
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply