Locks and Blocks: February 2012

Friday, February 24, 2012

Log Backup Job works, but reports failure.

All the transaction logs are created, but the Application log entry below
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
>
|||Here's the explanation!
http://support.microsoft.com/default...&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>

Log Backup Job works, but reports failure.

All the transaction logs are created, but the Application log entry below
appears, and the job shows as having failed.
Any ideas, anyone?
Thanks,
Jim
--
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Date: 8/2/2005
Time: 9:47:24 AM
User: N/A
Computer: GENOME-MARS
Description:
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) - Status:
Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job failed. The Job
was invoked by User UAB\jmoon. The last step to run was step 1 (Step 1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
----
--Start Enterprise Mangler. Drill down to Management | Database Maintenance
Plans. Right click and select "Maintenance Plan History...". Set the
filters to isolate the plan that failed. Select the entry for the plan and
the failure date, click the "Properties..." button to see the details of why
it failed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Jim" <please.reply@.group> wrote in message
news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
> All the transaction logs are created, but the Application log entry below
> appears, and the job shows as having failed.
> Any ideas, anyone?
> Thanks,
> Jim
> --
> Event Type: Warning
> Event Source: SQLSERVERAGENT
> Event Category: Job Engine
> Event ID: 208
> Date: 8/2/2005
> Time: 9:47:24 AM
> User: N/A
> Computer: GENOME-MARS
> Description:
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
> Plan 'DB Maintenance Plan 1'' (0xAF8C5DECB6A71142915AA55ED294E0B4) -
> Status: Failed - Invoked on: 2005-08-02 09:07:42 - Message: The job
> failed. The Job was invoked by User UAB\jmoon. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> ----
--
>|||Here's the explanation!
http://support.microsoft.com/defaul...2&Product=sql2k
Simple recovery model is the culprit--can't do transaction log backups...
Jim
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:u%23LtDp4lFHA.2628@.tk2msftngp13.phx.gbl...
> Start Enterprise Mangler. Drill down to Management | Database Maintenance
> Plans. Right click and select "Maintenance Plan History...". Set the
> filters to isolate the plan that failed. Select the entry for the plan
> and the failure date, click the "Properties..." button to see the details
> of why it failed.
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Jim" <please.reply@.group> wrote in message
> news:upRvnl4lFHA.3656@.TK2MSFTNGP09.phx.gbl...
>

LOG Backup in he presence of CHECKDB issues...

Friends,
Based on information I got during a conversation with MS PSS, I now have the
impression that a LOG backup performed on a DB that has serious CHECKDB
troubles should most likely NOT be restored. (Here, "serious" implies
allow_data_loss as the minimum CHECKDB repair level.)
Our customer made a full backup of a clean happy system. During the course
of the week, part of the database became corrupt. Nobody discovered this
until days after a late-night CHECKDB first reported the serious troubles.
I guessed that perhaps we could restore the full backup, apply the log
backups, and end up "clean". But, "Bob" from MS says that a LOG backup
would most likely have captured the corruption and the corruption would be
present upon LOG restore. I can certainly understand a full backup
capturing corruption, but was surprised that a LOG backup would (or might)
also.
Is "Bob" correct? For now I believe him, but it's nice to get other
opinions. Depending on the "truth", we might need to encourage our
customers to run DBCC CHECKDB far more often, and might need to recommend
automating the monitoring of any problems. Its sounds like any CHECKDB
troubles must be detected and dealt with immediately to minimize the
possibility of actual data loss, since RESTORE might be the only safe option
for recovery.
Any thoughts or links on this topic will be greatly appreciated! Any
thoughts or links on why this corruption might occur would be great too!
Thanks,
James Hunter RossBob is right. A Log backup will capture corruption just like a Full backup
will. Yes, it's best to capture ASAP to avoid lost data. I runn DBCC CheckDB
nightly.
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have
> the impression that a LOG backup performed on a DB that has serious
> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
> implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the
> course of the week, part of the database became corrupt. Nobody
> discovered this until days after a late-night CHECKDB first reported the
> serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log
> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
> would most likely have captured the corruption and the corruption would be
> present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might)
> also.
> Is "Bob" correct? For now I believe him, but it's nice to get other
> opinions. Depending on the "truth", we might need to encourage our
> customers to run DBCC CHECKDB far more often, and might need to recommend
> automating the monitoring of any problems. Its sounds like any CHECKDB
> troubles must be detected and dealt with immediately to minimize the
> possibility of actual data loss, since RESTORE might be the only safe
> option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any
> thoughts or links on why this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Will it always be the case? The log file will "only" capture data changes
that SQL is aware of. I'd imagine that if you have a corruption in your
database that comes from e.g. a disk problem, it could happen that this is
only in your database and not in the log. In that case you can restore the
Database backup and the logs and then you'll be ok again.
Regards
Steen
ChrisR wrote:
> Bob is right. A Log backup will capture corruption just like a Full
> backup will. Yes, it's best to capture ASAP to avoid lost data. I
> runn DBCC CheckDB nightly.
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> Based on information I got during a conversation with MS PSS, I now
>> have the impression that a LOG backup performed on a DB that has
>> serious CHECKDB troubles should most likely NOT be restored. (Here,
>> "serious" implies allow_data_loss as the minimum CHECKDB repair
>> level.) Our customer made a full backup of a clean happy system. During
>> the
>> course of the week, part of the database became corrupt. Nobody
>> discovered this until days after a late-night CHECKDB first reported
>> the serious troubles.
>> I guessed that perhaps we could restore the full backup, apply the
>> log backups, and end up "clean". But, "Bob" from MS says that a LOG
>> backup would most likely have captured the corruption and the
>> corruption would be present upon LOG restore. I can certainly
>> understand a full backup capturing corruption, but was surprised
>> that a LOG backup would (or might) also.
>> Is "Bob" correct? For now I believe him, but it's nice to get other
>> opinions. Depending on the "truth", we might need to encourage our
>> customers to run DBCC CHECKDB far more often, and might need to
>> recommend automating the monitoring of any problems. Its sounds
>> like any CHECKDB troubles must be detected and dealt with
>> immediately to minimize the possibility of actual data loss, since
>> RESTORE might be the only safe option for recovery.
>> Any thoughts or links on this topic will be greatly appreciated! Any
>> thoughts or links on why this corruption might occur would be great
>> too! Thanks,
>> James Hunter Ross|||I doubt Bob's statement. Imagine you have this entry in the tlog "add a row to this table with these
values for the columns ...". After three hours, the data page where this row ended up is corrupted
because of hw problems. The log record (for the moment in the ldf file) is still clean. You now do a
log backup, and the log record is still clean - now in the tlog backup file. You now restore from
the clean db backup and then restore the clean log record. I fail to see how the corruption would be
introduced!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
> Friends,
> Based on information I got during a conversation with MS PSS, I now have the impression that a LOG
> backup performed on a DB that has serious CHECKDB troubles should most likely NOT be restored.
> (Here, "serious" implies allow_data_loss as the minimum CHECKDB repair level.)
> Our customer made a full backup of a clean happy system. During the course of the week, part of
> the database became corrupt. Nobody discovered this until days after a late-night CHECKDB first
> reported the serious troubles.
> I guessed that perhaps we could restore the full backup, apply the log backups, and end up
> "clean". But, "Bob" from MS says that a LOG backup would most likely have captured the corruption
> and the corruption would be present upon LOG restore. I can certainly understand a full backup
> capturing corruption, but was surprised that a LOG backup would (or might) also.
> Is "Bob" correct? For now I believe him, but it's nice to get other opinions. Depending on the
> "truth", we might need to encourage our customers to run DBCC CHECKDB far more often, and might
> need to recommend automating the monitoring of any problems. Its sounds like any CHECKDB troubles
> must be detected and dealt with immediately to minimize the possibility of actual data loss, since
> RESTORE might be the only safe option for recovery.
> Any thoughts or links on this topic will be greatly appreciated! Any thoughts or links on why
> this corruption might occur would be great too!
> Thanks,
> James Hunter Ross
>
>|||Bob is absolutely correct.
Even though what you say below Tibor is also correct, the odds are that the
corrupt data have been incorporated into the txn log in some way (through a
modification to it, through modifications to corrupt page headers and so on)
after several days of operation.
You may be lucky and there's no corruption propagated in the log, but in the
vast majority of cases I've ever seen, luck does not hold after an interval
of many days.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ON$262ckFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I doubt Bob's statement. Imagine you have this entry in the tlog "add a row
>to this table with these values for the columns ...". After three hours,
>the data page where this row ended up is corrupted because of hw problems.
>The log record (for the moment in the ldf file) is still clean. You now do
>a log backup, and the log record is still clean - now in the tlog backup
>file. You now restore from the clean db backup and then restore the clean
>log record. I fail to see how the corruption would be introduced!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "James Hunter Ross" <james.ross@.oneilsoft.com> wrote in message
> news:OIIBa6VkFHA.2720@.TK2MSFTNGP10.phx.gbl...
>> Friends,
>> Based on information I got during a conversation with MS PSS, I now have
>> the impression that a LOG backup performed on a DB that has serious
>> CHECKDB troubles should most likely NOT be restored. (Here, "serious"
>> implies allow_data_loss as the minimum CHECKDB repair level.)
>> Our customer made a full backup of a clean happy system. During the
>> course of the week, part of the database became corrupt. Nobody
>> discovered this until days after a late-night CHECKDB first reported the
>> serious troubles.
>> I guessed that perhaps we could restore the full backup, apply the log
>> backups, and end up "clean". But, "Bob" from MS says that a LOG backup
>> would most likely have captured the corruption and the corruption would
>> be present upon LOG restore. I can certainly understand a full backup
>> capturing corruption, but was surprised that a LOG backup would (or
>> might) also.
>> Is "Bob" correct? For now I believe him, but it's nice to get other
>> opinions. Depending on the "truth", we might need to encourage our
>> customers to run DBCC CHECKDB far more often, and might need to recommend
>> automating the monitoring of any problems. Its sounds like any CHECKDB
>> troubles must be detected and dealt with immediately to minimize the
>> possibility of actual data loss, since RESTORE might be the only safe
>> option for recovery.
>> Any thoughts or links on this topic will be greatly appreciated! Any
>> thoughts or links on why this corruption might occur would be great too!
>> Thanks,
>> James Hunter Ross
>>
>|||Thank you all! It's sure nice to hear from a SQL Server development team
member too!
We will certainly modify our "best practices" recommendations to include
DBCC CHECKDB at least nightly, with a requisite monitoring of the results.
The corruption is rare, but "rare" is the whole reason we have a recovery
plan recommendation for our customers. Thanks again!
James

