SQL Queries - General Admin
USE: http://extras.sqlservercentral.com/prettifier/prettifier.aspx
for t-sql formatting
-- Query to search for a particular data type
-- Query to create login
USE MASTERGOIF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'svankaya')
CREATE LOGIN svankaya WITH PASSWORD = 'ch@ng3m3' MUST_CHANGE, CHECK_EXPIRATION = ON, DEFAULT_DATABASE=[master] ;GOIF NOT EXISTS (SELECT * FROM sys.server_role_members drm JOIN sys.server_principals mp
ON drm.member_principal_id = mp.principal_id
JOIN sys.server_principals rp
ON rp.principal_id = drm.role_principal_idWHERE rp.name = 'sysadmin' AND mp.name = 'svankaya')ALTER SERVER ROLE [sysadmin] ADD MEMBER [svankaya]
GO
USE: http://extras.sqlservercentral.com/prettifier/prettifier.aspx
for t-sql formatting
-- Query to search for a particular data type
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE data_type ='varchar'SELECT * FROM MASTER.INFORMATION_SCHEMA.COLUMNS WHERE data_type ='varchar'-- Query to create login
USE MASTERGOIF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'svankaya')
CREATE LOGIN svankaya WITH PASSWORD = 'ch@ng3m3' MUST_CHANGE, CHECK_EXPIRATION = ON, DEFAULT_DATABASE=[master] ;GOIF NOT EXISTS (SELECT * FROM sys.server_role_members drm JOIN sys.server_principals mp
ON drm.member_principal_id = mp.principal_id
JOIN sys.server_principals rp
ON rp.principal_id = drm.role_principal_idWHERE rp.name = 'sysadmin' AND mp.name = 'svankaya')ALTER SERVER ROLE [sysadmin] ADD MEMBER [svankaya]
GO
sp_who
sp_who2
SELECT * FROM MASTER..sysprocesses
--where status = 'runnable' --comment this outORDER BY CPU
DESC-- http://whoisactive.comsp_whoisactive
Running Jobs
SELECT sj.name
, sja.*FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
AND sja.stop_execution_date IS NULL
Uncompleted Jobs
SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id]
FROM [msdb].[dbo].[sysjobs_view] sjv
JOIN [msdb].[dbo].[sysjobactivity] sja ON [sjv].[job_id] = [sja].[job_id]
WHERE [sja].[run_requested_date] IS NOT NULL AND [sja].[stop_execution_date] IS NULL
-- Orphaned / uncompleted ... how to get only the activity for the current session
SELECT
job.name,
job.job_id,
job.originating_server,
activity.run_requested_date,
DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) AS Elapsed
FROM msdb.dbo.sysjobs_view job
JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
JOIN (
SELECT MAX( agent_start_date ) AS max_agent_start_date
FROM msdb.dbo.syssessions
) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL
-- Help about particular job
EXEC msdb.dbo.sp_help_job @Job_name = '' -- Gives Job details, step details, schedule details, server last run details
-- To stop job
EXEC dbo.sp_stop_job ''
