How do you find out all jobs/packages owners

-- Find out all job owners
USE msdbGo
EXEC dbo.sp_help_job
===============================
select s.name,l.name from msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid
======================================
-- Find out all package owners
select s.name,l.name from msdb..sysssispackages s
left join master.sys.syslogins l on s.ownersid = l.sid

How to choose a power plan

In the Control Panel click on Power Options, which takes you to Select a power plan page. If you do not see Power Options, type the word 'power' in the Search Control Panel box and then select Choose a power plan.

Default is Balanced

For Good Performance - Select High Performance

Daily Database Restore in Report Server

Create a job in Report Server

Job Name : Daily Database_Name Resotre

Step 01(Copy Differential/Restore/Archive):

DECLARE 

@rc int,

@FileName varchar(255),

@statement nvarchar(max),

@return int

 CREATE TABLE ##BackupFiles(

                [FileName] [varchar](255) NULL,

)

 -- Copy Full

 Insert Into ##BackupFiles(FileName)

EXEC @rc = master.dbo.xp_cmdshell 'dir \\SourceServer_Name\g$\MSSQL\100\Backups\Full\Database_Name /B'

 

IF @rc <> 0

BEGIN

RAISERROR('50005 Full Copy Failed',16, -1, @@servername )

END

 Set @FileName = (Select top 1 FileName From ##BackupFiles

Order By FileName Desc)

 Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''copy \\ SourceServer_Name \g$\MSSQL\100\Backups\Full\ Database_Name \' + @FileName + ' E:\PTCRestore\ Database_Name _backup.bak /Y /V'' IF @rc <> 0 BEGIN RAISERROR(''50005 Full Copy Failed'',16, -1, @@servername ) END'

print @statement

EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Full Restore Failed',16, -1, @@servername )

END

 Delete From ##BackupFiles

 -- Copy Differential

 Insert Into ##BackupFiles(FileName)

EXEC @rc = master.dbo.xp_cmdshell 'dir \\ SourceServer_Name \g$\MSSQL\100\Backups\Differential\ Database_Name /B'

 IF @rc <> 0

BEGIN

RAISERROR('50005 Differential Copy Failed',16, -1, @@servername )

END

 Set @FileName = (Select top 1 FileName From ##BackupFiles

Order By FileName Desc)

 

Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''copy \\ SourceServer_Name \g$\MSSQL\100\Backups\Differential\ Database_Name \' + @FileName + ' E:\ Database_Name Restore\' + @FileName + ' /Y /V'' IF @rc <> 0 BEGIN RAISERROR(''50005 Differential Copy Failed'',16, -1, @@servername ) END'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Restore Failed',16, -1, @@servername )

END

 -- Restore/Archive

 exec sp_killusers 'ptc'

 IF (select datename(dw,getdate())) = 'Sunday'

BEGIN

 RESTORE DATABASE [ptc] FROM  DISK = N'E:\ Database_Name Restore\ptc_backup.BAK' WITH  FILE = 1,  MOVE N'PTC_Data' TO N'E:\PTCRestore\ptc.MDF',  MOVE N'PTC_Log' TO N'F:\ Database_Name Restore\ptc.LDF',  NOUNLOAD,REPLACE,  STATS = 10

 

END

ELSE

BEGIN

 RESTORE DATABASE [Database_Name] FROM  DISK = N'E:\ Database_Name Restore\ Database_Name _backup.BAK' WITH  FILE = 1,  MOVE N' Database_Name _Data' TO N'E:\ Database_Name Restore\ptc.MDF',  MOVE N' Database_Name _Log' TO N'F:\ Database_Name Restore\ Database_Name.LDF',  NORECOVERY,  NOUNLOAD,REPLACE,  STATS = 10

 

