Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

GO

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

GO

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....

Database Mirroring hangs on ALTER DATABASE SET PARTNER

Hi,

Here is the scenario. I currently have a single XP Professional machine with three SQL Server instances installed. I'm trying to set up a database mirroring session.

The mirror instance was created with an endpoint listening on port 5151. The principal instance endpoint listens on port 5150. The witness listens on port 5152.

After creating the endpoints, I back up the database I wish to mirror on the principal instance, and then restore with NORECOVERY on the mirror instance.

I then execute the following code on the mirror instance:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5151'

GO

This works fine. I then go to the principal to add it as a partner too:

ALTER DATABASE BookStore

SET PARTNER = 'TCP://192.168.0.2:5150'

GO

The query runs indefinitely. I finally stop it after several minutes, and I see in the object browser that the database has the following in parenthesis "(Principal, Synchronizing)". I then cannot drop, modify, or do anything with the principal database.

Ideas?

hmmmm....

the first ALTER DATABASE should always work (as long as the db has been restored with NORECOVERY) because it puts the database in a "waiting to be contacted" mode.

you should be using the FQDN instead of the IP. i think you are getting away with that because you are on the same server.

your port mappings are not right. if the mirror is listening on 5151, then the principal should be issuing its ALTER DATABASE with the port 5151 on it, not 5150 as you have indicated.

thanks,
mark|||Thanks for the response, Mark.

I went ahead and tried switching the ALTER DATABASE to point to the mirror port, and get the following message:

Msg 1418, Level 16, State 1, Line 3

The server instance "TCP://192.168.0.2:5022" is not running or does not exist. Check the name and reissue the command.

You said I should be on FQDN (but unfortunately this the only machine where I can test this - and it isn't on a domain). What's more, I used the GUI to set up mirroring, and it worked.

Right now my goal is to see if I can get it to work using just Transact-SQL . (I thought to capture what SQL Server was doing via the Wizard with Profiler, but I'm getting an "Cannot retrieve trace definition for SQL Server version
9.0.1187." error on that).

Thanks for any thoughts you may have on this...

|||

we aren't testing using the direct TCP-IP address for setting up database mirroring, so you need to use the FQDN.

there is a section on troubleshooting database mirroring in BOL specifically designed for this error message. please look at that.

specifically,
1. for the FQDN use the output described in the troubleshooting giude to use the output from ipconfig to set the name.
2. you should be able to run sqlservr.exe as LocalSystem as long as the instances are on the same machine.

mark

|||Thank you Mark.|||

I use this script to setup test mirrored db

-- On Principal
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

create database testMirror
--sp_helpdb testmirror

alter database testmirror
modify file (name=testmirror_log, size=1,maxsize=2,filegrowth=1)

alter database testmirror
modify file (name=testmirror, Maxsize=3,filegrowth=2)

alter database testMirror set recovery full

-- We use SQL lite speed if you don't have then you are missing out, however you can use SQL backup

exec master..xp_backup_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.init=1
exec master..xp_backup_log @.database='testMirror', @.filename='\\SERVERA\\sqlbak\testMirror_log.lsb',@.init=1

-- On mirror
IF NOT EXISTS(SELECT * FROM sys.endpoints WHERE type = 4)
CREATE ENDPOINT DBMirroring STATE=STARTED AS TCP (LISTENER_PORT = 5022) FOR DATABASE_MIRRORING (ROLE = ALL)

exec master..xp_restore_database @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror.lsb',@.With='NoRecovery'

exec master..xp_restore_log @.database='testMirror', @.filename='\\SERVERA\sqlbak\testMirror_log.lsb',@.With='NoRecovery'
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERA:5022'

-- On principal
ALTER DATABASE testMirror SET PARTNER = 'TCP://SERVERB:5022'

-- testing failing over, run on principal
ALTER DATABASE testMirror SET PARTNER FAILOVER

-- once testing is completed, break the mirror and drop the database
ALTER DATABASE testMirror SET PARTNER off
drop database testMirror

--NOTE: I have set this up not using FQDN and it works OK, also this script is for between servers

-- I have tried setting this up on a VISTA machine running 2 instances of SQL (dev edition) however I can get mirroring to work YET, I think I'm hitting a firewall issue (could be wrong), my ports are open correctly and I can see then via netstat - a

-- I'm getting errors like The Network address XYZ can't be reached or does not exist

-- Oh The joys of firewalls and ports....

Database Marked as Single User Pls Help

Hi All,
Sql Server 7

I have database called ecatalog

i have a scheduled job which shrinks the database every day once at 12 am

today the job got failed

In the view Job history its showing the below contents
-----------------
Database 'ecatalog' is already open and can only have one user at a time. [SQLSTATE 42000] (Error
924) DBCC execution completed. If DBCC printed error messages, contact your system
administrator. [SQLSTATE 01000] (Message 2528). The step failed.

And in Application Log of event viewer its showing the below contents
------------------------
The description for Event ID ( 208 ) in Source ( SQLServerAgent$ABCSQL ) cannot be found. The
local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Shrink Database
- ecatalog, 0xA0C4F8157A744244A61A4ECABE8C6056, Failed, 4/19/2004 12:00:03 AM, The job failed.
The Job was invoked by Schedule 27 (Shrink Database - ecatalog). The last step to run was step 1 (Shrink Database - ecatalog)..

I ran the job manually it worked fine

but now when i go and see the database ecatalog in my Enterprise Manager
it is showing ecatalog(Single User)
What is the meaning of this, will this make any problem to my database

Please help me in this.

Waiting for Reply

AdilIt means only one connection can be allowed in this db. use sp_dboption 'ecatalog', 'single', 'false' to turn it off.|||Hi Thanks for reply,

I wanted to know that does this happens on its own or has some one has done it.

Thanks waiting for reply
Adil|||No it won't do this on its own. check all jobs to see if any sp_dboption command would have done that. Also check sql error logs and look for a series of Closing file, Starting up database and Opening file. that may represent an event to put the db in single user mode.

Note some operatins do require single user mode, such as using sp_rename to change a db name.|||I think that many of the maintenance jobs will put the database into SINGLE USER mode because it is required for some kinds of maintenance. Those jobs normally take the database back out of SINGLE USER automagically when they complete.

-PatP