Search This Blog

SQL Server: Sending Email in HTML Table Format Using TSQL

As part of our DBA life , we have to configure many alert or statistical  mails which gives an idea about the state of the database server. Let us discuss how we can send an email in HTML table format using TSQL. I am just going to give a sample script and it is self explanatory.


USE MSDB
GO
DECLARE @Reportdate DATE
SET @Reportdate =CONVERT(VARCHAR(10),GETDATE(),121)

/**************************************************************
           full backup Header
***************************************************************/

DECLARE @FullBackupHeader VARCHAR(MAX)
SET @FullBackupHeader='<font color=black bold=true size= 5>'
SET @FullBackupHeader=@FullBackupHeader+'<BR /> Full Backup Report<BR />' 
SET @FullBackupHeader=@FullBackupHeader+'</font>'
/**************************************************************
           full backup report Section
***************************************************************/
DECLARE @FullBackupTable VARCHAR(MAX)    
SET @FullBackupTable= CAST( (    
SELECT td = name + '</td><td>' + BackupType + '</td><td>'+  FileName + '</td><td>'  + 
Startdate + '</td><td>'  + FinishDate+ '</td><td>' + Duration + '</td><td>'  +BackupSize+ 
'</td><td>'  +CompressionRatio 
FROM (    
      
       SELECT 
       sd.name,
       ISNULL(db.[Backup Type],'0') AS [BackupType], 
       ISNULL(DB.Physical_device_name,'No Backup') AS 'FileName',
       CAST(ISNULL(DB.backup_start_date,'1900-01-01') AS VARCHAR(24))  AS Startdate ,
       CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate,
       CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration,
       LEFT(CAST(ISNULL(Backupsize,0)AS VARCHAR(100)),4)+' GB' AS BackupSize,
       LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio FROM 
       SYS.SYSDATABASES sd LEFT JOIN 
       (
       SELECT 
       bm.media_Set_id,
       'FullBackup' AS 'Backup Type', 
       bm.Physical_device_name ,
       backup_start_date,
       backup_finish_date,
       Duration = 
CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' +  
RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2), 
database_name,
       ROUND((compressed_backup_size)/1024/1024/1024,2) AS Backupsize ,
       100-(compressed_backup_size*100/backup_size) AS ratio
       FROM msdb..backupmediafamily BM 
       INNER JOIN msdb..backupset bs ON bm.media_Set_id = bs.media_Set_id 
       WHERE [type]='D' AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND 
backup_start_date<=@Reportdate
       ) db ON sd.name=db.database_name
      ) AS d ORDER BY BackupType
  FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )    

  
  SET @FullBackupTable= '<table cellpadding="0" cellspacing="0" border="1">'    
              + '<tr><th width="10">Database Name</th>
              <th  width="20">Backup Type</th>
              <th width="80">File Name</th>
              <th width="100">Start Date</th>
              <th width="40">Finish Date</th>
              <th width="40">Duration</th>
              <th width="10">Backup Size</th>
              <th width="40">Compression Ratio</th></tr>'    
              + REPLACE( REPLACE( @FullBackupTable, '&lt;', '<' ), '&gt;', '>' )   
              + '</table>' 
             /**************************************************************
           differential backup Header
***************************************************************/
DECLARE @DiffBackupHeader VARCHAR(MAX)
SET @DiffBackupHeader ='<font color=black bold=true size= 5>'
SET @DiffBackupHeader =@DiffBackupHeader +'<BR /> Differential Backup Report<BR />' 
SET @DiffBackupHeader =@DiffBackupHeader +'</font>'
/**************************************************************
           Differential backup Section
***************************************************************/
DECLARE @DiffBackupTable VARCHAR(MAX)    
SET @DiffBackupTable= CAST( (    
SELECT td = name + '</td><td>' + BackupType + '</td><td>'+  FileName + '</td><td>'  + 
Startdate + '</td><td>'  + FinishDate+ '</td><td>' + Duration + '</td><td>'  +BackupSize+ 
'</td><td>'  +CompressionRatio 
FROM (    
       SELECT 
       sd.name,
       ISNULL(db.[Backup Type],'0') AS [BackupType], 
       ISNULL(DB.Physical_device_name,'NO BACKUP') AS 'FileName'  ,
       CAST(ISNULL(DB.backup_start_date,'1900-01-01') AS VARCHAR(24))AS Startdate ,
       CAST(ISNULL(DB.backup_finish_date,'1900-01-01') AS VARCHAR(24)) AS FinishDate,
       CAST(ISNULL(DB.Duration,'0') AS VARCHAR(24)) AS Duration,
       LEFT(CAST(ISNULL(Backupsize,0) AS VARCHAR(100)),6)+' MB' AS BackupSize,
       LEFT(CAST(ISNULL(ratio,0)AS VARCHAR(100)),5)+'%' AS CompressionRatio  
       FROM SYS.SYSDATABASES sd LEFT JOIN 
       (
           SELECT 
           bm.media_Set_id,
           'Differential Backup' AS 'Backup Type',
           bm.Physical_device_name ,
           backup_start_date,
           backup_finish_date,
           Duration = 
CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)/60) + ':' +  
RIGHT('00' + CONVERT(VARCHAR(5),DATEDIFF(second,backup_start_date,backup_finish_date)%60),2),
           database_name,
           ROUND((compressed_backup_size)/1024/1024,2) AS Backupsize ,
           100-(compressed_backup_size*100/backup_size) AS ratio
           FROM msdb..backupmediafamily BM INNER JOIN msdb..backupset bs ON bm.media_Set_id = 
bs.media_Set_id 
           WHERE TYPE='I'  AND backup_start_date>=DATEADD(dd,-1,@Reportdate) AND 
backup_start_date<=@Reportdate
       ) db ON sd.name=db.database_name
       ) AS d ORDER BY BackupType
      FOR XML PATH( 'tr' ), TYPE ) AS VARCHAR(MAX) )    
  
    SET @DiffBackupTable= '<table cellpadding="0" cellspacing="0" border="1">'    
              + '<tr><th width="10">Database Name</th>
              <th  width="20">Backup Type</th>
              <th width="80">File Name</th>
              <th width="100">Start Date</th>
              <th width="40">Finish Date</th>
              <th width="40">Duration</th>
              <th width="10">Backup Size</th>
              <th width="40">Compression Ratio</th></tr>'    
              + REPLACE( REPLACE( @DiffBackupTable, '&lt;', '<' ), '&gt;', '>' )   
              + '</table>' 

