December 3, 2013 at 2:25 am
select log_reuse_wait_desc from sys.databases Where database_id =YOURDBID
pass database id and you'll get single row output.
December 3, 2013 at 2:30 am
amandac (12/3/2013)
If I runuse %databasename%
then the select command, will this give me the correct result?
Well, it'll give you correct results, but it won't give you want I asked for. It's a server-scoped DMV, it returns exactly the same results no matter what DB it's run in.
From Books Online:
sys.databases (Transact-SQL)
Contains one row per database in the instance of Microsoft SQL Server.
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 3, 2013 at 2:31 am
PravB4u (12/3/2013)
select log_reuse_wait_desc from sys.databases Where database_id =YOURDBIDpass database id and you'll get single row output.
Far easier to filter by the database name.
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 3, 2013 at 2:32 am
Ok, so that is REPLICATION.
I don't get that... there is nowhere for it to replicate to
December 3, 2013 at 2:34 am
Do you have transactional replication configured on that database? Don't assume, check the replication folder in SSMS. Is there a transactional replication publication there for this database?
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 3, 2013 at 2:38 am
There's nothing under Local Publications or Local Subscriptions
December 3, 2013 at 2:47 am
have you ever used replication on this database and removed improperly or have you enabled Change Data Capture.
December 3, 2013 at 2:55 am
PravB4u (12/3/2013)
have you ever used replication on this database and removed improperly or have you enabled Change Data Capture.
We haven't done anything to this database. There is a possibility that another support company has.
Background - we support the "hardware" and the maintenance on the server, another company supports the software that uses these databases. There has always been arguments over who maintains the databases.
December 3, 2013 at 3:03 am
December 3, 2013 at 3:21 am
PravB4u (12/3/2013)
try thishttp://akawn.com/blog/2012/01/resolve-log_reuse_wait_desc-is-replication/
Brilliant, thanks. That's changed the log_reuse_wait_desc to LOG_BACKUP.
I'm going to check my backups now.
December 3, 2013 at 3:32 am
Thanks for all your help everyone.
Just run a transaction log backup then a differential backup. There is now 31 GB of space in the log, and the diff. backup was only a few hundred MB!!.
Problem solved 😀
December 6, 2013 at 4:09 am
Grant Fritchey (12/2/2013)
When you run a full backup, it also backs up the transaction log. If your transaction log has 30gb of transactions that have not been backed up (guessing based on your statement that the files are full), then that will be part of the backup of the database. I'd suggest getting the log backups run, and much more frequently than once an hour (again, inferring from what you said). 10gb of data plust 30gb of log equal 40gb of backup.
I disagree with this. A full backup doesn't back up the entire transaction log. Only a transaction log backup can do that.
I am more inclined to believe that each backup is appended to the last in th esame file so that the backup file increases in size according to the size of the current backup.
December 6, 2013 at 4:47 am
amandac (12/3/2013)
Nope, no long running transactions, no blocking transactions....Any other ideas?
Were you sure to run DBCC OPENTRAN in the affected database ? I ask because in another thread I think the person ran it in Database_B, when the problem was with Database_A
EDIT: Ooops, didn't see it was resolved.
December 6, 2013 at 4:56 am
kevaburg (12/6/2013)
Grant Fritchey (12/2/2013)
When you run a full backup, it also backs up the transaction log. If your transaction log has 30gb of transactions that have not been backed up (guessing based on your statement that the files are full), then that will be part of the backup of the database. I'd suggest getting the log backups run, and much more frequently than once an hour (again, inferring from what you said). 10gb of data plust 30gb of log equal 40gb of backup.I disagree with this. A full backup doesn't back up the entire transaction log. Only a transaction log backup can do that.
I disagree.
A full backup can, in a number of scenarios, back up the entire transaction log. One of those scenarios is described in this thread where the entire log was marked for replication. The full backup must include the log back to the oldest open transaction or oldest unreplicated command. Since the entire log was marked for replication, the full backup included the entire log.
Now a full backup does not truncate the log, only a log backup does that.
I am more inclined to believe that each backup is appended to the last in th esame file so that the backup file increases in size according to the size of the current backup.
The OP confirmed that was not the case.
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 6, 2013 at 6:14 am
kevaburg (12/6/2013)
Grant Fritchey (12/2/2013)
When you run a full backup, it also backs up the transaction log. If your transaction log has 30gb of transactions that have not been backed up (guessing based on your statement that the files are full), then that will be part of the backup of the database. I'd suggest getting the log backups run, and much more frequently than once an hour (again, inferring from what you said). 10gb of data plust 30gb of log equal 40gb of backup.I disagree with this. A full backup doesn't back up the entire transaction log. Only a transaction log backup can do that.
I am more inclined to believe that each backup is appended to the last in th esame file so that the backup file increases in size according to the size of the current backup.
My statement was less than completely clear. I said it backed up the transaction log. What I should have said was, it backs up the parts of the transaction log that are not yet committed. That could be all, part, or none of the log. The resolution here was that there were transactions not yet committed, so the backup was capturing a pretty substantial portion of the entire log. My statement was less than clear, but it was accurate to the issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply