December 21, 2011 at 10:05 pm
Hi everybody, thanks in advance for your answers.
Hey i'm really just new as a DBA but definitely something strange happened here.
Last night my server was running out of space, quickly i checked the folders where my backups are regularly performed, and suddenly i noticed that one of my transactional backups was really huge (4 Gb) and the rest was around 500 MB, my question is why that specific log backup was different? any ideas?
My full backup was normal around 7 GB.
Regards and thanks.
December 21, 2011 at 10:58 pm
You need to know what was going on before that transaction log backup. I've seen that happen when Index Reorganizations were done on some really large indexes. It could also happen if a lot of deletes were being done on a very large table. Basically, you are looking for an unusually large number of fully-logged transactions.
Note: every statement in SQL is a transaction. Most transactions are implicit. You can define an explicit transaction with BEGIN TRANSACTION / COMMIT. Any transaction which changes something gets saved in the transaction log.
December 21, 2011 at 11:14 pm
Ok, Cool let me start with that and l'll update my results here asap, thanks David.
December 22, 2011 at 3:50 am
My gut feel would be index rebuilds. They're usually the culprit for stuff like this
Basically, you are looking for an unusually large number of fully-logged transactions.
Even a huge minimally logged operation could cause the log backups to be large. Minimally logged means the log space used is lower, but the log backups afterwards are larger because they have to include the pages that were affected by the minimally logged operation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2011 at 8:10 am
Ok, l'm researching on this matter, probably in one hour l'll have a better idea, thanks
December 22, 2011 at 8:43 am
I was checking jobs but nothing comes up, any idea how can i know if indexes were rebuild or activity related with high transactional activity.
Thanks
December 22, 2011 at 8:52 am
islas.tonatiuh (12/22/2011)
I was checking jobs but nothing comes up, any idea how can i know if indexes were rebuild or activity related with high transactional activity.Thanks
1 case I know of is in Ms Dynamics Nav where you can start a rebuild all indexes from the GUI.
Maybe you could check the stats dates to see if you have a buttload of them updated almost at the same time (within minutes)
December 22, 2011 at 8:59 am
So, if i'm take a look into the stats dates l'm going to have a better idea about what happened?
December 22, 2011 at 9:07 am
islas.tonatiuh (12/22/2011)
I was checking jobs but nothing comes up, any idea how can i know if indexes were rebuild or activity related with high transactional activity.Thanks
This query will summarize what objects and operations are currently logged.
use [my_db];
select allocunitname, operation, context
-- , sum([log record length])sum_log_record_length
from fn_dblog(null, null)
group by allocunitname, operation, context
order by allocunitname, operation, context;
allocunitname operation context
------------------------------ ------------------------------- -------------------------------
MYDB.CLIENT.IX_CLIENT_NAME LOP_DELETE_ROWS LCX_MARK_AS_GHOST
MYDB.CLIENT.IX_CLIENT_NAME LOP_INSERT_ROWS LCX_INDEX_LEAF
MYDB.CLIENT.IX_CLIENT_NAME LOP_SET_BITS LCX_PFS
MYDB.CLIENT.PK__CLIENT__30A40E8 LOP_DELETE_ROWS LCX_MARK_AS_GHOST
MYDB.CLIENT.PK__CLIENT__30A40E8 LOP_INSERT_ROWS LCX_CLUSTERED
. . .
. . .
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 22, 2011 at 9:12 am
Great Sir, let me try it and come back with more details.
Thanks
December 22, 2011 at 9:23 am
Very cool query, Mr. Russell. I did not know you could do that. The only problem in applying it to this particular problem is that the transactions he is interested in are no longer in the transaction log. They have all been saved to backup and then truncated from the log.
I am not sure how you would investigate this unless you have some kind of 3rd party database monitoring software. Quest and Redgate both make some good stuff.
December 22, 2011 at 9:29 am
David Moutray (12/22/2011)
Very cool query, Mr. Russell. I did not know you could do that. The only problem in applying it to this particular problem is that the transactions he is interested in are no longer in the transaction log. They have all been saved to backup and then truncated from the log.I am not sure how you would investigate this unless you have some kind of 3rd party database monitoring software. Quest and Redgate both make some good stuff.
Yes, the query I posted won't tell you what was contained in a transaction log before the backup. However, he can still keep an eye on the size of the log, and run the query today, if the problem reoccurs.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 22, 2011 at 9:38 am
One more question Sir, what's the interpretation for the operation and context columns retrieved from the query?
I ran it but how can i use those results, because i saw:
e.g.
dbo.TransactionLog.PK_TransactionLogLOP_MODIFY_ROWLCX_TEXT_MIX
dbo.TransactionLog.PK_TransactionLogLOP_MODIFY_ROWLCX_TEXT_TREE
dbo.TransactionLog.PK_TransactionLogLOP_SET_BITSLCX_GAM
dbo.TransactionLog.PK_TransactionLogLOP_SET_BITSLCX_IAM
sys.sysallocunits.clustLOP_COUNT_DELTALCX_CLUSTERED
December 22, 2011 at 9:44 am
islas.tonatiuh (12/22/2011)
One more question Sir, what's the interpretation for the operation and context columns retrieved from the query?I ran it but how can i use those results, because i saw:
e.g.
dbo.TransactionLog.PK_TransactionLogLOP_MODIFY_ROWLCX_TEXT_MIX
dbo.TransactionLog.PK_TransactionLogLOP_MODIFY_ROWLCX_TEXT_TREE
dbo.TransactionLog.PK_TransactionLogLOP_SET_BITSLCX_GAM
dbo.TransactionLog.PK_TransactionLogLOP_SET_BITSLCX_IAM
sys.sysallocunits.clustLOP_COUNT_DELTALCX_CLUSTERED
The function fn_dblog is not officially documented by Microsoft or anywhere near fully documented in blogs. You'll have to google each of those operations to see what you can find on them.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 22, 2011 at 9:46 am
Sorry i noticed your replay 🙂
ok let me google it
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply