SQL-BOX.COM

–SQL Server Storage

Category Archives: ARTICLES

How to Delete Millions of Rows From SQL Server Table ?

Leave a comment Posted by sqlfrndz on August 17, 2015

Sometimes there will be the requirement to delete million rows from a multi million table, as it is hard to run a single Delete Statement Like below Query 1 because it could eventually fill up your transaction log and may not be truncated from log until all the rows have been deleted and the statement is completed because it will be treated as open transaction. Instead we can achieve the same by running the delete in multiple batches in an easy way. Once we knew the number of rows we need to delete, we will choose the batch size and number of batches we need to run like in Query 2 where I need to Delete to 1 million rows, I chose batch size as 10000 and number of batches to 100 , so that 10000*100 equals to 1 Million rows. Number of batches could be more approximately.

Query 1: Query to Delete records older than ‘1/1/2015’

Select Count(*) from History Where Date <= ‘1/1/2015’

1000000 – 1 Million rows and we need to delete all 1 million rows.

Delete from History Where Date <= ‘1/1/2015’

Query 2:

DELETE Top (10000) from History Where Date <= ‘1/1/2015’

Go 100

The Tricky part is ‘Go ‘ which is batch separator , the number we pass to the Go is the number of times you want to run that Query and each run time is considered as single batch.

SQL server updates or service pack hung or failed to install!!

Leave a comment Posted by sqlfrndz on April 1, 2015

When installing sql server update or service pack, one of the reason to fail if you have invalid registry settings stored from previous installations and may not be valid anymore due to the change in location of the binaries when you installed previously. I recently had an issue while installing sql service pack 3 for sql 2008 r2 and thought it would be useful to share.

When ever your installation hung and does not move forward or failed, you can look into the installation logs that can be found under, C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log and every time you try to install it creates sub folder with logs in it, so check the time stamp for the current installation logs. Search through installation for any errors. Here is the sample of the error I got and the reason for the installation hang and never moves forward.

MSI (s) (0C:34) []: SOURCEMGMT: Attempting to use LastUsedSource from source list.

MSI (s) (0C:34) []: SOURCEMGMT: Trying source \\************\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\.

MSI (s) (0C:34) []: Note: 1: 1314 2: \\************\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\

MSI (s) (0C:34) []: ConnectToSource: CreatePath/CreateFilePath failed with: -2147483648 1314 -2147483648

MSI (s) (0C:34) [] ConnectToSource (con’t): CreatePath/CreateFilePath failed with: -2147483648 -2147483648

MSI (s) (0C:34) []: SOURCEMGMT: net source ‘\\**********\sql\Microsoft SQL Server 2008 DEV R2\x64\setup\sql_engine_core_inst_msi\’ is invalid.

MSI (s) (0C:34) []: Note: 1: 1706 2: -2147483647 3: sql_engine_core_inst.msi

MSI (s) (0C:34) []: SOURCEMGMT: Processing net source list.

MSI (s) (0C:34) []: Note: 1: 1706 2: -2147483647 3: sql_engine_core_inst.msi

MSI (s) (0C:34) []: SOURCEMGMT: Processing media source list.

MSI (s) (0C:34) []: Note: 1: 2203 2: 3: -2147287037

MSI (s) (0C:34) []: SOURCEMGMT: Source is invalid due to missing/inaccessible package.

As the error highlited in red, it is looking for the location of the binaries in the location where it was when the last time sql server or updates are installed as it stores this location in registry in location HKEY_CLASSES_ROOT>installer>Products> then we have to scroll down each product list under with some system number and find one for the SQl server Database Engine services and it will be missing for all other components too. So in order to fix this you need edit manually in registry and find the missing packages using a VB script provided from Microsoft as provide in the KB 969052.

The other easy workaround that I found and worked for me is try repair from programs and features > right click on SQL server > click change/remove >repair and not it will ask for the location of the SQL server binaries, once you pointed to the new binaries location it does repair the setup support files in registry and updates the new location of the missing packages for lastusedsource, once this is done reboot the server and re-install service pack and installed successfully.

