There’s a lot that goes into proper placement, growth, and maintenance of the data and log files in SQL Server. Here’s what I’ve written so far and maybe some notes on what I have to come.
Growing and Shrinking
Data File Growth <– Coming around April 2016 – Should you use autogrowth or manual growths? What should autogrowth be set to and why? How do you know when to grow manually? What is Instant File Initialization, and how does that play into my strategy?
Shrinking Database Data Files – The reasons when this is acceptable and what can of worms you’re opening.
Shrinking Database Log Files – It’s more common than shrinking data files. Understand what’s going on and a couple times this is the right thing to do.
Database Log VLFs – When you grow and shrink log files, you’re adding and removing VLFs. How big are they, and what does that mean to you?
Monitoring and Trending
Monitoring Database and Table Sizes – Without using any monitoring software, trend the growth of your databases and tables. This way you know what to expect (database size) and understand how it got to that point (table size).
Database Growth Trends – Idera Diagnostic Manager <– Coming around March 2016 – How fast are your databases growing, when will they need to grow again, and when will you run out of space completely? The monitoring software is already collecting it, but the GUI doesn’t show you this.
Table Growth Trends – Idera Diagnostic Manager <– Coming around March 2016 – If you see your databases are growing, what in the database is growing? This knowledge makes for an intelligent conversation with application owners on justifying the growth. Queries data already collected by the monitoring software I use.
File Sizes – How large are your files and filegroups right now?