Solved

database in simple recovery mode with huge transaction log file

Posted on 2015-01-28
11
144 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:ScottPletcher
ScottPletcher 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:ScottPletcher
ScottPletcher 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
 

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:ScottPletcher
ScottPletcher 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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:
ScottPletcher 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:ScottPletcher
ID: 40576247
You seem surprised ;-)
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now