Storage Space … Check It and Plan for Growth!

When I moved into my current 3 bedroom home, from my 1 bedroom apartment, I marveled at the amount of space and storage.  I had extra rooms and closets with nothing to put in them.  However, 5 years and 1 kid later, the house does not seem as spacious and empty as it once did.  Now, I am not a hoarder.  But, sometimes it does seem hard to find an empty spot to store something.

At times, we can feel like that with a new SQL Server.  We may feel that we have plenty of space and storage.  But, over time it does fill up.  And one of the main differences between your house and a SQL Server is when your SQL Server fills its storage, it will stop working correctly.

So, how do we make sure that does not happen?

Keep an eye on your storage space

There are many ways to check or keep an eye on your storage space.  If your company has a SQL Server or IT infrastructure monitoring solution, most of them will alert you to drive space issues.  So, by all means, use it!

However, if you don’t have a third party monitoring solution, here’s what you can do:
1. T-SQL – Use the undocumented stored procedure, xp_fixeddrives, to find out the amount of free space (MB) on each drive.

Example:
EXECUTE master.sys.xp_fixeddrives

drive MB free
C 7000
F 36180
T 15826
U 38000

2. PowerShell – One of the benefits to using PowerShell over T-SQL in this instance, is that you can also easily get the total size of the drive in addition to the free space.  This is handy in figuring out what percentage of the drive is free. (Win32_LogicalDisk: http://msdn.microsoft.com/en-us/library/windows/desktop/aa394173(v=vs.85).aspx)  In the following example, we filter on DriveType=3, which represents a local disk, as opposed to something like a removable or compact disk.  Also, keep in mind that the size and freespace are in bytes, so you may want to convert them to MB.

Example:
Get-WmiObject Win32_LogicalDisk -ComputerName [YourServerName] | select DeviceId, DriveType, Size, FreeSpace | where {$_.DriveType -eq 3}

DeviceId DriveType Size FreeSpace
C: 3 34359734272 7340666880
F: 3 42946523136 37938110464
T: 3 21471686656 16595726336
U: 3 42946523136 39845904384

Plan for growth

Just like with a house, once you realize you are running out of space, you need to take some action.  Perhaps that means doing a “spring cleaning”, adding an addition, or even moving to a bigger house.  It is also quite similar with a SQL Server that is running out of space.  Now, I’m assuming we are already doing log maintenance (i.e. taking log backups if our databases are in the FULL recovery model).  So, the growth we would be seeing is from additional user data.  And this could be easily addressed by adding more space to the existing drives.

However, we don’t want to wait until we are running out of space on a drive to think about what we are going to do.  We should take a proactive approach and plan for this growth.

Just imagine if you decided to log the results of your daily checks to a table.  After a period of time, you would know how fast your data is growing daily, weekly, and even monthly.  That would allow you to make a pretty good estimate of when in the future you are going to need more space, and then you could even budget and plan for it.  Additionally, by logging this data to a table, you could setup a morning report and even some custom alerts to help you monitor your storage space.

Since databases have the tendency to store more and more data, a DBA will always need to be on top of this by checking the storage space and planning for growth.

Advertisements
This entry was posted in DBA and tagged , , . Bookmark the permalink.

2 Responses to Storage Space … Check It and Plan for Growth!

  1. Pingback: DBA Basics – A Review for the New Year | SQL Bad Boy – Michael Lowery

  2. Pingback: Undocumented Stored Procedures … Do NOT Rely on Them in Production! | SQL Bad Boy – Michael Lowery

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s