||SQL Server Tips by Robin Schumacher
Storage Monitoring Basics
With a basic understanding of how SQL Server uses storage out of the
way, attention can be turned to how one should monitor space in SQL
Server to avoid immediate and vampire bottlenecks.
As has already been presented, a storage bottleneck can be an
overwhelming event that can completely stop the operation of a
database if it is severe enough. In SQL Server, a storage bottleneck
normally takes one of four forms:
Lack of free space in a database.
Lack of free space in a database
Lack of free space on the server.
One or multiple forms of fragmentation.
It is important that a DBA be able to
quickly spot storage bottlenecks before they threaten a database’s
availability and also know to correct and proactively prevent
bottlenecks from interrupting the key operations of a database. In
online transaction processing (OLTP) environments, the threat of
exhausting the available free space in a database during peak
working hours is always present. This being the case, how can the
DBA easily spot storage bottlenecks before they stop the operation
of one or more of the key databases?
The up_bn_storage_overview procedure below provides an overview of
the storage situation on an SQL Server, versions 7 - 2000. It
provides overview sections that display a count of databases,
filegroups, and files, along with amounts for total database and log
used in MB. It then displays detailed information for every database
on the server and breaks out space by database and log. Finally, it
displays information regarding space utilization on the server’s
-- Script is available in the Online Code Depot
Before examining the procedure’s output, a couple of notes about the
SQL procedures and code that are shown in this book should be
One will see a number of cursors used throughout most every
procedure that builds constructs of tables and databases. It is
certainly OK to replace them with the sp_msforeachtable and
sp_foreachdb procedures that allow easy looping through table and
Sometimes it is necessary to show alternative procedures or queries
for different versions of SQL Server because they vary too much to
simply point out the distinctions. Other times, only one or more
differences will be pointed out so the entire procedure’s code will
not have to be repeated.
The above book excerpt is from:
High-Performance SQL Server DBA
Tuning & Optimization Secrets