Navigation




Получить режим восстановления для всех баз SQL кроме системных

SELECT [name], DATABASEPROPERTYEX([name],'recovery') AS Recovery_model
FROM sysdatabases
WHERE name not in ('master','model','tempdb','msdb') 
ORDER BY Recovery_model, name

Изменить режим восстановления на SIMPLE для всех баз SQL кроме системных

USE MASTER
declare
   @isql varchar(2000),
   @dbname varchar(64),
   @logfile varchar(128)
 
   declare c1 cursor for 
   SELECT  d.name, mf.name as logfile
   FROM sys.master_files mf
      inner join sys.databases d
      on mf.database_id = d.database_id
   where recovery_model_desc <> 'SIMPLE'
   and d.name not in ('master','model','msdb','tempdb') 
   and mf.type_desc = 'LOG'   
   open c1
   fetch next from c1 into @dbname, @logfile
   While @@fetch_status <> -1
      begin
 
      select @isql = 'ALTER DATABASE ' + @dbname + ' SET RECOVERY SIMPLE'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' checkpoint'
      print @isql
      exec(@isql)
      select @isql='USE ' + @dbname + ' DBCC SHRINKFILE (' + @logfile + ', 1)'
      print @isql
      exec(@isql)
 
      fetch next from c1 into @dbname, @logfile
      end
   close c1
   deallocate c1

SQL - какая база грузит процессор

WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
           FROM sys.dm_exec_plan_attributes(qs.plan_handle)
           WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
    DatabaseName, [CPU_Time_Ms],
    CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY row_num OPTION (RECOMPILE);

SQL - как посмотреть все процессы с конкретной базой

select db_name(dbid) as db, spid as idproc, loginame, program_name, status
from sys.sysprocesses
where db_name(dbid) = 'DB1CX'

SQL - как убить процессы для конкретной базы

Перед выполнением запроса заблокируйте базу на сервере 1С, в запросе измените переменную dbname

set nocount on
declare @dbname varchar(100)
declare @query varchar(max)
set @query = ''
set @dbname = 'DB1CX'
select @query=coalesce(@query,',' )+'kill '+convert(varchar, spid)+ '; '
from sys.sysprocesses where dbid=db_id(@dbname)
if len(@query) > 0
begin
exec(@query)
end
Теперь надо просто разблокировать базу на сервере 1С.

SQL - Размер индексов со столбцами

USE DB1CX
SELECT        sys.tables.name AS Таблица, SUM(sys.columns.max_length) AS ДлинаИндекса, i.name AS Индекс, sys.columns.name AS Столбец 
                        
FROM            sys.tables INNER JOIN
                         sys.indexes AS i INNER JOIN
                         sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id INNER JOIN
                         sys.columns ON ic.column_id = sys.columns.column_id AND ic.object_id = sys.columns.object_id ON sys.tables.object_id = i.object_id
						 --where sys.tables.name = ''
GROUP BY  sys.tables.name, i.name, sys.columns.name
ORDER BY ДлинаИндекса DESC