Switching recovery models
-
I am looking to change the recovery model of a number of our SQL Server databases from FULL to Simple as we do not currently backup the transaction logs ! (I'm looking at resolving this problem).
It says on the Microsoft web site to:
Action: Optionally back up the transaction log prior to the change
Description: Executing a log backup immediately before the change permits recovery to that point. After switching to the simple model, stop executing log backups.
As we don't currently backup the Tran logs I was thinking it would be best to do a FULL Database backup and then switch from FULL to simple?
Thanks in advance
Carl
-
To perform PIT(point in time)-recovery we perform log-backups before every other type of backup.
If you are not interested in PIT-recovery, you can fall back to other ways :
- make frequent full backup
- make a scenario of Full and Differential backups
In your case, I guess just making the full-backup will be sufficient because you are no longer interested in all that has been going on before.
Then switch to simple.
Double check your SLA's and DRP's before doing so, so you are sure you can meet users service aggrements.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Once I have changed the recovery mode to simple; I can just do a backup log with truncate only to clear down the space used in the tran log can't I?
Just to recap:
- do a FULL database backup
- Switch recovery model to Simple
- backup log with truncate only to clear down the transaction log
-
Basicaly simple recovery works like this :
Your log is still in use for data-transaction-integrity. Once data gets committed, the space used in the log for that transaction is marked for reuse and the content will be ignored.
If I'm correct, the log will be cleared by the system when you switch to simple.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Question.
I have a SQL 2000 database with no need for anything but the simple recovery mode.
I have an ldf file that is 6 times the size of my database and free disk space has become an issue.
I was under the impression that if I switched from full to simple recovery mode the ldf file would shrink, it did not. I'm GUESSING I now need to run shrinkfile to reduce the ldf file, is this correct?
If this is correct how do I do this? What is the syntax? Are there additional steps I need to perform?
-
Edd-927544 (9/22/2009)
Question.I have a SQL 2000 database with no need for anything but the simple recovery mode.
I have an ldf file that is 6 times the size of my database and free disk space has become an issue.
I was under the impression that if I switched from full to simple recovery mode the ldf file would shrink, it did not. I'm GUESSING I now need to run shrinkfile to reduce the ldf file, is this correct?
If this is correct how do I do this? What is the syntax? Are there additional steps I need to perform?
Database files do not shrink, unless you have activated the dboption "autoshrink" (NOT ADVISED ! because you have no control over the "when" it will shrink)
You are guessing correct: If you want it to shrink, you'll need to do it yourself !
HowTo ?
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
---------------------------
-- Shrink_TrxLog.SQL
--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'logicalname', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you very much,
Since I was not real sure what I was doing I was a little nervous about running shrinkfile, I was hesitating to run it becasue I wasn't convinced I was not going to lose anything. But I now have a very small ldf file and life is good. Once again THANK YOU VERY MUCH!
-
HTH
Just keep in mind, by switching to simple recovery, you can nolonger perform point in time recoveries !
You should revise your DRP for that db because you may need to increase the full db backup frequency.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I appreceiate that comment. We have absolutely no need for point in time restores. Its a web discussion board using the same forum software used here.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply