Database name with point in it (mail.archive.mdf) not recognised in query analyser of SQL

Apparently the query analyser of sql server does not recognise a
database with a point in it, like
mail.archive.mdf

I receive the following error when I use it:

Server: Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'mail'. No entry
found with that name. Make sure that the name is entered correctly.

Please help, thanks
Premselect * from master..sysdatabases
where name ='mail.archive'

mdf is not part of the name

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Prem wrote:
> Apparently the query analyser of sql server does not recognise a
> database with a point in it, like
> mail.archive.mdf
> I receive the following error when I use it:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'mail'. No entry
> found with that name. Make sure that the name is entered correctly.
> Please help, thanks
> Prem|||Prem (premmetje@.zonnet.nl) writes:
> Apparently the query analyser of sql server does not recognise a
> database with a point in it, like
> mail.archive.mdf
> I receive the following error when I use it:
> Server: Msg 911, Level 16, State 1, Line 1
> Could not locate entry in sysdatabases for database 'mail'. No entry
> found with that name. Make sure that the name is entered correctly.

Need to use brackets or double quotes for identifiers with characters
that are not identifier characters normally:

USE [mail.archive.mdf]

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Database Mirrororing Monitor - error retrieving data

Hello,
I can't see data for mirror database in Database Mirrororing Monitor.
The mirroring is configured and runs properly between 2 locations. I can
even register mirrored database with both peers - it works the same with
principal and mirrored databases in registration process. I'm using windows
authentication and the account has sysadmin role in both server instances.
However upon registration, the principal database appears in state
Synchronized while mirror database appears with "Error retrieving data" in
Current Role, Mirroring State and Witness Connections columns.
Both SQL servers are 64-bit Enterprise 2005 SP2 versions on 64-bit Windows
Server 2003 SP2.
Could someone help me to setup the monitoring?
Thanks a lot
eXavierHello eXavier,
Can you please answer the following questions:
1. What version of SQL Server 2005 are you running? The build number
resembles
9.0.xxxx.
2. Can you please zip and send me the error logs for each of the servers.
3. Run the following SQL statements and please return the results to me in
an
output file
a. Select * from sys.databases (run this on principle and mirror server)
b. Select * from sys.database_mirroring (run this on principle and mirror
server)
c. Select * from sys.database_mirroring_witnesses (run this on the witness
server)
d. Select * from sys.database_mirroring_endpoints (run on all servers)
e. Select * from sys.tcp_endpoints (run on all servers)
f. Select * from sys.server_principals (run on all servers)
g. Select * from sys.database_recovery_status (run on principal and mirror
server)
h. Select * from msdb.dbo.backupset (run on principal server only)
i. Select * from sys.dm_os_wait_stats (run on principal and mirror servers)
j. Select * from sys.sysprocesses (run on all servers)
k. Verify the connectivity between the servers using Telnet and send the
results.
To do this:
l. Open a command prompt.
m. Run telnet <machine name> <port number> where machine name is the fully
qualified domain name and port number is the port number for the mirroring
endpoint.
n. Run this from the principal to the mirror and from the mirror to the
principal.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Database Mirroring Transport is disabled in the endpoint configuration.

Hi.

I am getting this error.

Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.

Someone please help me.It's urgent.

I am using same PC with 2 different intsances.

This is how i have done it:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@.#56'

GO

-- CREATE CERTIFICATE PRINCIPAL_CERT

CREATE CERTIFICATE PRINCIPAL_CERT

WITH SUBJECT = 'PRINCIPAL CERTIFICATE',

START_DATE = '03/07/2006',

EXPIRY_DATE = '01/01/2010'

GO

-- CREATE ENDPOINT NAMED (EPMIRRORING) USING CERTIFICATE AND ALGORITHM

CREATE ENDPOINT EPMIRRORING

STATE = STARTED

AS TCP(LISTENER_PORT = 6025, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL,

AUTHENTICATION = CERTIFICATE PRINCIPAL_CERT)

GO

Here is the of Event Log.

Date,Source,Severity,Message
08/03/2006 15:54:35,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 15:54:35,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 15:52:57,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 15:52:55,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:14:54,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:14:54,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 14:14:37,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 14:14:35,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:46,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:43,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:41,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:38,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:36,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:34,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:33,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:32,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:30,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:15,spid54,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:03:15,spid54,Unknown,Server is listening on [ 'any' <ipv4> 5022].

Any solution ?
Thanks
Mihir

Are you using SQL Server 2005 RTM or SP1?|||I am having the same problem and I running sql server 2005 sp1|||

ascetic wrote:

08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]

This indicates either that one host is configured to use CERTIFICATE authentication, the other is configured to use WINDOWS authentication, either one is configured to use WINDOWS KERBEROS and the other is using WINDOWS NTLM.

HTH,
~ Remus

|||I'm having this issue too, though mine says "State 22" at the end.

Remus, how would I know which the hosts are configured for and how can we change this? All of my servers are running as the same user in an AD domain so they should be able to talk fine?

thanks
Pete

Database Mirroring Transport is disabled in the endpoint configuration.

Hi.

I am getting this error.

Msg 1486, Level 14, State 2, Line 2
Database Mirroring Transport is disabled in the endpoint configuration.

Someone please help me.It's urgent.

I am using same PC with 2 different intsances.

This is how i have done it:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'test@.#56'

GO

-- CREATE CERTIFICATE PRINCIPAL_CERT

CREATE CERTIFICATE PRINCIPAL_CERT

WITH SUBJECT = 'PRINCIPAL CERTIFICATE',

START_DATE = '03/07/2006',

EXPIRY_DATE = '01/01/2010'

GO

-- CREATE ENDPOINT NAMED (EPMIRRORING) USING CERTIFICATE AND ALGORITHM

CREATE ENDPOINT EPMIRRORING

STATE = STARTED

AS TCP(LISTENER_PORT = 6025, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ENCRYPTION = REQUIRED ALGORITHM RC4, ROLE = ALL,

AUTHENTICATION = CERTIFICATE PRINCIPAL_CERT)

GO

Here is the of Event Log.

Date,Source,Severity,Message
08/03/2006 15:54:35,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 15:54:35,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 15:52:57,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 15:52:55,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:14:54,spid52,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:14:54,spid52,Unknown,Server is listening on [ 'any' <ipv4> 5122].
08/03/2006 14:14:37,spid53,Unknown,The Database Mirroring protocol transport is disabled or not configured.
08/03/2006 14:14:35,spid53,Unknown,The Database Mirroring protocol transport has stopped listening for connections.
08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:46,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:43,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:41,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:38,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:36,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:34,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:33,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:32,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:30,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]
08/03/2006 14:03:15,spid54,Unknown,The Database Mirroring protocol transport is now listening for connections.
08/03/2006 14:03:15,spid54,Unknown,Server is listening on [ 'any' <ipv4> 5022].

Any solution ?
Thanks
Mihir

Are you using SQL Server 2005 RTM or SP1?|||I am having the same problem and I running sql server 2005 sp1|||

ascetic wrote:

08/03/2006 14:03:48,Logon,Unknown,Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible authentication protocol. State 21.'. [CLIENT: 10.10.1.12]

This indicates either that one host is configured to use CERTIFICATE authentication, the other is configured to use WINDOWS authentication, either one is configured to use WINDOWS KERBEROS and the other is using WINDOWS NTLM.

HTH,
~ Remus

|||I'm having this issue too, though mine says "State 22" at the end.

Remus, how would I know which the hosts are configured for and how can we change this? All of my servers are running as the same user in an AD domain so they should be able to talk fine?

thanks
Pete
sql

Database Mirroring Transport Error

Hi

I am trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.

After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing

alter database set partner="..."

Am I missing something?

Thanks

First, upgrade to SP1. You don't have to specify the 1400 trace flag.

But, to answer your question....

For RTM, you must specify the trace flag 1400 as a startup parameter; you cannot use traceon. I notice that you have a " " (space) between the -T and 1400. Get rid of that. Start up sqlservr.exe with -T1400.

Thanks,

Mark

Database Mirroring Monitor Job Error

We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.

Both SQL Servers are running SP2 with the latest patches.

Can you please let me know what we have to do to resolve this issue?

Thank you!

Bosko

Is this been like this since the invocation of Database mirroring on these instances?

If not see what has been changed since the last successful occurence.

|||The problems started upon implementation of the database mirroring|||

I'm also facing the same issue.

Did anyone find a solution or workaround?

|||

I posted this in a couple different forums/newsgroups and never got an answer. We are still having this problem.

Bosko

Database Mirroring Monitor Job Error

We have recently set up two SQL Server 2005 Standard Edition servers with database mirroring. The mirrors function and fail over without a problem, but the Database Mirroring Monitor Job fails every time with the following error message:

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.

Both SQL Servers are running SP2 with the latest patches.

Can you please let me know what we have to do to resolve this issue?

Thank you!

Bosko

Is this been like this since the invocation of Database mirroring on these instances?

If not see what has been changed since the last successful occurence.

|||The problems started upon implementation of the database mirroringsql

Database Mirroring Monitor Job Error

We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.

Both SQL Servers are running SP2 with the latest patches.

Can anyone help with the resolutionto this this issue?

Thank you!
Bosko(bosko19@.gmail.com) writes:

Quote:

Originally Posted by

We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:
>
Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.
>
Both SQL Servers are running SP2 with the latest patches.
>
Can anyone help with the resolutionto this this issue?

Eh, that looks bad. I haven't worked much with mirroring, or more precisely
not at all. But a syntax errors is not what I would expect. Could you
list the exact name of the servers and databases involved? My suspicion
is that there is a problem with special characters.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Apr 13, 6:45 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

(bosk...@.gmail.com) writes:

Quote:

Originally Posted by

We have recently set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:

>

Quote:

Originally Posted by

Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrect
syntax near the keyword 'with'. If this statement is a common table
expression or an xmlnamespaces clause, the previous statement must be
terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step
failed.

>

Quote:

Originally Posted by

Both SQL Servers are running SP2 with the latest patches.

>

Quote:

Originally Posted by

Can anyone help with the resolutionto this this issue?

>
Eh, that looks bad. I haven't worked much with mirroring, or more precisely
not at all. But a syntax errors is not what I would expect. Could you
list the exact name of the servers and databases involved? My suspicion
is that there is a problem with special characters.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

LOL...I didn't think it looked good either :)

The database server has a dash in it (i.e. CompanyName-SQL1 and SQL2
respecitvely). All databases are just alpha characters except for one
that begins with a number sequence (i.e. 1000DatabaseName).

Thank you in advance!

Bosko|||(bosko19@.gmail.com) writes:

Quote:

Originally Posted by

The database server has a dash in it (i.e. CompanyName-SQL1 and SQL2
respecitvely). All databases are just alpha characters except for one
that begins with a number sequence (i.e. 1000DatabaseName).

What service-pack level are you at? (For all three machines.)

If you are on SP2, you should definitely submit a bug on
http://connect.microsoft.com/SqlServer/. And if it's not an acceptable
workaround to rename the servers, I think you should open a case with
Microsoft, and demand that you get a hotfix.

If you are not on SP2, I recommend you to download it to see if it addresses
the problem. Note there is a rollup to SP2 which addresses two serious
bugs with maintenance plans. Make sure you get the rollup, if you use
maintenance plans.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog (esquel@.sommarskog.se) writes:

Quote:

Originally Posted by

What service-pack level are you at? (For all three machines.)

Sorry, I see now in your first post that you are on SP2.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Apr 16, 5:30 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

Erland Sommarskog (esq...@.sommarskog.se) writes:

Quote:

Originally Posted by

What service-pack level are you at? (For all three machines.)

>
Sorry, I see now in your first post that you are on SP2.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you for taking the time to look into the issue! I will submit
the case to Microsoft and hopefully get a resolution from them.

Bosko

database mirroring error? -- Scenario HASL2.3

