SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Internals of sp_spaceused

This article is an effort to dissect the output of the sp_spaceused stored procedure.

Introduction

Understanding the database usage internals and the growth trends play a vital role in the defining the right sizing of the database. sp_spaceused is probably an administrator’s most widely-executed system stored procedure to find the disk space used by a database. This helps get a quick glimpse of the database usage. statistics. sp_spaceused is used to display the number of rows, the data size, index size, amount of used space, unused space by each object, and the unallocated size of the database. Although looking at the values given by sp_spaceused, one shouldn’t think of shrinking the database or data file or log file. Many a time, we are unaware of what we are doing. Many a time, we don’t know what would be the aftereffects of doing such resource intrinsic operations. The output of sp_spaceused tells us a lot about the current performance of the database. The unallocated column and the unused column tell us the free space left at the database and the table levels.

This article considers:

  1. A peek into sp_spaceused
  2. Impact of the auto-growth setting on the columns, unallocated and unused
  3. Finding the space usage details at the database and the instance levels
  4. Measuring the auto-growth events
  5. Finding the mdf and ldf file sizes
  6. Factors determining the performance of the database
  7. And more…

Further reading, click the following link sp_spaceused

Thanks for reading my space!!

 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

Comments

Leave a comment on the original post [sqlpowershell.blog, opens in a new window]

Loading comments...