If you click on net under the source list it will display your old location and new locations.

ARTICLES .msi sql srever, core_inst_msi, hotfix, missing/inaccessible package, service pack failed, service pack3

Finding top 10 large tables in each database in an instance of SQL server

Leave a comment Posted by sqlfrndz on March 23, 2015

It is a good practice to find and monitor your large SQL server tables, we may surprise in some cases where our large audit or logging tables that are not being cleaned up frequently and fill into multiple GB’s of data and may result in higher backup and restore times. Working with your development team on these large tables to see if they are serving any business else can be archived and reduces huge maintenance over head with backup/restores and also saves disk space available.

Here is the script that finds top 10 large tables that are ordered by reserved disk space.

--CREATING STAGING TABLES S
CREATE TABLE MASTER.DBO.SPT_SPACE
(
DBNAME VARCHAR(50) NOT NULL,
OBJID VARCHAR(300) NULL,
ROWS INT NULL,
RESERVED DEC(15) NULL,
DATA DEC(15) NULL,
INDEXP DEC(15) NULL,
UNUSED DEC(15) NULL
)
CREATE TABLE [DBO].[TOP10_LARGE](
[DBNAME] [VARCHAR](50) NOT NULL,
[TABLE_NAME] [SYSNAME] NULL,
[ROWS] [CHAR](11) NULL,
[RESERVED_KB] [VARCHAR](18) NULL,
[DATA_KB] [VARCHAR](18) NULL,
[INDEX_SIZE_KB] [VARCHAR](18) NULL,
[UNUSED_KB] [VARCHAR](18) NULL
) ON [PRIMARY]
EXEC SP_MSFOREACHDB 'USE ?

DECLARE @ID INT
DECLARE @TYPE CHARACTER(2)
DECLARE @PAGES INT
DECLARE @DBNAME SYSNAME
DECLARE @DBSIZE DEC(15,0)
DECLARE @BYTESPERPAGE DEC(15,0)
DECLARE @PAGESPERMB DEC(15,0)

SET NOCOUNT ON

-- CREATE A CURSOR TO LOOP THROUGH THE USER TABLES
DECLARE C_TABLES CURSOR FOR
SELECT ID
FROM SYSOBJECTS
WHERE XTYPE = ''U''

OPEN C_TABLES

FETCH NEXT FROM C_TABLES
INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

/* CODE FROM SP_SPACEUSED */
INSERT INTO MASTER.DBO.SPT_SPACE (DBNAME, OBJID, RESERVED)

SELECT DB_NAME(), OBJID = @ID, SUM(RESERVED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID

SELECT @PAGES = SUM(DPAGES)
FROM SYSINDEXES
WHERE INDID < 2
AND ID = @ID
SELECT @PAGES = @PAGES + ISNULL(SUM(USED), 0)
FROM SYSINDEXES
WHERE INDID = 255
AND ID = @ID
UPDATE MASTER.DBO.SPT_SPACE
SET DATA = @PAGES
WHERE OBJID = @ID
/* INDEX: SUM(USED) WHERE INDID IN (0, 1, 255) - DATA */
UPDATE MASTER.DBO.SPT_SPACE
SET INDEXP = (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
- DATA
WHERE OBJID = @ID

/* UNUSED: SUM(RESERVED) - SUM(USED) WHERE INDID IN (0, 1, 255) */
UPDATE MASTER.DBO.SPT_SPACE
SET UNUSED = RESERVED
- (SELECT SUM(USED)
FROM SYSINDEXES
WHERE INDID IN (0, 1, 255)
AND ID = @ID)
WHERE OBJID = @ID

UPDATE MASTER.DBO.SPT_SPACE
SET ROWS = I.ROWS
FROM SYSINDEXES I
WHERE I.INDID < 2
AND I.ID = @ID
AND OBJID = @ID

FETCH NEXT FROM C_TABLES
INTO @ID
END

CLOSE C_TABLES
DEALLOCATE C_TABLES'

EXEC SP_MSFOREACHDB' USE ?
INSERT INTO MASTER.DBO.TOP10_LARGE
SELECT TOP 10
DBNAME,
TABLE_NAME = (SELECT NAME FROM SYS.SYSOBJECTS WHERE ID = OBJID),
ROWS = CONVERT(CHAR(11), ROWS),
RESERVED_KB = LTRIM(STR(RESERVED * D.LOW / 1024.,15,0) ),
DATA_KB = LTRIM(STR(DATA * D.LOW / 1024.,15,0) ),
INDEX_SIZE_KB = LTRIM(STR(INDEXP * D.LOW / 1024.,15,0)),
UNUSED_KB = LTRIM(STR(UNUSED * D.LOW / 1024.,15,0) )

FROM MASTER.DBO.SPT_SPACE A, MASTER.DBO.SPT_VALUES D
WHERE D.NUMBER = 1 AND D.TYPE = ''E'' AND DBNAME = ''?''ORDER BY RESERVED DESC'
GO

SELECT * FROM MASTER.DBO.TOP10_LARGE WHERE DBNAME NOT IN ('MASTER', 'TEMPDB','MSDB', 'MODEL')

--CLEANING UP ALL TABLES.

-- DROP TABLE MASTER.DBO.TOP10_LARGE

-- DROP TABLE MASTER.DBO.SPT_SPACE

ARTICLES, DBA SCRIPTS big tables, dba, Finding Large tables, Large SQl tables, script for large tables, SQL Server

Always – ON Availability group Alerts

Leave a comment Posted by sqlfrndz on April 15, 2014

After setting up Always ON , we need to configure SQL server Alerts to know about the availability Group errors and other failover information you may want to know when occurred. Here is the list of error and scripts to create them.

You may don’t want to configure all alerts and flood inboxes when happened, you can choose which one to configure using below query,

SELECT message_id [error_number], severity, text
FROM sys.messages
WHERE text LIKE (‘%availability%’)
AND is_event_logged = 1;

Here is the Script to configure all alerts related to AG’s

————————————————————
— Always – ON HA Error Alerts
————————————————————

USE [msdb]
GO

/****** Object: Alert [HA Error – 35254] Script Date: 10/20/2014 1:55:32 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35254′,
@message_id=35254,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35262] Script Date: 10/20/2014 1:55:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35262′,
@message_id=35262,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35273] Script Date: 10/20/2014 1:56:01 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35273′,
@message_id=35273,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35274] Script Date: 10/20/2014 1:56:16 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35274′,
@message_id=35274,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35275] Script Date: 10/20/2014 1:56:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35275′,
@message_id=35275,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35276] Script Date: 10/20/2014 1:56:46 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35276′,
@message_id=35276,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35279] Script Date: 10/20/2014 1:57:03 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35279′,
@message_id=35279,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 35299] Script Date: 10/20/2014 1:57:23 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 35299′,
@message_id=35299,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41048] Script Date: 10/20/2014 1:57:38 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41048′,
@message_id=41048,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41049] Script Date: 10/20/2014 1:57:56 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41049′,
@message_id=41049,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41050] Script Date: 10/20/2014 1:58:13 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41050′,
@message_id=41050,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41051] Script Date: 10/20/2014 1:58:29 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41051′,
@message_id=41051,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41052] Script Date: 10/20/2014 1:58:43 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41052′,
@message_id=41052,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41053] Script Date: 10/20/2014 1:59:06 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41053′,
@message_id=41053,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41054] Script Date: 10/20/2014 1:59:21 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41054′,
@message_id=41054,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41055] Script Date: 10/20/2014 1:59:34 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41055′,
@message_id=41055,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [HA Error – 41089] Script Date: 10/20/2014 1:59:47 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’HA Error – 41089′,
@message_id=41089,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Resumed] Script Date: 10/20/2014 2:13:37 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Resumed’,
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Data Movement – Suspended] Script Date: 10/20/2014 2:13:45 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Data Movement – Suspended’,
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

/****** Object: Alert [AG Role Change] Script Date: 10/20/2014 2:13:51 PM ******/
EXEC msdb.dbo.sp_add_alert @name=N’AG Role Change’,
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]’,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO

Make sure to enable e-mail operator for each alert after executing above script.

ARTICLES Ag failover Alert, Ag notification, Always On ALerts, Always On failover alert

What is Parameter Sniffing in simple words?

1 Comment Posted by sqlfrndz on April 17, 2013

I have been asked recently what is parameter sniffing in an interview. Well, we all know how Optimizer in SQL server works to retrieving the data from the tables. We do also know that Optimizer reads the statistics and internal pre-compiled plans instead generating a new plan for parameterized Queries and Store procedures. Here we are, how Optimizer knows we are executing a similar query as it searching for pre-compiled plan for us because it was reading the parameters we passed to the stored procedure or a parameterized query and it is called “sniffing”.

What it counts for Performance, Good or bad?

“It depends “Yes “It depends” – I know we use this word many times in regards to databases

Good:

By default, Parameter sniffing is enabled and SQL Server caches the plans and reads the parameters those passed to the stored procedures or parameterized SQL Queries. We benefit from it as Optimizer will go and search for the plan it compiled earlier in cache when we execute the same query again. Optimizer will not need to re-create the plan to execute the same query multiple times.

Bad:

This is all good only when there are good statics are maintained and typical parameters are used. What changes day by day is the requirement for using the new parameters with different words and optimizer will tries to use the same old plan in cache and may not be good for the new parameters as data has grown a lot and statistics have not updated yet and may be the plan for new parameter was doing table scans.

Work Around:

Until we see a performance issue with this parameter sniffing it is better to leave it by default and we really benefit from it.
When we see lot of performance issue, we could disable it using a trace flag 4136, this will disable the parameter sniffing at the server level.
We could use it to disable only for specific query then we could use the recompile query hint

ARTICLES Optimizer, Parameter sniffing, sniffing in sql server, SQL Server

How to transfer logins and passwords between instances of SQL Server?

Leave a comment Posted by sqlfrndz on March 13, 2012

There are many ways to transfer logins between instances under the same domain like

1. Scripting out logins

2. Logins Transfer task in SSIS

But the above two of them transfer the logins with random password instead actual password and needs to change the password after transferring the logins.

Here is the another process to transfer the logins between the instances with passwords too in 3 steps.

Step 1: Run the below script to create two stored procedures called ‘sp_hexadecimal’ and ‘sp_help_revlogin’

----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF' 
WHILE (@i <= @length) 
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin 
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR 
    SELECT sid, name, xstatus, password FROM master..sysxlogins 
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs 
  DEALLOCATE login_curs 
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr 
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr 
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr 
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
 EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN 
        -- Null password
 EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
      ELSE 
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr 
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs 
DEALLOCATE login_curs 
RETURN 0
GO
 ----- End Script -----

Step 2: Now two stored procedures are created under master database, Run the below command

EXEC master..sp_help_revlogin

The output from the above query is the script to create the logins with passwords where passwords are encrypted in hexadecimal code . All we need to do is copy the logins needs to be created on the other instance

Step 3: Paste the code that is copied from the output in step 2 into the new query window in second instance and execute it.

Done. your logins are transferred with passwords.

For SQL Server 2005 and 2008 : Click here

ARTICLES, DBA SCRIPTS, DEVELOPMENT SCRIPTS accounts transfer between instance3s\, between instances, How to transfer sql logins, logins, logins transfer, sp_help_revlogin, SQL Server login, sql server login transfer between instances

How to delete multiple logins from SQL Server (Sp_DropLogins )

Leave a comment Posted by sqlfrndz on August 16, 2011

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.

ARTICLES, Blogs, DBA SCRIPTS, DEVELOPMENT SCRIPTS Delete Login, Scripts, sp_droplogin, SQL Server login, xp_logininfo

Script to find the fragmentation level of specific table and its indexes in SQL Server

1 Comment Posted by sqlfrndz on July 25, 2011

This script is very useful when you have large tables and queries against those table usually runs slow then you can find if that is due to fragmentation of the table and its indexes or not. This script gives detailed information about the fragmentation in percentage.

DECLARE @db_id INT;
DECLARE @object_id INT;
SET @db_id = db_id(N'Database_Name');
SET @object_id = OBJECT_ID(N'Table_Name');
BEGIN
   SELECT *
   FROM sys.dm_db_index_physical_stats(@db_Id,@object_ID, NULL, NULL , 'DETAILED') AS IPS;
END
GO

Sample:

What is IN_ROW_DATA?

The allocation of data in a row is of three types

Allocation unit type	Is used to manage
IN_ROW_DATA	Data or index rows that contain all data, except large object (LOB) data.Pages are of type Data or Index.
LOB_DATA	Large object data stored in one or more of these data types: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), or CLR user-defined types (CLR UDT).Pages are of type Text/Image.
ROW_OVERFLOW_DATA	Variable length data stored in varchar, nvarchar, varbinary, or sql_variant columns that exceed the 8,060 byte row size limit.Pages are of type Text/Image.