/**************************************************************
   Empty Section for giving space between table and headings
***************************************************************/
DECLARE @emptybody2 VARCHAR(MAX)  
SET @emptybody2=''  
SET @emptybody2 = '<table cellpadding="5" cellspacing="5" border="0">'    
              + 
              '<tr>
              <th width="500">               </th>
              </tr>'    
              + REPLACE( REPLACE( ISNULL(@emptybody2,''), '&lt;', '<' ), '&gt;', '>' )   
              + '</table>'    
/**************************************************************
           Sending Email
***************************************************************/
              DECLARE @subject AS VARCHAR(500)    
DECLARE @importance AS VARCHAR(6)    
DECLARE @EmailBody VARCHAR(MAX)
SET @importance ='High'     
DECLARE @recipientsList VARCHAR(8000)
SELECT @recipientsList = 'Dba@PracticalSqlDba.com;nelsonaloor@PracticalSqlDba.com'

SET @subject = 'Backup Report of MYSql Instance'     
SELECT @EmailBody 
=@FullBackupHeader+@emptybody2+@FullBackupTable+@emptybody2+@DiffBackupHeader 
+@emptybody2+@DiffBackupTable
EXEC msdb.dbo.sp_send_dbmail    
@profile_name ='MyMailProfile',    
@recipients=@recipientsList,
@subject = @subject ,    
@body = @EmailBody ,    
@body_format = 'HTML' ,    
@importance=@importance    

SQL Server: Performance Tuning (Understanding Set Statistics Time output)

In the last post we have discussed about Set Statistics IO and how it will help us in the performance tuning. In this post we will discuss about the Set Statistics Time which will give the statistics of time taken to execute a query.

 Let us start with a example.

 USE AdventureWorks2008
GO
            DBCC dropcleanbuffers
            DBCC freeproccache

 GO
SET STATISTICS TIME ON
GO
SELECT * 
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID 
    WHERE ProductID BETWEEN 700 
        AND 800
GO
SELECT * 
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID 
    WHERE ProductID BETWEEN 700 
        AND 800





















