Monday, 28 July 2014

Checking memory (spaced) used, numbers of records in SQL Table

Problem: 
I was told in one of the project to give find the rate at which table is growing in terms of memory and number of records.


Solution: 
After bit research I found a stored procedure ‘sp_spaceused’. This procedure take table name as parameter and give you result of space and count used by tables. 



I stored this result in an table along with dates and executed query via job every week to track the increase. After 3 months i was able to answer the question that at what rate my fact tables is growing.

The best way to execute on all table is to use sp_msforeachtable stored procedure. 

sp_msforeachtable 'exec sp_spaceused [?]'

MSDN Reference Link: SP_SPACEUSED

No comments:

Post a Comment