Here is beautiful script that gives the how much of data in you table is allocated into what type of allocation.

SELECT OBJECT_NAME(p.object_id) AS Object_Name
       , i.name AS Index_Name
       , ps.in_row_used_page_count AS IN_ROW_DATA
       , ps.row_overflow_used_page_count AS ROW_OVERFLOW_DATA
       , ps.lob_used_page_count AS LOB_DATA
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
JOIN sys.indexes i ON p.index_id = i.index_id AND p.object_id = i.object_id
WHERE OBJECTPROPERTY (p.[object_id], 'IsUserTable') = 1
order by IN_ROW_DATA desc,LOB_DATA

By running the above query you can know whether that table is used for Image or text data.

Follow @sqlfrndz

ARTICLES, DBA SCRIPTS Fragmentation

Track DML Changes Using after Trigger for Update, Delete and Insert rows- Capture Changed data rows using T-sql

4 Comments Posted by sqlfrndz on July 10, 2011

Track DML Changes Using after Trigger for Update, Delete and Insert rows

There is out lot many options to track DML changes to data in SQL server like Change data Capture(CDC), Change tracking which are very easy to implement and make use of them but there are scenarios where we cannot use these CDC and change tracking as they were introduced in SQL server 2008. If you want to implement this in before 2008 versions the best way can be using of triggers.

