How Long Before Your Database Runs Out of Space?

  • SQLBlimp

    SSCertifiable

    Points: 6325

    Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?

  • Ray Mond

    SSCertifiable

    Points: 7099

    Nice informative article. We have a tool that performs similar database growth and size data collection and analysis, and also raise alerts when certain limits are reached e.g.

    - when your disks cannot accommodate the next 2 growth extents of your database

    - when a path has less than 250 MB free

    - when the projected growth rate for the next 14 days exceeds your disks current capacity

    - when your databases' average growth rate exceeds 20%

    - when your TEMPDB database exceeds 1 GB in size

    - when your databases' average growth rate exceeds 200 MB

    - when your databases' transaction log exceeds 1 GB in size

    - when the disks containing your MASTER database has less than 500 MB free space remaining

    All the alerts are customizable. Have a look http://www.yohz.com/size_details.htm, and a single-instance license is available for free.

    SQL BAK Explorer - read SQL Server backup file details without SQL Server.
    Supports backup files created with SQL Server 2005 up to SQL Server 2017.

  • robert.nimstroem

    Valued Member

    Points: 61

    Nice.

    But you should also inform people looking at this that the user running this script has to have the sysadmin role and that xp_cmdshell and OLE AUTOMATION must be turned. on.

  • tromeo

    SSC Veteran

    Points: 289

    Very Nice! Sorry I tried to give an excellent rating, but my mouse malfunctioned.

    I'll try the size history method that you mentioned, looks like a clever approach.

    We use a tool for DB Backup that also performs disk space checks DBCC check DB, copy or FTP the backups off site, reindex, and provides a simple interface to purge old data.

    It generates reports and we receive emails on issues and potential issue, but could receive the complete report.

    It will also shrink the transaction log if we choose to turn that on.

    http://www.complianceabc.com/dbhealthassist.aspx

    http://www.complianceabc.com/dbdatacleaner.aspx

    http://www.complianceabc.com/dbbackup.aspx

    Tom Romeo
    Practical Compliance Solutions Corp.
    One Orient Way - Suite F156.
    Rutherford, NJ 07070-2524
    --------------------------------
    www.complianceabc.com
    email: tromeo@complianceabc.com
    Phone: 201-728-8809

  • Alan Burstein

    SSC Guru

    Points: 61084

    Nice article John. Well done.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • rick.foster

    Old Hand

    Points: 360

    HI Can you tell me what source supports this statement? "1024MB and multiples thereof have previously caused problems in SQL Server"? I searched the web and didn't find anything, I always use 1024 multiples for growth and/or max memory settings. Out of habit I also choose character size to the power of 2 when possible and practical, it comes from writing assembler code many moons ago. Then odd sizes was out.

    Nice article, I do something similar with 110 SQLServers but use "wmic.exe logicaldisk get deviceid,freespace,size, mediatype" and sift out mediatype 12. I got it from an earlier article on this site.

  • SQLBlimp

    SSCertifiable

    Points: 6325

    rick.foster (6/9/2016)


    HI Can you tell me what source supports this statement? "1024MB and multiples thereof have previously caused problems in SQL Server"? I searched the web and didn't find anything, I always use 1024 multiples for growth and/or max memory settings. Out of habit I also choose character size to the power of 2 when possible and practical, it comes from writing assembler code many moons ago. Then odd sizes was out.

    Nice article, I do something similar with 110 SQLServers but use "wmic.exe logicaldisk get deviceid,freespace,size, mediatype" and sift out mediatype 12. I got it from an earlier article on this site.

    Hi - I ran into this a few years ago, and I will look up and post the links to the issue(s) in this discussion.

    Thanks

    John.

  • Ed Wagner

    SSC Guru

    Points: 286982

    rick.foster (6/9/2016)


    HI Can you tell me what source supports this statement? "1024MB and multiples thereof have previously caused problems in SQL Server"? I searched the web and didn't find anything, I always use 1024 multiples for growth and/or max memory settings. Out of habit I also choose character size to the power of 2 when possible and practical, it comes from writing assembler code many moons ago. Then odd sizes was out.

    I know you can experience problems if you grow your log file in 4 GB increments, but I'm not aware of a 1024 MB problem. Paul explains the 4 GB problem at http://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/.

    Nice article, I do something similar with 110 SQLServers but use "wmic.exe logicaldisk get deviceid,freespace,size, mediatype" and sift out mediatype 12. I got it from an earlier article on this site.

    That would probably the one I wrote on drives, time and space at http://www.sqlservercentral.com/articles/Drive+space/134523/. I hope it's working well for you.

  • rick.foster

    Old Hand

    Points: 360

    It is, and it is working quite well, made some changes but the core would still be recognizable. Built a little DW to do some more in-depth trending but again you would recognize the trend scripts. Thanks, Rick

  • Ed Wagner

    SSC Guru

    Points: 286982

    rick.foster (6/9/2016)


    It is, and it is working quite well, made some changes but the core would still be recognizable. Built a little DW to do some more in-depth trending but again you would recognize the trend scripts. Thanks, Rick

    That's so very nice to hear, so thank you for the feedback.

  • ZdenekS

    Grasshopper

    Points: 15

    Why don't use for this integrated Management Data Warehouse?

  • SQLBlimp

    SSCertifiable

    Points: 6325

    This is the article that I remembered. multiples of 4GB (4096MB) problems in logs. Call me skittish, but I use X000MB increments for everything now. 😀

  • Ray Herring

    SSCertifiable

    Points: 5533

    Hi John,

    Lot's of good ideas and code snippets that I plan to put to good use. Thanks,

    You should look into sys.dm_os_volume_stats(<database_id>, <file_id>. This is a great little function added I think in SQL 2008R2. (perhaps in an SP).

    The function provides about everything you need when you join it with sys.master_files on database_id and file_id.

    Mount Point, Volume Id, Volume Logical Name, Total Bytes, Available Bytes.

    I have found it very useful. 😉 it avoids xp_cmdShell complications and I think "View Server State" is the highest permission required.

    It is not in BOL but it is on TechNet

    Thanks Again,

    Ray

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8969

    instead of @@SERVERNAME, I would suggest set @svrName = cast(serverproperty('MachineName') as varchar(128))

    Wilfred
    The best things in life are the simple things

  • Joe O'Connor

    SSC Eights!

    Points: 904

    SQLBlimp - Wednesday, June 8, 2016 10:23 PM

    Comments posted to this topic are about the item How Long Before Your Database Runs Out of Space?

    Thanks for sharing this!  I have implemented this exact idea in our environment - Calculates the average daily growth of all the database files on each volume and how many days until that volume reaches capacity.  It does not take SQL File growth size into account, so there may be room for improvement

    I don't have mount points in my environment, so I'm curious if my space gathering method (which does not use xp_cmdshell) gets the data from mount points as well - I don't mind sharing if it helps and gets you away from xp_cmdshell 🙂

    SELECT DISTINCT
       @@SERVERNAME AS ServerName
      , vs.volume_mount_point AS VolumeName
      , vs.logical_volume_name AS VolumeLabel
      , vs.total_bytes AS VolumeCapacity
      , vs.available_bytes AS VolumeFreeSpace
      , CAST(vs.available_bytes AS FLOAT) * 100 / CAST(vs.total_bytes AS FLOAT) AS VolumeFreePercent
    FROM sys.master_files AS f
      CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

Viewing 15 posts - 1 through 15 (of 38 total)

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