[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

SQL Server Large Log Files

Hi Guys,

Basically I have some very large log files for my SQL Server Databases. We don’t have a DBA and I am in need of assistance.

I have two SQL Servers running Windows 2008 R2 64bit with SQL Server 2008 running Databases with mirroring enabled and have a witness server too.

I have 9 databases setup on the server some more heavily used than others. Our Main database is as follows...

MDF File = 478Mb (Autogrow by 10% Unrestricted)
LDF File = 233GB (Autogrow by 10% Unrestricted)

Last Database Backup 03:34
Last Log File Backup12:30
Recovery Model Full
Mirroring Status "Fully Synchronized"

In SQL Server Agent Manager there is a job scheduled that runs a maintenance plan at 19:00 daily which

    Takes Full Backup of the DB to the local disk for all user databases.
    Deletes all backup files older than 2 days with .bak or .trn file extension.

There is another job set to take Transaction log backups hourly every day.

In the backups folder there are transaction log backups that are between 200Mb and 700Mb every hour.

Please can you advise what I can do as I don’t believe the log file should be this disproportionate to the Database file?

Running “DBCC OPENTRAN” I get

Replicated Transaction Information:

        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (219127:93:1)

Running “DBCC SQLPERF(Logspace)” I Get

Database Name      Log Size (MB)      Log Space Used (%)      Status
Main_Multibrand      239051.6      96.17966            0

Running “select name, log_reuse_wait_desc from sys.databases” I get

name                              log_reuse_wait_desc
GreenThings                        NOTHING
Import                              LOG_BACKUP
Main_Multibrand                  REPLICATION
Furniture                        LOG_BACKUP
Assoc                              LOG_BACKUP
Future                              LOG_BACKUP
Food                              LOG_BACKUP
Main_Multibrand_S                  LOG_BACKUP
discounts                        LOG_BACKUP
Survey                              LOG_BACKUP
Home                              LOG_BACKUP
Care                              LOG_BACKUP


If you need any further information then please shout.

Regards,

Nigel
0
Nigel_Taylor
Asked:
Nigel_Taylor
  • 4
  • 3
1 Solution
 
LesterClaytonCommented:
What happens if you just try to Shrink Database?

Open up SQL Management Studio
Log in as somebody who has sysadmin rights to the database or whole server
Find the database, right click and select "Tasks -> Shrink -> Database"

When prompted, just click OK.

This should shrink both the database and the transaction logs in one foul swoop.  If you find that this has helped you, then add a shrink database action to your full backup (not transaction log backup) maintenance task.

 Shrink Database
0
 
Scott PletcherSenior DBACommented:
If it's Main_Multibrand, the log is not being resused because it's preserving some log recores for replication.

If you still need the replication, you will have to either allow the replication to catch up itself or just turn off replication, shrink the log, turn repl back on, and re-sync the replicated copy from the start with a new db backup.

DON'T use the existing db backup to re-sync replication -- the existing backup will restore the log file to its current size.

Also, change the log file growth to a fixed amount, say 100M, rather than 10%.  That's always best, but even more so when the log is so huge, because you don't want to pause the db while SQL formats 25G of log space!
0
 
Nigel_TaylorAuthor Commented:
Hi Guys,

Thanks for the quick responses.

Things are starting to look better. I ahve gone through using the shrink command on some of the less critical databases and am noticing a big difference on some of the DB's.

For the DB Main_Multibrand I am happy to break the replication on it as I am running out of disk space on our backup server and need to reduce this file size. What would you deem the safest way to stop the replication?

Regards,

Nigel
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Nigel_TaylorAuthor Commented:
Ok I have dropped the replication on the DB Main_Multibrand and the files are as follows

Before
MDF = 500Mb
LDF = 233GB

After
MDF = 424Mb
LDF = 226GB

There are no Open Transactions in the Database and the log_reuse_wait_desc = LOG_BACKUP. I will wait for the next full backup and see what that does and then update this again.

Regards,

Nigel

0
 
Scott PletcherSenior DBACommented:
Once you have replication off, you can put the db in SIMPLE mode temporarily.

Then you should be able to shrink the *log* -- DON'T shrink the data files, *just* the *log* file.

USE <database_name>

DBCC SHRINKFILE ( logical_log_file_name, 100 )

For performance, you also don't want to have too many VLFs.  So run this command:

DBCC LOGINFO

If that returns more than, say, 60, shrink the log as much as possible and then re-alloc the log space all at once:

DBCC SHRINKFILE ( logical_log_file_name, 1 )

ALTER DATABASE <database_name> MODIFY FILE ( NAME = logical_log_file_name, SIZE = 100MB )




0
 
Scott PletcherSenior DBACommented:
Then, you can put the db back in FULL mode, take a full db backup, and you should be good from then on.
0
 
Nigel_TaylorAuthor Commented:
Thanks for the help Scott.

For the Main multibrand database the MDF file is now 466MB and the LDF is 600MB and queries over the DB are noticably faster.

The full back for the DB is now 420MB. Is there a way of sense checking that the transaction logs backed up hourly are correct to still be between 300Mb and 800Mb?

Regards,

Nigel
0
 
Scott PletcherSenior DBACommented:
Great, glad it helped!

You could put an alert or some other code to check that, but it really shouldn't be necessary.

Just make sure that the log backups actually do occur, and you should be fine.

If you haven't yet, do check the number of VLFs:

DBCC LOGINFO
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now