We can track data changes to a SQL server table like update, delete and insert operations individually by creating AFTER Triggers for update , Trigger for Insert and Trigger for Delete. In our scenario I’m going to explain creating a trigger to track the data changes and save into audit table for each individual operation happens on rows in a table.

What is the purpose of this trigger?

Track the newly inserted or updated row and save into the audit table
Track the deleted rows and save into the audit table by using date stamp column

By implementing this trigger we can have the historical changes to the rows in a table and also we can query the table by using data column to find out the productivity of the table and how much operational is the table?

Note: This trigger captures only the operation occurred and on which row is effected, See below for the snapshot of the result.

Keep in mind that I’m using SQL Server Denali (CTP) Version for creating this trigger but also tested in 2005 and 2008 versions too.

In order to implement this Trigger, I’m creating test database by using the below script

/*Creating a Test Database*/
 Create database TestDB
 Go

Next, Creating test table to implement trigger on

/*Create Test Table to make use for Implementing trigger for DML Changes */

 Create table Test_table(
 id int identity
,Name varchar(50)
,phonenumber varchar(10) )

Next, insert some data into the table to perform testing the trigger using the below script

/*Insert data into Test Table for which we track DML changes*/

 Insert into Test_table(Name,phonenumber) values('Lucky',9191919191)
 Insert into Test_table(Name,phonenumber) values('Priya',0110101101)
 Insert into Test_table(Name,phonenumber) values('Meha',9987979237)
 Insert into Test_table(Name,phonenumber) values('stacy',9178697239)
 Insert into Test_table(Name,phonenumber) values('Nancy',9126827982)

