SQL Mirror Database log file growing big


I have two SQL Server 2008 R2 running under database mirroring Synchronized mood. Mirroring is working fine. I noticed the log file is growing big although TLog backup is working and there are no any open active transaction. I have 21 GB left out of 100 GB in the log drive.  And also 99.9 % log space is unused and 0.1% is used.

Please advise what necessary steps should I take in order to release the log.


Zahid AhamedDatabase AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I suppose you are using Full recovery model.
The actual log size could be caused by some log intensive operations like e.g. database reindexing or database defragmentation.
If you have just 0.1% of the log file used then you are most likely OK with the log size. What is the used space before the backup?
Zahid AhamedDatabase AdministratorAuthor Commented:
How do I know what is used space before the backup. But this is retail sales database. I asked windows team add some space but now question is can I shrink the log file since it is heavy write intensive db.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> How do I know what is used space before the backup

You can try this approach to see what time your Data/ Log files are growing or shrinking..
1. Right Click your Database
2. Choose Reports -> Standard Reports -> Disk Usage
3. Once the report is generated, you can expand Data\Log Files Autogrow\Autoshrink events to see when the data or log file sizes are changing.

After that, we can confirm whether the time your Log file grows is
1. coinciding with Index Maintenance activities or any other bulk activities.
2. Mirroring Latency might be high causing your Log file size to grow.
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

To shrink the log file does not help you. Maybe as a one time operation to investigate the growth. The shrink will slow the future log writes down due to the more frequent requests for new log space reservation.

The best option is to design the log at the max size and leave it as is.

I don't understand your question "How do I know what is used space before the backup?"... If you know the used space now and if you know the log backup scheduled time then you should be able to obtain the log usage before the backup.
Zahid AhamedDatabase AdministratorAuthor Commented:
Log backup run every hour and also log backup is not truncating the log from the log file. Right now the log max size is 464 mb. Should I increase the auto growth max size close to 1024 MB?
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Log backup run every hour and also log backup is not truncating the log from the log file.

That's fine, Log backup won't truncate your logs instead it will free the space within the transaction log to be reused for new log records.

>> Right now the log max size is 464 mb. Should I increase the auto growth max size close to 1024 MB?

Log file size of 464 MB is too low as you mentioned that it is growing to a huge value..
Just set the Initial size of Log file to 10 GB and set auto growth of it to 1024 or 2048 MB.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
What's the problem to leave it as is at 80 GB size? Constant log file size is the best option for SQL Server performance. You have sufficient space for your log records, you are not slowed down by autogrow operations and everything you would need is to check for additional log file size change in some maintenance task.

Why do you need to free the disk space? Do you use the same disk for other files? It is not good decision for the SQL Server stability.
Zahid AhamedDatabase AdministratorAuthor Commented:
There are no files except log. As i mentioned earlier 99.9 % log space is unused and 0.1% is used and you also you mentioned that there is no problem have 80 GB out of 100 GB.  So there is no reason for shrinking to release the space since log space unused 99.9%.. I just wondering if the drive fills up, i just wanted to avoid issues so that why i asked sysadmin team to add some space on the log drive in stead of shrinking because this db is heavy write intensive.
Yes, the log file is almost always write intensive. For how long is the server running? Actual log file size could mean just one occurrence of the log intensive operation as I mentioned earlier. This could be some kind of maintenance which is (or should be) always scheduled after the backup. The risk to leave the drive size unchanged does not seem to be high but you should check the log size more often between backups.  BUT I have nothing against convincing admins to add the space, of course. :-)

If the space adding is so simple them it points to the fact the drive is possibly virtual... and this means additional bottleneck. The best SQL Server performance is achieved when you use the hardware directly w/o any virtualization. Maybe SSD drives could be an exception.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> because this db is heavy write intensive.

If the db is write intensive, then kindly schedule Transaction Log backups more frequently like once every 15 minutes to keep the Log file size under control. If the frequency reduces then the log file size will increase and after log backup you can see 99.9% unused as you are observing now..
If you want to know whether your log file is really used, then check the unused space before the Log backup happens to justify whether it can grow to huge values or not..Please do monitor this when your users are busy so that you can see the worst case values..
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
1. before you set mirror you need to make sure trans log is "small"  on your principal DB
2. if mirrored copy got a large trans log ( from principal db):
             you may :
                  a) add extra space to drive(s) with tran log file
                  b) do fail-over to the mirrored database and do the shrink there- > make sure to test this some where first and make sure principal              got translog shrunk and trans log backup are running ...            

in any case check what was running  or not running ( tran log backup for example) that contributed to  a large tran log file on principal that was mirrored
Zahid AhamedDatabase AdministratorAuthor Commented:
I cant see the transaction log file growth because in the standard disk report saying that No entry found for autogrow/autoshrink event for dbname in the trace log.. I can only see the data file growth events. I ran the following script but did not get any result.

Code Snippet
--check if default trace is enabled
select * from sys.configurations where configuration_id = 1568
--get the current trace rollover file
--use this path with the log.trc file in the path below.
--this will cause a file rollover to get all the data
select * from ::fn_trace_getinfo(0)
--list of events object deleted, object altered
select *
from sys.trace_events
--list of categories e.g. database, sp etc..
select *
from sys.trace_categories
SELECT ntusername,loginname, objectname, e.category_id, textdata, starttime,spid,hostname, eventclass,databasename, e.name
FROM ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',0)
      inner join sys.trace_events e
            on eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
            ON e.category_id = cat.category_id
where databasename = 'MyDB' and
      cat.category_id = 2 and --database category
      e.trace_event_id in (92,93) --db file growth
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
check your trasl log size from DB properties; file location, etc
Zahid AhamedDatabase AdministratorAuthor Commented:
TLog size is 80 GB
Zahid AhamedDatabase AdministratorAuthor Commented:
Shrinking log file will not release the space because this production db is in under mirroring session and  I believe that it will throw error like cannot shrink log file 2 (log) because the logical log file located at the end of the file is in use.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Check if the main db has translog backup and when there are no activities try to shrink trans log file again
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
If your situation is a very critical,
You can try the less desirable way if you choose:

Remove mirror,
Set prim db in Simple recovery,
Shrink trans log,
Set db to Full recovery with trans log backup
And reset up mirror...
From what is written above I cannot say the situation is critical.
Why would we need to shrink it?

The log size is 80 GB with 99.9% free space... on dedicated disk.

This is ideal situation from performance point of view.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hi Zahid,

I'm still not clear on what you are trying to achieve.
Myself and pcelba have been suggesting from the beginning that there is nothing to be worried about 99.9% unused space in Log file.
if you wish to keep the Log file size under control, then you would need to take Log backups more frequently and Mirroring latency should be minimal..
Zahid AhamedDatabase AdministratorAuthor Commented:
Thanks for the valuable information
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.