Solved

Questions about backups on a production system - Full with or without Log backups

Posted on 2009-07-15
8
158 Views
Last Modified: 2012-05-07
Hi, I'm working with a shop that doesn't have a DBA admin person (well, it's me for the moment but I'm a rookie) and need to get some stuff under control as fast as possible.  First is transaction logs are killing us.  SQL Server is SQL 2005.

I have some questions about controlling the transaction log space and trimming it back to where it ought to be.

The first step is a Backup.  The production databases are in "Full" mode, unless they are mostly read-only (such as config and lookup tables).  I'm really only concerned with the ones that are "Full" and hold production data.

So, I do a Full Backup.  

Q1. Does the full backup ALSO include the log?  Or do I immediately need to do a Log backup to get truncation?

Q2.  Once I do a full backup, how do I reduce the size of the TLog again?

Those two will do for this question.  I'll have another question on Employing Full Backups on a Weekly basis, Differentials on a Daily, and Log backups.  But I need the answer to these two (immediately if not sooner :> )

Thanks all,
-greg
0
Comment
Question by:OuttaCyTE
  • 4
  • 2
  • 2
8 Comments
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 500 total points
ID: 24865558
>> Q1. Does the full backup ALSO include the log?  Or do I immediately need to do a Log backup to get truncation?

Yes.. It includes all the logs except for the uncommitted transactions.

>> Q2.  Once I do a full backup, how do I reduce the size of the TLog again?

Since Transaction log file have grown to a huge size as you mentioned earlier, you need to shrink the log file to some pre-grown value. Pre-grown value refers to growing the Log File size to some huge value so that Files dont require frequent file increase condition.

Steps to follow now:

1. Take a Full Backup now.
2. Execute this command

BACKUP LOG ur_dbname WITH TRUNCATE_ONLY;
DBCC SHRINKFILE('ur_dbname', 5000);

kindly note 5000 refers to a pre-grown value so that it can accomodate for a week changes. And kindly change it to a value based upon your requirements.

3. Take a Full Backup again as DBCC SHRINKFILE affects the Backup chain.
4. Schedule a Maintenance task for Full backup Weekly as mentioned earlier.
5. Schedule a Maintenance task for Differential backup Daily as mentioned earlier.
6. Schedule a Maintenance task for Transactional Log backup 6 times daily as mentioned earlier. (Kindly note 6 times means 4 hours once and hence for point in time restore your risk of data lost may be 4 hrs in worst case). Hence configure accordingly as per your requirement.

Hope this helps.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24865588
It is worth noting that once you do the shrink operation, and start doing regular log backups, you'll not need to perform the shrink operation again.
0
 

Author Comment

by:OuttaCyTE
ID: 24865678
rrjegan17,

Thank you very much.  I just couldn't seem to get that clear no matter where I looked.

Is the Shrinkfile against the database or the log, or both?  

And extra thanks for the bit about taking another full backup, I would not have thought of that.

While I'm at it, and want to clean things up, Should I do a Index Rebuild and update statistics after the shrink?

Chapmandew,  Thank you.  
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24865695
>> Is the Shrinkfile against the database or the log, or both?  

Never do it on your database files.
Since your Log files have grown to a huge extent, just run it once.
Dont include this as part of your Maintenance plan as it would break the backup chain. ( chapmandew stressed this one earlier)

>> And extra thanks for the bit about taking another full backup, I would not have thought of that.

Since DBCC SHRINKFILE breaks the Backup chain, its advised to have a full Backup after you perform the shrink operation.

Hope this helps.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24865711
>> While I'm at it, and want to clean things up, Should I do a Index Rebuild and update statistics after the shrink?

Its usual practice to perform a Index Rebuild after your Full Backup and make sure you do it only on Heavily fragmented tables. Do Reorganize on tables that are not heavily fragmented.

script to identify list of tables with Fragmentation:

http://blog.sqlauthority.com/2008/03/27/sql-server-2005-find-index-fragmentation-details-slow-index-performance/

Update Statistics should be done one/ two days once as a separate task.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24865749
>>Since DBCC SHRINKFILE breaks the Backup chain, its advised to have a full Backup after you perform the shrink operation.
rrjegan17, I have to correct you.   DBCC SHRINKFILE doesn't break the backup chain.  BACKUP LOG WITH TRUNCATE_ONLY does, but not a shrink operation.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24866276
chapmandew,
     Typo..
I meant Step No 2, BACKUP LOG and DBCC SHRINKFILE breaks up the Backup chain.

Thanks for correcting and explaining it clearly
0
 

Accepted Solution

by:
OuttaCyTE earned 0 total points
ID: 25051031
I have finally managed to get them shrunk, but I had problems with it.

I did a full backup and tried to do the shrink and it would not do it.  I am pretty sure I tried the truncate log as well, with no luck.  It just refused.

After much research, I find that a RecoveryMode Full database seems to have this issue a lot.  So I did the following to shrink the things.

I did a full backup.
I did a log backup.
I changed the recovery mode to Simple.
Shrink-ed the log
Changed the recover mode back to Full.
I did a full backup.
Deleted the first full backup and the log backup (I'm space constrained) Since I now had a good full backup after the shrink.

The only windows of exposure here are:
    1)  If I were to crash while in simple mode, I would only be able to recover up to the poing of the full + log backup.  This was not a problem as I did it at 2 am and nobody was on.
    2) If for some reason, somebody decided that I needed to do a restore to a point that was in the window between my log backup and my next full backup I'd be out of luck.  Again, nobody was on, activity was quiescent and the Likelihood was miniscule.

Sad to say there doesn't seem to be a real shrink function that will work on a RecoveryMode Full database?!  Why can't the operation simply move the danged log blocks to the front of the file and chop off what isn't needed.  Why force me to this hoop-de-do ?  Sheesh!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now