Restore Progress Check...
==========================================================================
DECLARE @DBName VARCHAR(64) = 'ODS'
DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName
INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery completed', @DBName
SELECT TOP 1
@DBName AS [DBName]
,[LogDate]
,CASE
WHEN SUBSTRING([TEXT],10,1) = 'c'
THEN '100%'
ELSE SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4)
END AS PercentComplete
,CASE
WHEN SUBSTRING([TEXT],10,1) = 'c'
THEN 0
ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0
END AS MinutesRemaining
,CASE
WHEN SUBSTRING([TEXT],10,1) = 'c'
THEN 0
ELSE CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0
END AS HoursRemaining
,[TEXT]
FROM @ErrorLog ORDER BY CAST([LogDate] as datetime) DESC, [MinutesRemaining]
==========================================================================
Undocumented procedure
EXEC sp_MSforeachdb @command
--This query will return a listing of all tables in all databases on a SQL instance:
DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'
EXEC sp_MSforeachdb @command
--This query will return a listing of all tables in all databases on a SQL instance:
EXEC sp_MSforeachdb 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'
==========================================================================
Read Error Log
create table #t_789(Log_date datetime,Process_Info Nvarchar(1000),Txt Nvarchar(3500) )
insert into #t_789 exec sp_readerrorlog;
select * from #t_789 where Txt like '%error %';
==========================================================================
SERVER INFO...
SELECT
cpu_count AS [Number of Logical CPU]
,hyperthread_ratio
,cpu_count/hyperthread_ratio AS [Number of Physical CPU]
,physical_memory_in_bytes/1048576 AS [Total Physical Memory IN MB]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);
==========================================================================
Server Principal Name SPN
--Check Status
setspn -l domain\SVC_name
setspn -L hostname
--Un Register SNP
setspn -d MSSQLSvc/host_name.domain.com host_name
-- Register SPN
setspn -s MSSQLSvc/host_name.domain.com domain\SQL_Service_Account_Name
==========================================================================
use db_name
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;
select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
SUM (version_store_reserved_page_count)*8 as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb,
SUM (mixed_extent_page_count)*8 as mixedextent_kb
FROM sys.dm_db_file_space_usage
==========================================================================
SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid
==========================================================================
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;
==========================================================================
select * from sys.dm_exec_query_plan(0x05001000C7ACF10B40614B8E050000000000000000000000)
select * from sys.dm_exec_requests where status like '%runn%' or status like '%sus%' order by 1
==========================================================================
sqlcmd -S127.0.0.1,1434
==========================================================================
USE [Nart]
DBCC SHRINKFILE (N'Nart_Log' , 101903)
==========================================================================
USE DatabaseName
EXEC sp_change_users_login 'Report';
EXEC sp_change_users_login 'update_one', 'db_login1', 'db_login1';
==========================================================================
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
CASE msdb..backupset.type
WHEN 'D' THEN 'Database'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date
==========================================================================
Most Recent Database Backup for Each Database
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
ORDER BY
msdb.dbo.backupset.database_name
==========================================================================
SELECT
A.[Server],
A.last_db_backup_date,
B.backup_start_date,
B.expiration_date,
B.backup_size,
B.logical_device_name,
B.physical_device_name,
B.backupset_name,
B.description
FROM
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
GROUP BY
msdb.dbo.backupset.database_name
) AS A
LEFT JOIN
(
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
msdb.dbo.backupset.expiration_date,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'D'
) AS B
ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY
A.database_name
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
-- msdb.dbo.backupset.expiration_date,
cast ( msdb.dbo.backupset.backup_size/(1024*1024) as int) 'backup_size in mb',
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
msdb.dbo.backupset.name AS backupset_name,
msdb.dbo.backupset.description
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb..backupset.type = 'L' and msdb.dbo.backupset.database_name like 'EP1' and msdb.dbo.backupset.backup_start_date > getdate()-20
order by 3 desc
==========================================================================
create table #Login_Audit
(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Security Report] = '-----SQL SERVER SECURITY AUDIT Report-----','-----','-----','-----'
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Login count] = 'Total Count of Login','Windows User','SQL server User','Windows Group'
go
insert into #Login_Audit
select a,b,c,d from
(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count
(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, --for login is windows user
(select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login
(select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
go
insert into #Login_Audit (A,B,C,D)
SELECT
[sysadmin_server role] = '-- SYSADMIN SERVER ROLE ASSIGN TO---',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Sys Admin role] = 'Login name',' Type ',' Login Status ',''
go
insert into #Login_Audit (A,B,C)
SELECT a.name as Logins, a.type_desc, case a.is_disabled
when 1 then 'Disable'
when 0 then 'Enable'
End
FROM sys.server_principals a
INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id
WHERE b.role_principal_id = 3
ORDER BY a.name
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Fixed_server role] = '-- FIXED SERVER ROLE DETAILS --',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Fixed_server role] = 'ROLE name',' Members ',' Type ',''
go
insert into #Login_Audit (A,B,C)
SELECT c.name as Fixed_roleName, a.name as logins ,a.type_desc
FROM sys.server_principals a
INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id
INNER JOIN sys.server_principals c ON c.principal_id = b.role_principal_id
--WHERE a.principal_id > 250
ORDER BY c.name
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Fixed_database_Roles] = '-- FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Fixed_database_Role] = 'Database Name','Role Name','Member','Type'
go
insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]
SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc
FROM sys.database_principals a
INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id
INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name <> ''dbo''and c.is_fixed_role=1 '
go
------------ used is_fixed = 0 for non fixed database roles(need to run on each database)
insert into #Login_Audit (A,B,C,D)
SELECT
[NON_Fixed_database_Roles] = '-- NON FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Non Fixed_database role] = 'Database Name','Role Name','Member ','Type'
go
insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]
SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc
FROM sys.database_principals a
INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id
INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name <> ''dbo''and c.is_fixed_role=0 '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Server_Level_Permission] = '-- SERVER LEVEL PERMISSION DETAILS --',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Server permission] = 'Logins','Permission Type',' Permission_desc ','Status'
go
insert into #Login_Audit
SELECT b.name,a.type,a.permission_name,a.state_desc
FROM sys.server_permissions a
INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_id
--INNER JOIN sys.server_principals b ON b.principal_id = b.role_principal_id
WHERE b.name not like '%#%'
ORDER BY b.name
go
insert into #Login_Audit (A,B,C,D)
SELECT
[DATABASE_Level_Permission] = '-- DATABASE LEVEL PERMISSION DETAILS ----',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[DB permission] = 'Database Name','Login Name',' Permission ','Status'
go
insert into #Login_Audit
exec master.dbo.sp_MSforeachdb 'use [?]
SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_desc
FROM sys.database_permissions a
INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id
where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and b.name not like ''%#%'''
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Password_ Policy_Details] = '--- PASSWORD POLICY DETAILS ----',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Policy] = 'Users','type',' Policy status','Password policy status'
go
insert into #Login_Audit
SELECT a.name AS SQL_Server_Login,a.type_desc,
CASE b.is_policy_checked
WHEN 1 THEN 'Password Policy Applied'
ELSE
'Password Policy Not Applied'
END AS Password_Policy_Status,
CASE b.is_expiration_checked
WHEN 1 THEN 'Password Expiration Check Applied'
ELSE
'Password Expiration Check Not Applied'
END AS Password_Expiration_Check_Status
FROM sys.server_principals a INNER JOIN sys.sql_logins b
ON a.principal_id = b.principal_id
where a.name not like '%#%'
order by a.name
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Orphan_Login_Details] = '--- ORPHAN LOGINS ----',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[orphan logine] = 'Logins Name','ID','',''
go
insert into #Login_Audit (A,B) exec sp_validatelogins
go
insert into #Login_Audit (A,B,C,D)
SELECT
[Orphan_USERS_Details] = '--- ORPHAN USERS----',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[orphan users] = 'User Name','',' ',''
go
insert into #Login_Audit (A)
select u.name from master..syslogins l right join
sysusers u on l.sid = u.sid
where l.sid is null and issqlrole <> 1 and isapprole <> 1
and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
and u.name <> 'system_function_schema'and u.name <> 'sys')
insert into #Login_Audit (A,B,C,D)
SELECT
[Database_Owner_details] = '--- DATABASE OWENER DETAILS----',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit (A,B,C,D)
SELECT
[DB owner] = 'Database Name','Owener name',' ',''
go
insert into #Login_Audit (A,B)
select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
go
--select * from #Login_Audit where b like '%DDL%' order by 1
==========================================================================
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
CAST(ROUND(((SUM(a.total_pages) * 8) ), 2) AS NUMERIC(36, 2)) AS TotalSpaceKB
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
i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY 3 desc
SELECT i.name AS IndexName,
OBJECT_NAME(i.OBJECT_ID) AS TableName,
p.rows,
8 * a.used_pages AS 'Indexsize(KB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
where i.[OBJECT_ID] > 100 and i.name is not null
ORDER BY --OBJECT_NAME(i.OBJECT_ID),i.index_id
4 desc
==========================================================================
--dbcc sqlperf(logspace)
declare @name Nvarchar(100)
use EPM_HFM_Prod
select @name = name from sys.sysfiles where fileid =2
dbcc shrinkfile(@name,100)
use EPM_FDMEE_Prod
select @name = name from sys.sysfiles where fileid =2
dbcc shrinkfile(@name,100)
==========================================================================
SELECT
@@SERVERNAME as ServerName
,d.name as DBName
,d.database_id as DBID
,d.state_desc as DBStatus
,CASE M.mirroring_role
WHEN 2 THEN 'Mirrored'
WHEN 1 THEN 'PRINCIPAL'
ELSE 'Log Shipped'
END
as MirroringRole
,
CASE M.mirroring_state
WHEN 0 THEN 'Suspended'
WHEN 1 THEN 'Disconnected from the other partner'
WHEN 2 THEN 'Synchronizing'
WHEN 3 THEN 'Pending Failover'
WHEN 4 THEN 'Synchronized'
WHEN 5 THEN 'Failover is not possible now'
WHEN 6 THEN 'Failover is potentially possible'
ELSE 'Is Not Mirrored'
END
as MirroringState,
M.mirroring_partner_instance as 'Partner',
M.mirroring_partner_name as 'Endpoint',
M.mirroring_safety_level_desc as 'SaftyLevel',
E.state_desc as 'Endpoint State',
SUSER_SNAME(owner_sid) as DBOwner,
compatibility_level as CompatibilityLevel
from master.sys.databases d
JOIN master.sys.database_mirroring M
ON d.database_id = M.database_id,sys.database_mirroring_endpoints E
WHERE --d.database_id NOT IN (db_id('MiFile-PROD')) AND
d.state_desc <> 'OFFLINE'
AND M.mirroring_state IS NOT NULL
order by d.name
==========================================================================
use SQLsafeRepository
declare @server_name Nvarchar(500)
set @server_name = 'server_name'
select name from policies where policy_id in (
select policy_id from policies_instances_mode
where instance_id in (
select instance_id from instances where server_id in (
select server_id from servers where name like @server_name
)))
==========================================================================
select
object_schema_name(ps.object_id) as ObjectSchema,
object_name (ps.object_id) as ObjectName,
ps.object_id ObjectId,
i.name as IndexName,
ps.avg_fragmentation_in_percent,
ps.page_count
from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) ps
inner join sys.indexes i
on i.object_id = ps.object_id and
i.index_id = ps.index_id
where
avg_fragmentation_in_percent > 5 -- reorganize and rebuild
and ps.index_id > 0
order by avg_fragmentation_in_percent desc
==========================================================================
SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('TEST');
==========================================================================
Statistics for a table
SELECT * FROM sys.dm_db_stats_properties (object_id('Person.Person'), 1);
Index Info
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc
--SQL Server total memory utilization...
select
(physical_memory_in_use_kb/1024)Phy_Memory_usedby_Sqlserver_MB,
(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
(virtual_address_space_committed_kb/1024 )Total_Memory_UsedBySQLServer_MB,
process_physical_memory_low,
process_virtual_memory_low
from sys. dm_os_process_memory
==========================================================================
SELECT * FROM sys.sysprocesses WHERE open_tran = 1
==========================================================================
exec xp_logininfo 'DOMAIN\group_name','members'
==========================================================================
SELECT [ServerName]
,[InstanceName]
,[DatabaseName]
,[BackupStatus],
,[Start_Datetime]
,[End_Datetime]
,[DurationInMinutes]
,[CompressedSizeInGB]
,[DatabaseSizeInGB]
,[BackupType]
,[ActionType]
,[backup_set_name]
,[result_text]
FROM [SQLSafeRepository].[dbo].[BackupReportData]
Where BackupStatus like 'FAILED' and ActionType like 'Backup'
and Start_Datetime > GETDATE() - 2
--and BackupType like 'Full'
order by ServerName,DatabaseName
==========================================================================
SELECT
owt.session_id,
owt.exec_context_id,
owt.wait_duration_ms,
er.command,
owt.wait_type,
owt.blocking_session_id
FROM sys.dm_os_waiting_tasks owt INNER JOIN sys.dm_exec_sessions es ON
owt.session_id = es.session_id INNER JOIN sys.dm_exec_requests er
ON es.session_id = er.session_id
WHERE es.is_user_process = 1 --and es.session_id = 400
ORDER BY owt.session_id, owt.exec_context_id
==========================================================================
tempdb utilization report
SELECT
(SUM(unallocated_extent_page_count)/128) AS [Free space (MB)],
SUM(internal_object_reserved_page_count)*8/1024 AS [Internal objects (MB)],
SUM(user_object_reserved_page_count)*8/1024 AS [User objects (MB)],
SUM(version_store_reserved_page_count)*8 AS [Version store (KB)]
FROM sys.dm_db_file_space_usage
--database_id '2' represents tempdb
WHERE database_id = 2
checkpoint
DBCC DROPCLEANBUFFERS;
DBCC FREESYSTEMCACHE ('ALL');