HASL2.3 refers to the scenario within this article of a Mirror Server Loss
followed by Witness Loss:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
Win2k3 R2 servers.
We were planning on upgrading some hardware (adding a disk array) to our
secondary site (datacenter B) and I thought that it would be a good time to
test a couple of database mirroring scenarios.
I set up database mirroring on a test database. The primary was on a server
in datacenter A. I set up a witness on a box located in the test lab at our
office . With this setup I was running database mirroring in a high safety
with automatic failover (synchronous) mode.
Mirroring was working. I manually failed over and back. No problem there.
They shut down the server within datacenter B. The server in datacenter A
could still talk to my watcher box in the lab in our office. The primary
database on the server within datacenter A stayed available but it showed
that the mirrored server was disconnected (as expected).
I stopped SQL Server on the watch box in the lab. At that point the primary
database on the server within datacenter A became unavailable (Principal,
Disconnected / In Recovery). The database was unavailable (as expected).
The fly in the ointment:
They finished working on the server within datacenter B. The box came back
up without any issues. The other databases on that server are intact and
everything looks good. SQL Server is running normally...BUT the database
mirroring did not resume. The article indicates that "If Server B is
restored first, mirroring resumes, although no automatic failover is
possible without a witness." I did NOT experience this. My "Server B" has
been back online for 45 minutes and its status still shows as "Principal,
Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
Disconnected / In Recovery" on Server B (datacenter B).
Has anyone else experienced this scenario? I could try turning on the watch
server again, but I would like to see the two servers (principal and mirror)
synchronize on their own. I realize that automatic failover will not be
possible until I bring the watch back online, but the mirrored database(s)
should be available now that both the principal and mirror are back online.
Right?
--
Keith Kratochvilmy understanding of the situation is that when both or even one of the
datacenters comes online, it cannot automatically failover because both
are unable to deciede which of them is the actual principal(beacuse of
the link between them is not there) unless there is a witness as u
said. if both come online both try to actively become the principal and
hence the same problem
if u add a witness it should solve your problem or u can manually make
the mirror as your principal
Keith Kratochvil wrote:
> HASL2.3 refers to the scenario within this article of a Mirror Server Loss
> followed by Witness Loss:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>
> We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
> Win2k3 R2 servers.
> We were planning on upgrading some hardware (adding a disk array) to our
> secondary site (datacenter B) and I thought that it would be a good time to
> test a couple of database mirroring scenarios.
> I set up database mirroring on a test database. The primary was on a server
> in datacenter A. I set up a witness on a box located in the test lab at our
> office . With this setup I was running database mirroring in a high safety
> with automatic failover (synchronous) mode.
> Mirroring was working. I manually failed over and back. No problem there.
> They shut down the server within datacenter B. The server in datacenter A
> could still talk to my watcher box in the lab in our office. The primary
> database on the server within datacenter A stayed available but it showed
> that the mirrored server was disconnected (as expected).
>
> I stopped SQL Server on the watch box in the lab. At that point the primary
> database on the server within datacenter A became unavailable (Principal,
> Disconnected / In Recovery). The database was unavailable (as expected).
>
> The fly in the ointment:
> They finished working on the server within datacenter B. The box came back
> up without any issues. The other databases on that server are intact and
> everything looks good. SQL Server is running normally...BUT the database
> mirroring did not resume. The article indicates that "If Server B is
> restored first, mirroring resumes, although no automatic failover is
> possible without a witness." I did NOT experience this. My "Server B" has
> been back online for 45 minutes and its status still shows as "Principal,
> Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
> Disconnected / In Recovery" on Server B (datacenter B).
>
> Has anyone else experienced this scenario? I could try turning on the watch
> server again, but I would like to see the two servers (principal and mirror)
> synchronize on their own. I realize that automatic failover will not be
> possible until I bring the watch back online, but the mirrored database(s)
> should be available now that both the principal and mirror are back online.
> Right?
> --
> Keith Kratochvil|||Please re-read my post.
I had an automatic failover setup configured between data center A
(Primary), data center B (Mirror), and our office (Witness).
I wanted to test what would happen if we lost the Mirror and then the
Witness.
When we lost the Mirror (because it was down for hardware maintenance)
everything kept running because the Witness was still available.
I shut down the Witness and the Primary stopped working (as expected).
The hardware upgrade on the Mirror was complete and we brought that machine
back online. At this point we had
Primary server: on
Mirror server: on
Witness server: off
Mirroring: broken
Expected Result: Primary and Mirror would start "talking" to each other,
synchronize any changes , and come back online.
Actual Result: the database that I mirrored (MirroredDemo) is unavailable.
When I try to connect to or use that database on the primary I receive the
following message:
"Database MirroredDemo is enabled for Database Mirroring, but neither the
partner nor witness server instances are available: the database cannot be
opened.
"
Current situation:
(Via SSMS) The database is showing that it is "Principal, Disconnected / In
Recovery" within the database list. If I look at the database mirroring
properties the status text is: "No connection: cannot connect to the mirror
or witness server instances." In this state the database is UNAVAILABLE
If I bring the witness back online the databae shows that it is "Principal,
Disconnected." Database Mirroring is not functioning. The good news is
that I can access the database. The bad news is that I cannot fail over. I
receive this message when I try to fail over: "Msg 1422, Level 16, State 2,
Line 1
The mirror server instance is not caught up to the recent changes to
database "MirroredDemo". Unable to fail over."
I thought that I might need to resume the mirroring session. With the
witness server still offline, I tried to execute this command to resume
mirroring: "ALTER DATABASE mirroreddemo SET PARTNER RESUME"
No luck.
I brought the witness online. The status (as shown within the database list
of SSMS) is still "Principal/Disconnected."
I tried resuming mirroring (again, with the command "ALTER DATABASE
mirroreddemo SET PARTNER RESUME") but that did not work either.
At this point the P,M, and W are all online.
Still, the status within the database mirroring properties on the principal
shows "No connection: cannot connect to the mirror or witness server
instances"
I am stumped. Why didn't it (mirroring) come back online and start working
after all the servers are back online?
In researching this issue I have discovered the following articles:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/9211508f9b08072d/1de7d680cfd4d127?lnk=st&q=&rnum=2&hl=en#1de7d680cfd4d127
http://forums.microsoft.com/MSDN/showpost.aspx?postid=441900&siteid=1
We are in a similar situation as Muhammad. We have a few servers with more
than 100 databases on them. We are hoping to use mirroring as a high
availability solution to keep servers in different data centers in synch.
After reading those posts I am concerned that database mirroring might not
be able to meet our needs.
When we were running on SQL2k we used Replication. That is a path I would
rather not revisit.
Can anyone offer a solution to these problems
--mirroring not resuming properly on this one database
--mirroring not working (playing nice) on a server with many databases
Keith Kratochvil
"Udit Ghai" <ghai.udit@.gmail.com> wrote in message
news:1160458659.575183.108970@.i3g2000cwc.googlegroups.com...
> my understanding of the situation is that when both or even one of the
> datacenters comes online, it cannot automatically failover because both
> are unable to deciede which of them is the actual principal(beacuse of
> the link between them is not there) unless there is a witness as u
> said. if both come online both try to actively become the principal and
> hence the same problem
> if u add a witness it should solve your problem or u can manually make
> the mirror as your principal
> Keith Kratochvil wrote:
>> HASL2.3 refers to the scenario within this article of a Mirror Server
>> Loss
>> followed by Witness Loss:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
>>
>> We have SQL Server 2005 Standard Edition, SP1, + hotfix 2153 installed on
>> Win2k3 R2 servers.
>> We were planning on upgrading some hardware (adding a disk array) to our
>> secondary site (datacenter B) and I thought that it would be a good time
>> to
>> test a couple of database mirroring scenarios.
>> I set up database mirroring on a test database. The primary was on a
>> server
>> in datacenter A. I set up a witness on a box located in the test lab at
>> our
>> office . With this setup I was running database mirroring in a high
>> safety
>> with automatic failover (synchronous) mode.
>> Mirroring was working. I manually failed over and back. No problem
>> there.
>> They shut down the server within datacenter B. The server in datacenter
>> A
>> could still talk to my watcher box in the lab in our office. The primary
>> database on the server within datacenter A stayed available but it showed
>> that the mirrored server was disconnected (as expected).
>>
>> I stopped SQL Server on the watch box in the lab. At that point the
>> primary
>> database on the server within datacenter A became unavailable (Principal,
>> Disconnected / In Recovery). The database was unavailable (as expected).
>>
>> The fly in the ointment:
>> They finished working on the server within datacenter B. The box came
>> back
>> up without any issues. The other databases on that server are intact and
>> everything looks good. SQL Server is running normally...BUT the
>> database
>> mirroring did not resume. The article indicates that "If Server B is
>> restored first, mirroring resumes, although no automatic failover is
>> possible without a witness." I did NOT experience this. My "Server B"
>> has
>> been back online for 45 minutes and its status still shows as "Principal,
>> Disconnected / In Recovery" on Server A (datacenter A) and "Mirror,
>> Disconnected / In Recovery" on Server B (datacenter B).
>>
>> Has anyone else experienced this scenario? I could try turning on the
>> watch
>> server again, but I would like to see the two servers (principal and
>> mirror)
>> synchronize on their own. I realize that automatic failover will not be
>> possible until I bring the watch back online, but the mirrored
>> database(s)
>> should be available now that both the principal and mirror are back
>> online.
>> Right?
>> --
>> Keith Kratochvil
>|||Hello?
Is anyone out there?
More importantly, does anyone have any ideas, suggestions, or helpful
comments?
--
Keith Kratochvil

Database Mirroring Error

When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':

The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.

DATABASE MIRRORING TEST CONFIGURATION

SQL Server 2005 STD

Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07

A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO

B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO

C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO

D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO

E. Back up the Principal Database and copy backup files to mirror server.

F. Restore the Principal Database on the standby database server (use NORECOVERY).

G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.

1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO

2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_BB07

--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO

3. Execute the following SELECT on both servers to review mirroring information.

SELECT * FROM SYS.DATABASE_MIRRORING

Can ping both IP addresses and DNS names in both servers? If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall? Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.|||

Thanks for the info. During the meanwhile, I set up transactional replication.

|||

By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.

Thanks again.

|||

Cause and Resolution of Database Engine Errors

http://msdn2.microsoft.com/en-us/library/aa337361.aspx

http://msdn2.microsoft.com/en-us/library/ms365262.aspx|||

Did you try to install two SQL servers in same network before?

|||

I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.

Thanks.

|||

I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.

Database Mirroring in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Database mirroring setup in SQL Server 2005

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html?bucket=ETA&topic=301326

|||

Thanks. I will review the posted website.

|||

I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.

sql

Database Mirroring Error

When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':

The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.

DATABASE MIRRORING TEST CONFIGURATION

SQL Server 2005 STD

Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07

A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO

B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.

1. In the master database, create a database Master Key.

M:\DBADM\MIRRORING\CREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.

M:\DBADM\MIRRORING\CREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.

M:\DBADM\MIRRORING\CREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.

M:\DBADM\MIRRORING\CREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO

C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\BB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO

D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.

M:\DBADM\MIRRORING\CREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@.#';
GO

2. Create a user for that login.

M:\DBADM\MIRRORING\CREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:\DBADM\MIRRORING\CREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:\DBADM\MIRRORING\AA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.

M:\DBADM\MIRRORING\GRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO

E. Back up the Principal Database and copy backup files to mirror server.

F. Restore the Principal Database on the standby database server (use NORECOVERY).

G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.

1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO

2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).

M:\DBADM\MIRRORING\ACTIVATE_MIRROR_FOR_BB07

--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO

3. Execute the following SELECT on both servers to review mirroring information.

SELECT * FROM SYS.DATABASE_MIRRORING

Can ping both IP addresses and DNS names in both servers? If no, please try to add record in both networks DNS servers or add both SQL servers IP addresses in local hosts files (e.g. C:\Windows\System32\Drivers\Etc\hosts). Moreover, is it opened all SQL required ports in firewall? Detail information please refer to "How to: Configure a Firewall for SQL Server Access http://msdn2.microsoft.com/en-us/library/ms175043.aspx". Active Directory ports are required too.|||

Thanks for the info. During the meanwhile, I set up transactional replication.

|||

By the way, the servers have been added to the HOST files on each server. The ports appeared to be opened. I did a netstats -adn in the command prompt. I may stick with replication since I am on a tight deadline. I was hoping to get database mirroring to work in a short time since it appears to be easier in switching back and forth for failover scenerios.

Thanks again.

|||

Cause and Resolution of Database Engine Errors

http://msdn2.microsoft.com/en-us/library/aa337361.aspx

http://msdn2.microsoft.com/en-us/library/ms365262.aspx|||

Did you try to install two SQL servers in same network before?

|||

I wanted to first try going from server to server within the network but I could not find two server with similar configurations. So, I ended up testing database mirroring for the first time between two servers in their own DMZ space.

Thanks.

|||

I suggest you setup two testing servers in Virtual Environment, such as MS Virtual and VMware Server. It's free download and use. It easy to learn and test Database Mirroring.

