February 5, 2008 at 7:31 am
hi experts,
This is 2005.
Have a small database with a huge log - 200GB. Running out of disk space and need to clear the log.
BACKUP LOG EventsManager WITH TRUNCATE_ONLY
DBCC SHRINKFILE ('EventsManager_log',TRUNCATEONLY)
the ablove gives "logical file is in use" even though I have stopped all processes that use the db.
When I run:
DBCC LOGINFO shows 70,000 recs have a Status 2 - I think these are uncomitted rows ?
Even after rebooting the server they still have Status 2.
How can I force these to commit OR get the log truncated?
Thanks, John
February 5, 2008 at 8:00 am
... I should add that I do NOT need these uncommitted records. OK to lose them.
I dont have enough disk space to backup the log locally but I can backup log to another server.
Will I then be able to truncate the log?
Thanks, John
February 5, 2008 at 10:45 am
What does DBCC OPENTRAN mention?
If there are open transactions, then try a COMMIT transaction, or a ROLLBACK until such time as nothing is left open. Obviously you'd want to do this while no current user is on the system, so that you don't commit their stuff.
you may also have some orphaned stuff out there, in which case you might need to look at killing the SPID's that those are attached to (which will roll them back). You'd have to look at what you get from sp_who to figure out which SPID's to go after. As I recall - You might want to start with spid -2.
Once you get the transactions gone, then you should be able to backup/truncate.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 5, 2008 at 3:32 pm
SScrazy,
I've already started a database backup and backup log then truncate log. But I don't expect this to work because of the uncommitted transactions as you mention.
I did run DBCC OPENTRAN early today on the db and it appears to have some open trans.
It did NOT return "No active open transactions." it was 2 lines and the second line had a large value.
I killed all the SPids in this db long ago. I agree with you; it's the open transactions that are preventing me from truncating the (220 GB) log.
The server is down to 300 MB free space - ouch.
Thanks for the tip. John
February 5, 2008 at 3:40 pm
then try the COMMIT? you should at least TRY to commit said transactions.
you'd have to run it twice )once per transaction still open. The second one might take a LONG time to finish (if it's big like you said), but it being big probably means it was a big update.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 5, 2008 at 4:42 pm
Is the database in SIMPLE or FULL Recovery Mode? If it is in FULL, then you probably won't be able to truncate the log until it has been backed up. If that is the case, change it to SIMPLE mode, truncate the log (using BACKUP LOG WITH TRUNCATE ONLY), then if needed change it back to FULL mode.
-SQLBill
February 5, 2008 at 8:11 pm
SQLBill - It is now in Simple, before I started the backup. I have tried what you suggested. But it was originally Full causing the t-log to grow
"then try the COMMIT? you should at least TRY to commit said transactions." I don't think a COMMIT during the backup would work.
I will try it tomorrow - nothing to lose.
Someone on another forum suggested renaming the log file to force a new log file to be created. Would that work?
Thanks, John
February 5, 2008 at 11:24 pm
Renaming the log file might work. It's also quite likely to leave you with a suspect database.
Suggestions.
Set the database to simple recovery mode.
Run DBCC openTran. Note the spid that it returns.
Run sp_who2 <Spid> with the spid you got back from opentran to see what it's doing and where the connection came from. You can kill that spid, or you can find the app it's comming from (esp if its a querying tool) to force a commit. Commits work without problem during DB backups.
Note where the spid came from and what login for later fixing. If the spid has done a lot of work, this rollback may take a while.
Repeat the open tran and kill until opentran returns 'no active transactions' Note that opentran reports for a single database and only returns the oldest active transaction.
Once you have no more open transactions, run CHECKPOINT
In simple recovery mode, checkpoint truncates the log.
Run DBCC SQLPERF(LogSpace) and make sure that the log is now mostly empty.
Run DBCC shrinkFile to shrink the log (and only the log) down to a managable size.
Set the database to full recovery mode
Back up the database. Full DB backup. this is because the switch to simple broke the log chain.
Make sure you have regular log backups scheduled.
Once all is working again, see if you can use the info you got from sp_who2 to find the source of the uncommitted transactions so that this doesn't happen again.
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
February 6, 2008 at 3:27 pm
SSCrazy,
I set the db to Simple.
Then ran the DBCC OPENTRAN...
Transaction information for database 'EventsManager'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4881975:876:1)
Looks like this db may have been configured for Replication - but not within several months and there are no publishers, subscribers currently on this server.
I ran COMMIT TRANSACTION ;
and ROLLBACK TRANSACTION;
Didn't do anything.
I cleared a table then shrunk the data file - now have 11 GB free on the drive, some breathing room at least.
DBCC SQLPERF(LogSpace) shows 216GB and 100 percent used
There must be a way to force this log to clear. Any more ideas?
Thanks for the tips everyone has thought of.
John
February 6, 2008 at 3:40 pm
You're correct - "Oldest non-distributed LSN" means replication is still in place. It won't truncate the logs that haven't replicated.
Since it sounds like you DON't want replication on, turning off/disabling replication. Here's what you're looking for.
http://msdn2.microsoft.com/en-us/library/ms152757.aspx
If the replica is gone or can't be removed - then you will need to use the option mentioned in the Notes section (running sp_removedbreplication).
Once that is disabled - the logs should truncate.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 6, 2008 at 4:27 pm
Thanks Matt.
I've worked with replication a good bit but I needed to be reminded of that sp.
This is what worked:
exec sp_removedbreplication 'EventsManager', 'both';
then DBCC OPENTRAN showed no active open trans
a simple DBCC SHRINKDATABASE (EventsManager)
shrunk the db to 30MB !!
I have been struggling with this for 2 days - thanks for pointing me in the right direction.
Thank you GilaMonster as well.
Can we award points etc on this forum as we can on Tek-Tips?
John - Memphis TN USA
February 6, 2008 at 4:31 pm
No - over here you get points just for opening your mouth. As you can tell - I can barely keep my mouth shut...:hehe: Gila on the other hand has a much better way to phrase most things 🙂
That being said - happy to be of some help.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 6, 2008 at 4:41 pm
one parting thought - since you ran a shrink operation against the database itself at least once (I'm assuming that last shrink was against the LOG and not the database) - you will probably want to reorganize your database, rebuild your indexes, etc.... That SHRINKDATABASE will do a number on your perf.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 28, 2009 at 1:29 am
Matt,
You're great!!!!
I had a database build using SQLserver\Express with a log file of 6GB and truncation didn't work what ever I tried.
When I did
select [name], log_reuse_wait,log_reuse_wait_desc from sys.databases where database_id=22;
it returned Replication !!?? replication can't be turned on in a SQLserver\Express environment!!!
After a lot of searching I found your link to sp_removedbreplication and it cleaned the database from what ever was in there.
I then did
BACKUP LOG Gemini WITH TRUNCATE_ONLY;
DBCC SHRINKFILE ('Gemini_log',TRUNCATEONLY)
and I recovered many Gigs.
Thanks,
Rob
August 28, 2009 at 4:23 am
Rob de Vos (8/28/2009)
After a lot of searching I found your link to sp_removedbreplication and it cleaned the database from what ever was in there.I then did
BACKUP LOG Gemini WITH TRUNCATE_ONLY;
DBCC SHRINKFILE ('Gemini_log',TRUNCATEONLY)
Now either ensure that the DB is in Simple recovery or set up log backups. Also note that TruncateOnly is ignored when shrinking the log file, it's only valid for data files.
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