In this article, we will try to pull all sizes from SQL server. How to find Database Size in SQL Server? How to Get size of all tables in the SQL Server database? We will use different SQL Queries to find Database file size, database size, and tables size. Very often we need to check the size of the Database whenever we plan to move our entire database or file or only tables from one server to another server. Using these queries above mention task will easier for us.
Below script is to find DATABASE NAME, LOG SIZE in MB, ROW SIZE in MB & TOTAL SIZE in MB for a particular database.
1 2 3 4 5 6 7 8 9 10 11 12 |
USE [OurTechIdeas.com] Go SELECT DATABASE_NAME = DB_NAME(database_id) , LOG_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'LOG' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , ROW_SIZE_MB = CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , TOTAL_SIZE_MB = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() GROUP BY database_id Go |
Below script is to find Database Name, Logical Name & Physical location for a particular database.
1 2 3 4 5 6 7 8 9 10 |
USE [OurTechIdeas.com] Go SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, Physical_Name, (size * 8) / 1024 SizeMB FROM sys.master_files WHERE database_id = DB_ID() Go |
Very often DBAs needs to know about the largest table size in the database. Following SQL query is used to find the largest table in the Database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
USE [OurTechIdeas.com] Go ;with CTE AS( SELECT t.NAME AS TABLE_NAME, s.Name AS SCHEMA_NAME, p.rows AS ROW_COUNTS, SUM(a.total_pages) * 8 AS TOTAL_SPACE_KB, CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TOTAL_SPACE_MB, SUM(a.used_pages) * 8 AS UsedSpaceKB, CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS USED_SPACE_MB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UNUSED_SPACE_KB, CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UNUSED_SPACE_MB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ) SELECT * FROM CTE ORDER BY USED_SPACE_MB DESC Go |
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…