There aretwo select statement in the example .The first one is executed after clearing the buffer. Let us look into the output.

 SQL Server parse and Compile time : When we submit a query to SQL server to execute,it has to parse and compile for any syntax error and optimizer has to produce the optimal plan for the execution. SQL Server parse and Compile time refers to the time taken to complete this pre -execute steps.If you look into the output of second execution, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. That shows that SQL server did not spend any time in parsing and compiling the query as the execution plan was readily available in the cache. CPU time refers to the actual time spend on CPU and elapsed time refers to the total time taken for the completion of the parse and compile. The difference between the CPU time and elapsed time might wait time in the queue to get the CPU cycle or it was waiting for the IO completion. This does not have much significance in performance tuning as the value will vary from execution to execution. If you are getting consistent value in this section, probably you will be running the procedure with recompile option.
SQL Server Execution Time: This refers to the time taken by SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spend on CPU where as the elapsed time is the total time to complete the execution which includes signal wait time, wait time to complete the IO operation and time taken to transfer the output to the client.The CPU time can be used to baseline the performance tuning. This value will not vary much from execution to execution unless you modify the query or data. The load on the server will not impact much on this value. Please note that time shown is in milliseconds. The value of CPU time might vary from execution to execution for the same query with same data but it will be only in 100's which is only part of a second. The elapsed time will depend on many factor, like load on the server, IO load ,network bandwidth between server and client. So always use the CPU time as baseline while doing the performance tuning.

INTERVIEW Q&A


Most of the questions here are, which I was asked in my interviews!

 What is the Difference between Clustered Index and Non-clustered Index in SQL Server?

 Even this is basics in SQL server; I hear this question from most of the interviewers

 Clustered Index:  When you create a clustered Index, the actual leaf level pages of the Index contain the data of the base table and sorted by the column on which the index is created know as the clustered key. There can be only one clustered index per table as the data is sorted based on that column. Although multiple columns can be added to the Index also called as composite Index

 Non-Clustered Index: Whereas when you create a non-clustered index on a column, the leaf level pages does not contain the actual data of the base table but a pointer directed to the data records in the clustered key if you have clustered index created already or to the record of the base table in case of heap (No clustered Index) you can also add or include additional columns into non-clustered indexes as same in clustered Indexes. We can have up to 249 non-clustered indexes in SQL server 2005 and 999 in later versions per table.

 What is the difference between ‘TRUNCATE’ and ‘DELETE’?

 Truncate and delete both are used to delete the rows from a SQL Server Table. There is myth that so many DBA’s believe that Truncate is non logged operation but whereas DELETE is a logged operation but this is fully not true.

 Truncate is also a logged operation but logs only the de-allocations of the deleted rows from the table but Delete logs all the deleted rows in to the log file.

 Another major difference is you cannot use ‘where’ clause with TRUNCATE statement but ‘where’ clause can be used with DELETE

 EX:  Trunacte table [Dbo].[Employee]

 EX: DELETE from [Dbo].[Employee] where Name like ‘Microsoft’

 Which is faster to delete the entire table data? Obviously TRUNCATE whereas Delete has log each and every row.

 Note: Both Truncate and Delete deletes only data not the structure of the table.

 What is normalization and Normalization forms?

 The process of organizing data to minimize redundancy is called normalization. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

 There are 7 Normal forms defined in the real world but not really 6 Normal forms are implemented but only 3 normal forms are generally implemented in most cases. Any way we should beware of all of the normal forms in point of interview even though we don’t use them all because the client may use them..ha.

 First Normal Form (1 NF)
Second Normal Form (2 NF)
Third  Normal Form (3 NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4 NF)
Fifth Normal Form( 5 NF)
Sixth Normal Form(6 NF)
The best place I have seen them explain is here.  Knowing at least 3 NF in depth is needed for devlopers and DBA’s but not to Architects.

 What is de-normalization?

 De-normalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. De-normalization should only take place after a satisfactory level of normalization has taken place. De-normalization techniques are often used to improve the scalability of Web applications.

 De-normalization is often used to improve the performance of the database if there is any impact of Normalization on the database.

 For Example:

 You have two tables in referential integrity and while loading the data to tables might be any issue (depends on scenario) in that times we disable the referential integrity which is type of de- normalizing

 What is difference between the stored procedure and function?

 They are completely different in both the structure they will be created and the purpose too but even though I often hear this question from the interviewers.  The only similarity is they can be re-usable multiple times in your development now and in future and also both takes parameters .

 The function should return some value whereas stored procedure may not return a value.
