September 29, 2010 at 12:19 pm
Hi,
In our development server, we have dev,QA and Staging databases. We use this databases to to connect to dev website to testing purpose.
So these databases log files are growing like anything(GB), which is causing space issues in server.
We are taking only full backups of these server.
My question
It is dev server , we don’t need transactionlogs in this server. Can I change recovery model to simple for those databases. Any impact on application conncectivity?
Thank you.
September 29, 2010 at 12:31 pm
Changing recovery model does not terminate any connections to the database.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 29, 2010 at 12:33 pm
http://msdn.microsoft.com/en-us/library/ms190203(v=SQL.90).aspx
In case you want to read on it.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
September 29, 2010 at 2:00 pm
Hi,
After Switching to the Simple Recovery Model
If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, you back up the log immediately before switching, which allows you to recover the database up to that point. After switching, you need to take periodic data backups to protect your data and to truncate the inactive portion of the transaction log.
If u are Back again
After Switching from the Simple Recovery Model
Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
Schedule regular log backups and update your restore plan accordingly.
Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
My advice is go with Full Recovery since there is no point in time recovey for simple recovery model
Check this also
http://msdn.microsoft.com/en-us/library/ms175987.aspx
Thanks
Parthi
Thanks
Parthi
September 29, 2010 at 2:28 pm
I use Simple for dev/qa databases because there really is no need for point in time recovery.
Rule of thumb is if you do not need PIT recovery, and you are OK with losing up to one day's worth of data (assuming you take a full backup every night), there is no need to keep the DB on full.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply