SQL Server Performance Forum – Threads Archive
sysdatabases
I have shifted distribution db to another server, to reduce load on server. So now distributor is on another server. However whenever we restart SQL server it come up with error messages given below in red. I checked for "distribution" db in sysdatabases for entry and there is no entry for distribution db, however still whenever sql server starts it looks in for "distribution" db. Any ideas where else do I need to look at. From where does SQL server lookup for SQL server names?? As far as I know its entry is just in sysdatabases?? Any idea how SQL start up work and in which location is it looking for "distribution db"??? Error message says login failed and its not able to open up distribution db, However this DB does not exist on this server and this login is valid and its working fine for all databases and even for SQL server agent. 2007-04-09 00:30:00 – ! [298] SQLServer Error: 18456, Login failed for user ‘PATSQLACCOUNT’. [SQLSTATE 28000] 2007-04-09 00:30:00 – ! [298] SQLServer Error: 4060, Cannot open database "distribution" requested by the login. The login failed. [SQLSTATE 42000]There are some tables in msdb database which keeps track of distribution database… Check the following table and clean them but careful before doing anything which may screw up your replication… MSdistributor
MSdistributiondbs
MSdistpublishers
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Have you made such changes on replication to connect to that distributor instance?>
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Does SQL server look in for these tables while starting up ??
MSdistributor
MSdistributiondbs
MSdistpublishers Replication as such is working fine, there are no errors and data is OK as well. Its just the start up of SQL server which comes up with these error messages
I think it does…some of the procedures calls these tables. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I have checked this tables in sysobjects and there are no tables by these name. Any idea where else can I look for distribution database entry and rectify these error messages
In what db you checked?
I am sure these tables should exists in MSDB database… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I have checked this in Msdb and Master database and all these 3 tables do not exist in any of these databases
As the database changed the location the error is by default, you have to check whether such changes updated on the replication on this server too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Sorry But i didn’t understand what you are saying. Can you please explain??
Viksar, Are you able find the following tables in msdb database?
MSdistributor
MSdistributiondbs
MSdistpublishers
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
I believe he cannot as prior reply confirms, if you have changed any of the options or configuration for replication make sure you have updated the same on involved SQL instances in that replication. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
I am sure, these tables should exists in msdb even your server is not configured for replication…
I don’t know how he can’t find these objects… select * from msdb..sysobjects where name like ‘ms%’
and xtype = ‘u’
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
Hi Mohammed, Sorry these tables are not there in MSDB database. I have checked this twice. All the option and configuration have been changed and as I updated before replication is working fine. Its just the start up of SQL server that comes up with error messages. If you are specific about any settings, can you please update me??
Sorry! I was looking at the wrong sql instance…
You see these tables only when you configure distribution db on the server… Try enabling login audi for failed logins on publisher and distributor.
And also check the sql trace files which are located in log directory immediately after sql restart. sql server calls sp_MSrepl_startup procedure on startup to create a table in tempdb to collect agent status data… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
]]>