Stored procedures will be executed independently by using EXEC store_proc whereas function should be called with in a SQL Query suppose say select serverproperty(‘productlevel’)
What’s the difference between a primary key and a unique key?

 Even though both the primary key and unique key are one or more columns that can uniquely identify a row in a table, they have some important differences. Most importantly, a table can have only a single primary key while it can have more than one unique key. Primary key can be considered as a special case of the unique key. Another difference is that primary keys have an implicit NOT NULL constraint while the unique key does not have that constraint. Therefore, unique key columns may or may not contain NULL values but primary key columns cannot contain NULL values.

 List some of the new features in SQL server 2008? (Version Related)

 Backup Compression
Page compression
Row compression
Hot add CPU
Resource Governor
Policy Based Management
Data Collector
Data-tier applications
Change data Capture (CDC)
What is new release of SQL Server yet to come?

 Code Named “Denali”, referring as SQL server 2011 as of today DENALI (CTP3) is released.

 What is RAID and what are different types of RAID configurations?(Storage Related)

 RAID stands for Redundant Array of Independent Disks. RAID 0, RAID 1, RAID 2, RAID 5 and RAID 10 are most popular RAID levels used for relational databases storage but there are other RAID levels too. To better understand the RAID level architecture and RAID level functionality follow this link

 Why we should have High Availability?

 The main point of a high-availability strategy is to keep the critical data as available as possible in the event of a failure or disaster. Failures, by their very nature, are not planned events and so it is hard to predict when one will occur and what form it will take. There are also times when planned events may occur that affect data availability if preventative steps have not been taken.

 Difference between High Availability and Disaster Recovery?

 Disaster Recovery is the process to restore operations critical to the resumption of business after a natural or human-induced disaster. High availability is to continue operations when a component fails. This is usually a hardware component like a CPU, power supply, Disk failure, Memory failure or the complete server. With DR there is usually some short loss of service while the DR systems and databases are activated and the operation is switched over to the DR site. With HA there is usually no loss of service when a component fails as it is immediately fail over to another serve without any downtime for the users in some cases.

 Why does the log file grow? Explain the scenarios?

 There can be many reasons for the log file to grow; some of them can be below,

 Choosing improper recovery model for the database
Running Huge Bulk Loads and not having an appropriate recovery model
Huge inserts on highly indexed tables
Having database in FULL recovery model and failed to do log backup
Lack of good backup strategy.
Here is link for an excellent blog post

 What steps you follow  in tuning a long running stored procedure?

 Checking the resources (CPU, Memory, IO) utilized by the stored procedure execution.
See if you can better re-code it to get the same results( Ex: Union all instead union)
Checking the execution plan to find the high cost operation in the process of execution
Finding any missing indexes on tables
Checking if there is a need for any Query Hints likes Maxdop (1), with recompile.
Have you ever worked on Clustering? If yes then how does the failover works in clustering?

 Yes, the clustered nodes use the heartbeat to check whether each node is alive, at both the operating system and SQL Server level.

 At the operating system level, the primary node reserves the resource every 3 seconds, and the competing node every 5 seconds. The process lasts for 25 seconds and then starts over again. For example, if the node owning the instance fails due to a problem (network, disk, and so on), at second 19, the competing node detects it at the 20-second mark, and if it is determined that the primary node no longer has control, the competing node takes over the resource.

 At SQL Server Level, the node hosting the SQL Server resource does a looks-alive check every 5 seconds. This is a lightweight check to see whether the service is running and may succeed even if the instance of SQL Server is not operational. The IsAlive check is more thorough and involves running a SELECT @@SERVERNAME Transact SQL query against the server to determine whether the server itself is available to respond to requests; it does not guarantee that the user databases are up. If this query fails, the IsAlive check retried five times and then attempts to reconnect to the instance of SQL Server. If all five retries fail, the SQL Server resource fails. Depending on the failover threshold configuration of the SQL Server resource, Windows Clustering will attempt to either restart the resource on the same node or fail over to another available node.

 During the fail over from one node to another, Windows clustering starts the SQL Server service for that instance on the new node, and goes through the recovery process to start the databases. The fail over of the SQL Server virtual server will take a short time (probably seconds). After the service is started and the master database is online, the SQL Server resource is considered to be up. Now the user databases will go through the normal recovery process, which means that any completed transactions in the transaction log are rolled forward, and any incomplete transactions are rolled back. The length of the recovery process depends on how much activity must be rolled forward or rolled back upon startup. Set the recovery interval of the server to a low number to avoid long recovery times and to speed up the failover process.

 What is /3GB   switch, PAE and AWE  related to memory management and what they do? 

 Here is the best article I found and read  /3gb , PAE and  AWE 

 How to recover a database from ‘Suspect Mode’ in SQL Server?

 Step 1: ALTER DATABASE My dB SET EMERGENCY

 Step 2: ALTER DATABASE My dB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

 Step 3:  DBCC CHECKDB (‘MY DB’, REPAIR_ALLOW_DATA_LOSS)

 Step 4: ALTER DATABASE MY DB SET MULTI_USER

 How to copy backup files from one server to another server?

 This can be done in multiple ways,

 Method 1: xp_cmdshell  ‘Robocopy  <source folder>  < destination folder>  <file to copy>’

 Method 2: Creating an SSIS Package using File Transfer Package.

 Method 3: Copying using shared folder or Network Copy

 Method 4: Other third Party Tools, X-copy utility and other.

 How to start and stop SQL Server from CMD?

 I’m not sure why the interviewer asks this question but they do ask this :D

 Step 1: To Start, Open CMD and Type ‘NET START MSSQLSERVER’

 Step 2: To Stop, ‘NET STOP MSSQLSERVER’

 How to connect to SQL Server instance Using CMD?

 Step 1: Open CMD, Type:  sqlcmd  –S  ‘instance name’ , Check for  more information: BOL

 How to Restore Master Database?

 Step 1:  Re-Start the Server in Single-User mode using –m startup Parameter, Check BOL

 Step 2: Run à ‘RESTORE DATABASE master FROMWITH REPLACE’, this will end up in shutting down the server.  Recommended to do this step using SQLCMD

 Step 3: Now, Restart the server normal by taking off the –m parameter.

 How to failover database in log shipping?

 Step 1: Remove secondary database from Log shipping

 Step 2:  Copy over any recent transactional log backups made on primary that are not copied to the secondary shared folder yet.

 Step 3: Restore all transaction log backups to secondary database; If possible when primary database is not damaged and you should take Tail-log backup with leaving the database in restoring mode this would revoke access to users.

 Step 4: Restore Tail log on to secondary database and the databases are in Sync Now.

 Step 5: Now, Redirect the users to the secondary database.

 Accidentally deleted a table, how to recover it with data?

 There are 2 ways to do this, depends on our situation. In both ways you need a Full Backup and consecutive differential and log backups till time.

 1.  Restore the Full Backup on your current database with norecovery and then Differential with norecovery and series of log backups with recovery only for the last log file to bring it online.

 2. Restore the Full Backup to New Database with different name and then do the same process as in step 1 and copy over the table to the actual dB that you deleted , this 2nd way will not hurt or disturb your current users unless they were using this table till it get copied over.

 No Backups ! You are screwed. you will know the importance of the backup only when it needed.

Find Primary Keys, Foreign Keys or Constraints in a database, SQL Server 2008


 I was asked to list the Primary keys in our databases then I listed using the below script. This is very useful when you have large number of tables.
SELECT 
A.CONSTRAINT_CATALOG,
A.CONSTRAINT_SCHEMA,
B.CONSTRAINT_NAME,
A.CONSTRAINT_TYPE, 
B.COLUMN_NAME,
B.ORDINAL_POSITION
 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS A Inner join 
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE B  
on A.CONSTRAINT_NAME=B.CONSTRAINT_NAME


SQL Server 2012- Sample Databases


I was not able to find the sample databases for SQL server 2012 as most of the sites I have visited have only 2000  sample databases which we will be not able to restore to 2012. Here I have uploaded the link 2012 sample .bak files. (Easy to restore to 2012)
Pubs, Adventure Works, North Wind  -Download

Activity monitor, Script to find Head blocker- SQL Server 2005, 2008 and later


