July 11, 2012 at 5:37 pm
Hi All-
I used to have this script but can't find it now. I have a database in simple mode that has filled the transaction log and won't let me recover the space with my usual method.
ALTER DATABASE [test] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(test_log, 1)
ALTER DATABASE [test] SET RECOVERY FULL WITH NO_WAIT
In the past when I ran into this problem it's typically because of one transaction at the end of the log that prevents the log space from being recovered. This script runs and moves the transaction to the beginning of the log and recovers that extra space.
BTW, this is for a test server where Im deleting a ton of transactions and don't care about recovery, just need to delete the transactions and clear the log.
If anyone has a copy of the script or a link for it I would very much appreciate it. I've looked for several hours and can't seem to find it on the intertubes now for some reason.
Thanks
July 11, 2012 at 5:45 pm
Something that has worked very well for me in this case is to run two tlog backups back to back. That will often times clear that transaction.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 11, 2012 at 6:46 pm
SQLRNNR (7/11/2012)
Something that has worked very well for me in this case is to run two tlog backups back to back. That will often times clear that transaction.
Yep, did that too and no luck with that either.
July 11, 2012 at 10:14 pm
What does the following query return?
select
[name] as DatabaseName,
log_reuse_wait_desc
from
sys.databases;
July 12, 2012 at 1:14 pm
Lynn Pettis (7/11/2012)
What does the following query return?
select
[name] as DatabaseName,
log_reuse_wait_desc
from
sys.databases;
Checked that and it returns "NOTHING".
July 12, 2012 at 1:32 pm
Probably a silly question, why was the database in full recovery in the first place if you aren't worried about recoverability? And why shrink the log so? It will just end up growing again causing perf issues.
July 12, 2012 at 1:36 pm
Nicholas Cain (7/12/2012)
Probably a silly question, why was the database in full recovery in the first place if you aren't worried about recoverability? And why shrink the log so? It will just end up growing again causing perf issues.
This is a test system where the database was already created that way. I need to shrink it for space issues since I'm already out of drive space on this machine and the log is huge since we don't do regular backups since this is just a test system.
July 12, 2012 at 2:39 pm
If it's a test system then leave it in simple recovery. Have a daily job that sets all DBs to simple recovery. Set it back to full and you'll be right back with a full log in a few days
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
July 12, 2012 at 2:56 pm
July 12, 2012 at 3:19 pm
GilaMonster (7/12/2012)
If it's a test system then leave it in simple recovery. Have a daily job that sets all DBs to simple recovery. Set it back to full and you'll be right back with a full log in a few days
This is a script that I'm running on the test system that I'm moving to production when I'm done debugging it so there's not really much that goes on with this database generally. So in general I'm leaving it in simple mode but this script is one that I'm trying to verify will work.
July 12, 2012 at 3:19 pm
@SQLFRNDZ (7/12/2012)
Ther must be an open transaction that is filling your log, DBCC opentran.
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Nope
July 12, 2012 at 3:21 pm
@SQLFRNDZ (7/12/2012)
Ther must be an open transaction that is filling your log, DBCC opentran.
If there was, log_reuse_wait_desc would have returned "Open Transaction", not nothing.
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
July 12, 2012 at 3:25 pm
GilaMonster (7/12/2012)
@SQLFRNDZ (7/12/2012)
Ther must be an open transaction that is filling your log, DBCC opentran.If there was, log_reuse_wait_desc would have returned "Open Transaction", not nothing.
In most cases. Though the opposite, I have seen opentran return nothing and log_reuse_wait_desc show 'Replication' or 'Active Transaction'
That's usually when the double t-log backup helps.
Wondering if growing this t-log by a couple of MB and then trying the double backup will help.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 12, 2012 at 3:26 pm
Rogman (7/12/2012)
GilaMonster (7/12/2012)
If it's a test system then leave it in simple recovery. Have a daily job that sets all DBs to simple recovery. Set it back to full and you'll be right back with a full log in a few daysThis is a script that I'm running on the test system that I'm moving to production when I'm done debugging it so there's not really much that goes on with this database generally. So in general I'm leaving it in simple mode but this script is one that I'm trying to verify will work.
It will. Just one point...
In the past when I ran into this problem it's typically because of one transaction at the end of the log that prevents the log space from being recovered. This script runs and moves the transaction to the beginning of the log and recovers that extra space.
Nothing moves log records around in the log. Once a log record is in a specific place in the log, it cannot move. If it could, its VLF would change and that would cause all sorts of havok with a lot of the log management system.
The pattern log backup, shrink, log backup tells the logging system that you're trying to shrink the log and if there is an active VLF towards the end of the log file, it will start the next log records in a VLF earlier in the file if there is one. The log records at the end still need to be made inactive before you can shrink the log
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
July 12, 2012 at 3:40 pm
SQLRNNR (7/12/2012)
GilaMonster (7/12/2012)
@SQLFRNDZ (7/12/2012)
Ther must be an open transaction that is filling your log, DBCC opentran.If there was, log_reuse_wait_desc would have returned "Open Transaction", not nothing.
In most cases. Though the opposite, I have seen opentran return nothing and log_reuse_wait_desc show 'Replication' or 'Active Transaction'
Yes, it doesn't update immediately. Sometimes requires a checkpoint to update (and to actually truncate the log in simple)
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply