Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 at 18:58

4 Answers 4

up vote 2 down vote accepted

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 live database in the middle of working hours. Probably a good diea 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 dont 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: http://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx

share|improve this answer
    
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. –  Sean Gallardy Jul 1 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. –  Kris Gruttemeyer Jul 1 at 18:56
    
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. –  Sean Gallardy Jul 1 at 19:01

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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