Solved

database in simple recovery mode with huge transaction log file

Posted on 2015-01-28
11
157 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

839 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