Выборка по времени
SELECT * FROM таблица WHERE CONVERT(CHAR(8),поледаты,8) BETWEEN '11:00:00' AND '18:00:00'
Как вывести список пользователей
SELECT * FROM sys.server_principals
Как сделать фрагментацию индексов и обновление статистики
USE tsd1;
GO
-- Find the average fragmentation percentage of all indexes
-- in the HumanResources.Employee table.
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'tsd1'), OBJECT_ID(N'dbo.PRICELISTDATA_TSD'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
Все сервера mssql в локальной сети
create table #t (m ntext)
insert #t(m)
exec master..xp_cmdshell 'osql -L'
select case ltrim(cast(m as varchar(100)))
when '(local)' then cast(serverproperty('MachineName') as varchar(100))
else ltrim(cast(m as varchar(100))) end
from #t
Копирование структуры таблицы в MS SQL
select * into [destinationTable] from [sourceTable] where 0 = 1
Сжать лог у всех баз на сервере
--select * from [master].[sys].[databases]
--where not name in ('master','tempdb','model','msdb')
declare @name nvarchar(512)
declare @cmd nvarchar(512)
DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FOR
select name from [master].[sys].[databases]
where not name in ('master','tempdb','model','msdb')
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
--print @name
set @cmd =
'USE ['+@name+']
GO
DBCC SHRINKFILE (N'''+@name+ '_log'', 0, TRUNCATEONLY)
GO'
print @cmd
--exec (@cmd)
FETCH NEXT FROM @Cursor INTO @name
end
перевод всех бд в simple
declare @db nvarchar(10)
declare @sql nvarchar(256)
declare cursor_size_srv cursor for
SELECT --@@SERVERNAME AS Server ,
name AS DBName
--recovery_model_Desc AS RecoveryModel ,
--Compatibility_level AS CompatiblityLevel ,
--create_date ,
--state_desc
FROM sys.databases
where name not in ('master','msdb','model','tempdb') and recovery_model_desc = 'full'
ORDER BY Name;
OPEN cursor_size_srv
FETCH NEXT FROM cursor_size_srv INTO @db
WHILE (@@FETCH_STATUS=0)
BEGIN
set @sql = 'ALTER DATABASE '+@db+' SET RECOVERY SIMPLE WITH NO_WAIT'
print (@sql);
FETCH NEXT FROM cursor_size_srv INTO @db
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv
Сенсор для prtg. Сенсор для задания sql
USE msdb
SELECT
TOP 1
/* a.job_id AS Id
,a.name AS NAME_job
, b.run_date
, b.run_time ,
b.step_id,*/
b.run_status
--SELECT *
FROM
dbo.sysjobs a
FULL OUTER JOIN dbo.sysjobhistory b
ON a.job_id = b.job_id
AND a.[enabled] = '1'
--AND b.run_date = @@servername
WHERE
a.name IN ('LSRestore_s0030_ax_molniya_DB') AND b.run_status NOT IN ('4') AND b.step_id IN ('0')
/*GROUP BY a.name
,a.job_id
,b.step_id
,b.run_date*/
ORDER BY
b.run_date DESC,
b.run_time DESC
Настройка самого сенсора в prtg
ресторинг n баз из одного бэкапа под разными именами
declare @name nvarchar(56) declare @cmd nvarchar(512) DECLARE @Cursor CURSOR SET @Cursor = CURSOR FOR select '0'+right(servername,2) as name from s0055.rkur_dbastat.dbo.ServerName where is_disable = 0 --and servername = 's1027' OPEN @Cursor FETCH NEXT FROM @Cursor INTO @name WHILE (@@FETCH_STATUS = 0) BEGIN print @name set @cmd = 'RESTORE DATABASE [planograms'+@name+'] FROM DISK= N''H:\MSSQL\Backup\etalon_planograms_20150112.bak'' WITH FILE = 1 ,MOVE N''Planograms'' TO N''H:\MSSQL\Data\planograms'+@name+'.mdf'' ,MOVE N''Planograms_log'' TO N''H:\MSSQL\Data\planograms'+@name+'_1.ldf'' , NOUNLOAD , STATS = 10 ' print @cmd --exec (@cmd) FETCH NEXT FROM @Cursor INTO @name end
Как посмотреть файл трассировки в самом sql
SELECT top 50 *
FROM fn_trace_gettable(N'\\192.168.0.38\Backup\Trace_sql_serverov\ho_report\TracE20150822 000000.trc',DEFAULT)
where CONVERT(CHAR(8),StartTime,8) BETWEEN '09:00:00' and '23:00:00'
and not ApplicationName in ('NULL','Symantec Backup Exec™ for Windows Servers')
AND ApplicationName NOT LIKE ('SQLAgent - TSQL%')
AND ApplicationName NOT LIKE ('DatabaseMail%')
AND textdata not LIKE ('RESTORE%')
ORDER BY duration DESC
SELECT * FROM fn_trace_gettable(N'C:\TraceFiles\LongRunningQueries.trc',DEFAULT);