[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

do we need to take a full backup after  dbcc shrink command on log files

Posted on 2011-10-05
6
Medium Priority
?
404 Views
Last Modified: 2012-06-27
hi all,
do we need to take a full backup on a full recovery model database after we perform a dbcc shrink(logfile) on it?
will dbcc shrinkfile on logfiles upset the log sequence to restore the logbackups frmo the last full backup?

how about when I do truncate on logfiles, do I need to take a full backup after that?
thanks in advance.
0
Comment
Question by:msdba
6 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 36918237
You should perform a full backup after truncating the log file because you have effectively disabled a point in time restore by clearing the log file.  Performing a full backup will assure that you don't lose any data in the event of a disaster.

Greg

0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 36918912
You must do a T-log backup before you can truncate the log. In SQL2005 you can still use the no_log or truncate_only like below::

backup LOG databasename with truncate_only
--or
BACKUP LOG databasename WITH NO_LOG
0
 
LVL 40

Expert Comment

by:lcohan
ID: 36918936
And you must do a FULL backup immediately after that - sorry I missed it from my previous reply.
This way you have a FULL backup to rely on and from that on you should schedule T-log backups to keep your logfile under control. Maybe one FULL backup a day and t-log backups every four hours (or more) depending on your disaster recovery requirements.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 5

Assisted Solution

by:AlokJain0412
AlokJain0412 earned 500 total points
ID: 36919650
Hi
under the full recovery model, first back up the transaction log file. Then, use the DBCC SHRINKFILE statement to shrink the transaction log file.

DBCC SHRINKFILE is used to shrink the /allocated/ space, assuming that
there is available non-used (free) space in the file. The allocated
space is the total size of the file on the file system.

A BACKUP LOG is used to back up, and then truncate the /used/ space that
is within the allocated space. This does not change the size of the
allocated space - it only affects how much of that allocated space is
actually used.

A trans log can be 100 megs in size, and you can be using only a few
megs of actual space in it. You can check this w/
DBCC SQLPERF(LOGSPACE).

the key was to understanding the difference between allocated space and
used space. I was mixing the 2 up.

Hope its useful for u
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 500 total points
ID: 37001645
>> do we need to take a full backup on a full recovery model database after we perform a dbcc shrink(logfile) on it? <<

No.  The SHRINKFILE will only release the unneeded portions of the log, if any.


>> will dbcc shrinkfile on logfiles upset the log sequence to restore the logbackups frmo the last full backup? <<

No.  Same reason.


>> how about when I do truncate on logfiles, do I need to take a full backup after that? <<

Yes, as everyone has noted, assuming you mean a forced truncate, where the log is not properly backed up first.

Note, too, naturally you have killed no capability of forward-recovering your db past the last full/differential backup, since you can't apply the log, as you have destroyed the log contents.

If you are going to truncate the log, you should then pre-allocate a large enough amount of space to meet your log needs until the next truncate.  For example:

ALTER DATABASE <db_name> MODIFY FILE ( NAME = <logical_log_file_name>, 1000 ) --1G

Do NOT let the log grow automatically as a "normal" method.  That is a resource hog and severely damages your performance.  ALL modification activity in the db pauses while the log is being extended (log space must be pre-formatted before being used).  And the log is likely to be very badly fragmented after several autogrowths.

Similarly, set a fairly high autogrowth amount.  The fewer autogrows the better.

Too many small log autogrows can give you too many VLFs, also severely hurting performance.
0
 

Author Closing Comment

by:msdba
ID: 37082875
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question