Actual Disk Space being utilized?

Question

Actual Disk Space being utilized?

cbernardes

SSC Eights!

Points: 938
More actions
May 11, 2015 at 2:52 pm

#307886

I can run a script and find out what table is utilizing a large amount of disk space by looking that the row count and total space (KB). If I go to the particular tables and delete a large amount of rows, the row count goes down but my disk space usage has not changed.
I know that from reading various posts, deleting rows does not reclaim disk space. Also, the use of shrinking the database is a big no-no.
I can see via my ISP gui, my free space and used space, but that actually doesnt help me since my free space does not actually reflect what I have deleted in my tables. So is there a different way of seeing what I have left after deleting a large amount of rows?
I hope my post made a bit of sense.
Thanks.

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

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

ScottPletcher SSC Guru Points: 101093 More actions · Answer 1

Yes, it did :-).

A shrink is reasonable after you've deleted a lot of data. After the shrink, you can decide if you want to shrink the file to release the disk space back to the disk subsystem.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

cbernardes SSC Eights! Points: 938 More actions · Answer 2

I can upload a copy of the DB to a test site and see what happens if I do a SHRINK of the DB. Is there something I can run to see if I experience of some type of fragmentation issues?

ScottPletcher SSC Guru Points: 101093 More actions · Answer 3

Yes, after the shrink, you use sys.dm_db_index_physical_stats as usual to check for fragmentation.

Ooh, btw, I forgot something earlier. Even before you do the shrink, you can check if there is even a good potential for shrinking significant space out of a data file:

USE [your_db_name]

DBCC SHOWFILESTATS

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

cbernardes SSC Eights! Points: 938 More actions · Answer 4

Looks like I cant use sys.dm_db_index_physical_stats. My ISP doesnt allow this.

But thank you for your information. I will give it a look.

Edit: I can use the above command, I was not using it correctly.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

You can query DMVs for fragmentation. Do this before and after.

Note that unless this is a read only database, you need some free space for maintenance.

https://msdn.microsoft.com/en-us/library/ms188917.aspx

cbernardes SSC Eights! Points: 938 More actions · Answer 6

Steve Jones - SSC Editor (5/12/2015)
You can query DMVs for fragmentation. Do this before and after.
Note that unless this is a read only database, you need some free space for maintenance.
https://msdn.microsoft.com/en-us/library/ms188917.aspx

Thank you for the link.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 7

cheers. If that answered what you need, please mark it (or other posts) as answers for future readers.