Database Mirroring in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Database mirroring setup in SQL Server 2005

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1199004,00.html?bucket=ETA&topic=301326

|||

Thanks. I will review the posted website.

|||

I finally got pass the 1418 error message. The problem was with the way the server name was configured and the HOSTS file on the C drive.

Database Mirroring : Asynchronous mirroring without a witness

Hi

I am testing the manual failover of a Asynchronous mirroring without a witness but i get the following error when i issue "ALTER DATABASE testdb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS" on the mirror database server.

Both the mirror and principal is in a synchronized state before i issue the above command.

Msg 1455, Level 16, State 2, Line 1

The database mirroring service cannot be forced for database "testdb" because the database is not in the correct state to become the principal database.

Thanks,

Priyanga

To failover with Asyncronous mirroring, the preferred way to failover is to turn the safety to FULL and issue the ALTER DB SET PARTNER FAILOVER command. that way you will not lose any data when you failover.

The FORCE_SERVICE_ALLOW_DATA_LOSS command should only be used when the principal database is completely broken and it has been decided (by humans) that the mirror server should be promoted to be the new principal. Then the command is issued on the mirror.

State 2 indicates that you are issuing the command on the principal. FORCE_SERVICE can only be issued on the mirror and the mirror cannot talk to the principal.

thanks,
mark

|||

Thanks Mark.

The partner force_service_allow_data_loss command will only work on the mirror when the principal server is not reachable.

Cheers,
Priyanga

Database mirror error Msg 1447, Level 16, State 21, Line 1

we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.

ALTER DATABASE R3P
SET PARTNER resume

the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.

Hi David,

Can you please take a look at the SQL Server errorlog (on both the principal and mirror) and post the errors that are reported there at the time the command was executed.

Also, what does "SELECT * FROM sys.database_mirroring" return on each partner?

Thanks,

Kaloian.

|||Hi Kaloian,

thank you for your reply.

now the mirrow can running normally after restart the primary server.i guess that the server's issue caused the mirror can't send log to the mirror server.

when i run the following query in both server:
select * from sys.dm_db_mirroring_connections
two rows return in primary server and one row return in mirror server.so i restart the primary server after work time,then the mirror resume and about 50G log has been resent successfully.

in the error log,i can't find the ralted error except:
The alert for 'unrecovered log' has been raised. The current value of '30' surpasses the threshold '10'.
and
An I/O operation initiated by the Registry failed unrecoverably. The Registry could not read in, or write out, or flush, one of the files that contain the system's image of the Registry.

Server information:
OS:win 2003 enterprise SP1
SQL:SQL 2005 enterprise SP1
CPU:16
Memory:16G
disk:about 16T

Thanks.

Database Master Key Error

Hi,

In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?

Thanks in advance.

Regards

Babu

In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:

alter master key add encryption by service master key

HTH,
~ Remus

|||

Hi Remus,

It worked.. Thank you..

Regards

Babu

|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.

Database Master Key Error

Hi,

In sys.transmission_queue is showing the transmission_status with error. "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

But I do have a Master Key in the database, and many External Assemblies depend on that. Is there an option to use the existing master key. How to use the existing key ?

Thanks in advance.

Regards

Babu

In case your conversation spans between two databases, both databases need to have a master key. Also, the database master key has to be encrypted with the service master key. If the service master key encryption of the database master key is missing, you can add it like this:

alter master key add encryption by service master key

HTH,
~ Remus

|||

Hi Remus,

It worked.. Thank you..

Regards

Babu

|||Also had this problem on moving databases between servers using restore. Had to run this after the restore and re enable broker on each restored database.

Database Marked suspect with Error 3314

