Show Database and Log sizes, free space and location

  • yleonidsh

    Valued Member

    Points: 69

    Comments posted to this topic are about the item Show Database and Log sizes, free space and location

  • roland.hangg

    SSC Veteran

    Points: 269

    Very nice script, thanks for it. I have a suggestion for it so it will work also in a mirroring environment if not all databases are principle or if a database is offline...

    Just replace line 52 with this statement:

    DECLARE cur_Databases CURSOR FAST_FORWARD FOR

    SELECT DatabaseName = [name] FROM dbo.sysdatabases

    where status not in (32, 36, 48, 512, 528, 536, 1024, 2048, 3104,

    4096, 65568, 65664, 66048, 66056, 66560, 66568, 67072, 70664,

    4194336, 4194340, 4194352, 4194824, 4260360, 4194352, 4259872, 1073807392)

    ORDER BY DatabaseName

    That's all. Have a nice day!

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Thank you Leonio.

    Newbie to SQl!

    i need to get a report like this but that also details the last access date for databases.

    How can i get this with your script.

    Not a SQL expert.

  • yleonidsh

    Valued Member

    Points: 69

    Hello,

    I have found nice solutions online. For instance this link

    http://stackoverflow.com/questions/711394/how-do-you-find-the-last-time-a-database-was-accessed

    Using this idea last statement in my script may look like following:

    ...

    ...

    SELECT

    DatabaseName = fsi.DatabaseName,

    FileGroupName = fsi.FileGroupName,

    LogicalName = RTRIM(fsi.LogicalName),

    FileName = RTRIM(fsi.FileName),

    FileSize = CAST(fsi.FileSize*@PageSize/@Kb as decimal(15,2)),

    UsedSpace = CAST(ISNULL((fs.UsedExtents*@PageSize*8.0/@Kb), fsi.FileSize*@PageSize/@Kb * ls.SpaceUsedPercent/100.0) as decimal(15,2)),

    FreeSpace = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as decimal(15,2)),

    [FreeSpace %] = CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0) / fsi.FileSize * 100.0), 100-ls.SpaceUsedPercent) as decimal(15,2)),

    lad.LastAccessDate

    FROM #FileSize fsi

    LEFT JOIN #FileStats fs

    ON fs.FileName = fsi.FileName

    LEFT JOIN #LogSpace ls

    ON ls.DatabaseName = fsi.DatabaseName

    LEFT JOIN (SELECT

    DatabaseName = DB_NAME(database_id),

    LastAccessDate = COALESCE(MAX(last_user_seek),MAX(last_user_scan),MAX(last_user_lookup),MAX(last_user_update))

    FROM sys.dm_db_index_usage_stats

    GROUP BY DB_NAME(database_id)

    ) lad ON fsi.DatabaseName = lad.DatabaseName

    ORDER BY LEFT(RTRIM(fsi.FileName),1), CAST(ISNULL(((fsi.FileSize - UsedExtents*8.0)*@PageSize/@Kb), (100.0-ls.SpaceUsedPercent)/100.0 * fsi.FileSize*@PageSize/@Kb) as int) DESC

    Regards,

    Leonid

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Thanks Leonid.

    Unfortunately, I don't have any SQL programming training.

    I took what you had and tried to run it and had errors.

    I will be under taking some training, however, in the meantime I want to be able to run a script that would provide the info needed.

    So I have found 2 seperate scripts but need to have the results produced in one report.

    I would apprecaite any help you can offer.

    Thanks

  • yleonidsh

    Valued Member

    Points: 69

    Hello,

    See attachment

    Regards

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Thank you very much Leonid. Perfect!

    Since I'm running this on different servers, is there a way to list the server name on the report?

    I would apprecaite any input on training suggestions. This is a learning curve and I'm getting excited!

    Once again thanks. have a great day.

  • jtodd 60645

    SSC Enthusiast

    Points: 180

    Got it!

    Thanks again.

  • rjoseph

    Ten Centuries

    Points: 1087

    Hi Leonid and Gurus

    I really like your script and would like to use it as report via email in an html format. Would anyone help me how to get that to work. I am also a newbie in SQL environment. I would really appreciate your help on that.

  • Wayne West

    SSC-Insane

    Points: 22586

    Nice one, Leonid. I've made similar scripts in the past for such information, but I like yours better. 🙂 Definitely going in to my toolbox.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 10 posts - 1 through 10 (of 10 total)

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