Databases size
The following statement returns the size of all databases in current SQL Server instance in Megabytes. Information is separated in data and log information.
Category MSSQL
Non Clustered Primary Keys in current database
Creating a Primary Key in SQL Server, by default creates a Clustered Index. The following statement shows all Primary Keys in current database that are using nonclustered indexes instead of clustered. This might not be necessarily wrong, as a clustered…
Memory Allocation in SQL Server
xp_readerrorlog
Batch Requests / second
DECLARE @BRPS BIGINTSELECT @BRPS=cntr_valueFROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘Batch Requests/sec%’WAITFOR DELAY ‘000:00:01’SELECT (cntr_value-@BRPS) AS “Batch Requests/sec”FROM sys.dm_os_performance_countersWHERE counter_name LIKE ‘Batch Requests/sec%’
Resolve the blocking tree in multi-blocking situation
The situation where one or more session are blocking one or more sessions each, and the blocked ones block some other session(s) themselves is something that can occur.
Check if any long operation is running (Backup/Restore/DBCC CHECKDB)
You can check the progress of your Backup/Restore/CheckDB with the following statement
Check for suspect pages
SQL Servers keeps record of the corrupted pages it accesses in msdb table dbo.suspect_pages. Note that table only holds 1000 rows. Then again you should not find any records in it whenever querying it!
Check Backups
Following statement can be used to see the last backups of all databases. You get information of last backup’s start time, end time, duration in minutes, hours since last backup both for full and log backups. SELECT db.name,db.recovery_model_desc,d.[Database Backup Start],d.[Database…