?
Solved

database in simple recovery mode with huge transaction log file

Posted on 2015-01-28
11
Medium Priority
?
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 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 1600 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 1600 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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 1600 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 1600 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

765 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