Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Perform Maintenance with SQL Server Databases in Full Recovery mode


By:   |   Last Updated: 2006-12-12   |   Comments (4)   |   Related Tips: More > Maintenance

Problem

When I have performed database maintenance in the past the transaction log backups were huge and I was unable to restore the transaction logs.  This caused more problems for me so I have stopped performing SQL Server maintenance.  My SQL Server performance is now an issue, so I am looking for a process to be able to perform database maintenance for my databases in full recovery mode during a pre-defined maintenance window.

Solution

Performing regularly scheduled maintenance is critical for high performance and ensuring the users will have a positive experience with your applications.  The challenge with performing database maintenance for databases in full recovery mode is the excessively large transaction log backups as compared to databases in simple recovery mode.  The excessive size is due to the full recovery mode  retaining all of the before and after records.  With a great deal of maintenance the probable cause for not being able to restore the transaction logs is an incorrect pointer reference.

The process below to perform database maintenance during a pre-defined maintenance window should be tested in a development environment for databases in full recovery mode prior to executing in production:

Next Steps


Last Updated: 2006-12-12


next webcast button


next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, July 05, 2012 - 9:55:07 AM - Jeremy Kadlec Back To Top

Sreeni,

I would add a top clause to the logic you use and only rebuild\reorganize a hand full of tables per day so you do not break log shipping, replication, database mirroring, etc.  I would start off with 5 tables\indexes and see the impact.  Then scale it up to 10 or 15 tables and reassess.

Hopefully you will find a magic number to maintain your indexes.

The recommendation in this tip is for a different scenario, so sorry if this caused any confusion.

HTH.

Thank you,
Jeremy Kadlec


Wednesday, July 04, 2012 - 6:01:46 PM - Sreeni Back To Top

Jeremy,

I am running index reorg and index rebuild maintenance based on the fragmentation level (<30 reorg and > 30 rebuild). We are on WIndows 2008 R2 Ent and SQL 2008 R2 Ent. The database is Trans Replicated Mirrored and Log Shipped. I dont have any option except to go for full recovery model.

When maintenance run it generates huge log which breaks LS and huge latency on replication and Mirroring.

Appreciate any comments.

 

Thanks,
Sreeni

 


Tuesday, July 03, 2012 - 6:05:19 PM - Jeremy Kadlec Back To Top

Sreeni,

Exactly what SQL Server database maintenance are you running?  What error message(s) are you getting?  What version of SQL Server and Windows are you running?

Kindly let me know and I may be able to offer a few suggestions.

Thank you,
Jeremy Kadlec


Tuesday, July 03, 2012 - 1:52:45 PM - Sreeni Back To Top

Jeremy,

My Database is in Full recovery mode and is around 500 GB size. I am not able to perform maintenance on it as its Mirrored, Logshipped and replicated. All breaks if I perform any maintenance on anything.

 

Appreciate your reply.

 

Thanks,
Sreeni

 


Learn more about SQL Server tools