2 Replies Latest reply on Jun 14, 2012 10:28 PM by Yong Hao Gao

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

    gw2603 Newbie

      We have seen issues when running Jboss 4.2.3 with JBoss Messaging 1.4.2.GA-SP1 using MS SQL 2008 R2 for the data-store where in high volume environments the disk usage is disproportionate to the volume of messages being persisted.

       

      I know that there is a known behaviour with MS SQL and the following note from JBoss messaging docs explains this,

       

      Microsoft SQL Server does not automatically de-allocate the hard-drive space occupied by data in a database when that data is deleted. If used as a data-store for services that temporarily store many records, such as a messaging service, the disk space used will grow to be much greater than the amount of data actually being stored. Your database administrator should implement database maintenance plans to ensure that unused space is reclaimed. Please refer to your Microsoft SQL Server documentation for the DBCC commands ShrinkDatabase and UpdateUsage for guidance. https://jira.jboss.org/jira/browse/SOA-629

       

      however we have seen the JBM_MSG table grow beyond the volumes actually being submitted, we have other similar environments that do not exhibit the same behaviour. Users believe the number that each message is consuming 5mb for what would only be at most few hundred KB message and is actually consming 200MB of disk to store this, obviously this sounds like an issue with SQL Server and we have opened a ticket with Microsoft but was sseeing if anyone else had seen this.

       

      Thanks

        • 1. Re: Excessive disk usage on SQL 2008 R2 with JBoss messaging 1.4.2.GA-SP1
          gw2603 Newbie

          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)


          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