Growing Log File Frustrations

Toddy_Boy

Hi all

Can someone put their finger on why my log file continues to grow event though I have a database backup and transaction log backup running nightly, the backup is then restored to a fresh training database.

The Recovery Model is Full (as far as I have been informed this cannot be changed to simple) as the database is being mirrored.

What is worse is that I'm positive this has been running successfully until recently some recently added maintenance tasks to reorganize indexes.

These are the scripts;

BACKUP DATABASE [Dynamics Nav 2009 LIVE] TO  DISK = N'T:\SQL Log Backup LIVE\Dynamics Nav COPY.bak' WITH  COPY_ONLY, 
	NOFORMAT, 
	INIT,  
	NAME = N'Dynamics Nav 2009 LIVE-Full Database Backup', 
	SKIP, 
	NOREWIND, 
	NOUNLOAD,  
	STATS = 10
GO

BACKUP LOG [Dynamics Nav 2009 LIVE] TO  
DISK = N'T:\SQL Log Backup LIVE\LIVELog' WITH NOFORMAT, NOINIT,  
NAME = N'Dynamics Nav 2009 LIVE-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Any help is appreciated

bbrown

My question is why all those parameters? NOREWIND and NOUNLOAD are for use with tape systems.

Also why COPY_ONLY?

Toddy_Boy

I'm not a SQL DBA so I am not sure of the parameters, I simply populated the Backup screen in SQL Enterprise Manager (using google as my guide) and generated this script from - "Script Action to New Query Window", that would have populated the parameters.

What's the issue with COPY_Only? Is this causing the log not to truncate?

bbrown

The log is truncated by the log backups, so no, COPY_ONLY has no impact on that. COPY_ONLY is used to create a backup outside the normal backup sequence. An example might be that you need to grab a new backup for some testing. You should remove COPY_ONLY

Is your log file growing simply because your daily transaction volume is increasing? How often do you run log backups?

Toddy_Boy

This morning the log file has 6170.35 MB (8%) free space of a 70504.44 MB, this will go through the next few days and the log will expand again. After the backup database and backup log jobs have run I would expect this to be 99% free.

There is a high transaction volume thus the need to have the log truncated each night.

bbrown

How big is this database? You mention this database is mirrored. What mirroring mode are you using? (these questions are more curiosity as I also work with a mirrored database)

To start with you should run log backups much more frequently. I would suggest at least every hour. If not sooner.

A log backup will only truncate the continuous inactive portion of the log. So it's not unusual for it to not clear the entire log. Especially in a very active system.

Toddy_Boy

Its big 106,565,376 KB. No idea about the mirroring bit.

The system is not very active during the early morning, this is when the backup is scheduled so that should clear space in the log.

I have seen this;

http://stackoverflow.com/questions/646845/sql-server-2008-log-will-not-truncate-driving-me-crazy

It suggests this;

Backup logs
Full backup of the database
Shrink the logs file
Backup logs again
Shrink the logs file again

I'll try that in tonight's job

bbrown

Toddy Boy wrote:

Its big 106,565,376 KB. ...

That's not big. :-$

bbrown

You might need to temporarlily break the mirror to resolve this issue. Then re-establish it afterwards.

Toddy_Boy

So there's nothing wrong with the script?

Is there a way to pause the mirror service from T-SQL?

bbrown

I haven't said there was nothing wrong with the script. To begin with you should not be using COPY_ONLY or any of those tape commands. But also not saying those are your problems either.

Since I don't know how your mirroring is configured, I'll avoid giving you recommendations in that area for now.

kriki

Also mirroring uses the transactionlog. It is possible that the mirror(s) cannot keep up applying the changes and thus SQL Server cannot free the transaction log even after the transaction log backup.

With this command, you can check WHY SQL Server cannot free the transaction log:
SELECT name,log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait_desc <> 'NOTHING'; -- nothing means Nothing is blocking the reusing of transactionlog space.

With this command, you can see how many virtual log files there are in your DB (you need to run it using the db you want to check):
DBCC LOGINFO;

It is also possible (I doubt this is your case) you have too few virtual log files in your transactionlog, log space can only be reused when your virtual log file is not used anymore.

Toddy_Boy

SELECT name,log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait_desc <> 'NOTHING';

model LOG_BACKUP
Bdc_Service_DB_4336af912fbc4ed782ce98df46692fa0 LOG_BACKUP
Dynamics Nav 2009 LIVE LOG_BACKUP
Dynamics Nav 2009 COPY LOG_BACKUP
Copy of Barron McCann LOG_BACKUP
XA_Stream_DS LOG_BACKUP
MF20 LOG_BACKUP
NintexForms LOG_BACKUP
NW2010DB LOG_BACKUP
SharePoint_Config LOG_BACKUP
Subscription_Settings_DB LOG_BACKUP
BDC_Service_DB LOG_BACKUP
Secure_Store_Service_DB LOG_BACKUP
SharePoint_AdminContent_cbfe0c3c-b8cc-4ba4-96a9-d1288c639e50 LOG_BACKUP
App_Service_DB LOG_BACKUP
WSS_Content_2013 LOG_BACKUP
NW2013DB LOG_BACKUP
NF2013DB LOG_BACKUP

DBCC LOGINFO;

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 4587454464 8192 850 0 128 0
2 4587454464 4587462656 851 0 128 0
2 4587454464 9174917120 852 0 128 0
2 4587454464 13762371584 876 0 128 0
2 4587454464 18349826048 849 0 64 0
2 143327232 22937280512 853 0 64 70000895919900001
2 143327232 23080607744 854 0 64 70000895919900001
2 143327232 23223934976 855 0 64 70000895919900001
2 143327232 23367262208 856 0 64 70000895919900001
2 143327232 23510589440 857 0 64 70000895919900001
2 143327232 23653916672 858 0 64 70000895919900001
2 143327232 23797243904 859 0 64 70000895919900001
2 143327232 23940571136 860 0 64 70000895919900001
2 143327232 24083898368 861 0 64 70000895919900001
2 143327232 24227225600 862 0 64 70000895919900001
2 143327232 24370552832 863 0 64 70000895919900001
2 143327232 24513880064 864 0 64 70000895919900001
2 143327232 24657207296 865 0 64 70000895919900001
2 143327232 24800534528 866 0 64 70000895919900001
2 143327232 24943861760 867 0 64 70000895919900001
2 143908864 25087188992 868 0 64 70000895919900001
2 157679616 25231097856 869 0 64 86000028039800003
2 157679616 25388777472 870 0 64 86000028039800003
2 157679616 25546457088 871 0 64 86000028039800003
2 157679616 25704136704 872 0 64 86000028039800003
2 157679616 25861816320 873 0 64 86000028039800003
2 157679616 26019495936 874 0 64 86000028039800003
2 157679616 26177175552 875 0 64 86000028039800003
2 164560896 26334855168 877 0 128 723000895944900040
2 164560896 26499416064 878 0 128 723000895944900040
2 164560896 26663976960 879 0 128 723000895944900040
2 164560896 26828537856 880 0 128 723000895944900040
2 164560896 26993098752 881 0 128 723000895944900040
2 164560896 27157659648 882 0 128 723000895944900040
2 164560896 27322220544 883 0 128 723000895944900040
2 164560896 27486781440 884 0 128 723000895944900040
2 164560896 27651342336 885 0 128 723000895944900040
2 164560896 27815903232 886 0 128 723000895944900040
2 164560896 27980464128 887 0 128 723000895944900040
2 164560896 28145025024 888 0 128 723000895944900040
2 164560896 28309585920 889 0 128 723000895944900040
2 164560896 28474146816 890 0 128 723000895944900040
2 164560896 28638707712 891 0 128 723000895944900040
2 165085184 28803268608 892 0 128 723000895944900040
2 181010432 28968353792 893 0 128 739000032200000001
2 181010432 29149364224 894 0 128 739000032200000001
2 181010432 29330374656 895 0 128 739000032200000001
2 181010432 29511385088 896 0 128 739000032200000001
2 181010432 29692395520 897 0 128 739000032200000001
2 181010432 29873405952 898 0 128 739000032200000001
2 181010432 30054416384 899 0 128 739000032200000001
2 181010432 30235426816 900 0 128 739000032200000001
2 181010432 30416437248 901 0 128 739000032200000001
2 181010432 30597447680 902 0 128 739000032200000001
2 181010432 30778458112 903 0 128 739000032200000001
2 181010432 30959468544 904 0 128 739000032200000001
2 181010432 31140478976 905 0 128 739000032200000001
2 181010432 31321489408 906 0 128 739000032200000001
2 181010432 31502499840 907 0 128 739000032200000001
2 181731328 31683510272 942 2 128 739000032200000001
2 199098368 31865241600 756 0 64 755000035330800418
2 199098368 32064339968 757 0 64 755000035330800418
2 199098368 32263438336 758 0 64 755000035330800418
2 199098368 32462536704 759 0 64 755000035330800418
2 199098368 32661635072 760 0 64 755000035330800418
2 199098368 32860733440 761 0 64 755000035330800418
2 199098368 33059831808 762 0 64 755000035330800418
2 199098368 33258930176 763 0 64 755000035330800418
2 199098368 33458028544 764 0 64 755000035330800418
2 199098368 33657126912 765 0 64 755000035330800418
2 199098368 33856225280 766 0 64 755000035330800418
2 199098368 34055323648 767 0 64 755000035330800418
2 199098368 34254422016 768 0 64 755000035330800418
2 199098368 34453520384 769 0 64 755000035330800418
2 199098368 34652618752 770 0 64 755000035330800418
2 200081408 34851717120 771 0 64 755000035330800418
2 219021312 35051798528 772 0 64 771000038957700040
2 219021312 35270819840 773 0 64 771000038957700040
2 219021312 35489841152 774 0 64 771000038957700040
2 219021312 35708862464 775 0 64 771000038957700040
2 219021312 35927883776 776 0 64 771000038957700040
2 219021312 36146905088 777 0 64 771000038957700040
2 219021312 36365926400 778 0 64 771000038957700040
2 219021312 36584947712 779 0 64 771000038957700040
2 219021312 36803969024 780 0 64 771000038957700040
2 219021312 37022990336 781 0 64 771000038957700040
2 219021312 37242011648 782 0 64 771000038957700040
2 219021312 37461032960 783 0 64 771000038957700040
2 219021312 37680054272 784 0 64 771000038957700040
2 219021312 37899075584 785 0 64 771000038957700040
2 219021312 38118096896 786 0 64 771000038957700040
2 219873280 38337118208 787 0 64 771000038957700040
2 240975872 38556991488 788 0 64 786000032205800331
2 240975872 38797967360 789 0 64 786000032205800331
2 240975872 39038943232 790 0 64 786000032205800331
2 240975872 39279919104 791 0 64 786000032205800331
2 240975872 39520894976 792 0 64 786000032205800331
2 240975872 39761870848 793 0 64 786000032205800331
2 240975872 40002846720 794 0 64 786000032205800331
2 240975872 40243822592 795 0 64 786000032205800331
2 240975872 40484798464 796 0 64 786000032205800331
2 240975872 40725774336 797 0 64 786000032205800331
2 240975872 40966750208 798 0 64 786000032205800331
2 240975872 41207726080 799 0 64 786000032205800331
2 240975872 41448701952 800 0 64 786000032205800331
2 240975872 41689677824 801 0 64 786000032205800331
2 240975872 41930653696 802 0 64 786000032205800331
2 241106944 42171629568 803 0 64 786000032205800331
2 265027584 42412736512 804 0 64 790000042109600512
2 265027584 42677764096 805 0 64 790000042109600512
2 265027584 42942791680 806 0 64 790000042109600512
2 265027584 43207819264 807 0 64 790000042109600512
2 265027584 43472846848 808 0 64 790000042109600512
2 265027584 43737874432 809 0 64 790000042109600512
2 265027584 44002902016 810 0 64 790000042109600512
2 265027584 44267929600 811 0 64 790000042109600512
2 265027584 44532957184 812 0 64 790000042109600512
2 265027584 44797984768 813 0 64 790000042109600512
2 265027584 45063012352 814 0 64 790000042109600512
2 265027584 45328039936 815 0 64 790000042109600512
2 265027584 45593067520 816 0 64 790000042109600512
2 265027584 45858095104 817 0 64 790000042109600512
2 265027584 46123122688 818 0 64 790000042109600512
2 265879552 46388150272 819 0 64 790000042109600512
2 291569664 46654029824 820 0 64 819000051846700052
2 291569664 46945599488 821 0 64 819000051846700052
2 291569664 47237169152 822 0 64 819000051846700052
2 291569664 47528738816 823 0 64 819000051846700052
2 291569664 47820308480 824 0 64 819000051846700052
2 291569664 48111878144 825 0 64 819000051846700052
2 291569664 48403447808 826 0 64 819000051846700052
2 291569664 48695017472 827 0 64 819000051846700052
2 291569664 48986587136 828 0 64 819000051846700052
2 291569664 49278156800 829 0 64 819000051846700052
2 291569664 49569726464 830 0 64 819000051846700052
2 291569664 49861296128 831 0 64 819000051846700052
2 291569664 50152865792 832 0 64 819000051846700052
2 291569664 50444435456 833 0 64 819000051846700052
2 291569664 50736005120 834 0 64 819000051846700052
2 291897344 51027574784 835 0 64 819000051846700052
2 320733184 51319472128 836 0 64 835000056873400091
2 320733184 51640205312 837 0 64 835000056873400091
2 320733184 51960938496 838 0 64 835000056873400091
2 320733184 52281671680 839 0 64 835000056873400091
2 320733184 52602404864 840 0 64 835000056873400091
2 320733184 52923138048 841 0 64 835000056873400091
2 320733184 53243871232 842 0 64 835000056873400091
2 320733184 53564604416 843 0 64 835000056873400091
2 320733184 53885337600 844 0 64 835000056873400091
2 320733184 54206070784 845 0 64 835000056873400091
2 320733184 54526803968 846 0 64 835000056873400091
2 320733184 54847537152 847 0 64 835000056873400091
2 320733184 55168270336 848 0 64 835000056873400091
2 346750976 55489003520 908 0 64 907000035224900078
2 346750976 55835754496 909 0 64 907000035224900078
2 346750976 56182505472 910 0 64 907000035224900078
2 346750976 56529256448 911 0 64 907000035224900078
2 346750976 56876007424 912 0 64 907000035224900078
2 346750976 57222758400 913 0 64 907000035224900078
2 346750976 57569509376 914 0 64 907000035224900078
2 346750976 57916260352 915 0 64 907000035224900078
2 346750976 58263011328 916 0 64 907000035224900078
2 346750976 58609762304 917 0 64 907000035224900078
2 346750976 58956513280 918 0 64 907000035224900078
2 346750976 59303264256 919 0 64 907000035224900078
2 346750976 59650015232 920 0 64 907000035224900078
2 346750976 59996766208 921 0 64 907000035224900078
2 346750976 60343517184 922 0 64 907000035224900078
2 347668480 60690268160 923 0 64 907000035224900078
2 381485056 61037936640 924 0 64 923000067769400091
2 381485056 61419421696 925 0 64 923000067769400091
2 381485056 61800906752 926 0 64 923000067769400091
2 381485056 62182391808 927 0 64 923000067769400091
2 381485056 62563876864 928 0 64 923000067769400091
2 381485056 62945361920 929 0 64 923000067769400091
2 381485056 63326846976 930 0 64 923000067769400091
2 381485056 63708332032 931 0 64 923000067769400091
2 381485056 64089817088 932 0 64 923000067769400091
2 381485056 64471302144 933 0 64 923000067769400091
2 381485056 64852787200 934 0 64 923000067769400091
2 381485056 65234272256 935 0 64 923000067769400091
2 381485056 65615757312 936 0 64 923000067769400091
2 381485056 65997242368 937 0 64 923000067769400091
2 414842880 66378727424 938 0 64 937000074473600001
2 414842880 66793570304 939 0 64 937000074473600001
2 414842880 67208413184 940 2 64 937000074473600001
2 414842880 67623256064 941 2 64 937000074473600001

