Hi all!
About a month ago I had problems with SQL server, had to stop the sql server
process. After starting it I had to recover master DB. Everything was
looking good. But today I tried to grant DB access and found
sp_grantdbaccess missing.
After some experiments I found out a strange problem: I cannot create ANY
procedure on master DB (I have enough rights :) ).
Example: running script
CREATE PROC xxx AS SELECT 1
returns "There is already an object named 'xxx' in the database.", xxx is
any name.
This happens even on another SQL Server on db, that we backup/restored from
corrupted master db.
Any suggestions are welcomeThe correct way is to restore the last valid backup. If you don't have it,
you can try to rebuild the master database using Rebuildm.Exe command propt
utility (check for the syntax in Books OnLine). Then you will have to
recreate logins (maybe you can still generate scripts, so this task can be
easier?), attach all of the databases (check the sp_attach_db system SP) and
potentialy map logins to database users again (check the
sp_change_users_login for SQL logins and
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q298/8/97.asp&NoWebContent=1
for Windows logins).
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Sergei Almazov" <almazik@.ukr.net> wrote in message
news:e7v2x8teDHA.1820@.TK2MSFTNGP10.phx.gbl...
> Hi all!
> About a month ago I had problems with SQL server, had to stop the sql
server
> process. After starting it I had to recover master DB. Everything was
> looking good. But today I tried to grant DB access and found
> sp_grantdbaccess missing.
> After some experiments I found out a strange problem: I cannot create ANY
> procedure on master DB (I have enough rights :) ).
> Example: running script
> CREATE PROC xxx AS SELECT 1
> returns "There is already an object named 'xxx' in the database.", xxx is
> any name.
> This happens even on another SQL Server on db, that we backup/restored
from
> corrupted master db.
> Any suggestions are welcome
>
Showing posts with label corrupted. Show all posts
Showing posts with label corrupted. Show all posts
Monday, March 26, 2012
How to make an exact database duplicate, including replication
Hi
Yesterday I posted a question regarding our problem with our replication
being corrupted with time:
http://msdn.microsoft.com/newsgroups...8-8be420041185
While waiting for repsonse on this thread I would like to try some rougher
methods to find out what is going on, however I do not want to do this on our
live production server.
As we do not know the cause of the problem except that it manifests itself
and grows worse after long periods of heavy usage, we cannot recreate it
properly on our test servers.
Thus I would like to make a complete backup of a faulty database (including
corrupt replication and all) for us to dissect and analyze. Is this possible,
without halting the production server and ghosting it?
Normal backups of the database will not reproduce the error. Neither will I
get the error if I also use the methods for scripting out the replication and
applying it to the new server.
Does anyone know of a way to make an identical backup of a replicating
database?
Thank you
/Kjell
Besides what you have done, the only other option is to ghost the machine.
The question being can you even reproduce it on a test system? If it is
related to heavy, sustained usage, can you reproduce your poduction load as
well as query patterns on a test server? There seems to be something else
going on in here than just replication.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
news:F0458481-DF34-4B23-88E1-F44C09731121@.microsoft.com...
> Hi
> Yesterday I posted a question regarding our problem with our replication
> being corrupted with time:
> http://msdn.microsoft.com/newsgroups...8-8be420041185
> While waiting for repsonse on this thread I would like to try some rougher
> methods to find out what is going on, however I do not want to do this on
> our
> live production server.
> As we do not know the cause of the problem except that it manifests itself
> and grows worse after long periods of heavy usage, we cannot recreate it
> properly on our test servers.
> Thus I would like to make a complete backup of a faulty database
> (including
> corrupt replication and all) for us to dissect and analyze. Is this
> possible,
> without halting the production server and ghosting it?
> Normal backups of the database will not reproduce the error. Neither will
> I
> get the error if I also use the methods for scripting out the replication
> and
> applying it to the new server.
> Does anyone know of a way to make an identical backup of a replicating
> database?
> Thank you
> /Kjell
|||Hi Michael
Thank you for the answer.
We do have some automatic testing of updating client databases and
synchronizing afterwards. Though it seems that those tests are not enough, or
they are missing some important aspect, because they cannot replicate the
problem for us. The only databases that are afflicted, are those that have
been running for an extended time with many clients.
As the problem disappears when the replication is recreated we suspect that
there is something going on in the MSMerge tables that causes every client to
receive multiple updates but we cannot put our fingers on it. Just recreating
the snapshot won't solve the problem, the whole replication must be replaced.
Anyone know if there is a good description for how SQL2005 uses the tables
for its replication? Step by step from matching the client with generation
id, retrieveing snapshot and applying changes. If we find out what rows are
causing the multiple updates it should be easier figuring out what is causing
the corrupted rows.
/Kjell
"Michael Hotek" wrote:
> Besides what you have done, the only other option is to ghost the machine.
> The question being can you even reproduce it on a test system? If it is
> related to heavy, sustained usage, can you reproduce your poduction load as
> well as query patterns on a test server? There seems to be something else
> going on in here than just replication.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
> news:F0458481-DF34-4B23-88E1-F44C09731121@.microsoft.com...
>
>
|||Actually, it is all very well documented or at least as documented as it
gets. The replication engine does 100% of its work using triggers and
stored procedures. So you can trace the trigger code along with the stored
proc code to trace the full calculation path that a piece of data takes.
There isn't anything else that is published on the subject.
Now to narrow it down, the merge engine transfers batches. It tags these
batches by a generation number. By using MSmerge_genhistory, you can
determine the batches which exist on a particular server that don't exist on
another server. You can then take those values to the MSmerge_contents
table to pick up the rowguid and article ID. Those can then be used to
target specific rows in tables.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
news:901E0AEE-CDB0-4F95-97DE-5C2AD69C4A2A@.microsoft.com...[vbcol=seagreen]
> Hi Michael
> Thank you for the answer.
> We do have some automatic testing of updating client databases and
> synchronizing afterwards. Though it seems that those tests are not enough,
> or
> they are missing some important aspect, because they cannot replicate the
> problem for us. The only databases that are afflicted, are those that have
> been running for an extended time with many clients.
> As the problem disappears when the replication is recreated we suspect
> that
> there is something going on in the MSMerge tables that causes every client
> to
> receive multiple updates but we cannot put our fingers on it. Just
> recreating
> the snapshot won't solve the problem, the whole replication must be
> replaced.
> Anyone know if there is a good description for how SQL2005 uses the tables
> for its replication? Step by step from matching the client with generation
> id, retrieveing snapshot and applying changes. If we find out what rows
> are
> causing the multiple updates it should be easier figuring out what is
> causing
> the corrupted rows.
> /Kjell
> "Michael Hotek" wrote:
Yesterday I posted a question regarding our problem with our replication
being corrupted with time:
http://msdn.microsoft.com/newsgroups...8-8be420041185
While waiting for repsonse on this thread I would like to try some rougher
methods to find out what is going on, however I do not want to do this on our
live production server.
As we do not know the cause of the problem except that it manifests itself
and grows worse after long periods of heavy usage, we cannot recreate it
properly on our test servers.
Thus I would like to make a complete backup of a faulty database (including
corrupt replication and all) for us to dissect and analyze. Is this possible,
without halting the production server and ghosting it?
Normal backups of the database will not reproduce the error. Neither will I
get the error if I also use the methods for scripting out the replication and
applying it to the new server.
Does anyone know of a way to make an identical backup of a replicating
database?
Thank you
/Kjell
Besides what you have done, the only other option is to ghost the machine.
The question being can you even reproduce it on a test system? If it is
related to heavy, sustained usage, can you reproduce your poduction load as
well as query patterns on a test server? There seems to be something else
going on in here than just replication.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
news:F0458481-DF34-4B23-88E1-F44C09731121@.microsoft.com...
> Hi
> Yesterday I posted a question regarding our problem with our replication
> being corrupted with time:
> http://msdn.microsoft.com/newsgroups...8-8be420041185
> While waiting for repsonse on this thread I would like to try some rougher
> methods to find out what is going on, however I do not want to do this on
> our
> live production server.
> As we do not know the cause of the problem except that it manifests itself
> and grows worse after long periods of heavy usage, we cannot recreate it
> properly on our test servers.
> Thus I would like to make a complete backup of a faulty database
> (including
> corrupt replication and all) for us to dissect and analyze. Is this
> possible,
> without halting the production server and ghosting it?
> Normal backups of the database will not reproduce the error. Neither will
> I
> get the error if I also use the methods for scripting out the replication
> and
> applying it to the new server.
> Does anyone know of a way to make an identical backup of a replicating
> database?
> Thank you
> /Kjell
|||Hi Michael
Thank you for the answer.
We do have some automatic testing of updating client databases and
synchronizing afterwards. Though it seems that those tests are not enough, or
they are missing some important aspect, because they cannot replicate the
problem for us. The only databases that are afflicted, are those that have
been running for an extended time with many clients.
As the problem disappears when the replication is recreated we suspect that
there is something going on in the MSMerge tables that causes every client to
receive multiple updates but we cannot put our fingers on it. Just recreating
the snapshot won't solve the problem, the whole replication must be replaced.
Anyone know if there is a good description for how SQL2005 uses the tables
for its replication? Step by step from matching the client with generation
id, retrieveing snapshot and applying changes. If we find out what rows are
causing the multiple updates it should be easier figuring out what is causing
the corrupted rows.
/Kjell
"Michael Hotek" wrote:
> Besides what you have done, the only other option is to ghost the machine.
> The question being can you even reproduce it on a test system? If it is
> related to heavy, sustained usage, can you reproduce your poduction load as
> well as query patterns on a test server? There seems to be something else
> going on in here than just replication.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
> "Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
> news:F0458481-DF34-4B23-88E1-F44C09731121@.microsoft.com...
>
>
|||Actually, it is all very well documented or at least as documented as it
gets. The replication engine does 100% of its work using triggers and
stored procedures. So you can trace the trigger code along with the stored
proc code to trace the full calculation path that a piece of data takes.
There isn't anything else that is published on the subject.
Now to narrow it down, the merge engine transfers batches. It tags these
batches by a generation number. By using MSmerge_genhistory, you can
determine the batches which exist on a particular server that don't exist on
another server. You can then take those values to the MSmerge_contents
table to pick up the rowguid and article ID. Those can then be used to
target specific rows in tables.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Mirtul" <Mirtul@.discussions.microsoft.com> wrote in message
news:901E0AEE-CDB0-4F95-97DE-5C2AD69C4A2A@.microsoft.com...[vbcol=seagreen]
> Hi Michael
> Thank you for the answer.
> We do have some automatic testing of updating client databases and
> synchronizing afterwards. Though it seems that those tests are not enough,
> or
> they are missing some important aspect, because they cannot replicate the
> problem for us. The only databases that are afflicted, are those that have
> been running for an extended time with many clients.
> As the problem disappears when the replication is recreated we suspect
> that
> there is something going on in the MSMerge tables that causes every client
> to
> receive multiple updates but we cannot put our fingers on it. Just
> recreating
> the snapshot won't solve the problem, the whole replication must be
> replaced.
> Anyone know if there is a good description for how SQL2005 uses the tables
> for its replication? Step by step from matching the client with generation
> id, retrieveing snapshot and applying changes. If we find out what rows
> are
> causing the multiple updates it should be easier figuring out what is
> causing
> the corrupted rows.
> /Kjell
> "Michael Hotek" wrote:
Subscribe to:
Posts (Atom)