Hi,
I have a database that is constantly marked suspect with Error: 3314,
Severity: 21, State: 4 and it says "Error while undoing logged operation in
database 'prod_db'. Error at log record ID (81:7137:38)..". This database is
on SQL Server 2000 with SP3 on WIN2k AS with SP2. After it prints this
error, I also get Error: 9001, Severity: 21, State: 1 that states " The log
for database 'prod_db' is not available.. After I get the error message, the
database recovers itself and comes back on line. I have run DBCC commands
including the CHECKFILEGROUP and they are all coming up clean.
If someone can throw some light, on this I will greatly appreciate it.
Database is less than 1 GB and for data and log, it has unlimited file
growth. Recovery model for the database is "Full."
Thanks,
Sanjay.Are those the only errormessages? Is autoclose on for the database?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Sanjay" <s_dhakhwa@.hotmail.com> wrote in message news:eF$UJlylDHA.684@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a database that is constantly marked suspect with Error: 3314,
> Severity: 21, State: 4 and it says "Error while undoing logged operation in
> database 'prod_db'. Error at log record ID (81:7137:38)..". This database is
> on SQL Server 2000 with SP3 on WIN2k AS with SP2. After it prints this
> error, I also get Error: 9001, Severity: 21, State: 1 that states " The log
> for database 'prod_db' is not available.. After I get the error message, the
> database recovers itself and comes back on line. I have run DBCC commands
> including the CHECKFILEGROUP and they are all coming up clean.
> If someone can throw some light, on this I will greatly appreciate it.
> Database is less than 1 GB and for data and log, it has unlimited file
> growth. Recovery model for the database is "Full."
> Thanks,
> Sanjay.
>|||Hi Sanjay,
According to my research, this issue is mostly like a database corruption
problem. Do you have any backup of this database? If so, I would like you
to try to restore the database from the backup.
For additional information regarding restoring the database, please refer
to the following article on SQL Server Books Online.
Topic:"RESTORE"
Topic:"How to restore a database backup (Transact-SQL)"
Also, due to the complexity of this issue, it would be best to contact
Microsoft Product Support Services via telephone so that a dedicated
Support Professional can assist with your request. To obtain the phone
numbers for specific technology requests please take a look at the web site
listed below.
http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Thank you for all your suggestion.
I can restore from the backups of the database on to a different server and
all the DBCCs come out clean. The database does not have "Auto close" option
turned on. We opened up the case with microsoft since this is a really
wiered case.
Again, thanks a lot for your help.
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:Y9gqoj6lDHA.1548@.cpmsftngxa06.phx.gbl...
> Hi Sanjay,
> According to my research, this issue is mostly like a database corruption
> problem. Do you have any backup of this database? If so, I would like you
> to try to restore the database from the backup.
> For additional information regarding restoring the database, please refer
> to the following article on SQL Server Books Online.
> Topic:"RESTORE"
> Topic:"How to restore a database backup (Transact-SQL)"
> Also, due to the complexity of this issue, it would be best to contact
> Microsoft Product Support Services via telephone so that a dedicated
> Support Professional can assist with your request. To obtain the phone
> numbers for specific technology requests please take a look at the web
site
> listed below.
> http://support.microsoft.com/default.aspx?scid=fh;EN-US;PHONENUMBERS
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>

Database Maintenence Plan Error

When trying to setup a Database Maintenence Plan, it does not give me
an option for amount of days to keep before deleting, the dropdown box
is empty. also, I set the UNC path and when I go out and come back
into the plan, this is empty, but the backup runs with 17055 errors.
How can I find out how to correct this?
RickRick,
What build of SQL2005 are you running?
Chris
"Rick" <drummer10980@.gmail.com> wrote in message
news:1189777049.876020.129450@.y42g2000hsy.googlegroups.com...
> When trying to setup a Database Maintenence Plan, it does not give me
> an option for amount of days to keep before deleting, the dropdown box
> is empty. also, I set the UNC path and when I go out and come back
> into the plan, this is empty, but the backup runs with 17055 errors.
> How can I find out how to correct this?
> Rick
>|||Actually running MSDE
Rick
On Sep 14, 9:52 am, "Chris Wood" <anonym...@.microsoft.com> wrote:
> Rick,
> What build of SQL2005 are you running?
> Chris
> "Rick" <drummer10...@.gmail.com> wrote in message
> news:1189777049.876020.129450@.y42g2000hsy.googlegroups.com...
>
> > When trying to setup a Database Maintenence Plan, it does not give me
> > an option for amount of days to keep before deleting, the dropdown box
> > is empty. also, I set the UNC path and when I go out and come back
> > into the plan, this is empty, but the backup runs with 17055 errors.
> > How can I find out how to correct this?
> > Rick- Hide quoted text -
> - Show quoted text -

Database Maintenance Plans

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode.
-----
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)
You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>
|||Actually the job is attempting to ALTER DATABASE:
[vbcol=seagreen]
so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:

> You can try modifying the job for the maintenance plan and adding a step to
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> on
> SQL
> users
> SQL
> to
> --
> the
> ALL
>
>
|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...[vbcol=seagreen]
> Actually the job is attempting to ALTER DATABASE:
statement[vbcol=seagreen]
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
to[vbcol=seagreen]
set[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
statement[vbcol=seagreen]
failed.[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
needs[vbcol=seagreen]
> ----
except[vbcol=seagreen]
DISCONNECT[vbcol=seagreen]
Therefore,[vbcol=seagreen]
|||Uncheck the "attempt to repair minor problems" option in your maint plan. If you have a corruption,
you'd want to know about it and do root cause analysis instead of "having someone trying to fix it
overnight".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> -----
> As a result, I'm assuming the job must disconnect everyone first, except the
> job itself. The only way I can find to do this is to issue a DISCONNECT ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>

Database Maintenance Plans

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other users
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs to
be in single user mode
-----
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs
to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>|||Actually the job is attempting to ALTER DATABASE:
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> > failed.
so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:
> You can try modifying the job for the maintenance plan and adding a step to
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> > Setting up SQL Server Maintenance Plans. Getting the following error set
> on
> > some tasks:
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Database state cannot be changed while other
> users
> > are using the database 'abcdefg'
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> > failed.
> >
> > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> > [4] Database abcdefg: Check Data and Index Linkage...
> >
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]Repair statement not processed. Database needs
> to
> > be in single user mode.
> > ----
> --
> > As a result, I'm assuming the job must disconnect everyone first, except
> the
> > job itself. The only way I can find to do this is to issue a DISCONNECT
> ALL
> > statement. This is new turf for me, having never been a DBA. Therefore,
> > please give me any other options or feedback.
> >
> > Thanks :-)
> >
>
>|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...
> Actually the job is attempting to ALTER DATABASE:
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> > > failed.
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
> > You can try modifying the job for the maintenance plan and adding a step
to
> > do the following:
> >
> > ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> >
> > Let us know if it helps.
> >
> > -Argenis
> >
> >
> > "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> > news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> > > Setting up SQL Server Maintenance Plans. Getting the following error
set
> > on
> > > some tasks:
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Database state cannot be changed while other
> > users
> > > are using the database 'abcdefg'
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> > > failed.
> > >
> > > [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command
failed.
> > > [4] Database abcdefg: Check Data and Index Linkage...
> > >
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]Repair statement not processed. Database
needs
> > to
> > > be in single user mode.
> >
> ----
> > --
> > > As a result, I'm assuming the job must disconnect everyone first,
except
> > the
> > > job itself. The only way I can find to do this is to issue a
DISCONNECT
> > ALL
> > > statement. This is new turf for me, having never been a DBA.
Therefore,
> > > please give me any other options or feedback.
> > >
> > > Thanks :-)
> > >
> >
> >
> >|||Uncheck the "attempt to repair minor problems" option in your maint plan. If you have a corruption,
you'd want to know about it and do root cause analysis instead of "having someone trying to fix it
overnight".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption command failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs to
> be in single user mode.
> -----
> As a result, I'm assuming the job must disconnect everyone first, except the
> job itself. The only way I can find to do this is to issue a DISCONNECT ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>

