Thursday, 17 September 2015

How to kill all sessions for single Database in SQL server



Please find below query:

DECLARE @DatabaseName nvarchar(50)
SET @DatabaseName = N'Name _of_Database'
--SET @DatabaseName = DB_NAME()

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'Kill ' + Convert(varchar, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId

-- SELECT @SQL
EXEC(@SQL)

What is Clutter in Outlook


Whenever we tired of unknown and unwanted mails, In such case we apply rules in outlook and unwanted mails automatically moved to the defined folder. But as we know there is limitation of defining number of rules in outlook. Sometime your rules gets disabled and to overcome this Microsoft introduced Clutter in Outlook. Clutter" can help you filter low-priority email, saving time for your most important messages. The email server keeps track of the email you read and the ones you don't. Once you turn it on, Clutter is automatic. As new email comes in, it takes messages you're most likely to ignore and puts them into the "Clutter" folder. The more you use it, the better it gets. And if you find Clutter isn't for you, you can turn it off.

My experience with Clutter is quit good and i appreciate for this amazing feature. 

Friday, 28 June 2013

Locate missing indices

Locate missing indices

 SELECT * FROM sys.dm_db_missing_index_groups G INNER JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle INNER JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC ORDER BY D.index_handle ,[statement] 

Locate unused indices

 SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName ,OBJECT_NAME(I.OBJECT_ID) AS ObjectName ,I.NAME AS IndexName FROM sys.indexes I WHERE OBJECTPROPERTY(I.OBJECT_ID, 'IsUserTable') = 1 AND NOT EXISTS ( SELECT index_id FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = I.OBJECT_ID AND I.index_id = index_id AND database_id = DB_ID() ) ORDER BY SchemaName ,ObjectName ,IndexName

Thursday, 27 June 2013

Back Up your database using Cursor on SQL server 2008

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'D:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
       BACKUP DATABASE @name TO DISK = @fileName

       FETCH NEXT FROM db_cursor INTO @name  
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

Find out job name from command text

USE [msdb]
GO
SELECT j.job_id,
s.srvname,
j.name,
js.step_id,
js.command,
j.enabled
FROM dbo.sysjobs j
JOIN dbo.sysjobsteps js
ON js.job_id = j.job_id
JOIN master.dbo.sysservers s
ON s.srvid = j.originating_server_id
WHERE js.command LIKE N'%Your_text_Here%'
GO

SQL server 2008 Script for job details

set nocount on

select 'Server'       = left(@@ServerName,20),
       'JobName'      = left(S.name,30),
       'ScheduleName' = left(ss.name,25),
       'Enabled'      = CASE (S.enabled)
                          WHEN 0 THEN 'No'
                          WHEN 1 THEN 'Yes'
                          ELSE '??'
                        END,
       'Frequency'    = CASE(ss.freq_type)
                          WHENTHEN 'Once'
                          WHENTHEN 'Daily'
                          WHENTHEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Weeks'  else 'Weekly'  end)
                          WHEN 16 THEN (case when (ss.freq_recurrence_factor > 1) then  'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' else 'Monthly' end)
                          WHEN 32 THEN 'Every ' + convert(varchar(3),ss.freq_recurrence_factor) + ' Months' -- RELATIVE
                          WHEN 64 THEN 'SQL Startup'
                          WHEN 128 THEN 'SQL Idle'
                          ELSE '??'
                        END,
       'Interval'    = CASE
                         WHEN (freq_type = 1)                       then 'One time only'
                         WHEN (freq_type = 4 and freq_interval = 1) then 'Every Day'
                         WHEN (freq_type = 4 and freq_interval > 1) then 'Every ' + convert(varchar(10),freq_interval) + ' Days'
                         WHEN (freq_type = 8) then (select 'Weekly Schedule' = D1+ D2+D3+D4+D5+D6+D7
                                                       from (select ss.schedule_id,
                                                                     freq_interval,
                                                                     'D1' = CASE WHEN (freq_interval &<> 0) then 'Sun ' ELSE '' END,
                                                                     'D2' = CASE WHEN (freq_interval &<> 0) then 'Mon '  ELSE '' END,
                                                                     'D3' = CASE WHEN (freq_interval &<> 0) then 'Tue '  ELSE '' END,
                                                                     'D4' = CASE WHEN (freq_interval &<> 0) then 'Wed '  ELSE '' END,
                                                                    'D5' = CASE WHEN (freq_interval & 16 <> 0) then 'Thu '  ELSE '' END,
                                                                     'D6' = CASE WHEN (freq_interval & 32 <> 0) then 'Fri '  ELSE '' END,
                                                                     'D7' = CASE WHEN (freq_interval & 64 <> 0) then 'Sat '  ELSE '' END
                                                                 from msdb..sysschedules ss
                                                                where freq_type = 8
                                                           ) as F
                                                       where schedule_id = sj.schedule_id
                                                    )
                         WHEN (freq_type = 16) then 'Day ' + convert(varchar(2),freq_interval)
                         WHEN (freq_type = 32) then (select freq_rel + WDAY
                                                        from (select ss.schedule_id,
                                                                     'freq_rel' = CASE(freq_relative_interval)
                                                                                    WHEN 1 then 'First'
                                                                                    WHEN 2 then 'Second'
                                                                                    WHEN 4 then 'Third'
                                                                                    WHEN 8 then 'Fourth'
                                                                                    WHEN 16 then 'Last'
                                                                                    ELSE '??'
                                                                                  END,
                                                                    'WDAY'     = CASE (freq_interval)
                                                                                    WHEN 1 then ' Sun'
                                                                                    WHEN 2 then ' Mon'
                                                                                    WHEN 3 then ' Tue'
                                                                                    WHEN 4 then ' Wed'
                                                                                    WHEN 5 then ' Thu'
                                                                                    WHEN 6 then ' Fri'
                                                                                    WHEN 7 then ' Sat'
                                                                                    WHEN 8 then ' Day'
                                                                                    WHEN 9 then ' Weekday'
                                                                                    WHEN 10 then ' Weekend'
                                                                                    ELSE '??'
                                                                                  END
                                                                from msdb..sysschedules ss
                                                                where ss.freq_type = 32
                                                             ) as WS
                                                       where WS.schedule_id =ss.schedule_id
                                                       )
                       END,
       'Time' = CASE (freq_subday_type)
                        WHEN 1 then   left(stuff((stuff((replicate('0', 6 - len(Active_Start_Time)))+ convert(varchar(6),Active_Start_Time),3,0,':')),6,0,':'),8)
                        WHEN 2 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' seconds'
                        WHEN 4 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' minutes'
                        WHEN 8 then 'Every ' + convert(varchar(10),freq_subday_interval) + ' hours'
                        ELSE '??'
                      END,

       'Next Run Time' = CASE SJ.next_run_date
                           WHEN 0 THEN cast('n/a' as char(10))
                           ELSE convert(char(10), convert(datetime, convert(char(8),SJ.next_run_date)),120)  + ' ' + left(stuff((stuff((replicate('0', 6 - len(next_run_time)))+ convert(varchar(6),next_run_time),3,0,':')),6,0,':'),8)
                         END
 
   from msdb.dbo.sysjobschedules SJ
   join msdb.dbo.sysjobs         S  on S.job_id       = SJ.job_id
   join msdb.dbo.sysschedules    SS on ss.schedule_id = sj.schedule_id
order by S.name


GO

IF (object_id('sp_ShowJobSchedules') IS NOT NULL)
  PRINT 'Procedure created.'
ELSE
  PRINT 'Procedure NOT created.'
GO