locked
Database Storage RRS feed

  • Question

  • I have a production DB with around 500 gb.  The mdf was almost at 400 gb. I recently deleted few years worth of data from the DB. So this would have reduced the storage utilization. So i execute the below after the data deletion

    select
    	[FileSizeMB]	=
    		convert(numeric(10,2),round(a.size/128.,2)),
    	[UsedSpaceMB]	=
    		convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
    	[UnusedSpaceMB]	=
    		convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
    	[DBFileName]	= a.name
    from
    	sysfiles a

    And the output  is FileSize 408733 and UsedSpace is 329818. So it it saved me around 70gb. Now, i think i know the answer but wanted to make sure. 

    So every day when new data arrives, the Usedspace keeps growing and the Filespace will remain the same. Only after few months when the used space reaches 408733, that the Actual Hard drive will start showing more disk space being used and both Filesize and UseSpace will increase. 

    The whole point is to make a case to business that we dont need to buy any storage now and we can if needed to buy can be done in few months (if no more deletions occurs)

    Monday, November 23, 2015 10:25 PM

Answers

  • The files is 40 GB and you only use 32 GB now So you can keep adding data until those 32 GB increases upwards 40 GB until the physical file will start growing.

    And the autogrow setting is too small. Increase it to some 200 MB. And make sure that you have enabled "Instant File initialization" (search the net).


    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, November 26, 2015 7:32 PM

All replies