Capturing Baselines on SQL Server: Where's My Space?

  • Erin Stellato

    Ten Centuries

    Points: 1166

    Comments posted to this topic are about the item Capturing Baselines on SQL Server: Where's My Space?

  • Johan Bijnens

    SSC Guru

    Points: 134254

    Nice article, Erin.

    In my test, [vs].[logical_volume_name] is blanc !

    Win2008R2 / SQL2012DE (64-bit) SP1 (11.0.3000.0) with fixed drives.

    So I suggest altering the last query to :

    SELECT DISTINCT

    ( case [vs].[logical_volume_name]

    when '' then vs.volume_mount_point

    else [vs].[logical_volume_name]

    end ) AS 'Drive'

    , [vs].[available_bytes] / 1048576 AS 'MBFree'

    FROM [sys].[master_files] AS f

    CROSS APPLY [sys].[dm_os_volume_stats]([f].[database_id], [f].[file_id]) AS vs

    ORDER BY Drive ;

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • spaghettidba

    SSC Guru

    Points: 105661

    Great article, Erin.

    Also, lots of useful code in there.

    Thanks for sharing.

    ALZDBA (1/23/2013)


    Nice article, Erin.

    In my test, [vs].[logical_volume_name] is blanc !

    Win2008R2 / SQL2012DE (64-bit) SP1 (11.0.3000.0) with fixed drives.

    Johan, you get blank logical_volume_name because your disk doesn't have a label. Try assigning a label and you'll see it appear in the results.

  • Johan Bijnens

    SSC Guru

    Points: 134254

    Gianluca Sartori (1/23/2013)


    Great article, Erin.

    Also, lots of useful code in there.

    Thanks for sharing.

    ALZDBA (1/23/2013)


    Nice article, Erin.

    In my test, [vs].[logical_volume_name] is blanc !

    Win2008R2 / SQL2012DE (64-bit) SP1 (11.0.3000.0) with fixed drives.

    Johan, you get blank logical_volume_name because your disk doesn't have a label. Try assigning a label and you'll see it appear in the results.

    Checked it and ... You're right, Gianluca !

    None of our servers have labels assigned to their drives. :blink:

    Never had a use for it, except for documentation purposes, which always seem to be outdated by reality ....

    ( because of "temporary using this drive for" ... :crazy: )

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Nadrek

    SSCoach

    Points: 19979

    If you're going to capture per-file data in the first place, I'd highly recommend also capturing sys.dm_io_virtual_file_stats() data - then you can see which files/databases/disks are most "used" by a few metrics.

  • Erin Stellato

    Ten Centuries

    Points: 1166

    Nadrek (1/23/2013)


    If you're going to capture per-file data in the first place, I'd highly recommend also capturing sys.dm_io_virtual_file_stats() data - then you can see which files/databases/disks are most "used" by a few metrics.

    If you're looking for information about I/O activity, virtual file stats are absolutely what you need. For this post I targeted file size and used space for purposes of trending growth in order to create data-backed estimates for capacity planning. But if you want information about file access in terms of reads, writes, bytes read, and bytes written...as well as latency, then you should definitely capture virtual file stats. Thanks!

  • simon.murin

    SSC-Addicted

    Points: 449

    The Data Collection does it for you as well - the snapshots.disk_usage table contains db space info.

  • tim_harkin

    SSCrazy

    Points: 2392

    Great stuff here Erin. One minor item I found. Listing 2 has this as part of the script the following line:

    ; INSERT [BaselineData2012].[dbo].[FileInfo] (

    I beleive this should be

    ; INSERT [BaselineData].[dbo].[FileInfo] (

    I recently took on a role in a new company, and I am setting this up in our dev and test environments right now. Thanks!

  • Erin Stellato

    Ten Centuries

    Points: 1166

    You're right! It should be:

    ; INSERT [BaselineData].[dbo].[FileInfo] (

    I will see if I can get the script updated, thanks for the catch!

  • Tac11

    SSCertifiable

    Points: 6796

    Hi Erin,

    I am not able to use your script. I am trying to capture 'AdventureWorks' data file and log file growth every week, using SQL agent job. Can you help step by steps please?

  • Erin Stellato

    Ten Centuries

    Points: 1166

    Hi-

    The script to capture the information is in the post. What are you having a problem with?

    Erin

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

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