SQL Server Performance

sysdatabases

Discussion in 'SQL Server 2005 General DBA Questions' started by viksar, Apr 11, 2007.

  1. viksar New Member

    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]

  2. MohammedU New Member

    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.
  3. satya Moderator

    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.
  4. viksar New Member

    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
  5. MohammedU New Member

    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.
  6. viksar New Member

    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
  7. MohammedU New Member

    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.
  8. viksar New Member

    I have checked this in Msdb and Master database and all these 3 tables do not exist in any of these databases
  9. satya Moderator

    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.
  10. viksar New Member

    Sorry But i didn't understand what you are saying. Can you please explain??
  11. MohammedU New Member

    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.
  12. satya Moderator

    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.
  13. MohammedU New Member

    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.
  14. viksar New Member

    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??
  15. MohammedU New Member

    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.

Share This Page