Does anyone know if you can mirror a database while users are banging away on
it? I can't see how I'd get the log backups in sync on the mirror server,
it's pretty busy with transactions. I've done some small scale testing and it
seems like you can apply a log backup while the data on the primary is being
changed and it still sets up the mirror ok. It's a 24/7 system and we have to
schedule some downtime otherwise.
Thanks in advance.
Hi
How do you know that log backups are ansync?
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:98F90C89-B307-4FB3-85D1-ACA8CA2D2297@.microsoft.com...
> Does anyone know if you can mirror a database while users are banging away
> on
> it? I can't see how I'd get the log backups in sync on the mirror server,
> it's pretty busy with transactions. I've done some small scale testing and
> it
> seems like you can apply a log backup while the data on the primary is
> being
> changed and it still sets up the mirror ok. It's a 24/7 system and we have
> to
> schedule some downtime otherwise.
> Thanks in advance.
Showing posts with label cant. Show all posts
Showing posts with label cant. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Database Mirroring Problems - Can't drop database
Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.
Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>
|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.
|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.
Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>
|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.
|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Database Mirroring Problems - Can't drop database
Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Database Mirroring Problems - Can't drop database
Doing some mirroring testing and have run into problems.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Firstly, the database failed over to the secondary server. This is fine
and I wanted to manually fail back to the Primary but the databases
would not synchronize. The database is < 4mb and I let it try to
synchronize for an hour, it had clearly got itself into a mess. I then
deleted the "restoring database" on the Primary server and restored
from backup, intending to setup mirroring again. The restore was fine.
However now on the secondary server the database thinks it is in the
"principal disconnected" state.
If I try and delete it, I cannot drop the database because it is
currently in use. (No user connections are active.)
"Alter database X set partner off" fails as it says it cannot connect
to the mirror.
Any "alter database" variation (like setting it to single user), will
fail as SQL thinks the database "is involved in a database mirroring
session".
It fails if I try and detach it with - 'only user processes can be
killed.
I can't think of anything else to try. So how can I drop this database
and get it going again?
TIA,
S.Hi
Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
John
"Calculated" wrote:
> Doing some mirroring testing and have run into problems.
> Firstly, the database failed over to the secondary server. This is fine
> and I wanted to manually fail back to the Primary but the databases
> would not synchronize. The database is < 4mb and I let it try to
> synchronize for an hour, it had clearly got itself into a mess. I then
> deleted the "restoring database" on the Primary server and restored
> from backup, intending to setup mirroring again. The restore was fine.
> However now on the secondary server the database thinks it is in the
> "principal disconnected" state.
> If I try and delete it, I cannot drop the database because it is
> currently in use. (No user connections are active.)
> "Alter database X set partner off" fails as it says it cannot connect
> to the mirror.
> Any "alter database" variation (like setting it to single user), will
> fail as SQL thinks the database "is involved in a database mirroring
> session".
> It fails if I try and detach it with - 'only user processes can be
> killed.
> I can't think of anything else to try. So how can I drop this database
> and get it going again?
> TIA,
> S.
>|||John Bell wrote:
> Hi
> Have you tried issuing ALTER DATABASE <database_name> SET PARTNER OFF?
> John
>
Thanks for the reply John. Yes I have. The command returns:
Msg 1404, Level 16, State 11, Line 3
The command failed because the database mirror is busy. Reissue the
command later.
Which is not helpful as the "mirror" in this case is a new database
sans any mirroring. This error also occurs if I attempt to pause the
mirroring in the Management Studio.|||Have now been able to delete the database - restarting the SQL service
on the secondary database put the database into a state where it was
able to be deleted. I didn't really want to do this but it has worked
ok and the database is no more.
Thursday, March 8, 2012
Database Maintenance Plan - Can't select Remove Files Older Than .
Hello,
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.
Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> no change.
> Thanks in advance for any help or insight.
|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.
Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> no change.
> Thanks in advance for any help or insight.
|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.
Database Maintenance Plan - Can't select Remove Files Older Than .
Hello,
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> no change.
> Thanks in advance for any help or insight.|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.|||No, I definiately had the problem before I upgraded to SP4. I just tried the
SP4 upgrade in hopes that it would fix the problem.
"j.goines" wrote:
> hi dhood,
> i have the same problem!
> is it possible that this problem exists since you updated to sp4?
> because in my case all sql2000sp4 have this problem, sp3 not...
> with mangerstudio from sql 2005 this problem is gone, but others appear...
> greets
> jeff
> "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > Hello,
> >
> > Having a strange problem and hoping that someone else has seen this
> > before.
> >
> > Have an up and running SQL Server 2000 (no problems that I can detect).
> > Trying to create a maintenance plan to backup the databases each night.
> > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > Directory" screen and the "Remove Files Older Than" drop down box is
> > blank,
> > can't select "Weeks", "Days", etc.
> >
> > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > with
> > no change.
> >
> > Thanks in advance for any help or insight.
>
>|||I have tried it against another server, same problem. I have tried accessing
the problem server from the tools on another server and everything looks
normal. So it appears to be something specific on that machine.
"John Bell" wrote:
> Hi
> Have you tried using the tools on a different machine or against a different
> server?
> John
> "DHood" wrote:
> > Hello,
> >
> > Having a strange problem and hoping that someone else has seen this before.
> >
> > Have an up and running SQL Server 2000 (no problems that I can detect).
> > Trying to create a maintenance plan to backup the databases each night.
> > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > Directory" screen and the "Remove Files Older Than" drop down box is blank,
> > can't select "Weeks", "Days", etc.
> >
> > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> > no change.
> >
> > Thanks in advance for any help or insight.|||Hi
It certainly seems that a dll or somthing is corrupted on that machine. Did
you remove the shared dlls when you un-installed the client tools?
John
"DHood" wrote:
> No, I definiately had the problem before I upgraded to SP4. I just tried the
> SP4 upgrade in hopes that it would fix the problem.
> "j.goines" wrote:
> > hi dhood,
> > i have the same problem!
> > is it possible that this problem exists since you updated to sp4?
> > because in my case all sql2000sp4 have this problem, sp3 not...
> > with mangerstudio from sql 2005 this problem is gone, but others appear...
> >
> > greets
> > jeff
> >
> > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > Hello,
> > >
> > > Having a strange problem and hoping that someone else has seen this
> > > before.
> > >
> > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > Trying to create a maintenance plan to backup the databases each night.
> > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > blank,
> > > can't select "Weeks", "Days", etc.
> > >
> > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > with
> > > no change.
> > >
> > > Thanks in advance for any help or insight.
> >
> >
> >|||No, I didn't remove any files manually. Do you know what and where they are?
"John Bell" wrote:
> Hi
> It certainly seems that a dll or somthing is corrupted on that machine. Did
> you remove the shared dlls when you un-installed the client tools?
> John
> "DHood" wrote:
> > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > SP4 upgrade in hopes that it would fix the problem.
> >
> > "j.goines" wrote:
> >
> > > hi dhood,
> > > i have the same problem!
> > > is it possible that this problem exists since you updated to sp4?
> > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > >
> > > greets
> > > jeff
> > >
> > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > Hello,
> > > >
> > > > Having a strange problem and hoping that someone else has seen this
> > > > before.
> > > >
> > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > Trying to create a maintenance plan to backup the databases each night.
> > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > blank,
> > > > can't select "Weeks", "Days", etc.
> > > >
> > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > with
> > > > no change.
> > > >
> > > > Thanks in advance for any help or insight.
> > >
> > >
> > >|||Hi
When you un-install it usually prompts to remove shared files. You may want
to see what dlls are loaded when you start the application.
John
"DHood" wrote:
> No, I didn't remove any files manually. Do you know what and where they are?
> "John Bell" wrote:
> > Hi
> >
> > It certainly seems that a dll or somthing is corrupted on that machine. Did
> > you remove the shared dlls when you un-installed the client tools?
> >
> > John
> >
> > "DHood" wrote:
> >
> > > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > > SP4 upgrade in hopes that it would fix the problem.
> > >
> > > "j.goines" wrote:
> > >
> > > > hi dhood,
> > > > i have the same problem!
> > > > is it possible that this problem exists since you updated to sp4?
> > > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > > >
> > > > greets
> > > > jeff
> > > >
> > > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > > Hello,
> > > > >
> > > > > Having a strange problem and hoping that someone else has seen this
> > > > > before.
> > > > >
> > > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > > Trying to create a maintenance plan to backup the databases each night.
> > > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > > blank,
> > > > > can't select "Weeks", "Days", etc.
> > > > >
> > > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > > with
> > > > > no change.
> > > > >
> > > > > Thanks in advance for any help or insight.
> > > >
> > > >
> > > >|||See if this helps--
If you profile when on that form you should see the following:
xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
I was getting the same problem that you were, and discovered that this proc
was returning an error b/c the registry key did not exist. I entered key and
a valid value and the problem went away.
I know several people have posted on this same issue, so I hope someone gets
some value out of this.
JIM BOUTIT
MCSE MCDBA
"John Bell" wrote:
> Hi
> When you un-install it usually prompts to remove shared files. You may want
> to see what dlls are loaded when you start the application.
> John
>
> "DHood" wrote:
> > No, I didn't remove any files manually. Do you know what and where they are?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > It certainly seems that a dll or somthing is corrupted on that machine. Did
> > > you remove the shared dlls when you un-installed the client tools?
> > >
> > > John
> > >
> > > "DHood" wrote:
> > >
> > > > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > > > SP4 upgrade in hopes that it would fix the problem.
> > > >
> > > > "j.goines" wrote:
> > > >
> > > > > hi dhood,
> > > > > i have the same problem!
> > > > > is it possible that this problem exists since you updated to sp4?
> > > > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > > > >
> > > > > greets
> > > > > jeff
> > > > >
> > > > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > > > Hello,
> > > > > >
> > > > > > Having a strange problem and hoping that someone else has seen this
> > > > > > before.
> > > > > >
> > > > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > > > Trying to create a maintenance plan to backup the databases each night.
> > > > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > > > blank,
> > > > > > can't select "Weeks", "Days", etc.
> > > > > >
> > > > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > > > with
> > > > > > no change.
> > > > > >
> > > > > > Thanks in advance for any help or insight.
> > > > >
> > > > >
> > > > >
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> no change.
> Thanks in advance for any help or insight.|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.|||No, I definiately had the problem before I upgraded to SP4. I just tried the
SP4 upgrade in hopes that it would fix the problem.
"j.goines" wrote:
> hi dhood,
> i have the same problem!
> is it possible that this problem exists since you updated to sp4?
> because in my case all sql2000sp4 have this problem, sp3 not...
> with mangerstudio from sql 2005 this problem is gone, but others appear...
> greets
> jeff
> "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > Hello,
> >
> > Having a strange problem and hoping that someone else has seen this
> > before.
> >
> > Have an up and running SQL Server 2000 (no problems that I can detect).
> > Trying to create a maintenance plan to backup the databases each night.
> > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > Directory" screen and the "Remove Files Older Than" drop down box is
> > blank,
> > can't select "Weeks", "Days", etc.
> >
> > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > with
> > no change.
> >
> > Thanks in advance for any help or insight.
>
>|||I have tried it against another server, same problem. I have tried accessing
the problem server from the tools on another server and everything looks
normal. So it appears to be something specific on that machine.
"John Bell" wrote:
> Hi
> Have you tried using the tools on a different machine or against a different
> server?
> John
> "DHood" wrote:
> > Hello,
> >
> > Having a strange problem and hoping that someone else has seen this before.
> >
> > Have an up and running SQL Server 2000 (no problems that I can detect).
> > Trying to create a maintenance plan to backup the databases each night.
> > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > Directory" screen and the "Remove Files Older Than" drop down box is blank,
> > can't select "Weeks", "Days", etc.
> >
> > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
> > no change.
> >
> > Thanks in advance for any help or insight.|||Hi
It certainly seems that a dll or somthing is corrupted on that machine. Did
you remove the shared dlls when you un-installed the client tools?
John
"DHood" wrote:
> No, I definiately had the problem before I upgraded to SP4. I just tried the
> SP4 upgrade in hopes that it would fix the problem.
> "j.goines" wrote:
> > hi dhood,
> > i have the same problem!
> > is it possible that this problem exists since you updated to sp4?
> > because in my case all sql2000sp4 have this problem, sp3 not...
> > with mangerstudio from sql 2005 this problem is gone, but others appear...
> >
> > greets
> > jeff
> >
> > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > Hello,
> > >
> > > Having a strange problem and hoping that someone else has seen this
> > > before.
> > >
> > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > Trying to create a maintenance plan to backup the databases each night.
> > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > blank,
> > > can't select "Weeks", "Days", etc.
> > >
> > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > with
> > > no change.
> > >
> > > Thanks in advance for any help or insight.
> >
> >
> >|||No, I didn't remove any files manually. Do you know what and where they are?
"John Bell" wrote:
> Hi
> It certainly seems that a dll or somthing is corrupted on that machine. Did
> you remove the shared dlls when you un-installed the client tools?
> John
> "DHood" wrote:
> > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > SP4 upgrade in hopes that it would fix the problem.
> >
> > "j.goines" wrote:
> >
> > > hi dhood,
> > > i have the same problem!
> > > is it possible that this problem exists since you updated to sp4?
> > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > >
> > > greets
> > > jeff
> > >
> > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > Hello,
> > > >
> > > > Having a strange problem and hoping that someone else has seen this
> > > > before.
> > > >
> > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > Trying to create a maintenance plan to backup the databases each night.
> > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > blank,
> > > > can't select "Weeks", "Days", etc.
> > > >
> > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > with
> > > > no change.
> > > >
> > > > Thanks in advance for any help or insight.
> > >
> > >
> > >|||Hi
When you un-install it usually prompts to remove shared files. You may want
to see what dlls are loaded when you start the application.
John
"DHood" wrote:
> No, I didn't remove any files manually. Do you know what and where they are?
> "John Bell" wrote:
> > Hi
> >
> > It certainly seems that a dll or somthing is corrupted on that machine. Did
> > you remove the shared dlls when you un-installed the client tools?
> >
> > John
> >
> > "DHood" wrote:
> >
> > > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > > SP4 upgrade in hopes that it would fix the problem.
> > >
> > > "j.goines" wrote:
> > >
> > > > hi dhood,
> > > > i have the same problem!
> > > > is it possible that this problem exists since you updated to sp4?
> > > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > > >
> > > > greets
> > > > jeff
> > > >
> > > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > > Hello,
> > > > >
> > > > > Having a strange problem and hoping that someone else has seen this
> > > > > before.
> > > > >
> > > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > > Trying to create a maintenance plan to backup the databases each night.
> > > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > > blank,
> > > > > can't select "Weeks", "Days", etc.
> > > > >
> > > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > > with
> > > > > no change.
> > > > >
> > > > > Thanks in advance for any help or insight.
> > > >
> > > >
> > > >|||See if this helps--
If you profile when on that form you should see the following:
xp_instance_regread N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory'
I was getting the same problem that you were, and discovered that this proc
was returning an error b/c the registry key did not exist. I entered key and
a valid value and the problem went away.
I know several people have posted on this same issue, so I hope someone gets
some value out of this.
JIM BOUTIT
MCSE MCDBA
"John Bell" wrote:
> Hi
> When you un-install it usually prompts to remove shared files. You may want
> to see what dlls are loaded when you start the application.
> John
>
> "DHood" wrote:
> > No, I didn't remove any files manually. Do you know what and where they are?
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > It certainly seems that a dll or somthing is corrupted on that machine. Did
> > > you remove the shared dlls when you un-installed the client tools?
> > >
> > > John
> > >
> > > "DHood" wrote:
> > >
> > > > No, I definiately had the problem before I upgraded to SP4. I just tried the
> > > > SP4 upgrade in hopes that it would fix the problem.
> > > >
> > > > "j.goines" wrote:
> > > >
> > > > > hi dhood,
> > > > > i have the same problem!
> > > > > is it possible that this problem exists since you updated to sp4?
> > > > > because in my case all sql2000sp4 have this problem, sp3 not...
> > > > > with mangerstudio from sql 2005 this problem is gone, but others appear...
> > > > >
> > > > > greets
> > > > > jeff
> > > > >
> > > > > "DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > > > news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> > > > > > Hello,
> > > > > >
> > > > > > Having a strange problem and hoping that someone else has seen this
> > > > > > before.
> > > > > >
> > > > > > Have an up and running SQL Server 2000 (no problems that I can detect).
> > > > > > Trying to create a maintenance plan to backup the databases each night.
> > > > > > Start the maintenance plan wizard and get to the "Specify Backup Disk
> > > > > > Directory" screen and the "Remove Files Older Than" drop down box is
> > > > > > blank,
> > > > > > can't select "Weeks", "Days", etc.
> > > > > >
> > > > > > Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> > > > > > with
> > > > > > no change.
> > > > > >
> > > > > > Thanks in advance for any help or insight.
> > > > >
> > > > >
> > > > >
Database Maintenance Plan - Can't select Remove Files Older Than .
Hello,
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before
.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank
,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, wi
th
> no change.
> Thanks in advance for any help or insight.|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.
Having a strange problem and hoping that someone else has seen this before.
Have an up and running SQL Server 2000 (no problems that I can detect).
Trying to create a maintenance plan to backup the databases each night.
Start the maintenance plan wizard and get to the "Specify Backup Disk
Directory" screen and the "Remove Files Older Than" drop down box is blank,
can't select "Weeks", "Days", etc.
Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, with
no change.
Thanks in advance for any help or insight.Hi
Have you tried using the tools on a different machine or against a different
server?
John
"DHood" wrote:
> Hello,
> Having a strange problem and hoping that someone else has seen this before
.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is blank
,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4, wi
th
> no change.
> Thanks in advance for any help or insight.|||hi dhood,
i have the same problem!
is it possible that this problem exists since you updated to sp4?
because in my case all sql2000sp4 have this problem, sp3 not...
with mangerstudio from sql 2005 this problem is gone, but others appear...
greets
jeff
"DHood" <DHood@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0563B8C1-891B-4AF1-B49C-6EF3E39BA511@.microsoft.com...
> Hello,
> Having a strange problem and hoping that someone else has seen this
> before.
> Have an up and running SQL Server 2000 (no problems that I can detect).
> Trying to create a maintenance plan to backup the databases each night.
> Start the maintenance plan wizard and get to the "Specify Backup Disk
> Directory" screen and the "Remove Files Older Than" drop down box is
> blank,
> can't select "Weeks", "Days", etc.
> Have re-installed the Enterprise Manager tools and upgraded to SQL SP4,
> with
> no change.
> Thanks in advance for any help or insight.
Wednesday, March 7, 2012
database maintenance for companies that run 24/7
For a company like an airline that runs 24/7 and the database is always
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application.
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.
|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>
|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly[vbcol=seagreen]
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> their
shrink
>
|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is[vbcol=seagreen]
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> no
should[vbcol=seagreen]
an[vbcol=seagreen]
> into
you[vbcol=seagreen]
> highly
always
> shrink
>
|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> is
the[vbcol=seagreen]
Almost[vbcol=seagreen]
> should
> an
> you
> always
on
>
being accessed and they can't shut down, how do they do maintenance on their
databases? How do they do things like defrag or rebuild indexes or shrink
files?
Do they have two databases and they switch back and forth?
Thanks,
Dan
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases?
Clustering, perhaps? We use it here...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Generally they use dbcc indexdefrag instead of dbcc dbreindex, you just need to be more diligent with its application.
It also becomes critical to size the database correctly so that it does not go offline for database growths/shrinks.
|||They do what they have to based on their specific requirements. Almost no
one that is in a 24 x 7 will be doing any shrinking of files and it should
rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
option and INDEDXDEFRAG might be the way to go. It is impossableto go into
enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
have this type of requirement and are asking these questions I would highly
recommend you hire a qualified consultant.
Andrew J. Kelly SQL MVP
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> For a company like an airline that runs 24/7 and the database is always
> being accessed and they can't shut down, how do they do maintenance on
their
> databases? How do they do things like defrag or rebuild indexes or shrink
> files?
> Do they have two databases and they switch back and forth?
> Thanks,
> Dan
>
|||Good points. I was thinking more along the lines of applying security
fixes, windows update, etc.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:#Qh#aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly
> recommend you hire a qualified consultant.
|||Ken and Andrew,
So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it is
sufficient. Isn't there fragmentation that also happens to the data on the
disk as well as to the indexes? How would you handle that kind of
fragmentation?
Dan
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> They do what they have to based on their specific requirements. Almost
no
> one that is in a 24 x 7 will be doing any shrinking of files and it should
> rarely be done anyway. If it's truly 24 x 7 then DBREINDEX may not be an
> option and INDEDXDEFRAG might be the way to go. It is impossableto go
into
> enough detail of how to maintain a 24 x 7 operation in a newsgroup. If you
> have this type of requirement and are asking these questions I would
highly[vbcol=seagreen]
> recommend you hire a qualified consultant.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:%23T1z%23uaPEHA.252@.TK2MSFTNGP10.phx.gbl...
> their
shrink
>
|||Depending on what you mean by 'as efficient' (disk space, log space, cpu
usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more efficient
that DBREINDEX - it's meant to be an online alternative to rebuilding
indexes. You should read the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> Ken and Andrew,
> So, even though INDEXDEFRAG isn't as efficient as DBREINDEX, I guess it
is[vbcol=seagreen]
> sufficient. Isn't there fragmentation that also happens to the data on the
> disk as well as to the indexes? How would you handle that kind of
> fragmentation?
> Dan
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:%23Qh%23aDbPEHA.4036@.TK2MSFTNGP12.phx.gbl...
> no
should[vbcol=seagreen]
an[vbcol=seagreen]
> into
you[vbcol=seagreen]
> highly
always
> shrink
>
|||Thanks. I will.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uRVJRJcPEHA.252@.TK2MSFTNGP10.phx.gbl...
> Depending on what you mean by 'as efficient' (disk space, log space, cpu
> usage, IOs, elapsed time), I didn't design INDEXDEFRAG to be more
efficient
> that DBREINDEX - it's meant to be an online alternative to rebuilding
> indexes. You should read the whitepaper below for more details.
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:#FdM04bPEHA.556@.tk2msftngp13.phx.gbl...
> is
the[vbcol=seagreen]
Almost[vbcol=seagreen]
> should
> an
> you
> always
on
>
Sunday, February 19, 2012
Database Mail (can't connect to SMTP server)
I'm new to Database Mail and SQL 2005. I have an existing SMTP server (not local) which I'm able to connect to and send mail using Outlook. When I try to connect to the same SMTP server in Database Mail, I get the following error in the Log:
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-09-20T17:04:21). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine).
)
No matter what variation I try, I can't connect to the server. I also tried setting up a local SMTP server (the one that comes with IIS). I was able to send mail out successfully from Outlook using this local SMTP server too. However, I get the same error when trying the local server in Database Mail.
This is probably something stupid I'm missing. Any ideas?
I'm using SQL 2005 Developer Edition to test applications on Windows XP Pro.
Thanks,
James
We are experiencing the same issue, same error message but on SQL 2005 Enterprise Edition- (Database Mail).
Thanks,
Carmen
|||Same here ... i checked the error from
SELECT * FROM msdb.dbo.sysmail_event_log
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-11-15T06:58:44). Exception Message: Could not connect to mail server. (No such host is known). )
But i know my Host is right .
Thanks
Praveen
|||I had same problem using dbmail on my laptop, but I have figured out what was wrong. The anti-virus program is blocking any port 25 message out, so my dbmail cannot reach my mail server. I have another dbmail on a sql server in the same network segment as the mail server is can send mail without problem. Check with your network admin to see if anything is blocking port 25 message (anti-virus, firewall etc.). By the way, Outlook uses MAPI API to talk to Exchange Server.
Good luck.
Database Mail (can't connect to SMTP server)
I'm new to Database Mail and SQL 2005. I have an existing SMTP server (not local) which I'm able to connect to and send mail using Outlook. When I try to connect to the same SMTP server in Database Mail, I get the following error in the Log:
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-09-20T17:04:21). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine).
)
No matter what variation I try, I can't connect to the server. I also tried setting up a local SMTP server (the one that comes with IIS). I was able to send mail out successfully from Outlook using this local SMTP server too. However, I get the same error when trying the local server in Database Mail.
This is probably something stupid I'm missing. Any ideas?
I'm using SQL 2005 Developer Edition to test applications on Windows XP Pro.
Thanks,
James
We are experiencing the same issue, same error message but on SQL 2005 Enterprise Edition- (Database Mail).
Thanks,
Carmen
|||Same here ... i checked the error from
SELECT * FROM msdb.dbo.sysmail_event_log
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-11-15T06:58:44). Exception Message: Could not connect to mail server. (No such host is known). )
But i know my Host is right .
Thanks
Praveen
|||I had same problem using dbmail on my laptop, but I have figured out what was wrong. The anti-virus program is blocking any port 25 message out, so my dbmail cannot reach my mail server. I have another dbmail on a sql server in the same network segment as the mail server is can send mail without problem. Check with your network admin to see if anything is blocking port 25 message (anti-virus, firewall etc.). By the way, Outlook uses MAPI API to talk to Exchange Server.
Good luck.
Database Mail (can't connect to SMTP server)
I'm new to Database Mail and SQL 2005. I have an existing SMTP server (not local) which I'm able to connect to and send mail using Outlook. When I try to connect to the same SMTP server in Database Mail, I get the following error in the Log:
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2006-09-20T17:04:21). Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine).
)
No matter what variation I try, I can't connect to the server. I also tried setting up a local SMTP server (the one that comes with IIS). I was able to send mail out successfully from Outlook using this local SMTP server too. However, I get the same error when trying the local server in Database Mail.
This is probably something stupid I'm missing. Any ideas?
I'm using SQL 2005 Developer Edition to test applications on Windows XP Pro.
Thanks,
James
We are experiencing the same issue, same error message but on SQL 2005 Enterprise Edition- (Database Mail).
Thanks,
Carmen
|||Same here ... i checked the error from
SELECT * FROM msdb.dbo.sysmail_event_log
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2006-11-15T06:58:44). Exception Message: Could not connect to mail server. (No such host is known). )
But i know my Host is right .
Thanks
Praveen
|||I had same problem using dbmail on my laptop, but I have figured out what was wrong. The anti-virus program is blocking any port 25 message out, so my dbmail cannot reach my mail server. I have another dbmail on a sql server in the same network segment as the mail server is can send mail without problem. Check with your network admin to see if anything is blocking port 25 message (anti-virus, firewall etc.). By the way, Outlook uses MAPI API to talk to Exchange Server.
Good luck.
Subscribe to:
Posts (Atom)