[jboss-jira] [JBoss JIRA] Updated: (JBMESSAGING-1359) JBM is not able to work with MSSQL - MSSQL not cleaning deleted rows from JBM_MSG table, not reclaiming disk space

[Martin Vecera (JIRA)]

     [ http://jira.jboss.com/jira/browse/JBMESSAGING-1359?page=all ]

Martin Vecera updated JBMESSAGING-1359:
---------------------------------------

    Attachment: soaesb1_perf.sql.gz

I tried the soaesb1_perf.sql script (see attachment). It has created 9MB database file with one table containing no rows. So this is the same problem I see with JBM. After it has finished I executed:
dbcc shrinkdatabase ('soaesb1')
This took 1 day and was still running. I stopped executing this query and tried it again. Voila, it completed in a second. But no space was reclaimed. Another try to run this query failed due to:
DBCC SHRINKDATABASE: File ID 1 of database ID 12 was skipped because the file does not have enough free space to reclaim.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In this situation we definitely need to talk to an MS SQL expert. I was not able to find anything on this on the internet.

> JBM is not able to work with MSSQL - MSSQL not cleaning deleted rows from JBM_MSG table, not reclaiming disk space
> ------------------------------------------------------------------------------------------------------------------
>
>                 Key: JBMESSAGING-1359
>                 URL: http://jira.jboss.com/jira/browse/JBMESSAGING-1359
>             Project: JBoss Messaging
>          Issue Type: Bug
>          Components: Messaging Core Persistence
>    Affects Versions: 1.4.0.SP3.CP02
>         Environment: SOA-P 4.2 CP02, RHEL5, MSSQL
>            Reporter: Martin Vecera
>         Assigned To: Tim Fox
>            Priority: Critical
>         Attachments: dbstat.csv.gz, jbm_msg.csv.gz, soaesb1_dbstat.sql.gz, soaesb1_perf.sql.gz
>
>
> Reproducible: 100%
> Description: There is something that prevents MSSQL to clean deleted rows from JBM_MSG table and the disk space occupied by that table grows permanently.
> I had HelloWorld ESB service listening on JMS queue. I was constantly sending messages to that queue. JBM_MSG table contains around 1000 in average - this is balanced between sender and receiver. Consumer consumes old messages, new messages are still arriving.
> Every message has 5kB.
> The DB grows in size during this procedure until it takes the whole disk space and make the DB unusable for JBM.
> Evidence - attached files:
> dbstat.csv - status report for all the tables in DB - see RowCount, DataSpace for JBM_MSG
> jbm_msg.csv - content of JBM_MSG table when status report was generated
> soaesb1_dbstat.sql - SQL script for generating status report
> Caution: MSSQL is required in SOA-P PRD. If this cannot be solved, the requirement should be removed.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira