SQL Server Performance

Shrink table after plenty of deletes?

Discussion in 'SQL Server 2005 General DBA Questions' started by brimba, Jan 14, 2009.

  1. brimba New Member

    I have deleted ~2 million rows in the table sysmail_mailitems in the msdb database.
    The table was about 35gb, but now after the delete it's still 35gb.
    I have tried to shink booth database and files.
    When I run: EXEC sp_spaceused 'sysmail_mailitems' I get this:
    name, rows, reserved, data, index_size, unused
    sysmail_mailitems 49878 35641360 KB 35503096 KB 9200 KB 129064 KB
    If I understand that right it says that I have 35gb of data, but I cannot find it. I have tried to run the SP with @updateusage parameter set to true, like this:
    EXEC sp_spaceused 'sysmail_mailitems', @updateusage='true'
    But that doesn't help either. I have also run a backup on the database, but it still wont shrink.
  2. satya Moderator

    Did you check for any open transaction on that database?
    DO you have REPLICATION setup on this machine?
  3. brimba New Member

    Since that table is related to sending mail from the database I guess every now and then a transaction will occur. But not any long running transactions.
    No, we dont have any replication running.
  4. satya Moderator

    What query you have used to delete the rows?
    What is the service pack level on SQL?
    In general using msdb.dbo.sysmail_delete_mailitems_sp would do the good, also run this query and post the resutls:
    select object_name(object_id), *
    from sys.allocation_units a
    join sys.partitions p
    on p.partition_id = a.container_id
    order by total_pages desc
  5. brimba New Member

    Yes, I used that SP to delete the posts.
    This is the result.
    sysmail_mailitems 72057594049462272 2 LOB_DATA 72057594044874752 1 4198521 4197991 0 72057594044874752 1454628225 1 1 72057594044874752 50034
    sysmail_mailitems 72057594049331200 1 IN_ROW_DATA 72057594044874752 1 256801 241209 240059 72057594044874752 1454628225 1 1 72057594044874752 50034
  6. satya Moderator

    Though the MSDB will be in SIMPLE recovery model perform BACKUP LOG MSDB WITH TRUNCATE_ONLY and then try to shrink.
  7. brimba New Member

    That didn't seem to make any difference.
  8. satya Moderator

    Hmm, I'm started to panic now [;)].
    Is this a dedicated SQL Server with no DB Mirroring or CLustering setup?
    Do you have SERVICE BROKER enabled or application sending any messages regularly?
    n
  9. brimba New Member

    Yes, its a dedicated server (sql-server 2005 standard 64-bit sp2). No mirroring and no clustering. :)
    We are using the service broker functionality. However I cannot see that it's that in this case since all the numbers are pointing on that particular table? I was thinking about rebuilding some index, but the statistics says that it's the data size not the size of the indexes.
    We are also using database mail to send emails from the database. Before we noticed that it was storing a copy all sent messages (including body and everything) in the msdb database (why is that?!?) it had grown to 35gb.
  10. brimba New Member

    Any other suggestions?
    I guess truncate table would help, but that doesn't seem to be the best option here.
  11. brimba New Member

    I just found this in the process monitor.
    243 0 suspended MP-DOMAINSQL-server MP-DB2 0 msdb DELETE 0
    So process ID 243 is suspended against the msdb database. Waittype is BROKER_RECIEVE_WAITFOR

  12. ndinakar Member

    Copy the data into a new table if you can and perhaps you will see the released space..its only 50k rows so you should be done in a minute.
  13. brimba New Member

    Well, the backup returned alot smaller than the original, but the original didn't shrink.
    I ran the query:
    select * into backup_sysmail_mailitems from sysmail_mailitems

    sysmail_mailitems 62168 35664856 KB 35487160 KB 8672 KB 169024 KB
    backup_sysmail_mailitems 61923 1259968 KB 1259880 KB 24 KB 64 KB

    As you can see there were a couple of rows added to the original after the backup, but still. The original says 35gb of data. The copied one says 1.2gb of data.
  14. brimba New Member

    I deleted all the rows in the table. But it still says 35gb of data.
    sysmail_mailitems 0 35705936 KB 35505272 KB 8504 KB 192160 KB

  15. satya Moderator

    I'm still puzzled on this one and if your company has Support contract with Microsoft better to open up a case.

Share This Page