SQL Server Performance

Error while creating Replication

Discussion in 'SQL Server Clustering' started by akothand, Jan 13, 2004.

  1. akothand New Member

    Hi All,

    I have a SQL 2000 cluster with two named instances.
    When i try to create a Publication (By accessing Replication-->Create New publication in EM) it is throwing an error when creating the "Distribution" db
    <<< Create Database failed. (Error 1803) . >>
    But the location for this "Distibution" db's data and logfile has 12 GB free !
    i tried to change the Target location, but gettin the same error !

    Please throw light on this !



    With Regards
    Anand.K
    akothand@indymacbank.com

    "What would you attempt to do if you knew that you could never fail"
    -Robert Schuller
  2. akothand New Member

    Hi All, <br />Just to add :<br />After getting this error, i tried to do the same using the menu "Tools--&gt;Replication--&gt;Configure Publishers....... "<br />Surprisingly, I got another Error while creating the Distribution database :<br /><b><b>Error 5184 : Cannot use file "E:MSSQL$HRCI01datadistribution.mdf" for clustered server.<br />Only Formatted files non which the cluster resource of the server has a dependency can be used. CREATE DATABASE Failed "</b></b><br /><br />Am new both to Clustering and Replication. Such errors that seem to be combination of both <img src='/community/emoticons/emotion-6.gif' alt=':(' /> Am also readin BOL to get more insight ! <br />Pls let me know your opinions on this !<br /><br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by akothand</i><br /><br />Hi All,<br /><br />I have a SQL 2000 cluster with two named instances. <br />When i try to create a Publication (By accessing Replication--&gt;Create New publication in EM) it is throwing an error when creating the "Distribution" db <br />&lt;&lt;&lt; Create Database failed. (Error 1803) . &gt;&gt;<br />But the location for this "Distibution" db's data and logfile has 12 GB free !<br />i tried to change the Target location, but gettin the same error !<br /><br />Please throw light on this !<br /><br /><br /><br />With Regards<br />Anand.K<br />akothand@indymacbank.com<br /><br />"What would you attempt to do if you knew that you could never fail"<br />-Robert Schuller<br /><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />Thanks,<br />Anand.K<br />akothand@indymacbank.com<br /><br />"What would you attempt to do if you knew that you could never fail"<br />-Robert Schuller<br />
  3. Luis Martin Moderator

  4. satya Moderator

    The volume must be in SQL serve resource group, follow Luis's KBA reference.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. akothand New Member

    Thanks for the replies guyz. Am now getting the first Error constantly !
    <<< CREATE Database failed. Could not allocate enough disk space for a new database on the named disks. Total space allocated must be at least 100MB to accomodate a copy of model database >>>

    Can you please tell me what is the relevance of model in replication ? The Drive it is set to create the distribution.mdf and .ldf has 12 GB free. so the error msg does not make any sense !




    Thanks,
    Anand.K
    akothand@indymacbank.com

    "What would you attempt to do if you knew that you could never fail"
    -Robert Schuller
  6. Twan New Member

    Are you sure that the database is set to be created on the drive you expect? I'd suggest using SQLProfiler to get the SQL being applied to the server, to see if it sheds any light on this

    Cheers
    Twan
  7. satya Moderator

    Try to create the database on a different disk and follows the references in the above KBA.
    Have you checked whether the drive where database is being created is a part of SQL resource group in cluster.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  8. akothand New Member

    Hi guyz,

    looked at the cluster grouping !
    The Drive on which the database is supposed to get created, is in N: drive, which is part of a cluster named 'B'. The SQL Server resources and Binaries r part of "Local Cluster"
    So that could be the reason for this error !
    BUT Two things :
    1) I tried to create the database in a drive D: , which is part of the "Local cluster"
    Even then , it gave the same error.
    2)All my other databases are in the standard N: drive only. We have a standard where Data files must go to N: drive and Log files to O: drive.
    So why didnt the same error come while creating or Restoring other databases ?

    As of now, i made a workaround. I am having my distributor database in another server !
    But, hopin to fix this issue soon so that i can have my distr. database in the same server as the Publisher !






    Thanks,
    Anand.K
    akothand@indymacbank.com

    "What would you attempt to do if you knew that you could never fail"
    -Robert Schuller
  9. Argyle New Member

    What do you mean with "local cluster"? Could you post your cluster layout as seen in cluster administrator:

    Cluster
    - Group name 1
    -- Group resources
    - Group name 2
    -- Group resources
    - Group name 3
    -- Group resources

    /Argyle
  10. akothand New Member

    We cracked the issue at last guyz !
    Seems I have been misinterpreting the error message. <<< CREATE Database failed. Could not allocate enough disk space for a new database on the named disks. Total space allocated must be at least 100MB to accomodate a copy of model database >>>

    when the error message says ATLEAST 100MB, it does not mean there is no diskspace. It actually meant the size of the distribution database SHOULD BE atleast the size of model.
    And There is a bug in Enterprise manager (I call a BUG because, i see there is no setting where we can set the SIZE of the Distribution database. It is defaulting to some low value)
    We tried to create the distribution database thru T-SQL, by hardcoding the db size as 300MB..it worked !
    Thanks for your inputs guyz !

    Have a nice weekend !




    Thanks,
    Anand.K
    akothand@indymacbank.com

    "What would you attempt to do if you knew that you could never fail"
    -Robert Schuller
  11. satya Moderator

    Well, take this as a lesson and do follow query analyzer to carry upon any administrative tasks which will give you information about the process involved rather than depending upon GUI tool.(my exp.)

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page