/*select the rows inserted in the above script*/

select * from TestDB.dbo.Test_table

Next, creating an audit table in the TestDB to save the trigger tracked data from inserted, updated and deleted as below make sure that we are creating timestamp column to query later for historical changes to the table as below

/*Create Table to save Audit Data changes */

Create table TestDB.dbo.Test_table_Audit(
Effective_date datetime -- To get the date and time of the changed row
,Operation char(10) -- To get the operation occured like Insert or Update or Delete
,Id INT 
,Name Varchar(50)
,phonenumber varchar(10)
 )

In the next final step we will see how to create the After Trigger for all DML changes and save into the audit table using the below script which is well explained with comment lines.

Create TRIGGER  [dbo].[Tr_Test_Table_Audit]
ON [dbo].[Test_table] FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION
AS
-- SET NOCOUNT ON added to prevent extra result sets from query execution

 SET NOCOUNT ON;

-- Determine if this is an INSERT,UPDATE, or DELETE Action

    DECLARE @operation as Varchar(10)
    DECLARE @Count as int
    SET @operation = 'Inserted' -- Setting operation to 'Inserted'
              SELECT @Count = COUNT(*) FROM DELETED
    if @Count > 0
        BEGIN
              SET @operation = 'Deleted' -- Set Operation to 'Deleted'
              SELECT @Count = COUNT(*) FROM INSERTED
              IF @Count > 0
              SET @operation = 'Updated' -- Set Operation to 'Updated'
        END
-- Capturing Delete Operation
    if @operation = 'Deleted'

        BEGIN
            Insert into Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	        SELECT GETDATE(),'Deleted',ID,Name,phonenumber  from deleted
        END
ELSE
        BEGIN

-- trigger treats insert and update as same, so we can make it clear here 

            SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted

--Capturing Insert Operation
     if @operation = 'Inserted'

        BEGIN
             Insert into Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	         SELECT GETDATE(),'inserted',ID,Name,phonenumber from inserted
        END

-- Capture Update Operation
ELSE

        BEGIN
              INSERT INTO Test_table_Audit(Effective_date,Operation,Id,Name,phonenumber)
	          SELECT GETDATE(),'Updated',ID,Name,phonenumber from inserted

        END
END

Testing

Test Insert Operation – Insert a row into Test_table

/*Testing Insert Operation*/
insert into Test_table(Name,phonenumber) values('Microsoft',9190879979)

Then verify that the row we inserted above is tracked and saved in the audit table.

select * from dbo.Test_table_Audit where Name like ‘Microsoft’

Test Update Operation – Update a row just inserted above using below script

/*Testing Update Operation*/
Update Test_table
Set Name= 'SQLFRNDZ'
Where Name like 'Microsoft'

Then verify that the row we updated is tracked and inserted into audit table

select * from dbo.Test_table_Audit where Name like ‘SQLFRNDZ’

Test Delete operation- Delete a row we just updated above using below script

/*Delete a row from the table test_table*/
DELETE FROM Test_table
WHERE Name like 'SQLFRNDZ'

Then verify that the row we deleted is tracked and inserted into audit table

/*Verify the deleted row*/
Select * from dbo.Test_table_Audit where Operation like 'Deleted'

I would to see any other better type of trigger if you have to track the changes in a similar way please reply at the bottom of this page or send it to

admin@sqlfrndz.com

Follow @sqlfrndz

ARTICLES, DBA SCRIPTS Trigger

Compare Two Tables Using T-SQL or Command Line Utility (tablediff.exe)

Leave a comment Posted by sqlfrndz on June 28, 2011

There is out too many ways to compare two tables in SQL server but I prefer to do this task in an easy way using EXCEPT and INTERSECT introduced before SQL Server 2005 but more enhanced and improved performance since SQL Server 2005.

Where do we make use of this comparison?

For example if you have same database in multiple environments and you would like to verify the data in those tables are also same or not. Mostly this will be useful to compare the production tables to development server tables

Using EXCEPT and INTERSECT (T-SQL)

Conditions to use EXCEPT and INTERSECT

The number and the order of the columns must be the same in all queries or tables you are comparing.
EXCEPT and INTERSECT Commands performs based on results of the queries.
The data types must be compatible.

I will explain how to use this commands in different cases as below,

Case 1: Same number of columns and structure of the tables.

Comparing tables with same structure in different databases or instances to find matched and unmatched data in those tables

/*Returns all rows in Table_A that do not match exactly in Table_B*/

SELECT * FROM TABLE_A EXCEPT SELECT * FROM TABLE _B
/*Returns all rows in table B that do not match exactly in table B*/

SELECT * FROM TABLE_B EXCEPT SELECT * FROM TABLE_A

/* Returns all matched rows in both table_A and table_B*/

SELECT * FROM TABLE_A INTERSECT SELECT * FROM TABLE_B

Case 2: Unequal number of columns in two tables.

For example Table A has 5 columns and Table B has 10 Columns which 5 out of 10 columns are equal. Even though tables have unequal columns still you can use EXCEPT and INTERSECT to compare those 5 columns data

EX: SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE_A EXCEPT SELECT COL1, COL2, COL3, COL4, COL5 FROM TABLE_B

Case 3: Comparing tables in two different remote servers.

In this case you can use linked server as shown below

EX: Select * from Table_A EXCEPT select * from openquery(LinkedServer_Name, ‘Select * from Table_B’)

MSDN Reference: EXCEPT and INTERSECT

Using Command Line Utility (tablediff.exe):

The tablediff utility is used to compare the data in two tables. One luckier thing is it ships with SQL server for other replication purposes but we can make use of it for our purpose. It not only finds the unmatched data in two tables but also generates the script to synchronize those tables in comparison. This utility can be used from the command prompt or in a batch file to perform the following tasks:

A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
Perform a fast comparison by only comparing row counts and schema.
Perform column-level comparisons.
Generate a Transact-SQL script to synchronize unmatched rows on target table.
Log results to an output file or into a table in the destination database.

This tool can be found under following path in SQL server 2008 and 2011

C:\Program Files\Microsoft SQL Server\100\COM ‘tablediff.exe’ Or if you cannot find the exact path to the location Open CMD> type dir /s tablediff.exe> then it displays the path to the tablediff.exe application.

Syntax: Open CMD and Use below syntax to run tablediff.exe utility

“C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe” -sourceserver MyServer1

-sourcedatabase MyDatabase1

-sourcetable MyTable1

-destinationserver MyServer1

-destinationdatabase MyDatabase1

-destinationtable MyTable2

-et DiffsTable

Thanks for reading this post….. Follow @sqlfrndz

ARTICLES, DBA SCRIPTS

← Older posts

SQL-BOX.COM

Category Archives: ARTICLES

How to Delete Millions of Rows From SQL Server Table ?

SQL server updates or service pack hung or failed to install!!

Finding top 10 large tables in each database in an instance of SQL server

Always – ON Availability group Alerts

What is Parameter Sniffing in simple words?

Script to find the fragmentation level of specific table and its indexes in SQL Server

Track DML Changes Using after Trigger for Update, Delete and Insert rows- Capture Changed data rows using T-sql

Compare Two Tables Using T-SQL or Command Line Utility (tablediff.exe)

Pavan K Srirangam

Follow Blog via Email

Recent Posts

Top Posts- Most Read

Archives

Blog Stats

October 2017
S	M	T	W	T	F	S
« Aug
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

Category Archives: ARTICLES

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Rate this:

Share this:

Like this:

Pavan K Srirangam

Follow Blog via Email

Recent Posts

Top Posts- Most Read

Archives

Blog Stats