SQL and OLAP Database Sizes Retrieval

The stored procedure GetTableDataSizeByYear retrieves the data size and reserved space of SQL and OLAP databases for a specific year. This guide provides instructions on how to use this stored procedure to obtain information about database sizes, which is essential for capacity management and future disk space projections.

To create and run the stored procedure in SQL Server Management Studio (SSMS), follow these instructions:

1. On your machine, open SQL Server Management Studio (SSMS), click on Connect, then select the Database Engine. Enter the server name and choose an Authentication type.

   

   Connect to Database Engine

   
   

   

   Connect to Server

   
   

2. In the left pane, go to Databases > <database_name> > Programmability > Stored Procedures.

   

   Stored Procedure

   
   

3. Right-click on dbo.GetTableDataSizeByYear and select Execute Store Procedure...

   

   Execute Stored Procedure

   
   

4. Type your desired year under the Value column.

5. Click OK.

   

   Execute Procedure

   
   

   The result should include columns for the year, table name, row count, data size, and reserved size for the SQL Database. For the OLAP Database, the results should display the approx_olap_db_size_gb.

   Year	TableName	RowCount	data_size_gb	reserved_size_gb
   2014	--All--	1772360	0.3465	0.3653
   2014	95	1046310	0.2017	0.2109
   2014	102	542658	0.1076	0.1098
   2014	94	9100	0.019	0.0216
   2014	93	52949	0.0133	0.0155
   2014	75	31343	0.0049	0.0075

   SQL Database Size Query Results
   
   
   tip

   The --All-- entry under the TableName column represents the aggregated values for all tables.

   
   

   approx_olap_db_size_gb
   0.0381

   OLAP Database Size Query Results
   
   
   NOTE

   The output is an estimate of the size, and the numbers may differ from the actual MDF (SQL Server data file) size due to the inclusion of other data not reflected in this result set.

   You can use the results above to create a bar chart for capacity management and future disk space projections, utilizing the data from the query results.

   

   Database Size Query Results Graph