Перейти к содержимому

Исследуем базы данных с помощью T-SQL

Базовая информация

Во-первых, несколько простых @@Функций, которые предоставят нам базовую информацию.

-- Имена сервера и экземпляра <br />Select @@SERVERNAME as [Server\Instance];
-- версия SQL Server Select @@VERSION as SQLServerVersion;
-- экземпляр SQL Server <br />Select @@ServiceName AS ServiceInstance;
- Текущая БД (БД, в контексте которой выполняется запрос)Select DB_NAME() AS CurrentDB_Name;<span id="mce_marker" data-mce-type="bookmark">​</span>

Как долго ваш SQL Server работает после последнего перезапуска? Помните, что системная база данных tempdb пересоздаётся при каждом перезапуске SQL Server. Вот один из методов определения времени последнего перезапуска сервера.

SELECT @@Servername AS ServerName ,
create_date AS ServerStarted ,
DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
DATEDIFF(s,
… Читать далее

Показывает все задания в mssql с датой выполнения и вывести список заданий (fob) в sql

/************************************************************
 *  * k.moskvichev ©
 * Time: 10.12.2015 14:07:36
 ************************************************************/

USE msdb

SELECT 		
			DISTINCT
			TOP 100
			--a.job_id  AS Id
           a.name    AS NAME_job
           ,MAX(b.run_date),
            max(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'
			--WHERE
GROUP BY
       		--b.run_date, 
       		a.job_id
           ,a.name --a.job_id,a.name,b.step_id,b.run_status
ORDER BY
       		a.name       DESC
       		--  b.run_time  DESC
select * FROM [msdb].[dbo].[sysjobs]

 

Вывести список баз в sql и как узнать когда был последний бекап + где лежит физически сама база

 

 Список баз

select * from [master].[sys].[databases]
sp_helpdb

Бэкап

 

/************************************************************
 * k.moskvichev © 
 * Time: 20.07.2015 10:53:59
 ************************************************************/

--DROP TABLE #temp
SELECT 
            @@Servername                AS ServerName
      ,d.Name                     AS DBName
      ,MAX(b.Backup_finish_date)  AS databecap
       -- ,bmf.Physical_Device_name   AS gdebackup
      ,m.physical_name            AS FILENAME
      ,d.recovery_model_desc      AS modelBuckap
       ,m.size           AS DMSize  
       --INTO #temp
FROM   
       sys.databases d
       LEFT OUTER JOIN msdb..backupset b
            ON  b.database_name = d.name
                AND b.[type] = 'D'
                AND b.server_name = @@servername
                    -- FULL OUTER JOIN msdb.dbo.backupmediafamily bmf
                    --   ON  bmf.media_set_id = b.media_set_id
                    --AND bmf.[device_type] = '7'
                    
       FULL OUTER JOIN sys.master_files m
            ON  d.database_id = m.database_id
                AND m.[data_space_id] = '1'
      
WHERE  
       /*datepart(YEAR,Backup_finish_date) BETWEEN 2015 AND
… Читать далее

Время работы sql сервера

DECLARE @Days  int

DECLARE @Hours int

DECLARE @Mins  int

DECLARE @Secs  int

DECLARE @getdate datetime

DECLARE @getdateunix int

 

set @getdate = getdate()

set @getdateunix = DATEDIFF(s, '1970-01-01 00:00:00', GETUTCDATE())

 

SET @Secs = (

 

        SELECT datediff(ss, login_time, getdate())

 

        FROM master..sysprocesses 

 

        WHERE spid = 1

 

    )

 

 

SET @Days  = ((@Secs/60)/60)/24

SET @Hours = ((@Secs/60)/60)%24

SET @Mins  = (@Secs/60)%60

SET @Secs  = @Secs%60

 

 

select @@servername as servername,@getdate as createdate,@getdateunix as createdate_unix ,@Days as days,@Hours as hours,@Mins as mins ,@Secs as secs

 

Переименовать sql сервер

sp_dropserver 'старое имя сервера', 'droplogins' ;

GO

sp_addserver 'новое имя сервера', local;

GO

 

Узнать имя сервера select @@servername
net stop mssqlserver && net start mssqlserver

После переименования сервера не обходимо перезапустить службу MSSQLSERVER.

Перезапуск служб через командную строку

 

если были репликации

 

EXEC master.dbo.sp_serveroption @server=N'XXXX', @optname=N'dist', @optvalue=N'true'
GO

EXEC sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO

 

удаление кеш 1с у всех пользователей + чиста темп на терминальном сервере

SetLocal EnableExtensions

 

For /F "Tokens=2*" %%I In ('Reg Query "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ProfileList" /V ProfilesDirectory') Do Set Profiles=%%J

Call Set Profiles=%Profiles%

 

For /F "Delims=" %%I In ('Dir /B /AD-S-H "%Profiles%" ^| FindStr /V /B /I /C:"All Users"') Do (

  Del /F /S /Q "%Profiles%\%%I\Application Data\1C\1Cv82\*.*"

  Del /F /S /Q "%Profiles%\%%I\Local Settings\Application Data\1C\1Cv82\*.*"

 

  Del /F /S /Q "%Profiles%\%%I\Local Settings\Temp\*.*"

  Del /F /S /Q "%Profiles%\%%I\Local Settings\Temporary Internet Files\*.*"

 

  Del /F /S /Q "%Profiles%\%%I\AppData\Local\Temp\*.*"

  Del /F /S /Q "%Profiles%\%%I\AppData\Local\Microsoft\Windows\Temporary Internet Files\*.*"

 

)

 

Узнать sid у пользователя и Добавить пользователя с sid

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name 


    FROM sys.server_principals  p


    where name in ('sa','wmsadmin')
CREATE LOGIN [имя пользователя] WITH PASSWORD=N'пароль пользователя',sid=сид пользователя, DEFAULT_DATABASE=[WMPRD], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

 

Узнать версию sql запрос в самом sql

 

   SELECT


   SERVERPROPERTY('Edition') AS 'Edition',


   SERVERPROPERTY('ProductVersion') AS 'ProductVersion',


   SERVERPROPERTY('ProductLevel') AS 'ProductLevel',


   SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',


   SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'


   GO

 

Как узнать размер таблиц в БД sql. Узнать размер индексов в mssql.

 

DBCC UPDATEUSAGE (0);
CREATE TABLE #t(
[имя таблицы] varchar(255)
,[строк] varchar(255)
,[зарезервировано] varchar(255)
,[всего данных] varchar(255)
,[размер индексов] varchar(255)
,[свободно] varchar(255)
);
 
INSERT INTO #t
exec sp_msforeachtable N'exec sp_spaceused ''?''';
 
SELECT * FROM #t ORDER BY CONVERT(bigint, REPLACE([всего данных], ' KB', '')) DESC;
 
DROP TABLE #t;
DECLARE @TableInfo TABLE (
    table_name sysname,
    row_count int,
    reserved_size_kb nvarchar(50),
    data_size_kb nvarchar(50),
    index_size_kb nvarchar(50),
    unused_size_kb nvarchar(50)
)
 
INSERT INTO @TableInfo
EXEC sp_MSforeachtable 'sp_spaceused ''?'''
 
UPDATE @TableInfo 
SET 
    data_size_kb     = replace(data_size_kb, 'KB', ''),
    reserved_size_kb = replace(reserved_size_kb, 'KB', ''),
    index_size_kb    = replace(index_size_kb, 'KB', ''),
    unused_size_kb   = replace(unused_size_kb, 'KB', '')
 
SELECT *, 
    reserved_size_kb/1024 AS reserved_size_mb, 
    data_size_kb/1024
… Читать далее