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.
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.
or
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 1Cannot 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.
Twitter @thakurvinay
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.
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
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/RDBMShttp://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 DB2http://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.htmlhttp://www.petefreitag.com/item/5.cfmhttp://searchoracle.techtarget.com/tip/0,289483,sid41_gci1222615,00.html
Please share something if you have any.
twitter @ThakurVinay
http://vinay-thakur.spaces.live.com/
http://www.sqlservercentral.com/blogs/vinaythakur
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.
@thakurvinay
http://www.eggheadcafe.com/software/aspnet/29906548/how-does-update-statement.aspx
.
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:
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.
twitter @thakurvinay
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.sysdatabasesGO
we can use the same with OPENROWSET
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
http://blog.sqlauthority.com/
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:
I mostly use the following commands for the same:
sp_removedbreplication ‘publisherDB’
I will blog more about useful commands related to replication.
http://www.replicationanswers.com/default.asp
http://technet.microsoft.com/en-us/library/ms151198.aspx
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.
twitter @thakurvinay.
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.
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.
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).
