Transaction Log growing - Disk getting full -Sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Transaction Log growing - Disk getting full -Sql

Author

Topic

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-19 : 14:49:37

Hi,
I am having issue with out Transaction logs as it's keep getting full even after shrinking the file.
We have Sql server 2008 and it's also run MS CRM application.
We have T -Log Back up is set up to every 2 hrs.
I have changed my recovery model from FULL to SIMPLE then Shrink the file and did full back up and set up again to FULL Recovery Mode but still it's growing fast.

Appreciated any help!

Thanks.

chadmat
The Chadinator

1974 Posts

Posted - 2013-04-19 : 15:06:42

You obviously need to back up the log more frequently if you want to minimize growth.

-Chad

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-19 : 15:17:23

We are taking every two hrs T-Log back up, what you suggest the frequency?
Do you suggest any other maintenance tips as it's just started this week?

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-19 : 15:35:02

As Chad suggested, it is likely that you just have lot of activity, resulting in lot of writes to the log file. If that is the case, you do need to backup the logs more frequently (or live with the large log file).

What was not clear to me from your posting though, is whether the VLF's are cleared when you take the log backup. Immediately after you do a log backup, run DBCC loginfo. You should see most VLF's with status = 0. If you see them with status = 2, that means they cannot be cleared. Run DBCC opentran to see the oldest transactions.

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-19 : 17:17:00

Thanks Chad.
I will change Log back up frequency from 2 hours to every one hour, do you think it will cause any disk space issue?
I ran DBCC Loginfo command and lot of file shows with status = 2 as we just finished T-Log back up and I ran the query after 10 minutes.
Is it I need to run frequently DBCC Loginfo command and monitor it?
What you suggest if any FSeqNo has status = 2?
Is it CreateLSN is 0 then what is indicating in very first two rows?

Thanks for your help!

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-20 : 16:24:11

Could you please let me know about how to clear VLF's?

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-20 : 20:01:17

Hi,
Currently my log size shows 90.6 and log used shows 97.1 After Running SPGetSqlPerfStat.
I ran also another query and got the result:
Recovery Model Log Reuse Wait Description Log Size (KB) Log Used (KB) Log Used % DB Compatibility Level Page Verify Option
FULL LOG_BACKUP 5403256 4651491 86 100 CHECKSUM

I ran DBCC LOGINFO command and lot of files with status = 2 and total files are 295 with size from 253952 to 16384000.
I also ran DBCC OpenTran but no open transaction.
Please advice me, Appreciate your help!

Thanks.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-21 : 07:49:30

quote:
Originally posted by poratips

Hi,
Currently my log size shows 90.6 and log used shows 97.1 After Running SPGetSqlPerfStat.
I ran also another query and got the result:
Recovery Model Log Reuse Wait Description Log Size (KB) Log Used (KB) Log Used % DB Compatibility Level Page Verify Option
FULL LOG_BACKUP 5403256 4651491 86 100 CHECKSUM

I ran DBCC LOGINFO command and lot of files with status = 2 and total files are 295 with size from 253952 to 16384000.
I also ran DBCC OpenTran but no open transaction.
Please advice me, Appreciate your help!

Thanks.

I am not familiar with SPGetSQLPerfStat. Seems like it is a query that is trying to figure out log reuse wait info. But, for your log file size, I see conflicting info in your posting - initially you said it is 90.6 and then 5403256. We need to figure out what the actual size is and how much of it is used.

Another thing that I am not clear from your postings is whether or not log backups clear any VLF's. Not all VLF's would be cleared; in a busy database there would be many that are active, especially if there are long running transactions. And it is not just long running transactions - other things such as transactional replication, database mirroring etc. can cause VLF's to remain active even if there are no uncommitted transactions.

In any case, do the following:

1. Run the following:

DBCC SQLPERF(LogSpace)

This should give you info on log file usage and percentage used for each database. This is to determine what the log file sizes are, what percentages are used and to confirm that your CRM database is in-fact the one causing the problem.

2. Run the following script:

select name,log_reuse_wait_desc from sys.databases

That should tell you the reasons why the log file for each database cannot be cleared. From your posting, it seems like it is LOG_BACKUP that is given as the reason. Let us assume that is still the case.

3. In your log backup script, make sure that you DO NOT have WITH COPY_ONLY option.

4. Run the following and copy and save the results somewhere

DBCC LogInfo

5. Backup the log file manually once (make sure there is no WITH COPY_ONLY option!).

6. Backup the log one more time manually. (I don't know the reason for this requirement, MSDN just mentions it - see here: http://msdn.microsoft.com/en-us/library/ms178534.aspx )

7. Run DBCC LogInfo again and compare with what you got in step 4. I am expecting that there should be many more VLF's with status = 0.

8. If you see that no VLF's have been cleared (Fstatus = 0 is a cleared VLF and Fstatus = 2 is an active VLF), "DBCC SQLPERF(LogSpace)" and "select name,log_reuse_wait_desc from sys.databases" again to see the space usage and to understand what is holding up the VLFs.

9. If you still see a problem, post the log_reuse_wait_desc and also the results of "DBCC LoginInfo". When you post, use [code] and [/code] tags so it is readable. I probably won't be able to offer much help without having access to the database, but some of the others on the forum might have better insights.

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-21 : 10:41:56

Thanks James for your very useful and detail information.

 
[1] DBCC SQLPERF(LogSpace)
Log Size (MB)	Log Space Used (%)	Status
1.492188	87.43456	0

[2] select name,log_reuse_wait_desc from sys.databases
log_reuse_wait_desc
LOG_BACKUP

[3] My Backup script has: WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, COMPRESSION - We are using MS sql Management Job

[4]DBCC LogInfo
FileId	FileSize	StartOffset	FSeqNo	Status	Parity	CreateLSN
2	253952	8192	1040059	2	64	0
2	253952	262144	1040058	2	128	0
2	270336	516096	1040060	2	64	1.04006E+21
2	262144	786432	1040061	2	64	1.04006E+21
2	262144	1048576	1040062	2	64	1.04006E+21
2	262144	1310720	0	0	0	1.04006E+21

==>
DBCC SQLPERF(LogSpace)= 1.742188	95.6278	0

==>
log_reuse_wait_desc
LOG_BACKUP

Thanks for your great help! your detail explanation was so much informative and useful, really appreciate it!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-21 : 18:01:25

poratips, I don't see any problems with your log file. After your log backup the log file is TINY at under 2 Megs. In fact, it was tiny even before you did the backup, perhaps because your scheduled job was functioning as designed. If your log file is growing too big to tens or hundereds of gigabytes, simply increase the frequency of log backups to once per hour, or once every half hour, or whatever will get you to the log size that your disk space restrictions can tolerate. There are no adverse effects to increasing the frequency of log backups (unless you went overboard

)

Also, as a general best practice, you should:

1. Always ALLOW log file to autogrow.

2. Never LET log file to autogrow if you can help it.

What I mean is that, you should look at your work load and figure out how much log space you need. (Observe how big the log file gets to over a period of 2 or 3 typical days). Allocate that much or a bit more, so SQL Server won't have to autogrow the log file. If log file has to autogrow, all other activities on the database have to wait. What is even worse is that newly allocated VLFs have to be zeroed out - they cannot be added via instant initialization. So autogrowth of the log file can noticeably pause the database.

BUT, leave the autogrow capability enabled, because in case it needs to autogrow and you don't let it, all activities on the database will stop.

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-22 : 00:04:46

Thanks James once again.

I don't know but now it shows from DBCC SQLPERF(LogSpace)and also it show lots of VLs - and log_reuse_wait_desc shows "NOTHING" and DBCC LogInfo shows now 490 VLFs with only one has status = 0
Log Size (MB) Log Space Used (%) Status

14198.8 0.6455484 0

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-04-22 : 02:40:01

Have a look at this to troubleshoot VLF's.
http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/

mohammad.javeed.ahmed@gmail.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 08:53:32

quote:
Originally posted by poratips

Thanks James once again.

I don't know but now it shows from DBCC SQLPERF(LogSpace)and also it show lots of VLs - and log_reuse_wait_desc shows "NOTHING" and DBCC LogInfo shows now 490 VLFs with only one has status = 0
Log Size (MB) Log Space Used (%) Status

14198.8 0.6455484 0

When you saw this, how long ago was the last log backup? If 14 Gigs is too much, you should backup the log file more frequently.

When you backup the log file, any VLFs that can be cleared are cleared. As time goes on, more of them get consumed (so status changes to 2). So watch the usage and set the backup interval to be short enough to meet your disk usage requirements.

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-22 : 13:55:29

Thanks James.
When I posted during that time I saw yesterday and size is very.
I have just checked - select name,log_reuse_wait_desc from sys.databases and log_reuse_wait_desc shows Log_backup and also checking to VLfs, total files around 490 and 33 files with status = 2.
I have changed T-log backup frequency to every one hour, now backup log size is every hour is:
8 AM 7013 KB
9 AM 24897 KB
10 AM 45385 KB
11 AM 43220 KB
12 PM 419318 KB

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 14:17:26

I didn't follow what you said this: "When I posted during that time I saw yesterday and size is very."

For the rest of the data, everything seems normal to me. During business hours, it is under half a gig - that is good. From 10:00 AM to 11:00 AM, the size went down - which indicates that your log backups are doing its job. So I don't see anything wrong at all - are you experiencing any problems?

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-22 : 14:21:29

Sorry for confusion James. Currently I am watching and let you know if any issue.
Thanks for your help!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 15:12:40

Great! Glad to be of help.

poratips
Posting Yak Master

105 Posts

Posted - 2013-04-25 : 19:44:26

Looks like changing T-Log Backup frequency to one hour help lot.
Thanks everyone for your great help and valuable solution tips.

poratips
Posting Yak Master

105 Posts

Posted - 2013-05-08 : 13:52:13

Hi, It's started again to grow and it's getting full.
I have just taken another results:

[1] DBCC SQLPERF(LogSpace)
Log Size (MB) Log Space Used (%) Status
7659.492 0.5747056 0

[2] select name,log_reuse_wait_desc from sys.databases
log_reuse_wait_desc
LOG_BACKUP

[3]DBCC LogInfo ==> Total VLF's files are 400 and 7 files are with status = 2

FileId FileSize StartOffset FSeqNo Status Parity
2 253952 8192 1050755 2 128
2 253952 262144 1050754 2 64
2 9043968 370737152 1050753 2 128
2 9043968 379781120 1050756 2 128
2 9043968 388825088 1050757 2 128
2 9895936 397869056 1050758 2 128
2 9895936 407764992 1050759 2 128

Thanks for your great help!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-08 : 16:35:55

Only 0.57 percent is used - which is consistent with most of the VLF's being in cleared state. So while the log file itself may be large (extrapolating from what you said, it looks like it might be about 8 Gigs), most of the space in that 8 gigs is availabe for reuse.

The physical file may have grown probably because there was lot of activity between successive log back ups, long running queries, or large index rebuilds. If you expect them on a regular or periodic basis, you should leave the log file at that size and continue to do regular log backups.

srimami
Posting Yak Master

160 Posts

Posted - 2013-05-14 : 04:44:42

Hi Poratips,

Well, lot has been discussed on log file growth and some tips/tricks. If I were you, I would start with why the log file is growing huge? Find out the root cause and then start performing the tips. Last time, it happened with me was when we were rebuilding Index and also while creating Index for large database table. Also, it happens when some user queries. Check out the queries that were running on this database which is causing the log file grow and perform tuning the sql, this would help to great extent.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources