SqlServer Begin's

I was in the middle of a deployment and found that the database in the AG had been configured with service_broker enabled.  This setting shouldn't' have been enabled for the database and needed to be disabled. As i found, the setting can't be disabled for a database in a AG group. Following steps were followed to disable the service_broker setting for database.   1.) Removed the database from the AG group        USE [MASTER]; ALTER AVAILABILITY GROUP [ ] REMOVE DATABASE ;       FYI. By this time the rest of the replica's will be out of sync  2.) Change the service broker setting for the database  3.) Add the database to the AG group on the primary server  USE [MASTER]; ALTER AVAILABILITY GROUP [ ] ADD DATABASE ;  4.) Logged on to the secondary database, restored the transaction log from primary with Norecovery 5.) Add the secondary database to the AG group  USE [MASTER];  ALTER DATABASE SET HADR AVAILABILITY GROUP = [ ]; step

Very recently i wanted to migrate the data from 10M record table. This table didn't have clustered key and the business primary key was a GUID column.  For obvious reasons the data needed to be transferred in chunks. The listed code provided the upper and lower boundaries.     DECLARE @NumParts INT = 128; WITH PartsOf256(Part)  AS ( SELECT (256 / @NumParts) UNION ALL SELECT Part + (256 / @NumParts) FROM PartsOf256 WHERE Part < (256 - 256 / @NumParts) ) SELECT CONVERT(UNIQUEIDENTIFIER, 0x00000000000000000000 + CONVERT(VARBINARY(1), Part)) FROM PartsOf256 option (maxrecursion 0)

Including one more script to my script volt. I have always wanted to easily identify the dependent objects for a procedure. The core of code was grabbed from http://www.sqlservercentral.com/scripts/Cross+Reference/108582/ , I have changed the original script to accommodate the common filter conditions and other dependent  objects that I thought was needed. The query identifies the following dependent objects -          -   Tables -          -   Views -          -   Functions -          -   Procedures   /* Parameter -- @ProcedureName ( by default it will be set to  @ProcedureName  = '-1') --  @ProcedureName  = '-1' will  -- generate dependencies for all proceudre in the currnt database --  @ProcedureName  = ' ' + '%' -- will populate the dependencies which are in the LIKE clause */ DECLARE @ProcedureName sysname SET   @ProcedureName  = '-1' --  @ProcedureName  = ' ' + '%' -- Ena

Last week I had to investigate a problem with a third party databases in the Health industry and was told by the product owner to look into the potential issue and provide a set of recommendations. One of my objectives were to identify tables with clustered indexes and the composition of the index. i.e if the clustered index had multiple columns and the if the fist column was not incremental As usual,  I hope this script helps  IF OBJECT_ID ( 'TEMPDB..#Temp_Constraint_Columns' ) IS NOT NULL       DROP TABLE TEMPDB .. #Temp_Constraint_Columns CREATE TABLE #Temp_Constraint_Columns       (             IID int identity ( 1 , 1 )             , ObejctName varchar ( 100 )             , Index_Type varchar ( 50 )             , PK_YN      varchar ( 100 )             , Object_Type varchar ( 10 )             , KeyColumns varchar ( 500 )       )       INSERT INTO #Temp_Constraint_Columns        (             ObejctName            

I ran into a situation where It required me to transfer several securable for a particular user from PROD  to a UAT environment. I was surprised to find there wasn't any easy way to do this through a GUI.   Google to the rescue and found a simple query at http://www.sqlservercentral.com/Forums/Topic1360174-391-1.aspx , Few changes were done to accommodate my needs.      SELECT DP . State_desc + ' ' + DP . permission_name +               ' ON ' + object_name ( DP . major_id ) +               ' TO ' + '[' + SU . Name   COLLATE DATABASE_DEFAULT   + ']' FROM sys . database_permissions DP JOIN sys . sysusers SU ON   SU . UID = DP . grantee_principal_id WHERE SU . Name = ' ' order by object_name ( DP . major_id )