Solved

database in simple recovery mode with huge transaction log file

Posted on 2015-01-28
11
150 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 5 35
Sql Stored Procedure 65 26
SqlServer no dupes 25 34
SQL Server: Unable to remove duplicate sets in Header/Detail 6 23
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…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

776 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