LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
0
Guests Online:
100

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Weird log issue

Please wait...

Weird log issue

Postby Thirster42 on Tue May 22, 2012 4:10 pm

OK, this is happening on a couple of databases now. I've got full and log backups automated using sql agent. the logs are done every 2 hours. On two databases on two seperate servers (completley different versions) they aren't releasing log space after a log backup (ie, an 8 gb log file is 99% full right after a log backup). The only thing that I can think of is that something funky is happenign because of replication.

Any pointers on what to look at?
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Weird log issue

Postby gmmastros on Tue May 22, 2012 4:19 pm

You probably checked this already, but just a thought...

What is the initial size of the log file? If the initial size is 8gb, it won't get any smaller than that.
-George
User avatar
gmmastros
LTD Admin
LTD Admin
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630LTD Gold - Rating: 1630
LTD Gold - Rating: 1630LTD Gold - Rating: 1630
 
Posts: 2369
Joined: Tue Oct 09, 2007 5:19 pm
Unrated

Re: Weird log issue

Postby Thirster42 on Tue May 22, 2012 4:37 pm

gmmastros wrote:You probably checked this already, but just a thought...

What is the initial size of the log file? If the initial size is 8gb, it won't get any smaller than that.


It's 8813mb, but i'm not talking about the size of the actual log file, but rather how much data is in the log file, if that makes any sense. IF i look, it tells me that there is only 37.3 mb of data left in the log file, even though a log back just happened.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Weird log issue

Postby SQLDenis on Tue May 22, 2012 4:42 pm

Log truncation frees space in the log file for reuse by the transaction log. Because the active part of the log cannot be truncated or removed by shrinking, truncation can be delayed when log records remain active for a long time.

does this query return NOTHING for your database?


  1. select log_reuse_wait_desc ,name from sys.databases
  2.  


It could be any of these factors


0
NOTHING
Currently there are one or more reusable virtual log files.

1
CHECKPOINT
No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models).
This is a routine reason for delaying log truncation. For more information, see Checkpoints and the Active Portion of the Log.

2
LOG_BACKUP
A log backup is required to move the head of the log forward (full or bulk-logged recovery models only).
Note Log backups do not prevent truncation.
When the log backup is completed, the head of the log is moved forward, and some log space might become reusable.

3
ACTIVE_BACKUP_OR_RESTORE
A data backup or a restore is in progress (all recovery models).
A data backup works like an active transaction, and, when running, the backup prevents truncation. For more information, see "Data Backup Operations and Restore Operations," later in this topic.

4
ACTIVE_TRANSACTION
A transaction is active (all recovery models).
A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. For more information, see "Long-Running Active Transactions," later in this topic.
A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource. For information about the causes of deferred transactions and how to move them out of the deferred state, see Deferred Transactions.

5
DATABASE_MIRRORING
Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only).
For more information, see "Database Mirroring and the Transaction Log," later in this topic.

6
REPLICATION
During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).
For more information, see "Transactional Replication and the Transaction Log," later in this topic.

7
DATABASE_SNAPSHOT_CREATION
A database snapshot is being created (all recovery models).
This is a routine, and typically brief, cause of delayed log truncation.


8
LOG_SCAN
A log scan is occurring (all recovery models).
This is a routine, and typically brief, cause of delayed log truncation.

9
OTHER_TRANSIENT
This value is currently not used.


See here: Factors That Can Delay Log Truncation
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Weird log issue

Postby onpnt on Tue May 22, 2012 4:46 pm

Transactional Replication? That is more than likely why but let me know the type before going that route.

Denis's query will also tell you the reason (which I'm guessing is replication usage on the log)
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Weird log issue

Postby onpnt on Tue May 22, 2012 4:49 pm

If you want to read up on it
http://msdn.microsoft.com/en-us/library/ms190925.aspx

pretty much the trans replication are not distributed completely to the distributor (or subscribers). Check replication to ensure it isn't backed up.
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Weird log issue

Postby Thirster42 on Tue May 22, 2012 4:56 pm

onpnt wrote:If you want to read up on it
http://msdn.microsoft.com/en-us/library/ms190925.aspx

pretty much the trans replication are not distributed completely to the distributor (or subscribers). Check replication to ensure it isn't backed up.



Do you mean make sure the subscribed db is not backed up?


Denis, it's saying Replication.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Weird log issue

Postby SQLDenis on Tue May 22, 2012 4:58 pm

I am sure Ted will create a blog post out of this :-s
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Weird log issue

Postby Thirster42 on Tue May 22, 2012 5:12 pm

onpnt wrote:Transactional Replication? That is more than likely why but let me know the type before going that route.

Denis's query will also tell you the reason (which I'm guessing is replication usage on the log)


the replication type is snapshot which happens once an hour.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Weird log issue

Postby onpnt on Tue May 22, 2012 8:48 pm

No, replication is simply not done with distribution of the commands. If replication is not completed, it will not allow the transactions to be removed. Essentially, they are open.

Out of curiosity, why are you using snapshot replication on such a small time frame? If you find there is a need to have a data latency of 1 hour, it would almost seem more effective and a higher quality of performance to enlist it as transactional replication and run your log reader with a greater time frame between retries.

I'll put something together but as Denis already knew, replication is simply not done and not allowing your log to flush out. If you are seeing this all the time, and cannot change the over all structure of the landscape on moving data, you'll need to leave the log as sized so it can maintain it all. So basically, your 8GB is where you need to be :)
Tarwn: Yeah yeah, all you do is SELECT * all day long and say "no" to people...Life of a DBA
User avatar
onpnt
LTD Admin
LTD Admin
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623LTD Silver - Rating: 623
 
Posts: 1608
Joined: Tue Oct 09, 2007 5:23 pm
Location: Kenosha, WI
Unrated

Re: Weird log issue

Postby SQLDenis on Tue May 22, 2012 9:01 pm

Out of curiosity does your replication run at the same time that the log backup is running
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Weird log issue

Postby Thirster42 on Tue May 22, 2012 9:08 pm

onpnt wrote:No, replication is simply not done with distribution of the commands. If replication is not completed, it will not allow the transactions to be removed. Essentially, they are open.

Out of curiosity, why are you using snapshot replication on such a small time frame? If you find there is a need to have a data latency of 1 hour, it would almost seem more effective and a higher quality of performance to enlist it as transactional replication and run your log reader with a greater time frame between retries.

I'll put something together but as Denis already knew, replication is simply not done and not allowing your log to flush out. If you are seeing this all the time, and cannot change the over all structure of the landscape on moving data, you'll need to leave the log as sized so it can maintain it all. So basically, your 8GB is where you need to be :)



it's jsut set up the way that i got it to work when i was first figuring things out. this replication is jsut going to a reporting server so i can change how the replication works, just have to let my manager know what i'm doing.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Weird log issue

Postby Thirster42 on Tue May 22, 2012 9:09 pm

SQLDenis wrote:Out of curiosity does your replication run at the same time that the log backup is running


no.
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Weird log issue

Postby Thirster42 on Fri May 25, 2012 2:30 pm

Changing to transactional replicaiton seems to have fixed the issue
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4646
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated