DBCC opentran;
EXEC Sp_lock
EXEC Sp_who2
DBCC inputbuffer (<spid>)
SELECT *
FROM sys.sysprocesses
WHERE dbid = Db_id('dababase')
AND blocked > 0
SELECT t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
o1.NAME 'object name',
o1.type_desc 'object descr',
p1.partition_id 'partition id',
p1.rows 'partition/page rows',
a1.type_desc 'index descr',
a1.container_id 'index/page container_id'
FROM sys.dm_tran_locks AS t1
INNER JOIN sys.dm_os_waiting_tasks AS t2
ON t1.lock_owner_address = t2.resource_address
LEFT OUTER JOIN sys.objects o1
ON o1.object_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.partitions p1
ON p1.hobt_id = t1.resource_associated_entity_id
LEFT OUTER JOIN sys.allocation_units a1
ON a1.allocation_unit_id = t1.resource_associated_entity_id
SELECT *
FROM sys.dm_tran_database_transactions
WHERE database_id = Db_id('dababase')
DECLARE @DateNow DATETIME
SELECT @DateNow = Dateadd(dd, 0, Datediff(dd, 0, Getdate()))
DECLARE @SQL NVARCHAR(max)
SELECT @SQL = (SELECT
' UPDATE STATISTICS ['
+ Schema_name(o.[schema_id]) + '].[' + o.NAME
+ '] [' + s.NAME + '] WITH FULLSCAN' + CASE WHEN
s.no_recompute = 1
THEN
', NORECOMPUTE' ELSE '' END + ';'
FROM (SELECT [object_id],
NAME,
stats_id,
no_recompute,
last_update = Stats_date([object_id], stats_id)
FROM sys.stats WITH(nolock)
WHERE auto_created = 0
AND is_temporary = 0) s
JOIN sys.objects o WITH(nolock)
ON s.[object_id] = o.[object_id]
JOIN (SELECT p.[object_id],
p.index_id,
total_pages = Sum(a.total_pages)
FROM sys.partitions p WITH(nolock)
JOIN sys.allocation_units a WITH(nolock)
ON p.[partition_id] = a.container_id
GROUP BY p.[object_id],
p.index_id) p
ON o.[object_id] = p.[object_id]
AND p.index_id = s.stats_id
WHERE o.[type] IN ( 'U', 'V' )
AND o.is_ms_shipped = 0
AND ( last_update IS NULL
AND p.total_pages > 0
-- never updated and contains rows
OR last_update <= Dateadd(dd, CASE
WHEN
p.total_pages > 4096
-- > 4 MB
THEN -2
-- updated 3 days ago
ELSE 0
END, @DateNow) )
FOR xml path(''), type)
.value('.', 'NVARCHAR(MAX)')
PRINT @SQL
EXEC sys.Sp_executesql
@SQL
DECLARE @SQL NVARCHAR(max)
DECLARE cur CURSOR local read_only forward_only FOR
SELECT ' ALTER INDEX [' + i.NAME + N'] ON ['
+ Schema_name(o.[schema_id]) + '].[' + o.NAME
+ '] ' + CASE WHEN s.avg_fragmentation_in_percent > 30 THEN
'REBUILD WITH (SORT_IN_TEMPDB = ON'
-- Enterprise, Developer
+ CASE WHEN Serverproperty('EditionID') IN (1804890536, -2117995310)
THEN
', ONLINE = ON' ELSE '' END + ')' ELSE 'REORGANIZE' END + ';'
FROM (SELECT s.[object_id],
s.index_id,
avg_fragmentation_in_percent =
Max(s.avg_fragmentation_in_percent)
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL,
'DETAILED') s
WHERE s.page_count > 128 -- > 1 MB
AND s.index_id > 0 -- <> HEAP
AND s.avg_fragmentation_in_percent > 5
GROUP BY s.[object_id],
s.index_id) s
JOIN sys.indexes i WITH(nolock)
ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
JOIN sys.objects o WITH(nolock)
ON o.[object_id] = s.[object_id]
OPEN cur
FETCH next FROM cur INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @SQL
EXEC sys.Sp_executesql
@SQL
FETCH next FROM cur INTO @SQL
END
CLOSE cur
DEALLOCATE cur
В журнале Application появляются сообщения об ошибках вида "Event filter with query "SELECT * FROM __InstanceModificationEvent WITHIN 60 WHERE TargetInstance ISA "Win32_Processor" AND TargetInstance.LoadPercentage > 99" could not be reactivated in namespace "//./root/CIMV2" because of error 0x80041003. Events cannot be delivered through this filter until the problem is corrected."
Описание и решение - https://support.microsoft.com/en-us/kb/2545227.
Необходимо установить исправление Desktop Experience Decoder Update for Windows Server 2008 R2. Это обновление добавляет декодеры H.264 и AAC для поддержки воспроизведения этих форматов.