Changing DB from Full to Simple Recovery - and how to truncate the current transaction log to free space

Can someone please help or point to instructions on how to #1 truncate the current transaction log which is too large and #2 change the database to SIMPLE recovery from FULL.  (should the current log be truncated first and then the database recovery changed, or will the process of change the recovery mode truncate the log.  Also, will truncate automatically reduce the log size, or will it need  a 'shrink' process to get it back to a acceptable size?)  If multiple steps, please direct me to the correct order - much appreciated.
BigSkyCountryAsked:
Who is Participating?
 
Anthony PerkinsCommented:
1.  Change the Recovery Model to Simple.
ALTER DATABASE YourDatabaseNameGoesHere SET RECOVERY SIMPLE

2. Execute several checkpoints in a row:
CHECKPOINT

3. Shrink the database to an appropriate size:
DBCC SHRINKFILE('YourTransactionLogFileNameGoesHere', 2000)     -- 2GB.

0
 
Rajkumar GsSoftware EngineerCommented:
These are the steps, to shrink database.

// Shrink SQL Server database log file
1. Execute this command against the database
Dump transaction <<your db name>> with no_log

2. Right-Click database - Shrink Files - Select 'Reorganize pages before releasing usused space' - Enter 'Shrink File to ' - 0 - Click OK

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
Recommend to backup your database before attempting this
Raj
0
 
Rajkumar GsSoftware EngineerCommented:
Changing Recovery Model
 Recovery Model - Settings
Raj
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.