Today, I am jumping into Adam Machanic T-SQL Tuesday challenge with the following post.
Recently, I created a build request to have a new server to move SQL Server databases. I put together an estimate for the space needed for data, logs and backups and included this information in the build document. This request was put in the queue and all was well. Once this request popped to the top of the queue I reviewed the size of database files using Idera’s Space Analyzer and noticed unexpected data growth in a few of the data files. Thankfully, the data collector and Management Data Warehouse (MDW) was enabled which helped us track the unexpected data growth.
Introduction
SQL Server 2008 introduces the data collector and the management data warehouse (MDW) into SQL Server Management Studio (SSMS). The Management Data Warehouse exists by leveraging the data collector and a MDW database and custom reports. This allows administrators and developers the opportunity to do some proactive monitoring. If you are familiar with the Performance Dashboard then you will be impressed with the new Management Data Warehouse.
How does the data collector help you?
Out of the box, the data collector provides you with the tools to monitor disk usage, server activity and query statistics. Therefore, when you are asked the following questions. How much space do we need for database xyz? Why did database xyz grow from size 123 to size 456? you can use the data collector report to help answer these questions. For example, below is a screen shot of some sample databases.
You can see that the third, forth and fifth databases had their current size changed. I want to focus on this fact because it doesn’t represent the data inside the database files. This means the actual data files (mdf, ndf, ldf) are growing which can cause disk fragmentation. You want to avoid this when possible. Ideally, you want your database graph to be a straight line like the sixth database. Next we will click on the graph for the fourth database and drill deeper to the analysis.
Looking at the two image above you can see that the data size multiplied during a six hour between noon and 6pm on November 30th. We were then able to use this information to determine the cause for the unexpected growth of data.
Conclusion
I think this is a great tool to troubleshoot unexpected data growth. I look forward to using this tool as part of the process to estimate future database growth.