I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts?
Robert
Hi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts?
> Robert
|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
|||Hi
Check out
http://msdn.microsoft.com/library/de...r_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Thursday, March 29, 2012
database move
I recently moved a database and its transaction log file to another drive du
e
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set o
n
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive
due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get
an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set
on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
>|||Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service accoun
t
> that starts mssqlserver didn't have specific rights to the new location.
I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit t
he
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
>sql
e
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set o
n
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive
due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get
an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
[vbcol=seagreen]
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set
on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
>|||Hi
Check out
http://msdn.microsoft.com/library/d...r />
_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
[vbcol=seagreen]
> In the move of this large db/log to another volume, the sql service accoun
t
> that starts mssqlserver didn't have specific rights to the new location.
I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit t
he
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
>sql
database move
I recently moved a database and its transaction log file to another drive due
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql
to space issues. I followed the kb article 224071 on how to move a user
database and it appears that it worked correctly.
However, now the database comes up as "read-only" and if I view the
properties of the db and try to remove the "read-only" designation, I get an
error that it can't.
Any thoughts'
RobertHi Robert
Check the SQL Server error log to see if there is any information on the
problem. You may also want to check that the readonly attribute is not set on
the mdf and ldf files.
John
"Robert Gandrud" wrote:
> I recently moved a database and its transaction log file to another drive due
> to space issues. I followed the kb article 224071 on how to move a user
> database and it appears that it worked correctly.
> However, now the database comes up as "read-only" and if I view the
> properties of the db and try to remove the "read-only" designation, I get an
> error that it can't.
> Any thoughts'
> Robert|||In the move of this large db/log to another volume, the sql service account
that starts mssqlserver didn't have specific rights to the new location. I
gave it full control to the new db location and rebooted.
The error log then stated that it performed a full recovery on that db,
which it couldn't do before. I was then able to remove the Read-only
property in the db properties in Ent. Mgr.
Thanks for your help.
Also, for some reason, I have about a 30GB log file. I was under the
understanding that if I backup the transaction log, that it would commit the
transactions and severly reduce my log file size, but that didn't seem to
work.
Any thoughts on that?
Thanks again,
Robert
"John Bell" wrote:
> Hi Robert
> Check the SQL Server error log to see if there is any information on the
> problem. You may also want to check that the readonly attribute is not set on
> the mdf and ldf files.
> John
> "Robert Gandrud" wrote:
> > I recently moved a database and its transaction log file to another drive due
> > to space issues. I followed the kb article 224071 on how to move a user
> > database and it appears that it worked correctly.
> >
> > However, now the database comes up as "read-only" and if I view the
> > properties of the db and try to remove the "read-only" designation, I get an
> > error that it can't.
> >
> > Any thoughts'
> >
> > Robert|||Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1uzr.asp
on shrinking the transaction log.
John
"Robert Gandrud" wrote:
> In the move of this large db/log to another volume, the sql service account
> that starts mssqlserver didn't have specific rights to the new location. I
> gave it full control to the new db location and rebooted.
> The error log then stated that it performed a full recovery on that db,
> which it couldn't do before. I was then able to remove the Read-only
> property in the db properties in Ent. Mgr.
> Thanks for your help.
> Also, for some reason, I have about a 30GB log file. I was under the
> understanding that if I backup the transaction log, that it would commit the
> transactions and severly reduce my log file size, but that didn't seem to
> work.
> Any thoughts on that?
> Thanks again,
> Robert
> "John Bell" wrote:
> > Hi Robert
> >
> > Check the SQL Server error log to see if there is any information on the
> > problem. You may also want to check that the readonly attribute is not set on
> > the mdf and ldf files.
> >
> > John
> >
> > "Robert Gandrud" wrote:
> >
> > > I recently moved a database and its transaction log file to another drive due
> > > to space issues. I followed the kb article 224071 on how to move a user
> > > database and it appears that it worked correctly.
> > >
> > > However, now the database comes up as "read-only" and if I view the
> > > properties of the db and try to remove the "read-only" designation, I get an
> > > error that it can't.
> > >
> > > Any thoughts'
> > >
> > > Robertsql
Tuesday, March 27, 2012
Database mirroring with truncate log on checkpoint
Hi All,
Understand that database mirroring need to use FULL recovery model, meaning
that all transaction detail will be save into log file. Hence the log file
will increase tremendously.
Can I enable truncate log on checkpoint on principle database and mirror
database? so the log file size will under control.
Thanks.
--
Regards,
VenedictHello There!
You have to set FULL recovery model for database mirroring.
To learn more about this, you can refer to the following document:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Also, when you try to dump \ truncate your log file which is used by
Database Mirroring, you'll get an error and operation will not be completed
successfully.
To keep log file's size under control, you may try to setup a job to take
it's backup regularly for example every 10-20 mins and disable it's
autogrowth and set some size manually. However, be wise while configuring
it.
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Venedict" <Venedict@.discussions.microsoft.com> wrote in message
news:36A23B54-820D-4B0E-9DA9-8A37A850D7F8@.microsoft.com...
> Hi All,
> Understand that database mirroring need to use FULL recovery model,
> meaning
> that all transaction detail will be save into log file. Hence the log file
> will increase tremendously.
> Can I enable truncate log on checkpoint on principle database and mirror
> database? so the log file size will under control.
> Thanks.
> --
> Regards,
> Venedict
Understand that database mirroring need to use FULL recovery model, meaning
that all transaction detail will be save into log file. Hence the log file
will increase tremendously.
Can I enable truncate log on checkpoint on principle database and mirror
database? so the log file size will under control.
Thanks.
--
Regards,
VenedictHello There!
You have to set FULL recovery model for database mirroring.
To learn more about this, you can refer to the following document:
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Also, when you try to dump \ truncate your log file which is used by
Database Mirroring, you'll get an error and operation will not be completed
successfully.
To keep log file's size under control, you may try to setup a job to take
it's backup regularly for example every 10-20 mins and disable it's
autogrowth and set some size manually. However, be wise while configuring
it.
Ekrem Ã?nsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCTS: SQL Server 2005, MCITP:DBA, MCSD.Net, MCSE, MCT
"Venedict" <Venedict@.discussions.microsoft.com> wrote in message
news:36A23B54-820D-4B0E-9DA9-8A37A850D7F8@.microsoft.com...
> Hi All,
> Understand that database mirroring need to use FULL recovery model,
> meaning
> that all transaction detail will be save into log file. Hence the log file
> will increase tremendously.
> Can I enable truncate log on checkpoint on principle database and mirror
> database? so the log file size will under control.
> Thanks.
> --
> Regards,
> Venedict
Thursday, March 22, 2012
Database mirroring and log file growth
Is there a way to shrink the log file while using database mirroring?
We have a 20 GB database with a log file about 80 GB.
Transaction log backup is done every 30 minutes, full backup each night, but the log file is not truncated.
We have a 20 GB database with a log file about 80 GB.
Transaction log backup is done every 30 minutes, full backup each night, but the log file is not truncated.
Since it seems that the log file grows about 1 GB each day (we have migrated from sql2000 about 50 days ago) we are going to run out of disk space in a few weeks!
Ivan
refer this link,
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=146&messageid=377087
they have suggested to ,issue checkpoint and then truncate log......
USE XXX
GO
CHECKPOINT
GO
BACKUP LOG [XXX] TO [XXX_TLOG] WITH NOFORMAT, INIT, NAME = N'XXX-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
DBCC SHRINKFILE(XXX_Log, 1)
GO
Is the Database also part of replication ?
Is the Database Mirroring Active , STATE = SYNCRONIZED ?
|||just try the above method and get back if its not possible you need to remove mirroringrefer, http://technet.microsoft.com/en-us/library/ms189112.aspx then try to truncate the tran log and try to shrink using dbcc shrinkfile........i am not sure which of the above 2 will work out.........|||
Removing and Reestablishing mirroring for a large database might be some work , again it depends how quickly you want to fix it , however what i will suggest ; check if the scheduled TLOG backups are successfull ? if the database is acting as a publisher in replication topology ; also if you could post the mode of mirroring Sync or Async ?
Monday, March 19, 2012
Database marked suspect
My primary database is currently marked suspect. I
believe it could be because the transaction log was
deleted. How can I restore the database to be
operational?
Any assistance is greatly appreciated!use this one sp_resetstatus [ @.DBName =3D ] 'database'
See BOL for more information om the same ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Michelle" <msarna@.starpower.net> wrote in message =news:042c01c3588e$c42cda60$a401280a@.phx.gbl...
> My primary database is currently marked suspect. I > believe it could be because the transaction log was > deleted. How can I restore the database to be > operational?
> > Any assistance is greatly appreciated!
believe it could be because the transaction log was
deleted. How can I restore the database to be
operational?
Any assistance is greatly appreciated!use this one sp_resetstatus [ @.DBName =3D ] 'database'
See BOL for more information om the same ...
-- HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com/
"Michelle" <msarna@.starpower.net> wrote in message =news:042c01c3588e$c42cda60$a401280a@.phx.gbl...
> My primary database is currently marked suspect. I > believe it could be because the transaction log was > deleted. How can I restore the database to be > operational?
> > Any assistance is greatly appreciated!
Sunday, March 11, 2012
database maintenance plan...
I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.
Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.
Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>
database maintenance plan...
I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>
database maintenance plan...
I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>
automatically remove old transaction log backups. A couple of months ago,
the transaction log backups stop getting tossed. I don't think it has
anything to do with whether or not the db is set to do a simple backup.
I've split the dbs between different maintenance plans and have had no luck.
I'm thinking it has something to do with a hotfix because both SQL servers
had issues about the same time.
I didn't make any changes to any of the Dbs or the SQL server that I can
remember.
Do I need to register the SQL server with Active Directory? I upgraded the
network from NT4 to W2003 and AD.Registering with AD should have no effect... Does the DBMaint Plan log show
any information?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Chris Geiser" <glg.dell@.worldnet.att.net> wrote in message
news:%23CAcS9JaEHA.2792@.TK2MSFTNGP09.phx.gbl...
> I'm running SQL 2000 on W2K on two servers. The DB maintenance is set to
> automatically remove old transaction log backups. A couple of months ago,
> the transaction log backups stop getting tossed. I don't think it has
> anything to do with whether or not the db is set to do a simple backup.
> I've split the dbs between different maintenance plans and have had no
luck.
> I'm thinking it has something to do with a hotfix because both SQL servers
> had issues about the same time.
> I didn't make any changes to any of the Dbs or the SQL server that I can
> remember.
> Do I need to register the SQL server with Active Directory? I upgraded
the
> network from NT4 to W2003 and AD.
>
Thursday, March 8, 2012
Database Maintenance Plan no longer deleting old files
My maintenance plan is set up to delete the old database dump files and transaction logs. After months of this working, the dump files are no longer being deleted. This has happened before. I recreated the plan and it worked for a while, then stopped again. The old transaction logs are being deleted as scheduled. Please help.Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> My maintenance plan is set up to delete the old database dump files and transaction logs. After
months of this working, the dump files are no longer being deleted. This has happened before. I
recreated the plan and it worked for a while, then stopped again. The old transaction logs are
being deleted as scheduled. Please help.|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<O7uyC2XxDHA.3224@.tk2msftngp13.phx.gbl>...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Smith" <anonymous@.discussions.microsoft.com> wrote in message
> news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> > My maintenance plan is set up to delete the old database dump files and transaction logs. After
> months of this working, the dump files are no longer being deleted. This has happened before. I
> recreated the plan and it worked for a while, then stopped again. The old transaction logs are
> being deleted as scheduled. Please help.
You may also want to check what the 'effective' date is on your job -
see if your date range is out of whack, or else, if the process that
is supposed to be deleting the files is running into a 'file lock'. I
have had problems before like this, where a process that copies my old
backup files to a new directory was not actually terminating all the
time, and so then whenever the other process tried to delete them, it
couldn't. If you can look at that - it may help - or else, try
rebooting the server (if you can) and waiting to see if it behaves
normally again.
When in doubt, reboot *grin*
Sonya
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> My maintenance plan is set up to delete the old database dump files and transaction logs. After
months of this working, the dump files are no longer being deleted. This has happened before. I
recreated the plan and it worked for a while, then stopped again. The old transaction logs are
being deleted as scheduled. Please help.|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se> wrote in message news:<O7uyC2XxDHA.3224@.tk2msftngp13.phx.gbl>...
> Below KB might help:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
>
> Also, check out below great troubleshooting suggestions from Bill H at MS:
>
> -- Log files don't delete --
> This is likely to be either a permissions problem or a sharing violation
> problem. The maintenance plan is run as a job, and jobs are run by the
> SQLServerAgent service.
> Permissions:
> 1. Determine the startup account for the SQLServerAgent service
> (Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
> account is the security context for jobs, and thus the maintenance plan.
> 2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
> account) then skip step 3.
> 3. On that box, log onto NT as that account. Using Explorer, attempt to
> delete an expired backup. If that succeeds then go to Sharing Violation
> section.
> 4. Log onto NT with an account that is an administrator and use Explorer to
> look at the Properties|Security of the folder (where the backups reside)
> and ensure the SQLServerAgent startup account has Full Control. If the
> SQLServerAgent startup account is LocalSystem, then the account to consider
> is SYSTEM.
> 5. In NT, if an account is a member of an NT group, and if that group has
> Access is Denied, then that account will have Access is Denied, even if
> that account is also a member of the Administrators group. Thus you may
> need to check group permissions (if the Startup Account is a member of a
> group).
> 6. Keep in mind that permissions (by default) are inherited from a parent
> folder. Thus, if the backups are stored in C:\bak, and if someone had
> denied permission to the SQLServerAgent startup account for C:\, then
> C:\bak will inherit access is denied.
> Sharing violation:
> This is likely to be rooted in a timing issue, with the most likely cause
> being another scheduled process (such as NT Backup or Anti-Virus software)
> having the backup file open at the time when the SQLServerAgent (i.e., the
> maintenance plan job) tried to delete it.
> 1. Download filemon and handle from www.sysinternals.com.
> 2. I am not sure whether filemon can be scheduled, or you might be able to
> use NT scheduling services to start filemon just before the maintenance
> plan job is started, but the filemon log can become very large, so it would
> be best to start it some short time before the maintenance plan starts.
> 3. Inspect the filemon log for another process that has that backup file
> open (if your lucky enough to have started filemon before this other
> process grabs the backup folder), and inspect the log for the results when
> the SQLServerAgent agent attempts to open that same file.
> 4. Schedule the job or that other process to do their work at different
> times.
> 5. You can use the handle utility if you are around at the time when the
> job is scheduled to run.
> If the backup files are going to a \\share or a mapped drive (as opposed to
> local drive), then you will need to modify the above (with respect to where
> the tests and utilities are run).
> Finally, inspection of the maintenance plan's history report might be
> useful.
> Thanks,
> Bill Hollinshead
> Microsoft, SQL Server
>
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Smith" <anonymous@.discussions.microsoft.com> wrote in message
> news:B0355203-D93B-44F1-AD39-9EEE5194DEE3@.microsoft.com...
> > My maintenance plan is set up to delete the old database dump files and transaction logs. After
> months of this working, the dump files are no longer being deleted. This has happened before. I
> recreated the plan and it worked for a while, then stopped again. The old transaction logs are
> being deleted as scheduled. Please help.
You may also want to check what the 'effective' date is on your job -
see if your date range is out of whack, or else, if the process that
is supposed to be deleting the files is running into a 'file lock'. I
have had problems before like this, where a process that copies my old
backup files to a new directory was not actually terminating all the
time, and so then whenever the other process tried to delete them, it
couldn't. If you can look at that - it may help - or else, try
rebooting the server (if you can) and waiting to see if it behaves
normally again.
When in doubt, reboot *grin*
Sonya
Database Maintenance Plan for SQL Server 2005 Problem
I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If these
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:
> I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected.
> I have checked and found that SQL Server Agent is already started. Is there
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am
> not able to create both DB Backup (early in the morning) and Transaction log
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
Most commonly this is because you have included some databases that are in simple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that task.
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you don't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ... it
> seems that they are run at the same time. Is there any way to change the time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance
>>Plan.
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild
>> index, database backup ... it seems that they are run at the same time.
>> Is there any way to change the time they are running ?
>> Thanks
>> Peter
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a likely cause.
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors, I assume that the
statements were executed with success.
> If there is problem when the maintenance plan is executed, will the error message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged to the various log
destinations (report file, maint plan history tables etc). Whether an error is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed yesterday (It supposed to
> start immediately) but it works properly today. The daily production database backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system database backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error). If there is problem when the maintenance plan is executed, will the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Most commonly this is because you have included some databases that are in simple recovery mode.
>> When a maint plan is returned an error inside a task, it will terminate that task.
>>
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
>> big thing since you will still have to create at least two tasks:
>> One for the databases in simple recovery (master, for example), where you don't do log backup.
>> And another for the database where you do want to do log backups...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ...
>> it seems that they are run at the same time. Is there any way to change the time they are
>> running ?
>> Thanks
>> Peter
>>
>
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If these
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:
> I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected.
> I have checked and found that SQL Server Agent is already started. Is there
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am
> not able to create both DB Backup (early in the morning) and Transaction log
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
Most commonly this is because you have included some databases that are in simple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that task.
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you don't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
> However, I find that the transaction log backup job doesn't run as expected. I have checked and
> found that SQL Server Agent is already started. Is there any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I am not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ... it
> seems that they are run at the same time. Is there any way to change the time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance
>>Plan.
>> However, I find that the transaction log backup job doesn't run as
>> expected. I have checked and found that SQL Server Agent is already
>> started. Is there any way to check where the problem is ?
>> Besides, when I am creating the database maintenance plan, I find that I
>> am not able to create both DB Backup (early in the morning) and
>> Transaction log backup (hourly) by using the same DB Maintenance Plan.
>> Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild
>> index, database backup ... it seems that they are run at the same time.
>> Is there any way to change the time they are running ?
>> Thanks
>> Peter
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a likely cause.
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors, I assume that the
statements were executed with success.
> If there is problem when the maintenance plan is executed, will the error message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged to the various log
destinations (report file, maint plan history tables etc). Whether an error is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed yesterday (It supposed to
> start immediately) but it works properly today. The daily production database backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system database backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintenance plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't executed and there is of course
> no error). If there is problem when the maintenance plan is executed, will the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Most commonly this is because you have included some databases that are in simple recovery mode.
>> When a maint plan is returned an error inside a task, it will terminate that task.
>>
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Yes. But in sp2, you will be able to have separate schedules for each task. I don't see this as a
>> big thing since you will still have to create at least two tasks:
>> One for the databases in simple recovery (master, for example), where you don't do log backup.
>> And another for the database where you do want to do log backups...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Peter" <Peter@.discussions.microsoft.com> wrote in message
>> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
>> However, I find that the transaction log backup job doesn't run as expected. I have checked and
>> found that SQL Server Agent is already started. Is there any way to check where the problem is
>> ?
>> Besides, when I am creating the database maintenance plan, I find that I am not able to create
>> both DB Backup (early in the morning) and Transaction log backup (hourly) by using the same DB
>> Maintenance Plan. Does it mean that I have to create 2 different plans with the wizard ?
>> Moreover, for weekly maintenance, database integrity check, rebuild index, database backup ...
>> it seems that they are run at the same time. Is there any way to change the time they are
>> running ?
>> Thanks
>> Peter
>>
>
Database Maintenance Plan for SQL Server 2005 Problem
I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan.
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If thes
e
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:
> I am new to SQL Server 2005 and just set up a number of DB Maintenance Pla
n.
> However, I find that the transaction log backup job doesn't run as expecte
d.
> I have checked and found that SQL Server Agent is already started. Is the
re
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m
> not able to create both DB Backup (early in the morning) and Transaction l
og
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that
I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checke
d and
> found that SQL Server Agent is already started. Is there any way to check where t
he problem is ?
Most commonly this is because you have included some databases that are in s
imple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that
task.
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the w
izard ?
Yes. But in sp2, you will be able to have separate schedules for each task.
I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you do
n't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan
.
> However, I find that the transaction log backup job doesn't run as expecte
d. I have checked and
> found that SQL Server Agent is already started. Is there any way to check
where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans wi
th the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it
> seems that they are run at the same time. Is there any way to change the
time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I
change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a li
kely cause.
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors
, I assume that the
statements were executed with success.
> If there is problem when the maintenance plan is executed, will the error
message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged
to the various log
destinations (report file, maint plan history tables etc). Whether an error
is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed y
esterday (It supposed to
> start immediately) but it works properly today. The daily production datab
ase backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system datab
ase backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error). If there is problem when the maintenance plan is executed, wil
l the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>
However, I find that the transaction log backup job doesn't run as expected.
I have checked and found that SQL Server Agent is already started. Is there
any way to check where the problem is ?
Besides, when I am creating the database maintenance plan, I find that I am
not able to create both DB Backup (early in the morning) and Transaction log
backup (hourly) by using the same DB Maintenance Plan. Does it mean that I
have to create 2 different plans with the wizard ?
Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it seems that they are run at the same time. Is there
any way to change the time they are running ?
Thanks
PeterHi Peter
If you edit the plan in Management Studio, there is a logging button to send
information to a file, which also has an extended logging check box. If thes
e
are set up you may have information in this file.
I would expect you would want transaction log backups and full backups to be
scheduled separately and therefore the easiest solution would be to have two
plans. I don't think you can add conditional execution in the maintenance
wizard, but you could use BIDS to create a SSIS package that does the
maintenance tasks for you and then add a script task to make what backup you
do conditional.
John
"Peter" wrote:
> I am new to SQL Server 2005 and just set up a number of DB Maintenance Pla
n.
> However, I find that the transaction log backup job doesn't run as expecte
d.
> I have checked and found that SQL Server Agent is already started. Is the
re
> any way to check where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m
> not able to create both DB Backup (early in the morning) and Transaction l
og
> backup (hourly) by using the same DB Maintenance Plan. Does it mean that
I
> have to create 2 different plans with the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
> database backup ... it seems that they are run at the same time. Is there
> any way to change the time they are running ?
> Thanks
> Peter
>
>|||> However, I find that the transaction log backup job doesn't run as expected. I have checke
d and
> found that SQL Server Agent is already started. Is there any way to check where t
he problem is ?
Most commonly this is because you have included some databases that are in s
imple recovery mode.
When a maint plan is returned an error inside a task, it will terminate that
task.
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans with the w
izard ?
Yes. But in sp2, you will be able to have separate schedules for each task.
I don't see this as a
big thing since you will still have to create at least two tasks:
One for the databases in simple recovery (master, for example), where you do
n't do log backup.
And another for the database where you do want to do log backups...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>I am new to SQL Server 2005 and just set up a number of DB Maintenance Plan
.
> However, I find that the transaction log backup job doesn't run as expecte
d. I have checked and
> found that SQL Server Agent is already started. Is there any way to check
where the problem is ?
> Besides, when I am creating the database maintenance plan, I find that I a
m not able to create
> both DB Backup (early in the morning) and Transaction log backup (hourly)
by using the same DB
> Maintenance Plan. Does it mean that I have to create 2 different plans wi
th the wizard ?
> Moreover, for weekly maintenance, database integrity check, rebuild index,
database backup ... it
> seems that they are run at the same time. Is there any way to change the
time they are running ?
> Thanks
> Peter
>|||Dear John and Tibor,
Many thanks for your advice.
To my surprise, I find that the transaction log backup doesn't performed
yesterday (It supposed to start immediately) but it works properly today.
The daily production database backup also doesn't performed last night at
8:00pm. On the other hand, the daily system database backup is performed
successfully at 2:00am this morning. It is pretty weird.
I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change it to 3:30pm later. Would it be a reason for the
problem ?
Besides, from the maintenance log file, it only mentions that the
maintenance plan is executed but it hasn't mentioned any error encountered
(It seems that it hasn't executed and there is of course no error). If
there is problem when the maintenance plan is executed, will the error
message be shown on the log files OR should be found in SQL Error Log ?
Thanks
Peter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
> Most commonly this is because you have included some databases that are in
> simple recovery mode. When a maint plan is returned an error inside a
> task, it will terminate that task.
>
> Yes. But in sp2, you will be able to have separate schedules for each
> task. I don't see this as a big thing since you will still have to create
> at least two tasks:
> One for the databases in simple recovery (master, for example), where you
> don't do log backup.
> And another for the database where you do want to do log backups...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Peter" <Peter@.discussions.microsoft.com> wrote in message
> news:uUYrZRQHHHA.2236@.TK2MSFTNGP02.phx.gbl...
>|||> I believe that it may be because when I set up the server, the time is set at 9:00pm and I
change
> it to 3:30pm later. Would it be a reason for the problem ?
I vaguelly recall others seeing similar issues, so I'd say that this is a li
kely cause.
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error).
You mean the Maint Plan report file? In general, when I don't see any errors
, I assume that the
statements were executed with success.
> If there is problem when the maintenance plan is executed, will the error
message be shown on the
> log files OR should be found in SQL Error Log ?
I haven't played enough with 2005 maint plans to say for sure what is logged
to the various log
destinations (report file, maint plan history tables etc). Whether an error
is logged to the event
log depends on that error (see the sys.messages catalog view).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter" <Peter@.discussions.microsoft.com> wrote in message
news:%23CIMj3oHHHA.816@.TK2MSFTNGP06.phx.gbl...
> Dear John and Tibor,
> Many thanks for your advice.
> To my surprise, I find that the transaction log backup doesn't performed y
esterday (It supposed to
> start immediately) but it works properly today. The daily production datab
ase backup also doesn't
> performed last night at 8:00pm. On the other hand, the daily system datab
ase backup is performed
> successfully at 2:00am this morning. It is pretty weird.
> I believe that it may be because when I set up the server, the time is set
at 9:00pm and I change
> it to 3:30pm later. Would it be a reason for the problem ?
> Besides, from the maintenance log file, it only mentions that the maintena
nce plan is executed but
> it hasn't mentioned any error encountered (It seems that it hasn't execute
d and there is of course
> no error). If there is problem when the maintenance plan is executed, wil
l the error message be
> shown on the log files OR should be found in SQL Error Log ?
> Thanks
> Peter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:etjWvuRHHHA.4904@.TK2MSFTNGP04.phx.gbl...
>
Database maintenance plan backup
I've created a database maintenance plan with backup database task which is
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
Paul
Paul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>
|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>
|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
Paul
Paul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>
|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>
|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
Database maintenance plan backup
I've created a database maintenance plan with backup database task which is
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> > I've created a database maintenance plan with backup database task which
> > is
> > set to back up the transaction logs of several specified databases. I
> > open
> > up the backup database task, then choose <select one or more> from the
> > Database(s) prompt. When the drop down window opens, I select the radio
> > box
> > for "these databases", and I check the databases that I want. I save the
> > database maintenance plan and run it and it works fine. If I open up the
> > backup database task again, and go to see what databases I have selected,
> > none are showing as selected. If I save the database maintenance plan and
> > run it, it fails with the following message, which does not tell me much:
> >
> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> > 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> > 100% complete End Progress DTExec: The package execution returned
> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> > 1.437 seconds. The package execution failed. The step failed.
> >
> > This message contrasts with the text file report that shows:
> >
> > NEW COMPONENT OUTPUT
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> > Report was generated on "pdcnt23".
> > Maintenance Plan: MaintenancePlan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > If I go back into the backup task and reselect the databases and save the
> > maintenance plan and run it, everything works fine.
> >
> > This doesn't work as I would expect it to. Is there something I need to
> > do?
> >
> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> >
> > Thanks,
> >
> > Paul
> >
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>> Paul,
>> Losing the database is a known problem. MS is looking into it for a later
>> build of SQL2005.
>> Chris
>> "PT" <PT@.discussions.microsoft.com> wrote in message
>> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>> > I've created a database maintenance plan with backup database task
>> > which
>> > is
>> > set to back up the transaction logs of several specified databases. I
>> > open
>> > up the backup database task, then choose <select one or more> from the
>> > Database(s) prompt. When the drop down window opens, I select the radio
>> > box
>> > for "these databases", and I check the databases that I want. I save
>> > the
>> > database maintenance plan and run it and it works fine. If I open up
>> > the
>> > backup database task again, and go to see what databases I have
>> > selected,
>> > none are showing as selected. If I save the database maintenance plan
>> > and
>> > run it, it fails with the following message, which does not tell me
>> > much:
>> >
>> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server
>> > Execute
>> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C)
>> > Microsoft
>> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM
>> > Progress:
>> > 2007-11-16 10:58:15.67 Source:
>> > {0EDA7F25-ED67-4796-9399-668FB2506E5F}
>> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE
>> > msdb..sp".:
>> > 100% complete End Progress DTExec: The package execution returned
>> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM
>> > Elapsed:
>> > 1.437 seconds. The package execution failed. The step failed.
>> >
>> > This message contrasts with the text file report that shows:
>> >
>> > NEW COMPONENT OUTPUT
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
>> > Report was generated on "pdcnt23".
>> > Maintenance Plan: MaintenancePlan
>> > Duration: 00:00:00
>> > Status: Succeeded.
>> > Details:
>> >
>> > If I go back into the backup task and reselect the databases and save
>> > the
>> > maintenance plan and run it, everything works fine.
>> >
>> > This doesn't work as I would expect it to. Is there something I need
>> > to
>> > do?
>> >
>> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
>> >
>> > Thanks,
>> >
>> > Paul
>> >
>>
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> > I've created a database maintenance plan with backup database task which
> > is
> > set to back up the transaction logs of several specified databases. I
> > open
> > up the backup database task, then choose <select one or more> from the
> > Database(s) prompt. When the drop down window opens, I select the radio
> > box
> > for "these databases", and I check the databases that I want. I save the
> > database maintenance plan and run it and it works fine. If I open up the
> > backup database task again, and go to see what databases I have selected,
> > none are showing as selected. If I save the database maintenance plan and
> > run it, it fails with the following message, which does not tell me much:
> >
> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> > 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5F}
> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> > 100% complete End Progress DTExec: The package execution returned
> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> > 1.437 seconds. The package execution failed. The step failed.
> >
> > This message contrasts with the text file report that shows:
> >
> > NEW COMPONENT OUTPUT
> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> > Report was generated on "pdcnt23".
> > Maintenance Plan: MaintenancePlan
> > Duration: 00:00:00
> > Status: Succeeded.
> > Details:
> >
> > If I go back into the backup task and reselect the databases and save the
> > maintenance plan and run it, everything works fine.
> >
> > This doesn't work as I would expect it to. Is there something I need to
> > do?
> >
> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> >
> > Thanks,
> >
> > Paul
> >
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>> Paul,
>> Losing the database is a known problem. MS is looking into it for a later
>> build of SQL2005.
>> Chris
>> "PT" <PT@.discussions.microsoft.com> wrote in message
>> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>> > I've created a database maintenance plan with backup database task
>> > which
>> > is
>> > set to back up the transaction logs of several specified databases. I
>> > open
>> > up the backup database task, then choose <select one or more> from the
>> > Database(s) prompt. When the drop down window opens, I select the radio
>> > box
>> > for "these databases", and I check the databases that I want. I save
>> > the
>> > database maintenance plan and run it and it works fine. If I open up
>> > the
>> > backup database task again, and go to see what databases I have
>> > selected,
>> > none are showing as selected. If I save the database maintenance plan
>> > and
>> > run it, it fails with the following message, which does not tell me
>> > much:
>> >
>> > Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server
>> > Execute
>> > Package Utility Version 9.00.3042.00 for 32-bit Copyright (C)
>> > Microsoft
>> > Corp 1984-2005. All rights reserved. Started: 10:58:14 AM
>> > Progress:
>> > 2007-11-16 10:58:15.67 Source:
>> > {0EDA7F25-ED67-4796-9399-668FB2506E5F}
>> > Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE
>> > msdb..sp".:
>> > 100% complete End Progress DTExec: The package execution returned
>> > DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM
>> > Elapsed:
>> > 1.437 seconds. The package execution failed. The step failed.
>> >
>> > This message contrasts with the text file report that shows:
>> >
>> > NEW COMPONENT OUTPUT
>> > Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
>> > Report was generated on "pdcnt23".
>> > Maintenance Plan: MaintenancePlan
>> > Duration: 00:00:00
>> > Status: Succeeded.
>> > Details:
>> >
>> > If I go back into the backup task and reselect the databases and save
>> > the
>> > maintenance plan and run it, everything works fine.
>> >
>> > This doesn't work as I would expect it to. Is there something I need
>> > to
>> > do?
>> >
>> > Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
>> >
>> > Thanks,
>> >
>> > Paul
>> >
>>
Database maintenance plan backup
I've created a database maintenance plan with backup database task which is
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5
F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506
E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>
set to back up the transaction logs of several specified databases. I open
up the backup database task, then choose <select one or more> from the
Database(s) prompt. When the drop down window opens, I select the radio box
for "these databases", and I check the databases that I want. I save the
database maintenance plan and run it and it works fine. If I open up the
backup database task again, and go to see what databases I have selected,
none are showing as selected. If I save the database maintenance plan and
run it, it fails with the following message, which does not tell me much:
Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506E5
F}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
100% complete End Progress DTExec: The package execution returned
DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
1.437 seconds. The package execution failed. The step failed.
This message contrasts with the text file report that shows:
NEW COMPONENT OUTPUT
Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
Report was generated on "pdcnt23".
Maintenance Plan: MaintenancePlan
Duration: 00:00:00
Status: Succeeded.
Details:
If I go back into the backup task and reselect the databases and save the
maintenance plan and run it, everything works fine.
This doesn't work as I would expect it to. Is there something I need to do?
Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
Thanks,
PaulPaul,
Losing the database is a known problem. MS is looking into it for a later
build of SQL2005.
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
> I've created a database maintenance plan with backup database task which
> is
> set to back up the transaction logs of several specified databases. I
> open
> up the backup database task, then choose <select one or more> from the
> Database(s) prompt. When the drop down window opens, I select the radio
> box
> for "these databases", and I check the databases that I want. I save the
> database maintenance plan and run it and it works fine. If I open up the
> backup database task again, and go to see what databases I have selected,
> none are showing as selected. If I save the database maintenance plan and
> run it, it fails with the following message, which does not tell me much:
> Executed as user: PDCDOMAIN\$SQLAGNTSRV. Microsoft (R) SQL Server Execute
> Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft
> Corp 1984-2005. All rights reserved. Started: 10:58:14 AM Progress:
> 2007-11-16 10:58:15.67 Source: {0EDA7F25-ED67-4796-9399-668FB2506
E5F}
> Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE msdb..sp".:
> 100% complete End Progress DTExec: The package execution returned
> DTSER_FAILURE (1). Started: 10:58:14 AM Finished: 10:58:16 AM Elapsed:
> 1.437 seconds. The package execution failed. The step failed.
> This message contrasts with the text file report that shows:
> NEW COMPONENT OUTPUT
> Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3175
> Report was generated on "pdcnt23".
> Maintenance Plan: MaintenancePlan
> Duration: 00:00:00
> Status: Succeeded.
> Details:
> If I go back into the backup task and reselect the databases and save the
> maintenance plan and run it, everything works fine.
> This doesn't work as I would expect it to. Is there something I need to
> do?
> Currently running SQL Server 2005, SP2 9.00.3054 Standard Edition.
> Thanks,
> Paul
>|||Thanks Chris.
I had a look around to try and find information about it. Where would I
find more information about it?
Thanks,
Paul
"Chris Wood" wrote:
> Paul,
> Losing the database is a known problem. MS is looking into it for a later
> build of SQL2005.
> Chris
> "PT" <PT@.discussions.microsoft.com> wrote in message
> news:FE4C4861-8C97-46C0-AD6B-754C46BBFEF1@.microsoft.com...
>
>|||Paul,
Signon to Microsoft Connect:- https://connect.microsoft.com/default.aspx and
register for SQL
Chris
"PT" <PT@.discussions.microsoft.com> wrote in message
news:4C86DAAF-11E8-4CA7-B740-A3CF4BF32C69@.microsoft.com...[vbcol=seagreen]
> Thanks Chris.
> I had a look around to try and find information about it. Where would I
> find more information about it?
> Thanks,
> Paul
> "Chris Wood" wrote:
>
Friday, February 17, 2012
Database log truncated
Recently we've upgraded SQL 7.0 to SQL 2000. We use a Database Maintenance Plans to backup the databases and transaction logs. Everything seems to be working, but Im getting an error message in the event viewer that has been described in Microsoft Knowledge Base article 818202 "PRB: A'Database log truncated" Error is logged in the Event Log When you try to Backup the Transaction Log. The article describes the cause of this warning, but does not give any fix or some kind of work around. Does anybody knows what to do or may be had this problem before? Please help. Thanks a lot.Switch your database to simple recovery.
backup the transaction log with truncate_only
Switch your database back to Full recovery.
run your maintance plan.|||As the article states, the cause for this entry in Event Log is the presence of either TRUNCATE_ONLY or NO_LOG.
Look into SQLMAINT command line utility, it gives you higher level of control, and you can use it against Database Maintenance plans as well.|||Make sure you issue full backup statement once NO_LOG or TRUNCATE_ONLY is issued on Tlog.|||Originally posted by rdjabarov
As the article states, the cause for this entry in Event Log is the presence of either TRUNCATE_ONLY or NO_LOG.
Look into SQLMAINT command line utility, it gives you higher level of control, and you can use it against Database Maintenance plans as well.
Yes, the article states that, but where I can see presence of these two options(TRUNCATE_ONLY or NO_LOG). I mean this job was created by Maintenance plan and is running as :
EXECUTE master.dbo.xp_sqlmaint N'-PlanID E4B335C8-20F2-4E29-A332-BD4778515EF5 -Rpt "D:\MSSQL7\LOG\DB Maintenance Plan20006.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\MSSQL7\BACKUP" -DelBkUps 1DAYS -BkExt "TRN"'
And if i use SQLMAINT utility to run the Database Maintenance plans what swich should i use for TRUNCATE_ONLY or NO_LOG options?
One more thing i don't understand it only happened on one server and 2 other works just fine.|||That's a mystery. Did you try to run the job manually? Could it be that it's not your job that fires NO_LOG, but some other maybe even ad-hoc statement issued from QA?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;288577 KBA to troubleshoot Maint.plans.
HTH
backup the transaction log with truncate_only
Switch your database back to Full recovery.
run your maintance plan.|||As the article states, the cause for this entry in Event Log is the presence of either TRUNCATE_ONLY or NO_LOG.
Look into SQLMAINT command line utility, it gives you higher level of control, and you can use it against Database Maintenance plans as well.|||Make sure you issue full backup statement once NO_LOG or TRUNCATE_ONLY is issued on Tlog.|||Originally posted by rdjabarov
As the article states, the cause for this entry in Event Log is the presence of either TRUNCATE_ONLY or NO_LOG.
Look into SQLMAINT command line utility, it gives you higher level of control, and you can use it against Database Maintenance plans as well.
Yes, the article states that, but where I can see presence of these two options(TRUNCATE_ONLY or NO_LOG). I mean this job was created by Maintenance plan and is running as :
EXECUTE master.dbo.xp_sqlmaint N'-PlanID E4B335C8-20F2-4E29-A332-BD4778515EF5 -Rpt "D:\MSSQL7\LOG\DB Maintenance Plan20006.txt" -DelTxtRpt 2WEEKS -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpLog "D:\MSSQL7\BACKUP" -DelBkUps 1DAYS -BkExt "TRN"'
And if i use SQLMAINT utility to run the Database Maintenance plans what swich should i use for TRUNCATE_ONLY or NO_LOG options?
One more thing i don't understand it only happened on one server and 2 other works just fine.|||That's a mystery. Did you try to run the job manually? Could it be that it's not your job that fires NO_LOG, but some other maybe even ad-hoc statement issued from QA?|||http://support.microsoft.com/default.aspx?scid=kb;en-us;288577 KBA to troubleshoot Maint.plans.
HTH
Database log files
Does anyone know of anyway to read the transaction logs? The reason why I am
curios is, the transaction logs are way to large for the amount of
transactions we are running(yes, the transaction logs are set to
automatically grow). Our application would not send that amount of
transactions to the database. The other thing I am thinking is that there is
another db within this sqlserver that I think access this DB and I think the
transaction logs would prove this.
Thanks for your assistance in advance....
I've listed three log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there is
> another db within this sqlserver that I think access this DB and I think the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||What recovery mode are you in? If it is FULL and you don't issue regular
Log backups it will continue to grow and grow. Change it to simple mode and
you should not see it grow any more. If you really want to see what is
happening on the server you should use Profiler or Trace.
Andrew J. Kelly SQL MVP
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I
> am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there
> is
> another db within this sqlserver that I think access this DB and I think
> the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||TJ
Another option to consider is the Bulk-Logged recovery mode. Bulk-logged
has better performance than Full with large bulk inserts and bcp operations
because the transaction log files should not grow as quickly as a result of
only recording complete transactions. In other words, you sacrifice the
ability to recover row by row that might occur with large batches.
Regards,
Keith
This posting is provided "as is" without inferring rights or warranties.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOihJpswFHA.2076@.TK2MSFTNGP14.phx.gbl...
> What recovery mode are you in? If it is FULL and you don't issue regular
> Log backups it will continue to grow and grow. Change it to simple mode
> and you should not see it grow any more. If you really want to see what
> is happening on the server you should use Profiler or Trace.
> --
> Andrew J. Kelly SQL MVP
>
> "TJ" <TJ@.discussions.microsoft.com> wrote in message
> news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
>
curios is, the transaction logs are way to large for the amount of
transactions we are running(yes, the transaction logs are set to
automatically grow). Our application would not send that amount of
transactions to the database. The other thing I am thinking is that there is
another db within this sqlserver that I think access this DB and I think the
transaction logs would prove this.
Thanks for your assistance in advance....
I've listed three log reader tools on my links page:
http://www.karaszi.com/SQLServer/links.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there is
> another db within this sqlserver that I think access this DB and I think the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||What recovery mode are you in? If it is FULL and you don't issue regular
Log backups it will continue to grow and grow. Change it to simple mode and
you should not see it grow any more. If you really want to see what is
happening on the server you should use Profiler or Trace.
Andrew J. Kelly SQL MVP
"TJ" <TJ@.discussions.microsoft.com> wrote in message
news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
> Does anyone know of anyway to read the transaction logs? The reason why I
> am
> curios is, the transaction logs are way to large for the amount of
> transactions we are running(yes, the transaction logs are set to
> automatically grow). Our application would not send that amount of
> transactions to the database. The other thing I am thinking is that there
> is
> another db within this sqlserver that I think access this DB and I think
> the
> transaction logs would prove this.
> Thanks for your assistance in advance....
|||TJ
Another option to consider is the Bulk-Logged recovery mode. Bulk-logged
has better performance than Full with large bulk inserts and bcp operations
because the transaction log files should not grow as quickly as a result of
only recording complete transactions. In other words, you sacrifice the
ability to recover row by row that might occur with large batches.
Regards,
Keith
This posting is provided "as is" without inferring rights or warranties.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOihJpswFHA.2076@.TK2MSFTNGP14.phx.gbl...
> What recovery mode are you in? If it is FULL and you don't issue regular
> Log backups it will continue to grow and grow. Change it to simple mode
> and you should not see it grow any more. If you really want to see what
> is happening on the server you should use Profiler or Trace.
> --
> Andrew J. Kelly SQL MVP
>
> "TJ" <TJ@.discussions.microsoft.com> wrote in message
> news:92CF3A8E-4DA0-4E1B-BCA8-EC4491DCAFED@.microsoft.com...
>
Subscribe to:
Posts (Atom)