How Long Before Your Database Runs Out of Space?

Question

How Long Before Your Database Runs Out of Space?

SQLBlimp

SSCertifiable

Points: 6340
More actions
June 8, 2016 at 10:23 pm

#314373

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

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

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

Ray Mond SSCertifiable Points: 7159 More actions · Answer 1

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 More actions · Answer 2

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: 284 More actions · Answer 3

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: 61136 More actions · Answer 4

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 More actions · Answer 5

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: 6340 More actions · Answer 6

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: 287024 More actions · Answer 7

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

rick.foster Old Hand Points: 360 More actions · Answer 8

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: 287024 More actions · Answer 9

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ZdenekS Grasshopper Points: 15 More actions · Answer 10

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

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 11

Ed Wagner (6/9/2016)
(SNIP)
http://www.sqlskills.com/blogs/paul/bug-log-file-growth-broken-for-multiples-of-4gb/.

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 More actions · Answer 12

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: 9003 More actions · Answer 13

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 More actions · Answer 14

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;