disk-spacesql serversql-server-2008sql-server-2012
Is it possible to use historic information to show disk usage growth?
I haven't keep much in the way of database setup and disk usage information. So I can't say how much disk growth has occurred in the last 6/12/18 months.
Can I reconstruct that picture from info stored somewhere in SQL Server?
I don't recommend to shrink your database files unless you are completely sure that the space will not ever be needed again. Why shrink every night only for it to grow every day? You're going to run into the pain points of shrinking your data files, and you're forcing yourself to run into performance impact when the database files need to grow during the day.
If it ramped up to 120 GB because of initial space requirements, is it safe to say that the database will not be larger than that (obviously with some buffer)? Size your database appropriately.
Otherwise, if the database will continuously grow, and you don't have any input on the data growth that happens (in other words, if you can't archive data off of the database) then you will simply need to ensure that there is ample drive space for the required growths.
Do you know which database files have been growing? There is different behavior that will cause data files to grow, as opposed to transaction logs to grow as well as maintaining their size. If the transaction log is the main consumer of the space because of heavily logged transactions, you can consider more frequent transaction log backups to ensure the log is reused more often (there are some stops here, as open transactions, so this might not be possible across the board).
More information surrounding what is growing and the scenario is needed to get more specific, but I don't recommend scheduled shrinking of your database files.
No SQL Server does not hold a latch. The storage engine keeps track of the current index scan position using a "cookie". The cookie is revalidated if another process takes a latch (of a type that means the page might have been changed) on the same page since the cookie was acquired.
If the cookie (scan position) is no longer valid, the b-tree structure is traversed (using key + uniquifier information stored in the cookie) to reacquire the correct position, and the cookie updated.
In case it is of interest, here is a stack capture when the saved position is validated:
And another one just after cookie validation has failed due to a concurrent incompatible latch. The engine is repositioning the scan position using a seek to find the correct new position:
There are performance counters related to cookie activity, for example Scan Point Revalidations/sec and Used page/leaf page cookie.
There is no public documentation that describes exactly how this all works. The details above are based on observations, subsequently confirmed in conversations with Paul Randal and Bob Ward.
Best Answer
If your DBs reside on a SAN or other shared storage that platform may have logs you can reference. For local storage I know of nothing held by default.
A couple of years ago we started tracking DB size and disk free/used space. It has proved very valuable. A couple of Powershell scripts, a scheduled task and a small table has helped us avoid several incidents. Eventually we had enough data to make Monte Carlo prediction workable.