SQL Mirror Database log file growing big

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?

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.

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

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.

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?

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.

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.

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

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

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

check your trasl log size from DB properties; file location, etc

TLog size is 80 GB

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.

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

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.

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

Thanks for the valuable information