Sql error code 3621

Sql error code 3621

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Transaction is rolled back when accessing version store. It was earlier marked as victim when the version store was shrunk due to insufficient space in tempdb. This transaction was marked as a victim earlier because it may need the row version(s) that have already been removed to make space in tempdb. Retry the transaction [SQLSTATE 42000] (Error 3966) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

i was executing sql server online index rebuild of 2 TB cluster table. how to do the same online ?

Answers

Rebuilding an index can be executed online or offline.

ALTER INDEX REBUILD WITH (ONLINE = ON)*

For each instance of the Database Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. The database administrator must ensure that tempdb has ample space to support the version store.

There are two version stores in tempdb:
The online index build version store is used for online index builds in all databases.
The common version store is used for all other data modification operations in all databases.

Источник

Sql error code 3621

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Transaction is rolled back when accessing version store. It was earlier marked as victim when the version store was shrunk due to insufficient space in tempdb. This transaction was marked as a victim earlier because it may need the row version(s) that have already been removed to make space in tempdb. Retry the transaction [SQLSTATE 42000] (Error 3966) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

i was executing sql server online index rebuild of 2 TB cluster table. how to do the same online ?

Answers

Rebuilding an index can be executed online or offline.

ALTER INDEX REBUILD WITH (ONLINE = ON)*

For each instance of the Database Engine, tempdb must have enough space to hold the row versions generated for every database in the instance. The database administrator must ensure that tempdb has ample space to support the version store.

There are two version stores in tempdb:
The online index build version store is used for online index builds in all databases.
The common version store is used for all other data modification operations in all databases.

Источник

Sql error code 3621

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

SQL index maintenance job is keep on failing with the below error (both scheduled job and manual run). The job was created and last edited in 2015 and nothing is changed. Please suggest.

Job script:

DECLARE @ReturnCode int

EXEC @ReturnCode = [maintenance].[index_maintenance]
@RebuildLevel = 25.00,
@DefragLevel = 25.00,
@MinimumPagesToReorg = 8,
@DatabaseName = NULL,
@AllowOnlineRebuild = 1,
@RebuildDisabled = 0;

PRINT ‘@ReturnCode = ‘ + CAST(@ReturnCode AS varchar(30));
IF @ReturnCode != 0
RAISERROR (‘Index Maintenance Failed with %d errors. Please check job history/log for details.’, 10, 1, @ReturnCode);

Error :

Date 10/28/2019 1:16:35 AM
Log Job History (index maintenance)

Step ID 1
Server xxxxxxxx
Job Name index maintenance
Step Name index_maintenance
Duration 06:13:54
Sql Severity 21
Sql Message ID 3621
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: xxxxxxSQLAdmin. . ************************************** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) **** msdb **** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) ***********************************************************************************************/ [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [msdb].[dbo].[backupmediafamily] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [backupmediafamilyuuid] ON [msdb].[dbo].[backupmediafamily] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:00:053 [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [msdb].[dbo].[backupmediaset] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [backupmediasetuuid] ON [msdb].[dbo].[backupmediaset] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:00:023 [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [msdb].[dbo].[backupset] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [backupsetuuid] ON [msdb].[dbo].[backupset] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:00:017 [SQLSTATE 01000] (Message 0) /*********************************************************************************************** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) **** Accums **** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) ***********************************************************************************************/ [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [Accums].[Accum].[AccumulatorLineClaimLine] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [ixAccumAccumulatorLineClaimLineAccumulatorClaimLineId] ON [Accums].[Accum].[AccumulatorLineClaimLine] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:01:407 [SQLSTATE 01000] (Message 0) /*********************************************************************************************** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) **** ClaimODS_AQA **. The step failed.

Источник

Sql error code 3621

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

Problem description:
O n two SQL Server 2008 RTM installations t he following simple batch aborts during the delete operation with user error: 3621, Severity: 10, State: 0
BEGIN TRAN
SELECT TOP 1 * FROM ScopeMatcherMap with (tablockx)
DELETE FROM ScopeMatcherMap
INSERT INTO ScopeMatcherMap (UserId, SerializedMatcher) (SELECT * FROM [DIV23!PRJ=UniErla!DB=ISHTSYS].dbo.ScopeMatcherMap)
COMMIT

The SELECT statements completes successfully. Afterwards a lock on the schema and on the principal are aquired. If we execute the batch locally on the SQL Server then the lock on the schema gets immediately released and if we execute the batch from a remote pc then a table scan starts on the ScopeMatcherMap (object_id 101575400). RID and Page Locks are aquired and the changes are writen to the transaction log. Suddenly the scan stops and the the lock on schema_id 5 which belongs to the currently logged on user gets released. In both cases the batch completes with error 2 — Aborted. This behaviour is 100% reproducable but only on 2 SQL Server 2008 RTM installations. On many other installations this transaction never fails. The attachment FailAndOk contains a profiler trace which documents an abort and a successfull completion of the batch .

Additional information:

— We modified the batch for test purpose (OPTION MAXDOP(1), Trunc Table, removed tablockx) but the problem persists.
— The table contains less than 100 rows and FOREIGN KEY constraint s are not violated (not used) , Triggers and indexes are not used at all. UserId int not null, SerializedMatcher nvarchar(MAX) NULL
— Performance is excellent on both servers. About 600 concurrent connections are handled by the server.
— For test purpose we reproduced the problem with a small test application which exectutes only this transaction via ATL CCommand:Open.
— The command succeeded when the batch has been executed from sqlcmd or from the Managment Studio. Which surprised us quite a bit.
— A schema modification lock has not been aquired during the transaction.
— The batch does not fail if we restore the databases on an other sql server installation.
— The affected servers run in a customer’s production environment and currently it is not possible to reduce the load on th ese servers nor to apply a SQL Server SP.

Questions:

What could cause this behaviour? May an upgrade to SP3 resolve this issue? How can we determine the cause of the problem (it is a severity 10 error) ?

Источник

Sql error code 3621

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

SQL index maintenance job is keep on failing with the below error (both scheduled job and manual run). The job was created and last edited in 2015 and nothing is changed. Please suggest.

Job script:

DECLARE @ReturnCode int

EXEC @ReturnCode = [maintenance].[index_maintenance]
@RebuildLevel = 25.00,
@DefragLevel = 25.00,
@MinimumPagesToReorg = 8,
@DatabaseName = NULL,
@AllowOnlineRebuild = 1,
@RebuildDisabled = 0;

PRINT ‘@ReturnCode = ‘ + CAST(@ReturnCode AS varchar(30));
IF @ReturnCode != 0
RAISERROR (‘Index Maintenance Failed with %d errors. Please check job history/log for details.’, 10, 1, @ReturnCode);

Error :

Date 10/28/2019 1:16:35 AM
Log Job History (index maintenance)

Step ID 1
Server xxxxxxxx
Job Name index maintenance
Step Name index_maintenance
Duration 06:13:54
Sql Severity 21
Sql Message ID 3621
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: xxxxxxSQLAdmin. . ************************************** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) **** msdb **** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) ***********************************************************************************************/ [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [msdb].[dbo].[backupmediafamily] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [backupmediafamilyuuid] ON [msdb].[dbo].[backupmediafamily] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:00:053 [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [msdb].[dbo].[backupmediaset] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [backupmediasetuuid] ON [msdb].[dbo].[backupmediaset] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:00:023 [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [msdb].[dbo].[backupset] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [backupsetuuid] ON [msdb].[dbo].[backupset] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:00:017 [SQLSTATE 01000] (Message 0) /*********************************************************************************************** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) **** Accums **** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) ***********************************************************************************************/ [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) —==============================================================================================— [SQLSTATE 01000] (Message 0) — [Accums].[Accum].[AccumulatorLineClaimLine] [SQLSTATE 01000] (Message 0) — Start Time: 2019-10-28 02:59:49 [SQLSTATE 01000] (Message 0) ALTER INDEX [ixAccumAccumulatorLineClaimLineAccumulatorClaimLineId] ON [Accums].[Accum].[AccumulatorLineClaimLine] REBUILD WITH (ONLINE = ON); [SQLSTATE 01000] (Message 0) — Success [SQLSTATE 01000] (Message 0) — Duration: 00:00:01:407 [SQLSTATE 01000] (Message 0) /*********************************************************************************************** [SQLSTATE 01000] (Message 0) **** **** [SQLSTATE 01000] (Message 0) **** ClaimODS_AQA **. The step failed.

Источник

Symptoms

Consider the following scenario:

• You install the Absconfig tool in a Microsoft Lync Server 2013 environment.

• You run Absconfig.exe on Lync Server 2013 front-end servers that have the Address Book Service enabled to change an address book attribute.

• You run the Update-CsUserDatabase cmdlet to start a new replicator sync cycle.

• You apply either the October 2013 or the January 2014 cumulative update for Lync Server 2013.

• You run the following command to update the rtcab database:
  Install-CsDatabase -ConfiguredDatabases -SqlServerFqdn <FQDN>

In this situation, you receive an error message that states the RtcResetAbAttributes procedure fails. For example, you may receive the following error message:

Error creating procedure RtcResetAbAttributes:

sql error code = 547, error message = The DELETE statement conflicted with the REFERENCE constraint «FK_AbAttrValue_AbAttribute». The conflict occurred in database «rtcab», table «dbo.AbAttributeValue», column ‘AttrId’., line number = 26 sql error code = 3621, error message = The statement has been terminated., line number = 26

Cause

This issue occurs because the rtcab.RtcResetAbAttributes stored procedure deletes rows from the AbAttributes table. That violates the foreign key constraint in the AbAttributeValues table. Therefore, the Install-CsDatabase cmdlet fails.

Resolution

To resolve this issue, install the following cumulative update:

2937310 August 2014 Cumulative Update 5.0.8308.738 for Lync Server 2013 (Front End Server and Edge Server)

More Information

For more information about the October 2013 cumulative update for Lync Server 2013, click the following article number to view the article in the Microsoft Knowledge Base:

2881684 Description of the cumulative update 5.0.8308.556 for Lync Server 2013 (Front End Server and Edge Server) : October 2013
For more information about the January 2014 cumulative update for Lync Server 2013, click the following article number to view the article in the Microsoft Knowledge Base:

2905048 Description of the cumulative update 5.0.8308.577 for Lync Server 2013 (Front End Server and Edge Server): January 2014

Today while working with an Informatica mapping, I faced a strange issue. The image below shows the design of the INFA mapping:

Here I’m Acquiring a new table with ~500k records and the table is also very fat with PK as a GUID column. Its just a plain data pull with a simple SELECT query, no JOINs, UNIONs, etc. But while running the Workflow it gave me PK Violation error, as shown below:

Severity: ERROR
Timestamp: 5/30/2013 7:22:54 PM
Node: INFA_NODE_SERVERNAME
Thread: WRITER_1_*_1
Process ID: 8216
Message Code: WRT_8229
Message: Database errors occurred: 
Microsoft OLE DB Provider for SQL Server: The statement has been terminated.
SQL State: 01000	Native Error: 3621
State: 1	Severity: 0
SQL Server Message: The statement has been terminated.

Microsoft OLE DB Provider for SQL Server:
Violation of PRIMARY KEY constraint 'PK_tblTableName'. 
Cannot insert duplicate key in object 'dbo.tblTableName'.
The duplicate key value is (2ea8b6b9-e505-4ef1-a385-0cf9143d2cfd).

SQL State: 23000	Native Error: 2627
State: 1	Severity: 14
SQL Server Message: Violation of PRIMARY KEY constraint 'PK_tblTableName'. 
Cannot insert duplicate key in object 'dbo.tblTableName'. 
The duplicate key value is (2ea8b6b9-e505-4ef1-a385-0cf9143d2cfd).

Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO tblTableName

This was strange because the error was for the GUID PK column, and there is no reason for duplicate values here. As I’ve already taken care of INSERTs & UPDATEs by ROUTER Transformation, which can be seen in the image above. I tried to find the root cause of the error, BINGed/GOOGLEd a lot, but no luck.

Then I checked with an experienced person in my team about this error. He immediately asked me if I’ve added any “WITH (NOLOCK)” option in my query with the Source Table. I said yes, so he asked me to remove it and try, as they had faced similar issues with NOLOCK option before. I removed it and the Workflow ran successfully 🙂 .

I knew that with NOLOCK option I’m doing Dirty Reads, and had added it just to avoid locking/blocking at the Source end. But I was not aware that these Dirty Reads means any kind of data, which can also be duplicate. Thus by adding NOLOCK option with the Table, which is also going through lot of changes, we may allow data to be read more than once. This may be due to Data Movement, Uncommitted Data or Page Splits on the Source table during our reads, where we may be reading the data before and after the Page-Splits.

So, be careful while using NOLOCK option while designing your queries.

For more information check following blog: http://sqlmag.com/database-development/quaere-verum-clustered-index-scans-part-iii