November 30, 2009 at 10:32 am
Hi,
I had a problem few days ago on a Dev server (i did not sweat too much thanks god)
where one transaction made the transaction log file grow until it ran out of disk space.
I tried to kill the transaction but too late so nothing happen it ran for a log time to no good.
Detached the Db to delete the log ..... nothing worked
I end up :
- stopping the service
- delete the log file
- restart the service
- restored a previous version of the Db
This is not a great way to do things.
what is the best way to handle this if it was in a production environment. (SQL2005)
I have Disk space Allert on my productions box
Thanks
November 30, 2009 at 10:36 am
Not much advice we can give you here as you haven't given us enough information to really help you out.
If you could provide details about the query that exploded the t-log, the actual code would be best, that would help.
November 30, 2009 at 10:51 am
with the limited information provided:
Plan for it with a lot of disk space
Better code control
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
November 30, 2009 at 4:16 pm
The best way to handle this in a production environment is to cap the size of the log file so it does not fill up the entire disk or you will have problems bringing SQL back online. Then, once it fills up you can issue a backup log followed by a shrinkfile to get the size back down manageable. Also, be careful of truncating log files (backup log XXX with truncate_only) in a production environment if you are running tran log backups as this will interrupt the log chain LSN's and the next tran log backup will result in a full backup.
Also, if this trnasaction needs to complete make sure you have enough log space to enable the trnasaction to complete. I recommend putting the log on its own partition so it is not competing with other databases for partition space.
Hope this helps
November 30, 2009 at 9:24 pm
atramard (11/30/2009)
Detached the Db to delete the log ..... nothing worked
Do not delete the transaction log unless you're comfortable with the chance of getting a suspect and unusable database afterwards.
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
November 30, 2009 at 9:28 pm
RCC-JAXDBA (11/30/2009)
The best way to handle this in a production environment is to cap the size of the log file so it does not fill up the entire disk or you will have problems bringing SQL back online. Then, once it fills up you can issue a backup log followed by a shrinkfile to get the size back down manageable. Also, be careful of truncating log files (backup log XXX with truncate_only) in a production environment if you are running tran log backups as this will interrupt the log chain LSN's and the next tran log backup will result in a full backup.Also, if this trnasaction needs to complete make sure you have enough log space to enable the trnasaction to complete. I recommend putting the log on its own partition so it is not competing with other databases for partition space.
Hope this helps
I don't see how setting the max size of the transaction log to something less than the size of the available disk space is going to solve anything considering it expanded to fill up the disk and the transaction failed.
What we need to see is the code being run to determine if it can be rewritten or enhanced to limit the growth of the transaction log.
November 30, 2009 at 9:28 pm
RCC-JAXDBA (11/30/2009)
Also, be careful of truncating log files (backup log XXX with truncate_only) in a production environment if you are running tran log backups as this will interrupt the log chain LSN's and the next tran log backup will result in a full backup.
If you truncate the log and are running log backups, the next log backup will fail, and log backups will continue failing until a full or differential backup is taken. SQL won't secretly run a full backup for you.
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
November 30, 2009 at 9:57 pm
I would say that when this happens best option is to first add additional log file size at the diff drive which has quite free space. Than take all remedy options like looking for transaction using it max, killing it, backing it up, putting limit on it etc.
November 30, 2009 at 10:04 pm
I would say that when this happens best option is to first add additional log file size at the diff drive which has quite free space. Than take all remedy options like looking for transaction using it max, killing it, backing it up, putting limit on it etc.
November 30, 2009 at 10:07 pm
As Lynn has mentioned in his first reply, there is nothing much to suggest since no technical details are presented. Let me make my attempt.
What was the transaction that caused the log to fill? Investigate that query / SP and see that, it does not run the query (or set of queries in a single transaction), instead break it down into smaller chunks of transactions.
I had been in the same situation couple of weeks back, when a genius inside our application developer woke up and started running a transaction that archives and 6 months (50 Million records) of data. All in a single transaction causing alerts into SQL Error Log and email alerts pounded when the transaction log failed (due to insufficient space).
I guess something similar (huge update, or deletes) must have occurred and check with that user who ran that query and see that is broken down into smaller chunks. (In our case I made the developer to move 10 days at a time).
All the replies before and including me have been based on guess work, what might have happened at your end.
Post with more technical information and would be better.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 30, 2009 at 11:51 pm
Does your backup strategy include log backups?
As you've been previously told, revise the code. Also if you do bulk data import / inserts, change the recovery model to "bulk logged" or "simple". BOL will guide you through the steps you need to take in this case.
December 1, 2009 at 2:21 am
Hi,
To Add a bit more information (let me know what you need),
The statememnt was a store procedure that delete a large number of data, it's working fine in production as it's running every 10 min but in dev it use tu run wenever they wanted it's now i a job that also run every 10 min.
The database size is
The database is on Simple mode.
Log file was 1Mb on the L drive and had 80Gb of free space, the log grew to take all the space.
The data file if 5Gb on the D drive.
I have put somme dummy file 1Gb that i can delete to release space (Last resort), but that may not work as the log file could still grow, and caping the log file would not sort the issue...
Wat i really wanted to know is how would i recover from the problem without having to restart the services or kill the Db
Thanks
December 1, 2009 at 4:12 am
Again, I think that you should revise that code.
For example, consider deleting the records "chunk by chunk" - i.e. not all records in a single DELETE operation.
Also I'm not sure if you keep your dev database on a separate machine - if not, you should...
December 1, 2009 at 4:45 am
All my Dev and Staging databases are on different box than production databases.
the Sp is fine as long as it's run regulary.
My question is more theorical what would be the best way to handle the problem once youve ran out of space.
Ie you get a Disk out of space allert what can you do to sort the server out once youve:
- shrink any file you can
- move DB's if you can to other drives
...
December 1, 2009 at 5:40 am
It is obvious that this procedure has not been run on a regular basis on the DEV system. You are going to have to modify the procedure there to work in small chunks, or batches, until you have deleted all the records that need to be deleted. As long as you are attempting to delete everything in one batch (transaction), you are going to have the problem with the transaction log filling up your disk drive.
Please read this article: Deleting Large Number of Records[/url].
I think it may help you.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply