Show Database and Log sizes, free space and location

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

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

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

  • 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

  • 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

  • Hello,

    See attachment

    Regards

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

  • Got it!

    Thanks again.

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

  • 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 9 (of 9 total)

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