June 14, 2010 at 8:44 am
I am running 2005 with SP3, my replacted db is on simple mode,
I try to shrink the log file thought Management studio, but it is not shrinking, database is in simple mode.
I try following command but it is not shrinking
USE <replicated db>
GO
DBCC SHRINKFILE(repliczated_db_log.ldf, 10)
BACKUP LOG <replicated db> WITH TRUNCATEONLY
also I add onemore log file to run my database, now that drive is filling up too, can someone help me.
June 14, 2010 at 8:46 am
Why are you trying to shrink?
How much free space is in the log file?
What's the value of log_reuse_wait_desc in sys.databases for this DB?
p.s. there's no need for backup log .. truncate only on a database in simple recovery. Log will be auto truncated on checkpoint.
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
June 14, 2010 at 9:00 am
Why are you trying to shrink? I am running out of space
How much free space is in the log file? 25GB left.
one file have 100GB that is full
2nd have 35 GB that is 17GB full
What's the value of log_reuse_wait_desc in sys.databases for this DB? Replication
p.s. there's no need for backup log .. truncate only on a database in simple recovery. Log will be auto truncated on checkpoint.
it is not truncating the log.
June 14, 2010 at 9:43 am
MAK-1128556 (6/14/2010)
p.s. there's no need for backup log .. truncate only on a database in simple recovery. Log will be auto truncated on checkpoint.it is not truncating the log.
If checkpoint doesn't clear the log, running an explicit log truncation won't either. As the output from sys.databases clearly showed, the log is not being reused because of replication.
Is this database a publisher for transactional replication? if so, is the log reader job running?
The problem isn't shrinking the log, it's identifying why the log space is not being reused.
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
June 14, 2010 at 9:48 am
Thanks for helping me,
database is subscription database for transactional replication.
June 14, 2010 at 9:50 am
Subscriber? Not publisher?
Can you post the output of the following please?
DBCC OPENTRAN
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
June 14, 2010 at 9:53 am
Right now, but last night when no one user this database I can't shrink it
Transaction information for database 'DB_Replicated '.
Oldest active transaction:
SPID (server process ID): 53
UID (user ID) : -1
Name : user_transaction
LSN : (362134:127:1)
Start time : Jun 14 2010 11:51:49:863AM
SID : 0x0e6a4259aa0ac14f904c995eb5c293aa
Replicated Transaction Information:
Oldest distributed LSN : (318309:27942:217)
Oldest non-distributed LSN : (318310:16285:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 14, 2010 at 10:57 am
Ok, from the distributed and non-distributed this is clearly the publisher for some transactional replication. Check that the log reader is running.
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
June 14, 2010 at 11:09 am
Log reader is running, I will stop the logreader after the production is done tonight, then try to shrink the database.
if you know any other way pl let me know.
June 14, 2010 at 12:04 pm
No, no, no, no! You misunderstand. The log reader MUST be running or the log space cannot be reused. Stop it and you'll be making things worse, not better.
If the log reader is running, there must be something else wrong here. Is there high latency on the replication?
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
June 14, 2010 at 12:21 pm
Logreader is running, latency rate is good.
I don't now what else wrong.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply