Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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

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
OuttaCyTE
Asked:
OuttaCyTE
  • 4
  • 2
  • 2
2 Solutions
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
chapmandewCommented:
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
 
OuttaCyTEAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
chapmandewCommented:
>>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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
OuttaCyTEAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now