-------------------------- db growth plan query
SELECT *
FROM (SELECT @@servername [ServerName],[DbName],YEAR(ObsvDate) [Year],
convert(varchar(10),ObsvDate,121) [Date],
SUM(FileFreeSpaceMB) [FileFreeSpaceMB]
FROM [DBAdmin].[dbo].[DBA_Monitor_DB_Growth]
GROUP BY [ServerName],[DbName],YEAR(ObsvDate),
convert(varchar(10),ObsvDate,121)) AS MontlySalesData
PIVOT( SUM([FileFreeSpaceMB])
FOR [Date] IN ([2017-06-01],[2017-06-02],[2017-06-03],[2017-06-04],[2017-06-05],
[2017-06-06],[2017-06-07],[2017-06-08],[2017-06-09],[2017-06-10],[2017-06-11],
[2017-06-12],[2017-06-13],[2017-06-14],[2017-06-15],[2017-06-16],[2017-06-17],
[2017-06-18],[2017-06-19],[2017-06-20],[2017-06-21],[2017-06-22],[2017-06-23],
[2017-06-24],[2017-06-25],[2017-06-26],[2017-06-27],[2017-06-28],[2017-06-29],[2017-06-30],[2017-01-31]
)) AS MNamePivot
SELECT *
FROM (SELECT @@servername [ServerName],YEAR(ObsvDate) [Year],[DbName],
Datename(Month,ObsvDate) [Month],
SUM(FileFreeSpaceMB) [FileFreeSpaceMB]
FROM [DBAdmin].[dbo].[DBA_Monitor_DB_Growth]
GROUP BY [ServerName],[DbName],YEAR(ObsvDate),
Datename(Month,ObsvDate)) AS MontlySalesData
PIVOT( SUM([FileFreeSpaceMB])
FOR Month IN ([January],[February],[March],[April],[May],
[June],[July],[August],[September],[October],[November],
[December])) AS MNamePivot
----------------to know server name----------------
use master
select getdate() 'Today Date and Time'
Select @@version as Version
SELECT
@@SERVERNAME AS ServerName,SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',
SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'
---------------------------To get dbname and logicalname and physical name path--------------------------
Select sd.name as DatabaseName,sm.Name as logicalname,physical_name from sys.master_files sm
join sys.databases sd on sm.database_id=sd.database_id
----------------------------------------reset-sql-server-password-in-single-user-mode----------
https://www.top-password.com/knowledge/reset-sql-server-password-in-single-user-mode.html
---------------------------------------T SQL Script to find Total DB size in MB and GB and Total Database Count
Create Table #DBDETAILS(Name Char(100),db_size char(30),owner char(100),dbid int,created char(30),
status nvarchar(max),comptlevel int)
Insert into #DBDETAILS
Exec master.dbo.sp_helpdb
Create Table #DBDETAILScount(Name Char(100),db_int float)
insert into #DBDETAILScount
Select name,left(db_size,10) db_int from #DBDETAILS
Create Table #DBCOUNTGB(dbsum float,[dbcount] int)
insert into #DBCOUNTGB
Select sum(db_int)[dbsum],count(name) [dbcount]
from #DBDETAILScount
select @@servername as ServerName,Round([dbsum],0) [Total DB Size in MB],Round([dbsum]/1024,0) as [Total DB Size in GB],dbcount as [db count] from #DBCOUNTGB
drop table #DBDETAILS
drop table #DBDETAILScount
drop table #DBCOUNTGB
----------------------------------downgrading-enterprise-to-standard-edition Copy db path and check features ---------
SELECT * FROM sys.dm_db_persisted_sku_features
I've tackled this in the past by doing the following:
SELECT sysDB.database_id
,sysDB.NAME AS 'Database Name'
,syslogin.NAME AS 'DB Owner'
,sysDB.*
FROM sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid
SELECT *
FROM sys.dm_server_services
DECLARE @DefaultData NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData'
,@DefaultData OUTPUT
DECLARE @DefaultLog NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultLog'
,@DefaultLog OUTPUT
DECLARE @DefaultBackup NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'BackupDirectory'
,@DefaultBackup OUTPUT
DECLARE @MasterData NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg0'
,@MasterData OUTPUT
SELECT @MasterData = substring(@MasterData, 3, 255)
SELECT @MasterData = substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))
DECLARE @MasterLog NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg2'
,@MasterLog OUTPUT
SELECT @MasterLog = substring(@MasterLog, 3, 255)
SELECT @MasterLog = substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
SELECT isnull(@DefaultData, @MasterData) DefaultData
,isnull(@DefaultLog, @MasterLog) DefaultLog
,isnull(@DefaultBackup, @MasterLog) DefaultBackup
-----------------------------------------------------------------Time one hour Query-------------
select Distinct historyTime from [dbo].[DO_XMLhistory]
where convert(varchar(10),HISTORYTIME,121) BETWEEN '2017-06-21' AND '2017-06-28'
ORDER BY historyTime ASC
SELECT *
FROM (SELECT CAST(historyTime AS DATE) [Date],
DATEPART(hour,historyTime) [Hour], Count(1) [Sales Count]
FROM [dbo].[DO_XMLhistory]
GROUP BY CAST(historyTime AS DATE),
DATEPART(hour,historyTime)) AS HourlySalesData
PIVOT( SUM([Sales Count])
FOR [Date] IN ([2017-06-21],[2017-06-22],[2017-06-23],[2017-06-24],[2017-06-25],
[2017-06-26],[2017-06-27],[2017-06-28]
)) AS DatePivot
--------------------------------------------------------------- to get records hourly basis
declare @DOdate datetime
declare @CTdate datetime
declare @Cvalue int
set @CTdate =getdate()
set @DOdate=(select max(historyTime) from [dbo].[CO_XMLhistory])
select @CTdate AS Currentdate
select @DOdate as Lastupdatesince
set @Cvalue =(Select DATEDIFF(mi,@DOdate,@CTdate))
SELECT @Cvalue
IF @Cvalue>15
BEGIN
Declare @emailSubject VARCHAR(100) ,@Body varchar(100),@sub varchar(1000)
set @sub='Server'+space(1)+@@servername+space(1)+'DB of'+space(1)+db_name()+space(1)+'CO_XMLhistory table did not have any update Since ' +cast(@Cvalue as varchar) + space(2)+ 'Minutes'
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>CO_XMLhistory table</H1>' +
N'<table border="1">' +
N'<tr><th>ID</th><th>OBORNO</th>' +
N'<th>TimeStmp</th><th>Queuetime</th>' +
N'<th>HistoryTime</th></tr>' +
CAST ( ( SELECT TOP 1 td = Id, '',
td = OBORNO, '',
td = TimeStmp, '',
td = ISNULL(CONVERT(varchar, [Queuetime], 100) , '' ),'' , '',
td = ISNULL(CONVERT(varchar, [historytime], 100) , '' ),''
FROM M3_LEAN_TMS.[dbo].[CO_XMLhistory]
ORDER BY Queuetime DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='MHCommandCentre@cognizant.com;ASHOK.KUMAR4@cognizant.com;Ganesh.Kumar9@cognizant.com;SriBalaji.Sekar@cognizant.com',
@profile_name = 'DBAMailAccount',
@subject = @sub,
@body = @tableHTML,
@body_format = 'HTML'
END
ELSE
BEGIN
SELECT 'There no issue with CO_XMLhistory table'
END
----------------------------------------------------------------------To Detect Compatibility level issue:
DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)
DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))
DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BY O.Name, C.colid
Open C
FETCH NEXT FROM c INTO @ProcName, @Text
SET @sql=@Text
SET @ProcName1=@ProcName
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM c INTO @ProcName, @Text
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
BEGIN TRY
EXEC(@sql) -- try to create the proc
END TRY
BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH
print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName
END
END
CLOSE c
DEALLOCATE c
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
SELECT * FROM @T
where ErrorMessage not like '%There is already an object named%'
SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'
ALTER DATABASE dbname SET SINGLE_USER
ALTER DATABASE dbname
SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE dbname SET MULTI_USER
select name, compatibility_level
from sys.databases
To check object info:
select * from sys.triggers
select * from sys.views
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'SET' + '%'
and OBJECT_NAME(OBJECT_ID) not like '%sp_%'
AND OBJECT_NAME(OBJECT_ID) not like '%dt_%'
GO
SELECT
CASE TYPE
WHEN 'U'
THEN 'User Defined Tables'
WHEN 'P'
THEN 'Stored Procedures'
WHEN 'PC'
THEN 'CLR Stored Procedures'
WHEN 'X'
THEN 'Extended Stored Procedures'
WHEN 'V'
THEN 'Views'
WHEN 'TF'
THEN 'SQL_TABLE_VALUED_FUNCTION'
WHEN 'IF'
THEN 'INLINE_TABLE_VALUED_FUNCTION'
WHEN 'FN'
THEN 'SQL_SCALAR_FUNCTION'
END as 'Objects',
COUNT(*) [Objects Count]
FROM SYS.OBJECTS
WHERE TYPE IN ('U','P', 'PC','V','TF','IF','FN')
GROUP BY TYPE
order by Objects
compute sum(count(*))
To check dynamic SP Used or not
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%exec%' OR c.TEXT LIKE '%execute%' or c.text LIKE '%sp_executesql %'
---------------------------------------------Full text catalog----------------
SELECT
t.name AS TableName,
c.name AS FTCatalogName ,
i.name AS UniqueIdxName,
cl.name AS ColumnName,
cdt.name AS DataTypeColumnName
FROM
sys.tables t
INNER JOIN
sys.fulltext_indexes fi
ON
t.[object_id] = fi.[object_id]
INNER JOIN
sys.fulltext_index_columns ic
ON
ic.[object_id] = t.[object_id]
INNER JOIN
sys.columns cl
ON
ic.column_id = cl.column_id
AND ic.[object_id] = cl.[object_id]
INNER JOIN
sys.fulltext_catalogs c
ON
fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
sys.indexes i
ON
fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id]
LEFT JOIN
sys.columns cdt
ON
ic.type_column_id = cdt.column_id
AND fi.object_id = cdt.object_id;
-------------------------------Complete server DB info-------------
SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'
------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON;
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;
-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH (NOLOCK)
INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
server_permissions.state_desc ,
server_permissions.permission_name;
-------------sp_help_revlogin-------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--EXEC sp_help_revlogin
--------------------------------------Table Row count----------
SELECT @@servername as servername,T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
------------------------------------------------Log shipping disable bulk------------------------
Kindly verify and check before do
1)Check the Secondary and Primary server which involve log shipping:
SELECT secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
from msdb.dbo.log_shipping_monitor_secondary
you can modify as below to use inside cursor
SELECT
secondary_database
from msdb.dbo.log_shipping_monitor_secondary
where primary_server= NTSM770TEST01A
2) Disable job at bulk kindly verify before do
Select * from MSDB.dbo.sysjobs
WHERE [Name] LIKE 'LSBackup_servername%';
Select * from MSDB.dbo.sysjobs
WHERE [Name] LIKE 'LSCopy_servername%';
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'LSBackup_servername%';
GO
3) Cursor to make secondary database online
USE master
GO
-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)
-- Declare a cursor to iterate through the list of databases
DECLARE db_recovery_cursor CURSOR FOR
SELECT
secondary_database
from msdb.dbo.log_shipping_monitor_secondary
where primary_server= ‘Servername’------give the primary server name
Open the cursor
OPEN db_recovery_cursor
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> '<RECOVERYMODEL>' and @dbName <> 'tempdb' BEGIN
-- create the alter database command for each database
SET @cmd = 'RESTORE DATABASE "' + @dbname + '" WITH RECOVERY '
--EXEC(@cmd)
PRINT @cmd--------------------------------------kindly check with this statement
end
FETCH NEXT FROM db_recovery_cursor INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor
-------------------------------Table row for all count--------------------------------------
SELECT T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
--------------------------------------------Sys histor with owner name--------------
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_id] AS [JobStartStepNo]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]
, [sSCH].[schedule_uid] AS [JobScheduleID]
, [sSCH].[name] AS [JobScheduleName]
, CASE [sJOB].[delete_level]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On Success'
WHEN 2 THEN 'On Failure'
WHEN 3 THEN 'On Completion'
END AS [JobDeletionCriterion]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
where [sJOB].[name]='Full Backups.fullbackup'
ORDER BY [JobName]
select Distinct
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',suser_sname(owner_sid)[Job owner],owner_sid,*
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
LEFT JOIN master.dbo.syslogins L ON j.owner_sid = L.sid
where j.enabled = 1 --Only Enabled Jobs
and j.name like '%full%'
order by JobName, RunDateTime desc
-----------------job name modified------------------------
select getdate() as servertime
SELECT @@servername as servername,J.name AS Job_Name
, L.name AS Job_Owner,owner_sid,J.date_created,J.date_modified
FROM msdb.dbo.sysjobs_view J
LEFT JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid
where J.name like '%full%'
--------to get missing index details-------------
SELECT Distinct
dm_mid.database_id AS DatabaseID,db_name(database_id) dbname,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
-------------To get missing index in all db at single shot----------
EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '
-----------------Backup progress ------------------
SELECT command,db_name(database_id)DB_Name,percent_complete,total_elapsed_time, estimated_completion_time, start_time
,estimated_completion_time/60 Estimate_Remaining_time_in_sec,estimated_completion_time/60/60 Estimate_Remaining_time_in_Mint
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE')
-------------------------How to Check Index Fragmentation on Indexes in a Database
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
The following table summarizes when to use each one:
Reference Values (in %) Action SQL statement
avg_fragmentation_in_percent > 5 AND < 30 Reorganize Index ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30 Rebuild Index ALTER INDEX REBUILD
--------------log reuse wait desc----------------------
select log_reuse_wait_desc,* from sys.databases
where log_reuse_wait_desc <>'NOTHING'
----------------to check open connections on tempdb
To find the open spid in tempdb
Select * from sys.sysprocesses where dbid = 2
----------READERRORLOG---------------------------------------------------------
DECLARE @T table(LogDate datetime,Processinfo char(10),Text Nvarchar(Max))
Insert into @T
EXEC XP_READERRORLOG
Select TOP 50 * from @T
Order by LogDate desc
--------------------------------------Replication status
DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)
DECLARE @pubname VARCHAR(100)
CREATE TABLE #replmonitor(status INT NULL,warning INT NULL,subscriber sysname NULL,subscriber_db sysname NULL,publisher_db sysname NULL,
publication sysname NULL,publication_type INT NULL,subtype INT NULL,latency INT NULL,latencythreshold INT NULL,agentnotrunning INT NULL,
agentnotrunningthreshold INT NULL,timetoexpiration INT NULL,expirationthreshold INT NULL,last_distsync DATETIME,
distribution_agentname sysname NULL,mergeagentname sysname NULL,mergesubscriptionfriendlyname sysname NULL,mergeagentlocation sysname NULL,
mergeconnectiontype INT NULL,mergePerformance INT NULL,mergerunspeed FLOAT,mergerunduration INT NULL,monitorranking INT NULL,
distributionagentjobid BINARY(16),mergeagentjobid BINARY(16),distributionagentid INT NULL,distributionagentprofileid INT NULL,
mergeagentid INT NULL,mergeagentprofileid INT NULL,logreaderagentname VARCHAR(100))
EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher = ''
, @publisher_db = @pub_db
, @publication = @pubname
, @publication_type = 0
DECLARE replmonitor CURSOR FOR
SELECT b.srvname,a.publisher_db,a.publication
FROM distribution.dbo.MSpublications a, master.dbo.sysservers b
WHERE a.publisher_id=b.srvid
OPEN replmonitor
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #replmonitor
EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher = @srvname
, @publisher_db = @pub_db
, @publication = @pubname
, @publication_type = 0
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
END
CLOSE replmonitor
DEALLOCATE replmonitor
SELECT getdate() Date,@@servername as 'Publisher server',publication,publisher_db,subscriber,subscriber_db,
CASE publication_type WHEN 0 THEN 'Transactional publication'
WHEN 1 THEN 'Snapshot publication'
WHEN 2 THEN 'Merge publication'
ELSE 'Not Known' END as 'Replication Type',
CASE subtype WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
ELSE 'Not Known' END as 'Method',
CASE status WHEN 1 THEN 'Started'
WHEN 2 THEN 'Succeeded'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retrying'
WHEN 6 THEN 'Failed'
ELSE 'Not Known' END as 'Agent Status',
latency as 'Latency in seconds',
distribution_agentname,'DistributorStatus'= CASE WHEN (DATEDIFF(hh,last_distsync,GETDATE())>1) THEN 'Distributor has not executed more than n hour'
ELSE 'Distributor running fine' END
FROM #replmonitor
DROP TABLE #replmonitor
------------TDE--------
Script
To Create Master key @Primary
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
To Open Master key @Primary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
USE master
GO
To see Master key @Primary
use master
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date
from sys.symmetric_keys
To Backup Master key @Primary
use master
BACKUP MASTER KEY TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Create Certificate @Primary
use master
CREATE CERTIFICATE ATTL_Logshipping_TDE WITH SUBJECT = 'ATTL_Logshipping_TDE'
To Backup Certificate @Primary
use master
BACKUP CERTIFICATE ATTL_Logshipping_TDE
TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY
(
FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO
To see Master key info @Primary
USE master
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date
FROM sys.certificates where name = 'ATTL_Logshipping_TDE'
GO
To Create Database Encryption @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ATTL_Logshipping_TDE
GO
To Enable Encryption on Database @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
ALTER DATABASE [ALL_Test_TDE_LogShipping]
SET ENCRYPTION ON
GO
To see Encryption status @Primary
use master
SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END
FROM sys.dm_database_encryption_keys
To Backup Full Backup @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP DATABASE [ALL_Test_TDE_LogShipping] TO DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_bk.bak'
WITH NOFORMAT, NOINIT, NAME = N'ALL_Test_TDE_LogShipping-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
To Backup Log Backup @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP LOG [ALL_Test_TDE_LogShipping] TO DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_TLBK.trn'
WITH NOFORMAT, NOINIT, NAME = N'ALL_Test_TDE_LogShipping-Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Destination:
To see Symmetric Key file info @Secondary
USE master
GO
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date
from sys.symmetric_keys
To Restore Master key @Secondary
USE master
RESTORE MASTER KEY
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
DECRYPTION BY PASSWORD = 'Ws@2012'
ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Open Master key @Secondary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
To Create Certificate key @Secondary which we created and backedup on Primary
USE master
CREATE CERTIFICATE ATTL_Logshipping_TDE
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY (FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
DECRYPTION BY PASSWORD = 'Ws@2012');
GO
To see Backup file info @ primary
USE master
SELECT distinct
s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
msdb..backupset AS s INNER JOIN
msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
(s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
s.backup_finish_date DESC;
-------------you can cycle the error log so it will create a new one with the following command
Exec Sp_Cycle_Errorlog
----Monitor Error log size and location
Declare @ServerErrorLog TABLE
(
Archive INT,
dt DATETIME,
FileSize INT
)
INSERT INTO @ServerErrorLog
Exec xp_enumerrorlogs
SELECT @@servername as ServerName,Archive, FileSize/1024/1024 AS [Error Log Filef size (MB)],SUM(FileSize/1024/1024)[Size in MB]
FROM @ServerErrorLog
Group by Archive,FileSize/1024/1024
order by Archive,FileSize/1024/1024
COMPUTE SUM(FileSize/1024/1024)
Exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
-------------------------When was database /db went offline----
select *
from sys.messages
where language_id = 1033
and text like '%setting database option%for database%';
This was the message in event viewer
Event ID: 5084
-------------------------------Restore Database without data only Schema
/* PART 1: Restore the backup to the new location */
RESTORE DATABASE [TargetDB]
FROM DISK = N'D:\backup.bak' WITH FILE = 1,
MOVE N'OriginalDB' TO N'D:\sql data\TargetDB.mdf',
MOVE N'OriginalDB' TO N'C:\SQL Data\TargetDB_1.ldf',
NOUNLOAD, STATS = 33
GO
/* PART 2: Delete all tables' data in the migration testing target */
PRINT N'Clearing [TargetDB]'
USE [TargetDB]
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- disable all constraints
EXEC sp_MSForEachTable "DELETE FROM ?" -- delete data in all tables
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" -- enable all constraints
----------------------------------------------------------------------------------------
-- BLANK DATABASE COPY CREATED, READY FOR TESTING
-----------------TEMPDB shrink
DBCC SHRINKFILE(Databasefileid,Put size in MB we need to shrink)---------------------Shrink Tempdb
We should need to put 30 % of original Tempdb size
Example:
DBCC SHRINKFILE(2,15360)---------------------Shrink Tempdb
Before that check it below info on tempdb
dbcc sqlperf(logspace)
select log_reuse_wait_desc,* from sys.databases
sp_helpdb TEMPDB
SELECT *
FROM (SELECT @@servername [ServerName],[DbName],YEAR(ObsvDate) [Year],
convert(varchar(10),ObsvDate,121) [Date],
SUM(FileFreeSpaceMB) [FileFreeSpaceMB]
FROM [DBAdmin].[dbo].[DBA_Monitor_DB_Growth]
GROUP BY [ServerName],[DbName],YEAR(ObsvDate),
convert(varchar(10),ObsvDate,121)) AS MontlySalesData
PIVOT( SUM([FileFreeSpaceMB])
FOR [Date] IN ([2017-06-01],[2017-06-02],[2017-06-03],[2017-06-04],[2017-06-05],
[2017-06-06],[2017-06-07],[2017-06-08],[2017-06-09],[2017-06-10],[2017-06-11],
[2017-06-12],[2017-06-13],[2017-06-14],[2017-06-15],[2017-06-16],[2017-06-17],
[2017-06-18],[2017-06-19],[2017-06-20],[2017-06-21],[2017-06-22],[2017-06-23],
[2017-06-24],[2017-06-25],[2017-06-26],[2017-06-27],[2017-06-28],[2017-06-29],[2017-06-30],[2017-01-31]
)) AS MNamePivot
SELECT *
FROM (SELECT @@servername [ServerName],YEAR(ObsvDate) [Year],[DbName],
Datename(Month,ObsvDate) [Month],
SUM(FileFreeSpaceMB) [FileFreeSpaceMB]
FROM [DBAdmin].[dbo].[DBA_Monitor_DB_Growth]
GROUP BY [ServerName],[DbName],YEAR(ObsvDate),
Datename(Month,ObsvDate)) AS MontlySalesData
PIVOT( SUM([FileFreeSpaceMB])
FOR Month IN ([January],[February],[March],[April],[May],
[June],[July],[August],[September],[October],[November],
[December])) AS MNamePivot
----------------to know server name----------------
use master
select getdate() 'Today Date and Time'
Select @@version as Version
SELECT
@@SERVERNAME AS ServerName,SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',
SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion'
---------------------------To get dbname and logicalname and physical name path--------------------------
Select sd.name as DatabaseName,sm.Name as logicalname,physical_name from sys.master_files sm
join sys.databases sd on sm.database_id=sd.database_id
----------------------------------------reset-sql-server-password-in-single-user-mode----------
https://www.top-password.com/knowledge/reset-sql-server-password-in-single-user-mode.html
---------------------------------------T SQL Script to find Total DB size in MB and GB and Total Database Count
Create Table #DBDETAILS(Name Char(100),db_size char(30),owner char(100),dbid int,created char(30),
status nvarchar(max),comptlevel int)
Insert into #DBDETAILS
Exec master.dbo.sp_helpdb
Create Table #DBDETAILScount(Name Char(100),db_int float)
insert into #DBDETAILScount
Select name,left(db_size,10) db_int from #DBDETAILS
Create Table #DBCOUNTGB(dbsum float,[dbcount] int)
insert into #DBCOUNTGB
Select sum(db_int)[dbsum],count(name) [dbcount]
from #DBDETAILScount
select @@servername as ServerName,Round([dbsum],0) [Total DB Size in MB],Round([dbsum]/1024,0) as [Total DB Size in GB],dbcount as [db count] from #DBCOUNTGB
drop table #DBDETAILS
drop table #DBDETAILScount
drop table #DBCOUNTGB
----------------------------------downgrading-enterprise-to-standard-edition Copy db path and check features ---------
SELECT * FROM sys.dm_db_persisted_sku_features
I've tackled this in the past by doing the following:
SELECT sysDB.database_id
,sysDB.NAME AS 'Database Name'
,syslogin.NAME AS 'DB Owner'
,sysDB.*
FROM sys.databases sysDB
INNER JOIN sys.syslogins syslogin ON sysDB.owner_sid = syslogin.sid
SELECT *
FROM sys.dm_server_services
DECLARE @DefaultData NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultData'
,@DefaultData OUTPUT
DECLARE @DefaultLog NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'DefaultLog'
,@DefaultLog OUTPUT
DECLARE @DefaultBackup NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer'
,N'BackupDirectory'
,@DefaultBackup OUTPUT
DECLARE @MasterData NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg0'
,@MasterData OUTPUT
SELECT @MasterData = substring(@MasterData, 3, 255)
SELECT @MasterData = substring(@MasterData, 1, len(@MasterData) - charindex('\', reverse(@MasterData)))
DECLARE @MasterLog NVARCHAR(512)
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
,N'Software\Microsoft\MSSQLServer\MSSQLServer\Parameters'
,N'SqlArg2'
,@MasterLog OUTPUT
SELECT @MasterLog = substring(@MasterLog, 3, 255)
SELECT @MasterLog = substring(@MasterLog, 1, len(@MasterLog) - charindex('\', reverse(@MasterLog)))
SELECT isnull(@DefaultData, @MasterData) DefaultData
,isnull(@DefaultLog, @MasterLog) DefaultLog
,isnull(@DefaultBackup, @MasterLog) DefaultBackup
-----------------------------------------------------------------Time one hour Query-------------
select Distinct historyTime from [dbo].[DO_XMLhistory]
where convert(varchar(10),HISTORYTIME,121) BETWEEN '2017-06-21' AND '2017-06-28'
ORDER BY historyTime ASC
SELECT *
FROM (SELECT CAST(historyTime AS DATE) [Date],
DATEPART(hour,historyTime) [Hour], Count(1) [Sales Count]
FROM [dbo].[DO_XMLhistory]
GROUP BY CAST(historyTime AS DATE),
DATEPART(hour,historyTime)) AS HourlySalesData
PIVOT( SUM([Sales Count])
FOR [Date] IN ([2017-06-21],[2017-06-22],[2017-06-23],[2017-06-24],[2017-06-25],
[2017-06-26],[2017-06-27],[2017-06-28]
)) AS DatePivot
--------------------------------------------------------------- to get records hourly basis
declare @DOdate datetime
declare @CTdate datetime
declare @Cvalue int
set @CTdate =getdate()
set @DOdate=(select max(historyTime) from [dbo].[CO_XMLhistory])
select @CTdate AS Currentdate
select @DOdate as Lastupdatesince
set @Cvalue =(Select DATEDIFF(mi,@DOdate,@CTdate))
SELECT @Cvalue
IF @Cvalue>15
BEGIN
Declare @emailSubject VARCHAR(100) ,@Body varchar(100),@sub varchar(1000)
set @sub='Server'+space(1)+@@servername+space(1)+'DB of'+space(1)+db_name()+space(1)+'CO_XMLhistory table did not have any update Since ' +cast(@Cvalue as varchar) + space(2)+ 'Minutes'
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>CO_XMLhistory table</H1>' +
N'<table border="1">' +
N'<tr><th>ID</th><th>OBORNO</th>' +
N'<th>TimeStmp</th><th>Queuetime</th>' +
N'<th>HistoryTime</th></tr>' +
CAST ( ( SELECT TOP 1 td = Id, '',
td = OBORNO, '',
td = TimeStmp, '',
td = ISNULL(CONVERT(varchar, [Queuetime], 100) , '' ),'' , '',
td = ISNULL(CONVERT(varchar, [historytime], 100) , '' ),''
FROM M3_LEAN_TMS.[dbo].[CO_XMLhistory]
ORDER BY Queuetime DESC
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@recipients='MHCommandCentre@cognizant.com;ASHOK.KUMAR4@cognizant.com;Ganesh.Kumar9@cognizant.com;SriBalaji.Sekar@cognizant.com',
@profile_name = 'DBAMailAccount',
@subject = @sub,
@body = @tableHTML,
@body_format = 'HTML'
END
ELSE
BEGIN
SELECT 'There no issue with CO_XMLhistory table'
END
----------------------------------------------------------------------To Detect Compatibility level issue:
DECLARE @sql VARCHAR(max),
@Text VARCHAR(max),
@ProcName VARCHAR(200),
@ProcName1 VARCHAR(200)
DECLARE @T TABLE (ProcName VARCHAR(200), sql VARCHAR(max), ErrorMessage VARCHAR(4000))
DECLARE c Cursor FOR
SELECT O.Name, C.Text
FROM sysobjects O
JOIN syscomments C ON o.ID=C.ID
WHERE O.XType IN ('P','TF','FN')
and C.text IS NOT NULL
ORDER BY O.Name, C.colid
Open C
FETCH NEXT FROM c INTO @ProcName, @Text
SET @sql=@Text
SET @ProcName1=@ProcName
WHILE @@FETCH_STATUS = 0 BEGIN
FETCH NEXT FROM c INTO @ProcName, @Text
IF @@FETCH_STATUS = 0 AND @ProcName1=@ProcName BEGIN
SET @sql=@sql+@Text
END ELSE BEGIN
SET @sql = REPLACE(@sql, @ProcName1, 'Temp_TestProc_DeleteMe') -- change proc name
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
BEGIN TRY
EXEC(@sql) -- try to create the proc
END TRY
BEGIN CATCH
INSERT @T values (@ProcName1, @sql, ERROR_MESSAGE()) -- record procs that couldn't be created
END CATCH
print @ProcName1
SET @sql=@Text
SET @ProcName1=@ProcName
END
END
CLOSE c
DEALLOCATE c
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType='P') EXEC('DROP PROC Temp_TestProc_DeleteMe')
IF EXISTS (SELECT * FROM sysobjects WHERE Name='Temp_TestProc_DeleteMe' AND XType IN ('TF','FN')) EXEC('DROP FUNCTION Temp_TestProc_DeleteMe')
SELECT * FROM @T
where ErrorMessage not like '%There is already an object named%'
SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'
ALTER DATABASE dbname SET SINGLE_USER
ALTER DATABASE dbname
SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE dbname SET MULTI_USER
select name, compatibility_level
from sys.databases
To check object info:
select * from sys.triggers
select * from sys.views
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'SET' + '%'
and OBJECT_NAME(OBJECT_ID) not like '%sp_%'
AND OBJECT_NAME(OBJECT_ID) not like '%dt_%'
GO
SELECT
CASE TYPE
WHEN 'U'
THEN 'User Defined Tables'
WHEN 'P'
THEN 'Stored Procedures'
WHEN 'PC'
THEN 'CLR Stored Procedures'
WHEN 'X'
THEN 'Extended Stored Procedures'
WHEN 'V'
THEN 'Views'
WHEN 'TF'
THEN 'SQL_TABLE_VALUED_FUNCTION'
WHEN 'IF'
THEN 'INLINE_TABLE_VALUED_FUNCTION'
WHEN 'FN'
THEN 'SQL_SCALAR_FUNCTION'
END as 'Objects',
COUNT(*) [Objects Count]
FROM SYS.OBJECTS
WHERE TYPE IN ('U','P', 'PC','V','TF','IF','FN')
GROUP BY TYPE
order by Objects
compute sum(count(*))
To check dynamic SP Used or not
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%exec%' OR c.TEXT LIKE '%execute%' or c.text LIKE '%sp_executesql %'
---------------------------------------------Full text catalog----------------
SELECT
t.name AS TableName,
c.name AS FTCatalogName ,
i.name AS UniqueIdxName,
cl.name AS ColumnName,
cdt.name AS DataTypeColumnName
FROM
sys.tables t
INNER JOIN
sys.fulltext_indexes fi
ON
t.[object_id] = fi.[object_id]
INNER JOIN
sys.fulltext_index_columns ic
ON
ic.[object_id] = t.[object_id]
INNER JOIN
sys.columns cl
ON
ic.column_id = cl.column_id
AND ic.[object_id] = cl.[object_id]
INNER JOIN
sys.fulltext_catalogs c
ON
fi.fulltext_catalog_id = c.fulltext_catalog_id
INNER JOIN
sys.indexes i
ON
fi.unique_index_id = i.index_id
AND fi.[object_id] = i.[object_id]
LEFT JOIN
sys.columns cdt
ON
ic.type_column_id = cdt.column_id
AND fi.object_id = cdt.object_id;
-------------------------------Complete server DB info-------------
SELECT @@servername as ServerName,NAME,COMPATIBILITY_LEVEL,version_name =
CASE compatibility_level
WHEN 65 THEN 'SQL Server 6.5'
WHEN 70 THEN 'SQL Server 7.0'
WHEN 80 THEN 'SQL Server 2000'
WHEN 90 THEN 'SQL Server 2005'
WHEN 100 THEN 'SQL Server 2008/R2'
WHEN 110 THEN 'SQL Server 2012'
WHEN 120 THEN 'SQL Server 2014'
END,LOG_REUSE_WAIT_DESC,recovery_model_desc,state_desc,* from sys.databases
--where name='dbname'
------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON;
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;
-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH (NOLOCK)
INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
server_permissions.state_desc ,
server_permissions.permission_name;
-------------sp_help_revlogin-------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--EXEC sp_help_revlogin
--------------------------------------Table Row count----------
SELECT @@servername as servername,T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
------------------------------------------------Log shipping disable bulk------------------------
Kindly verify and check before do
1)Check the Secondary and Primary server which involve log shipping:
SELECT secondary_server,
secondary_database,
primary_server,
primary_database,
last_copied_file,
last_copied_date,
last_restored_file,
last_restored_date
from msdb.dbo.log_shipping_monitor_secondary
you can modify as below to use inside cursor
SELECT
secondary_database
from msdb.dbo.log_shipping_monitor_secondary
where primary_server= NTSM770TEST01A
2) Disable job at bulk kindly verify before do
Select * from MSDB.dbo.sysjobs
WHERE [Name] LIKE 'LSBackup_servername%';
Select * from MSDB.dbo.sysjobs
WHERE [Name] LIKE 'LSCopy_servername%';
USE MSDB;
GO
UPDATE MSDB.dbo.sysjobs
SET Enabled = 0
WHERE [Name] LIKE 'LSBackup_servername%';
GO
3) Cursor to make secondary database online
USE master
GO
-- Declare a variable to store the value [database name] returned by FETCH.
DECLARE @dbname sysname, @cmd varchar(1000)
-- Declare a cursor to iterate through the list of databases
DECLARE db_recovery_cursor CURSOR FOR
SELECT
secondary_database
from msdb.dbo.log_shipping_monitor_secondary
where primary_server= ‘Servername’------give the primary server name
Open the cursor
OPEN db_recovery_cursor
-- Perform the first fetch and store the value in a variable.
FETCH NEXT FROM db_recovery_cursor INTO @dbname
-- loop through cursor until no more records fetched
WHILE @@FETCH_STATUS = 0
BEGIN
IF (SELECT DATABASEPROPERTYEX(@dbname,'RECOVERY')) <> '<RECOVERYMODEL>' and @dbName <> 'tempdb' BEGIN
-- create the alter database command for each database
SET @cmd = 'RESTORE DATABASE "' + @dbname + '" WITH RECOVERY '
--EXEC(@cmd)
PRINT @cmd--------------------------------------kindly check with this statement
end
FETCH NEXT FROM db_recovery_cursor INTO @dbname
END
-- close the cursor and deallocate memory used by cursor
CLOSE db_recovery_cursor
-------------------------------Table row for all count--------------------------------------
SELECT T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
--------------------------------------------Sys histor with owner name--------------
SELECT
[sJOB].[job_id] AS [JobID]
, [sJOB].[name] AS [JobName]
, [sDBP].[name] AS [JobOwner]
, [sCAT].[name] AS [JobCategory]
, [sJOB].[description] AS [JobDescription]
, CASE [sJOB].[enabled]
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END AS [IsEnabled]
, [sJOB].[date_created] AS [JobCreatedOn]
, [sJOB].[date_modified] AS [JobLastModifiedOn]
, [sSVR].[name] AS [OriginatingServerName]
, [sJSTP].[step_id] AS [JobStartStepNo]
, [sJSTP].[step_name] AS [JobStartStepName]
, CASE
WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
ELSE 'Yes'
END AS [IsScheduled]
, [sSCH].[schedule_uid] AS [JobScheduleID]
, [sSCH].[name] AS [JobScheduleName]
, CASE [sJOB].[delete_level]
WHEN 0 THEN 'Never'
WHEN 1 THEN 'On Success'
WHEN 2 THEN 'On Failure'
WHEN 3 THEN 'On Completion'
END AS [JobDeletionCriterion]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].[sys].[servers] AS [sSVR]
ON [sJOB].[originating_server_id] = [sSVR].[server_id]
LEFT JOIN [msdb].[dbo].[syscategories] AS [sCAT]
ON [sJOB].[category_id] = [sCAT].[category_id]
LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sJSTP]
ON [sJOB].[job_id] = [sJSTP].[job_id]
AND [sJOB].[start_step_id] = [sJSTP].[step_id]
LEFT JOIN [msdb].[sys].[database_principals] AS [sDBP]
ON [sJOB].[owner_sid] = [sDBP].[sid]
LEFT JOIN [msdb].[dbo].[sysjobschedules] AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN [msdb].[dbo].[sysschedules] AS [sSCH]
ON [sJOBSCH].[schedule_id] = [sSCH].[schedule_id]
where [sJOB].[name]='Full Backups.fullbackup'
ORDER BY [JobName]
select Distinct
j.name as 'JobName',
run_date,
run_time,
msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',suser_sname(owner_sid)[Job owner],owner_sid,*
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h
ON j.job_id = h.job_id
LEFT JOIN master.dbo.syslogins L ON j.owner_sid = L.sid
where j.enabled = 1 --Only Enabled Jobs
and j.name like '%full%'
order by JobName, RunDateTime desc
-----------------job name modified------------------------
select getdate() as servertime
SELECT @@servername as servername,J.name AS Job_Name
, L.name AS Job_Owner,owner_sid,J.date_created,J.date_modified
FROM msdb.dbo.sysjobs_view J
LEFT JOIN
master.dbo.syslogins L
ON J.owner_sid = L.sid
where J.name like '%full%'
--------to get missing index details-------------
SELECT Distinct
dm_mid.database_id AS DatabaseID,db_name(database_id) dbname,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
-------------To get missing index in all db at single shot----------
EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '
-----------------Backup progress ------------------
SELECT command,db_name(database_id)DB_Name,percent_complete,total_elapsed_time, estimated_completion_time, start_time
,estimated_completion_time/60 Estimate_Remaining_time_in_sec,estimated_completion_time/60/60 Estimate_Remaining_time_in_Mint
FROM sys.dm_exec_requests
WHERE command IN ('BACKUP DATABASE')
-------------------------How to Check Index Fragmentation on Indexes in a Database
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
The following table summarizes when to use each one:
Reference Values (in %) Action SQL statement
avg_fragmentation_in_percent > 5 AND < 30 Reorganize Index ALTER INDEX REORGANIZE
avg_fragmentation_in_percent > 30 Rebuild Index ALTER INDEX REBUILD
--------------log reuse wait desc----------------------
select log_reuse_wait_desc,* from sys.databases
where log_reuse_wait_desc <>'NOTHING'
----------------to check open connections on tempdb
To find the open spid in tempdb
Select * from sys.sysprocesses where dbid = 2
----------READERRORLOG---------------------------------------------------------
DECLARE @T table(LogDate datetime,Processinfo char(10),Text Nvarchar(Max))
Insert into @T
EXEC XP_READERRORLOG
Select TOP 50 * from @T
Order by LogDate desc
--------------------------------------Replication status
DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)
DECLARE @pubname VARCHAR(100)
CREATE TABLE #replmonitor(status INT NULL,warning INT NULL,subscriber sysname NULL,subscriber_db sysname NULL,publisher_db sysname NULL,
publication sysname NULL,publication_type INT NULL,subtype INT NULL,latency INT NULL,latencythreshold INT NULL,agentnotrunning INT NULL,
agentnotrunningthreshold INT NULL,timetoexpiration INT NULL,expirationthreshold INT NULL,last_distsync DATETIME,
distribution_agentname sysname NULL,mergeagentname sysname NULL,mergesubscriptionfriendlyname sysname NULL,mergeagentlocation sysname NULL,
mergeconnectiontype INT NULL,mergePerformance INT NULL,mergerunspeed FLOAT,mergerunduration INT NULL,monitorranking INT NULL,
distributionagentjobid BINARY(16),mergeagentjobid BINARY(16),distributionagentid INT NULL,distributionagentprofileid INT NULL,
mergeagentid INT NULL,mergeagentprofileid INT NULL,logreaderagentname VARCHAR(100))
EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher = ''
, @publisher_db = @pub_db
, @publication = @pubname
, @publication_type = 0
DECLARE replmonitor CURSOR FOR
SELECT b.srvname,a.publisher_db,a.publication
FROM distribution.dbo.MSpublications a, master.dbo.sysservers b
WHERE a.publisher_id=b.srvid
OPEN replmonitor
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #replmonitor
EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher = @srvname
, @publisher_db = @pub_db
, @publication = @pubname
, @publication_type = 0
FETCH NEXT FROM replmonitor INTO @srvname,@pub_db,@pubname
END
CLOSE replmonitor
DEALLOCATE replmonitor
SELECT getdate() Date,@@servername as 'Publisher server',publication,publisher_db,subscriber,subscriber_db,
CASE publication_type WHEN 0 THEN 'Transactional publication'
WHEN 1 THEN 'Snapshot publication'
WHEN 2 THEN 'Merge publication'
ELSE 'Not Known' END as 'Replication Type',
CASE subtype WHEN 0 THEN 'Push'
WHEN 1 THEN 'Pull'
WHEN 2 THEN 'Anonymous'
ELSE 'Not Known' END as 'Method',
CASE status WHEN 1 THEN 'Started'
WHEN 2 THEN 'Succeeded'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retrying'
WHEN 6 THEN 'Failed'
ELSE 'Not Known' END as 'Agent Status',
latency as 'Latency in seconds',
distribution_agentname,'DistributorStatus'= CASE WHEN (DATEDIFF(hh,last_distsync,GETDATE())>1) THEN 'Distributor has not executed more than n hour'
ELSE 'Distributor running fine' END
FROM #replmonitor
DROP TABLE #replmonitor
------------TDE--------
Script
To Create Master key @Primary
USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='Ws@2012';
To Open Master key @Primary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
USE master
GO
To see Master key @Primary
use master
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date
from sys.symmetric_keys
To Backup Master key @Primary
use master
BACKUP MASTER KEY TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Create Certificate @Primary
use master
CREATE CERTIFICATE ATTL_Logshipping_TDE WITH SUBJECT = 'ATTL_Logshipping_TDE'
To Backup Certificate @Primary
use master
BACKUP CERTIFICATE ATTL_Logshipping_TDE
TO FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY
(
FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
ENCRYPTION BY PASSWORD = 'Ws@2012'
);
GO
To see Master key info @Primary
USE master
SELECT name, pvt_key_encryption_type ,pvt_key_encryption_type_desc ,issuer_name ,expiry_date ,start_date
FROM sys.certificates where name = 'ATTL_Logshipping_TDE'
GO
To Create Database Encryption @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ATTL_Logshipping_TDE
GO
To Enable Encryption on Database @Primary
USE [ALL_Test_TDE_LogShipping]-------------------------Put databasename here
ALTER DATABASE [ALL_Test_TDE_LogShipping]
SET ENCRYPTION ON
GO
To see Encryption status @Primary
use master
SELECT db_name(database_id), EncryptionState =
CASE encryption_state
WHEN 1 THEN 'Unencrypted'
WHEN 3 THEN 'Encrypted'
END
FROM sys.dm_database_encryption_keys
To Backup Full Backup @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP DATABASE [ALL_Test_TDE_LogShipping] TO DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_bk.bak'
WITH NOFORMAT, NOINIT, NAME = N'ALL_Test_TDE_LogShipping-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
To Backup Log Backup @Primary
use [ALL_Test_TDE_LogShipping]
BACKUP LOG [ALL_Test_TDE_LogShipping] TO DISK = N'\\USER-PC\Backup\ALL_Test_TDE_LogShipping\Backup\ALL_Test_TDE_LogShipping_TLBK.trn'
WITH NOFORMAT, NOINIT, NAME = N'ALL_Test_TDE_LogShipping-Log Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Destination:
To see Symmetric Key file info @Secondary
USE master
GO
SELECT @@servername as servername,name, key_length,key_algorithm ,algorithm_desc ,create_date
from sys.symmetric_keys
To Restore Master key @Secondary
USE master
RESTORE MASTER KEY
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\MasterKeyBack_MSSQL2012SOURCE'
DECRYPTION BY PASSWORD = 'Ws@2012'
ENCRYPTION BY PASSWORD = 'Ws@2012';
GO
To Open Master key @Secondary
USE master
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Ws@2012';
To Create Certificate key @Secondary which we created and backedup on Primary
USE master
CREATE CERTIFICATE ATTL_Logshipping_TDE
FROM FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE'
WITH PRIVATE KEY (FILE = 'F:\Backup\TDE\MSSQL2012SOURCE\ATTL_Logshipping_TDE_Private',
DECRYPTION BY PASSWORD = 'Ws@2012');
GO
To see Backup file info @ primary
USE master
SELECT distinct
s.database_name,s.backup_finish_date,y.physical_device_name,s.first_lsn
FROM
msdb..backupset AS s INNER JOIN
msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN
msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN
msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id
WHERE
(s.database_name = 'ALL_Test_TDE_LogShipping')
ORDER BY
s.backup_finish_date DESC;
-------------you can cycle the error log so it will create a new one with the following command
Exec Sp_Cycle_Errorlog
----Monitor Error log size and location
Declare @ServerErrorLog TABLE
(
Archive INT,
dt DATETIME,
FileSize INT
)
INSERT INTO @ServerErrorLog
Exec xp_enumerrorlogs
SELECT @@servername as ServerName,Archive, FileSize/1024/1024 AS [Error Log Filef size (MB)],SUM(FileSize/1024/1024)[Size in MB]
FROM @ServerErrorLog
Group by Archive,FileSize/1024/1024
order by Archive,FileSize/1024/1024
COMPUTE SUM(FileSize/1024/1024)
Exec xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
-------------------------When was database /db went offline----
select *
from sys.messages
where language_id = 1033
and text like '%setting database option%for database%';
This was the message in event viewer
Event ID: 5084
-------------------------------Restore Database without data only Schema
/* PART 1: Restore the backup to the new location */
RESTORE DATABASE [TargetDB]
FROM DISK = N'D:\backup.bak' WITH FILE = 1,
MOVE N'OriginalDB' TO N'D:\sql data\TargetDB.mdf',
MOVE N'OriginalDB' TO N'C:\SQL Data\TargetDB_1.ldf',
NOUNLOAD, STATS = 33
GO
/* PART 2: Delete all tables' data in the migration testing target */
PRINT N'Clearing [TargetDB]'
USE [TargetDB]
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- disable all constraints
EXEC sp_MSForEachTable "DELETE FROM ?" -- delete data in all tables
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" -- enable all constraints
----------------------------------------------------------------------------------------
-- BLANK DATABASE COPY CREATED, READY FOR TESTING
-----------------TEMPDB shrink
DBCC SHRINKFILE(Databasefileid,Put size in MB we need to shrink)---------------------Shrink Tempdb
We should need to put 30 % of original Tempdb size
Example:
DBCC SHRINKFILE(2,15360)---------------------Shrink Tempdb
Before that check it below info on tempdb
dbcc sqlperf(logspace)
select log_reuse_wait_desc,* from sys.databases
sp_helpdb TEMPDB