Log Backup General question

Do Log Backups read from the data file or log file or both ? I always
thought that log backups read data from log file but as I was thinking, I
believe it reads from the data file... am i right ?A log backup has interaction with both the data file and the log file.
Most of the information stored in the backup file is from the log file but
it can also get data from the data file. For instance if you had some type
of minimally logged load the information in the log file is only which
extents were affected by the load. When the log backup occurs it must read
the entire extent from the database to put into the log backup.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> Do Log Backups read from the data file or log file or both ? I always
> thought that log backups read data from log file but as I was thinking, I
> believe it reads from the data file... am i right ?
>|||So does the log backup contain all the extents ? I always had the
misconception that the log file only contain insert/update and delete
statements only and was always wondering what happens when create
tables/create index/Dbcc shrinkfile/Dbcc dbreindex occur and what the Tlog
file contains and also what the log backup would contain. I would appreciate
if you can in brief tell me what occurs.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23IXoo3%23fDHA.2248@.TK2MSFTNGP09.phx.gbl...
> A log backup has interaction with both the data file and the log file.
> Most of the information stored in the backup file is from the log file but
> it can also get data from the data file. For instance if you had some
type
> of minimally logged load the information in the log file is only which
> extents were affected by the load. When the log backup occurs it must
read
> the entire extent from the database to put into the log backup.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> > Do Log Backups read from the data file or log file or both ? I always
> > thought that log backups read data from log file but as I was thinking,
I
> > believe it reads from the data file... am i right ?
> >
> >
>|||I don't know the actual format the log uses and quite frankly I don't care
since it does the job. But it will include any DDL and DML that is needed
to correctly redo or roll back the said transaction. In some cases this is
the full DML with the data involved such as an INSERT or UPDATE with all the
values. In the case of a TRUNCATE Table it just logs the Truncate command
as that can be replayed as is. When it comes to Extents this is usually
only logged when there is a Bulklogged operation like I mentioned or
something that happens to affect the entire extent. Otherwise it gets the
data from the DML statement. If you issued an update or Delete statement
that happened to affect all the rows ina page it would still log them on a
row by row basis into the log. Log entries are sequential and it doesn't
know if the actions your taking on a row by row basis will eventually affect
the whole page or even extent. But something like a minimally logged Bulk
Insert is a different story and it will log the extent that has been touched
(not the data). When you back up the log it will then grab that extents
data. As for the other row by row operations, it already has the data it
needs. I suggest (again<g>) that you get a hold of Inside SQL Server 2000.
I am pretty sure it covers most if not all of this.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:u2uGT7%23fDHA.3284@.tk2msftngp13.phx.gbl...
> So does the log backup contain all the extents ? I always had the
> misconception that the log file only contain insert/update and delete
> statements only and was always wondering what happens when create
> tables/create index/Dbcc shrinkfile/Dbcc dbreindex occur and what the Tlog
> file contains and also what the log backup would contain. I would
appreciate
> if you can in brief tell me what occurs.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23IXoo3%23fDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > A log backup has interaction with both the data file and the log file.
> > Most of the information stored in the backup file is from the log file
but
> > it can also get data from the data file. For instance if you had some
> type
> > of minimally logged load the information in the log file is only which
> > extents were affected by the load. When the log backup occurs it must
> read
> > the entire extent from the database to put into the log backup.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> > > Do Log Backups read from the data file or log file or both ? I always
> > > thought that log backups read data from log file but as I was
thinking,
> I
> > > believe it reads from the data file... am i right ?
> > >
> > >
> >
> >
>|||> I suggest (again<g>) that you get a hold of Inside SQL Server 2000.
> I am pretty sure it covers most if not all of this.
Or, for gory details about transaction handling, logging, locking etc, "Transaction Processing
Concepts and Techniques" by Gray and Reuter. Some 1000 pages rather heavy reading but not SQL Server
specific, though.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uGRtVREgDHA.556@.TK2MSFTNGP11.phx.gbl...
> I don't know the actual format the log uses and quite frankly I don't care
> since it does the job. But it will include any DDL and DML that is needed
> to correctly redo or roll back the said transaction. In some cases this is
> the full DML with the data involved such as an INSERT or UPDATE with all the
> values. In the case of a TRUNCATE Table it just logs the Truncate command
> as that can be replayed as is. When it comes to Extents this is usually
> only logged when there is a Bulklogged operation like I mentioned or
> something that happens to affect the entire extent. Otherwise it gets the
> data from the DML statement. If you issued an update or Delete statement
> that happened to affect all the rows ina page it would still log them on a
> row by row basis into the log. Log entries are sequential and it doesn't
> know if the actions your taking on a row by row basis will eventually affect
> the whole page or even extent. But something like a minimally logged Bulk
> Insert is a different story and it will log the extent that has been touched
> (not the data). When you back up the log it will then grab that extents
> data. As for the other row by row operations, it already has the data it
> needs. I suggest (again<g>) that you get a hold of Inside SQL Server 2000.
> I am pretty sure it covers most if not all of this.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:u2uGT7%23fDHA.3284@.tk2msftngp13.phx.gbl...
> > So does the log backup contain all the extents ? I always had the
> > misconception that the log file only contain insert/update and delete
> > statements only and was always wondering what happens when create
> > tables/create index/Dbcc shrinkfile/Dbcc dbreindex occur and what the Tlog
> > file contains and also what the log backup would contain. I would
> appreciate
> > if you can in brief tell me what occurs.
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23IXoo3%23fDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > > A log backup has interaction with both the data file and the log file.
> > > Most of the information stored in the backup file is from the log file
> but
> > > it can also get data from the data file. For instance if you had some
> > type
> > > of minimally logged load the information in the log file is only which
> > > extents were affected by the load. When the log backup occurs it must
> > read
> > > the entire extent from the database to put into the log backup.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:eDOurz%23fDHA.908@.tk2msftngp13.phx.gbl...
> > > > Do Log Backups read from the data file or log file or both ? I always
> > > > thought that log backups read data from log file but as I was
> thinking,
> > I
> > > > believe it reads from the data file... am i right ?
> > > >
> > > >
> > >
> > >
> >
> >
>

