Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL log files very large

Posted on 2013-06-27
26
Medium Priority
?
366 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 1176 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 1176 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

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 1176 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 168 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 656 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 70

Accepted Solution

by:
Scott Pletcher earned 656 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 656 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 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 656 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

886 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