Solved

MS SQL log files very large

Posted on 2013-06-27
26
340 Views
Last Modified: 2013-10-15
Good day everyone. I have an issue today where I ran out of space on the disk partition that holds the databases and log files. I noticed that most log files are very large. My largest one is nearly 7 GB.

I run log backups twice a day, differential backup nightly, and full backup over the weekend on these databases using Backup Exec 12.5. Any idea why they are so large still and how can I get them to shrink?

I noticed that the log_reuse_wait for most DBs in the sys.databases shows as being LOG_BACKUP. What can i do to resolve this issue?
0
Comment
Question by:mig1980
  • 13
  • 8
  • 4
  • +1
26 Comments
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39282602
How full are the transaction logs?

dbcc sqlperf( logspace );

Open in new window

0
 

Author Comment

by:mig1980
ID: 39282675
They range from 0.39% to about 50%. I have about 15 databases.
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39282718
I would be interested to see what they are right before the transaction log backups and right after the backups.  Those log use amounts don't seem impossible for realistic values in a properly backed up full recovery model...  Another important point is that once the logs grow, they never shrink unless you manually shrink them.  I would advise against that until you get to the root cause of why they are the sizes that they are.  You may also need to increase the transaction log backup frequency on the databases with the larger log files before you shrink them (otherwise if they are legitimately growing to that size, it's going to just grow back to that size and be fragmented, yielding degraded performance)
0
 

Author Comment

by:mig1980
ID: 39282724
I can monitor them today. The logs were just backed up about 30 minutes ago.
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39282742
If they were just backed up and they are still 50% used, my guess is there's something getting in the way of the checkpoint or backup exec isn't backing up the logs properly.  Try a SQL server transaction log backup on one of the databases with the high logspace and see if the log space used % decreases afterwards.  Do you have any replication or mirroring setup ?
0
 

Author Comment

by:mig1980
ID: 39282744
There is also a shrink db job that is ran nightly for three of the most utilized DBs. Is this good practice to do or should backing up the logs supplement this?

The code used for the job is as follows

DBCC SHRINKDATABASE (DBData, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE (DBUsers, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE (DBTrans, TRUNCATEONLY)
GO
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39282754
I wouldn't recommend shrinking the databases - the transaction log backups should keep them relatively small and shrinking database files can cause fragmentation on the disk.  There's something else afoot if they keep growing until you shrink them.
0
 

Author Comment

by:mig1980
ID: 39282757
No replication or mirroring set. By the way, this is all running on SQL 2005 Server.
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39282766
Try a transaction log backup out of SQL Server on one of the databases with high transaction log file utilization and see if it decreases afterwards?
0
 

Author Comment

by:mig1980
ID: 39282792
One question, should I choose "Back up to the existing media set" or Back up to a new media set, and erase all existing backup sets"?
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39282964
I would remove the current media location and then add a new location with a new file name so that it's assuredly a new file and then erase all existing backup sets.
0
 

Author Comment

by:mig1980
ID: 39283005
OK, so I just did that to one of the dbs that doesn't get used much at all and had slightly over 50% (1 GB) log space used. After the backup ran (which totaled 16KB in size), the log space used did not decrease or increase at all since two hours ago when the Backup Exec backups ran.
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39283104
That's really confusing... Log backups are supposed to move the log head forward and free up space inside of the log file.  Is the log_reuse_wait still log_backup?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:mig1980
ID: 39283124
Yes it is.
0
 

Author Comment

by:mig1980
ID: 39283216
So i just took a look at the DB I backup and even though the log space used still states 50%, the size of the log file is only 1MB.

I tried running a log backup on another db that was over 1 GB in size and nothing changed.
0
 
LVL 8

Assisted Solution

by:didnthaveaname
didnthaveaname earned 294 total points
ID: 39283225
can you try the following and let me know if it's returning lsn's or null:

select 
   db_name(database_id) AS dbName, 
   last_log_backup_lsn 
from
   sys.database_recovery_status;

Open in new window

0
 
LVL 16

Assisted Solution

by:EvilPostIt
EvilPostIt earned 42 total points
ID: 39283653
Do you have any long running open transactions? Try executing the following code...


DBCC OPENTRAN

Open in new window

0
 

Author Comment

by:mig1980
ID: 39284806
To answer didnthaveaname's question, only the master and tempdb returned NUll values. The rest show LSNs.

To answer EvilPostIt's question, there are no active open transactions currently.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 164 total points
ID: 39291099
If you need to shrink the log, just shrink it, not the entire db.  Shrinking dbs should only be used very sparingly, as it creates fragmentation.

If you don't need the log back up, you can just truncate the log (SQL 2005 or before only).

BACKUP LOG DBData WITH TRUNCATE_ONLY


After backup (to a file or just truncating), you can shrink the log file itself to a given size like so:


USE DBData
DBCC SHRINKFILE ( 2, 2048 ) --2048 = <#_mb_to_leave_in_log>
0
 

Author Comment

by:mig1980
ID: 39339678
We are currently backing up logs but I noticed a job running currently that is also truncating specific databases nightly.

From what your saying, you should not do both. Is that correct?

With regards to shrinking the logs the way you describe, are there any ill affects to doing this?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 164 total points
ID: 39344568
>> From what [you're] saying, you should not do both. Is that correct? <<

Not exactly.  You should never shrink the entire database using DBCC SHRINKDATABASE.

Instead, you should only shrink data files extremely rarely, such as when a huge amount of data has been deleted.

You can shrink the log file anytime it uses autogrow multiple times and gets way too large.  If that happens, shrink the log, then immediatlely re-allocate it to the maximum size you need.  You don't want the log to have to use autogrow except in rare and unusual conditions.

For example, say you initially allocate the log at 500M, but notice that it continually grows to 2GB.  In that case, shrink the log and them immediately grow it to, say, 2.5-3GB, so that you have some extra space in the log to prevent autogrow during normal operations:


USE DBData
-- shrink the log file to the minimum size possible to free up all VLFs
DBCC SHRINKFILE ( 2 )
-- grow the log initially to 0.5GB, to create some smaller VLFs
ALTER DATABASE DBData MODIFY FILE ( NAME = DBData_log, SIZE = 512MB )
-- grow the log to 2.5GB, to prevent autogrow from being required
ALTER DATABASE DBData MODIFY FILE ( NAME = DBData_log, SIZE = 2560MB )


Say you do a massive load one weekend and the log unexpectedly grows to 5GB.  Then after taking the appropriate log backups if needed, shrink the log back down to its normal size of 2.5GB:

DBCC SHRINKFILE ( 2, 2560 )


>> With regards to shrinking the logs the way you describe, are there any ill affects to doing this? <<

No.  That is the preferred method for shrinking the log.  But, there can be extreme ill effects from shrinking the entire database instead.
0
 

Author Comment

by:mig1980
ID: 39344861
Here is the code currently being used. I just wanted to verify that we are talking about the same thing.

DBCC SHRINKDATABASE (MI, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE (MS_W, TRUNCATEONLY)
GO
DBCC SHRINKDATABASE (LC2, TRUNCATEONLY)
GO

Open in new window


There is also another job that performs history cleanup using a Maintenance Plan. Not sure if this is proper protocol to do this. Here is the job description:

Cleanup history on Local server connection
History type: Backup,Job,Maintenance Plan
Age: Older than 2 Weeks
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 164 total points
ID: 39345599
Again, NO database should EVER be shrunk except in extreme cases.

Iow, the command "DBCC SHRINKDATABASE" should NEVER be used routinely.

You will waste resources and fragment your tables.
0
 

Author Comment

by:mig1980
ID: 39371314
The databases that get shrunk are transnational and a lot of data moves in and out of them. Deletion, updates, adds, etc.

What are the best practices to follow in this scenario?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 164 total points
ID: 39371342
If the activity in specific table(s) generates the need to shrink those tables frequently, then move those tables into a separate filegroup, and shrink only the files in that file group.

Again, if necessary, you can use the command:

DBCC SHRINKFILE ( ... )

if/when necessary to shrink a specific file for a specific reason.

But you should never use DBCC SHRINKDATABASE, which arbitrarily shrinks every file in the db.  I suspect the reason some people use it is because they are unaware of SHRINKFILE.

Btw:

DBCC SHOWFILESTATS

can be very helpful in determining if there really are a lot of pages that would be freed by shrinking a given file.
0
 

Author Comment

by:mig1980
ID: 39424475
Thank you for the heads up on this. I will give it a try and see if it works for us.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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 tutorial will walk an individual through the process of configuring basic necessities in order to use the 2010 version of Data Protection Manager. These include storage, agents, and protection jobs. Launch Data Protection Manager from the deskt…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

758 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

20 Experts available now in Live!

Get 1:1 Help Now