Post reply

Database Reporting wrong size

  • June 9, 2015 at 3:29 pm

    #298811

    Hello All

    I have a 50gb database, with 3 files at the primary filegroup, each one of those has around 16gb

    I truncated 2 tables releasing 33gb, so the database should have around 17gb now, but when I check at the properties it says that each file doesn't have any empty space

    what can I do as to fix this?

    this is on a MSSQL 2012 SP2 CU1

    Thanks in advance

  • June 9, 2015 at 3:32 pm

    #1804146

    If you are using SSMS to determine size, have you tried using a query to check if it reports the same size?

    Have you issued DBCC UPDATEUSAGE (MyDatabase) WIT NO_INFOMSGS for the database in question?

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • June 9, 2015 at 3:40 pm

    #1804150

    ricardo_chicas (6/9/2015)

    Hello All

    I have a 50gb database, with 3 files at the primary filegroup, each one of those has around 16gb

    I truncated 2 tables releasing 33gb, so the database should have around 17gb now, but when I check at the properties it says that each file doesn't have any empty space

    what can I do as to fix this?

    this is on a MSSQL 2012 SP2 CU1

    Thanks in advance

    I've seen something like this before, with tables that didn't have a clustered index.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • June 9, 2015 at 4:00 pm

    #1804162

    Alvin Ramard (6/9/2015)

    ricardo_chicas (6/9/2015)

    Hello All

    I have a 50gb database, with 3 files at the primary filegroup, each one of those has around 16gb

    I truncated 2 tables releasing 33gb, so the database should have around 17gb now, but when I check at the properties it says that each file doesn't have any empty space

    what can I do as to fix this?

    this is on a MSSQL 2012 SP2 CU1

    Thanks in advance

    I've seen something like this before, with tables that didn't have a clustered index.

    Or a lot of blob data being deleted. IIRC, isn't there a DBCC command for free space like that?

  • June 9, 2015 at 4:11 pm

    #1804165

    Shawn Melton (6/9/2015)

    If you are using SSMS to determine size, have you tried using a query to check if it reports the same size?

    Have you issued DBCC UPDATEUSAGE (MyDatabase) WIT NO_INFOMSGS for the database in question?

    I just tried with that command, no change...

  • June 9, 2015 at 4:12 pm

    #1804166

    Alvin Ramard (6/9/2015)

    ricardo_chicas (6/9/2015)

    Hello All

    I have a 50gb database, with 3 files at the primary filegroup, each one of those has around 16gb

    I truncated 2 tables releasing 33gb, so the database should have around 17gb now, but when I check at the properties it says that each file doesn't have any empty space

    what can I do as to fix this?

    this is on a MSSQL 2012 SP2 CU1

    Thanks in advance

    I've seen something like this before, with tables that didn't have a clustered index.

    All of them have clustered indexes, and just in case I just rebuild all of them

  • June 9, 2015 at 4:28 pm

    #1804172

    Well, after playing with it for a while I used the "Shrink database" option and that was able to release all the space, not pretty but it was able to do what I needed and now I was able to recover all the space

    thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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