[jboss-jira] [JBoss JIRA] Commented: (JBMESSAGING-1359) MSSQL not cleaning deleted rows from JBM_MSG table, not reclaiming disk space

[Adrian Brock (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBMESSAGING-1359?page=comments#action_12420823 ] 
            
Adrian Brock commented on JBMESSAGING-1359:
-------------------------------------------

Like Tim, I'm not sure why MSSQL database administration is a topic for a JBoss Messaging JIRA issue.

But since I spotted this issue and always find MSSQL very "amusing" in the way it does things by default, 
I did some quick research on the issue and found these "FAQ"s;

http://www.tek-tips.com/viewthread.cfm?qid=1424488&page=9
http://www.dba-sql-server.com/sql_server_tips/t_hpsdba_36_server-level_space_dbcc.htm

i.e. MSSQL is "inaccurate" in the way it keeps track of free space.

So try using the suggested "DBCC UpdateUsage"

NOTE: Also the first link hints at a "blob" issue/bug which would certainly effect JBM 
(if it is actually true - there's nothing to support this assertion).

> 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: Task
>          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
>             Fix For:  1.4.0.SP3.CP04
>
>         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