re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

re-establishing mirror

When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

On the witness server the following sql

select * from sys.database_mirroring_witnesses

returns 1 row with the previous mirroring information.

How do I remove old information from witness server? Thanks.

If the witness server contacts the former principal, it should discover that mirroring was turned off and remove the metadata from master.

What version of SQL are you using? Build number?

Thanks,

Mark

|||

Witness came up but it did not remove the metadata. I am using SQL server 2005 sp1. Build number is 9.0.2047. Thanks

Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks.

|||

Thanks for the info.

"Is there any way I can manually remove the information from witness or force the primary server to use witness and overwrite wintness information? Thanks."

1. There is no way to manually remove the witness information.

2. The principal server should be able to over write the witness information.

My guess is that there is a communication problem between the two servers. Did you change the port # for mirroring? Look in the errorlog. It should have more information about what went wrong. My guess is that you are setting up mirroring again and somehow changed the network configuration.

Thanks,

Mark

|||

Witness is showing the following error:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://primary:5022'.

Primary server has the following error in the error log:

Database mirroring connection error 4 'An error occurred while receiving data: '1236(The network connection was aborted by the local system.)'.' for 'TCP://witness:5024'.

Server combination have been changed after the initial breakdown of database mirroring. So initial primary server is no longer the new primary server. So you are right about network settings being changed. What can I do so that I can reestablish the mirroring? Thanks.

|||

So, the only way that the witness can drop its mirroring information is to connect to one of the original servers and be told that the database is not there or the mirroring session is different. Now you can't change any of the connection parameters on the witness.

Setup a server just like on of the partners in the original scenario, which means have a sql server instance running on a server with the same name as one of the earlier partners AND have a mirroring endpoint up and running on the original port with the original settings.

The witness is using the orginal connection properties to log into the partner servers once a second. Once it gets to a sql server instance on the original ports, the witness will be told that database for which it wants to be a witness for doesn't exist or it isn't mirrored. At that point the witness will stop trying to be a witness.

As far as setting up the new witness, are you sure that the servers can connect to each other?

Thanks,

Mark

|||

I can't set up the original server. I don't have another computer to name as the original computer name and I can't change the name of the existing servers.

Current primary and witness can see each other. I created linked servers and verified queries.

When I am going through the wizard setting up mirroring on the primary server, security configuration is working fine. When I press start mirroring button, I am getting the error. Please let me know if you want to verify connection any other way or have any other ideas to set up mirroring. Thanks.

|||

So, if you can telnet into the endpoints from each server, then the endpoints on setup properly.

Is it still failing with the same error number? I believe that the witness server should be able to be a new witness server for this new database because the mirroring GUID will be different from the previous one.

But, to get rid of the original metadata for the first witness config, you need to have that server talk to sql on the original port and name.

Thanks,

Mark

|||If I can't get rid of original metadata, I can live with that. However I can't use the witness server because it is failing with the same error. Database name is same from the original one. I can try with a new database name to see if database name is the problem. I need to make this work with the original database name. The only other thing I can think of is reinstall mirror server. Thanks.|||I used a different database. Witness server did not work. Finally I completely uninstalled witness server and reinstalled wintness server. Re-established mirroring using the new mirror server. Now mirroring is working fine. So when things go wrong witness server will have certain stuff which will be only cleaned up if it can see the original servers. If it can't see the original servers then we can not only clean up witness server, that server will not be able to participate as witness server again. This could be an isolated situation, in Microsoft labs as Mark indicated, witness server works on unique guids, so one mirror combination should not effect others. In my case some thing went wrong. In rare cases like this it would be nicer if I can clean up the witness server rather than reinstall. Thanks very much Mark for all your help. On a different thread I asked about changing synchronous to asynchronous when witness and mirror are down. I would like to read your comments on that kind of thinking. Thanks again.|||

OK, so I'm sorry you had so much trouble with mirroring. The witness should have been able to be used as is with the metadata intact.

Since the information for the witness is stored in master. If you had a copy of master from before, you could have restored it and see what happens.

Since the database isn't on the witness server, there really isn't a good way to turn off the witness on the witness. We'll think about that.

Thanks,

Mark

|||I did not think about that. That's a good suggestion. Thanks Mark.|||I am having this very issue using certificate-based authentication. Am I correct in understanding that the current resolution, in absence of a backup of Master, is to uninstall and reinstall SQL Server 2005 SP1 on the witness server?|||

FYI, as it turned out in my case, I had been trying to use the IP address to configure the Witness (This worked for Principal and Mirror). E.g.:

ALTER DATABASE [AdventureWorks]
SET WITNESS = N'TCP://10.0.0.5:10001';
GO

That T-SQL statement, run on Principal, produced this error:

Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://10.0.0.5:10001'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.

The Windows Event Log said:
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://10.0.0.5:10001'.

The solution to my problems was when I put entries for Principal, Mirror, and Witness in each machine's C:\WINDOWS\system32\drivers\etc\hosts file.

Now it works.

|||

Hi,

I think i am having the same mirroring issues as you had.....in my case, server1 is the principal, server2 is the mirror and server2\witness is the witness..

i am getting the Msg 1456 error when i go through the mirroring wizard

in event log, i am getting the same error as you had...

can you please explain a little bit more on how to add entries in the hosts file?

thanks

Re-Establishing Maintenance Jobs?

