Vinay Thakur

New Blog Location

2010-01-09T05:13:11Z

Hi Friends.

Thanx for your support. for this year onwords I will be bloging on my own website....

Hope to see you there

http://rdbmsexperts.com/Blogs/

Thanx.

Vinay

Error Log:

2009-12-26T01:02:45Z

Following steps happened when sql server starts… we can see that in error log::

1. Build of sql server and platform as Microsoft Sql server 2005 version. Edition on OS.

2. Windows process ID (kid):

3. Error log file location.

4. when last time sql was down and what spid it used.

5. New to sql server 2005, start up options either from register/command prompt.

6. –d Read “Master.mdf” file and store configuration into memory.

7. –e error log file

8. –l master.ldf file

9. Configuration of the system : CPU…

10. SQLOS subsystem initialization. memory and mode of operation DAC/light weighted pooling.

11. DTC Initialization attempt if any recovery require.

12. SQL Network Interface (SNI) initialize prepare server to listen connection.

13. Main background tread will start : " – LazyWriter, LogWriter, Checkpoint and database mirroring.

14. Ready to startup system database and user database (it goes with session(s)).

15. Configure Default trace if any.

16. Resource database is started.

17. Server starts – Service Broker and Mirroring. during connection starts.

18. Master startup stored procedure runs if any.

Happy Holiday – Merry Christmas.

Thanx.

Vinay

Reference:

Book: SQL Server 2005 Practical Troubleshooting: The Database Engine: Ken Henderson.

Remove Replication:

2009-12-17T03:45:57Z

Its quite some time that I blog… but good news is I have some good stuff to blog on… so could be you will see some good blog in pipeline soon.

I blog on how to configure the replication here with commands and a separate blog for replication setup here.

I might have discussed about how to remove replication on my earlier blog but would like to have separate blog for future reference so blogging this again about how to remove the replication, I know its very important to remove the replication properly.

Replication removal steps:

-----------------------------Process 1 –replication clean up on server

1. Remove the subscriber.

a.Go to Replication –>selection Publications->select Publisher, you will get the respective subscriber(s) for that publisher. select subscriber and right click –>delete

2 Remove the publisher.

Go to Replication –>selection Publications->select Publisher –>Right click and delete

after this I generally enable none on

Go to Replication –>–>selection Publications->Right click –>configuration of publisher, subscriber or distributor and enable none for all.

3 Remove the Distributor.

Go to Server Property-> Select Replication tab –> select “Disable” button –> this will open a wizard. go through the wizard and disable the distribution. it will drop the distribution database.

sp_dropdistributiondb 'distribution'

exec master..sp_dropdistributor – this will remove the replication monitor as well and logins

at the end I generally run

sp_removedbreplication ‘replicated database’ – to make sure we have completely remove the replication

Their is a great KB article which describe it in details.

http://support.microsoft.com/kb/324401

This will also solve the issue with following error:

Cannot drop the distribution database ‘distribution’ because it is currently in use.

-----------------------------Process 2

exec master..sp_dropdistributor @no_checks = 1

please use it with care as it will not check anything and will delete the distributor corresponding to that replication(subscriber/publisher/distributor including logins). at the same time its very fast, so if you want to just clean everything from the server specially on dev this is good.

http://msdn.microsoft.com/en-us/library/ms173516.aspx

-----------------------------Process 3 --check

Today I was working on Transaction replication on Sql server 2000 sp3a... found very weird thing happened I removed replication with above commands no replication onto the server but I was still getting below error:

Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 'test1' because it is used for replication.

I searched a lot but could not find much information, I also tried to detach the db and attached on different server but no luck.

finally found that "replinfo" column from "sysobjects" table

update sysobjects set replinfo=0 where replinfo=1

Thanx to Vyas for the great article.

http://vyaskn.tripod.com/repl_ans3.htm

I know you might have read the same in my blog but this will be one point for removal of replication and try to keep up to date or keep it with series.

Thanx.

Vinay

Twitter @thakurvinay

Transactional Replication Conversations –Must read

2009-12-04T04:09:29Z

Everyone who wants to know about how transaction replication works and how to improve the performance of transaction replication must read a great blog :

http://blogs.msdn.com/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx

Summary:

log reader read the transactions(Changes) with sp_replcmds, for xp_replcmds.

then log reader writes into distributor database using sp_MSadd_replcmds

Distributor reads commands using sp_MSget_repl_commands

Distributor writes command to subscriber using sp_MSupd, sp_MSins, sp_MSdel stored procedures

Log reader and distributor keeps the history at distributor database with MSlogreader_history and MSdistribution_history tables.

This is very important information everyone should know.

Thanx.

Vinay

Microsoft Release end dates:

2009-11-13T23:11:15Z

I realize that we all should know when the support of Microsoft sql server will expire. we should have this information handy. hence want to keep it in my Blog.

As everybody knows sql server 2000 support is already ended. but you know the extended support is exists till 2013… but it might be having some conditions.

The Sql server product is very stable now a days. but due to support expires and new features we have to go for next version. I still work on sql server 2000 and I like it. It is still very stable but as support ends we moved to sql server 2005.

Below link is having more information on this:

http://blogs.msdn.com/sqlreleaseservices/archive/2009/10/08/end-of-service-pack-support-for-sql-server-2005-sp2-and-sql-server-2008-rtm.aspx

Thanx.

Vinay

Compare Microsoft Sql Server with other DBMS/RDBMS

2009-11-05T04:53:51Z

Today I was just thinking about to know what is the difference between sql server vs.. other DBMS/RDBMS. so thought of documenting this.searched on net and found some very interesting links….

All RDBMS... Very good:
http://troels.arvin.dk/db/rdbms/

Comparison MS Sql Server with Other DBMS/RDBMS
http://www.mssqlcity.com/Articles/Compare/Compare.htm

>>MS Sql Server VS Oracle:

http://nocoug.org/download/2004-05/BegSQL.ppt

http://www.alinean.com/PDFs/Alinean-MicrosoftAndOracleTCAStudy.pdf

----- so many sites has this info.

I like this...
http://www.infoworld.com/d/data-management/real-difference-between-sql-server-and-oracle-755

>>MS Sql Server VS DB2
http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/ComparingSQLServer2005andDB28.2_DevelopmentProductivity.doc

>>MS sql server vs Informix

http://forums.databasejournal.com/showthread.php?t=19408

>>MS sql server vs. mysql (old).....
http://www.tometasoftware.com/mysql_vs_sqlserver.asp

>>Sql server VS PostgreSql

http://www.postgresonline.com/journal/index.php?/archives/130-Cross-Compare-of-PostgreSQL-8.4,-SQL-Server-2008,-MySQL-5.1.html
http://www.petefreitag.com/item/5.cfm
http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1222615,00.html

Please share something if you have any.

Thanx.

Vinay

twitter @ThakurVinay

http://vinay-thakur.spaces.live.com/

http://www.sqlservercentral.com/blogs/vinaythakur

Comparison Microsoft Sql Server with other DBMS/RDBMS

2009-11-05T04:53:51Z

Today I was just thinking about to know what is the difference between sql server vs.. other DBMS/RDBMS. so thought of documenting this.searched on net and found some very interesting links….

All RDBMS... Very good:
http://troels.arvin.dk/db/rdbms/

Comparison MS Sql Server with Other DBMS/RDBMS
http://www.mssqlcity.com/Articles/Compare/Compare.htm

>>MS Sql Server VS Oracle:

http://nocoug.org/download/2004-05/BegSQL.ppt

http://www.alinean.com/PDFs/Alinean-MicrosoftAndOracleTCAStudy.pdf

----- so many sites has this info.

I like this...
http://www.infoworld.com/d/data-management/real-difference-between-sql-server-and-oracle-755

>>MS Sql Server VS DB2
http://download.microsoft.com/download/a/4/7/a47b7b0e-976d-4f49-b15d-f02ade638ebe/ComparingSQLServer2005andDB28.2_DevelopmentProductivity.doc

>>MS sql server vs Informix

http://forums.databasejournal.com/showthread.php?t=19408

>>MS sql server vs. mysql (old).....
http://www.tometasoftware.com/mysql_vs_sqlserver.asp

>>Sql server VS PostgreSql

Please share something if you have any.

Thanx.

Vinay

twitter @ThakurVinay

http://vinay-thakur.spaces.live.com/

http://www.sqlservercentral.com/blogs/vinaythakur

Consider this while doing any UPDATE

2009-10-31T01:52:24Z

Trigger:

When you create trigger for insert and delete we will get two reference table as “inserted” and “deleted” respectively now what if we create a trigger for “update” on trigger, your answer might be we will get reference table “updated” :-) …. NO their is no updated table in trigger.

Here when we create a trigger for “update” on any table it will use two reference tables “deleted” and “inserted” to make the update.

Replication:

In the same way when we make any update on publisher it will go to subscriber as two statement Delete that records and insert that records.

As I already explain it here and also discuss about the issue we get due to this here.

We come across situation that when we have update on primary key. our replication may fail as update will be delete and insert to subscriber.

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_xxxx'. The conflict occurred in database 'DBnamexxx', table 'tablexxxxx', column 'column_pk'.

I explain the resolution what I do here.

Update on table:

