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

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.
msdbaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JestersGrindCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lcohanDatabase AnalystCommented:
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
lcohanDatabase AnalystCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

AlokJain0412Commented:
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
Scott PletcherSenior DBACommented:
>> 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
msdbaAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.