I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.
Example:
----Following script will recompile all the stored procedure on table Sales.Customer in AdventureWorks database.
USE AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO
----Following script will recompile specific stored procedure uspGetBillOfMaterials only.
USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetBillOfMaterials';
GO
Monday, November 29, 2010
Wednesday, November 24, 2010
How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008
In this article, server A and server B are different servers. Additionally, both server A and server B are running SQL Server 2005.
Note This information also applies to SQL Server 2008.
After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B. Additionally, the users may receive the following error message:
Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)
This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.
To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:
On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
Open a new Query Editor window, and then run the following script.
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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.
1. Run the following statement.
EXEC sp_help_revlogin
The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
2. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.
Important Before you go to step 5, review the information in the "Remarks" section.
3. Open a new Query Editor window, and then run the output script that is generated in step 3.
Back to the top
Remarks
Review the following information before you run the output script on the instance on server B:
• Review the output script carefully. If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. For more information about how to resolve these orphaned users, click the following article number to view the article in the Microsoft Knowledge Base:
240872 How to resolve permission issues when you move a database between servers that are running SQL Server
If server A and server B are in the same domain, the same SID is used. Therefore, the users are not likely to be orphaned.
• In the output script, the logins are created by using the encrypted password. This is because of the HASHED argument in the CREATE LOGIN statement. This argument specifies that the password that is entered after the PASSWORD argument is already hashed.
• By default, only a member of the sysadmin fixed server role can run a SELECT statement from the sys.server_principals view. Unless a member of the sysadmin fixed server role grants the necessary permissions to the users, the users cannot create or run the output script.
• The steps in this article do not transfer the default database information for a particular login. This is because the default database may not always exist on server B. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.
• The sort order of server A may be case insensitive, and the sort order of server B may be case sensitive. In this case, the users must type all the letters in the passwords as uppercase letters after you transfer the logins and the passwords to the instance on server B.
Alternatively, the sort order of server A may be case sensitive, and the sort order of server B may be case insensitive. In this case, the users cannot log in by using the logins and the passwords that you transfer to the instance on server B unless one of the following conditions is true:
o The original passwords contain no letters.
o All the letters in the original passwords are uppercase letters.
The sort order of both server A and server B may be case sensitive, or the sort order of both server A and server B may be case insensitive. In these cases, the users do not experience a problem.
• A login that already is in the instance on server B may have a name that is the same as a name in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15025, Level 16, State 1, Line 1
The server principal 'MyLogin' already exists.
Similarly, a login that already is in the instance on server B may have a SID that is the same as a SID in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15433, Level 16, State 1, Line 1
Supplied parameter sid is in use.
Therefore, you must do the following:
1. Review the output script carefully.
2. Examine the contents of the sys.server_principals view in the instance on server B.
3. Address these error messages accordingly.
• In SQL Server 2005, the SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement.
Note This information also applies to SQL Server 2008.
After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, the users may not be able to log in to the database on server B. Additionally, the users may receive the following error message:
Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)
This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.
To transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B, follow these steps:
On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
Open a new Query Editor window, and then run the following script.
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 (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
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 @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
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 ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Note This script creates two stored procedures in the master database. The two stored procedures are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure.
1. Run the following statement.
EXEC sp_help_revlogin
The output script that is generated by the sp_help_revlogin stored procedure is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
2. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.
Important Before you go to step 5, review the information in the "Remarks" section.
3. Open a new Query Editor window, and then run the output script that is generated in step 3.
Back to the top
Remarks
Review the following information before you run the output script on the instance on server B:
• Review the output script carefully. If server A and server B are in different domains, you have to modify the output script. Then, you have to replace the original domain name with the new domain name in the CREATE LOGIN statements. The integrated logins that are granted access in the new domain do not have the same SID as the logins in the original domain. Therefore, the users are orphaned from these logins. For more information about how to resolve these orphaned users, click the following article number to view the article in the Microsoft Knowledge Base:
240872 How to resolve permission issues when you move a database between servers that are running SQL Server
If server A and server B are in the same domain, the same SID is used. Therefore, the users are not likely to be orphaned.
• In the output script, the logins are created by using the encrypted password. This is because of the HASHED argument in the CREATE LOGIN statement. This argument specifies that the password that is entered after the PASSWORD argument is already hashed.
• By default, only a member of the sysadmin fixed server role can run a SELECT statement from the sys.server_principals view. Unless a member of the sysadmin fixed server role grants the necessary permissions to the users, the users cannot create or run the output script.
• The steps in this article do not transfer the default database information for a particular login. This is because the default database may not always exist on server B. To define the default database for a login, use the ALTER LOGIN statement by passing in the login name and the default database as arguments.
• The sort order of server A may be case insensitive, and the sort order of server B may be case sensitive. In this case, the users must type all the letters in the passwords as uppercase letters after you transfer the logins and the passwords to the instance on server B.
Alternatively, the sort order of server A may be case sensitive, and the sort order of server B may be case insensitive. In this case, the users cannot log in by using the logins and the passwords that you transfer to the instance on server B unless one of the following conditions is true:
o The original passwords contain no letters.
o All the letters in the original passwords are uppercase letters.
The sort order of both server A and server B may be case sensitive, or the sort order of both server A and server B may be case insensitive. In these cases, the users do not experience a problem.
• A login that already is in the instance on server B may have a name that is the same as a name in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15025, Level 16, State 1, Line 1
The server principal 'MyLogin' already exists.
Similarly, a login that already is in the instance on server B may have a SID that is the same as a SID in the output script. In this case, you receive the following error message when you run the output script on the instance on server B:
Msg 15433, Level 16, State 1, Line 1
Supplied parameter sid is in use.
Therefore, you must do the following:
1. Review the output script carefully.
2. Examine the contents of the sys.server_principals view in the instance on server B.
3. Address these error messages accordingly.
• In SQL Server 2005, the SID for a login is used as the basis for implementing database-level access. A login may have two different SIDs in two different databases on a server. In this case, the login can only access the database that has the SID that matches the SID in the sys.server_principals view. This problem may occur if the two databases are consolidated from two different servers. To resolve this problem, manually remove the login from the database that has a SID mismatch by using the DROP USER statement. Then, add the login again by using the CREATE USER statement.
Friday, November 19, 2010
SQL Server 2008: Hot-Add CPU (and affinity masks)
SQL Server 2005 introduced the concept of hot-add memory, to allow for dynamic workload handling. SQL Server 2008 increases these capabilities by adding hot-add CPU as well. 'Hot-add' means being able to plug in a CPU while the machine is running and then reconfigure SQL Server to make use of the CPU ONLINE! (i.e. no application downtime required at all)
There are a few restrictions:
You need a 64-bit system that support hot-add CPU (obviously :-))
You need Enterprise Edition of SQL Server 2008
You need Windows Server Datacenter or Enterprise Edition
When you plug in the new CPU, SQL Server won't automatically start using it. If you think about it, it can't - you may not want that CPU to be used by SQL Server - so it has to be told that it can use it. You do that by setting the appropriate affinity masks and then running a RECONFIGURE.
Ok - now we come to the bit that needs the "(and affinity masks)" in the title. What's an affinity mask? In a nutshell, it's a bitmap of all the CPUs on the machine that specifies which ones are available for general SQL Server Engine use, and which ones are available only for SQL Server I/O. There are two kinds of masks, a regular affinity mask, and an affinity I/O mask. The regular affinity mask, if all zeroes, says that Windows decides who get's what CPU when. If the affinity mask is non-zero, then there's a bit per CPU. If it's set to 1 then SQL Server can use the CPU. If a bit is set to 1 in the affinity I/O mask, then the CPU can only be used for I/O. A bit cannot be set in both masks. Now - an affinity mask is 32-bits wide, so if you have more than 32 CPUs, you need to use two more affinity masks, called affinity64 and affinity64 I/O. These do the same thing but for CPUs 32-63 on the machine.
There are a few restrictions:
You need a 64-bit system that support hot-add CPU (obviously :-))
You need Enterprise Edition of SQL Server 2008
You need Windows Server Datacenter or Enterprise Edition
When you plug in the new CPU, SQL Server won't automatically start using it. If you think about it, it can't - you may not want that CPU to be used by SQL Server - so it has to be told that it can use it. You do that by setting the appropriate affinity masks and then running a RECONFIGURE.
Ok - now we come to the bit that needs the "(and affinity masks)" in the title. What's an affinity mask? In a nutshell, it's a bitmap of all the CPUs on the machine that specifies which ones are available for general SQL Server Engine use, and which ones are available only for SQL Server I/O. There are two kinds of masks, a regular affinity mask, and an affinity I/O mask. The regular affinity mask, if all zeroes, says that Windows decides who get's what CPU when. If the affinity mask is non-zero, then there's a bit per CPU. If it's set to 1 then SQL Server can use the CPU. If a bit is set to 1 in the affinity I/O mask, then the CPU can only be used for I/O. A bit cannot be set in both masks. Now - an affinity mask is 32-bits wide, so if you have more than 32 CPUs, you need to use two more affinity masks, called affinity64 and affinity64 I/O. These do the same thing but for CPUs 32-63 on the machine.
Thursday, November 18, 2010
sequence for restoring differential database backups
The sequence for restoring differential database backups is:
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.
Restore the most recent database backup.
Restore the last differential database backup.
Apply all transaction log backups created after the last differential database backup was created if you use Full or Bulk-Logged Recovery.
Wednesday, November 17, 2010
SQL Server 2005 Recovery Models
SQL Server 2005 Recovery Models
Full Recovery Model
Bulk-Logged Recovery Model
Simple Recovery Model
Full Recovery Model
The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.
For heavy OLTP databases, there is overhead associated with logging all of the transactions, and the transaction log must be continually backed up to prevent it from getting too large.
Benefits:
Disadvantages:
Bulk-Logged Recovery Model
The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.
The following transactions are minimally logged in a Bulk-Logged Recovery Model:
SELECT INTO
bcp and BULK INSERT
CREATE INDEX
Text and Image operations
Benefits:
Simple Recovery Model
The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.
Benefits:
Full Recovery Model
Bulk-Logged Recovery Model
Simple Recovery Model
Full Recovery Model
The Full Recovery Model is the most resistant to data loss of all the recovery models. The Full Recovery Model makes full use of the transaction log – all database operations are written to the transaction log. This includes all DML statements, but also whenever BCP or bulk insert is used.
- Most resistant to data loss
- Most flexible recovery options - including point in time recovery
Disadvantages:
- Can take up a lot of disk space
- Requires database administrator time and patience to be used properly
Bulk-Logged Recovery Model
The Bulk-Logged Recovery Model differs from the Full Recovery Model in that rows that are inserted during bulk operations aren’t logged – yet a full restore is still possible because the extents that have been changed are tracked.
bcp and BULK INSERT
CREATE INDEX
Text and Image operations
Benefits:
- Transaction log stays small
- Easier from an administration standpoint (don’t have to worry about transaction logs)
- Disadvantages:
- Not for production systems
- Point in time recovery not possible
- Least data resistant recovery model
Simple Recovery Model
The simple recovery model is the most open to data loss. The transaction log can’t be backed up and is automatically truncated at checkpoints. This potential loss of data is makes the simple recovery model a poor choice for production databases. This option can take up less disk space since the transaction log is constantly truncated.
- Transaction log stays small
- Easier from an administration standpoint (don’t have to worry about transaction logs)
- Disadvantages:
- Not for production systems
- Point in time recovery not possible
- Least data resistant recovery model
Tuesday, November 16, 2010
find Unused indexes in SQL Server 2005
USE AdventureWorks
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
This is only works for SQL Server 2005 only
GO
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC
GO
This is only works for SQL Server 2005 only
Monday, November 15, 2010
clustering in SQL Server
It is high avilability concept only. It does not provide any 'Fault Tolarene' (i.e, I have Data Base, in this some table is missing.. By using Clustering we cont restore that table data.)
Adv:
No down time i say ( i.e 99.9999%) not 100%.
Note:
It is totally related to Windows based only. Not Sql Server related.
Defination:
It is concept of providing high avilability. By using using this we can reduce down time.
Note:
After installation of Clustering the total sql server behaves like normal sqlserver. There is no change in behaviour.
Clustering supports only Server Operating Systems.
Requirements:
Quorum Drive:
All sql server installation things are stored.
FAQS:
1.What is Active/ Passive ?
2.Is sql server behaves different afetr we instal sql server in Cluster machine?
Ans: No, It behaves normal
3.How to take Offline/Online?
Ans: Right Click on Group--> Select Offline
4.What are the issues you faced?
Ans: The node getting down
5.What are the minimum IP's Required for Clustering?
Ans: For Two Cluster environment we need 6 IP's
6.How to trace failOver?
7.What happend We plugged out SAN Box Cable?
8.Is it support Load balancing?
Ans: No
9.What is the minimum storage space require for Quorum?
Ans:500 MB
10.How much time it takes come online from Offline?
Ans: 1 min
11.What is hart Beat?
Ans: It is a process of authenticating both nodes it alive or not
12.What is group?
Ans: A group is nothing but managing node resources seperatly
13.Is it posible to install Two Sql Server Instances in single Group?
Ans:No, This feature is avilable in 2008 version
14.How to Stop and Start SQl Servr Service in Cluster Environment?
Ans: From Cluadmin then Click on group-->Right Click on service appear in right side pannel and then click stop/start. dont go to services.msc and stop from there.
15. how to Check Is our Machine participating in Clustering?
Ans: Select serverproperty('IsCluster')
16.What is the Difference Between 32 bit and 64 bit?
17. How many cluster nodes are support in SQLSERVER 2005 with windows 2003 server?
Ans:8 nodes
18. How many cluster nodes are support in SQLSERVER 2008 with windows 2008 server?
Ans:16 nodes
19.How to Add node fro Cluster?
Ans: Down load from the link fro etailed information... ClickHere
Adv:
No down time i say ( i.e 99.9999%) not 100%.
Note:
It is totally related to Windows based only. Not Sql Server related.
Defination:
It is concept of providing high avilability. By using using this we can reduce down time.
Note:
After installation of Clustering the total sql server behaves like normal sqlserver. There is no change in behaviour.
Clustering supports only Server Operating Systems.
Requirements:
- Minimum Two machines(we cal this machine as NODE) with same Hardware (Same Ram, Same Hard drive, Same Processor)
- Same Server Operating system(win 2003server..) in each node(Machine)
- Active Directory in another machine(domain Controller)
- SAN Box(Shared Array) like External Hard Disk.
- 2 Network cards for each machine
- 6 IP's (1 for Public Cluster, 1 for Public sqlserver, 4 for Network cards)
Quorum Drive:
All sql server installation things are stored.
FAQS:
1.What is Active/ Passive ?
2.Is sql server behaves different afetr we instal sql server in Cluster machine?
Ans: No, It behaves normal
3.How to take Offline/Online?
Ans: Right Click on Group--> Select Offline
4.What are the issues you faced?
Ans: The node getting down
5.What are the minimum IP's Required for Clustering?
Ans: For Two Cluster environment we need 6 IP's
6.How to trace failOver?
7.What happend We plugged out SAN Box Cable?
8.Is it support Load balancing?
Ans: No
9.What is the minimum storage space require for Quorum?
Ans:500 MB
10.How much time it takes come online from Offline?
Ans: 1 min
11.What is hart Beat?
Ans: It is a process of authenticating both nodes it alive or not
12.What is group?
Ans: A group is nothing but managing node resources seperatly
13.Is it posible to install Two Sql Server Instances in single Group?
Ans:No, This feature is avilable in 2008 version
14.How to Stop and Start SQl Servr Service in Cluster Environment?
Ans: From Cluadmin then Click on group-->Right Click on service appear in right side pannel and then click stop/start. dont go to services.msc and stop from there.
15. how to Check Is our Machine participating in Clustering?
Ans: Select serverproperty('IsCluster')
16.What is the Difference Between 32 bit and 64 bit?
17. How many cluster nodes are support in SQLSERVER 2005 with windows 2003 server?
Ans:8 nodes
18. How many cluster nodes are support in SQLSERVER 2008 with windows 2008 server?
Ans:16 nodes
19.How to Add node fro Cluster?
Ans: Down load from the link fro etailed information... ClickHere
Saturday, November 13, 2010
SQLCODE4YOU: Select Query Life Cycle
SQLCODE4YOU: Select Query Life Cycle: "We need to look more into select query execution. How it take query from client and how to perform internal operations and soon. Download p..."
Select Query Life Cycle
We need to look more into select query execution.
How it take query from client and how to perform internal operations and soon.
Download ppt for Step by step understatding of select query life cycle.
Click Here To Download PPT(power point presentation)
How it take query from client and how to perform internal operations and soon.
Download ppt for Step by step understatding of select query life cycle.
Click Here To Download PPT(power point presentation)
Friday, November 12, 2010
1486 Error in Mirroring
Error Description:
Database mirroring transport is disabled in the endpoint configuration ............
Solution:
ALTER ENDPOINT [mirroring] STATE= STARTED
Here 'mirroring' is my end point name..
Database mirroring transport is disabled in the endpoint configuration ............
Solution:
ALTER ENDPOINT [mirroring] STATE= STARTED
Here 'mirroring' is my end point name..
Monday, November 1, 2010
Enabling Dedicated Administrator Connection Feature in SQL Server 2008
http://www.mssqltips.com/tip.asp?tip=1801
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT
When to use @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT ?
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
To give you a quick overview....
» @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
» SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
» IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
@@IDENTITY
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
SCOPE_IDENTITY (Transact-SQL)
It returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
IDENT_CURRENT (Transact-SQL)
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. It returns NULL when the function is invoked on an empty table or on a table that has no identity column.
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
To give you a quick overview....
» @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
» SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.
» IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
@@IDENTITY
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
SCOPE_IDENTITY (Transact-SQL)
It returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.
For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.
Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.
IDENT_CURRENT (Transact-SQL)
IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. It returns NULL when the function is invoked on an empty table or on a table that has no identity column.
Extents and pages
What are extents and pages in the context of SQL Server 2005 ? What are the different types extents and pages in SQL Server 2005 ?
An extent is a block of eight pages totaling 64KB in size. Because the extent is the basic unit of allocation for tables and indexes and all objects are saved in a table of some kind, all objects are stored in extents.
SQL Server has two types of extents:
» Uniform: In uniform extents, all eight pages are used by the same object.
» Mixed:Mixed extents are used by objects that are too small to take up eight pages, so more than one object is stored in the extent.
When a table or an index needs additional storage space, another extent is allocated to that object. A new extent will generally not be allocated for a table or index until all pages on that extent have been used. This process of allocating extents rather than individual pages to objects serves two useful purposes. First, the time-consuming process of allocation takes place in one batch rather than forcing each allocation to occur whenever a new page is needed. Second, it forces the pages allocated to an object to be at least somewhat contiguous. If pages were allocated directly, on an asneeded basis, then pages belonging to a single object would not be next to each other in the data file. Page 1 might belong to table 1, page 2 might belong to index 3, page 3 might belong to table 5, and so on. This is called fragmentation. Fragmentation can have a significant negative impact on performance. When pages for a single object are contiguous, though, reads and writes can occur much more quickly.
PAGES
At the most fundamental level, everything in SQL Server is stored on an 8KB page. The page is the one common denominator for all objects in SQL Server. Many types of pages exist, but every page has some factors in common. Pages are always 8KB in size and always have a header, leaving about 8,060 bytes of usable space on every page.
SQL Server has eight primary types of pages:
Data pages
Data pages hold the actual database records. The data page is 8,192 bytes, but only 8,060 of those bytes are available for data storage because a header at the beginning of each data page contains information about the page itself. Rows are not allowed to span more than one page, but if you have variable-length columns that exceed this limit, you can move them to a page in the ROW_OVERFLOW_DATA allocation unit.
Index pages
Index pages store the index keys and levels making up the entire index tree. Unlike data pages, you have no limit for the total number of entries you can make on an index page.
Text/image pages
Text and image pages hold the actual data associated with text, ntext, and image datatypes. When a text field is saved, the record will contain a 16-byte pointer to a linked list of text pages that hold the actual text data. Only the 16-byte pointer inside the record is counted against the 8,060-byte record-size limit.
Global Allocation Map pages
The Global Allocation Map (GAM) page type keeps track of which extents in a data file are allocated and which are still available.
Index Allocation Map pages
Index Allocation Map (IAM) pages keep track of what an extent is being used for—specifically, to which table or index the extent has been allocated.
Page Free Space pages
This is not an empty page; rather, it is a special type of page that keeps track of free space on all the other pages in the database. Each Page Free Space page can keep track of the amount of free space of up to 8,000 other pages.
Bulk Changed Map pages
This page contains information about other pages that have been modified by bulk operations (such as BULK INSERT) since the last BACKUP LOG statement.
Differential Changed Map pages
This page contains information about other pages that have changes since the last BACKUP DATABASE statement.
An extent is a block of eight pages totaling 64KB in size. Because the extent is the basic unit of allocation for tables and indexes and all objects are saved in a table of some kind, all objects are stored in extents.
SQL Server has two types of extents:
» Uniform: In uniform extents, all eight pages are used by the same object.
» Mixed:Mixed extents are used by objects that are too small to take up eight pages, so more than one object is stored in the extent.
When a table or an index needs additional storage space, another extent is allocated to that object. A new extent will generally not be allocated for a table or index until all pages on that extent have been used. This process of allocating extents rather than individual pages to objects serves two useful purposes. First, the time-consuming process of allocation takes place in one batch rather than forcing each allocation to occur whenever a new page is needed. Second, it forces the pages allocated to an object to be at least somewhat contiguous. If pages were allocated directly, on an asneeded basis, then pages belonging to a single object would not be next to each other in the data file. Page 1 might belong to table 1, page 2 might belong to index 3, page 3 might belong to table 5, and so on. This is called fragmentation. Fragmentation can have a significant negative impact on performance. When pages for a single object are contiguous, though, reads and writes can occur much more quickly.
PAGES
At the most fundamental level, everything in SQL Server is stored on an 8KB page. The page is the one common denominator for all objects in SQL Server. Many types of pages exist, but every page has some factors in common. Pages are always 8KB in size and always have a header, leaving about 8,060 bytes of usable space on every page.
SQL Server has eight primary types of pages:
Data pages
Data pages hold the actual database records. The data page is 8,192 bytes, but only 8,060 of those bytes are available for data storage because a header at the beginning of each data page contains information about the page itself. Rows are not allowed to span more than one page, but if you have variable-length columns that exceed this limit, you can move them to a page in the ROW_OVERFLOW_DATA allocation unit.
Index pages
Index pages store the index keys and levels making up the entire index tree. Unlike data pages, you have no limit for the total number of entries you can make on an index page.
Text/image pages
Text and image pages hold the actual data associated with text, ntext, and image datatypes. When a text field is saved, the record will contain a 16-byte pointer to a linked list of text pages that hold the actual text data. Only the 16-byte pointer inside the record is counted against the 8,060-byte record-size limit.
Global Allocation Map pages
The Global Allocation Map (GAM) page type keeps track of which extents in a data file are allocated and which are still available.
Index Allocation Map pages
Index Allocation Map (IAM) pages keep track of what an extent is being used for—specifically, to which table or index the extent has been allocated.
Page Free Space pages
This is not an empty page; rather, it is a special type of page that keeps track of free space on all the other pages in the database. Each Page Free Space page can keep track of the amount of free space of up to 8,000 other pages.
Bulk Changed Map pages
This page contains information about other pages that have been modified by bulk operations (such as BULK INSERT) since the last BACKUP LOG statement.
Differential Changed Map pages
This page contains information about other pages that have changes since the last BACKUP DATABASE statement.
Raid levels
RAID levels
RAID-0
RAID-0 uses disk striping; that is, it writes data across multiple hard disk partitions in what is called A stripe set. This can greatly improve speed because multiple hard disks are working at the same time. You can implement RAID-0 through the use of Windows Server software or third-party hardware. Although RAID-0 gives you the best speed, it does not provide any fault-tolerance. If one of the hard disks in the stripe set is damaged, you lose all of your data. Because of the lack of faulttolerance, Microsoft doesn’t recommend storing any of your SQL Server data on RAID-0 volumes.
RAID-1
RAID-1 uses disk mirroring. Disk mirroring actually writes your information to disk twice — once to the primary file and once to the mirror. This gives you excellent fault-tolerance, but it is fairly slow, because you must write to disk twice. Windows Server allows you to implement RAID-1 with a single controller, or you can use a controller for each drive in the mirror, commonly referred to as disk duplexing. This is the recommended place for storing your transaction logs because RAID-1 gives fast sequential write speed (writing data in sequence on the disk rather than jumping from one empty spot to the next), a requirement for transaction logs.
RAID-5
RAID-5—striping with parity — writes data to the hard disk in stripe sets. Parity checksums will be written across all disks in the stripe set. This gives you excellent fault-tolerance as well as excellent speed with a reasonable amount of overhead. You can use the parity checksums to re-create information lost if a single disk in the stripe set fails. If more than one disk in the stripe set fails, however, you will lose all your data. Although Windows Server supports RAID-5 in a software implementation, a hardware implementation is faster and more reliable, and we suggest you use it if you can afford it. Microsoft recommends storing your data files on this type of RAID because data files require fast read speed as opposed to transaction logs, which need fast write speed.
RAID-10
You should use RAID-10 (sometimes referred to as RAID 0+1) in mission-critical systems that require 24/7 uptime and the fastest possible access. RAID-10 implements striping with parity as in RAID-5 and then mirrors the stripe sets. So, you get the incredible speed and faulttolerance, but RAID-10 has a drawback. With this type of RAID you get the added expense of using more than twice the disk space of RAID-1. Then again, we are talking about a situation that can afford no SQL Server downtime
RAID-0
RAID-0 uses disk striping; that is, it writes data across multiple hard disk partitions in what is called A stripe set. This can greatly improve speed because multiple hard disks are working at the same time. You can implement RAID-0 through the use of Windows Server software or third-party hardware. Although RAID-0 gives you the best speed, it does not provide any fault-tolerance. If one of the hard disks in the stripe set is damaged, you lose all of your data. Because of the lack of faulttolerance, Microsoft doesn’t recommend storing any of your SQL Server data on RAID-0 volumes.
RAID-1
RAID-1 uses disk mirroring. Disk mirroring actually writes your information to disk twice — once to the primary file and once to the mirror. This gives you excellent fault-tolerance, but it is fairly slow, because you must write to disk twice. Windows Server allows you to implement RAID-1 with a single controller, or you can use a controller for each drive in the mirror, commonly referred to as disk duplexing. This is the recommended place for storing your transaction logs because RAID-1 gives fast sequential write speed (writing data in sequence on the disk rather than jumping from one empty spot to the next), a requirement for transaction logs.
RAID-5
RAID-5—striping with parity — writes data to the hard disk in stripe sets. Parity checksums will be written across all disks in the stripe set. This gives you excellent fault-tolerance as well as excellent speed with a reasonable amount of overhead. You can use the parity checksums to re-create information lost if a single disk in the stripe set fails. If more than one disk in the stripe set fails, however, you will lose all your data. Although Windows Server supports RAID-5 in a software implementation, a hardware implementation is faster and more reliable, and we suggest you use it if you can afford it. Microsoft recommends storing your data files on this type of RAID because data files require fast read speed as opposed to transaction logs, which need fast write speed.
RAID-10
You should use RAID-10 (sometimes referred to as RAID 0+1) in mission-critical systems that require 24/7 uptime and the fastest possible access. RAID-10 implements striping with parity as in RAID-5 and then mirrors the stripe sets. So, you get the incredible speed and faulttolerance, but RAID-10 has a drawback. With this type of RAID you get the added expense of using more than twice the disk space of RAID-1. Then again, we are talking about a situation that can afford no SQL Server downtime
Troubleshooting Transactional Replication
Troubleshooting Transactional Replication
Here is the scenario…
I received an alert today that one of the order tables (articles) on a subscriber would not allow NULL values. Well nothing was changed on the subscriber side so I had to figure out where the issue was.
Fortunately, the Order ID was logged in the error message so on the distribution server\database and all of the other fields for that record had NULL values. I wanted to remove the offending rows from replication so I ran the following on the distribution database: exec sp_browsereplcmds. This returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. From here you can grab the xact_seqno field from the result set and then you are able to remove the offending rows from the Distribution.dbo.msrepl_transactions and Distribution.dbo.msrepl_commands tables. Here are the steps:
Step 1 – Stop the SQL Server Agent on the Distribution Server this will stop all replication transactions from going into the distribution database.
Step 2 – Run the following T-SQL commands in the distribution database
use distribution
go
delete from msrepl_transactions where xact_seqno =
go
delete from msrepl_commands where xact_seqno =
go
Step 3 – Start the SQL Server Agent on the distribution server this will start all replication transactions to go into the distribution database.
Step 4 – If need be, restart the synchronization of the distribution agent on the distribution server. Replication should now continue.
Here is the scenario…
I received an alert today that one of the order tables (articles) on a subscriber would not allow NULL values. Well nothing was changed on the subscriber side so I had to figure out where the issue was.
Fortunately, the Order ID was logged in the error message so on the distribution server\database and all of the other fields for that record had NULL values. I wanted to remove the offending rows from replication so I ran the following on the distribution database: exec sp_browsereplcmds. This returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. From here you can grab the xact_seqno field from the result set and then you are able to remove the offending rows from the Distribution.dbo.msrepl_transactions and Distribution.dbo.msrepl_commands tables. Here are the steps:
Step 1 – Stop the SQL Server Agent on the Distribution Server this will stop all replication transactions from going into the distribution database.
Step 2 – Run the following T-SQL commands in the distribution database
use distribution
go
delete from msrepl_transactions where xact_seqno =
go
delete from msrepl_commands where xact_seqno =
go
Step 3 – Start the SQL Server Agent on the distribution server this will start all replication transactions to go into the distribution database.
Step 4 – If need be, restart the synchronization of the distribution agent on the distribution server. Replication should now continue.
Subscribe to:
Posts (Atom)