This is very handy useful script in production environment. Even the activity monitor does  the same but when there is high server high activity then your activity monitor hangs and do not respond. Also it is not recommended to use activity monitor for long in production environments as it uses high resources.
SELECT
   [Session ID]    = s.session_id,
   [User Process]  = CONVERT(CHAR(1), s.is_user_process),
   [Login]         = s.login_name,  
   [Database]      = ISNULL(db_name(p.dbid), N''),
   [Task State]    = ISNULL(t.task_state, N''),
   [Command]       = ISNULL(r.command, N''),
   [Application]   = ISNULL(s.program_name, N''),
   [Wait Time (ms)]     = ISNULL(w.wait_duration_ms, 0),
   [Wait Type]     = ISNULL(w.wait_type, N''),
   [Wait Resource] = ISNULL(w.resource_description, N''),
   [Blocked By]    = ISNULL(CONVERT (varchar, w.blocking_session_id), ''),
   [Head Blocker]  =
        CASE
            -- session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others
            WHEN r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL) THEN '1'
            -- session is either not blocking someone, or is blocking someone but is blocked by another party
            ELSE ''
        END,
   [Total CPU (ms)] = s.cpu_time,
   [Total Physical I/O (MB)]   = (s.reads + s.writes) * 8 / 1024,
   [Memory Use (KB)]  = s.memory_usage * 8192 / 1024,
   [Open Transactions] = ISNULL(r.open_transaction_count,0),
   [Login Time]    = s.login_time,
   [Last Request Start Time] = s.last_request_start_time,
   [Host Name]     = ISNULL(s.host_name, N''),
   [Net Address]   = ISNULL(c.client_net_address, N''),
   [Execution Context ID] = ISNULL(t.exec_context_id, 0),
   [Request ID] = ISNULL(r.request_id, 0),
   [Workload Group] = ISNULL(g.name, N'')
FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)
LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)
LEFT OUTER JOIN
(
    -- In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as
    -- waiting for several different threads.  This will cause that thread to show up in multiple rows
    -- in our grid, which we don't want.  Use ROW_NUMBER to select the longest wait for each thread,
    -- and use it as representative of the other wait relationships this thread is involved in.
    SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num
    FROM sys.dm_os_waiting_tasks
) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1
LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)
LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g ON (g.group_id = s.group_id)--TAKE THIS dmv OUT TO WORK IN 2005
LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)
ORDER BY s.session_id; 


How to delete multiple logins from SQL Server (Sp_DropLogins)


I have been asked several times to delete multiple logins in a day in my environment then initially I was deleting them one after one using sp_droplogin with a single parameter @login . I thought it would have been really great with Microsoft if they have it as sp_droplogins so that I can drop multiple logins by just passing the login ID’s.

 However I created a script for it and I would like to share here and save you time. This stored procedure makes use of a user defined function to split the string (Multiple logins) we pass to it.

 Step1: Create a function Dbo.split using the code below in the master database context, thanks to coder


CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int      
declare @slice varchar(8000)      
select @idx = 1       
if len(@String)<1 or @String is null  
return      while @idx!= 0      
begin       
set @idx = charindex(@Delimiter,@String)       
if @idx!=0        
set @slice = left(@String,@idx - 1)       
else        
set @slice = @String       
if(len(@slice)>0)    
insert into @temptable(Items) 
values(@slice)       
set @String = right(@String,len(@String) - @idx)       
if len(@String) = 0 break      
end 
return     
END

Sample result from the function:
If you pass a string with multiple login ids as ‘test123, test456’
SELECT * FROM dbo.SPLIT(‘test123,test456′,’,’)
Result:
Step 2 : Create a stored procedure with name sp_droplogins, if we feel as it is confusing with system styored procedure sp_droplogin, you can change it your name there is no impact with name on the internal code of the stored procedure.
Create a stored Procedure using the below code in master database context.,

Create procedure sp_droplogins
( 
 @loginIDS Varchar(max)
)
AS
DECLARE @login VARCHAR(max)
DECLARE @Droplogins CURSOR
SET @droplogins = CURSOR FOR
select name from sys.syslogins where name IN
(SELECT* FROM dbo.SPLIT(@loginIds,',')
)
OPEN @droplogins
FETCH NEXT
FROM @droplogins INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sys.sp_droplogin @login
FETCH NEXT
FROM @droplogins INTO @login
END
CLOSE @droplogins
DEALLOCATE @droplogins
GO

Step3:
The execution of stored procedure looks like
EXEC sp_droplogins @loginids='test123,test456'
By executing the above stored procedure, the logins with names ‘test123’ and ‘test456’ are dropped or deleted.
Note: By deleting the logins, the associated database users are not deleted.
Subscribe to: Posts (Atom)