SQL Mirror Database log file growing big

Zahid Ahamed
Zahid Ahamed used Ask the Experts™
on
Hi,

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.

Thanks

Zahid
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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 Administrator

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 Administrator

Author

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?
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> 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.
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 Administrator

Author

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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> 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..

Commented:
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 Administrator

Author

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

Commented:
check your trasl log size from DB properties; file location, etc
Zahid AhamedDatabase Administrator

Author

Commented:
TLog size is 80 GB
Zahid AhamedDatabase Administrator

Author

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.

Commented:
Check if the main db has translog backup and when there are no activities try to shrink trans log file again

Commented:
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 Guide
Awarded 2009
Distinguished Expert 2018

Commented:
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 Administrator

Author

Commented:
Thanks for the valuable information

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial