What else can take up space in a database besides tables

Question

What else can take up space in a database besides tables

Steve-443559

SSCrazy

Points: 2309
More actions
September 28, 2017 at 2:03 pm

#328838

I have a database that says it's 108 GB USED - I verified with dbcc showfilestats as well as just clicking tasks-shrink file in SSMS. however when i run the built in report to show table sizes (including indexes), they only add up to about 75 GB? I know Service broker queues can take space, but i don't have any queues in this database. is there anything else?
Thanks,
Steven

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Arsh SSCertifiable Points: 6354 More actions · Answer 1

Steve-443559 - Thursday, September 28, 2017 2:03 PM
I have a database that says it's 108 GB USED - I verified with dbcc showfilestats as well as just clicking tasks-shrink file in SSMS. however when i run the built in report to show table sizes (including indexes), they only add up to about 75 GB? I know Service broker queues can take space, but i don't have any queues in this database. is there anything else?
Thanks,
Steven

Any audit running on the database ? Is it a standalone database ?

Steve-443559 SSCrazy Points: 2309 More actions · Answer 2

There are no Audits running on the database. The database is on a clustered server, if that's what you mean.

Thanks!

Steve-443559 SSCrazy Points: 2309 More actions · Answer 3

ok, i found something about text fields. that is likely it.

Arsh SSCertifiable Points: 6354 More actions · Answer 4

Steve-443559 - Friday, September 29, 2017 7:02 AM
ok, i found something about text fields. that is likely it.

Can also look for VARCHAR(MAX) , another LOB type .

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Steve-443559 - Friday, September 29, 2017 7:02 AM
ok, i found something about text fields. that is likely it.

Two way street here... tell us what you found, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Heh... Right now I'm thinking, "Check-valve".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve-443559 SSCrazy Points: 2309 More actions · Answer 7

Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Steve-443559 - Monday, October 30, 2017 6:34 AM
Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.

Hmmm... I've not previously seen the same kind of discrepancy before. Thanks for the heads up, Steve. I'll check it out.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Steve-443559 - Monday, October 30, 2017 6:34 AM
Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.

sp_spaceused could be incorrect as well. You may want to consider executing dbcc updateusage and see how things look after that:
DBCC UPDATEUSAGE

Sue

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

Sue_H - Monday, October 30, 2017 8:35 AM
Steve-443559 - Monday, October 30, 2017 6:34 AM
Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.
sp_spaceused could be incorrect as well. You may want to consider executing dbcc updateusage and see how things look after that:
DBCC UPDATEUSAGE
Sue

Use with some bit of caution, though. It can be a bit intensive resource usage wise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 11

Jeff Moden - Monday, October 30, 2017 8:59 AM
Sue_H - Monday, October 30, 2017 8:35 AM
Steve-443559 - Monday, October 30, 2017 6:34 AM
Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.
sp_spaceused could be incorrect as well. You may want to consider executing dbcc updateusage and see how things look after that:
DBCC UPDATEUSAGE
Sue
Use with some bit of caution, though. It can be a bit intensive resource usage wise.

That's why I provided the link - explains the same. Has a Best Practices, recommendations section.

Sue

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Sue_H - Monday, October 30, 2017 11:21 AM
Jeff Moden - Monday, October 30, 2017 8:59 AM
Sue_H - Monday, October 30, 2017 8:35 AM
Steve-443559 - Monday, October 30, 2017 6:34 AM
Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.
sp_spaceused could be incorrect as well. You may want to consider executing dbcc updateusage and see how things look after that:
DBCC UPDATEUSAGE
Sue
Use with some bit of caution, though. It can be a bit intensive resource usage wise.
That's why I provided the link - explains the same. Has a Best Practices, recommendations section.
Sue

Understood. To be sure, although I was too brief in my post for you to think otherwise, the comment wasn't directed at you because you absolutely know what you're doing (can't help but notice your good posts over time... thanks for what you do). It was actually meant for the person(s) that read this thread but won't take the time to learn about your good suggestion by actually reading the link you provided.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sue_H SSC Guru Points: 90860 More actions · Answer 13

Jeff Moden - Monday, October 30, 2017 12:33 PM
Sue_H - Monday, October 30, 2017 11:21 AM
Jeff Moden - Monday, October 30, 2017 8:59 AM
Sue_H - Monday, October 30, 2017 8:35 AM
Steve-443559 - Monday, October 30, 2017 6:34 AM
Sorry, I was using the Disk Usage by Top Tables report and it was not totalling to the used size of my database, so i believe it's not calculating the size correctly. when I do a sp_spaceused <tablename>, it shows me a much larger index size than what is in the built in report and I believe it has something to do with the nvarchar(max) and XML fields in the table. I haven't dug into the code behind sp_spaceused and the built in report, but I'm sure it will explain the discrepancy.
sp_spaceused could be incorrect as well. You may want to consider executing dbcc updateusage and see how things look after that:
DBCC UPDATEUSAGE
Sue
Use with some bit of caution, though. It can be a bit intensive resource usage wise.
That's why I provided the link - explains the same. Has a Best Practices, recommendations section.
Sue
Understood. To be sure, although I was too brief in my post for you to think otherwise, the comment wasn't directed at you because you absolutely know what you're doing (can't help but notice your good posts over time... thanks for what you do). It was actually meant for the person(s) that read this thread but won't take the time to learn about your good suggestion by actually reading the link you provided.

Thanks for the clarification - I actually did think you were directing that towards me.
Good reminder as I never read the links I post. Just kidding of course. 🙂

Sue

Bill Talada SSChampion Points: 11954 More actions · Answer 14

I've had problems finding where space was used and having it add up to the full amounts plenty of times. If you are using ServiceBroker and people aren't ending their conversations it can use up gigs of unreported space after a few months. Also, I vaguely remember heap space used not reporting correctly after many deletes.