Database Maintenance Plans

Setting up SQL Server Maintenance Plans. Getting the following error set on
some tasks:
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Database state cannot be changed while other u
sers
are using the database 'abcdefg'
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE st
atement
failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption comma
nd failed.
[4] Database abcdefg: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]Repair statement not processed. Database needs
to
be in single user mode.
----
--
As a result, I'm assuming the job must disconnect everyone first, except the
job itself. The only way I can find to do this is to issue a DISCONNECT ALL
statement. This is new turf for me, having never been a DBA. Therefore,
please give me any other options or feedback.
Thanks :-)You can try modifying the job for the maintenance plan and adding a step to
do the following:
ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
Let us know if it helps.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]Repair statement not processed. Database needs[/vbco
l]
to[vbcol=seagreen]
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except
the
> job itself. The only way I can find to do this is to issue a DISCONNECT
ALL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>|||Actually the job is attempting to ALTER DATABASE:

so I'm guessing it cannot because there are open connections.
Thanks anyway
"Argenis Fernandez" wrote:
[vbcol=seagreen]
> You can try modifying the job for the maintenance plan and adding a step t
o
> do the following:
> ALTER DATABASE <name> SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE.
> Let us know if it helps.
> -Argenis
>
> "Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
> news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> on
> SQL
> users
> SQL
> to
> --
> the
> ALL
>
>|||Yes, but probably the job is not issuing a "WITH ROLLBACK IMMEDIATE"
statement. Can't tell for sure. You might want to give it a try -- just
remember to bring the DB to MULTI_USER again at the end of the job.
-Argenis
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:DB23A1EC-B4EF-469C-BCD9-DAF6466FFF45@.microsoft.com...[vbcol=seagreen]
> Actually the job is attempting to ALTER DATABASE:
>
statement[vbcol=seagreen]
> so I'm guessing it cannot because there are open connections.
> Thanks anyway
>
> "Argenis Fernandez" wrote:
>
to[vbcol=seagreen]
set[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
statement[vbcol=seagreen]
failed.[vbcol=seagreen]
[Microsoft][ODBC[vbcol=seagreen]
needs[vbcol=seagreen]
> ----
except[vbcol=seagreen]
DISCONNECT[vbcol=seagreen]
Therefore,[vbcol=seagreen]|||Uncheck the "attempt to repair minor problems" option in your maint plan. If
you have a corruption,
you'd want to know about it and do root cause analysis instead of "having so
meone trying to fix it
overnight".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Brett Ossman" <BrettOssman@.discussions.microsoft.com> wrote in message
news:A2B4D992-8FB9-4F7C-A559-EB6FDCA32D66@.microsoft.com...
> Setting up SQL Server Maintenance Plans. Getting the following error set
on
> some tasks:
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 5070: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Database state cannot be changed while other
users
> are using the database 'abcdefg'
> [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER DATABASE
statement
> failed.
> [Microsoft][ODBC SQL Server Driver][SQL Server]sp_dboption com
mand failed.
> [4] Database abcdefg: Check Data and Index Linkage...
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 7919: [Microsoft]
[ODBC SQL
> Server Driver][SQL Server]Repair statement not processed. Database nee
ds to
> be in single user mode.
> ----
--
> As a result, I'm assuming the job must disconnect everyone first, except t
he
> job itself. The only way I can find to do this is to issue a DISCONNECT A
LL
> statement. This is new turf for me, having never been a DBA. Therefore,
> please give me any other options or feedback.
> Thanks :-)
>