Will Truncate Command be carried forward to Secondary Server in Log Shipping?

Truncate command will not delete data row by row hence deleted data will not be logged in Transaction Log. In Log shipping, transaction log backups will be transferred to the secondary server. So the natural question is whether truncate command will be carried forward to Secondary Server.

Let us examine, what is happening to transaction log during the truncate command. Following is the part of transaction log you will see when you issue a truncate statement.

 

Current LSN	Operation	Context
0000001c:00000014:0005	LOP_MODIFY_ROW	LCX_PFS
0000001c:00000014:0006	LOP_MODIFY_ROW	LCX_PFS
0000001c:00000014:0007	LOP_FORMAT_PAGE	LCX_IAM
0000001c:00000014:0008	LOP_HOBT_DELTA	LCX_NULL
0000001c:00000014:0009	LOP_MODIFY_ROW	LCX_IAM

PFS – Page Free Space

IAM – Index Allocation Maps

The actual process for Truncate Table is to de-allocate the pages assigned to a table, this de-allocating part will be captured in log file. So in the, transaction log backup, this statement will be captured and will be transferred to the secondary server. This means that Truncate command will be carried forward to the Secondary Server.

Similarly, in mirroring, transaction log backup restoring, transactional replication truncate statement will be transferred to the intend targets.

PN: table is de-allocated by unhooking the IAM chains and then de-allocating the individual pages and extents using a background task – a process called deferred-drop, to avoid running out of locks during the de-allocation process.

Brad's Sure DBA Checklist

Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don’ts. It provides a handy reminder. Brad has come up with a new update to his famous checklist

http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/?utm_source=simpletalk&utm_medium=email&utm_content=BradChecklist-20100125&utm_campaign=SQL

Version Information on SQL Server

If you want to find out version information, you can use @@Version function. However, in SQL Server 2005, you won’t get the service pack level of the SQL Server.

If you run SELECT @@VERSION in SQL Server 2005, you will get following output.

Microsoft SQL Server 2005 - 9.00.4207.00 (X64) Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

PN: here Service Pack 2 means service pack of the operating system, not the service pack of the SQL Server. To find out the service pack level in SQL Server 2005, you need to run following T-SQL.

SELECT SERVERPROPERTY('ProductLevel') ServicePack

In SQL Server 2008 this is what you get for SELECT @@VERSION.

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Copyright (c) 1988-2008 Microsoft Corporation Developer Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)

In this you can see that you will get the Service pack level from the @@VERSION.

Apart from above commands, following stored procedures also give you the server information.

EXEC master..xp_msver

EXEC sp_server_info

BUG: Naming convention issue with SQL Server Configuration Manager

In the first image, SQL Server Configuration Manager shortcut, you have SQL with all caps. In the next image, which is the SQL Server Configuration Manager tool, in the title it shows as Sql with title case.

BUG: SQL Server 2008 Installation

In the SQL Server 2008 installation, there is service accounts configuration. In this SQL Server Analysis Service and SQL Server Reporting Services has a case error as shown in the following image.

 

An Error Occurred during decryption when creating a linked server.

When linked server is created, following error is occurring.

An error occurred during decryption.

PN: Also, when configuring distribution for replication following error occurred.

An error occurred during decryption.

There is no remote user 'distributor_admin' mapped to local user '(null)' from the remote server 'repl_distributor'.

Changed database context to 'master'. (Microsoft SQL Server, Error: 15466)

This also due to unable to create linked server named repl_distributor.

The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

Solution is to regenerate the service master key with following command.

ALTER SERVICE MASTER KEY REGENERATE

In case above statement generates an error, you have to use with FORCE command. http://support.microsoft.com/kb/914261

ALTER SERVICE MASTER KEY FORCE REGENERATE

How to Generate Data Scripts for Your Tables

Generating Data into scripts is a common problem faced by database developers. Many times you need to use third party tools. With SQL Server 2008, there is an inbuilt feature to generate Data scripts. Follow the steps given below.

1. Right-Click the Database and Select Generate Scripts… from the Task context menu.
2. In the Script option dialog select True fro Script Data option. By default this is true. If you have logged into SQL Server 2005 server, still you can enable this option.

 

 

3. After this option next options are normal options that you will get.

Following is the sample script that was generated by using above option.

USE [AdventureWorks]

GO

/****** Object:  Table [HumanResources].[Department]    Script Date: 01/02/2010 21:32:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [HumanResources].[Department](

      [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,

      [Name] [dbo].[Name] NOT NULL,

      [GroupName] [dbo].[Name] NOT NULL,

      [ModifiedDate] [datetime] NOT NULL,

 CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

(

      [DepartmentID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [HumanResources].[Department] ON

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (1, N'Engineering', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (2, N'Tool Design', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (3, N'Sales', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (4, N'Marketing', N'Sales and Marketing', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (5, N'Purchasing', N'Inventory Management', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (6, N'Research and Development', N'Research and Development', CAST(0x00008C6900000000 AS DateTime))

INSERT [HumanResources].[Department] ([DepartmentID], [Name], [GroupName], [ModifiedDate]) VALUES (7, N'Production', N'Manufacturing', CAST(0x00008C6900000000 AS DateTime))

SET IDENTITY_INSERT [HumanResources].[Department] OFF

/****** Object:  Default [DF_Department_ModifiedDate]    Script Date: 01/02/2010 21:32:38 ******/

ALTER TABLE [HumanResources].[Department] ADD  CONSTRAINT [DF_Department_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

GO