What am i looking for?

kriki

The first query tells me that all your databases cannot reuse the logfile because they are waiting for log backups.

The second query tells me you have +- 187 VTL's (=Virtual Transaction Log files) in your transaction log. This is quite a lot but still acceptable (I have seen logfiles with over 20000 VLF's! => performance down the drain!).

So, the mirroring is not the problem. And neither is having to few VTL's.

Rereading your post, it seems you only do a transaction log backup at night.

To keep your transactionlog file used-% low, you need to do regular transaction log backups. Usually I do that every 15 minutes. Otherwise transaction log backups, full recovery model, mirroring have no sense.

Example : if someone (=developer) does a DELETEALL(FALSE) on your G/L Entry table. What do you do? Your mirror has lost the G/L entries too.
That is when regular transactionlog backups come into play : You can restore the DB up too a few minutes before the DELETEALL and lose very little transactions that have to be redone again instead of taking the backup of last night.


PS: "SQL Enterprise Manager". I suppose you mean SQL Server Management Studio because the Enterprise manager existed until SQL2000.

BTW: about the COPY_ONLY : what bbrown says is true : get rid of it in your backup strategy. It is to be used if you do a full backup that is completely out of the backup plan to avoid SQL uses it to propose a restore plan (=which backups to restore). In your case, you would need the VERY FIRST full backup without the "COPY_ONLY" and all transaction log backup after that to restore a database up to a few minutes ago.

About the NOREWIND and NOUNLOAD : those don't do anything for disk-backups but why dirten your backup command with those?

Toddy_Boy

Thanks Kriki and bbrown for the backup advice, I'll look at that when I get this thing shrunk.

Does this mean there is a stuck job somewhere locking the transaction log?

The first query tells me that all your databases cannot reuse the log file because they are waiting for log backups.

kriki

Toddy Boy wrote:

Does this mean there is a stuck job somewhere locking the transaction log?

No. You just need to do log backups during the day. Like I said: generally I implement it at customers to do a logbackup every 15 minutes. And if the database is communicating with another database in real time, I even go down to 10 and 5 minutes on both databases.
If something happens on 1 of the databases, it generally is a big problem to get them back in sync. With those very often TL backups, it gets easier.

Toddy_Boy

The problem has been resolved. I introduced the second backup and shrink as mentioned in an earlier thread however I think it was down to the database mirror which had paused itself. It makes sense for the transaction log not to be allowed to be truncated until the mirror has caught up. Phew.

I will start to truncate the log more frequently (good advice there guys) every hour to start with to make sure there is no impact on performance.

Many thanks guys