When we update the data from any column and that data size fits into the page that will be ok. but when the data is not fit into the page they in that case update will delete that records from that page and insert into new page…

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/12/13/More-undocumented-fun_3A00_-DBCC-IND_2C00_-DBCC-PAGE_2C00_-and-off_2D00_row-columns.aspx.

Also update on huge data will also impact the performance. will try to blog more detail about Update in future.

Let me know your thought on this.

Thanx.

Vinay

@thakurvinay

http://www.eggheadcafe.com/software/aspnet/29906548/how-does-update-statement.aspx

Replication Setup:

2009-10-29T01:38:38Z

I saw too many questions on forum asking step by step process of configuring replication I have these links in my favorites so though consolidating it.

For Sql Server 2000:

Snapshot Replication setup:

By Alexander Chigrik

http://www.databasejournal.com/features/mssql/article.php/1458491/Setting-Up-Snapshot-Replication-A-Step-by-step-Guide.htm

Transaction Replication setup:

This is by Alexander Chigrik at database journal you can also find the same at mssqlcity.com

http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm

Merge Replication setup:

This is also by Alexander Chigrik

http://www.databasejournal.com/features/mssql/article.php/1438231/Setting-Up-Merge-Replication-A-Step-by-step-Guide.htm

Another for merge replication by D J Nagendra

http://www.codeproject.com/KB/database/MergeReplication.aspx

and more information about replication and step by step by Mahesh Kodli

http://www.devarticles.com/c/a/SQL-Server/Replication-SQL-Server-2000-Part-2/

For Sql Server 2005:

Transaction Replication setup:

I always refer this great link by longrujun

http://blog.csdn.net/longrujun/archive/2006/06/09/783357.aspx

Merge replication setup:

here is link by Ahmad Eid Salim

http://www.codeproject.com/KB/database/sql2005-replication.aspx

Finally To Implement Bidirectional Transactional Replication:

http://support.microsoft.com/kb/820675

Thanks you very much to all the bloggers to share this valuable information with us.

I will try to keep this up to date. Appreciate more links if you have any.

Thanx.

Vinay

twitter @thakurvinay

Tricks and issues Part 1

2009-10-24T02:50:26Z

Today was browsing the Blog http://blog.sqlauthority.com/ Pinal Dave expert on Sql server and great Blog writer. Got surprise to see the comments on his Blog. Great job and thank you Pinal for having such a wonderful site and sharing valuable information.

Found following information helpful to me today (*as I was having some issue related to this)

Bulk insert CSV and xls:

With this script we can import the CSV data into sql server table.
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

Following script helped me to import excel file

SELECT
* FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\excelsourcefile.xls;Extended Properties=Excel 8.0')...[Sheet1$]

I used sql server 2000 and sheet 1$ data

http://msdn.microsoft.com/en-us/library/ms179856.aspx

and if we want to export into xls we can use the below query but we have to give heading Name on excelTargetfile.xls file before running this query:

INSERT INTO
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=e:\excelTargetfile.xls;Extended Properties=Excel 8.0;')...[Sheet1$]
SELECT name FROM master.dbo.sysdatabases
GO

we can use the same with OPENROWSET

http://www.mssqltips.com/tip.asp?tip=1202

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Job server name change (Instance name). Error 14274:

While working on msdb database I restore msdb of other server/instance to different instance. found Pinal’s Blog helped me to solve my issue. by changing the server name in sysjobs table on sql server 2000.

http://blog.sqlauthority.com/2006/12/20/sql-server-fix-error-14274-cannot-add-update-or-delete-a-job-or-its-steps-or-schedules-that-originated-from-an-msx-server-the-job-was-not-saved/

We get the same error when server name changes for that we may have to follow Microsoft KB link as follows

http://support.microsoft.com/kb/281642

Insert multiple records with single insert

Before sql server 2008 to insert multiple records with single query was very difficult, got the very good Blog by Pinal where he shows a trick to insert multiple records with single insert statement as follows link:

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

As Microsoft introduce new feature in insert statement were we can insert multiple records with single insert statement simply by a comma separated with multiple records value as follows:
http://msdn.microsoft.com/en-us/library/dd776381.aspx

Sending mail from sql server

using SMTP :

Following is a great KB articles by Microsoft, which explains everything and gives a sample script for the same.

http://support.microsoft.com/kb/312839

We can use sqlmail and database mail for sending mail via sql server.

http://support.microsoft.com/kb/311231

http://msdn.microsoft.com/en-us/library/ms186358.aspx

Writing this blog to keep things as a reference to me.

Thanx

Vinay

Twitter @thakurvinay

Reference:

http://blog.sqlauthority.com/

Some useful commands and sp I use for Transaction Replication:

2009-10-23T23:01:19Z

I write several blog about replication and mansion commands and stored procedure related to transaction replication, but I would like to dedicate one blog (series of blogs) which should have all these information.

>>Some useful commands and stored procedures

How it works:

Log reader agent stores the command and transaction into following tables.

SELECT * FROM distribution.dbo.MSrepl_Commands --- commands for all transactions for replication for seqno or all
WHERE xact_seqno = 0x00001C030000F4540018

SELECT * FROM distribution.dbo.MSrepl_transactions -- -- transactions for particular seqno or all
WHERE xact_seqno= 0x00001C030000F4540018

and sql script stores into sp_browsereplcmds stored procedure.

sp_browsereplcmds @xact_seqno_start = '0x00001C030000F4540018', -- returns all/seqno info (sql query) on replication
@xact_seqno_end = '0x00001C030000F4540018'

http://technet.microsoft.com/en-us/library/ms176109.aspx

It stores all the transactions(sql script) on distribution which will distribute to subscriber.and once these transaction delivers to subscriber. it will remove after “Distribution cleanup job” from these tables.

------------------

Informative Commands:

select * from distribution.dbo.MSrepl_errors ---returns errors for replication.

select * from distribution.dbo.msdistribution_history ---- job history

select * from distribution.dbo.MSarticles ---- give info about replication articles

select * from publisher.dbo.sysarticles ---- give info about replication articles

Also transaction replication takes article up to 255 columns limit.

To get the information about publisher from subscriber:

exec sp_MSenumsubscriptions 'both'
SELECT * FROM MSreplication_subscriptions

To remove the Replication:

* Once we found on our dev server, the distribution database was not properly deleted and we could not able to work on it.

We wanted to drop the distribution but system was not allowing us... entry was their in this table.

select * from distribution.dbo.msdistribution_agents --- distributor agent info

To remove the unwanted Replication follow the below link:

http://support.microsoft.com/kb/324401

I mostly use the following commands for the same:

sp_removedbreplication ‘publisherDB’

exec master..sp_dropdistributor @no_checks = 1

I will blog more about useful commands related to replication.

Thanx.

Vinay

Twitter @thakurvinay

Reference:

http://www.replicationanswers.com/default.asp

http://technet.microsoft.com/en-us/library/ms151198.aspx

Logins no longer exists.

2009-10-22T21:55:55Z

Today while checking the logins, just want to clear/clean logins which are no longer exists on NT domain.

To get the logins which are no longer exits run the following:

EXEC master.dbo.sp_validatelogins

http://msdn.microsoft.com/en-us/library/ms181728.aspx

It will show you the list of SID and logins which are not exists. And then after that run the below command to remove that login.

Exec.sp_revokelogin ‘domain\Login name’

http://msdn.microsoft.com/en-us/library/ms188784.aspx

This is old commands and will be remove in future release… so if you are using sql server 2005 and above use below command which will be same as sp_revokelogin.

DROP LOGIN login_name

http://msdn.microsoft.com/en-us/library/ms188012(SQL.90).aspx

You can use the Enterprise Manager or SSMS for the same.

Go to Securities –> logins –> Right click and Delete login.

HTH.

Vinay

twitter @thakurvinay.

Table Variable and Temp Table

2009-10-16T22:28:31Z

Today I come across the great blog by Wayne Sheffield on Sqlservercentral.com about temp table and table variable so I would like to keep this blog as my reference to this so blogging about it.

http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

This blog explains everything about this as well as provide us some good links.

HTH.

Vinay

Network backup Access via Query Analyzer

2009-10-14T20:56:29Z

Hi,

Today while working on query analyzer found very interesting thing about security.

My sql server A is running with service account which is same as service account of server B and having all access.and I am login with my credential to server A, I am not having any access to server B.When I open a query analyzer with my credential.and run a restore command like this.

Restore database B_DB_on_A from disk =’\\B_servername\Drive$\backupfilelocation\backupfile.bkp’

Here the backup file is at located on B server. where I don't have access but as sql server A running with service account this command works and it will restore the database.

This works well if you want to restore things production to development.

HTH.

Vinay

Blogging

2009-10-14T02:28:55Z

Hi,

Yesterday while talking to my friend, he reviewed my blog and suggested that I was having so many spelling issues in my blog... so was working from morning to correct that and finally feels its corrected. I installed Windows Live Writer to write a blog… hope now on I will not have any spelling mistakes in my blogging.

I blog because it will help me for future reference, and it will be in my collection. also it might help others as others blog which helped me a lot.

I will try to improve my blogging and knowledge…

Thanx (This is my way of saying Thanks).

Vinay