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

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

  • 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

    Learn to play, play to learn !

    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 but most of the time this is me

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

    -- Gianluca Sartori

  • 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

    Learn to play, play to learn !

    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 but most of the time this is me

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

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

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

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

  • You're right! It should be:

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

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

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

  • Hi-

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

    Erin

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

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