December 9, 2011 at 8:26 am
We upgraded to 2008 R2 SP1 over the weekend. Since the upgrade, one of our instances of SQL has had issues with the transaction logs filling up and not truncating like it usually does. Our current maintenance plan for that instance is 15 minute tlog backups and a nightly full backup for this instance. It houses our OLTP databases and some batch processing databases.
Here's what I see when I do a dbbc opentran:
Transaction information for database 'TIDBNEW'.
Oldest active transaction:
SPID (server process ID): 7s
UID (user ID) : -1
Name : user_transaction
LSN : (543263:28204:1)
Start time : Dec 8 2011 11:02:19:483PM
SID : 0x01
Replicated Transaction Information:
Oldest distributed LSN : (543759:117348:229)
Oldest non-distributed LSN : (543263:28204:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
According to the sp_who2, SPID 7 is the SIGNAL HOLDER process. I'm not sure why it would have the oldest Active transaction, but it does. I hadn't seen this until after we upgraded on Saturday.
Last night, we needed to reboot the box due to an administration SNAFU. When we brought the databases back online, it took them about 28 minutes to recover. Normally, they are back up and ready in about 2-3 minutes.
I'm perplexed as to what is causing the logs to grow. I've checked and there aren't unreplicated transactions (we have transactional replication for these databases to feed our OpsDataStore) nor could I find any other Active transactions. According to the sysdatabases, this is what the log wait description is.
Any ideas or directions I should go?
December 10, 2011 at 3:34 pm
Select log_reuse_wait_desc From sys.databases
Using the above, that database will probably show REPLICATION. Is this database replicated - and if it is have you verified the replication is working?
If it isn't, looks like replication got partially setup - you will need to shut it down and once that happens your log files will be able to be reused.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 12, 2011 at 6:51 am
Jeffrey Williams 3188 (12/10/2011)
Select log_reuse_wait_desc From sys.databasesUsing the above, that database will probably show REPLICATION. Is this database replicated - and if it is have you verified the replication is working?
If it isn't, looks like replication got partially setup - you will need to shut it down and once that happens your log files will be able to be reused.
MYdbACTIVE_TRANSACTION
This is what it shows in sys.databases.
The database is replicated, however, prior to the upgrade, our t-log was 14GB and generally empty (growth due to maintenance plan for indexes). I've tried to run the sp_repldone to clear it out and that has not worked either. Replication has been up on this box and working fine for almost a year.
December 13, 2011 at 9:20 am
Rebuilding replication did not help.
Some more research I've done on this so far:
select * from sys.dm_tran_active_transactions
152worktable2011-12-08 22:37:57.1472NULL200000NULL
155worktable2011-12-08 22:37:57.1472NULL200000NULL
157worktable2011-12-08 22:37:57.1472NULL200000NULL
159worktable2011-12-08 22:37:57.1472NULL200000NULL
162worktable2011-12-08 22:37:57.1472NULL200000NULL
164worktable2011-12-08 22:37:57.1472NULL200000NULL
14781user_transaction2011-12-08 23:02:19.4831NULL2025800-1NULL
78270813SELECT2011-12-13 11:19:34.5332NULL200000NULL
78270814SELECT2011-12-13 11:19:34.6632NULL200000NULL
5577LobStorageProviderSession2011-12-08 23:01:57.9932NULL200000NULL
78270291user_transaction2011-12-13 11:19:30.9831NULL2025800-1NULL
I'm assuming that it's 14781 which is the culprit at this point. However, I keep coming back to it being SPID 7, which is the SIGNAL HANDLER process.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply