Querying dynamic management views:
- You can query sys.dm_exec_requests to find blocking queries.
- You can query sys.dm_os_memory_cache_counters to check the health of the system memory cache.
- You can query sys.dm_exec_sessions for information about active sessions.
- You can use sys.dm_db_index_physical_stats to check index fragmentation
Running basic DBCC commands:
- You can use DBCC FREEPROCCACHE to remove all elements from the procedure cache.
- You can use DBCC FREESYSTEMCACHE to remove all unused entries from all caches.
- You can use DBCC DROPCLEANBUFFERS to remove all clean buffers from the buffer pool.
- You can use DBCC SQLPERF to retrieve statistics about how the transaction log spaceis used in all databases.
- DBCC SHOWCONTIG to show index fragmentation
Using the KILL command to end an errant session
|
Identifying and Rectifying the Cause of a Block
|
SELECT session_id, status,
blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id > |
|
Finding Last Backup Time for All Database
|
SELECT sdb.Name AS DatabaseName,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'-')
AS LastBackUpTime
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name =
sdb.name
GROUP BY sdb.Name
|
|
Duration of backup
|
DECLARE @dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS
[Database],
bup.server_name AS [Server],
bup.backup_start_date AS
[Backup Started],
bup.backup_finish_date AS
[Backup Finished]
,CAST((CAST(DATEDIFF(s,
bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + '
hours, '
+ CAST((CAST(DATEDIFF(s,
bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ '
minutes, '
+ CAST((CAST(DATEDIFF(s,
bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ '
seconds'
AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
(SELECT MAX(backup_set_id)
FROM msdb.dbo.backupset
WHERE database_name =
ISNULL(@dbname, database_name) --if no dbname, then return all
AND type = 'D' --only
interested in the time of last full backup
GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name
|
No comments:
Post a Comment