I have a client that hasn't had a successful run of any of the four
"standard" maintenance jobs in over 4 months. (Integrity Check,
Optimization, DB Backup, and Transaction Log Backup).
I know what the problem is, but I'm not his DBA (in fact I'm a mere contract
software developer). Apparently my client doesn't have a DBA, or this
wouldn't be the case, and he may turn to me to fix the problem. (I suspect
that they renamed the server, and the job owner is listed with the old
server name).
I'm logging in remotely, so I'm somewhat apprehensive of simply turning the
four jobs on overnight. I can envision problems with temp space, disk
space, and possibly excessive time required for index rebuilds. I don't
know if a CHECKDB takes longer if it hasn't been done in a while.
Is there anything else I should be prepared for?
Is there a recommended approach to "softly" bring a potentially ailing (no
symptoms that I know of) database back into a verified and backed up state?
Should I do DBCC CHECKTABLE on a table at a time before doing CHECKDB, or is
it best to do DBCC CHECKDB to start with?
Would the following be a good sequence:
1: Verify disk space available compared to backup space required (how do I
estimate the backup space required?).
1: Manually perform backup of the database and Transaction log.
2: Manually perform DBCC CHECKDB (Estimate Only first to verify sufficient
space in tempdb). I assume I should do it as REPAIR_FAST?
3: Manually perform DBCC INDEXDEFRAG before or instead of DBCC REINDEX
4: Re-Establish the Agent Jobs.
TIA,
Tore.Thanks.
This is a 24/7 system (medium traffic web site), so my rationale for doing
the initial backup before anything else was to make sure that a copy of the
database would be available just in case something went very wrong during
the subsequent operations. I know the (a) backup really needs to be not
just on another drive, but offline and preferably off-site. However, the
initial backup would only be for the time it took to get everything back
into normal working order - at what point their overall server backup
procedures would (hopefully) address the proper security of the database
backups.
From your response, I guess I don't need to worry about problems during
checkdb or dbreindex or whether the database will remain in working order
should an error be found?
Should I specify FAST_REPAIR on the initial run of CHECKDB?
Tore.
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:uW6MFHCWDHA.1512@.TK2MSFTNGP11.phx.gbl...
> See inline
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it
community
> of SQL Server professionals.
> www.sqlpass.org
>
> "Tore Bostrup" <newspost_at_bostrup.us> wrote in message
> news:#KnN$7#VDHA.1620@.TK2MSFTNGP12.phx.gbl...
> > I have a client that hasn't had a successful run of any of the four
> > "standard" maintenance jobs in over 4 months. (Integrity Check,
> > Optimization, DB Backup, and Transaction Log Backup).
> >
> > I know what the problem is, but I'm not his DBA (in fact I'm a mere
> contract
> > software developer). Apparently my client doesn't have a DBA, or this
> > wouldn't be the case, and he may turn to me to fix the problem. (I
> suspect
> > that they renamed the server, and the job owner is listed with the old
> > server name).
> >
> > I'm logging in remotely, so I'm somewhat apprehensive of simply turning
> the
> > four jobs on overnight. I can envision problems with temp space, disk
> > space, and possibly excessive time required for index rebuilds. I don't
> > know if a CHECKDB takes longer if it hasn't been done in a while.
> The time checkdb takes is related to the amount of IO required to read the
> records.
> >
> > Is there anything else I should be prepared for?
> >
> > Is there a recommended approach to "softly" bring a potentially ailing
(no
> > symptoms that I know of) database back into a verified and backed up
> state?
> > Should I do DBCC CHECKTABLE on a table at a time before doing CHECKDB,
or
> is
> > it best to do DBCC CHECKDB to start with?
> I would IMMEDIATELY implement backups - period...
> THen add CHECKDB - regardless of how long it takes...
> >
> > Would the following be a good sequence:
> >
> > 1: Verify disk space available compared to backup space required (how
do
> I
> > estimate the backup space required?).
> Backup the database either to tape or to a remote hard drive, backing up
on
> the local box does not protect from local box burn ups...sp_spaceused
> should show about how big things will be..
> > 1: Manually perform backup of the database and Transaction log.
> > 2: Manually perform DBCC CHECKDB (Estimate Only first to verify
> sufficient
> > space in tempdb). I assume I should do it as REPAIR_FAST?
> > 3: Manually perform DBCC INDEXDEFRAG before or instead of DBCC REINDEX
> I would probably do dbcc dbreindex (at least the first time) since it has
> been forever since maintenance has been done... on highly fragmented
tables
> indexdefrag can take longer than dbreindex. (after the first time, do
> whichever of the two you prefer.)
> > 4: Re-Establish the Agent Jobs.
> When scheduling the jobs, run index maintenance BEFORE the db backups...
The
> backup will then be a copy of a well maintained database...so if you ever
> need to restore, you will NOT have to immediately do index maintenance
after
> the restore..
> >
> > TIA,
> > Tore.
> >
> >
>

Re-establishing link to SQL from ACCESS XP (wireless)

Hi,
I am developing an ACCESS ADP that links to MS SQL, the SQL is on a server,
the ACCESS links to it via a wireless network. When it goes out of wireless
range, I will lose the link to the database. Is there a standard way to hav
e
it re-link effortlessly (quickly) when it comes back into range. I don't
want the operation to have to do anything.
Thanks,
SteveYou can write code to relink the tables. The Knowledge Base
used to have sample code to do this, the Access FAQ has
sample code - http://www.mvps.org/access/
-Sue
On Mon, 10 Apr 2006 13:40:02 -0700, SteveInBeloit
<SteveInBeloit@.discussions.microsoft.com> wrote:

>Hi,
>I am developing an ACCESS ADP that links to MS SQL, the SQL is on a server,
>the ACCESS links to it via a wireless network. When it goes out of wireles
s
>range, I will lose the link to the database. Is there a standard way to ha
ve
>it re-link effortlessly (quickly) when it comes back into range. I don't
>want the operation to have to do anything.
>Thanks,
>Steve