— List all Locks of the Current Database SELECT TL.resource_type AS ResType ,TL.resource_description AS ResDescr ,TL.request_mode AS ReqMode ,TL.request_type AS ReqType ,TL.request_status AS ReqStatus ,TL.request_owner_type AS ReqOwnerType ,TAT.[name] AS TransName ,TAT.transaction_begin_time AS TransBegin ,DATEDIFF(ss, TAT.transaction_begin_time, GETDATE()) AS TransDura ,ES.session_id AS S_Id ,ES.login_name AS LoginName ,COALESCE(OBJ.name, PAROBJ.name) AS ObjectName ,PARIDX.name ASCzytaj dalej / Read more
Statystyki WAIT TYPES
Author: Paul Randal Original post: http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ WITH [Waits] AS (SELECT [wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] – [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum] FROM sys.dm_os_wait_stats WHERE [wait_type]Czytaj dalej / Read more
Historia backupów
Wersja compact: SELECT CONVERT(CHAR(100), SERVERPROPERTY(’Servername’)) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D’ GROUP BY msdb.dbo.backupset.database_name ORDER BY msdb.dbo.backupset.database_name Wersja extended: SELECT CONVERT(CHAR(100), SERVERPROPERTY(’Servername’)) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, CASE msdb..backupset.type WHEN 'D’ THEN 'Database’ WHEN 'L’ THENCzytaj dalej / Read more
Wykorzystanie pamięci przez bazy i obiekty – memory consumption
–sprawdzanie baz DECLARE @total_buffer INT; SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager’ AND counter_name = 'Total Pages’; ; WITH src AS ( SELECT database_id , db_buffer_pages = COUNT_BIG(*) FROM sys.dm_os_buffer_descriptors –WHERE database_id BETWEEN 5 AND 32766 GROUP BY database_id ) SELECT [db_name] = CASE [database_id] WHENCzytaj dalej / Read more
Generator nagłówka komentarza
DECLARE @Napis NVARCHAR(MAX) = N’TEST’; DECLARE @MinimumLength INT = 64; DECLARE @MSG NVARCHAR(MAX); DECLARE @Offset INT = 8; DECLARE @Even BIT = CASE WHEN LEN(@Napis) % 2 = 0 THEN 1 ELSE 0 END; DECLARE @nl NCHAR(2) = CHAR(13) + CHAR(10); DECLARE @LineLength INT; PRINT @Even; IF LEN(@Napis) > @MinimumLengthCzytaj dalej / Read more