Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

MS SQL log files very large

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
mig1980
Asked:
mig1980
  • 13
  • 8
  • 4
  • +1
12 Solutions
 
didnthaveanameCommented:
How full are the transaction logs?

dbcc sqlperf( logspace );

Open in new window

0
 
mig1980Author Commented:
They range from 0.39% to about 50%. I have about 15 databases.
0
 
didnthaveanameCommented:
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
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!

 
mig1980Author Commented:
I can monitor them today. The logs were just backed up about 30 minutes ago.
0
 
didnthaveanameCommented:
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
 
mig1980Author Commented:
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
 
didnthaveanameCommented:
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
 
mig1980Author Commented:
No replication or mirroring set. By the way, this is all running on SQL 2005 Server.
0
 
didnthaveanameCommented:
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
 
mig1980Author Commented:
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
 
didnthaveanameCommented:
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
 
mig1980Author Commented:
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
 
didnthaveanameCommented:
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
 
mig1980Author Commented:
Yes it is.
0
 
mig1980Author Commented:
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
 
didnthaveanameCommented:
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
 
EvilPostItCommented:
Do you have any long running open transactions? Try executing the following code...


DBCC OPENTRAN

Open in new window

0
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
>> 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
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
mig1980Author Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
mig1980Author Commented:
Thank you for the heads up on this. I will give it a try and see if it works for us.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 13
  • 8
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now