Check MSSQL Database Size

 

To check the size of your database in SQL Server, you can use the following query:

Chatgpt - Refer this https://chatgpt.com/share/681c2bc9-c948-8001-8530-d2563ec53c59

1. Check Database Size (Data + Log)

USE YourDatabaseName;
GO
EXEC sp_spaceused;

2. Detailed Database Size (Data and Log Separately)

SELECT 

    name AS FileName,

    size * 8 / 1024 AS SizeMB, 

    max_size * 8 / 1024 AS MaxSizeMB,

    physical_name AS FilePath,

    type_desc AS FileType

FROM sys.master_files

WHERE database_id = DB_ID('YourDatabaseName');

3. Size of All Databases on the Server

SELECT 

    DB_NAME(database_id) AS DatabaseName,

    SUM(size * 8 / 1024) AS SizeMB

FROM sys.master_files

GROUP BY database_id;

Analysis and Recommendations:

1. High Index Size: Your index size is almost as large as your data size (4.09 GB for indexes vs 4.83 GB for data).

   • This suggests you may have many indexes, including unused or redundant indexes.

   • Consider using the following query to check index usage statistics:

     SELECT 
         OBJECT_NAME(ix.[object_id]) AS TableName,
         ix.name AS IndexName,
         ius.user_seeks, 
         ius.user_scans, 
         ius.user_lookups, 
         ius.user_updates
     FROM sys.dm_db_index_usage_stats AS ius
     INNER JOIN sys.indexes AS ix 
         ON ius.[object_id] = ix.[object_id]
         AND ius.index_id = ix.index_id
     WHERE OBJECTPROPERTY(ix.[object_id], 'IsUserTable') = 1;
     

   • 


2. Unused Space: You have around 849.55 MB of unallocated space, which is fine, but if you need to optimize disk usage:

   • You can shrink the database using:

     DBCC SHRINKDATABASE (syncdb);
     

   • 


3. High Database Size (87 GB): If this is not normal for your system, consider:

   • Regularly deleting old data (using the batch delete stored procedure I helped you create).

   • Monitoring log file size (LDF) for unexpected growth.

   • Optimizing indexes and queries.