Find Free Space by Database

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

  • 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

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

  • 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

  • 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

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

  • 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

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

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