Get all SQL Server databases sizes in Bytes, MBs & GBs. Here in script f.size is the number of 8K pages in the database, so there are f.size * 8 * 1024 bytes in the database & the rest is pure conversion to mega/giga/ etc..
There are 1048576 bytes in a megabyte, 1073741824 bytes in a gigabyte.
Simply copy the below script & run it in SSMS
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
select d.name as Database_Name, f.name as Physical_File_Type, f.physical_name as Physical_File_Location, f.state_desc as Online_Status, f.size * 8.00 * 1024.00 as Size_In_Bytes, cast((f.size * 8.00 * 1024.00) / 1048576.00 as numeric (18,2)) as Size_In_MB, cast((f.size * 8.00 * 1024.00) / 1073741824.00 as numeric(18,2)) as Size_In_GB, cast(cast(v.total_bytes - v.available_bytes as float) / cast(v.total_bytes as float) * 100 as numeric(18,2)) Used_Disk_Percent from sys.master_files f inner join sys.databases d on d.database_id = f.database_id cross apply sys.dm_os_volume_stats(f.database_id, f.file_id) v order by d.name |
Here is the output
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…