SQL 2005 Shrink issue

#**171**

Joe C (JoeC (AT) discussions (DOT) microsoft.com) writes:

Quote:

I'm running a full database backup nightly, and my recover mode is full
as well in SQL. I'm using Symantec Backup exec 11D to do the backup's.
What I got from Symantec today was that I need to run a FULL back up job
in SBE11D then a Log backup with truncate option so it deletes the logs.
I thought that If I just ran a full back up Job it would truncate the
logs as well. It sounds like I should be using simple restore in SQL
since I only back up nightly,

Most of all, you should use simple backup, if you are content with
recovering from the nightly backup.

Note that if you first truncate the log, and then backup your database,
you can still recover from failures on the MDF files during the day, as
well as from human errors.

If you first back up the database, and then truncate the log, you have
broken the log chain, and whatever you assemble in the transaction log
is useless.

Quote:

but my questions is why isn't the log
files truncating since I'm running a full database back up nightly?

Because you don't backup the transaction log. Backing up the database
backs up the database, nothing more.

And, again and again, truncating the log is a very unusual operation,
as is shrinking a database file.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

#**172**

Joe C (JoeC (AT) discussions (DOT) microsoft.com) writes:

Quote:

I'm running a full database backup nightly, and my recover mode is full
as well in SQL. I'm using Symantec Backup exec 11D to do the backup's.
What I got from Symantec today was that I need to run a FULL back up job
in SBE11D then a Log backup with truncate option so it deletes the logs.
I thought that If I just ran a full back up Job it would truncate the
logs as well. It sounds like I should be using simple restore in SQL
since I only back up nightly,

Most of all, you should use simple backup, if you are content with
recovering from the nightly backup.

Note that if you first truncate the log, and then backup your database,
you can still recover from failures on the MDF files during the day, as
well as from human errors.

If you first back up the database, and then truncate the log, you have
broken the log chain, and whatever you assemble in the transaction log
is useless.

Quote:

but my questions is why isn't the log
files truncating since I'm running a full database back up nightly?

Because you don't backup the transaction log. Backing up the database
backs up the database, nothing more.

And, again and again, truncating the log is a very unusual operation,
as is shrinking a database file.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SQL 2005 Shrink issue

microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools

Re: SQL 2005 Shrink issue - 07-22-2008 , 04:45 PM

Re: SQL 2005 Shrink issue - 07-22-2008 , 04:45 PM