sql backup files: perform

Showing posts with label perform. Show all posts

Tuesday, March 27, 2012

Backup SQL to both TAPE AND DISK

Here's my problem. I want to perform occasional full backups on a tape but I
also want to backup to disk. The disk backup does a full backup every night
and trans log backups every half hour between 7 AM and 7 PM. The tape backup
(Veritas) just performs a full backup every day. Now when I restore, I don't
want my disk backup to "depend" on my tape backup. In other words, I don't
want to see the tape backup in the SQL backup log when I'm trying to
performs a restore from the disk backups. Is there a way to do this?
Thanks
jBA database backup doesn't break the chain of log backups so you are fine. Yo
u can apply any of the
database backups as long as you apply all subsequent log backups in sequence
. But why not skip the
Veritas SQL Server agent and let Veritas pick up the database backup files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"msnews.microsoft.com" <jbonds@.XXX.com> wrote in message
news:O2XVi3BWGHA.4960@.TK2MSFTNGP05.phx.gbl...
> Here's my problem. I want to perform occasional full backups on a tape but
I also want to backup
> to disk. The disk backup does a full backup every night and trans log back
ups every half hour
> between 7 AM and 7 PM. The tape backup (Veritas) just performs a full back
up every day. Now when I
> restore, I don't want my disk backup to "depend" on my tape backup. In oth
er words, I don't want
> to see the tape backup in the SQL backup log when I'm trying to performs a
restore from the disk
> backups. Is there a way to do this?
> Thanks
> jB
>|||msnews.microsoft.com wrote:
> Here's my problem. I want to perform occasional full backups on a tape but
I
> also want to backup to disk. The disk backup does a full backup every nigh
t
> and trans log backups every half hour between 7 AM and 7 PM. The tape back
up
> (Veritas) just performs a full backup every day. Now when I restore, I don
't
> want my disk backup to "depend" on my tape backup. In other words, I don't
> want to see the tape backup in the SQL backup log when I'm trying to
> performs a restore from the disk backups. Is there a way to do this?
> Thanks
> jB
Unless you are also doing Differential backups, nothing will "depend"
on your full backup to tape.
Why do you care what is in the backup log (I assume you mean the
backupfile table)? If you aren't using Maintenance Plans you can safely
delete from that table anyway so maybe you could just delete stuff you
don't want to see.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--sql

Thursday, March 8, 2012

Backup passw

Hello Experts,
I perform a backup with a password (quiet long), but I'm
afraid of tools that may read the password/break the file.
My question is if I place a long password would it be
more difficult tools read the passw (255 chars) or it's
hopeless?
Thanks in advance!
Konstantinos MichasUsing a password on a backup doesn't prevent other people reading your data.
It just prevents an unauthorised person using SQLServer to restore the data
and therefore may make it slightly more difficult for them to access the
data. The backup isn't encrypted so it's still possible to read the backup
media using third party tools.
In short, if the confidentiality of your data is important to you, you
probably shouldn't rely on a backup password (of any length).
--
David Portas
SQL Server MVP
--|||To add some to David's correct response there is a 3rd party tool called SQL
LiteSpeed that will encrypt the entire backup for you and they won't be able
to easily crack it.
Andrew J. Kelly SQL MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:JuWdnZVgqIWAtTvdRVn-uw@.giganews.com...
> Using a password on a backup doesn't prevent other people reading your
data.
> It just prevents an unauthorised person using SQLServer to restore the
data
> and therefore may make it slightly more difficult for them to access the
> data. The backup isn't encrypted so it's still possible to read the backup
> media using third party tools.
> In short, if the confidentiality of your data is important to you, you
> probably shouldn't rely on a backup password (of any length).
> --
> David Portas
> SQL Server MVP
> --
>|||Hello and thanks both for your response, 3d party tools
can read the structure of DB from bakcup file, right?
Thanks in advance?
Konstantinos
>--Original Message--
>To add some to David's correct response there is a 3rd
party tool called SQL
>LiteSpeed that will encrypt the entire backup for you
and they won't be able
>to easily crack it.
>
>--
>Andrew J. Kelly SQL MVP
>
>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
wrote in message
>news:JuWdnZVgqIWAtTvdRVn-uw@.giganews.com...
>> Using a password on a backup doesn't prevent other
people reading your
>data.
>> It just prevents an unauthorised person using
SQLServer to restore the
>data
>> and therefore may make it slightly more difficult for
them to access the
>> data. The backup isn't encrypted so it's still
possible to read the backup
>> media using third party tools.
>> In short, if the confidentiality of your data is
important to you, you
>> probably shouldn't rely on a backup password (of any
length).
>> --
>> David Portas
>> SQL Server MVP
>> --
>>
>
>.
>|||What do you mean by that? The 3rd party tool I was referring to simply does
a backup and encrypts the resulting backup file. Are you asking if it can
then read the file later on? If so then of coarse as it would be pretty
useless to encrypt the file and not be able to restore it. But you will
need the key to do this and if memory serves me right it is 128 bit (or
higher) encryption.
Andrew J. Kelly SQL MVP
"ATHENS 2004" <anonymous@.discussions.microsoft.com> wrote in message
news:d8b101c43a8f$4f4b8460$a601280a@.phx.gbl...
> Hello and thanks both for your response, 3d party tools
> can read the structure of DB from bakcup file, right?
> Thanks in advance?
> Konstantinos
> >--Original Message--
> >To add some to David's correct response there is a 3rd
> party tool called SQL
> >LiteSpeed that will encrypt the entire backup for you
> and they won't be able
> >to easily crack it.
> >
> >
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
> wrote in message
> >news:JuWdnZVgqIWAtTvdRVn-uw@.giganews.com...
> >> Using a password on a backup doesn't prevent other
> people reading your
> >data.
> >> It just prevents an unauthorised person using
> SQLServer to restore the
> >data
> >> and therefore may make it slightly more difficult for
> them to access the
> >> data. The backup isn't encrypted so it's still
> possible to read the backup
> >> media using third party tools.
> >>
> >> In short, if the confidentiality of your data is
> important to you, you
> >> probably shouldn't rely on a backup password (of any
> length).
> >>
> >> --
> >> David Portas
> >> SQL Server MVP
> >> --
> >>
> >>
> >
> >
> >.
> >|||I don't know of any tool specifically designed to read a password protected
SQL backup if that's what you mean. I was actually referring to software
that can read *any* data from backup media (for example:
http://www.intermedia.uk.com/mmpc.htm). If you can read the data file then
it's certainly possible to extract information from it. I'm pretty sure it's
also theoretically possible to hack a password-protected backup so that it
can be restored to SQLServer without the password.
--
David Portas
SQL Server MVP
--|||I see, thank you both answering!
>--Original Message--
>Hello and thanks both for your response, 3d party tools
>can read the structure of DB from bakcup file, right?
>Thanks in advance?
>Konstantinos
>>--Original Message--
>>To add some to David's correct response there is a 3rd
>party tool called SQL
>>LiteSpeed that will encrypt the entire backup for you
>and they won't be able
>>to easily crack it.
>>
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org>
>wrote in message
>>news:JuWdnZVgqIWAtTvdRVn-uw@.giganews.com...
>> Using a password on a backup doesn't prevent other
>people reading your
>>data.
>> It just prevents an unauthorised person using
>SQLServer to restore the
>>data
>> and therefore may make it slightly more difficult for
>them to access the
>> data. The backup isn't encrypted so it's still
>possible to read the backup
>> media using third party tools.
>> In short, if the confidentiality of your data is
>important to you, you
>> probably shouldn't rely on a backup password (of any
>length).
>> --
>> David Portas
>> SQL Server MVP
>> --
>>
>>
>>.
>.
>

Backup passw

Friday, February 24, 2012

Backup Maintenence plan question

I have recently installed some maintenace plans to perform database backups for an application database. The database is in full recovery mode so I have setup a full backup to be run once per day and then transaction log backups to run every 2 hours throughout the day. My question pertains to the coordination within SQL Server with respect to the database backup and the transaction log backups.

Let's say the full database backup is in progress and at the same time a trasnaction log backup is fired. What happens? Does SQL Server just skip the tran log backup since it won't be needed? or does it do the backup of the tran log anyway (in case the backup fails).

Another question would be when is it safe to delete the transaction log backup target file? Can it be assumed that after a SUCCESSFUL full backup that the transaction log backup can be deleted?

Since I have seperate maintenance plans in place here I am really trying to find out how/if these tasks are integrated within SQL Server. I'm more familiar with Oracle and the use of RMAN. Specifically for cleanups, RMAN knows what is safe to delete and what isn't and so it will keep a backup on disk if it feels it is needed. Is there similar functionality in SQL Server?

Hello,

You're correct: a full backup will block a tran log backup (until the blocking backup cmd completes), and vice-a-versa.

Following a successful full backup, you can safely delete all tran log backups taken prior to the full backup.

There is no equivelant RMAN functionality provided by SQL Server. There is, however the EXPRIEDATE and RETAINDAYS clause you can make use of in the backup command. Check out BOL for more info, but it may help you...

Cheers,

Rob

|||

Actually, that was the case in SQL 2000.

In SQL 2005, you can concurrently run database backup and tran-log backups. The tran-log backup would simply contain the range of LSNs that span the time of the full backup. It would be used as the starting point for rolling forward from restoring that full backup.

Sunday, February 19, 2012

Backup log with no_log

Hi,

Every day we perform a full database backup.
(during the night).
Since we need no "additional" backups we had the problem that the
transaction log file grows and grows.
Therefore now just after the backup we perform a backup of the transaction
log with no log.
So we free up the space in the log file. (it truncates).

However, now we get this errormessage:

PRB: A "Database log truncated" Error is Logged in the Event Log When You
Try to Back Up the Transaction Log

http://support.microsoft.com/defaul...ben-us%3b818202

I am aware of this message but I think that it is basically a warning.
Am I correct ?

Arno de Jong, The Netherlands.The message is a warning that your database is using the Full recovery
model, but you have truncated the log and so it will not be possible to
recover to a point in time, or following a system failure - you will have to
load your last full backup, and any changes after that are lost.

If I understand your description correctly, you do a full backup every
night, but no transaction log backups during the day. If so, you might
consider putting the database in the Simple recovery model, which means the
log will not grow so large, and you will not see these warning messages. The
disadvantage is that if the system fails, you will lose all modifications
since the last full backup, but it looks like you don't want or need fuller
recovery anyway.

The BOL topic "Selecting a Recovery Model" has a good explanation of the
differences between the various recovery models.

Simon

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bdidgh$cn7$1@.reader08.wxs.nl...
> Hi,
> Every day we perform a full database backup.
> (during the night).
> Since we need no "additional" backups we had the problem that the
> transaction log file grows and grows.
> Therefore now just after the backup we perform a backup of the transaction
> log with no log.
> So we free up the space in the log file. (it truncates).
> However, now we get this errormessage:
> PRB: A "Database log truncated" Error is Logged in the Event Log When You
> Try to Back Up the Transaction Log
> http://support.microsoft.com/defaul...ben-us%3b818202
>
>
> I am aware of this message but I think that it is basically a warning.
> Am I correct ?
> Arno de Jong, The Netherlands.|||Hi Simon,

So it means that it is not possible to restore my database to any time
after the last backup is taken ?
But the transactions after the log file is shrunken are still in the
transaction log file, aren't they ??
So I would think that I still can restore up to any time ?

