Navigation




SQL - Сжатие mdf файлов после удаления данных в таблицах

После удаления большого количества строк из регистра сведений, база SQL не будет меньше. То есть файл mdf так и останется большого размера.
Можно выгрузить и загрузить базу через dt, но на больших объемах (700-900 Гб), может не сработать из-за нехватки места в папке Temp на диске C.
Поможет пункт меню в самом SQL - Task-Shrink-Files
(с базой не должно быть соединений, и надо использовать флаг - Reorganize pages before releasing unused space)
Но это долгий процесс - примерно 6 часов на то чтобы уменьшить 700 Гб в 600 Гб.
Аналогично можно запустить скрипт SQL:

USE [DB1CX]
GO
DBCC SHRINKFILE (N'DB1CX' , 600000)
GO

SQL - Проверить все открытые транзакции к базе

DBCC OPENTRAN

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

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

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

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

SQL - Количество строк и размер таблиц в базе

USE DB1CX
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp