June 25, 2008 at 5:26 am
Hello,
Can anyone tell me what changes should i make to this query in order to run quickly.
I need to get the output of this query for troubleshoot one problem that i'm having with the growing of a log file. This query is running since yesterday at 12:30pm and still runs.
select * from ::fn_dblog(null,null)
where [Transaction ID] in (
select distinct [Transaction ID] from ::fn_dblog(null,null)
where description='**********')
Can you tell me to what does this ::fn_ tables do?
Thanks and regards,
JMSM 😉
June 25, 2008 at 5:29 am
You don't need the distinct inside the IN. In is just checking for existance. It doesn't matter how many times a value is there.
Other than that, I don't think there's any way to get a query of the transaction log faster. It's not a real table aftere all and it's optimised for writing, not reading.
What's the problem you're trying to diagnose? There may be a better way.
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
June 25, 2008 at 7:03 am
Hello Gila,
We're trying to get all the operations inside the transaction log where description is Replicate because we've got one transaction log that growths and growths and as the 'only thing' that is running on the machine is replication, we're trying to get what is exactly increasing the size of the transaction log.
Other thing that i need to ask you what changes should you use to do to the query?
Once more, thank a lot Gina.
As i told you one or two months a go hope one day be as useful to you as you've being useful to me.
Thanks and regards,
JMSM 😉
June 25, 2008 at 7:28 am
Ok, there are other ways of doing that then reading the tran log (which is messy)
Have a look at the sys.databases view. There's a column called log_reuse_wait_desc. It lists the reason that the transaction log is not been reused.
Please check what recovery mode you are in.
Please check that your transaction log backups are running if you are in full rcovery mode.
Please run DBCC OPENTRAN and see what it says.
Run sp_replcounters and see what it lists for 'replicated transactions'
Replicated transactions is the number of unreplicated commands in the log that need to be transmitted to the distributor.
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
June 25, 2008 at 7:39 am
Hi Gila,
We've yet try to do every commands that you send me, but we cant truncate the log because of transactions that we're not being commited.
Is there any way to put an 'set auto commit on' in the transactions that don't want to to let the transaction log to be truncated or anything else that we should check.
Thanks and regards,
JMSM 😉
June 25, 2008 at 7:44 am
The stuff I gave tyou is to see why the tran log can't be truncated. Once we know that, fixing is usually isn't hard.
Auto commit is the SQL default. Unless you explicitly state BEGIN TRANSACTION or if you've set implicit transactions on, they you're running in auto commit mode.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply