|
|
Posted by Erland Sommarskog on 10/01/24 11:48
DataPro (datapro01@yahoo.com) writes:
> Well SQL Server definetely seems to be a different animal.
>
> Here are some questions a newguy like myself would have about the
> logging.
>
> The documentation says that once a backup log truncate only is done, a
> full backup should be taken as the recovery ability of the system has
> been degraded. Why is that, if that command merely archives committed
> transactions?
Up-to-the point recovery. You take a full backup at midnight, then
you backup the transaction log once an hour. at 10:23 you run an
UPDATE statement, but forget the WHERE clause. At this point you
can back up the transaction log.
You can the restore the full backup and reapply the transaction log
dumps but stop at 10:22, so you can recover the lost data. If you
somewhere along the line has backed up the transaction log with
NO_LOG or TRUNCATE_ONLY, the log chain is broken, and it's not
possible to continue to apply logs.
> A SELECT @@TRANCOUNT yields a count of zero.
> dbcc opentran(eas) says zero also.
> dbcc loginfo yeilds 308 rows marked with a '2' which apparently implies
> that they are open transactions. Why don't they show up in trancount
> then?
>
> If a log backup supposedly makes the log 'reusable' then why would a 12
> gig log grow another Gig right within a few hours of the backup with
> minimal database use?
> Wouldn't most of that space be reusable.
I'm not really sure what you are talking about here. If the log file
grew, apparently there was some action going on in the database.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|