[jboss-user] [JBoss Messaging] - Re: Excessive disk usage on SQL 2008 R2 with JBoss messaging 1.4.2.GA-SP1

[Graeme Wright]

Graeme Wright [https://community.jboss.org/people/gw2603] created the discussion

"Re: Excessive disk usage on SQL 2008 R2 with JBoss messaging 1.4.2.GA-SP1"

To view the discussion, visit: https://community.jboss.org/message/741779#741779

--------------------------------------------------------------
We have found out why this issue occurred.

During our investigation we found that SQL Server has a problem with a specific isolation level setting. Please find the below article ( http://support.microsoft.com/KB/2674636/EN http://support.microsoft.com/KB/2674636/EN)

The isolation level is the degree to which one transaction must be isolated from other transactions. The level at which a transaction is prepared to accept inconsistent data is termed the isolation level. There are 5 types of Isolation level:


1. READ UNCOMMITTED-Specifies that statements can read rows that have been modified by other transactions but not yet committed.


2. READ COMMITTED-Specifies that statements cannot read data that has been modified but not committed by other transactions


3. REPEATABLE READ-Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.


4. SNAPSHOT-Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction


5. SERIALIZABLE-Specifies that statements cannot read data that has been modified but not yet committed by other transactions, no other transactions can modify data that has been read by the current transaction until the current transaction completes


READ COMMITTED is the default isolation level for SQL Server.

Investigating on this point we found that our databases on all environments except production is set to READ COMMITTED, while production is set to READCOMMITTEDSNAPSHOT. Based on the above knowledge base we can see that database with READCOMMITTEDSNAPSHOT isolation level behave differently with LOB objects. When a forwarded versioned record is deleted on a page, the record type on the page is changed to GHOST_VERSION_RECORD. However, the corresponding Page Free Space (PFS) page does not indicate that the page contains a ghost record. This causes the page to prevent the database file from being reduced in size.This is very similar to our case. This problem has been fixed in SQL Server 2008 R2 Cumulative Update 13.


So the solution we used here was to set the database which exhibited this behaviour to READ COMMITTED

--------------------------------------------------------------

Reply to this message by going to Community
[https://community.jboss.org/message/741779#741779]

Start a new discussion in JBoss Messaging at Community
[https://community.jboss.org/choose-container!input.jspa?contentType=1&containerType=14&container=2042]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20120614/3e107805/attachment-0001.html