Set @statement = 'RESTORE DATABASE [Database_Name] FROM  DISK = N''E:\ Database_Name Restore\' + @FileName + ''' WITH  FILE = 1,  MOVE N''PTC_Data'' TO N''E:\ Database_Name Restore\ Database_Name.MDF'',  MOVE N'' Database_Name _Log'' TO N''F:\ Database_Name Restore\ Database_Name.LDF'',  NOUNLOAD, STATS = 10'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Restore Failed',16, -1, @@servername )

END

 END

 Set @statement = 'DECLARE @rc int exec @rc = master.dbo.xp_cmdshell ''move /Y E:\ Database_Name Restore\' + @FileName + ' E:\ Database_Name Restore\Archives'' IF @rc <> 0 BEGIN RAISERROR(''50005 Differential Move Failed'',16, -1, @@servername ) END'

 EXEC sp_executesql @statement,N'@return2 int OUTPUT',@return2 = @return OUTPUT

 IF @return > 0

BEGIN

RAISERROR('50005 Differential Move Failed',16, -1, @@servername )

END

 Drop Table ##BackupFiles

 ALTER DATABASE [ptc] SET READ_ONLY WITH NO_WAIT

 Step 02(CleanUp TransLog Files):

 EXEC MSDB.dbo.sp_start_job @Job_Name = 'CleanUp Translog.Subplan_1'

 Step 03(CleanUp Differential Files):

EXEC MSDB.dbo.sp_start_job @Job_Name = 'CleanUp Differential.Subplan_1'

How unlock sa account

How unlock sa account
ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

Find Current Users

Find Current Users
Select login_name,COUNT(session_id)from sys.dm_exec_sessions group by login_name

How do shrink data file / log file

Shrink Datafile/Logfile:
 DBCC SHRINKFILE(datafile logical name)
DBCC SHRINKFILE(logfile logical name)

Find out Expensive queries

SELECT TOP 5

object_name(objectID)

,[Avg CPU TIME] = total_worker_time/execution_count

,execution_count

,Plan_handle

,query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

ORDER BY total_worker_time/execution_count DESC;

-------------------------------------

SELECT

TEXT

,query_plan

,requested_memory_kb

,granted_memory_kb

,used_memory_kb

FROM sys.dm_exec_query_memory_grants emg

CROSS APPLY sys.dm_exec_sql_text(sql_handle) 

CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)

ORDER BY emg.requested_memory_kb DESC

 

Find queries that take the most CPU overall

SELECT TOP 50

    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)

    ,TextData           = qt.text

    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads

    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads

    ,Executions         = qs.execution_count

    ,TotalCPUTime       = qs.total_worker_time

    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count

    ,DiskWaitAndCPUTime = qs.total_elapsed_time

    ,MemoryWrites       = qs.max_logical_writes

    ,DateCached         = qs.creation_time

    ,DatabaseName       = DB_Name(qt.dbid)

    ,LastExecutionTime  = qs.last_execution_time

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

 ORDER BY qs.total_worker_time DESC

 

Find queries that have the highest average CPU usage:

SELECT TOP 50

    ObjectName          = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)

    ,TextData           = qt.text 

    ,DiskReads          = qs.total_physical_reads   -- The worst reads, disk reads

    ,MemoryReads        = qs.total_logical_reads    --Logical Reads are memory reads

    ,Executions         = qs.execution_count

    ,TotalCPUTime       = qs.total_worker_time

    ,AverageCPUTime     = qs.total_worker_time/qs.execution_count

    ,DiskWaitAndCPUTime = qs.total_elapsed_time

    ,MemoryWrites       = qs.max_logical_writes

    ,DateCached         = qs.creation_time

    ,DatabaseName       = DB_Name(qt.dbid)

    ,LastExecutionTime  = qs.last_execution_time

 FROM sys.dm_exec_query_stats AS qs

 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

 ORDER BY qs.total_worker_time/qs.execution_count DESC

Database Mail Information

Select * from sysmail_mailitemsSelect * from sysmail_sentitems
Select * from sysmail_unsentitems
Select * from sysmail_logSelect * from sysmail_faileditems order by send_request_date desc

