Welcome to Port3101.org : Your BES Connection Mark forums read | View Forum Leaders
Port3101.org : Your BES Connection



Reply
LinkBack Thread Tools Display Modes
Transaction Logs Getting Full
 
  #1 (permalink)  
Old 09-13-2011, 07:35 AM
BES Expert
 
Join Date: Feb 2009
Location: UK
Posts: 58
Default Transaction Logs Getting Full

We have been running BESXpress for a while now and our SQL DBA spotted that on the SQL Server the BesX db transaction logs have been filling around 4am every day for the past few weeks.

Since the logs cleared down by 4.30 am it has not presented a problem to the disruption of service allowing him to keep monitoring the situation looking for possible causes.

However, when he looked at the Database logs this morning the transaction logs had continued to fill. By looking at the SQL Server activity monitor he found a transaction that had been running since 3.44 am, initiated by a call from our BESX server:

Command: Delete (Details: EXEC RemoveOldMSAddresses)
Host: BesX
login time: 03.44.27 (still running at 10.30 am)
Physical IO: 20,964002
Resource: 6:1:268022
CPU: 593500
Net Library: TCP/IP

Running the following SQL query against the BESX db to find details of the currently running SQL Query and it returned the query listed at the bottom of this post.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

he had to kill the process as it was locked and causing the logs to continually grow which has resolved the issue.

Has anyone come across this before in terms of the procedure RemoveOldMSAddresses - how/when it is called, why is it taking so long to run etc

a quick google seach and found that it is a "Stored procedure that is engaged by the BlackBerry Administration Service timers" :

http://docs.blackberry.com/en/admin/...001-5.0-US.pdf

with a description that
"This table removes old MSAddresses and retains two copies for each BlackBerry Domain by default."

According to the document it is scheduled by default to run daily at 07.45 UTC which is 08.45 am GMT I believe so we'll need to find out whether this has been changed during our implementation to 3.45 am and why


CREATE PROCEDURE dbo.RemoveOldMSAddresses ( @SequencesToKeep INT = 2, @Debug INT = 1, @BatchSize INT = 3000, @MaxRows INT = 2000000, @DelaySecond TINYINT = 5) AS BEGIN --EXEC RemoveOldMSAddresses SET NOCOUNT ON DECLARE @SequenceNumber INT, @LastAddressRefreshCompleteSequenceNumber INT, @TotalRows INT, @Chunks INT, @DeletedRows INT, @Delay VARCHAR(100), @Message VARCHAR(4000), @Query VARCHAR(8000) SELECT @SequencesToKeep = CASE WHEN @SequencesToKeep IS NULL THEN 2 ELSE @SequencesToKeep END, @Debug = CASE WHEN @Debug IS NULL THEN 1 ELSE @Debug END, @BatchSize = CASE WHEN @BatchSize IS NULL THEN 3000 ELSE @BatchSize END, @MaxRows = CASE WHEN @MaxRows IS NULL THEN 2000000 ELSE @MaxRows END, @DelaySecond = CASE WHEN @DelaySecond IS NULL THEN 5 ELSE @DelaySecond END CREATE TABLE #Temp_MsAddressCleanUp (MailStoreDomainId INT NOT NULL, SequenceNumber INT NOT NULL) SET @BatchSize = CASE WHEN @MaxRows < @BatchSize THEN @MaxRows ELSE @BatchSize END SET @Delay = CASE WHEN @DelaySecond <= 0 OR @DelaySecond IS NULL THEN NULL ELSE '00:00:'+ RIGHT('00'+CASE WHEN @DelaySecond >= 60 THEN '59' ELSE LTRIM(STR(@DelaySecond)) END, 2) END INSERT INTO #Temp_MsAddressCleanUp (MailStoreDomainId,SequenceNumber) SELECT MailStoreDomainId, (LastAddressRefreshCompleteSequenceNumber - @SequencesToKeep) FROM MsDomains WHERE LastAddressRefreshCompleteSequenceNumber > @SequencesToKeep IF EXISTS (SELECT * FROM #Temp_MsAddressCleanUp) BEGIN SELECT @TotalRows = COUNT(*) FROM MsAddresses a (NOLOCK), #Temp_MsAddressCleanUp b (NOLOCK) WHERE a.MailStoreDomainId = b.MailStoreDomainId AND a.SequenceNumber <= b.SequenceNumber SET @TotalRows = CASE WHEN @TotalRows >= @MaxRows THEN @MaxRows ELSE @TotalRows END SELECT @Chunks = CASE WHEN @TotalRows IS NULL OR @TotalRows =0 THEN 0 WHEN (@TotalRows/(@BatchSize*1.00)) =(@TotalRows/@BatchSize) THEN (@TotalRows/@BatchSize) ELSE (@TotalRows/@BatchSize) +1 END, @DeletedRows = 0 WHILE (@Chunks > 0) BEGIN IF EXISTS(SELECT * FROM MsAddresses a (NOLOCK), #Temp_MsAddressCleanUp b (NOLOCK) WHERE a.MailStoreDomainId = b.MailStoreDomainId AND a.SequenceNumber <= b.SequenceNumber) BEGIN SET @Message = 'Batch Start Time: ' + CONVERT(VARCHAR, GETUTCDATE(), 121) SET @Query = 'DELETE FROM MsAddresses WHERE MailStoreAddressKey IN ( SELECT TOP ' + LTRIM(STR(@BatchSize)) + ' MailStoreAddressKey FROM MsAddresses a (NOLOCK), #Temp_MsAddressCleanUp b (NOLOCK) WHERE a.MailStoreDomainId = b.MailStoreDomainId AND a.SequenceNumber <= b.SequenceNumber )' EXEC (@Query) SET @DeletedRows = @DeletedRows + @@ROWCOUNT SET @Message = 'Total ' + LTRIM(STR(@DeletedRows)) + ' were deleted. ' + @Message + ' Batch End Time: ' + CONVERT(VARCHAR, GETUTCDATE(), 121) SET @Chunks = @Chunks - 1 IF (@Debug >= 1) PRINT @Message IF (@Delay IS NOT NULL OR @DelaySecond > 0) BEGIN IF(@Debug > 1) PRINT 'Request delay '+ @Delay WAITFOR DELAY @Delay END END ELSE BEGIN BREAK; END END END SELECT LTRIM(STR(@@ERROR)) AS ERROR END
Reply With Quote
Sponsored Links
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Pipe Full in router logs mjyp Port 3101: The BES Admin Bar & Grill 2 03-23-2010 03:23 PM
Pipe Full message in Log. BBGalvipa Port 3101: The BES Admin Bar & Grill 1 03-05-2010 06:21 PM
KB11209 - The SQL transaction log file for the BESMgmt database on SQL is full hdawg Featured BlackBerry KB Articles 0 08-23-2009 08:53 PM
KB02960 - Transaction error messages hdawg Featured BlackBerry KB Articles 0 12-19-2008 03:57 PM
KB03968 - How to increase or decrease the BB Config Database transaction log size hdawg Featured BlackBerry KB Articles 0 12-18-2008 07:04 PM


All times are GMT -4. The time now is 09:53 PM.
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.


 

SEO by vBSEO 3.3.2 PL2