What else can take up space in a database besides tables

  • 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

  • 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 ?

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

    Thanks!

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

  • 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 .

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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

  • 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.

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

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