Solved

database in simple recovery mode with huge transaction log file

Posted on 2015-01-28
11
161 Views
Last Modified: 2015-01-28
I have a database that was recently changed from full recovery mode to simple recovery mode.
jan 26th it was changed.

the transaction log file for this db is 25 gig.  I thought the transaction log would be minimized after a day or so but it has not.
I have also run the checkpoint command against the db.

any suggestions?
0
Comment
Question by:jamesmetcalf74
  • 5
  • 5
11 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 40575918
>I thought the transaction log would be minimized after a day
Unless you are taking the Transaction log backups, it will continue to grow.  Since it is already 25GB, you should take a full bckup, change the recovery model to simple and shrink the log file. then change it back to Full recovery.
Schedule a TL backup run every 15min - 1 hr depending on the traffic  and how critical the data is .
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 40575997
First, check to see if something is keeping SQL from reusing existing log space:

SELECT name, log_reuse_wait_desc
FROM sys.databases
WHERE name = '<your_db_name>'
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 40576005
Also, SQL should truncate the log when in simple recovery mode ... but "truncate" as SQL uses it is not quite its natural meaning.  A log "truncate" just means that the space is marked as reusable, but not that the log file has been "shrunk".

To shrink the log -- i.e. release the disk space -- you must explicitly enter a command(s) to do that.  SQL doesn't automatically shrink log files (unless you have AUTO_SHRINK ON, in which case you should immediately turn it off!, since it's terrible for performance).

Since the log is probably very fragmented, it's usually best to do a max shrink and then grow the log back to the size you want.

For example, say you want to get the log down to 4GB.  Issue these commands:

--if you don't know the logical log file name, copy it from the first column of this output:
USE <your_db_name>
EXEC sp_helpfile

USE <your_db_name>
DBCC SHRINKFILE ( 2 )

ALTER DATABASE <your_db_name> MODIFY FILE ( NAME = <logical_log_file_name>, SIZE = 4GB )
0
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 

Author Comment

by:jamesmetcalf74
ID: 40576190
I keep getting an error
here is my command

alter database test
modify file (name=test.ldf, size = 1gb)

the error points to the period between test and ldf
the thing is the mdf and ldf files are the exact same name except they have the different extensions.
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 40576201
It needs to be the logical file name, not the physical file name.

--if you don't know the logical log file name, you can copy it from the first column of this output:
 USE <your_db_name>
 EXEC sp_helpfile


If that is the logical name, then put brackets around it:

alter database test
 modify file (name=[test.ldf], size = 1gb)
0
 

Author Comment

by:jamesmetcalf74
ID: 40576213
getting a different error message now

Msg 5039, Level 16, State 1, Line 1
MODIFY FILE failed. Specified size is less than or equal to current size.

output of dbcc shrinkfile (2)
DbId      FileId      CurrentSize      MinimumSize      UsedPages      EstimatedPages
77      2      995632                          6400                          995632                           6400

when I look at test.ldf in windows explorer it has a size of 7,965,056 kb
0
 

Author Comment

by:jamesmetcalf74
ID: 40576223
the name in the first column from the output of exec sp_helpfile is
row 1 column 1
test.mdf
row 2 column 1
test.ldf
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 40576226
The last record in use in the log must be around the ~8GB byte location in the log file.  SQL can't remove active log records, obviously, and it won't copy log records to new locations.

Wait a while, then try again.  By then the log writes should of looped around to the front of the log file, allowing you to shrink from the end of the file back.

But at least the log is down to 8GB from 25GB :-) .
0
 

Author Comment

by:jamesmetcalf74
ID: 40576232
the test file is a file I am using to test the shrink log file before I use it on the production file which is still 25 gig.
im practicing on the test file first.

neither one has changed

sorry about that...
0
 

Author Comment

by:jamesmetcalf74
ID: 40576240
holy crap it worked
thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40576247
You seem surprised ;-)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 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