Find Free Space by Database

Question

Find Free Space by Database

Ramasankar Molleti

Ten Centuries

Points: 1044
More actions
June 21, 2016 at 10:16 pm

#327384

Comments posted to this topic are about the item Find Free Space by Database

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

The script doesn't find the free space by database. It does it for the databases' files, so its title is not corresponding to its function.

Igor Micev,My blog: www.igormicev.com

dsolanki 37114 SSC Rookie Points: 43 More actions · Answer 2

Hi Rama Sankar,

Thanks for sharing, i run your script in my database, i got mostly LDF and MDF files in the result of query.

So i like to know what action i should take from the result. i mean how it can help. Please suggest.

tskelley SSCommitted Points: 1870 More actions · Answer 3

Works like a charm. Does have some extra html formatting code on Line 13 (an extra </pre>) at the end of the line. And yes, I could see how changing the title would be a little more helpful. Also, please provide a description about the intent of the code; in other words, what problem/issue is it attempting to solve.

Thanks,

Tom

Lee Linares SSCrazy Points: 2727 More actions · Answer 4

Excellent script. Thanks for sharing. I did however notice a problem with the ORDER BY. Since the values for FreeSpacePct are VARCHAR, they are sorted by the value of the FIRST character. So in a DESC order 99.00% would come before 100.00% and 4.00% would list before 38.00%. I overcame this by changing the final SELECT from this:

SELECT

ServerName,

DatabaseName,

FileSizeMB,

LogicalFileName,

PhysicalFileName,

Status,

Updateability,

RecoveryMode,

FreeSpaceMB,

FreeSpacePct,

PollDate

FROM @DatabaseInfo

ORDER BY FreeSpacePct DESC,

ServerName,

DatabaseName

TO This:

SELECT

ServerName,

DatabaseName,

FileSizeMB,

LogicalFileName,

PhysicalFileName,

Status,

Updateability,

RecoveryMode,

FreeSpaceMB,

RIGHT(' ' + FreeSpacePct,7) AS FreeSpacePct,

PollDate

FROM @DatabaseInfo

ORDER BY FreeSpacePct DESC,

ServerName,

DatabaseName

Thanks again,

Lee

dsolanki 37114 SSC Rookie Points: 43 More actions · Answer 5

Hi, Rama/Lee,

As i requested earlier, can anyone tell me what is the important of this script? is it useful for DBA only? or as a developer we can do some change in database which help in performance.

Lee Linares SSCrazy Points: 2727 More actions · Answer 6

I'm strictly a DBA so I cannot answer from a developer standpoint. As a DBA I can see several uses for the script. It can show you how big the database and transaction log files are in terms of physical size on the disks and how much of that space is actually used. For example; let us say you have a database file that you check today that is 50GB in size but only 5GB is being used. A month later you check again and that same database file is 70GB in size with 60GB of it being used. This tells you that the amount of data added to the database exceeded the original size and the physical file had to grow. You now have to decide if that is going to be a problem or not. If the amount of data added is going to follow that trend then you have to be sure there is enough available drive space.

Lee