• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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.
0
BigSkyCountry
Asked:
BigSkyCountry
  • 3
1 Solution
 
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now