Vadim's KB

Необходимо включить режим SQLCMD в Management Studio.

:setvar db_src "src"
:setvar db_dst "dst"

DECLARE @create_scripts TABLE
  (
     id     INT IDENTITY,
     script NVARCHAR(max)
  )
DECLARE @sql NVARCHAR(max)

BEGIN TRANSACTION recreate_constraints

BEGIN try
    USE $(db_src)

    INSERT INTO @create_scripts
    SELECT 'alter table [' + x.[table]
           + '] add constraint ' + x.pk_name
           + ' primary key clustered (' + x.pk_fields
           + ') ON [PRIMARY]'
    FROM   (SELECT kc.NAME                                      AS pk_name,
                   Object_name(kc.parent_object_id)             AS [table],
                   (SELECT Stuff((SELECT ',' + [column] + ' ' + sort
                                  FROM   (SELECT kcu.column_name      AS
                                                 'column',
                                                 kcu.ordinal_position AS
                                                 'position',
                                                 CASE
                                                   WHEN ic.is_descending_key = 1
                                                 THEN
                                                   'DESC'
                                                   ELSE 'ASC'
                                                 END                  AS 'sort'
                                          FROM
                                 information_schema.key_column_usage
                                 kcu
                                 INNER JOIN
    information_schema.table_constraints tc
            ON tc.table_name = kcu.table_name
               AND tc.constraint_name =
                   kcu.constraint_name
               AND tc.constraint_type =
                   'PRIMARY KEY'
    INNER JOIN sys.index_columns ic
            ON ic.object_id =
               Object_id(kcu.table_name)
               AND ic.index_column_id =
                   kcu.ordinal_position
    WHERE  kcu.table_name =
    Object_name(kc.parent_object_id)
    AND kcu.constraint_name = kc.NAME) cte
    ORDER  BY position
    FOR xml path('')), 1, 1, '')) AS pk_fields
    FROM   sys.key_constraints kc
    WHERE  kc.[type] = 'PK') x
    ORDER  BY x.[table] ASC

    USE $(db_dst)

    DECLARE @i INT

    SET @i = (SELECT Min(id)
              FROM   @create_scripts);

    WHILE EXISTS(SELECT TOP 1 1
                 FROM   @create_scripts
                 WHERE  id = @i)
      BEGIN
          SET @sql = (SELECT TOP 1 script
                      FROM   @create_scripts
                      WHERE  id = @i);

          EXEC(@sql);

          SET @i = @i + 1;
      END

    PRINT 'Primary keys done!'

    DELETE FROM @create_scripts

    USE $(db_src)

    INSERT INTO @create_scripts
    SELECT ' ALTER TABLE ' + const.parent_obj
           + ' WITH CHECK ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY ( '
           + const.parent_col_csv + ' ) REFERENCES '
           + const.ref_obj + '(' + const.ref_col_csv
           + ') ALTER TABLE ' + const.parent_obj
           + ' CHECK CONSTRAINT ' + const.const_name
    FROM   (SELECT Quotename(fk.NAME)                                 AS
                   [const_name],
                   Quotename(schParent.NAME) + '.'
                   + Quotename(Object_name(fkc.parent_object_id))     AS
                   [parent_obj],
                   Stuff((SELECT ','
                                 + Quotename(Col_name(fcP.parent_object_id,
                                 fcp.parent_column_id))
                          FROM   sys.foreign_key_columns AS fcP
                          WHERE  fcp.constraint_object_id = fk.object_id
                          FOR xml path('')), 1, 1, '')                AS
                   [parent_col_csv],
                   Quotename(schRef.NAME) + '.'
                   + Quotename(Object_name(fkc.referenced_object_id)) AS
                   [ref_obj],
                   Stuff((SELECT ','
                                 + Quotename(Col_name(fcR.referenced_object_id,
                                       fcR.referenced_column_id))
                          FROM   sys.foreign_key_columns AS fcR
                          WHERE  fcR.constraint_object_id = fk.object_id
                          FOR xml path('')), 1, 1, '')                AS
                   [ref_col_csv]
            FROM   sys.foreign_key_columns AS fkc
                   INNER JOIN sys.foreign_keys AS fk
                           ON fk.object_id = fkc.constraint_object_id
                   INNER JOIN sys.objects AS oParent
                           ON oParent.object_id = fkc.parent_object_id
                   INNER JOIN sys.schemas AS schParent
                           ON schParent.schema_id = oParent.schema_id
                   INNER JOIN sys.objects AS oRef
                           ON oRef.object_id = fkc.referenced_object_id
                   INNER JOIN sys.schemas AS schRef
                           ON schRef.schema_id = oRef.schema_id
            GROUP  BY fkc.parent_object_id,
                      fkc.referenced_object_id,
                      fk.NAME,
                      fk.object_id,
                      schParent.NAME,
                      schRef.NAME) AS const
    ORDER  BY const.const_name

    USE $(db_dst)

    SET @i = (SELECT Min(id)
              FROM   @create_scripts);

    WHILE EXISTS(SELECT TOP 1 1
                 FROM   @create_scripts
                 WHERE  id = @i)
      BEGIN
          SET @sql = (SELECT TOP 1 script
                      FROM   @create_scripts
                      WHERE  id = @i);

          EXEC(@sql);

          SET @i = @i + 1;
      END

    PRINT 'Foreign keys done!'

    DELETE FROM @create_scripts

    COMMIT TRANSACTION recreate_constraints;

    PRINT '----'

    PRINT 'Ok'
END try

BEGIN catch
    ROLLBACK TRANSACTION recreate_constraints;

    PRINT 'Error: ' + Error_message()
END catch 

Выключить:

powercfg -h off

Включить:

powercfg -h on

1. Install "Desktop Experience" feature.

2. Copy files manually (not for Windows Server 2012 R2):

The location of the files you need to copy depend on your version of Windows:

Once you’ve located the files move them to the following locations (Server 2012 non-R2 and earlier):

1. Copy Cleanmgr.exe to %systemroot%\System32.
    
2. Copy Cleanmgr.exe.mui to %systemroot%\System32\en-US.
    

You can now launch the Disk cleanup tool by running Cleanmgr.exe from the command prompt.

https://technet.microsoft.com/ru-ru/library/ff630161(v=ws.10).aspx

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.