SELECT recipients,sent_status, sent_date FROM msdb.dbo.sysmail_allitems
WHERE recipients = 'sharafat_tex@yahoo.com'

How can get Database ID ?

Run below one of them

select DB_ID()

sp_helpDB

How can give permission in analysis service ?

 

How do find all sys admin role users

SELECT

SP1.name AS LoginName, SP2.name AS RoleName

FROM

sys.server_role_members SRM,

sys.server_principals SP1,

 

sys.server_principals SP2

 

WHERE

SRM.member_principal_id = SP1.principal_id

AND

SRM.role_principal_id = SP2.principal_id

How can find Trace file location ?

SELECTREVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),LEN(path)))+'log.trc' 
FROM
sys.traces WHERE path LIKE '%\MSSQL\Log\log%.trc';
===========================================================

SELECT
* FROM ::fn_trace_getinfo(default)

EXEC
sp_trace_setstatus @traceid = 3, @status = 0; -- Stop/pause Trace

EXEC
sp_trace_setstatus @traceid = 3, @status = 2; -- Close trace and delete it from the

EXEC
sp_trace_setstatus @traceid = #, @status = 0; -- Stop/pause Trace

EXEC
sp_trace_setstatus @traceid = #, @status = 2; -- Close trace and delete it from the serv select

===============================


select


[Status] =

case
tr.[status]

when
1 THEN 'Running'

when
0 THEN 'Stopped'

end


,
[Default] =

case
tr.is_default

when
1 THEN 'System TRACE'

when
0 THEN 'User TRACE'

end


,
[login_name] = coalesce(se.login_name,se.login_name,'No reader spid')

,
[Trace Path] = coalesce(tr.[Path],tr.[Path],'OLE DB Client Side Trace')

from
sys.traces tr

left
join sys.dm_exec_sessions se on tr.reader_spid = se.session_id

How do you find last update statistics date

SELECTOBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name, STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM
sys.indexes A
INNER
JOIN sys.tables B ON A.object_id = B.object_id
WHERE
A.name IS NOT NULL
ORDER
BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC

Which trace flag do you need to capture deadlock evets ?

Enable Trace Flag 1204 and Trace Flag 1222
Find message  in below location:
G:\System Data and Logs\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

Find location from Startup Parameter

Sys Admin Permission required for below any activities
Enable Trace Flag

For current session level:

DBCC TRACEON(1222)
Go
DBCC TRACEON(1204)
Go
OR
DBCC TRACEON(1222, 1204)
Go
For global/instance level:

DBCC TRACEON(1222, -1)

Go
DBCC TRACEON(1204, -1)
Go
OR
DBCC TRACEON(1222,1204 -1)

Disable Trace:

DBCC TRACEOFF(1222)

DBCC TRACEOFF(1222, -1)

DBCC TRACEOFF(1222, 1204, -1)

Trace Flag on using Startup Parameter:

N.B No semicolon at end

-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf;-T1222;-T1204

Verify Trace Status:

DBCC TRACESTATUS(-1)
GO
DBCC TRACESTATUS
GO

How do you find TCP/port related information

Run the below script in sql server:
xp_readerrorlog 0, 1, N'Server is listening on'   

How do you move Available Storage/Cluster group between nodes

--cmd run as administrator
---Moving Available Storage from Node1 to Node2
cluster group "Available storage" /move
----Verify Available Storage Moved or Not-----------
cluster group "Available storage" /move: Node1Name
--------- Moving Quorum----------------
cluster group "cluster group" /move: Node1Name
-- Same thing you can do from Node2

How do you verify SQL Server is clustered or not ?

Run below anyone of scripts:

Select ServerProperty('ComputerNamePhysicalNetBIOS')
Go
Select 'IsClustered', SERVERPROPERTY('IsClustered')
Go
Select * FROM fn_virtualservernodes()
Go
Select @@VERSION
Go