Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when I plug out the ethernet cable from SQL1 the whitness is detecting the failure an is switching over to SQL2 and so on. The running application is connecting to SQL2 after the failure. In case of a restart of the application the client tries to connect to SQL1, which is not reachable. As I'm expecting there should be a connect to SQL2 after the sqlclient can't reach SQL1 but with netstat -na I only see connection attempts to SQL1. From a former posting I got the Information about some existing connections to SQL1, but I made a restart of the client so be sure that there is no existing connection to SQL1. When both SQL-Servers are reachable and I do a manual failover everything is working fine, so I think database mirroring only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/
Database mirroring works either way depending on which mode you're in. It's
the automatic failover in high safety mode that may or may not meet your
needs. Specifically, not the failover part but rather the new client
connections part post failover (as per your scenario).
Automatic failover only works if your client's data access layer (SNAC or
ADO/ODBC.NET) has cached both server names. This happens in the first
connection attempt on the server. This is usually the primary server unless
you have specific code in your app (just a few simple lines) to test for
both. Many DB Mirroring implementations are "as-is" using exising apps
without code tweaks so they connect to their "original" primary as per
normal ops but benefit from automatic reconnect in a failover.
However, if the primary server was down before the client connects for the
first time, it would not know there is a mirror server to connect to which
is what you're seeing. Whether it's a new client connection or a re-started
app/web server, it's the same effect; you don't have the mirror server name
cached so the app/web server or client doesn't know it exists.
joe.
"Christian Grbner [MVP]" <newsgroups@.groeby.net> wrote in message
news:uunWT7wuHHA.2360@.TK2MSFTNGP06.phx.gbl...
Hello,
I'm just configuring a Database Mirror with SQL 2005 SP2. I have two
SQL-Servers SQL1 and SQL2 and one Whitness. Everything is working fine, when
I plug out the ethernet cable from SQL1 the whitness is detecting the
failure an is switching over to SQL2 and so on. The running application is
connecting to SQL2 after the failure. In case of a restart of the
application the client tries to connect to SQL1, which is not reachable. As
I'm expecting there should be a connect to SQL2 after the sqlclient can't
reach SQL1 but with netstat -na I only see connection attempts to SQL1. From
a former posting I got the Information about some existing connections to
SQL1, but I made a restart of the client so be sure that there is no
existing connection to SQL1. When both SQL-Servers are reachable and I do a
manual failover everything is working fine, so I think database mirroring
only works, when both servers are online. Is this true?
My connection string looks like this:
Data Source=DBSRV1;Failover Partner=DBSRV2;Initial Catalog=TEST;User
ID=test;Password=test;Network Library=DBMSSOCN;Load Balance Timeout=5"
Thanks for your help
Greetings from Germany
Christian
Christian Grbner
MVP ISA Server
Hilfe & Infos rund um den ISA Server: http://www.msisafaq.de !!!!
NEU !!! Das Handbuch zum ISA 2006 - http://www.msisafaq.de/buch/
Showing posts with label sp2. Show all posts
Showing posts with label sp2. Show all posts
Wednesday, March 21, 2012
Thursday, March 8, 2012
database maintenance plan
i currently am using: sharepoint portal 2003 / windows sharepoint
services with sp2 and sql 2000 with sp4.
i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.
i tried adding something like this:
use master
go
alter database db1_prod set single_user with rollback immediate
go
...and it didn't work. anyone have a better idea?
thanks!
Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."
You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.
-Sue
On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>i tried adding something like this:
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>...and it didn't work. anyone have a better idea?
>thanks!
|||i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?
Tibor Karaszi wrote:[vbcol=seagreen]
> Backup log is perfectly possible while users are connected to the database. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This is a bad option in the
> first place. I suggest you remove that option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <eric.olson@.gmail.com> wrote in message
> news:1164121998.074547.209600@.f16g2000cwb.googlegr oups.com...
services with sp2 and sql 2000 with sp4.
i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.
i tried adding something like this:
use master
go
alter database db1_prod set single_user with rollback immediate
go
...and it didn't work. anyone have a better idea?
thanks!
Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."
You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.
-Sue
On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>i tried adding something like this:
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>...and it didn't work. anyone have a better idea?
>thanks!
|||i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?
Tibor Karaszi wrote:[vbcol=seagreen]
> Backup log is perfectly possible while users are connected to the database. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This is a bad option in the
> first place. I suggest you remove that option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <eric.olson@.gmail.com> wrote in message
> news:1164121998.074547.209600@.f16g2000cwb.googlegr oups.com...
database maintenance plan
i currently am using: sharepoint portal 2003 / windows sharepoint
services with sp2 and sql 2000 with sp4.
i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.
i tried adding something like this:
use master
go
alter database db1_prod set single_user with rollback immediate
go
...and it didn't work. anyone have a better idea?
thanks!> but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
Backup log is perfectly possible while users are connected to the database. My guess is that your
integrity check has the "attempt to repair minor problems" checked. This is a bad option in the
first place. I suggest you remove that option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<eric.olson@.gmail.com> wrote in message
news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...
>i currently am using: sharepoint portal 2003 / windows sharepoint
> services with sp2 and sql 2000 with sp4.
> i'm trying to develop a database maintenance plan in sql 2000. so far,
> to get past the
> QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> in the steps, but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
> i tried adding something like this:
> use master
> go
> alter database db1_prod set single_user with rollback immediate
> go
> ...and it didn't work. anyone have a better idea?
> thanks!
>|||Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."
You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.
-Sue
On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>i tried adding something like this:
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>...and it didn't work. anyone have a better idea?
>thanks!|||where i'm at now:
Backup cannot be performed on database 'master'. The sub task is
ignored.
Backup cannot be performed on database 'msdb'. The sub task is ignored.
Sue Hoegemeier wrote:
> Regarding the statement:
> "but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs."
> You can do database and log backups while the database is in use. You
> don't need to drop any connections to perform the backup.
> -Sue
> On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
> >i currently am using: sharepoint portal 2003 / windows sharepoint
> >services with sp2 and sql 2000 with sp4.
> >
> >i'm trying to develop a database maintenance plan in sql 2000. so far,
> >to get past the
> >QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> >in the steps, but since sharepoint actively keeps a connection to the
> >database, i'm unable to backup the transaction logs.
> >
> >i tried adding something like this:
> >
> >use master
> >go
> >alter database db1_prod set single_user with rollback immediate
> >go
> >
> >...and it didn't work. anyone have a better idea?
> >
> >thanks!|||eric.olson@.gmail.com wrote:
> where i'm at now:
> Backup cannot be performed on database 'master'. The sub task is
> ignored.
> Backup cannot be performed on database 'msdb'. The sub task is ignored.
That means you're attempting to do a transaction log backup on those
databases, and they're in Simple recovery mode. You can't backup the
t-log on a Simple mode database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?
Tibor Karaszi wrote:
> > but since sharepoint actively keeps a connection to the
> > database, i'm unable to backup the transaction logs.
> Backup log is perfectly possible while users are connected to the database. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This is a bad option in the
> first place. I suggest you remove that option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <eric.olson@.gmail.com> wrote in message
> news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...
> >i currently am using: sharepoint portal 2003 / windows sharepoint
> > services with sp2 and sql 2000 with sp4.
> >
> > i'm trying to develop a database maintenance plan in sql 2000. so far,
> > to get past the
> > QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> > in the steps, but since sharepoint actively keeps a connection to the
> > database, i'm unable to backup the transaction logs.
> >
> > i tried adding something like this:
> >
> > use master
> > go
> > alter database db1_prod set single_user with rollback immediate
> > go
> >
> > ...and it didn't work. anyone have a better idea?
> >
> > thanks!
> >|||right... but a microsoft article also said not to put master or msdb
in full mode.
should i put it in full recovery mode?
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> > where i'm at now:
> >
> > Backup cannot be performed on database 'master'. The sub task is
> > ignored.
> > Backup cannot be performed on database 'msdb'. The sub task is ignored.
> That means you're attempting to do a transaction log backup on those
> databases, and they're in Simple recovery mode. You can't backup the
> t-log on a Simple mode database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||eric.olson@.gmail.com wrote:
> right... but a microsoft article also said not to put master or msdb
> in full mode.
> should i put it in full recovery mode?
>
No, just don't include those databases in the transaction log backup
job. Create two plans - one for system DB's, one for user DB's.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||that's exactly what i ended up doing :).
- plan1 - backup user databases w/transaction logs, database
optimization, and error checking
- plan2 - backup system databases and check for errors
thanks.
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> > right... but a microsoft article also said not to put master or msdb
> > in full mode.
> >
> > should i put it in full recovery mode?
> >
> No, just don't include those databases in the transaction log backup
> job. Create two plans - one for system DB's, one for user DB's.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
services with sp2 and sql 2000 with sp4.
i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.
i tried adding something like this:
use master
go
alter database db1_prod set single_user with rollback immediate
go
...and it didn't work. anyone have a better idea?
thanks!> but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
Backup log is perfectly possible while users are connected to the database. My guess is that your
integrity check has the "attempt to repair minor problems" checked. This is a bad option in the
first place. I suggest you remove that option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<eric.olson@.gmail.com> wrote in message
news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...
>i currently am using: sharepoint portal 2003 / windows sharepoint
> services with sp2 and sql 2000 with sp4.
> i'm trying to develop a database maintenance plan in sql 2000. so far,
> to get past the
> QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> in the steps, but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
> i tried adding something like this:
> use master
> go
> alter database db1_prod set single_user with rollback immediate
> go
> ...and it didn't work. anyone have a better idea?
> thanks!
>|||Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."
You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.
-Sue
On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>i tried adding something like this:
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>...and it didn't work. anyone have a better idea?
>thanks!|||where i'm at now:
Backup cannot be performed on database 'master'. The sub task is
ignored.
Backup cannot be performed on database 'msdb'. The sub task is ignored.
Sue Hoegemeier wrote:
> Regarding the statement:
> "but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs."
> You can do database and log backups while the database is in use. You
> don't need to drop any connections to perform the backup.
> -Sue
> On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
> >i currently am using: sharepoint portal 2003 / windows sharepoint
> >services with sp2 and sql 2000 with sp4.
> >
> >i'm trying to develop a database maintenance plan in sql 2000. so far,
> >to get past the
> >QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> >in the steps, but since sharepoint actively keeps a connection to the
> >database, i'm unable to backup the transaction logs.
> >
> >i tried adding something like this:
> >
> >use master
> >go
> >alter database db1_prod set single_user with rollback immediate
> >go
> >
> >...and it didn't work. anyone have a better idea?
> >
> >thanks!|||eric.olson@.gmail.com wrote:
> where i'm at now:
> Backup cannot be performed on database 'master'. The sub task is
> ignored.
> Backup cannot be performed on database 'msdb'. The sub task is ignored.
That means you're attempting to do a transaction log backup on those
databases, and they're in Simple recovery mode. You can't backup the
t-log on a Simple mode database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?
Tibor Karaszi wrote:
> > but since sharepoint actively keeps a connection to the
> > database, i'm unable to backup the transaction logs.
> Backup log is perfectly possible while users are connected to the database. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This is a bad option in the
> first place. I suggest you remove that option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <eric.olson@.gmail.com> wrote in message
> news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...
> >i currently am using: sharepoint portal 2003 / windows sharepoint
> > services with sp2 and sql 2000 with sp4.
> >
> > i'm trying to develop a database maintenance plan in sql 2000. so far,
> > to get past the
> > QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> > in the steps, but since sharepoint actively keeps a connection to the
> > database, i'm unable to backup the transaction logs.
> >
> > i tried adding something like this:
> >
> > use master
> > go
> > alter database db1_prod set single_user with rollback immediate
> > go
> >
> > ...and it didn't work. anyone have a better idea?
> >
> > thanks!
> >|||right... but a microsoft article also said not to put master or msdb
in full mode.
should i put it in full recovery mode?
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> > where i'm at now:
> >
> > Backup cannot be performed on database 'master'. The sub task is
> > ignored.
> > Backup cannot be performed on database 'msdb'. The sub task is ignored.
> That means you're attempting to do a transaction log backup on those
> databases, and they're in Simple recovery mode. You can't backup the
> t-log on a Simple mode database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||eric.olson@.gmail.com wrote:
> right... but a microsoft article also said not to put master or msdb
> in full mode.
> should i put it in full recovery mode?
>
No, just don't include those databases in the transaction log backup
job. Create two plans - one for system DB's, one for user DB's.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||that's exactly what i ended up doing :).
- plan1 - backup user databases w/transaction logs, database
optimization, and error checking
- plan2 - backup system databases and check for errors
thanks.
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> > right... but a microsoft article also said not to put master or msdb
> > in full mode.
> >
> > should i put it in full recovery mode?
> >
> No, just don't include those databases in the transaction log backup
> job. Create two plans - one for system DB's, one for user DB's.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Wednesday, March 7, 2012
database maintenance plan
i currently am using: sharepoint portal 2003 / windows sharepoint
services with sp2 and sql 2000 with sp4.
i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.
i tried adding something like this:
use master
go
alter database db1_prod set single_user with rollback immediate
go
...and it didn't work. anyone have a better idea?
thanks!> but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
Backup log is perfectly possible while users are connected to the database.
My guess is that your
integrity check has the "attempt to repair minor problems" checked. This is
a bad option in the
first place. I suggest you remove that option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<eric.olson@.gmail.com> wrote in message
news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...
>i currently am using: sharepoint portal 2003 / windows sharepoint
> services with sp2 and sql 2000 with sp4.
> i'm trying to develop a database maintenance plan in sql 2000. so far,
> to get past the
> QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> in the steps, but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
> i tried adding something like this:
> use master
> go
> alter database db1_prod set single_user with rollback immediate
> go
> ...and it didn't work. anyone have a better idea?
> thanks!
>|||Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."
You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.
-Sue
On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>i tried adding something like this:
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>...and it didn't work. anyone have a better idea?
>thanks!|||where i'm at now:
Backup cannot be performed on database 'master'. The sub task is
ignored.
Backup cannot be performed on database 'msdb'. The sub task is ignored.
Sue Hoegemeier wrote:[vbcol=seagreen]
> Regarding the statement:
> "but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs."
> You can do database and log backups while the database is in use. You
> don't need to drop any connections to perform the backup.
> -Sue
> On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>|||eric.olson@.gmail.com wrote:
> where i'm at now:
> Backup cannot be performed on database 'master'. The sub task is
> ignored.
> Backup cannot be performed on database 'msdb'. The sub task is ignored.
That means you're attempting to do a transaction log backup on those
databases, and they're in Simple recovery mode. You can't backup the
t-log on a Simple mode database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?
Tibor Karaszi wrote:[vbcol=seagreen]
> Backup log is perfectly possible while users are connected to the database
. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This i
s a bad option in the
> first place. I suggest you remove that option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <eric.olson@.gmail.com> wrote in message
> news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...|||right... but a microsoft article also said not to put master or msdb
in full mode.
should i put it in full recovery mode?
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> That means you're attempting to do a transaction log backup on those
> databases, and they're in Simple recovery mode. You can't backup the
> t-log on a Simple mode database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||eric.olson@.gmail.com wrote:
> right... but a microsoft article also said not to put master or msdb
> in full mode.
> should i put it in full recovery mode?
>
No, just don't include those databases in the transaction log backup
job. Create two plans - one for system DB's, one for user DB's.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||that's exactly what i ended up doing
.
- plan1 - backup user databases w/transaction logs, database
optimization, and error checking
- plan2 - backup system databases and check for errors
thanks.
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> No, just don't include those databases in the transaction log backup
> job. Create two plans - one for system DB's, one for user DB's.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
services with sp2 and sql 2000 with sp4.
i'm trying to develop a database maintenance plan in sql 2000. so far,
to get past the
QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
in the steps, but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs.
i tried adding something like this:
use master
go
alter database db1_prod set single_user with rollback immediate
go
...and it didn't work. anyone have a better idea?
thanks!> but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
Backup log is perfectly possible while users are connected to the database.
My guess is that your
integrity check has the "attempt to repair minor problems" checked. This is
a bad option in the
first place. I suggest you remove that option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<eric.olson@.gmail.com> wrote in message
news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...
>i currently am using: sharepoint portal 2003 / windows sharepoint
> services with sp2 and sql 2000 with sp4.
> i'm trying to develop a database maintenance plan in sql 2000. so far,
> to get past the
> QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
> in the steps, but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs.
> i tried adding something like this:
> use master
> go
> alter database db1_prod set single_user with rollback immediate
> go
> ...and it didn't work. anyone have a better idea?
> thanks!
>|||Regarding the statement:
"but since sharepoint actively keeps a connection to the
database, i'm unable to backup the transaction logs."
You can do database and log backups while the database is in use. You
don't need to drop any connections to perform the backup.
-Sue
On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>i currently am using: sharepoint portal 2003 / windows sharepoint
>services with sp2 and sql 2000 with sp4.
>i'm trying to develop a database maintenance plan in sql 2000. so far,
>to get past the
>QUOTED_IDENTIFIER, i've added -SupportComputedColumn to the T-SQL code
>in the steps, but since sharepoint actively keeps a connection to the
>database, i'm unable to backup the transaction logs.
>i tried adding something like this:
>use master
>go
>alter database db1_prod set single_user with rollback immediate
>go
>...and it didn't work. anyone have a better idea?
>thanks!|||where i'm at now:
Backup cannot be performed on database 'master'. The sub task is
ignored.
Backup cannot be performed on database 'msdb'. The sub task is ignored.
Sue Hoegemeier wrote:[vbcol=seagreen]
> Regarding the statement:
> "but since sharepoint actively keeps a connection to the
> database, i'm unable to backup the transaction logs."
> You can do database and log backups while the database is in use. You
> don't need to drop any connections to perform the backup.
> -Sue
> On 21 Nov 2006 07:13:18 -0800, eric.olson@.gmail.com wrote:
>|||eric.olson@.gmail.com wrote:
> where i'm at now:
> Backup cannot be performed on database 'master'. The sub task is
> ignored.
> Backup cannot be performed on database 'msdb'. The sub task is ignored.
That means you're attempting to do a transaction log backup on those
databases, and they're in Simple recovery mode. You can't backup the
t-log on a Simple mode database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||i took off the 'attempt to repair minor problems' but still get errors
with the master and msdb databases backup. perhaps i need to make a
separate database maintenance plan for those two, and only have them
backup and not touch the transaction logs?
Tibor Karaszi wrote:[vbcol=seagreen]
> Backup log is perfectly possible while users are connected to the database
. My guess is that your
> integrity check has the "attempt to repair minor problems" checked. This i
s a bad option in the
> first place. I suggest you remove that option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <eric.olson@.gmail.com> wrote in message
> news:1164121998.074547.209600@.f16g2000cwb.googlegroups.com...|||right... but a microsoft article also said not to put master or msdb
in full mode.
should i put it in full recovery mode?
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> That means you're attempting to do a transaction log backup on those
> databases, and they're in Simple recovery mode. You can't backup the
> t-log on a Simple mode database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||eric.olson@.gmail.com wrote:
> right... but a microsoft article also said not to put master or msdb
> in full mode.
> should i put it in full recovery mode?
>
No, just don't include those databases in the transaction log backup
job. Create two plans - one for system DB's, one for user DB's.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||that's exactly what i ended up doing
.- plan1 - backup user databases w/transaction logs, database
optimization, and error checking
- plan2 - backup system databases and check for errors
thanks.
Tracy McKibben wrote:
> eric.olson@.gmail.com wrote:
> No, just don't include those databases in the transaction log backup
> job. Create two plans - one for system DB's, one for user DB's.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Friday, February 24, 2012
Database Mail fails to email Operator
Sqlserver 2005 SP2
Hi All,
Upon completion of a job, an email should be sent to an operator but instead
it return the following error:
Message
The job succeeded. The Job was invoked by User. The last step to run was
step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
I am able to send the test email without any issues so I am not sure why the
email notification does not go through.
Regards
JNTry to verify the privileges of the SQL Server Agent Service Account;
More important: do you configured Database Mail and created a profile for it
(and added members to DatabaseMailUserRole of msdb)?
Look at Books on line the topics related to Database Mail
Gilberto Zampatti
"JN" wrote:
> Sqlserver 2005 SP2
> Hi All,
> Upon completion of a job, an email should be sent to an operator but instead
> it return the following error:
> Message
> The job succeeded. The Job was invoked by User. The last step to run was
> step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
> I am able to send the test email without any issues so I am not sure why the
> email notification does not go through.
> Regards
> JN
>
>
Hi All,
Upon completion of a job, an email should be sent to an operator but instead
it return the following error:
Message
The job succeeded. The Job was invoked by User. The last step to run was
step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
I am able to send the test email without any issues so I am not sure why the
email notification does not go through.
Regards
JNTry to verify the privileges of the SQL Server Agent Service Account;
More important: do you configured Database Mail and created a profile for it
(and added members to DatabaseMailUserRole of msdb)?
Look at Books on line the topics related to Database Mail
Gilberto Zampatti
"JN" wrote:
> Sqlserver 2005 SP2
> Hi All,
> Upon completion of a job, an email should be sent to an operator but instead
> it return the following error:
> Message
> The job succeeded. The Job was invoked by User. The last step to run was
> step 1 (Daily Backup). NOTE: Failed to notify 'John' via email.
> I am able to send the test email without any issues so I am not sure why the
> email notification does not go through.
> Regards
> JN
>
>
Database Mail Error "Invalid Parameter"
Hello,
We currently have the 64-bit SQL 2005 SP2 running on our machine. Prior to installation of SP2 we were able to send mail through Database Mail without any problem. Now the mail is successfully cued but not sent. Here is the message in the log:
Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Does anyone have any thoughts?
Thanks in advance.
Simone
Have you checked the log file after the SP2 application?|||Thank you for the response. I did check the logs and saw nothing out of the ordinary.|||Have you physically restarted the server?
We find we Database Mail stops working on our 64 bit versions of 2005 with any config change or SP install until after we reboot - then all works okay again.
Regards,
Gary.
|||Interesting. We will try the reboot this weekend. I'll post the result on Monday. Thanks for the response.|||Try restarting SQLAgent alone without the whole SQL services, as this sometimes clears the log behind the process.|||We restarted the server over the weekend but this did not help. As advised by Satya, I restarted only SQLAgent. These are the only two messages in the log:
configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Mail is still failing to be sent. I am lost.
|||How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editionshttp://support.microsoft.com/kb/908360
SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions. To send any notifications, such as job status notifications and alert notifications, follow the steps in this article to manually configure MAPI for use by SQL Server Agent Mail.
Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems.
|||I'm not actually using Sql Agent Mail, we are using Database Mail. It did work on this machine a few weeks ago, it just has since stopped working.|||I believe I found our issue. We didn't receive any error messages stating that SP2 failed, but something did. Procedures in the msdb database were updated to allow for customized parameters (below taken from "What's New in SQL Server 2005 SP2 - http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm):
Database Mail
You can now customize parameters that are passed to DatabaseMail90.exe
Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.|||
Although we received no errors when installing SP2, something did fail. The stored procedures did not all copy over. After reinstallation, database mail works as expected. Thank you to all.
Simone
|||Thanks, we were experiencing the same problem with SP2 intallation failure and Database Mail invalid parameter error message.
Database Mail Error "Invalid Parameter"
Hello,
We currently have the 64-bit SQL 2005 SP2 running on our machine. Prior to installation of SP2 we were able to send mail through Database Mail without any problem. Now the mail is successfully cued but not sent. Here is the message in the log:
Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Does anyone have any thoughts?
Thanks in advance.
Simone
Have you checked the log file after the SP2 application?|||Thank you for the response. I did check the logs and saw nothing out of the ordinary.|||Have you physically restarted the server?
We find we Database Mail stops working on our 64 bit versions of 2005 with any config change or SP install until after we reboot - then all works okay again.
Regards,
Gary.
|||Interesting. We will try the reboot this weekend. I'll post the result on Monday. Thanks for the response.|||Try restarting SQLAgent alone without the whole SQL services, as this sometimes clears the log behind the process.|||We restarted the server over the weekend but this did not help. As advised by Satya, I restarted only SQLAgent. These are the only two messages in the log:
configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Mail is still failing to be sent. I am lost.
|||How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editionshttp://support.microsoft.com/kb/908360
SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions. To send any notifications, such as job status notifications and alert notifications, follow the steps in this article to manually configure MAPI for use by SQL Server Agent Mail.
Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems.
|||I'm not actually using Sql Agent Mail, we are using Database Mail. It did work on this machine a few weeks ago, it just has since stopped working.|||I believe I found our issue. We didn't receive any error messages stating that SP2 failed, but something did. Procedures in the msdb database were updated to allow for customized parameters (below taken from "What's New in SQL Server 2005 SP2 - http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm):
Database Mail
You can now customize parameters that are passed to DatabaseMail90.exe
Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.|||
Although we received no errors when installing SP2, something did fail. The stored procedures did not all copy over. After reinstallation, database mail works as expected. Thank you to all.
Simone
|||Thanks, we were experiencing the same problem with SP2 intallation failure and Database Mail invalid parameter error message.
Database Mail Error "Invalid Parameter"
Hello,
We currently have the 64-bit SQL 2005 SP2 running on our machine. Prior to installation of SP2 we were able to send mail through Database Mail without any problem. Now the mail is successfully cued but not sent. Here is the message in the log:
Description:
The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'Error executing extended stored procedure: Invalid Parameter'
Does anyone have any thoughts?
Thanks in advance.
Simone
Have you checked the log file after the SP2 application?|||Thank you for the response. I did check the logs and saw nothing out of the ordinary.|||Have you physically restarted the server?
We find we Database Mail stops working on our 64 bit versions of 2005 with any config change or SP install until after we reboot - then all works okay again.
Regards,
Gary.
|||Interesting. We will try the reboot this weekend. I'll post the result on Monday. Thanks for the response.|||Try restarting SQLAgent alone without the whole SQL services, as this sometimes clears the log behind the process.|||We restarted the server over the weekend but this did not help. As advised by Satya, I restarted only SQLAgent. These are the only two messages in the log:
configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Mail is still failing to be sent. I am lost.
|||How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editionshttp://support.microsoft.com/kb/908360
SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions. To send any notifications, such as job status notifications and alert notifications, follow the steps in this article to manually configure MAPI for use by SQL Server Agent Mail.
Note In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems.
|||I'm not actually using Sql Agent Mail, we are using Database Mail. It did work on this machine a few weeks ago, it just has since stopped working.|||I believe I found our issue. We didn't receive any error messages stating that SP2 failed, but something did. Procedures in the msdb database were updated to allow for customized parameters (below taken from "What's New in SQL Server 2005 SP2 - http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm):
Database Mail
You can now customize parameters that are passed to DatabaseMail90.exe
Customers and SQL Server Customer Support had difficulty in quickly identifying Database mail misconfigurations. Error reporting has been enhanced. The user will be given much more context to the underlying problems.|||
Although we received no errors when installing SP2, something did fail. The stored procedures did not all copy over. After reinstallation, database mail works as expected. Thank you to all.
Simone
|||Thanks, we were experiencing the same problem with SP2 intallation failure and Database Mail invalid parameter error message.
Sunday, February 19, 2012
Database Mail
Hi All,
sql server 2005 sp2
I am able to receive email notifications when a maintenance plan is executed
but not when the sql server agent job is executed.
According to the error log, "an attempt was made to send an email when no
email session has been established"
Any reason why it works for one but not the other?
Thanks.
JNDid you enable mail for SQL Agent? You need to configure
mail for Agent - If you view the properties for agent, go to
the Alert System window and set mail up on the screen.
-Sue
On Mon, 4 Jun 2007 11:05:22 -0400, "JN"
<jnsimex@.hotmail.com> wrote:
>Hi All,
>sql server 2005 sp2
>I am able to receive email notifications when a maintenance plan is executed
>but not when the sql server agent job is executed.
>According to the error log, "an attempt was made to send an email when no
>email session has been established"
>Any reason why it works for one but not the other?
>Thanks.
>JN
>|||Yes, I have a check mark besides "Enable mail profile" and configured the
sql server agent mail properties under the Alert System to use the Database
Mail with the Mail profile that I want to use.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ebm9635bakqghvfrelio40l4je9gmghed9@.4ax.com...
> Did you enable mail for SQL Agent? You need to configure
> mail for Agent - If you view the properties for agent, go to
> the Alert System window and set mail up on the screen.
> -Sue
> On Mon, 4 Jun 2007 11:05:22 -0400, "JN"
> <jnsimex@.hotmail.com> wrote:
>>Hi All,
>>sql server 2005 sp2
>>I am able to receive email notifications when a maintenance plan is
>>executed
>>but not when the sql server agent job is executed.
>>According to the error log, "an attempt was made to send an email when no
>>email session has been established"
>>Any reason why it works for one but not the other?
>>Thanks.
>>JN
>
sql server 2005 sp2
I am able to receive email notifications when a maintenance plan is executed
but not when the sql server agent job is executed.
According to the error log, "an attempt was made to send an email when no
email session has been established"
Any reason why it works for one but not the other?
Thanks.
JNDid you enable mail for SQL Agent? You need to configure
mail for Agent - If you view the properties for agent, go to
the Alert System window and set mail up on the screen.
-Sue
On Mon, 4 Jun 2007 11:05:22 -0400, "JN"
<jnsimex@.hotmail.com> wrote:
>Hi All,
>sql server 2005 sp2
>I am able to receive email notifications when a maintenance plan is executed
>but not when the sql server agent job is executed.
>According to the error log, "an attempt was made to send an email when no
>email session has been established"
>Any reason why it works for one but not the other?
>Thanks.
>JN
>|||Yes, I have a check mark besides "Enable mail profile" and configured the
sql server agent mail properties under the Alert System to use the Database
Mail with the Mail profile that I want to use.
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:ebm9635bakqghvfrelio40l4je9gmghed9@.4ax.com...
> Did you enable mail for SQL Agent? You need to configure
> mail for Agent - If you view the properties for agent, go to
> the Alert System window and set mail up on the screen.
> -Sue
> On Mon, 4 Jun 2007 11:05:22 -0400, "JN"
> <jnsimex@.hotmail.com> wrote:
>>Hi All,
>>sql server 2005 sp2
>>I am able to receive email notifications when a maintenance plan is
>>executed
>>but not when the sql server agent job is executed.
>>According to the error log, "an attempt was made to send an email when no
>>email session has been established"
>>Any reason why it works for one but not the other?
>>Thanks.
>>JN
>
Database losing changes
I have a SQL Server 2000 SP2 database. I can work on it all day, and everything is fine. Backups run at 7:00pm successfully. At some point after that, usually about midnight, all changes are rolled back. I can restore from the 7:00pm backup, but overnight every change (including those restored) will again disappear.
I have tinkered for two weeks, checking every setting and running every automated job (there are three) interactively. Nothing seems to cause or cure this problem. But every morning when I come in, my database is back to where it was on or about March 15.
One possible clue: there is a SQL log entry stating "Transaction Log Truncated" that appears to happen about the same time - midnight. I do not know why this is happening or whether it is significatnt; I truncated the transaction log interactively, with no negative effect. Also, the problems began after I installed SP2.
Any ideas?Probably best to leave the profiler running - logging t a text file to find out whats happenning.
It sounds like something is restoring a backup.
If your backup restore does hold the data then it gets lost again at midnight then there must be a task that is deleting everything that has been added after march 15 or a backup is being restored from that date.
Try creating a new table - if that disappears then you will now that it isn't just a task that deleted data.
Check with the network people that they aren't trying to backup the disk. This will probably lock the database files and crash the server. If you are holding open transactions then all changes in those transactions would be lost.
I have tinkered for two weeks, checking every setting and running every automated job (there are three) interactively. Nothing seems to cause or cure this problem. But every morning when I come in, my database is back to where it was on or about March 15.
One possible clue: there is a SQL log entry stating "Transaction Log Truncated" that appears to happen about the same time - midnight. I do not know why this is happening or whether it is significatnt; I truncated the transaction log interactively, with no negative effect. Also, the problems began after I installed SP2.
Any ideas?Probably best to leave the profiler running - logging t a text file to find out whats happenning.
It sounds like something is restoring a backup.
If your backup restore does hold the data then it gets lost again at midnight then there must be a task that is deleting everything that has been added after march 15 or a backup is being restored from that date.
Try creating a new table - if that disappears then you will now that it isn't just a task that deleted data.
Check with the network people that they aren't trying to backup the disk. This will probably lock the database files and crash the server. If you are holding open transactions then all changes in those transactions would be lost.
Friday, February 17, 2012
Database Log file doesn't shrink ?
Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon
i'm trying to shrink some of the log files for my databases
One has free space of about 200mb for the log file
I've tried shrinking it in enterprise manager but this doesn't help
It still has 200mb free space
What do i need to do
I was hoping to shrink them all b4 going to SP3
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
> >Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
> >i'm trying to shrink some of the log files for my databases.
> >One has free space of about 200mb for the log file.
> >I've tried shrinking it in enterprise manager but this doesn't help.
> >It still has 200mb free space.
> >
> >What do i need to do ?
> >I was hoping to shrink them all b4 going to SP3.
> >
> >thanks
>
i'm trying to shrink some of the log files for my databases
One has free space of about 200mb for the log file
I've tried shrinking it in enterprise manager but this doesn't help
It still has 200mb free space
What do i need to do
I was hoping to shrink them all b4 going to SP3
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
> >Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
> >i'm trying to shrink some of the log files for my databases.
> >One has free space of about 200mb for the log file.
> >I've tried shrinking it in enterprise manager but this doesn't help.
> >It still has 200mb free space.
> >
> >What do i need to do ?
> >I was hoping to shrink them all b4 going to SP3.
> >
> >thanks
>
Database Log file doesn't shrink ?
Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.
4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
>
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanksRefer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.
4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
>
Database Log file doesn't shrink ?
Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanks
Refer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
i'm trying to shrink some of the log files for my databases.
One has free space of about 200mb for the log file.
I've tried shrinking it in enterprise manager but this doesn't help.
It still has 200mb free space.
What do i need to do ?
I was hoping to shrink them all b4 going to SP3.
thanks
Refer to the following article:
INF: Shrinking the Transaction Log in SQL Server 2000 with
DBCC SHRINKFILE
http://support.microsoft.com/?id=272318
-Sue
On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
<jpond@.railcu.org.au> wrote:
>Hi, i'm trying to clean up my SQL Server 2000 SP2 (moving to SP3 soon)
>i'm trying to shrink some of the log files for my databases.
>One has free space of about 200mb for the log file.
>I've tried shrinking it in enterprise manager but this doesn't help.
>It still has 200mb free space.
>What do i need to do ?
>I was hoping to shrink them all b4 going to SP3.
>thanks
|||Hi,
How to reduce the Transaction log size
1. Backup the transaction log (Use command:- BACKUP Log dbname to
disk='c:\backup\dbname.tr1' (or use enterprise manager)
or (if you do need the trasaction log backup execute below command)
backup log <dbname> with truncate_only
2. Indetify the log file for shrinking:-
use <dbname>
go
sp_helpfile
Based on the name column for the transaction log file execute the dbcc
shrinkfile
3. Shrink the transaction log file.
DBCC SHRINKFILE('logical_transaction_log file name','truncateonly')
Thanks
Hari
MCDBA
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:0pfqb05p4dq7pcgd913pc40o6hc638hb3u@.4ax.com...
> Refer to the following article:
> INF: Shrinking the Transaction Log in SQL Server 2000 with
> DBCC SHRINKFILE
> http://support.microsoft.com/?id=272318
> -Sue
> On Tue, 1 Jun 2004 18:56:03 -0700, Jeremy
> <jpond@.railcu.org.au> wrote:
>
Subscribe to:
Posts (Atom)