Showing posts with label setting. Show all posts
Showing posts with label setting. Show all posts

Tuesday, March 27, 2012

database mirroring... restore issue

I'm setting it up using the directions provided by Microsoft @.
http://msdn2.microsoft.com/en-us/library/ms189047.aspx
The problem I originally ran into was exactly like the one mentioned in the
following posting:
http://www.microsoft.com/technet/community/newsgroups/managed/dgbrowser/en-us/default.mspx?&query=mirroring&lang=en&cr=US&guid=&sloc=en-US&dg=microsoft.public.sqlserver.clustering&p=1&tid=95381f96-a34a-4d99-9e31-12719452a11d
I found an article online stating that this can occur of your principle
backup wasn't restored to the mirror server in NO RECOVERY mode. I missed
that step when performing the first restore, so i deleted that database and
started over using the NO RECOVERY option on the mirror restore.
The problem I've run into now is that after my restore executes and
completes, the database remains in a (Restoring...) state and never becomes
accessible.
Anyone know what causes this?
Thank you,
- Tyler> The problem I've run into now is that after my restore executes and
> completes, the database remains in a (Restoring...) state and never becomes
> accessible.
This is expected. The mirror database *is* inaccessible. After you have done your ALTER DATABASE SET
PARTNER, you should see some other status, but the database is still inaccessible.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Tyler McLaughlin" <TylerMcLaughlin@.discussions.microsoft.com> wrote in message
news:C26DD0C5-1726-4732-B0AA-AAD302D86782@.microsoft.com...
> I'm setting it up using the directions provided by Microsoft @.
> http://msdn2.microsoft.com/en-us/library/ms189047.aspx
> The problem I originally ran into was exactly like the one mentioned in the
> following posting:
> http://www.microsoft.com/technet/community/newsgroups/managed/dgbrowser/en-us/default.mspx?&query=mirroring&lang=en&cr=US&guid=&sloc=en-US&dg=microsoft.public.sqlserver.clustering&p=1&tid=95381f96-a34a-4d99-9e31-12719452a11d
> I found an article online stating that this can occur of your principle
> backup wasn't restored to the mirror server in NO RECOVERY mode. I missed
> that step when performing the first restore, so i deleted that database and
> started over using the NO RECOVERY option on the mirror restore.
> The problem I've run into now is that after my restore executes and
> completes, the database remains in a (Restoring...) state and never becomes
> accessible.
> Anyone know what causes this?
> Thank you,
> - Tyler|||If you are looking for a "Hot Standby" (Automatic Failover\Failback)
solution and use the target server as a report server at the same time, then
you must use SQL Server Active\Active Cluster. You can not access a Mirrored
database unless you use Database Snaphosts of that mirrored database. And
this Database Snapshot thing is only possible with SQL Server 2005
Enterprise Edition.
If you say "Warm Standby (Manual Failover) would be OK for me" then go with
Transactional Replication. And you'll be able to use your replicated
database as a report server.
You can also use Log Shipping using Standby recovery state (instead of
NORECOVERY) but then you'd have to kick out all connections\users every time
before applying new transaction logs that shipped from the Primary. By the
way, this is also called as a Warm Standby solution.
--
Ekrem Ã?nsoy
"Tyler McLaughlin" <TylerMcLaughlin@.discussions.microsoft.com> wrote in
message news:C26DD0C5-1726-4732-B0AA-AAD302D86782@.microsoft.com...
> I'm setting it up using the directions provided by Microsoft @.
> http://msdn2.microsoft.com/en-us/library/ms189047.aspx
> The problem I originally ran into was exactly like the one mentioned in
> the
> following posting:
> http://www.microsoft.com/technet/community/newsgroups/managed/dgbrowser/en-us/default.mspx?&query=mirroring&lang=en&cr=US&guid=&sloc=en-US&dg=microsoft.public.sqlserver.clustering&p=1&tid=95381f96-a34a-4d99-9e31-12719452a11d
> I found an article online stating that this can occur of your principle
> backup wasn't restored to the mirror server in NO RECOVERY mode. I missed
> that step when performing the first restore, so i deleted that database
> and
> started over using the NO RECOVERY option on the mirror restore.
> The problem I've run into now is that after my restore executes and
> completes, the database remains in a (Restoring...) state and never
> becomes
> accessible.
> Anyone know what causes this?
> Thank you,
> - Tyler

Sunday, March 25, 2012

Database Mirroring Setup Document.

Hi:

I am looking for a Step-by-Step document for setting up and configuring of database mirroring if one is available. I have gone through BOL and found some related documentation but want to see if a step-by-step document is available. If so can any one please direct me to the resource.

Thank you

AK

Please look at the How-to section in the BOL.

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

Database mirroring and raw partitions

I have several servers that I am setting up using raw disk partitions for th
e
data and log storage, and at the same time I am very interested in the new
database mirroring technology in SQL 2005. My question and problem is this,
I can get mirroring to work if I have the logs on a ntfs partition, but if I
place them on a raw partition the mirror goes into a suspended state and the
error logs have the following errors:
----
--
While acting as a mirroring partner for database 'RealTime', server instance
'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
will be suspended. Try to resolve the error and resume mirroring.
Error: 1454, Severity: 16, State: 1.
CREATE FILE encountered operating system error 32(The process cannot access
the file because it is being used by another process.) while attempting to
open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'.
----
--
The raw partitions are set up using mapped folders on a ntfs partition, and
the database can execute queries 20-25% faster than an identical server I
have set up with the data and logs on NTFS partitions.Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:

> I have several servers that I am setting up using raw disk partitions for
the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is thi
s,
> I can get mirroring to work if I have the logs on a ntfs partition, but if
I
> place them on a raw partition the mirror goes into a suspended state and t
he
> error logs have the following errors:
> ----
--
> While acting as a mirroring partner for database 'RealTime', server instan
ce
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot acces
s
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3'.
> ----
--
> The raw partitions are set up using mapped folders on a ntfs partition, an
d
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>|||Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:

> I have several servers that I am setting up using raw disk partitions for
the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is thi
s,
> I can get mirroring to work if I have the logs on a ntfs partition, but if
I
> place them on a raw partition the mirror goes into a suspended state and t
he
> error logs have the following errors:
> ----
--
> While acting as a mirroring partner for database 'RealTime', server instan
ce
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot acces
s
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3'.
> ----
--
> The raw partitions are set up using mapped folders on a ntfs partition, an
d
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>

Database mirroring and raw partitions

I have several servers that I am setting up using raw disk partitions for the
data and log storage, and at the same time I am very interested in the new
database mirroring technology in SQL 2005. My question and problem is this,
I can get mirroring to work if I have the logs on a ntfs partition, but if I
place them on a raw partition the mirror goes into a suspended state and the
error logs have the following errors
-----
While acting as a mirroring partner for database 'RealTime', server instance
'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
will be suspended. Try to resolve the error and resume mirroring.
Error: 1454, Severity: 16, State: 1.
CREATE FILE encountered operating system error 32(The process cannot access
the file because it is being used by another process.) while attempting to
open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'
-----
The raw partitions are set up using mapped folders on a ntfs partition, and
the database can execute queries 20-25% faster than an identical server I
have set up with the data and logs on NTFS partitions.Hi
As you can't do normal OS operations on a RAW partition it does not really
surprise me that this is not possible, but I have not found anything to say
that!!!
What was the sector size on the NTFS volume that did not perform?
John
"MDBVV" wrote:
> I have several servers that I am setting up using raw disk partitions for the
> data and log storage, and at the same time I am very interested in the new
> database mirroring technology in SQL 2005. My question and problem is this,
> I can get mirroring to work if I have the logs on a ntfs partition, but if I
> place them on a raw partition the mirror goes into a suspended state and the
> error logs have the following errors:
> -----
> While acting as a mirroring partner for database 'RealTime', server instance
> 'DATA2' encountered error 5123, status 1, severity 16. Database mirroring
> will be suspended. Try to resolve the error and resume mirroring.
> Error: 1454, Severity: 16, State: 1.
> CREATE FILE encountered operating system error 32(The process cannot access
> the file because it is being used by another process.) while attempting to
> open or create the physical file 'C:\DriveMapping\RealTime_Log_Disk3\'.
> -----
> The raw partitions are set up using mapped folders on a ntfs partition, and
> the database can execute queries 20-25% faster than an identical server I
> have set up with the data and logs on NTFS partitions.
>
>

Thursday, March 22, 2012

Database Mirroring and Backup

I am looking for help in setting up our mirroring / backup / snapshot strategy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
Chris
Which mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris
|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris

Database Mirroring and Backup

I am looking for help in setting up our mirroring / backup / snapshot strate
gy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
ChrisWhich mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris

Database Mirroring and Backup

I am looking for help in setting up our mirroring / backup / snapshot strategy
We are definately going to use Database Mirroring
I also want to get my databases onto tape for archive. Do I only backup the
Primary SQL server? If so, do I just use the standard MS backup tools? If
so, and I backup the databases to .BAK files on the SAN, then I can just use
Veritas to backup those .BAKs to tape.
What about the Mirror SQL server? It is in a constant state of restore,
right? So how do I back that one up... or do I? If I don't, and it becomes
the Primary, then how is that newly-become Primary going to get backed-up?
Also, how can I fit Snapshots in to my scenario, to allow piecemeal restore
of my data?
thanks so much for any direction
ChrisWhich mirroring option you're planing to use?
Regarding backups of primary, you'll need to set up a strategy for backing
up. I would set up maintenance plans on both servers and then disabled the
one on the Mirror. That leaves the problem of enabling it when it becomes
primary.
I'm not certain what are your requirements so I cant really comment on the
snapshots, only that offcourse you can do them on the mirror server.
MC
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chris|||The mirror can NOT be backed up. It is in a restoring state and constantly
appying transactions.
You configure your backups against your principal database. You should also
create the same jobs on the mirror, but disable them. When the mirror fails
over, you enable the jobs and backups continue. You then need to disable
the backups on what used to be the principal. Even though your chain of
backups occurs across 2 instances when you fail it over, this does NOT break
your transaction log chain. This is because the mirror database is
maintained in lock step with the principal, including the LSN chain. (There
are considerations you need to make if you are running in high performance
mode where all of the transactions on the principal might not have made it
to the mirror when it fails over.)
Backups are backups are backups. They don't change, just because you have a
mirror involved.
Database snapshots can be created against a mirror. You can NOT backup a
snapshot nor can you revert from a snapshot unless it is the only one in
existence against the database. It has nothing to do with a piecemeal
restore. A piecemeal restore refers to the ability to restore filegroups to
the database while other portions of the database remain operational and
accessible to applications.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Chris B" <Chris B@.discussions.microsoft.com> wrote in message
news:72BC6B9D-E1CD-4E55-AF7F-718C40F52E51@.microsoft.com...
>I am looking for help in setting up our mirroring / backup / snapshot
>strategy
> We are definately going to use Database Mirroring
> I also want to get my databases onto tape for archive. Do I only backup
> the
> Primary SQL server? If so, do I just use the standard MS backup tools? If
> so, and I backup the databases to .BAK files on the SAN, then I can just
> use
> Veritas to backup those .BAKs to tape.
> What about the Mirror SQL server? It is in a constant state of restore,
> right? So how do I back that one up... or do I? If I don't, and it
> becomes
> the Primary, then how is that newly-become Primary going to get backed-up?
> Also, how can I fit Snapshots in to my scenario, to allow piecemeal
> restore
> of my data?
> thanks so much for any direction
> Chrissql

Database Mirroring - setting up the witness

SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>

Database Mirroring - setting up the witness

SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
Corporate stuff runs in the parent domain (including witness)
Hosted (client) servers run in the child domain(s).
I set up mirroring between two SQL2k5 boxes in our child domain. SQL Server
runs under a network account in the child domain. We run our corparate SQL
Servers under a different account in our parent domain.
I wanted to have the witness box (located in our corporate domain) monitor
the primary and mirrors in the various child domains. I am running into the
following error message: "The ALTER DATABASE command could not be sent to
the remote server instance 'TCP://Witness-Server.TheParentDomain.com:5022'.
The database mirroring configuration was not changed. Verify that the server
is connected, and try again."
I verified that the appropriate endpoints exist within the witness via the
following query: SELECT name, role_desc, state_desc FROM
sys.database_mirroring_endpoints
What am I doing wrong?
--
Keith KratochvilThe service accounts are most likely different between the two domains. You
need to add the service account of the witness to the SQL Server hosting the
principal and mirror and vice versa. You then have to grant CONNECT TO
permissions to the account for the Database Mirroring endpoint.
--
Mike Hotek
MHS Enterprises, Inc
http://www.mssqlserver.com
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:%23T73eWR0GHA.2036@.TK2MSFTNGP05.phx.gbl...
> SQL Server 2005 Standard Edition SP1 + .2153 hotfixes, Windows 2003 R2 SP1
> Witness: SQL Server 2005 Developer Edition SP1 + .2153 hotfixes
> Corporate stuff runs in the parent domain (including witness)
> Hosted (client) servers run in the child domain(s).
>
> I set up mirroring between two SQL2k5 boxes in our child domain. SQL
> Server runs under a network account in the child domain. We run our
> corparate SQL Servers under a different account in our parent domain.
> I wanted to have the witness box (located in our corporate domain) monitor
> the primary and mirrors in the various child domains. I am running into
> the following error message: "The ALTER DATABASE command could not be sent
> to the remote server instance
> 'TCP://Witness-Server.TheParentDomain.com:5022'. The database mirroring
> configuration was not changed. Verify that the server is connected, and
> try again."
> I verified that the appropriate endpoints exist within the witness via the
> following query: SELECT name, role_desc, state_desc FROM
> sys.database_mirroring_endpoints
> What am I doing wrong?
> --
> Keith Kratochvil
>
>sql

Sunday, March 11, 2012

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 :-)
>

Thursday, March 8, 2012

Database Maintenance Plan Grief

I've been setting up database maintenance plans for years, but lately, I've
been stumped by this particular database maintenance plan.
All it contains are full backup routines for 2 databases in Simple Recovery
Mode in a named instance to write out to a local drive. Every aspect of the
plan deals with local resources: e.g. not master / target server
relationships, no UNC drive paths, no linked servers, everything as simple
as can be. Drive space is more than ample. However, executing the database
maintenance plan-created job will fail. Maintenance Plan reports show
nothing. Searching google and KB has yielded nothing.
When I create a separate full backup job by using the 'BACKUP DATABASE'
command that virtually has the same characteristics as the maintenance plan
(same schedule, dumps to the same dir), this particular job runs flawlessly.
The job and SQL Server Agent are assigned to accounts that have unrestricted
access to the system at hand.
However, what I did was take the command from the job created by the
Database Maintenance Plan and attempt to run it in QA. Here are the
results.
COMMAND
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft SQL
Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
[Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
NULL
(7 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Here's my system information:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )
I would do two things.
1. Try the basic same plan, but using sqlmaint.exe
2. Check server network utility and client network utility (for instance if you have configured some
alias).
The error suggests that sqlmaint.exe can't find the sql server. Perhaps the sql server doesn't
listen to the IP netlib. Also, check the errorlog file. This should include info about the netlibs
listened to and also if there were any errors with this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H Lee" <anon@.anon.com> wrote in message news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
> I've been setting up database maintenance plans for years, but lately, I've been stumped by this
> particular database maintenance plan.
> All it contains are full backup routines for 2 databases in Simple Recovery Mode in a named
> instance to write out to a local drive. Every aspect of the plan deals with local resources: e.g.
> not master / target server relationships, no UNC drive paths, no linked servers, everything as
> simple as can be. Drive space is more than ample. However, executing the database maintenance
> plan-created job will fail. Maintenance Plan reports show nothing. Searching google and KB has
> yielded nothing.
> When I create a separate full backup job by using the 'BACKUP DATABASE' command that virtually has
> the same characteristics as the maintenance plan (same schedule, dumps to the same dir), this
> particular job runs flawlessly.
> The job and SQL Server Agent are assigned to accounts that have unrestricted access to the system
> at hand.
> However, what I did was take the command from the job created by the Database Maintenance Plan and
> attempt to run it in QA. Here are the results.
> COMMAND
> --
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program
> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program Files\Microsoft SQL
> Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>
> output
> ----------
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
> NULL
> (7 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
>
> Here's my system information:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>
|||I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
follows:
d:
cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
sqlmaint
-S <appropriate_server_name>
-U "_test_sa"
-P "_test_sa"
-D master
and received the following error:
Executed as user: <domain_name>\<user_name>. The process could not be
created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason: Access
is denied). The step failed.
I'll check the server network utility on the server soon. Client network
Utility seems OK-- my SQL EM and QA have been connecting fine to the server
and are still able to connect successfully.
ERRORLOG's show that SQL is successfully listening on TCP. This server has
had many users who successfully connect to it multiple times each day.
"SQL server listening on TCP, Shared Memory, Named Pipes"
I do see one error before this, however:
"Resource Manager Creation Failed: Result Code = 0x8004d01c"
Any help would be appreciated.
Thanks,
- H -
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
>I would do two things.
> 1. Try the basic same plan, but using sqlmaint.exe
> 2. Check server network utility and client network utility (for instance
> if you have configured some alias).
> The error suggests that sqlmaint.exe can't find the sql server. Perhaps
> the sql server doesn't listen to the IP netlib. Also, check the errorlog
> file. This should include info about the netlibs listened to and also if
> there were any errors with this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "H Lee" <anon@.anon.com> wrote in message
> news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
--
>
|||It turns out that, indeed, an alias in the SQL Server's own Client Network
Utility was referring to itself by its own NetBIOS name but had a
manually-set port number that was different from what the server was
actually listening on.
Craziness.
Thanks for your help.
"H Lee" <anon@.anon.com> wrote in message
news:%237eYXQzVFHA.2560@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
>I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
>follows:
>
> d:
> cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
> sqlmaint
> -S <appropriate_server_name>
> -U "_test_sa"
> -P "_test_sa"
> -D master
>
> and received the following error:
> Executed as user: <domain_name>\<user_name>. The process could not be
> created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason:
> Access is denied). The step failed.
> I'll check the server network utility on the server soon. Client network
> Utility seems OK-- my SQL EM and QA have been connecting fine to the
> server and are still able to connect successfully.
> ERRORLOG's show that SQL is successfully listening on TCP. This server
> has had many users who successfully connect to it multiple times each day.
> "SQL server listening on TCP, Shared Memory, Named Pipes"
> I do see one error before this, however:
> "Resource Manager Creation Failed: Result Code = 0x8004d01c"
> Any help would be appreciated.
> Thanks,
> - H -
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
--
>

Database Maintenance Plan Grief

I've been setting up database maintenance plans for years, but lately, I've
been stumped by this particular database maintenance plan.
All it contains are full backup routines for 2 databases in Simple Recovery
Mode in a named instance to write out to a local drive. Every aspect of the
plan deals with local resources: e.g. not master / target server
relationships, no UNC drive paths, no linked servers, everything as simple
as can be. Drive space is more than ample. However, executing the database
maintenance plan-created job will fail. Maintenance Plan reports show
nothing. Searching google and KB has yielded nothing.
When I create a separate full backup job by using the 'BACKUP DATABASE'
command that virtually has the same characteristics as the maintenance plan
(same schedule, dumps to the same dir), this particular job runs flawlessly.
The job and SQL Server Agent are assigned to accounts that have unrestricted
access to the system at hand.
However, what I did was take the command from the job created by the
Database Maintenance Plan and attempt to run it in QA. Here are the
results.
COMMAND
--
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft SQL
Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
output
----
----
----
--
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
[Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][
ODBC SQL
Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied
.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpe
n
(Connect()).
NULL
(7 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Here's my system information:
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )I would do two things.
1. Try the basic same plan, but using sqlmaint.exe
2. Check server network utility and client network utility (for instance if
you have configured some
alias).
The error suggests that sqlmaint.exe can't find the sql server. Perhaps the
sql server doesn't
listen to the IP netlib. Also, check the errorlog file. This should include
info about the netlibs
listened to and also if there were any errors with this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H Lee" <anon@.anon.com> wrote in message news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...[vbc
ol=seagreen]
> I've been setting up database maintenance plans for years, but lately, I'v
e been stumped by this
> particular database maintenance plan.
> All it contains are full backup routines for 2 databases in Simple Recover
y Mode in a named
> instance to write out to a local drive. Every aspect of the plan deals wi
th local resources: e.g.
> not master / target server relationships, no UNC drive paths, no linked se
rvers, everything as
> simple as can be. Drive space is more than ample. However, executing the
database maintenance
> plan-created job will fail. Maintenance Plan reports show nothing. Searc
hing google and KB has
> yielded nothing.
> When I create a separate full backup job by using the 'BACKUP DATABASE' co
mmand that virtually has
> the same characteristics as the maintenance plan (same schedule, dumps to
the same dir), this
> particular job runs flawlessly.
> The job and SQL Server Agent are assigned to accounts that have unrestrict
ed access to the system
> at hand.
> However, what I did was take the command from the job created by the Datab
ase Maintenance Plan and
> attempt to run it in QA. Here are the results.
> COMMAND
> --
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0763D5A4-5A35-4993-B7FA-0CC4D44DF
EE3 -Rpt "G:\Program
> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt
" -DelTxtRpt
> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program File
s\Microsoft SQL
> Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>
> output
> ----
----
----
--[/vbcol]
--
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft]
1;ODBC SQL Server Driver][TCP/IP
> Sockets]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionO
pen (Connect()).
> NULL
> (7 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
>
> Here's my system information:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>|||I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
follows:
d:
cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
sqlmaint
-S <appropriate_server_name>
-U "_test_sa"
-P "_test_sa"
-D master
and received the following error:
Executed as user: <domain_name>\<user_name>. The process could not be
created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason: Access
is denied). The step failed.
I'll check the server network utility on the server soon. Client network
Utility seems OK-- my SQL EM and QA have been connecting fine to the server
and are still able to connect successfully.
ERRORLOG's show that SQL is successfully listening on TCP. This server has
had many users who successfully connect to it multiple times each day.
"SQL server listening on TCP, Shared Memory, Named Pipes"
I do see one error before this, however:
"Resource Manager Creation Failed: Result Code = 0x8004d01c"
Any help would be appreciated.
Thanks,
- H -
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
>I would do two things.
> 1. Try the basic same plan, but using sqlmaint.exe
> 2. Check server network utility and client network utility (for instance
> if you have configured some alias).
> The error suggests that sqlmaint.exe can't find the sql server. Perhaps
> the sql server doesn't listen to the IP netlib. Also, check the errorlog
> file. This should include info about the netlibs listened to and also if
> there were any errors with this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "H Lee" <anon@.anon.com> wrote in message
> news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
--[vbcol=seagreen]
>|||It turns out that, indeed, an alias in the SQL Server's own Client Network
Utility was referring to itself by its own NetBIOS name but had a
manually-set port number that was different from what the server was
actually listening on.
Craziness.
Thanks for your help.
"H Lee" <anon@.anon.com> wrote in message
news:%237eYXQzVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
>follows:
>
> d:
> cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
> sqlmaint
> -S <appropriate_server_name>
> -U "_test_sa"
> -P "_test_sa"
> -D master
>
> and received the following error:
> Executed as user: <domain_name>\<user_name>. The process could not be
> created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason:
> Access is denied). The step failed.
> I'll check the server network utility on the server soon. Client network
> Utility seems OK-- my SQL EM and QA have been connecting fine to the
> server and are still able to connect successfully.
> ERRORLOG's show that SQL is successfully listening on TCP. This server
> has had many users who successfully connect to it multiple times each day.
> "SQL server listening on TCP, Shared Memory, Named Pipes"
> I do see one error before this, however:
> "Resource Manager Creation Failed: Result Code = 0x8004d01c"
> Any help would be appreciated.
> Thanks,
> - H -
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
--[vbcol=seagreen]
>

Database Maintenance Plan Grief

I've been setting up database maintenance plans for years, but lately, I've
been stumped by this particular database maintenance plan.
All it contains are full backup routines for 2 databases in Simple Recovery
Mode in a named instance to write out to a local drive. Every aspect of the
plan deals with local resources: e.g. not master / target server
relationships, no UNC drive paths, no linked servers, everything as simple
as can be. Drive space is more than ample. However, executing the database
maintenance plan-created job will fail. Maintenance Plan reports show
nothing. Searching google and KB has yielded nothing.
When I create a separate full backup job by using the 'BACKUP DATABASE'
command that virtually has the same characteristics as the maintenance plan
(same schedule, dumps to the same dir), this particular job runs flawlessly.
The job and SQL Server Agent are assigned to accounts that have unrestricted
access to the system at hand.
However, what I did was take the command from the job created by the
Database Maintenance Plan and attempt to run it in QA. Here are the
results.
COMMAND
--
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft SQL
Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
output
-----------
NULL
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
Copyright (C) Microsoft Corporation, 1995 - 1998
NULL
[Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
(Connect()).
NULL
(7 row(s) affected)
Server: Msg 22029, Level 16, State 1, Line 0
sqlmaint.exe failed.
Here's my system information:
--
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: )I would do two things.
1. Try the basic same plan, but using sqlmaint.exe
2. Check server network utility and client network utility (for instance if you have configured some
alias).
The error suggests that sqlmaint.exe can't find the sql server. Perhaps the sql server doesn't
listen to the IP netlib. Also, check the errorlog file. This should include info about the netlibs
listened to and also if there were any errors with this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H Lee" <anon@.anon.com> wrote in message news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
> I've been setting up database maintenance plans for years, but lately, I've been stumped by this
> particular database maintenance plan.
> All it contains are full backup routines for 2 databases in Simple Recovery Mode in a named
> instance to write out to a local drive. Every aspect of the plan deals with local resources: e.g.
> not master / target server relationships, no UNC drive paths, no linked servers, everything as
> simple as can be. Drive space is more than ample. However, executing the database maintenance
> plan-created job will fail. Maintenance Plan reports show nothing. Searching google and KB has
> yielded nothing.
> When I create a separate full backup job by using the 'BACKUP DATABASE' command that virtually has
> the same characteristics as the maintenance plan (same schedule, dumps to the same dir), this
> particular job runs flawlessly.
> The job and SQL Server Agent are assigned to accounts that have unrestricted access to the system
> at hand.
> However, what I did was take the command from the job created by the Database Maintenance Plan and
> attempt to run it in QA. Here are the results.
> COMMAND
> --
> EXECUTE master.dbo.xp_sqlmaint N'-PlanID 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program
> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program Files\Microsoft SQL
> Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>
> output
> -----------
> NULL
> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
> Copyright (C) Microsoft Corporation, 1995 - 1998
> NULL
> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][TCP/IP
> Sockets]SQL Server does not exist or access denied.
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()).
> NULL
> (7 row(s) affected)
> Server: Msg 22029, Level 16, State 1, Line 0
> sqlmaint.exe failed.
>
> Here's my system information:
> --
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>|||I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
follows:
d:
cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
sqlmaint
-S <appropriate_server_name>
-U "_test_sa"
-P "_test_sa"
-D master
and received the following error:
Executed as user: <domain_name>\<user_name>. The process could not be
created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason: Access
is denied). The step failed.
I'll check the server network utility on the server soon. Client network
Utility seems OK-- my SQL EM and QA have been connecting fine to the server
and are still able to connect successfully.
ERRORLOG's show that SQL is successfully listening on TCP. This server has
had many users who successfully connect to it multiple times each day.
"SQL server listening on TCP, Shared Memory, Named Pipes"
I do see one error before this, however:
"Resource Manager Creation Failed: Result Code = 0x8004d01c"
Any help would be appreciated.
Thanks,
- H -
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
>I would do two things.
> 1. Try the basic same plan, but using sqlmaint.exe
> 2. Check server network utility and client network utility (for instance
> if you have configured some alias).
> The error suggests that sqlmaint.exe can't find the sql server. Perhaps
> the sql server doesn't listen to the IP netlib. Also, check the errorlog
> file. This should include info about the netlibs listened to and also if
> there were any errors with this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "H Lee" <anon@.anon.com> wrote in message
> news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
>> I've been setting up database maintenance plans for years, but lately,
>> I've been stumped by this particular database maintenance plan.
>> All it contains are full backup routines for 2 databases in Simple
>> Recovery Mode in a named instance to write out to a local drive. Every
>> aspect of the plan deals with local resources: e.g. not master / target
>> server relationships, no UNC drive paths, no linked servers, everything
>> as simple as can be. Drive space is more than ample. However, executing
>> the database maintenance plan-created job will fail. Maintenance Plan
>> reports show nothing. Searching google and KB has yielded nothing.
>> When I create a separate full backup job by using the 'BACKUP DATABASE'
>> command that virtually has the same characteristics as the maintenance
>> plan (same schedule, dumps to the same dir), this particular job runs
>> flawlessly.
>> The job and SQL Server Agent are assigned to accounts that have
>> unrestricted access to the system at hand.
>> However, what I did was take the command from the job created by the
>> Database Maintenance Plan and attempt to run it in QA. Here are the
>> results.
>> COMMAND
>> --
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft SQL
>> Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
>> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
>> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>>
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
>> Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
>> (Connect()).
>> NULL
>> (7 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>>
>> Here's my system information:
>> --
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>|||It turns out that, indeed, an alias in the SQL Server's own Client Network
Utility was referring to itself by its own NetBIOS name but had a
manually-set port number that was different from what the server was
actually listening on.
Craziness.
Thanks for your help.
"H Lee" <anon@.anon.com> wrote in message
news:%237eYXQzVFHA.2560@.TK2MSFTNGP10.phx.gbl...
>I tried creating a CmdExec SQL Server Agent job to run sqlmaint.exe as
>follows:
>
> d:
> cd d:\"Program Files"\"Microsoft SQL Server"\<server_name_dir>\binn
> sqlmaint
> -S <appropriate_server_name>
> -U "_test_sa"
> -P "_test_sa"
> -D master
>
> and received the following error:
> Executed as user: <domain_name>\<user_name>. The process could not be
> created for step 1 of job 0x8982332A9E97994B8A010AEC322ABCAB (reason:
> Access is denied). The step failed.
> I'll check the server network utility on the server soon. Client network
> Utility seems OK-- my SQL EM and QA have been connecting fine to the
> server and are still able to connect successfully.
> ERRORLOG's show that SQL is successfully listening on TCP. This server
> has had many users who successfully connect to it multiple times each day.
> "SQL server listening on TCP, Shared Memory, Named Pipes"
> I do see one error before this, however:
> "Resource Manager Creation Failed: Result Code = 0x8004d01c"
> Any help would be appreciated.
> Thanks,
> - H -
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:OguZDcxVFHA.3176@.TK2MSFTNGP12.phx.gbl...
>>I would do two things.
>> 1. Try the basic same plan, but using sqlmaint.exe
>> 2. Check server network utility and client network utility (for instance
>> if you have configured some alias).
>> The error suggests that sqlmaint.exe can't find the sql server. Perhaps
>> the sql server doesn't listen to the IP netlib. Also, check the errorlog
>> file. This should include info about the netlibs listened to and also if
>> there were any errors with this.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "H Lee" <anon@.anon.com> wrote in message
>> news:%23qD5qUxVFHA.2328@.TK2MSFTNGP10.phx.gbl...
>> I've been setting up database maintenance plans for years, but lately,
>> I've been stumped by this particular database maintenance plan.
>> All it contains are full backup routines for 2 databases in Simple
>> Recovery Mode in a named instance to write out to a local drive. Every
>> aspect of the plan deals with local resources: e.g. not master / target
>> server relationships, no UNC drive paths, no linked servers, everything
>> as simple as can be. Drive space is more than ample. However,
>> executing the database maintenance plan-created job will fail.
>> Maintenance Plan reports show nothing. Searching google and KB has
>> yielded nothing.
>> When I create a separate full backup job by using the 'BACKUP DATABASE'
>> command that virtually has the same characteristics as the maintenance
>> plan (same schedule, dumps to the same dir), this particular job runs
>> flawlessly.
>> The job and SQL Server Agent are assigned to accounts that have
>> unrestricted access to the system at hand.
>> However, what I did was take the command from the job created by the
>> Database Maintenance Plan and attempt to run it in QA. Here are the
>> results.
>> COMMAND
>> --
>> EXECUTE master.dbo.xp_sqlmaint N'-PlanID
>> 0763D5A4-5A35-4993-B7FA-0CC4D44DFEE3 -Rpt "G:\Program Files\Microsoft
>> SQL Server\MSSQL$ABSSQL_CLU1\LOG\DB Maintenance Plan14.txt" -DelTxtRpt
>> 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "G:\Program
>> Files\Microsoft SQL Server\MSSQL$ABSSQL_CLU1\BACKUP" -BkExt "BAK"'
>>
>> output
>> -----------
>> NULL
>> Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.760
>> Copyright (C) Microsoft Corporation, 1995 - 1998
>> NULL
>> [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL
>> Server Driver][TCP/IP Sockets]SQL Server does not exist or access
>> denied.
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
>> (Connect()).
>> NULL
>> (7 row(s) affected)
>> Server: Msg 22029, Level 16, State 1, Line 0
>> sqlmaint.exe failed.
>>
>> Here's my system information:
>> --
>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>> Dec 17 2002 14:22:05
>> Copyright (c) 1988-2003 Microsoft Corporation
>> Enterprise Edition on Windows NT 5.2 (Build 3790: )
>>
>

Sunday, February 19, 2012

Database Mail

I am just setting up database mail on a new server. It is up and
working correctly, however when I run a trace looking for things that
have a very long running duration I see this statement over and over.
I can't find any documentation on what it is, besides part of database
mail. Is this normal or did I miss configure something?
Durations of 30-40 seconds, 0 CPU time, 40-50 reads
exec sp_readrequest @.receive_timeout=600000
IT is configured to send emails to our corporate Exchange server.The procedure is issuing a WAITFOR(RECEIVE ...) waiting for a mail message
to be enqueued for sending. As such, the duration shows all the time the
procedure is stays in the WAITFOR(...) as execution time. This is not real
execution (CPU and I/O consumption), so you should just ignore it.
HTH,
~ Remus
<cploessel@.gmail.com> wrote in message
news:1165877369.144745.176210@.16g2000cwy.googlegroups.com...
>I am just setting up database mail on a new server. It is up and
> working correctly, however when I run a trace looking for things that
> have a very long running duration I see this statement over and over.
> I can't find any documentation on what it is, besides part of database
> mail. Is this normal or did I miss configure something?
> Durations of 30-40 seconds, 0 CPU time, 40-50 reads
> exec sp_readrequest @.receive_timeout=600000
>
> IT is configured to send emails to our corporate Exchange server.
>|||Not sure what this has to do with the original question. This sp reads a
service broker queue to obtain mail to send. It suspends until a mail
message appears on the queue. The duration is the length of time the queue
has no messages to send so it could easily have a duration of an hour or
more. When it's waiting for a message to appear it doesn't use resources
other than an idle execution thread.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<cploessel@.gmail.com> wrote in message
news:1165877369.144745.176210@.16g2000cwy.googlegroups.com...
>I am just setting up database mail on a new server. It is up and
> working correctly, however when I run a trace looking for things that
> have a very long running duration I see this statement over and over.
> I can't find any documentation on what it is, besides part of database
> mail. Is this normal or did I miss configure something?
> Durations of 30-40 seconds, 0 CPU time, 40-50 reads
> exec sp_readrequest @.receive_timeout=600000
>
> IT is configured to send emails to our corporate Exchange server.
>

Database Mail

I am just setting up database mail on a new server. It is up and
working correctly, however when I run a trace looking for things that
have a very long running duration I see this statement over and over.
I can't find any documentation on what it is, besides part of database
mail. Is this normal or did I miss configure something?
Durations of 30-40 seconds, 0 CPU time, 40-50 reads
exec sp_readrequest @.receive_timeout=600000
IT is configured to send emails to our corporate Exchange server.The procedure is issuing a WAITFOR(RECEIVE ...) waiting for a mail message
to be enqueued for sending. As such, the duration shows all the time the
procedure is stays in the WAITFOR(...) as execution time. This is not real
execution (CPU and I/O consumption), so you should just ignore it.
HTH,
~ Remus
<cploessel@.gmail.com> wrote in message
news:1165877369.144745.176210@.16g2000cwy.googlegroups.com...
>I am just setting up database mail on a new server. It is up and
> working correctly, however when I run a trace looking for things that
> have a very long running duration I see this statement over and over.
> I can't find any documentation on what it is, besides part of database
> mail. Is this normal or did I miss configure something?
> Durations of 30-40 seconds, 0 CPU time, 40-50 reads
> exec sp_readrequest @.receive_timeout=600000
>
> IT is configured to send emails to our corporate Exchange server.
>|||Not sure what this has to do with the original question. This sp reads a
service broker queue to obtain mail to send. It suspends until a mail
message appears on the queue. The duration is the length of time the queue
has no messages to send so it could easily have a duration of an hour or
more. When it's waiting for a message to appear it doesn't use resources
other than an idle execution thread.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<cploessel@.gmail.com> wrote in message
news:1165877369.144745.176210@.16g2000cwy.googlegroups.com...
>I am just setting up database mail on a new server. It is up and
> working correctly, however when I run a trace looking for things that
> have a very long running duration I see this statement over and over.
> I can't find any documentation on what it is, besides part of database
> mail. Is this normal or did I miss configure something?
> Durations of 30-40 seconds, 0 CPU time, 40-50 reads
> exec sp_readrequest @.receive_timeout=600000
>
> IT is configured to send emails to our corporate Exchange server.
>

Database Mail

I am just setting up database mail on a new server. It is up and
working correctly, however when I run a trace looking for things that
have a very long running duration I see this statement over and over.
I can't find any documentation on what it is, besides part of database
mail. Is this normal or did I miss configure something?
Durations of 30-40 seconds, 0 CPU time, 40-50 reads
exec sp_readrequest @.receive_timeout=600000
IT is configured to send emails to our corporate Exchange server.
The procedure is issuing a WAITFOR(RECEIVE ...) waiting for a mail message
to be enqueued for sending. As such, the duration shows all the time the
procedure is stays in the WAITFOR(...) as execution time. This is not real
execution (CPU and I/O consumption), so you should just ignore it.
HTH,
~ Remus
<cploessel@.gmail.com> wrote in message
news:1165877369.144745.176210@.16g2000cwy.googlegro ups.com...
>I am just setting up database mail on a new server. It is up and
> working correctly, however when I run a trace looking for things that
> have a very long running duration I see this statement over and over.
> I can't find any documentation on what it is, besides part of database
> mail. Is this normal or did I miss configure something?
> Durations of 30-40 seconds, 0 CPU time, 40-50 reads
> exec sp_readrequest @.receive_timeout=600000
>
> IT is configured to send emails to our corporate Exchange server.
>
|||Not sure what this has to do with the original question. This sp reads a
service broker queue to obtain mail to send. It suspends until a mail
message appears on the queue. The duration is the length of time the queue
has no messages to send so it could easily have a duration of an hour or
more. When it's waiting for a message to appear it doesn't use resources
other than an idle execution thread.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
<cploessel@.gmail.com> wrote in message
news:1165877369.144745.176210@.16g2000cwy.googlegro ups.com...
>I am just setting up database mail on a new server. It is up and
> working correctly, however when I run a trace looking for things that
> have a very long running duration I see this statement over and over.
> I can't find any documentation on what it is, besides part of database
> mail. Is this normal or did I miss configure something?
> Durations of 30-40 seconds, 0 CPU time, 40-50 reads
> exec sp_readrequest @.receive_timeout=600000
>
> IT is configured to send emails to our corporate Exchange server.
>

Tuesday, February 14, 2012

Database level setting : Allow user input chinese character~

Dear all,

To allow users enter chinese character into table, I did try to change the field type to nchar and this is workable. But I have few hundred tables.. Is there anyway to change the setting in easier way? by instead change the field type one by one for each table?

I tried change the collation to Chinese_PRC_90_BIN for the database, but it is not support chinese input..

Any Idea?

Thanks.

You will have to ALTER each table, changing the datatype from char()/varchar() to nchar()/nvarchar() on each column that will contain Chinese characters.

There is a undocumented system stored procedure, sp_MSForEachTable that may be useful in combination with using dynamic SQL.

There have been recent postings related to a similar issue, perhaps a search in the forums for 'sp_MSForEachTable', as well as a Google? search would also be useful.

|||

Hi,

you can use the following script to change every char / varchar column to contain unicode data. The statement will create ALTER statements which can be examined (in the case that you do not want to change every columns and want to sort out the appropiate ones)


SELECT
'ALTER TABLE [' + T.TABLE_NAME + ']' +
' ALTER COLUMN' + '[' + COLUMN_NAME + '] N' + DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)) + ');'
FROM [INFORMATION_SCHEMA].[COLUMNS] C
INNER JOIN [INFORMATION_SCHEMA].[TABLES] t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
C.TABLE_NAME = t.TABLE_NAME
WHERE data_type IN ('CHAR','VARCHAR')
AND t.TABLE_TYPE = 'BASE TABLE'

Jens K. Suessmeyer.


http://www.sqlserver2005.de