Suppose I want still be able to restore up to any time (since last full
backup) AND I would like to shrink my transaction log file: what is the
alternative ?

Bye

Arno de Jong

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||See comments inline.

"Arno De Jong" <anonymous@.devdex.com> wrote in message
news:3efecadc$0$198$75868355@.news.frii.net...
> Hi Simon,
> So it means that it is not possible to restore my database to any time
> after the last backup is taken ?

Right, because NO_LOG means all transactions after the full backup took
place are lost.

> But the transactions after the log file is shrunken are still in the
> transaction log file, aren't they ??

No - if you execute backup log ... with no_log then you lose transactions
completely.

> So I would think that I still can restore up to any time ?

No - point in time recovery is only possible if you have backed up the
transaction log, and you have not truncated it with NO_LOG since the last
full (or perhaps differential) backup. Check out "How to restore to the
point of failure (Transact-SQL)", and "Recovering to a Point In Time" in
Books Online.

> Suppose I want still be able to restore up to any time (since last full
> backup) AND I would like to shrink my transaction log file: what is the
> alternative ?

Then you should consider using Full (or possibly Bulk-Logged) recovery
model, with regular transaction log backups during the day - look at the
topic "Transaction Log Backups" in BOL for an example.

BACKUP LOG always frees up space inside the log file, even without NO_LOG,
but doesn't make the log files any smaller physically. The space will get
re-used for logging future transactions, or you can physically shrink the
log files to reclaim the space. "Shrinking the Transaction Log" in BOL gives
a detailed explanation of logical vs physical space in the logs.

> Bye
> Arno de Jong
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Monday, February 13, 2012

Backup Job Failing

I have an SSIS package that does one simple thing: perform a FULL backup of a database.

I executed this package yesterday at 2:00. The package backs up to four individual files on a network share. The network share is accessible from the SQL Server. THe database in question is 245GB in size.

The package was running fine when I left for the day. When I got in today, there was an error in the SQL Server log:

Error: 3041, Severity: 16, State: 1.

BACKUP failed to complete the command BACKUP DATABASE ServicingODS. Check the backup application log for detailed messages.

Where is this infamous backup application log?!? The Event Viewer says the same thing. Needless to say, the error message is a bit vague.

There were no "issus" overnight (power outages, network issues, etc.)

Anyone have any ideas?

Thanks!

Hi A.M

If you check the SQL server error log, there should be another line of error above the message that you have posted.

Could you please check and post a few lines from above and below the message you have already posted.

Jag

Sunday, February 12, 2012

Backup issue: Transaction & Differential

I have a backup schedule to do transaction log every hour and it fails quite
often latetly. In order to have a continuous backup, I have to perform
differential in between which is not part of the backup plan. The error I
received when the transaction log failed is "the mdf file has been subjects
to nonlogged updates and cannot be rolled forward. Perform a full data or
differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
goal is to have transaction throughout the day not a mix bag of transaction
log and differential. Is there a way to resolve this issue or how it can be
prevented? Thanks!You can put the recovery mode in FULL instead of Bulk Logged and then
everything will be fully logged. You must have some sort of bulk operation
happening during the day which voids the point in time restore capability of
the log backups.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>I have a backup schedule to do transaction log every hour and it fails
>quite
> often latetly. In order to have a continuous backup, I have to perform
> differential in between which is not part of the backup plan. The error
> I
> received when the transaction log failed is "the mdf file has been
> subjects
> to nonlogged updates and cannot be rolled forward. Perform a full data or
> differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> goal is to have transaction throughout the day not a mix bag of
> transaction
> log and differential. Is there a way to resolve this issue or how it can
> be
> prevented? Thanks!|||What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better to
keep track of whether the db has been in simple mode and will provide a more useful error message if
such is the case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>I have a backup schedule to do transaction log every hour and it fails quite
> often latetly. In order to have a continuous backup, I have to perform
> differential in between which is not part of the backup plan. The error I
> received when the transaction log failed is "the mdf file has been subjects
> to nonlogged updates and cannot be rolled forward. Perform a full data or
> differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> goal is to have transaction throughout the day not a mix bag of transaction
> log and differential. Is there a way to resolve this issue or how it can be
> prevented? Thanks!|||It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
FULL. The transaction log ran correctly before last 3 weeks and we have BCP
processes running for last 2 years. I can see the BCP process but why it
occurred since last 2 weeks is a puzzle to me. I also issue a check point
oftenly, but it does not help to resolve the issue.
Due to a lot of different reasons, we are not able to upgrade to later
version of SQL Server yet, but planning is in progress. Is there a way to
fix this?
Thanks to both Andrew & Tibor.
"Tibor Karaszi" wrote:
> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better to
> keep track of whether the db has been in simple mode and will provide a more useful error message if
> such is the case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >I have a backup schedule to do transaction log every hour and it fails quite
> > often latetly. In order to have a continuous backup, I have to perform
> > differential in between which is not part of the backup plan. The error I
> > received when the transaction log failed is "the mdf file has been subjects
> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> > goal is to have transaction throughout the day not a mix bag of transaction
> > log and differential. Is there a way to resolve this issue or how it can be
> > prevented? Thanks!
>|||The recovery model concept was introduced in 2000. So you should check the settings for below
database options:
truncate log on checkpoint
select into/bulkcopy
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> processes running for last 2 years. I can see the BCP process but why it
> occurred since last 2 weeks is a puzzle to me. I also issue a check point
> oftenly, but it does not help to resolve the issue.
> Due to a lot of different reasons, we are not able to upgrade to later
> version of SQL Server yet, but planning is in progress. Is there a way to
> fix this?
> Thanks to both Andrew & Tibor.
>
> "Tibor Karaszi" wrote:
>> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
>> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better
>> to
>> keep track of whether the db has been in simple mode and will provide a more useful error message
>> if
>> such is the case.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >I have a backup schedule to do transaction log every hour and it fails quite
>> > often latetly. In order to have a continuous backup, I have to perform
>> > differential in between which is not part of the backup plan. The error I
>> > received when the transaction log failed is "the mdf file has been subjects
>> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> > goal is to have transaction throughout the day not a mix bag of transaction
>> > log and differential. Is there a way to resolve this issue or how it can be
>> > prevented? Thanks!
>>|||Three options are set to check for database options: select into/bulk copy,
auto update statistics, auto create statistics. Because I like to have
transaction running, therefore, I have truncate log on checkpoint turn off.
My understand this is the setting so that transaction log can occur. Is
there any other settings or patch I should take a look at? Thanks!
"Tibor Karaszi" wrote:
> The recovery model concept was introduced in 2000. So you should check the settings for below
> database options:
> truncate log on checkpoint
> select into/bulkcopy
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> > processes running for last 2 years. I can see the BCP process but why it
> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
> > oftenly, but it does not help to resolve the issue.
> >
> > Due to a lot of different reasons, we are not able to upgrade to later
> > version of SQL Server yet, but planning is in progress. Is there a way to
> > fix this?
> >
> > Thanks to both Andrew & Tibor.
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is better
> >> to
> >> keep track of whether the db has been in simple mode and will provide a more useful error message
> >> if
> >> such is the case.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >I have a backup schedule to do transaction log every hour and it fails quite
> >> > often latetly. In order to have a continuous backup, I have to perform
> >> > differential in between which is not part of the backup plan. The error I
> >> > received when the transaction log failed is "the mdf file has been subjects
> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> >> > goal is to have transaction throughout the day not a mix bag of transaction
> >> > log and differential. Is there a way to resolve this issue or how it can be
> >> > prevented? Thanks!
> >>
> >>
>|||Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
operations. This is your problem.
7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does. In
2000, running in full recovery means that these potentially minimally logged operations will be
fully logged. But in earlier versions, some of these operations operation aren't allowed unless you
set "select into...". You can start by setting this to off and see if somebody screams. Or try to
track down the operation some other way and see what can be done about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> Three options are set to check for database options: select into/bulk copy,
> auto update statistics, auto create statistics. Because I like to have
> transaction running, therefore, I have truncate log on checkpoint turn off.
> My understand this is the setting so that transaction log can occur. Is
> there any other settings or patch I should take a look at? Thanks!
>
> "Tibor Karaszi" wrote:
>> The recovery model concept was introduced in 2000. So you should check the settings for below
>> database options:
>> truncate log on checkpoint
>> select into/bulkcopy
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
>> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
>> > processes running for last 2 years. I can see the BCP process but why it
>> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
>> > oftenly, but it does not help to resolve the issue.
>> >
>> > Due to a lot of different reasons, we are not able to upgrade to later
>> > version of SQL Server yet, but planning is in progress. Is there a way to
>> > fix this?
>> >
>> > Thanks to both Andrew & Tibor.
>> >
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
>> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
>> >> better
>> >> to
>> >> keep track of whether the db has been in simple mode and will provide a more useful error
>> >> message
>> >> if
>> >> such is the case.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >I have a backup schedule to do transaction log every hour and it fails quite
>> >> > often latetly. In order to have a continuous backup, I have to perform
>> >> > differential in between which is not part of the backup plan. The error I
>> >> > received when the transaction log failed is "the mdf file has been subjects
>> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> >> > goal is to have transaction throughout the day not a mix bag of transaction
>> >> > log and differential. Is there a way to resolve this issue or how it can be
>> >> > prevented? Thanks!
>> >>
>> >>
>>|||Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
last 3 weeks even though we have queries with select into or BCP running. Is
there any configuration in version 7.0 that can be track or set? I do run
profile from time to time, but all processes that perform select into or BCP
are the same since last 2 years. That is the mystery and there is no
specific pattern that I can pinpoint so in order I can figure a work around.
I understand your explanation & concept, but I am still not clear why last 3
weeks but not any time before that.
"Tibor Karaszi" wrote:
> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
> operations. This is your problem.
> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does. In
> 2000, running in full recovery means that these potentially minimally logged operations will be
> fully logged. But in earlier versions, some of these operations operation aren't allowed unless you
> set "select into...". You can start by setting this to off and see if somebody screams. Or try to
> track down the operation some other way and see what can be done about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> > Three options are set to check for database options: select into/bulk copy,
> > auto update statistics, auto create statistics. Because I like to have
> > transaction running, therefore, I have truncate log on checkpoint turn off.
> > My understand this is the setting so that transaction log can occur. Is
> > there any other settings or patch I should take a look at? Thanks!
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> The recovery model concept was introduced in 2000. So you should check the settings for below
> >> database options:
> >>
> >> truncate log on checkpoint
> >> select into/bulkcopy
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> >> > processes running for last 2 years. I can see the BCP process but why it
> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
> >> > oftenly, but it does not help to resolve the issue.
> >> >
> >> > Due to a lot of different reasons, we are not able to upgrade to later
> >> > version of SQL Server yet, but planning is in progress. Is there a way to
> >> > fix this?
> >> >
> >> > Thanks to both Andrew & Tibor.
> >> >
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has been
> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
> >> >> better
> >> >> to
> >> >> keep track of whether the db has been in simple mode and will provide a more useful error
> >> >> message
> >> >> if
> >> >> such is the case.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
> >> >> > often latetly. In order to have a continuous backup, I have to perform
> >> >> > differential in between which is not part of the backup plan. The error I
> >> >> > received when the transaction log failed is "the mdf file has been subjects
> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
> >> >> > log and differential. Is there a way to resolve this issue or how it can be
> >> >> > prevented? Thanks!
> >> >>
> >> >>
> >>
> >>
>|||BCP does not constitute a Minimally logged load which is the issue here. It
is a Bulk Load but a Bulk load will still be fully logged unless you have
certain conditions set. I can't remember what this is under in BOL 7.0 but
it should be under BCP - xxx or Minimally logged load and will explain all
the conditions you need. So it is quite possible that you only recently did
something to meet all of the conditions.
--
Andrew J. Kelly SQL MVP
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
> last 3 weeks even though we have queries with select into or BCP running.
> Is
> there any configuration in version 7.0 that can be track or set? I do run
> profile from time to time, but all processes that perform select into or
> BCP
> are the same since last 2 years. That is the mystery and there is no
> specific pattern that I can pinpoint so in order I can figure a work
> around.
> I understand your explanation & concept, but I am still not clear why last
> 3
> weeks but not any time before that.
> "Tibor Karaszi" wrote:
>> Since you have "select into/bulkcopy" turned on, you allow non-logged (or
>> minimally logged)
>> operations. This is your problem.
>> 7.0 and earlier versions didn't handle non-logged operations as neatly as
>> 2000 and later does. In
>> 2000, running in full recovery means that these potentially minimally
>> logged operations will be
>> fully logged. But in earlier versions, some of these operations operation
>> aren't allowed unless you
>> set "select into...". You can start by setting this to off and see if
>> somebody screams. Or try to
>> track down the operation some other way and see what can be done about
>> it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
>> > Three options are set to check for database options: select into/bulk
>> > copy,
>> > auto update statistics, auto create statistics. Because I like to
>> > have
>> > transaction running, therefore, I have truncate log on checkpoint turn
>> > off.
>> > My understand this is the setting so that transaction log can occur.
>> > Is
>> > there any other settings or patch I should take a look at? Thanks!
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> The recovery model concept was introduced in 2000. So you should check
>> >> the settings for below
>> >> database options:
>> >>
>> >> truncate log on checkpoint
>> >> select into/bulkcopy
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database
>> >> > is set to
>> >> > FULL. The transaction log ran correctly before last 3 weeks and we
>> >> > have BCP
>> >> > processes running for last 2 years. I can see the BCP process but
>> >> > why it
>> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check
>> >> > point
>> >> > oftenly, but it does not help to resolve the issue.
>> >> >
>> >> > Due to a lot of different reasons, we are not able to upgrade to
>> >> > later
>> >> > version of SQL Server yet, but planning is in progress. Is there a
>> >> > way to
>> >> > fix this?
>> >> >
>> >> > Thanks to both Andrew & Tibor.
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> What version of SQL Server? I'd run a Profiler to catch what
>> >> >> non-logged operations has been
>> >> >> performed in your database. The reason I ask for version is that
>> >> >> AFAIK, SQL Server 2005 is
>> >> >> better
>> >> >> to
>> >> >> keep track of whether the db has been in simple mode and will
>> >> >> provide a more useful error
>> >> >> message
>> >> >> if
>> >> >> such is the case.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >> >I have a backup schedule to do transaction log every hour and it
>> >> >> >fails quite
>> >> >> > often latetly. In order to have a continuous backup, I have to
>> >> >> > perform
>> >> >> > differential in between which is not part of the backup plan.
>> >> >> > The error I
>> >> >> > received when the transaction log failed is "the mdf file has
>> >> >> > been subjects
>> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full
>> >> >> > data or
>> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE
>> >> >> > HY007]. My
>> >> >> > goal is to have transaction throughout the day not a mix bag of
>> >> >> > transaction
>> >> >> > log and differential. Is there a way to resolve this issue or
>> >> >> > how it can be
>> >> >> > prevented? Thanks!
>> >> >>
>> >> >>
>> >>
>> >>
>>|||Thanks Andrew. I will look for more info regarding to BCP option that caused
this issue in 7.0 version.
"Andrew J. Kelly" wrote:
> BCP does not constitute a Minimally logged load which is the issue here. It
> is a Bulk Load but a Bulk load will still be fully logged unless you have
> certain conditions set. I can't remember what this is under in BOL 7.0 but
> it should be under BCP - xxx or Minimally logged load and will explain all
> the conditions you need. So it is quite possible that you only recently did
> something to meet all of the conditions.
> --
> Andrew J. Kelly SQL MVP
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
> > Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
> > last 3 weeks even though we have queries with select into or BCP running.
> > Is
> > there any configuration in version 7.0 that can be track or set? I do run
> > profile from time to time, but all processes that perform select into or
> > BCP
> > are the same since last 2 years. That is the mystery and there is no
> > specific pattern that I can pinpoint so in order I can figure a work
> > around.
> > I understand your explanation & concept, but I am still not clear why last
> > 3
> > weeks but not any time before that.
> >
> > "Tibor Karaszi" wrote:
> >
> >> Since you have "select into/bulkcopy" turned on, you allow non-logged (or
> >> minimally logged)
> >> operations. This is your problem.
> >>
> >> 7.0 and earlier versions didn't handle non-logged operations as neatly as
> >> 2000 and later does. In
> >> 2000, running in full recovery means that these potentially minimally
> >> logged operations will be
> >> fully logged. But in earlier versions, some of these operations operation
> >> aren't allowed unless you
> >> set "select into...". You can start by setting this to off and see if
> >> somebody screams. Or try to
> >> track down the operation some other way and see what can be done about
> >> it.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> >> > Three options are set to check for database options: select into/bulk
> >> > copy,
> >> > auto update statistics, auto create statistics. Because I like to
> >> > have
> >> > transaction running, therefore, I have truncate log on checkpoint turn
> >> > off.
> >> > My understand this is the setting so that transaction log can occur.
> >> > Is
> >> > there any other settings or patch I should take a look at? Thanks!
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> The recovery model concept was introduced in 2000. So you should check
> >> >> the settings for below
> >> >> database options:
> >> >>
> >> >> truncate log on checkpoint
> >> >> select into/bulkcopy
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> >> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database
> >> >> > is set to
> >> >> > FULL. The transaction log ran correctly before last 3 weeks and we
> >> >> > have BCP
> >> >> > processes running for last 2 years. I can see the BCP process but
> >> >> > why it
> >> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check
> >> >> > point
> >> >> > oftenly, but it does not help to resolve the issue.
> >> >> >
> >> >> > Due to a lot of different reasons, we are not able to upgrade to
> >> >> > later
> >> >> > version of SQL Server yet, but planning is in progress. Is there a
> >> >> > way to
> >> >> > fix this?
> >> >> >
> >> >> > Thanks to both Andrew & Tibor.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> What version of SQL Server? I'd run a Profiler to catch what
> >> >> >> non-logged operations has been
> >> >> >> performed in your database. The reason I ask for version is that
> >> >> >> AFAIK, SQL Server 2005 is
> >> >> >> better
> >> >> >> to
> >> >> >> keep track of whether the db has been in simple mode and will
> >> >> >> provide a more useful error
> >> >> >> message
> >> >> >> if
> >> >> >> such is the case.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >> >> >I have a backup schedule to do transaction log every hour and it
> >> >> >> >fails quite
> >> >> >> > often latetly. In order to have a continuous backup, I have to
> >> >> >> > perform
> >> >> >> > differential in between which is not part of the backup plan.
> >> >> >> > The error I
> >> >> >> > received when the transaction log failed is "the mdf file has
> >> >> >> > been subjects
> >> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full
> >> >> >> > data or
> >> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE
> >> >> >> > HY007]. My
> >> >> >> > goal is to have transaction throughout the day not a mix bag of
> >> >> >> > transaction
> >> >> >> > log and differential. Is there a way to resolve this issue or
> >> >> >> > how it can be
> >> >> >> > prevented? Thanks!
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>|||I agree. I have a vague recollection that presence of indexes can affect whether a load will be
minimally logged or not. So perhaps a change in the index scheme of the table caused this...?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eQ8rGgD1GHA.1292@.TK2MSFTNGP03.phx.gbl...
> BCP does not constitute a Minimally logged load which is the issue here. It is a Bulk Load but a
> Bulk load will still be fully logged unless you have certain conditions set. I can't remember what
> this is under in BOL 7.0 but it should be under BCP - xxx or Minimally logged load and will
> explain all the conditions you need. So it is quite possible that you only recently did something
> to meet all of the conditions.
> --
> Andrew J. Kelly SQL MVP
> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
>> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
>> last 3 weeks even though we have queries with select into or BCP running. Is
>> there any configuration in version 7.0 that can be track or set? I do run
>> profile from time to time, but all processes that perform select into or BCP
>> are the same since last 2 years. That is the mystery and there is no
>> specific pattern that I can pinpoint so in order I can figure a work around.
>> I understand your explanation & concept, but I am still not clear why last 3
>> weeks but not any time before that.
>> "Tibor Karaszi" wrote:
>> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
>> operations. This is your problem.
>> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does.
>> In
>> 2000, running in full recovery means that these potentially minimally logged operations will be
>> fully logged. But in earlier versions, some of these operations operation aren't allowed unless
>> you
>> set "select into...". You can start by setting this to off and see if somebody screams. Or try
>> to
>> track down the operation some other way and see what can be done about it.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
>> > Three options are set to check for database options: select into/bulk copy,
>> > auto update statistics, auto create statistics. Because I like to have
>> > transaction running, therefore, I have truncate log on checkpoint turn off.
>> > My understand this is the setting so that transaction log can occur. Is
>> > there any other settings or patch I should take a look at? Thanks!
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> The recovery model concept was introduced in 2000. So you should check the settings for below
>> >> database options:
>> >>
>> >> truncate log on checkpoint
>> >> select into/bulkcopy
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
>> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
>> >> > processes running for last 2 years. I can see the BCP process but why it
>> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
>> >> > oftenly, but it does not help to resolve the issue.
>> >> >
>> >> > Due to a lot of different reasons, we are not able to upgrade to later
>> >> > version of SQL Server yet, but planning is in progress. Is there a way to
>> >> > fix this?
>> >> >
>> >> > Thanks to both Andrew & Tibor.
>> >> >
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has
>> >> >> been
>> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
>> >> >> better
>> >> >> to
>> >> >> keep track of whether the db has been in simple mode and will provide a more useful error
>> >> >> message
>> >> >> if
>> >> >> such is the case.
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
>> >> >> > often latetly. In order to have a continuous backup, I have to perform
>> >> >> > differential in between which is not part of the backup plan. The error I
>> >> >> > received when the transaction log failed is "the mdf file has been subjects
>> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
>> >> >> > log and differential. Is there a way to resolve this issue or how it can be
>> >> >> > prevented? Thanks!
>> >> >>
>> >> >>
>> >>
>> >>
>>
>|||The index has not change in the database for a while, at least 2 months. But
I do have a schedule of reindex, DBCC REINDEX, running nightly. Would this
cause this issue. Would select into statements cause the issue?
Thanks!
"Tibor Karaszi" wrote:
> I agree. I have a vague recollection that presence of indexes can affect whether a load will be
> minimally logged or not. So perhaps a change in the index scheme of the table caused this...?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eQ8rGgD1GHA.1292@.TK2MSFTNGP03.phx.gbl...
> > BCP does not constitute a Minimally logged load which is the issue here. It is a Bulk Load but a
> > Bulk load will still be fully logged unless you have certain conditions set. I can't remember what
> > this is under in BOL 7.0 but it should be under BCP - xxx or Minimally logged load and will
> > explain all the conditions you need. So it is quite possible that you only recently did something
> > to meet all of the conditions.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> > "KTN" <KTN@.discussions.microsoft.com> wrote in message
> > news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
> >> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
> >> last 3 weeks even though we have queries with select into or BCP running. Is
> >> there any configuration in version 7.0 that can be track or set? I do run
> >> profile from time to time, but all processes that perform select into or BCP
> >> are the same since last 2 years. That is the mystery and there is no
> >> specific pattern that I can pinpoint so in order I can figure a work around.
> >> I understand your explanation & concept, but I am still not clear why last 3
> >> weeks but not any time before that.
> >>
> >> "Tibor Karaszi" wrote:
> >>
> >> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
> >> operations. This is your problem.
> >>
> >> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later does.
> >> In
> >> 2000, running in full recovery means that these potentially minimally logged operations will be
> >> fully logged. But in earlier versions, some of these operations operation aren't allowed unless
> >> you
> >> set "select into...". You can start by setting this to off and see if somebody screams. Or try
> >> to
> >> track down the operation some other way and see what can be done about it.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
> >> > Three options are set to check for database options: select into/bulk copy,
> >> > auto update statistics, auto create statistics. Because I like to have
> >> > transaction running, therefore, I have truncate log on checkpoint turn off.
> >> > My understand this is the setting so that transaction log can occur. Is
> >> > there any other settings or patch I should take a look at? Thanks!
> >> >
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> The recovery model concept was introduced in 2000. So you should check the settings for below
> >> >> database options:
> >> >>
> >> >> truncate log on checkpoint
> >> >> select into/bulkcopy
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
> >> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
> >> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
> >> >> > processes running for last 2 years. I can see the BCP process but why it
> >> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
> >> >> > oftenly, but it does not help to resolve the issue.
> >> >> >
> >> >> > Due to a lot of different reasons, we are not able to upgrade to later
> >> >> > version of SQL Server yet, but planning is in progress. Is there a way to
> >> >> > fix this?
> >> >> >
> >> >> > Thanks to both Andrew & Tibor.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Tibor Karaszi" wrote:
> >> >> >
> >> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has
> >> >> >> been
> >> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005 is
> >> >> >> better
> >> >> >> to
> >> >> >> keep track of whether the db has been in simple mode and will provide a more useful error
> >> >> >> message
> >> >> >> if
> >> >> >> such is the case.
> >> >> >>
> >> >> >> --
> >> >> >> Tibor Karaszi, SQL Server MVP
> >> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> >> http://www.solidqualitylearning.com/
> >> >> >>
> >> >> >>
> >> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
> >> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
> >> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
> >> >> >> > often latetly. In order to have a continuous backup, I have to perform
> >> >> >> > differential in between which is not part of the backup plan. The error I
> >> >> >> > received when the transaction log failed is "the mdf file has been subjects
> >> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
> >> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
> >> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
> >> >> >> > log and differential. Is there a way to resolve this issue or how it can be
> >> >> >> > prevented? Thanks!
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
> >
> >
>|||I don't recall whether DBCC DBREINDEX will break the log sequence chain in 7.0. It is possible.
SELECT INTO will, I know that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"KTN" <KTN@.discussions.microsoft.com> wrote in message
news:2C657683-8090-4857-BCAD-9D4857CF32A8@.microsoft.com...
> The index has not change in the database for a while, at least 2 months. But
> I do have a schedule of reindex, DBCC REINDEX, running nightly. Would this
> cause this issue. Would select into statements cause the issue?
> Thanks!
> "Tibor Karaszi" wrote:
>> I agree. I have a vague recollection that presence of indexes can affect whether a load will be
>> minimally logged or not. So perhaps a change in the index scheme of the table caused this...?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eQ8rGgD1GHA.1292@.TK2MSFTNGP03.phx.gbl...
>> > BCP does not constitute a Minimally logged load which is the issue here. It is a Bulk Load but
>> > a
>> > Bulk load will still be fully logged unless you have certain conditions set. I can't remember
>> > what
>> > this is under in BOL 7.0 but it should be under BCP - xxx or Minimally logged load and will
>> > explain all the conditions you need. So it is quite possible that you only recently did
>> > something
>> > to meet all of the conditions.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> >
>> > "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> > news:F33F372F-9971-44F3-A341-C449107F7D53@.microsoft.com...
>> >> Thanks Tibor. I am still puzzle why it occurs a lot lately but not before
>> >> last 3 weeks even though we have queries with select into or BCP running. Is
>> >> there any configuration in version 7.0 that can be track or set? I do run
>> >> profile from time to time, but all processes that perform select into or BCP
>> >> are the same since last 2 years. That is the mystery and there is no
>> >> specific pattern that I can pinpoint so in order I can figure a work around.
>> >> I understand your explanation & concept, but I am still not clear why last 3
>> >> weeks but not any time before that.
>> >>
>> >> "Tibor Karaszi" wrote:
>> >>
>> >> Since you have "select into/bulkcopy" turned on, you allow non-logged (or minimally logged)
>> >> operations. This is your problem.
>> >>
>> >> 7.0 and earlier versions didn't handle non-logged operations as neatly as 2000 and later
>> >> does.
>> >> In
>> >> 2000, running in full recovery means that these potentially minimally logged operations will
>> >> be
>> >> fully logged. But in earlier versions, some of these operations operation aren't allowed
>> >> unless
>> >> you
>> >> set "select into...". You can start by setting this to off and see if somebody screams. Or
>> >> try
>> >> to
>> >> track down the operation some other way and see what can be done about it.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> news:51525F9D-7420-45EE-B025-707EEE393471@.microsoft.com...
>> >> > Three options are set to check for database options: select into/bulk copy,
>> >> > auto update statistics, auto create statistics. Because I like to have
>> >> > transaction running, therefore, I have truncate log on checkpoint turn off.
>> >> > My understand this is the setting so that transaction log can occur. Is
>> >> > there any other settings or patch I should take a look at? Thanks!
>> >> >
>> >> >
>> >> > "Tibor Karaszi" wrote:
>> >> >
>> >> >> The recovery model concept was introduced in 2000. So you should check the settings for
>> >> >> below
>> >> >> database options:
>> >> >>
>> >> >> truncate log on checkpoint
>> >> >> select into/bulkcopy
>> >> >>
>> >> >> --
>> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> http://www.solidqualitylearning.com/
>> >> >>
>> >> >>
>> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> news:BDB2A08A-5FBD-4277-A8B2-3DADA07E7FBB@.microsoft.com...
>> >> >> > It is version 7.0 Enterprise Ed (Windows 2000 AD) and the database is set to
>> >> >> > FULL. The transaction log ran correctly before last 3 weeks and we have BCP
>> >> >> > processes running for last 2 years. I can see the BCP process but why it
>> >> >> > occurred since last 2 weeks is a puzzle to me. I also issue a check point
>> >> >> > oftenly, but it does not help to resolve the issue.
>> >> >> >
>> >> >> > Due to a lot of different reasons, we are not able to upgrade to later
>> >> >> > version of SQL Server yet, but planning is in progress. Is there a way to
>> >> >> > fix this?
>> >> >> >
>> >> >> > Thanks to both Andrew & Tibor.
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> > "Tibor Karaszi" wrote:
>> >> >> >
>> >> >> >> What version of SQL Server? I'd run a Profiler to catch what non-logged operations has
>> >> >> >> been
>> >> >> >> performed in your database. The reason I ask for version is that AFAIK, SQL Server 2005
>> >> >> >> is
>> >> >> >> better
>> >> >> >> to
>> >> >> >> keep track of whether the db has been in simple mode and will provide a more useful
>> >> >> >> error
>> >> >> >> message
>> >> >> >> if
>> >> >> >> such is the case.
>> >> >> >>
>> >> >> >> --
>> >> >> >> Tibor Karaszi, SQL Server MVP
>> >> >> >> http://www.karaszi.com/sqlserver/default.asp
>> >> >> >> http://www.solidqualitylearning.com/
>> >> >> >>
>> >> >> >>
>> >> >> >> "KTN" <KTN@.discussions.microsoft.com> wrote in message
>> >> >> >> news:3F043BF1-D65B-4452-B791-9831D35225AA@.microsoft.com...
>> >> >> >> >I have a backup schedule to do transaction log every hour and it fails quite
>> >> >> >> > often latetly. In order to have a continuous backup, I have to perform
>> >> >> >> > differential in between which is not part of the backup plan. The error I
>> >> >> >> > received when the transaction log failed is "the mdf file has been subjects
>> >> >> >> > to nonlogged updates and cannot be rolled forward. Perform a full data or
>> >> >> >> > differential backup. [SQLStATE 4200] [Error 4213]...[SQLSTATE HY007]. My
>> >> >> >> > goal is to have transaction throughout the day not a mix bag of transaction
>> >> >> >> > log and differential. Is there a way to resolve this issue or how it can be
>> >> >> >> > prevented? Thanks!
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >
>> >
>>

Backup in MSDE

Hello, I need to to a create a SQL Server 2000 job to perform a backup for a
client. They're running Small Business Server 2003. I went on the machine
and I don't see Enterprise Manager. I see that there is a SQL Server
directory with mdf and ldf files. Does this mean that ther're running MSDE,
if so how can you create jobs to run backups if you don't have Enterprise
Manager?
Thanks
Hi, Don
You can create jobs using sp_add_job, sp_add_job_step,
sp_add_job_schedule and sp_add_job_server. You can execute a T-SQL
script using OSQL. See Books Online for more informations. It may be
easier to see how Enterprise Manager calls these procedures, using
Profiler (on another computer with the full SQL Server installed).
Razvan

Backup in MSDE

Hello, I need to to a create a SQL Server 2000 job to perform a backup for a
client. They're running Small Business Server 2003. I went on the machine
and I don't see Enterprise Manager. I see that there is a SQL Server
directory with mdf and ldf files. Does this mean that ther're running MSDE,
if so how can you create jobs to run backups if you don't have Enterprise
Manager?
ThanksHi, Don
You can create jobs using sp_add_job, sp_add_job_step,
sp_add_job_schedule and sp_add_job_server. You can execute a T-SQL
script using OSQL. See Books Online for more informations. It may be
easier to see how Enterprise Manager calls these procedures, using
Profiler (on another computer with the full SQL Server installed).
Razvan

Backup in MSDE

Hello, I need to to a create a SQL Server 2000 job to perform a backup for a
client. They're running Small Business Server 2003. I went on the machine
and I don't see Enterprise Manager. I see that there is a SQL Server
directory with mdf and ldf files. Does this mean that ther're running MSDE,
if so how can you create jobs to run backups if you don't have Enterprise
Manager?
ThanksHi, Don
You can create jobs using sp_add_job, sp_add_job_step,
sp_add_job_schedule and sp_add_job_server. You can execute a T-SQL
script using OSQL. See Books Online for more informations. It may be
easier to see how Enterprise Manager calls these procedures, using
Profiler (on another computer with the full SQL Server installed).
Razvan

Tuesday, March 27, 2012

Backup SQL to both TAPE AND DISK

Thursday, March 8, 2012

Backup passw

Backup passw

Backup passw

Friday, February 24, 2012

Backup Maintenence plan question

Sunday, February 19, 2012

Backup log with no_log

Monday, February 13, 2012

Backup Job Failing

Sunday, February 12, 2012

Backup issue: Transaction & Differential

Backup in MSDE

Backup in MSDE

Backup in MSDE

sql backup files

Blog Archive

About Me