7

I have an old database - a users membership/role that was setup automatically by an ASP.Net 2 application years ago:

ss1

The Sql Server version currently running is: Sql Server 10.5.1617

The users database log file is huge (the ldf file is approx 400 times the size of the mdf file).

The recovery model is currently set to "Full". I understand what that is - and I don't need point in time restoration.

If I simply changed the recovery model to "Simple" from within Sql Server Management Studio:

ss2

...and clicked ok to save the changes - would I be risking my current database in any way? Or is Sql Server fine with making changes like this to live databases? And would the log file automatically shrink itself?

Thanks for your advice,

Mark

1
  • 1
    How did it get into Full Recovery mode? It looks like you're not doing backups of the transaction log file so whoever set this up skipped a critical step!
    – Ram
    Jul 1, 2014 at 18:58

4 Answers 4

8

I would do this when the database is in low activity (end of day, overnight, first thing in the morning). I would NEVER recommend making changes like this to a live database in the middle of working hours. It's probably a good idea to change the database to read-only or make sure no users/applications are hitting it.

  • Run a full backup of the database/log file in question, while still in full recovery mode. This will give you a starting point in case anything gets borked and you can just restore it.

  • Switch to SIMPLE recovery, I don't like using the GUI, I'd script it out.

Here's a great article from MSDN about things to consider when changing your backup model: Considerations for Switching from the Full or Bulk-Logged Recovery Model.

3
  • 2
    It's perfectly fine making recovery model changes during business hours if that's acceptable to the business and does not require a tight SLA. You're only losing recoverability aspects which he already said he was ok with. I would not switch this to read only for any reason, that would be an interruption of service where changing the recovery model would not. Jul 1, 2014 at 18:55
  • Like I said, I would rather be safe. Without any knowledge of SLAs or anything, we can't know what his business rules are. Always err on the side of caution, in my opinion. Jul 1, 2014 at 18:56
  • 1
    I took this as him saying he knows - "I understand what that is - and I don't need point in time restoration." and was fine with it. Jul 1, 2014 at 19:01
5

When you switch to simple recovery, the log will be truncated when a CHECKPOINT occurs (as a background task - automatic or manually running a CHECKPOINT) on the database.

It simply means that the log portion will be reused by sql server instead of growing to keep a record of all transactions (just like FULL recovery until you take log backup).

This does not mean that you will be reclaiming disk space back.

For reclaiming disk space, you have to shrink the log file.

DO this is in a less/quiet time when there is minimal user activity

use master

go

--- first take a FULL backup

backup database db_name to disk = 'D:\backups\db_name_FULL.bak' with stats =10, compression, init

go

use db_name

go

---- manually issue a checkpoint
checkpoint

go
checkpoint -- run twice so the log file wraps around

go

----- now shrink the log file to reclaim disk space

dbcc shrinkfile(db_name_LOG,xxMB)  -- xxMB is the size that you want.
go

** Make sure that you dont size the log file too small as it will eventually have to grow. Its very expensive for sql server to fire AUTO-GROWTH events.

3

You can certainly change a database to SIMPLE recovery model. The 'risk' is that you might need point in time restores, but you have said that is not a requirement.

The huge log file is likely because the transaction log is not being backed up.

If you switch to SIMPLE mode you will have to shrink the file. Like this:

USE DatabaseName;
CHECKPOINT; 
DBCC SHRINKFILE(LogFileName, size of log);

Repeat if necessary, to get the file shrunk to a reasonable size.

Then think if you really want a FULL recovery model. If so, set up regular log backups and the log file will remain in control. Do a new FULL backup and then the LOG backups should run just fine.

2

You can change this at any time from full to simple. After making the change manually run checkpoint for the databases, then you can begin shrinking the transaction log.

This does require a short lock on the database while the change is made or as long as you don't have a ton of users on the system it's fine to do at any time. Otherwise wait for after hours.

Don't forget to remove it from your log backup jobs to stop job failures.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.