Solved

Changing recovery model from Full to Simple

Posted on 2006-07-12
8
1,154 Views
Last Modified: 2008-01-09
Got a Raiser's Edge database.  Sits on SQL Server 2k.  It installs by default in Simple recovery, and the company doesn't support it going to Full.  But I thought it would be a good idea to get those log backups going so switched it to full and set up full/differential/log backups.  Today got reports that the db was going v slowly.  Discovered that the log file was growing rapidly, presumably the slowness coming from the log recreating itself every time it got 10% bigger.  It's up to 3 gig now.  So I made an extra log backup, and stopped and restarted the server but the log is still at 3 gig.  

Decided all I could do was set the db back to Simple recovery until I can find out what the problem is.  

Questions:

1. The log file is still 3 gig - is that to be expected after going back to Simple?
2. I thought stopping/starting the server emptied the log?  Or did I dream that?
3. Any ideas what could have caused this?
4. Is there anything about going from Full to Simple that could cause any problems (beyond not being able to do log backups)?
0
Comment
Question by:looper8
8 Comments
 
LVL 8

Expert Comment

by:Kobe_Lenjou
Comment Utility
1) Yes
2) It was a dream, SQL server recreates the tempdb database on startup
3) Heavy transactional use and slow disk IO
4) No

Solve this by doing a DBCC shrinkfile or DBCC shrinkdatabase after changing the recovert model to simple
0
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
1.The log file is still 3 gig - is that to be expected after going back to Simple?
Yes. what i usally is to shrink the file after taking the backup
run this statement in sql query analyzer
dump transaction dbname with no_log
go
then shrink the log file


2. I thought stopping/starting the server emptied the log?  Or did I dream that?
no, it will not happen.

3. Any ideas what could have caused this?
heavy transactions

4. Is there anything about going from Full to Simple that could cause any problems (beyond not being able to do log backups)?
it will not log every thing.


When FULL is specified, database backups and transaction log backups are used to provide full recoverability from media failure. All operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.

When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
How much data is being bulk-loaded into the db?  A good compromise might be "BULK_LOGGED" recovery mode.

Another thing is to *pre-allocate* a relatively large amount of space to the log so that it does not have to dynamically expand very often, *because dynamic expansion is a slow process*.

Also, set the increment for the log to a specific amount rather than 10% -- I've seen several cases where that alone sped up expansion a lot (I guess calc'ing the 10% causes slowdowns itself).

So, shrink the current log to, say, 400M with an increment of 100M, like so:
[All commands are from Query Analyzer.]

USE databaseName
EXEC sp_helpfile
--copy/remember the logical log name in the first column:
--    you will need it for the other commands

BACKUP LOG databaseName WITH TRUNCATE_ONLY

DBCC SHRINKFILE ( logical_log_name, 400 )

ALTER DATABASE databaseName
    MODIFY FILE ( NAME = logical_log_name, FILEGROWTH = 100MB )

--optional, if you want to try BULK_LOGGED mode
ALTER DATABASE databaseName
    SET RECOVERY BULK_LOGGED
0
 
LVL 1

Author Comment

by:looper8
Comment Utility
Thanks guys.  Ah yes, now I remember the dream ...

Thanks for the pre-allocation bit Scott, that sounds eminently sensible.  

Not quite sure of the order to proceed however.  Can I go back to full recovery, then take a backup, then truncate the log, then change the initial log size/increment?

I think I need to ask more questions about what was being done at the time, because as far as I know no bulk imports are done.  However if I go back to Full recovery and this situation recurs and it isn't a bulk operation then do I need to truncate the log more frequently?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
You don't need to take a log backup after going back to FULL from SIMPLE; the log has already been automatically truncated by SQL in SIMPLE so there's not much point in backing it up.

If you want to be able to apply logs / forward recover in the future, you will need to take a full db backup after going back to FULL mode.

If you want, you can TRUNCATE the log as often as needed, although doing that prevents doing a forward recovery.
0
 
LVL 1

Author Comment

by:looper8
Comment Utility
Thanks.  Sorry to be asking lots of basic questions, but I need to get this right!

Am I right to say that log truncation is part of normal log backing up?  If so then I shouldn't ever need to truncate the log if I've got log backups going?  

All in all this is what I think I'll do (can you please comment!!) : go back to Full reocvery, set up the Full/Diff/Log backups again (perhaps with more frequent log backups), check out if Bulk-Logged is ever necessary, shrink the log file, set the log file allocated space to larger than normal (say 500MB) with a growth of say 100MB.

Many thanks.
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
>> Am I right to say that log truncation is part of normal log backing up? <<

Yes; normally after backing up the log SQL truncates it.


>> If so then I shouldn't ever need to truncate the log if I've got log backups going? <<

In theory, yes; in practice, no.  SQL doesn't always function as it should, especially after large inserts.


The rest seems like a good approach.  However, you will still need to periodically check the total log space allocated.  You could set up a job to check this if you wanted to.
0
 
LVL 1

Author Comment

by:looper8
Comment Utility
Thanks Scott.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now