8

I want to know the factors that we need to consider in case we need to enable auto-growth settings for my PROD database.

I have already checked the database growth history and the database is growing 1 to 1.5 GB daily on a daily basis(on an average).

Current Database size is 600 GB. Also, I have checked the available free space and we have sufficient space for the files to growth. However, if I set auto growth now to 1 GB, then this space will get filled up after some time.

Please suggest me some more factors that should be taken into account so that I can utilize this available free space effectively and thus eliminate the need for new disk for at-least 10-12 months.

David Browne - Microsoft
  • 49,000
  • 3
  • 53
  • 102
LAKSHAY ARORA
  • 71
  • 1

3 Answers3

4

You've made an impressive start, here, starting to gather raw data that you can monetise and present to the business. "This much data, this much cash each year".

To make plans about data usage, you need to have an understanding of what's being stored in the database and, more importantly, how long your business should retain it (or, indeed, how long they're allowed to keep it).

All too often, business people just want to keep everything. "Disk space is cheap".

That's fine, as long as they're prepared to put up the money for the extra disk space involved and for the overhead of [your] maintaining the table structures with things like Partitioning, to keep their Applications performing well.

These days, however, there are additional, regulatory restrictions on how long data can be kept for and when it must be deleted at a User's request and, again, your business needs to come up with mechanisms for getting rid of data that they're no longer entitled to have or have been asked to delete.

It's not something you can do on your own; you need to be working with your business to come up with the strategy together.

Phill W.
  • 9,889
  • 1
  • 12
  • 24
3

Here are some recommendations on your situation:

  • grant SQL Server's service account permission to "Perform Volume Maintenance tasks" in Windows to enable Instant File Initialization, this will allow data file to grow much quicker
  • best is to pre-grow your database data file so it does not auto-grow automatically everyday, at all
  • consider using database compression. Rebuild your tables/indexes with data_compression = page. That will save significant amount of space for you
Aleksey Vitsko
  • 6,148
  • 5
  • 39
  • 70
2

I wish I could fit this in a comment instead of a new answer. Above answers cover many important aspect but did not mention about When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points. Since you did not mention what version of SQL Server Engine you are dealing with I recommend you familiar yourself with Trace Flag 1117 and change made starting SQL Server 2016. Here are the resources that will help you.

One point missed by many is Trace Flag 1117 's behavior is turned on by default ONLY FOR TempDB database starting SQL Server 2016 . If you need to turn on same for any database other than TempDB read this document.

SqlWorldWide
  • 13,687
  • 3
  • 30
  • 54