0

All,

I am completely at a loss here and I'm hoping you guys can help me out on this one. I am experiencing the following situation on an SQL Server 2008 R2 database.

MDF-file: Allocated space: 87392.69 MB Available space: 256.13 MB

The LDF file is only a few megabytes, most of which are available. So as you can see my database is pretty full at the moment. I tried looking for the source of this, but here is where things get "weird".

When check the top tables by usage the top table was only 3 GB big, followed by a few of 1 GB. I've summarized all those totals and came to something around +- 20 GB of data. There's a giant gap here... and I'm not sure where it's coming from. I've been googling for the last few hours hoping to get some pointers, I've checked if any BLOB data was causing this but that seems to be not the case as far as I can see.

Am I missing something here? Did anyone experience something like this before? I can't find anything related to this so I'm completely at your mercy.

If you need any specific details I'll add them when requested.

Top 10 tables based on Aaron's query. (masked names for security reasons)

Top 10 Tables

Thanks in advance

  • Can you show us the query you used to check top tables, I guess you are making mistake – Shanky Jan 30 '15 at 16:59
  • I've used several different ones, and tried the standard reports included. One which I tried was exec sp_spaceused to check for the size. Also tried this one: stackoverflow.com/questions/2094436/… – Jens Jan 30 '15 at 17:02
  • You might try running DBCC UPDATEUSAGE for the database and then rerun the disk space report. Just to make sure. – user507 Jan 30 '15 at 17:02
  • @ShawnMelton Did that, same results – Jens Jan 30 '15 at 17:03
2

Identify the top 10 biggest tables/indexes with this as a starter:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192 
FROM sys.dm_db_partition_stats AS ps 
INNER JOIN sys.objects AS o
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE o.is_ms_shipped = 0
ORDER BY reserved_page_count DESC;

SQL Server doesn't just occupy 87 GB for nothing - the space is being used somewhere. Maybe it's in a system table:

SELECT TOP (10) s.name, o.name, ps.row_count,
  used_bytes = ps.used_page_count * 8192,
  reserved_bytes = ps.reserved_page_count * 8192 
FROM sys.dm_db_partition_stats AS ps 
INNER JOIN sys.all_objects AS o -- change this to all_objects
ON ps.[object_id] = o.[object_id]
INNER JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
-- WHERE o.is_ms_shipped = 0 -- comment this out
ORDER BY reserved_page_count DESC;

Would also be curious to see what size the backup is, and what happens if you try shrink the data file.

| improve this answer | |
  • Well, you cracked this one. It shows sys.sysxmitqueue, sys.sysdercv & sys.sysdesend taking all the space I was missing. Sysxmitqueue is taking a whopping 57 GB alone. What can I do with those? Never heard of those tables before. – Jens Jan 30 '15 at 18:16
  • It has something to do with broker's it seems (after some googling). I've checked and currently the broker is enabled on tempdb & msdb. It's not active on any of the user databases though. Where can I go from here? – Jens Jan 30 '15 at 18:25
  • 1
    @J.Vermeire Assuming the database is msdb (you might have mentioned that in the question from the start!) then from here: ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE; – Aaron Bertrand Jan 30 '15 at 18:58
  • Well no. It's actually a user database, not a system database. Can I run that query on any other database as well, without further consequences? – Jens Jan 30 '15 at 19:00
  • 2
    As long as you're not actually using Service Broker there, sure. Anyway I assumed it was msdb because you said that broker was only enabled in tempdb and msdb. So it must have been enabled in this other database at some point... so if you use NEW_BROKER there you may have some other cleanup to do or you may just end up in the same boat. – Aaron Bertrand Jan 30 '15 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.