| Author |
Topic |
|
Significant_Other
Starting Member
5 Posts |
 Posted - 2009-09-21 : 11:45:08
|
| My tran log is growing very large, over 80gb's as of this morning. I have 15 minute tran log backup's occuring from DPM 2007. The backups are successful.The backup is not truncating the log file and a shrink has no effect. The file momentarily shrinks by a few gigs, but then it jumps back up.I am running out of space and dont know why.Any thoughts we be great.Thanks,Kevin |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Significant_Other
Starting Member
5 Posts |
Posted - 2009-09-21 : 12:59:35
|
| The server is not publishing any transactional replication. It is however a subscriber to many transactional publications.Your T-sql returns log_reuse_wait_desc 'REPLICATION'. Hmm, so according to this there is an open transaction from replication? Yet there r no open transactions according to DBCC OPENTRAN.DPM is Data Protection Manager. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-21 : 13:04:37
|
| Data Protection Manager.I'm no expert on DPM, but I heard it only did differential backups, not log backups. Check in the SQL error log to see exactly what backups are been done.--Gail ShawSQL Server MVP |
|
|
|
Significant_Other
Starting Member
5 Posts |
Posted - 2009-09-21 : 13:05:52
|
| DPM does both full, differential and tran log backups. It's been working fine until 3 days ago. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2009-09-21 : 13:06:28
|
quote:
Originally posted by Significant_Other
Your T-sql returns log_reuse_wait_desc 'REPLICATION'. Hmm, so according to this there is an open transaction from replication? Yet there r no open transactions according to DBCC OPENTRAN.
Not an open transaction, an unreplicated transaction.What kind of replication do you have set up on the server? What exactly does DBCC OPENTRAN return? (has to be run in the DB in question)--Gail ShawSQL Server MVP |
|
|
|
Significant_Other
Starting Member
5 Posts |
Posted - 2009-09-21 : 13:20:56
|
| The server is a subscriber to many different servers for transactional replication.DBCC returns;Transaction information for database 'MYDB'.Oldest active transaction: SPID (server process ID): 155 UID (user ID) : -1 Name : user_transaction LSN : (16618:191153:1) Start time : Sep 21 2009 11:20:32:297AM SID : 0x0105000000000005150000005a075ac4765c269d023f710df4010000Replicated Transaction Information: Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (15757:1417:145)DBCC execution completed. If DBCC printed error messages, contact your system administrator.This changes every few seconds. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-09-21 : 15:13:11
|
| You'll need to investigate what's going on with replication. Check Replication Monitor and the SQL jobs associated with replication. I've only ever seen the publisher's tlog grow due to unreplicated transactions, so I'm surprised you are having the issue on the subscriber. I guess this would happen if you were a pull subscription, which I've never used.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
Significant_Other
Starting Member
5 Posts |
Posted - 2009-09-21 : 16:30:27
|
| Turns out the tran log still thought it had a transactional publication. I created a new transactional publication, then deleted it. This allowed me to truncate and shrink the log.Thanks for your help it sent me in the right direction.Kevin |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Tran Log Growing, Truncating,shrink not working!