Archive for April, 2010

How to install SQL Server 2008 32-BIT on Windows 2008 64 BIT

April 27, 2010 Leave a comment

While doing some R&D work I came across this that how can we install SQL Server 2008 32 BIT version on windows server 2008 Ent.Edition 64 BIT.Though of just write about it.

Click on the SQL Server 2008 installtion (setup.exe)

In the SQL Server installation centre you will see options menu on the left panel.Click on the options there you will see all three version of SQL Server 2008 , select the version u want to install.

Note : SQL Server 2008 64  BIT can’t be installed on 32 BIT OS.

Get undistributed Transaction List (Transactional Replication)

April 23, 2010 Leave a comment

Thursday i was too happy that weekend started as I took a leave on friday, as i stepped out from my technology room i got an unexpected call from the technology datacenter hearing what he said said my dream of weekend shattered.

 The problem was one of the SQL Server on Cluster failed .On the server we have a huge OLTP database over 1.2 TB which was configured with transactional replication on a subscriber db on different server.

 The issues where will the transaction get replicated from distribution when the server will be up and running or will there be any problem doing so.

When the server came up the replication was messed up, Transaction where getting replicated from publisher to distributor but failed to replicate the same transaction from distributor to subscriber.

I spent enough time to know what all transaction failed to replicate and what the way to replicate the same at subscriber seamlessly.

Later hopefully I managed to resolve this with one transaction loss (identified one).

How to know total number of transaction which is not replicated to subscriber?

select * From msdistribution_status

article_id agent_id UndelivCmdsInDistDB DelivCmdsInDistDB
1 1                0                   1


How to know details of undelivered transactions?

—–Code has been copied and modified from Paul’s Blog————–

declare @xact_seqno varbinary(16)
select @xact_seqno = max(xact_seqno) from MSsubscriptions
inner join MSpublications
on MSpublications.publication_id = MSsubscriptions.publication_id
inner join MSdistribution_history
on MSdistribution_history.agent_id = MSsubscriptions.agent_id
Where subscriber_db = ‘reptest_test’
AND Publication = ‘Pub_test’

Print @xact_seqno

declare @str varchar(255)
set @str = master.dbo.fn_varbintohexstr (@xact_seqno)
set @str = left(@str, len(@str) – 8)
–if exists(select object_id(‘tempdb..#trancommands’)) drop table #trancommands
create table #trancommands
(xact_seqno varbinary(16) null,
originator_srvname sysname null,
originator_db sysname null,
article_id int null,
type int null,
partial_command bit null,
hashkey int null,
originator_publication_id int null,
originator_db_version int null,
originator_lsn varbinary(16) null,
command nvarchar(1024) null,
command_id int) 

insert into #trancommands
exec sp_browsereplcmds @xact_seqno_start = @str
select * from #trancommands where xact_seqno > @xact_seqno
drop table #trancommands

Here we got lot of transactions which were in the waiting list (waiting to be replicated), Now we have to identify those transaction which need to be marked as replicated or deleted.

I put lot of efforts to identify those transactions and lucky we found one such transaction.

Now the question is how to mark that transaction as replicated or delete it from the distribution list.

I posted the question on MSDN SQL Server Forum and in a day time I got my answer.

We can delete the transaction using the below code:

DELETE FROM  distribution.dbo.msrepl_commands  where xact_seqno=put the xact_seqno which need to be deleted.

Doing so my transaction replication started without pain and we lost nothing as I have deleted the same transaction from publisher also.

I wana thank though this BLOG to Wissam and Hilary on helping me on this.

Automating Sliding Window Table Partitioning

April 22, 2010 Leave a comment

Tables configured with Partition Scheme storage

April 20, 2010 Leave a comment

How to get information about tables configured with database partition scheme or table configured to use database partitioning using partition scheme and function.

I have a database with more than 3000 tables which are being partitioned, while automating the partitioning feature I came across a situation when I need info about what all tables are configured with database partitioning (partition scheme).

Below code helped me doing so, Write this blog thinking this will help others as well.

DECLARE @PartitionSchemeId varchar (max)
 SELECT @PartitionSchemeId=data_space_id from sys.data_spaces where type=’PS’

Print @PartitionSchemeId

 SELECT * FROM sys.tables where lob_data_space_id=@PartitionSchemeId



Performing Remote Database Backup using linked server

April 19, 2010 Leave a comment

To perform the remote server database backup using linked server

Replicate Indexed Views as Table (Transactional Replication)

April 14, 2010 6 comments

In this blog we will talk about why to create an indexed view, how to create an index view and replicate the index view as a table to existing or new Transactional Replication.

While working with one of my client running PeopleSoft Application having database on SQL Server 2005 wanted to segregate the reporting part. To do so we proposed Transactional Replication and use the subscriber database as Reporting Database.

The problem came up was number of tables doesn’t have any primary key and they were not ready to create any type of Keys on the table. So, the antonym is how to add those tables as article in the publication.

Searching the BOL we came across about Indexed view. Now the question is how we can use it.

We took one of the tables which doesn’t have a primary key and created an indexed view with SCHEMA BINDING referencing table.

SCHEMA BINDING: Binds the view to the schema of the underlying base tables.

Creating Indexed View with Schema Binding

USE <Database Name>
SELECT EmloyeeID, SalDay, LastSalMon, SalYrs
FROM dbo.Emp_SalTrans

Once the Indexed View is created,Create Unique Clustered Index on the same

Vw_Emp_SalTrans_rep (EmloyeeID, SalDay, LastSalMon, SalYrs)


Confirm that table on which view has been created exists on both the publisher and subscriber.
Doing so is a mandatory step since indexed view works on the principle of a log based operation i.e whatever DMLs are being committed on the production table gets replicated to the subscriber through the indexed view.


Add the indexed view as an article in existing transactional Replication

To added index view in the exiting transactional publication we will use sp_addartical stored procedure as GUI doesn’t much option to do so.


Generate the snasphot of the publication with re-initialize all subscription.

Once is snapshot is generated the same get applied to the subscription database.

Stored Procedure sp_addartical has lot of more option , so to explore more search for sp_addartical in BOL.




How to Create Indexed Views

April 13, 2010 Leave a comment

An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. They are not well-suited for underlying data sets that are frequently updated

SELECT Col1,Col2,Col3
FROM dbo.tablename

create unique clustered index indexname on dbo.viewname (colnames)