Log backup files - appending

Quick question regarding log backup files.

Currently I have a maintenance plan running a Full backup weekly, differential backups nightly, and log backups hourly. The log backups are all going into a single backup file - but it's hard to see what's going on behind the scenes here.

Does this file get 'reset' when the full backup is performed? Will it just keep growing indefinitely and should I be creating new files for each log backup, or manually deleting the file each week during the full backup task?

Thanks

If there's only a single file then all the backups will be appended to that file, even when a full backup is taken. I would change your maintenance task so that each full backup starts a new file. This makes the backups more manageable. Don't delete the previous week's backup file straight away though - keep it around for a week or two just in case you need it.

A few other things I'd recommend if you're not already doing them:

keep the backup files on a different set of drives than the databases

add a step to periodically restore the backups to make sure they're valid - we like to say that you don't have a backup until you know you can restore it

practice doing a disaster recovery from them - point in time restores

Log backup does not truncate the log file

Hi,
My db uses full recovery model. I used maintenance wizard to perform:
1) Index rebuild every night
2) Full backup once a week
3) Log backup every night
4) Integrity check every night
The log file is 1GB now and because of regular index rebuild I expected this
growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
row 240 which is 2.
My problem is that the log backup does not free the space to OS. I checked
shrink file GUI in EM and it says the minimum log file can be 23MB! So why
the log backup does not free the space?
Thanks in advance,
LeilaLeila wrote:
> Hi,
> My db uses full recovery model. I used maintenance wizard to perform:
> 1) Index rebuild every night
> 2) Full backup once a week
> 3) Log backup every night
> 4) Integrity check every night
> The log file is 1GB now and because of regular index rebuild I expected this
> growth. DBCC LOGINFO displays 243 VLFs that status of all are 0 except the
> row 240 which is 2.
> My problem is that the log backup does not free the space to OS. I checked
> shrink file GUI in EM and it says the minimum log file can be 23MB! So why
> the log backup does not free the space?
> Thanks in advance,
> Leila
>
To clear unused virtual files you can try:
DBCC SHRINKFILE(<database name>_log,<size in MB>)
for more details (and if that does not work) see:
http://support.microsoft.com/kb/272318/|||I know this command, but based on explanation in BOL, the BACKUP LOG must
truncate the log file automatically. Is it true or I have misunderstood
this?
Thanks!
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
> To clear unused virtual files you can try:
> DBCC SHRINKFILE(<database name>_log,<size in MB>)
> for more details (and if that does not work) see:
> http://support.microsoft.com/kb/272318/|||Leila wrote:
> I know this command, but based on explanation in BOL, the BACKUP LOG must
> truncate the log file automatically. Is it true or I have misunderstood
> this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>
The backup only marks unused VLFs as deleted but does not remove them.
The shrink does.|||You mean the shrink does not break the log chain?
"Zero One" <efes_echad@.hotmail.com> wrote in message
news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Leila wrote:
>> I know this command, but based on explanation in BOL, the BACKUP LOG must
>> truncate the log file automatically. Is it true or I have misunderstood
>> this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>>
> The backup only marks unused VLFs as deleted but does not remove them. The
> shrink does.|||Leila wrote:
> You mean the shrink does not break the log chain?
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:OyIUXbUJHHA.4376@.TK2MSFTNGP03.phx.gbl...
>> Leila wrote:
>> I know this command, but based on explanation in BOL, the BACKUP LOG must
>> truncate the log file automatically. Is it true or I have misunderstood
>> this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>> The backup only marks unused VLFs as deleted but does not remove them. The
>> shrink does.
>
Quoting from the aforementioned KB:
"Shrinking the log in SQL Server 2000 is no longer a deferred operation."
The shrink only removes unused VLFs that where invalidated during the
backup. You can only break the log chain by invalidating VLFs WITHOUT
backing them up and then shrinking. For example (from the KB):
BACKUP LOG pubs WITH TRUNCATE_ONLY
GO
DBCC SHRINKFILE(pubs_log,2)
If you use TRUNCATE_ONLY you break the log chain and have to do a full
backup. Otherwise it should be safe (as far as I understand).|||Leila
TRUNCATE is a logical operation, not a physical one. The fact that DBCC
LOGINFO indicates all 0's means that the log has been truncated, and the
VLFs are now reusable. To reduce the physical size of the operating system
file, you have to DBCC SHRINKFILE.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>|||"Leila" <Leilas@.hotpop.com> wrote in message
news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>I know this command, but based on explanation in BOL, the BACKUP LOG must
>truncate the log file automatically. Is it true or I have misunderstood
>this?
You misunderstood.
Truncate != shrink.
And generally you do NOT want to shrink the log file if it's just going to
grow again. This can lead to performance issues (as it expands each time)
and disk level fragmentation.
> Thanks!
>
> "Zero One" <efes_echad@.hotmail.com> wrote in message
> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>|||Thanks every body for clarifications :-)
BTW, I cannot browse your blog Kalen!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ueaCxRVJHHA.3916@.TK2MSFTNGP02.phx.gbl...
> Leila
> TRUNCATE is a logical operation, not a physical one. The fact that DBCC
> LOGINFO indicates all 0's means that the log has been truncated, and the
> VLFs are now reusable. To reduce the physical size of the operating system
> file, you have to DBCC SHRINKFILE.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23CWp7WUJHHA.420@.TK2MSFTNGP06.phx.gbl...
>>I know this command, but based on explanation in BOL, the BACKUP LOG must
>>truncate the log file automatically. Is it true or I have misunderstood
>>this?
>> Thanks!
>>
>> "Zero One" <efes_echad@.hotmail.com> wrote in message
>> news:eF735SUJHHA.4112@.TK2MSFTNGP04.phx.gbl...
>> Leila wrote:
>> Hi,
>> My db uses full recovery model. I used maintenance wizard to perform:
>> 1) Index rebuild every night
>> 2) Full backup once a week
>> 3) Log backup every night
>> 4) Integrity check every night
>> The log file is 1GB now and because of regular index rebuild I expected
>> this growth. DBCC LOGINFO displays 243 VLFs that status of all are 0
>> except the row 240 which is 2.
>> My problem is that the log backup does not free the space to OS. I
>> checked shrink file GUI in EM and it says the minimum log file can be
>> 23MB! So why the log backup does not free the space?
>> Thanks in advance,
>> Leila
>> To clear unused virtual files you can try:
>> DBCC SHRINKFILE(<database name>_log,<size in MB>)
>> for more details (and if that does not work) see:
>> http://support.microsoft.com/kb/272318/
>>
>|||What do you mean?
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:OuDzdfZJHHA.1240@.TK2MSFTNGP03.phx.gbl...
> Thanks every body for clarifications :-)
> BTW, I cannot browse your blog Kalen!

Log backup does not truncate the log file

Log backup chain

Hi all, BOL states
Although the transaction log may be truncated manually, it is strongly
recommended that you do not do this, as it breaks the log backup chain. Until
a full database backup is created, the database is not protected from media
failure. Use manual log truncation only in very special circumstances, and
create a full database backup as soon as practical.
Case:
A daily full backup of a db occurs at 7 p.m. and tran log backup every
15min, and a job shrinks the log file (DBCC SHRINKFILE) daily at 6 a.m. What
should happen if I try to restore the database to a point of failure after 6
a.m. (say 11.45 a.m.)? Can I still continue restoring my tran log backups up
11:45 or everthing from 6 a.m onwards is lost?
I thought that truncating the log file manually would break the log backup
chain because inactive part of the log will be released. However, when I
tried to do so all seemed to be fine (no error messages). Surely, I
misunderstood something. Can anyone explain what "it breaks the log backup
chain" actually means?Hi,
If u studied the BOL then u should come to know that as u will do the
back up of the database transaction log automatically truncates and
flushes out the unused space.
When we execute any DML command sql writes it in transaction log and
issues a checkpoint and when we do backup the sql server clears all old
transaction records except the active portion of the transaction log.
So it will not harm u .u can do the point in time recovery.
U are doing transaction log backup every 15 min so suppose u had backup
at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
PM and finishes in few seconds.
So u have the backup till 7 PM and when u will run the truncate command
it will clear the logfile before 7 pm and u have the tlog backup after
7 pm.
So, now u have tlog back every 15 mins.In case u have a crash then u
have to apply the tlog after the restore of last backup back of the
database and then restore all the tlog after 7 PM.
Aslo study virtual logfile and truncate logfile from BOL
If u use the command with Tuncate_only it will shrink the logfile on
disk
No it will not break the chain because as u will do the backup of
database database issue a checkpoint and stores a entry in it self so
when u apply the tlog it will take the LSN after the backup.
Hope it helps u.
from
Killer.|||Thanks doller for your time. But what if dbcc shrinkfile was executed between
tlob backups. e.g
Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
I understand what after tran log backup, inactive part of the log gets
removed and we don't need to run the shrinkfile command. But I am looking at
the server that has this configs (?) . I have tried to restore the data,
wanting to see if it will cause any problems and it did not.
"doller" wrote:
> Hi,
> If u studied the BOL then u should come to know that as u will do the
> back up of the database transaction log automatically truncates and
> flushes out the unused space.
> When we execute any DML command sql writes it in transaction log and
> issues a checkpoint and when we do backup the sql server clears all old
> transaction records except the active portion of the transaction log.
> So it will not harm u .u can do the point in time recovery.
> U are doing transaction log backup every 15 min so suppose u had backup
> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> PM and finishes in few seconds.
> So u have the backup till 7 PM and when u will run the truncate command
> it will clear the logfile before 7 pm and u have the tlog backup after
> 7 pm.
> So, now u have tlog back every 15 mins.In case u have a crash then u
> have to apply the tlog after the restore of last backup back of the
> database and then restore all the tlog after 7 PM.
> Aslo study virtual logfile and truncate logfile from BOL
> If u use the command with Tuncate_only it will shrink the logfile on
> disk
> No it will not break the chain because as u will do the backup of
> database database issue a checkpoint and stores a entry in it self so
> when u apply the tlog it will take the LSN after the backup.
>
> Hope it helps u.
> from
> Killer.
>|||Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> Thanks doller for your time. But what if dbcc shrinkfile was executed between
> tlob backups. e.g
> Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> I understand what after tran log backup, inactive part of the log gets
> removed and we don't need to run the shrinkfile command. But I am looking at
> the server that has this configs (?) . I have tried to restore the data,
> wanting to see if it will cause any problems and it did not.
>
> "doller" wrote:
>> Hi,
>> If u studied the BOL then u should come to know that as u will do the
>> back up of the database transaction log automatically truncates and
>> flushes out the unused space.
>> When we execute any DML command sql writes it in transaction log and
>> issues a checkpoint and when we do backup the sql server clears all old
>> transaction records except the active portion of the transaction log.
>> So it will not harm u .u can do the point in time recovery.
>> U are doing transaction log backup every 15 min so suppose u had backup
>> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> PM and finishes in few seconds.
>> So u have the backup till 7 PM and when u will run the truncate command
>> it will clear the logfile before 7 pm and u have the tlog backup after
>> 7 pm.
>> So, now u have tlog back every 15 mins.In case u have a crash then u
>> have to apply the tlog after the restore of last backup back of the
>> database and then restore all the tlog after 7 PM.
>> Aslo study virtual logfile and truncate logfile from BOL
>> If u use the command with Tuncate_only it will shrink the logfile on
>> disk
>> No it will not break the chain because as u will do the backup of
>> database database issue a checkpoint and stores a entry in it self so
>> when u apply the tlog it will take the LSN after the backup.
>>
>> Hope it helps u.
>> from
>> Killer.
>>|||Thanx Tibor, I have visited your site many times before but did see this
article. I follow now. That's why somitimes when you do
"dbcc shrinkfile" and the size of the tlog is not reduced we need to run
"backup log xxx with truncate_only" and than dbcc shrinkfile.
Thanx again.
"Tibor Karaszi" wrote:
> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any of
> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit on
> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
> > tlob backups. e.g
> >
> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
> >
> > I understand what after tran log backup, inactive part of the log gets
> > removed and we don't need to run the shrinkfile command. But I am looking at
> > the server that has this configs (?) . I have tried to restore the data,
> > wanting to see if it will cause any problems and it did not.
> >
> >
> > "doller" wrote:
> >
> >> Hi,
> >> If u studied the BOL then u should come to know that as u will do the
> >> back up of the database transaction log automatically truncates and
> >> flushes out the unused space.
> >> When we execute any DML command sql writes it in transaction log and
> >> issues a checkpoint and when we do backup the sql server clears all old
> >> transaction records except the active portion of the transaction log.
> >> So it will not harm u .u can do the point in time recovery.
> >> U are doing transaction log backup every 15 min so suppose u had backup
> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
> >> PM and finishes in few seconds.
> >>
> >> So u have the backup till 7 PM and when u will run the truncate command
> >> it will clear the logfile before 7 pm and u have the tlog backup after
> >> 7 pm.
> >> So, now u have tlog back every 15 mins.In case u have a crash then u
> >> have to apply the tlog after the restore of last backup back of the
> >> database and then restore all the tlog after 7 PM.
> >>
> >> Aslo study virtual logfile and truncate logfile from BOL
> >>
> >> If u use the command with Tuncate_only it will shrink the logfile on
> >> disk
> >> No it will not break the chain because as u will do the backup of
> >> database database issue a checkpoint and stores a entry in it self so
> >> when u apply the tlog it will take the LSN after the backup.
> >>
> >>
> >>
> >> Hope it helps u.
> >> from
> >> Killer.
> >>
> >>
>|||> That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
Exactly. It's all in understanding how the virtual log files work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ruski" <Ruski@.discussions.microsoft.com> wrote in message
news:362F54BB-0F65-404D-BE62-F35BCCB9249A@.microsoft.com...
> Thanx Tibor, I have visited your site many times before but did see this
> article. I follow now. That's why somitimes when you do
> "dbcc shrinkfile" and the size of the tlog is not reduced we need to run
> "backup log xxx with truncate_only" and than dbcc shrinkfile.
> Thanx again.
> "Tibor Karaszi" wrote:
>> Shrinkfile doesn't empty the log. It will just shrink the physical file size without removing any
>> of
>> the data inside the file. I.e., it doesn't break a log backup chain. You might want to read a bit
>> on
>> the subject at: http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ruski" <Ruski@.discussions.microsoft.com> wrote in message
>> news:4864B3EC-0126-4946-A660-D7F5D376CB1A@.microsoft.com...
>> > Thanks doller for your time. But what if dbcc shrinkfile was executed between
>> > tlob backups. e.g
>> >
>> > Full Bk (7 p.m.) -> .... -> Tlog Bk (8 p.m.) -> Tlog Bk (8:15 p.m.) -> DBCC
>> > SHRINKFILE (8:20 p.m.) -> Tlog Bk (8:30 p.m.)
>> >
>> > I understand what after tran log backup, inactive part of the log gets
>> > removed and we don't need to run the shrinkfile command. But I am looking at
>> > the server that has this configs (?) . I have tried to restore the data,
>> > wanting to see if it will cause any problems and it did not.
>> >
>> >
>> > "doller" wrote:
>> >
>> >> Hi,
>> >> If u studied the BOL then u should come to know that as u will do the
>> >> back up of the database transaction log automatically truncates and
>> >> flushes out the unused space.
>> >> When we execute any DML command sql writes it in transaction log and
>> >> issues a checkpoint and when we do backup the sql server clears all old
>> >> transaction records except the active portion of the transaction log.
>> >> So it will not harm u .u can do the point in time recovery.
>> >> U are doing transaction log backup every 15 min so suppose u had backup
>> >> at 6 PM and finishes at 7 PM and ur truncate log file job runs at 7.01
>> >> PM and finishes in few seconds.
>> >>
>> >> So u have the backup till 7 PM and when u will run the truncate command
>> >> it will clear the logfile before 7 pm and u have the tlog backup after
>> >> 7 pm.
>> >> So, now u have tlog back every 15 mins.In case u have a crash then u
>> >> have to apply the tlog after the restore of last backup back of the
>> >> database and then restore all the tlog after 7 PM.
>> >>
>> >> Aslo study virtual logfile and truncate logfile from BOL
>> >>
>> >> If u use the command with Tuncate_only it will shrink the logfile on
>> >> disk
>> >> No it will not break the chain because as u will do the backup of
>> >> database database issue a checkpoint and stores a entry in it self so
>> >> when u apply the tlog it will take the LSN after the backup.
>> >>
>> >>
>> >>
>> >> Hope it helps u.
>> >> from
>> >> Killer.
>> >>
>> >>
>>

LOG Backup and Remote Recovery - Msg 4330

Hi,

We have remote offices running SQL 2000, which are on a small bandwidth pipeline, so we implemented an incremental backup schema where they back up their LOGs overnight and send it to our main office. After an initial full backup, they just keep sending **incremental** LOG backups.

In the main office we restore the full backup of each remote office and from then on do a restore of each night's LOG file (with standby option), so that we keep in sync with them. The issue we have now is that we cannot get regular full backups from the remote (and sometimes independent) offices on a regular basis - maybe only once a *quarter*.

If we use differential backups, the size of each one grows each day, until we get a full backup. Incremental backups thru LOGs seemed to be the only way to go. *HOWEVER*, we now have multiple LOG files sitting on our server in the main office and since the databases that we restored to is in warm-standby and read-only mode, we cannot make a backup of them in-house, without breaking the LOG restore chain.

That also means that we would not be able to easily re-create the environment locally, should something go wrong - we would have to apply all of the LOG restores for (potentially) the whole quarter!

I even tried to create two restored databases - one where I keep it in standby state and ready to receive more incremental LOG backups and another where I would take it out of standby mode (and into recovery mode). At that point I am then able to make a full backup locally of this second database. *BUT*, when I then try to restore the next day's LOG backup to this second database, it fails with an error

- Msg 4330 - Level 16, State 4, Line 1
- The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.

How do I do incremental backups and restores, while maintaining the ability to make a new full backup of the restored database, before the restore sequence is done?

Help !

You can restore the full backup with standby option and subsequent tlogs using with standby option so that the db will be in read-only mode..........

OR you can also restore the full backup and Tlog using with norecovery mode (i.e leave db nonoperational but apply tlogs)

you need to use any of the above 2 options to perform log shipping...........i think you are using the option "Leave db operational so that no tlogs could be applied" or the log backups are not being restored sequentially

Thanxx

Deepak

Log Backup

Hi,
I read in a book that when a database becomes suspected, the first thing to
do is backing up the log file. I tried to simulate suspecting but I could
not backup the log file( I deleted a NDF file):
Server: Msg 945, Level 14, State 2, Line 3
Database 'Northwind' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
What's the solution if I want to keep my data in such situations?
Thanks in advance,
Leila
Have a look at this:
http://support.microsoft.com/kb/253817/EN-US/
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I read in a book that when a database becomes suspected, the first thing
> to
> do is backing up the log file. I tried to simulate suspecting but I could
> not backup the log file( I deleted a NDF file):
> --
> Server: Msg 945, Level 14, State 2, Line 3
> Database 'Northwind' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> Server: Msg 3013, Level 16, State 1, Line 3
> BACKUP LOG is terminating abnormally.
> --
> What's the solution if I want to keep my data in such situations?
> Thanks in advance,
> Leila
>
|||Thanks Andrew,
But I cannot rename the log file of suspected database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMta6q$tFHA.3628@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Have a look at this:
> http://support.microsoft.com/kb/253817/EN-US/
>
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
could
>

Log Backup

Hi,
I read in a book that when a database becomes suspected, the first thing to
do is backing up the log file. I tried to simulate suspecting but I could
not backup the log file( I deleted a NDF file):
--
Server: Msg 945, Level 14, State 2, Line 3
Database 'Northwind' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
--
What's the solution if I want to keep my data in such situations?
Thanks in advance,
LeilaHave a look at this:
http://support.microsoft.com/kb/253817/EN-US/
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I read in a book that when a database becomes suspected, the first thing
> to
> do is backing up the log file. I tried to simulate suspecting but I could
> not backup the log file( I deleted a NDF file):
> --
> Server: Msg 945, Level 14, State 2, Line 3
> Database 'Northwind' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> Server: Msg 3013, Level 16, State 1, Line 3
> BACKUP LOG is terminating abnormally.
> --
> What's the solution if I want to keep my data in such situations?
> Thanks in advance,
> Leila
>|||Thanks Andrew,
But I cannot rename the log file of suspected database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMta6q$tFHA.3628@.TK2MSFTNGP14.phx.gbl...
> Have a look at this:
> http://support.microsoft.com/kb/253817/EN-US/
>
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I read in a book that when a database becomes suspected, the first thing
> > to
> > do is backing up the log file. I tried to simulate suspecting but I
could
> > not backup the log file( I deleted a NDF file):
> > --
> > Server: Msg 945, Level 14, State 2, Line 3
> > Database 'Northwind' cannot be opened due to inaccessible files or
> > insufficient memory or disk space. See the SQL Server errorlog for
> > details.
> > Server: Msg 3013, Level 16, State 1, Line 3
> > BACKUP LOG is terminating abnormally.
> > --
> > What's the solution if I want to keep my data in such situations?
> > Thanks in advance,
> > Leila
> >
> >
>

Log Backup

Hi,
I read in a book that when a database becomes suspected, the first thing to
do is backing up the log file. I tried to simulate suspecting but I could
not backup the log file( I deleted a NDF file):
--
Server: Msg 945, Level 14, State 2, Line 3
Database 'Northwind' cannot be opened due to inaccessible files or
insufficient memory or disk space. See the SQL Server errorlog for details.
Server: Msg 3013, Level 16, State 1, Line 3
BACKUP LOG is terminating abnormally.
--
What's the solution if I want to keep my data in such situations?
Thanks in advance,
LeilaHave a look at this:
http://support.microsoft.com/kb/253817/EN-US/
Andrew J. Kelly SQL MVP
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I read in a book that when a database becomes suspected, the first thing
> to
> do is backing up the log file. I tried to simulate suspecting but I could
> not backup the log file( I deleted a NDF file):
> --
> Server: Msg 945, Level 14, State 2, Line 3
> Database 'Northwind' cannot be opened due to inaccessible files or
> insufficient memory or disk space. See the SQL Server errorlog for
> details.
> Server: Msg 3013, Level 16, State 1, Line 3
> BACKUP LOG is terminating abnormally.
> --
> What's the solution if I want to keep my data in such situations?
> Thanks in advance,
> Leila
>|||Thanks Andrew,
But I cannot rename the log file of suspected database.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eMta6q$tFHA.3628@.TK2MSFTNGP14.phx.gbl...
> Have a look at this:
> http://support.microsoft.com/kb/253817/EN-US/
>
> --
> Andrew J. Kelly SQL MVP
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23UQdQr7tFHA.2160@.TK2MSFTNGP10.phx.gbl...
could[vbcol=seagreen]
>

Log and DB backups without writing infomsgs to error log?

I was wondering if anyone knows of a way that you can have your backups not
write info messages to the sql server error log in SQL SERVER 2k5. We have
500 databases per instance of sql server and when we run hourly tran and
nightly full's we end up with 24*500 = 12000 rows of data that I don't need
to see in my error logs every night. As you can see with that nightly
volume of log data it is a nightmare when you have to go searching through
the logs. I've written a script that parses the results of the undocumented
proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
however it's a memory hog when it goes through a week's worth of log data (I
see excessive waits in the mssql_xp type when it runs). I ideally would
like to be able to parse through the logs every 15 minutes or so and alert
back through e-mail to the DBA's when it sees something concerning. Can I
set up db alerts to do this? If not I guess the only other option I have is
writing a standalone external app that can parse the error log and email me
when it sees concerning information. Has anyone else encountered this
situation? Any help/insight would be appreciated.
-Abair
There's a trace flag...
http://sqlblog.com/blogs/andrew_kelly/archive/2007/10/29/successful-backup-messages-no-more.aspx
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Michael Abair" <mabair@.autotask.com> wrote in message
news:OUZx$lKSIHA.3316@.TK2MSFTNGP02.phx.gbl...
>I was wondering if anyone knows of a way that you can have your backups not
>write info messages to the sql server error log in SQL SERVER 2k5. We have
>500 databases per instance of sql server and when we run hourly tran and
>nightly full's we end up with 24*500 = 12000 rows of data that I don't need
>to see in my error logs every night. As you can see with that nightly
>volume of log data it is a nightmare when you have to go searching through
>the logs. I've written a script that parses the results of the
>undocumented proc 'sp_readerrorlog' and e-mails our DBA team any pertinent
>info it finds, however it's a memory hog when it goes through a week's
>worth of log data (I see excessive waits in the mssql_xp type when it
>runs). I ideally would like to be able to parse through the logs every 15
>minutes or so and alert back through e-mail to the DBA's when it sees
>something concerning. Can I set up db alerts to do this? If not I guess
>the only other option I have is writing a standalone external app that can
>parse the error log and email me when it sees concerning information. Has
>anyone else encountered this situation? Any help/insight would be
>appreciated.
> -Abair
>
|||It is interesting that I have seen some Oracle DBAs in the past doing just
that: parsing the logs to find errors and send alerts.
In SQL Server that is the job of the SQL Server Agent and you can define
Alerts to do the same job.
Anyway, in my opinion, even with alerts set up, it is always a good idea to
look at the error log once in a while.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Michael Abair" wrote:

> I was wondering if anyone knows of a way that you can have your backups not
> write info messages to the sql server error log in SQL SERVER 2k5. We have
> 500 databases per instance of sql server and when we run hourly tran and
> nightly full's we end up with 24*500 = 12000 rows of data that I don't need
> to see in my error logs every night. As you can see with that nightly
> volume of log data it is a nightmare when you have to go searching through
> the logs. I've written a script that parses the results of the undocumented
> proc 'sp_readerrorlog' and e-mails our DBA team any pertinent info it finds,
> however it's a memory hog when it goes through a week's worth of log data (I
> see excessive waits in the mssql_xp type when it runs). I ideally would
> like to be able to parse through the logs every 15 minutes or so and alert
> back through e-mail to the DBA's when it sees something concerning. Can I
> set up db alerts to do this? If not I guess the only other option I have is
> writing a standalone external app that can parse the error log and email me
> when it sees concerning information. Has anyone else encountered this
> situation? Any help/insight would be appreciated.
> -Abair
>
>
|||> Anyway, in my opinion, even with alerts set up, it is always a good idea to
> look at the error log once in a while.
This statement seems to validate the 'Oracle DBA' approach. I mean, you
can't possibly just manually 'look at the error log once in a while' unless
you only have one, two, or a very small number of servers. Even that, it's
boring to look at these errorlog files manually. It's much better to let a
program do the borign work for you.
BTW, that is not an Oracle DBA approach, but is a generally effective
practice in my opinion.
Linchi
"Ben Nevarez" wrote:
[vbcol=seagreen]
> It is interesting that I have seen some Oracle DBAs in the past doing just
> that: parsing the logs to find errors and send alerts.
> In SQL Server that is the job of the SQL Server Agent and you can define
> Alerts to do the same job.
> Anyway, in my opinion, even with alerts set up, it is always a good idea to
> look at the error log once in a while.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Michael Abair" wrote:

Friday, February 24, 2012

Log Backup Job works, but reports failure.

Log Backup Job works, but reports failure.

LOG Backup in he presence of CHECKDB issues...

LOG Backup in he presence of CHECKDB issues...

LOG Backup in he presence of CHECKDB issues...

Log Backup General question

Log backup files - appending

Log backup does not truncate the log file

Log backup does not truncate the log file

Log backup does not truncate the log file

Log backup chain

LOG Backup and Remote Recovery - Msg 4330

Log Backup

Log Backup

Log Backup

Log and DB backups without writing infomsgs to error log?

Locks and Blocks

Blog Archive

About Me