DETERMINE THE SIZES OF DATABASE TABLES
SELECT
t.NAME AS TableName, s.Name AS SchemaName,
p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB desc
CHECK DB SIZE
EXEC sp_spaceused;
SELECT
d.name AS ‘Database’,
m.name AS ‘File’,
m.size,
m.size * 8/1024 ‘Size (MB)’,
SUM(m.size * 8/1024) OVER (PARTITION BY d.name) AS ‘Database Total’
–,m.max_size
FROM sys.master_files m
INNER JOIN sys.databases d ON
d.database_id = m.database_id;
FIND SQL BLOCKING QUERIES
SELECT db.name DBName, tl.request_session_id, wt.blocking_session_id, OBJECT_NAME(p.OBJECT_ID) BlockedObjectName ,
tl.resource_type, h1.TEXT AS RequestingText, h2.TEXT AS BlockingTest, tl.request_mode
FROM sys.dm_tran_locks AS tl INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address INNER JOIN sys.partitions AS p
ON p.hobt_id = tl.resource_associated_entity_id INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle)
AS h1 CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
FIND RUNNING QUERIES
SELECT MID.statement AS [Database.Schema.Table],
MIC.column_id AS ColumnId,MIC.column_name AS ColumnName,
MIC.column_usage AS ColumnUsage,MIGS.user_seeks AS UserSeeks,
MIGS.user_scans AS UserScans,MIGS.last_user_seek AS LastUserSeek,
MIGS.avg_total_user_cost AS AvgQueryCostReduction,
MIGS.avg_user_impact AS AvgPctBenefit
FROM sys.dm_db_missing_index_details AS MID
CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MIG.index_handle = MID.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS
ON MIG.index_group_handle=MIGS.group_handle
ORDER BY MIGS.avg_user_impact DESC;
CHECK SERVER DISK USAGE
DECLARE @svrName VARCHAR(255)
DECLARE @sql VARCHAR(400)
DECLARE @output TABLE (line VARCHAR(255))
–by default it will take the current server name, we can the SET the server name as well
SET @svrName = @@SERVERNAME
IF CHARINDEX (”, @svrName) > 0
SET @svrName = SUBSTRING(@svrName, 1, CHARINDEX(”,@svrName)-1)
SET @sql = ‘powershell.exe -c “Get-WmiObject -ComputerName ‘ + QUOTENAME(@svrName,””) + ‘ -Class Win32_Volume -Filter ”DriveType = 3” | SELECT name,capacity,freespace | foreach{$.name+”|”+$.capacity/1048576/1024+”%”+$_.freespace/1048576/1024+”*”}”‘
–INSERTing disk name, total space and free space value in to temporary table
INSERT @output
EXEC xp_cmdshell @sql
–script to retrieve the values in MB FROM PS Script output
SELECT RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX(‘|’,line) -1))) AS drivename
,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) AS FLOAT),0) AS ‘capacity(GB)’
,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘‘,line) -1)-CHARINDEX(‘%’,line)) )) AS FLOAT),0) AS ‘freespace(GB)’
,CAST (((ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX(‘%’,line)+1,
(CHARINDEX(‘‘,line) -1)-CHARINDEX(‘%’,line)) )) AS FLOAT),0)))*100/
(ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX(‘|’,line)+1,
(CHARINDEX(‘%’,line) -1)-CHARINDEX(‘|’,line)) )) AS FLOAT),0)) AS INT) AS ‘freespace %’
FROM @output
WHERE line LIKE ‘[A-Z][:]%’
ORDER BY drivename
CHECK SERVER AND SQL INSTANCE MEMORY
SELECT [server memory] = physical_memory_kb/1024.00/1024.00 FROM sys.dm_os_sys_info;
SELECT object_name, cntr_value/1024/1024 sql_mem_gb FROM sys.dm_os_performance_counters WHERE counter_name = ‘Total Server Memory (KB)’;
SECURITY REVIEW : SYSADMIN USERS
SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
‘sysadmin’ as PermissionLevel,
[is_disabled]= CASE p.is_disabled
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END,
‘WINDOWS_LOGIN’ as [policy_checked],
‘Windows AD Policy’ as [expiration_checked]
FROM sys.server_principals p
where IS_SRVROLEMEMBER (‘sysadmin’,p.name) = 1 and p.type_desc=’WINDOWS_LOGIN’
union
SELECT ServerName=@@servername, dbname=db_name(db_id()),p.name as UserName, p.type_desc as TypeOfLogin,
‘sysadmin’ as PermissionLevel,
[is_disabled]= CASE log.is_disabled
WHEN 1 THEN ‘Yes’
WHEN 0 THEN ‘No’
END,
[policy_checked]= CASE log.is_policy_checked
WHEN 1 THEN ‘Enabled’
WHEN 0 THEN ‘Disabled’
END,
[expiration_checked] = CASE log.is_expiration_checked
WHEN 1 THEN ‘Enabled’
WHEN 0 THEN ‘Disabled’
END
FROM sys.server_principals p JOIN sys.sql_logins log
ON p.name=log.name where IS_SRVROLEMEMBER (‘sysadmin’,p.name) = 1 order by 1,2,3
ENABLE A PARAMETER WITH ADVANCED OPTIONS
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
SELECT * FROM sys.configurations where name like ‘%Agent%’
EXEC SP_CONFIGURE ‘Agent XPs’;
EXEC SP_CONFIGURE ‘Agent XPs’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘show advanced options’, 0;
GO
RECONFIGURE;
GO
BACKUP A DATABASE
BACKUP DATABASE databasename TO DISK = ‘filepath’;
BACKUP DATABASE [YourDatabaseName]
TO DISK = ‘C:\Backups\YourDatabaseName.bak’
WITH FORMAT, INIT, COMPRESSION, STATS = 10;
TO CHECK A BACKUP
RESTORE VERIFYONLY FROM DISK = ‘\\svr-dev\Backups\Mybd.bak’
RESTORE A DB IN SINGLE_USER MODE
USE [master]
ALTER DATABASE [mydb]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE [master]
RESTORE DATABASE [mydb] FROM DISK = N’E:\MSSQL\Bakups\mydb_Full_.bak’ WITH FILE = 1, MOVE N’mydb_Data’ TO N’F:\MSSQL\Data\mydb.mdf’, MOVE N’mydb_Log’ TO N’E:\MSSQL\Logs\mydb_log.ldf’, NOUNLOAD, REPLACE, STATS = 5
GO
USE master;
GO
ALTER DATABASE [mydb]
SET MULTI_USER;
GO
CHECK SYNONYMS FOR A DB
SELECT * FROM [db_name].sys.synonyms order by name;
CHECK IF DATABASE IS ENCRYPTED
select db_name(database_id), encryption_state
from sys.dm_database_encryption_keys;
SELECT
db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM
sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
DELETE AND CREATE A LINKED SERVER
—-Select
SELECT * FROM sys.Servers a
LEFT OUTER JOIN sys.linked_logins b ON b.server_id = a.server_id
LEFT OUTER JOIN sys.server_principals c ON c.principal_id = b.local_principal_id
SELECT name, data_source FROM sys.servers WHERE is_linked = 1; — linked servers effectifs
—- Drop
EXEC master.dbo.sp_dropserver
@server=N’MYDBLK_REG’
, @droplogins=’droplogins’
GO
—–Add
EXEC master.dbo.sp_addlinkedserver
@server = N’SVR_LKNAME’,
@srvproduct=N’svr-dev\prod’,
@provider=N’SQLNCLI11′,
@datasrc=N’svr-dev\Prod’
QUERY DIRECTLY OPENQUERY AND LINKED SERVER
SELECT * FROM OPENQUERY(MYDBLINK, ‘SELECT * FROM db_name.dbo.table_name’
READ DIRECTORY WITH XCOPY
Declare @Command varchar(1000)
set @command = ‘dir /o /b “\\bkpfolder\*.*”‘
print ‘Command for dir : ‘+@Command
exec master..xp_cmdshell @Command
— Grant access
GRANT exec ON xp_cmdshell TO “domain\testuser”;
REBUILD ALL INDEXES
— In a table
ALTER INDEX ALL ON dbo.tablename REBUILD
USING BEGIN AND END TRANSACTION
— — Example 1 — — —
BEGIN TRANSACTION [Tran1]
INSERT INTO [dbo].UserTestTable
VALUES(‘Jean’, ‘Marc’)
Select * from [dbo].[UserTestTable]
COMMIT TRANSACTION [Tran1]
ROLLBACK TRANSACTION [Tran1]
— — — Example 2 — — —
BEGIN TRANSACTION [Tran1]
BEGIN TRY
INSERT INTO [dbo].UserTestTable
VALUES(‘Jean’, ‘Marc’)
COMMIT TRANSACTION [Tran1]
END TRY
BEGIN CATCH
PRINT ERROR_MESSAGE()
PRINT ‘Rolling back’
ROLLBACK TRANSACTION [Tran1]
END CATCH
SCHEDULE SSIS AS JOB
Note: In the “Command line” tab, it may be necessary to add /X86 to avoid compatibility issues.
Ex: /DTS “”\MSDB<Folder to SSIS>”” /SERVER “”SVR-DEV”” /X86 /CHECKPOINTING OFF /REPORTING E
Grant access to SQL Profiler
USE Master;
GO
GRANT ALTER TRACE TO [domain\toto.titi]
GO
GRANT ACCESS TO CREATE AND MANAGE JOBS
SQL LOGIN : ENFORCE PASSWORD LENGTH
To force a password length for SQL Logins accounts, you need to modify the value of the local parameter Minimum password length in :
Start >> All Programs >> Administrative Tools >> Local Security Policy >> Security Settings >> Account Policies >> Password Policy
LIST ALL REPORTS ACCESSIBLE BY A USER
SELECT
C.Name AS ReportName,
U.UserName,
C.Path AS ReportPath,
R.RoleName AS Permission
FROM
ReportServer.dbo.Catalog C
JOIN
ReportServer.dbo.PolicyUserRole PUR ON C.PolicyID = PUR.PolicyID
JOIN
ReportServer.dbo.Roles R ON PUR.RoleID = R.RoleID
JOIN
ReportServer.dbo.Users U ON PUR.UserID = U.UserID
WHERE
C.Type = 2 — Seulement les rapports (Type 2 = Report, 1 = Dossier)
AND U.UserName like ‘%Alexandre%’ — Remplacer par le compte à vérifier
ORDER BY
C.Path;
CHECK STATISTICS ON A TABLE
SELECT
t.name AS TableName,
s.name AS StatisticName,
sp.last_updated AS LastUpdated,
sp.rows AS TotalRows,
sp.rows_sampled AS RowsSampled,
sp.modification_counter AS ModificationCounter,
CAST(sp.modification_counter AS FLOAT) * 100 / NULLIF(sp.rows, 0) AS ModificationPercentage,
sp.steps AS HistogramSteps,
CASE
WHEN s.no_recompute = 1 THEN ‘❌ NORECOMPUTE ON’
WHEN sp.rows = 0 THEN ‘⚠️ EMPTY TABLE’
WHEN sp.modification_counter > 0 AND sp.modification_counter < 500 THEN ‘🟢 Minor changes’
WHEN sp.modification_counter > sp.rows * 0.2 THEN ‘🔴 CRITICAL — Update STATISTICS’
WHEN sp.modification_counter > sp.rows * 0.1 THEN ‘🟡 WARNING — Monitor’
ELSE ‘✅ OK’
END AS Status,
s.auto_created, s.user_created, s.has_filter, s.filter_definition
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE t.name = ‘Mytable‘ — Replace table name
ORDER BY ModificationPercentage DESC;
UPDATE STATISTICS (INDEX + COLUMN) ON A TABLE
UPDATE STATISTICS dbo. Tablename’
TO RESET QUERYSTORE
ALTER DATABASE MYDB SET QUERY_STORE CLEAR;
KILL SESSIONS ID
— Replace ‘user_name’ by the SQL or Windows login
DECLARE @login_name NVARCHAR(128) = N’user_name’;
DECLARE @sql NVARCHAR(MAX) = N”;
SELECT @sql += N’KILL ‘ + CAST(s.session_id AS NVARCHAR(10)) + N’;’ + CHAR(13)
FROM sys.dm_exec_sessions s
WHERE s.login_name = @login_name
AND s.session_id <> @@SPID; — to not kill your current session
— Optional : print sql commands to run
PRINT @sql;
— Execute KILL commands
EXEC sp_executesql @sql;