Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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 transaction log.

  • 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 hard disks.

* up_bn_storage_overview.sql

-- 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 explained.

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 database lists.

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

ISBN: 0-9761573-6-5
Robin Schumacher

 http://www.rampant-books.com/book_2005_2_sql_server_dba.htm  

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter