Sburk Posted September 23, 2008 Report Share Posted September 23, 2008 I have a database that is currently running in Full recovery mode and a SQL log file that is 20GB, which is obviously too large. I want to switch the database from Full recovery mode to Simple recovery mode. What steps should I take through EMC retrospect to prepare for this switch? I need the log to commit the changes to the database. I'm a bit concerned that Retrospect does not do this with a 'Full Database Backup' already. Please let me know. Thanks! Quote Link to comment Share on other sites More sharing options...
Mayoff Posted September 23, 2008 Report Share Posted September 23, 2008 Retrospect uses standard Microsoft commands for the backup of SQL databases. Retrospect sends these commands to the SQL Server, it is then up to the SQL Server to do the "right thing" with those commands. See screenshot. Quote Link to comment Share on other sites More sharing options...
Sburk Posted September 23, 2008 Author Report Share Posted September 23, 2008 I understand that EMC sends commands to the SQL server. However, we are currently in full recovery mode and the Full Backup option does not truncate the log. Do you know if this is by design? If you run a 'Full Backup' directly from the SQL Server agent (not through Retrospect), it would truncate the log. So, I'm assuming that I will need to run a Full Backup and then run a Log Backup against each database in order for the log to truncate and commit changes to the database. Is this a correct assumption or is there a workaround?...or is this flat out not working correctly for me? Thanks! Quote Link to comment Share on other sites More sharing options...
Mayoff Posted September 23, 2008 Report Share Posted September 23, 2008 Retrospect itself does not truncate logs. Retrospect sends the backup command and it is then up to SQL to handle the logs correctly. If the Logs do not truncate then it is due to an error on the SQL side or a limitation of the specific backup API being used by Microsoft while in the recovery mode.. Quote Link to comment Share on other sites More sharing options...
Sburk Posted September 23, 2008 Author Report Share Posted September 23, 2008 So you are saying that the logs SHOULD truncate if I run a Full Backup from Retrospect? I don't know what backup commands retrospect is sending to the sql agent, so I don't know if it's sending a truncate command or not. Quote Link to comment Share on other sites More sharing options...
Mayoff Posted September 24, 2008 Report Share Posted September 24, 2008 I believe full backups normally normally truncate logs but I don't know what SQL does in recovery mode. Once again, Retrospect does not control this, SQL does. Quote Link to comment Share on other sites More sharing options...
emulator Posted October 5, 2008 Report Share Posted October 5, 2008 In my experience: -- Full backup does NOT truncate the logs -- Differential does NOT truncate the logs -- Log backup no truncate does NOT truncate the logs -- Log backup DOES truncate the logs We run regular LOG backups every hour, and any transactions that are completed (and thus written to the database) are truncated after the backup. We run regular FULL backups every 12 hours. This does NOT truncate any log data; it simply backs up the database. Check out this thread: http://forums.dantz.com/showtopic.php?tid/27109/ Quote Link to comment Share on other sites More sharing options...
emulator Posted October 5, 2008 Report Share Posted October 5, 2008 (edited) Here's some more useful information: http://www.sqlservercentral.com/Forums/Topic567010-357-1.aspx#bm567015 Edited October 5, 2008 by Guest Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.