SQL Server Forum / Programming / SQL / July 2008
deletion cause transacation log to grow
|
|
Thread rating:  |
ChickenCoder - 19 Jul 2008 19:43 GMT SQL Express 2005
I delete the 31st day of data from my customers Production data to keep the database at a manageble size. I run the query below.
Delete From ProductionData Where ProdWorkDate < '6/19/2008'
I noticed that he transactioni log was growing out of control. I did not think it was coming from my trimmer and spent weeks tearing up my program; trying to determine the cause. At present, the customer's log is nearly 30 gbyte! I have tried a back-up and restore but the Truncate Transaction Log in grayed out. Maybe it is because we using the Express version.
I have also run DBCC SHRINKDATABASE and table but no change in size. Now I have discovered the cause and want to understand why a simple deletion statement is doing this, even though I know the deletion is completing successfully.
After the deletetion, I ran select * from sys.dm_tran_active_transactions and got back:
237 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 241 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 243 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 245 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 249 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 255 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 259 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 263 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 267 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 269 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 271 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 273 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 277 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 281 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 285 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 289 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 291 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 293 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 295 worktable 2008-07-19 09:31:15.373 2 NULL 2 0 0 0 0 0 527674 SELECT 2008-07-19 14:12:38.733 2 NULL 2 0 0 0 0 0
I must admit I am not up on Transaction Logs so I don't know what all of this means.
In my test, I deleted about 90,000 records (a typical day would be 230,000). My log went from 1024 kb to 219,264 kb. Since the entire transaction was successful, why should the transaction log be anything other then its minimum?
Thanks for any input
Aaron Bertrand [SQL Server MVP] - 19 Jul 2008 21:10 GMT What is your recovery model? When was the last time you backed up the database? How about the transaction log? Deletes are logged so that you can recover the activity. Once you back up the log, there is no need to keep it anymore, since you can recover from the log. But if you are not backing up the log, it needs to keep them around. Thus, the space in the log file cannot be re-used. You can try and use SHRINKDATABASE or SHRINKFILE until you are blue in the face, but you are not going to get anywhere. You need to either switch to simple recovery mode or you need to institute log backups. If you backup the log then right away you can say:
USE databasename; GO DBCC SHRINKFILE(<log_file_name>, <size_in_MB>);
Where <size_in_MB> is some reasonable number that will prevent the log from growing again under normal activity. Do not use SHRINKDATABASE for this.
And please have a look at Tibor's article, in order to help you understand the consequences of having the log file too small, which forces it to grow, and in turn can have you manually shrinking the file more often than you should (which ideally is never):
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
On 7/19/08 2:43 PM, in article uCqgk.1991$W64.1753@newsfe09.iad,
> SQL Express 2005 > [quoted text clipped - 49 lines] > > Thanks for any input ChickenCoder - 19 Jul 2008 21:50 GMT Thank you for your rapid reply. But here is a little backround.
My application runs in a poultry plant with limited intellectual resources. It is several states away. I have asked that they perform regular back-ups but they express no interest and probably do not know how (or care).
I guess I will log in and try to set up a backup schedule but I would prefer to simply delete the records without recording to the log. It seems that was an option in SQL 7, wasn't it? There is zero chance that they would ever want to retrieve those records.
Is it possible to delete records w/o making log entries?
> What is your recovery model? When was the last time you backed up the > database? How about the transaction log? Deletes are logged so that you [quoted text clipped - 6 lines] > to > institute log backups. If you backup the log then right away you can say: Aaron Bertrand [SQL Server MVP] - 19 Jul 2008 22:03 GMT No, you can't say, DELETE but don't log. If they are not interested in backups at all, then why can't you change to simple recovery model?
If you're going to log in and run a DELETE statement for them, then why can't you log in and run a DELETE statement and then a backup log statement?
On 7/19/08 4:50 PM, in article ytsgk.2800$W64.2036@newsfe09.iad,
> Thank you for your rapid reply. But here is a little backround. > [quoted text clipped - 19 lines] >> to >> institute log backups. If you backup the log then right away you can say: ChickenCoder - 19 Jul 2008 22:24 GMT I don't log in and DO STUFF!! I have an application that runs under the scheduler and that deletes the data. I think if I added the Back up to a stored procedure, I could call the SP from my database trimmer.
I assume there is no reason why I can't run a Back-up with the truncate log option from a SP, is there?
I noticed in these groups that discussions quickly digress into arguements and challenges. I don't know why. Many of us asking questions are not DBAs. If we were, we probably would not need to enter this group.
I am a software developer for the food processing industry. I write embedded and visual code and have been doing so for over 17 yrs.
All I want to do is have my program work and get paid for my work.
Thanks
> No, you can't say, DELETE but don't log. If they are not interested in > backups at all, then why can't you change to simple recovery model? [quoted text clipped - 35 lines] >>> institute log backups. If you backup the log then right away you can >>> say: Aaron Bertrand [SQL Server MVP] - 19 Jul 2008 22:34 GMT > I don't log in and DO STUFF!! Sorry, you weren't exactly clear about that. You mentioned logging in, and you also said "I delete..."
> I assume there is no reason why I can't run a Back-up with the truncate log > option from a SP, is there? Nope.
> I noticed in these groups that discussions quickly digress into arguements > and challenges. I don't know why. Many of us asking questions are not [quoted text clipped - 4 lines] > > All I want to do is have my program work and get paid for my work. I am trying to help you understand what you are doing, so that you get paid honestly. I am sorry if the way I am trying to help . I guess you could wait for the next person to come along and help, and see if they have different advice. <shrug>
ChickenCoder - 19 Jul 2008 22:56 GMT Maybe I am be over sensitive but I do appreciate your help.
I am sitting here running experiments. I have a data generator that creates a days worth of data.
I delete the data using my little app.
I check the LDF and it has grown as inspected.
I run a back up in what I assume to be "Simple Mode"
BACKUP LOG [GaincoOH] TO DISK = N'C:\SoftwareProjects\SpecialTools\DataTrimmerOH\GaincoOH.BAK' WITH NOFORMAT, NOINIT, NAME = N'GaincoOH-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO
I check the log size and it is the same.
I run the data generator again and then delete the data again and check the log size and it nearly doubles.
I try a restore but it fails and says it requires exclusive use. I have nothing connectted to the database.
What is going on here? How do I stop the Log from growing out of control.
Thanks
Erland Sommarskog - 19 Jul 2008 23:06 GMT > I run a back up in what I assume to be "Simple Mode" > [quoted text clipped - 3 lines] > NOREWIND, NOUNLOAD, STATS = 10 > GO No, simple recovery is something you change with the ALTER DATABSE command.
Actually, once the database in in simple recovery, you cannot backup the log at all, as it is not a meaningful operation. (The log is truncated every now and then.)
 Signature Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Andrew J. Kelly - 19 Jul 2008 23:12 GMT If you are really in simple recovery mode you cannot do a log backup and there is not reason to since you cannot recover anything from the log in Simple mode anyway. Since the client doesn't care about recovering their db I would put it in simple mode and then you don't have to worry about Log backups. But you or someone should still do regular FULL backups. As for the Log size if you switch to Simple mode you should be able to run a DBCC SHRINKFILE to get it down to a manageable size again. But you still need x amount of MB or GB of space to handle each days Deletes. But if you have long running open transactions you will not be able to reuse the space in the log from the previous deletes. If you run DBCC OPENTRAN() in that db does it say you have any long running open transactions? If so you need to either commit or roll them back and find our why you are not committing them.
 Signature Andrew J. Kelly SQL MVP Solid Quality Mentors
> Maybe I am be over sensitive but I do appreciate your help. > [quoted text clipped - 24 lines] > > Thanks Aaron Bertrand [SQL Server MVP] - 19 Jul 2008 23:49 GMT > I run a back up in what I assume to be "Simple Mode" > [quoted text clipped - 3 lines] > NOREWIND, NOUNLOAD, STATS = 10 > GO This is a backup log command, and confirms that your database is using the FULL recovery model (or perhaps BULK LOGGED but that is less likely). Otherwise, you would have received error Msg 4208 (The statement BACKUP LOG is not allowed while the recovery model is SIMPLE).
> I check the log size and it is the same. Right. A backup log command will backup entries in the log. But it will not shrink the size of the file itself. There is a big misconception around that backing up or truncating the log means the size of the .LDF file will shrink. This is only accomplished using SHRINKDATABASE or SHRINKFILE, and as I mentioned before (and pointed you to Tibor's great article about it), it should be done with care.
> I run the data generator again and then delete the data again and check the > log size and it nearly doubles. It is possible that your transaction log still had active transactions, and so the backup was not able to remove inactive portions of the log. What happens when you issue DBCC OPENTRAN against this database?
> I try a restore but it fails and says it requires exclusive use. I have > nothing connectted to the database. You count as a connection if you are in that database (meaning Query Analyzer, or Enterprise Manager, or Management Studio, etc.). But I'm not sure why you are trying to do a restore at this point, anyway. You are trying to get your log file under control, yes? If so, then as I suggested earlier, you have two possible courses of action:
(1) set the database to simple recovery mode. The log should not grow as long as you are committing your transactions and the system can self-regulate the free space in the log file. Note though that this means your customer will only be able to go back to the most recent full database backup if something should go wrong in the meantime. So, this option may not be acceptable. If it is, then you can take this script as a starting point to get where you are going:
USE master; GO ALTER DATABASE GaincoOH SET RECOVERY SIMPLE; GO -- let's make sure we have a current copy before we change anything: BACKUP DATABASE GaincoOH TO DISK = N'C:\whatever...' WITH <options>; GO -- let's BACKUP LOG GaincoOH WITH TRUNCATE_ONLY; -- note that this option is deprecated in SQL Server 2008 GO USE GaincoOH; GO DBCC SHRINKFILE(GaincoOH_Log, 128); GO
If the shrinkfile does not have the desired effect, then again, run DBCC OPENTRAN and see if you have open transactions in that database. If you don't, then try the shrinkfile again. If you do, you should be able to tell by the age of the transaction in the results if this is something current or something you can forcefully kill before attempting to shrink the file again. And I cannot stress enough that you gain nothing by making the log file ridiculously small... I have seen people bragging that they free up 20GB on the database server, but the next day when the application times out waiting for the log file to grow again, funny how they are nowhere to be found...
Now set up your nightly maintenance script (or more often, if necessary) to backup the database in full.
(2) leave the database in full recovery mode, but create a script that will back up your log periodically. I suggest more than once a day (here we typically do 10 minutes or 15 minutes). This will allow for greatest flexibility in restore to point of time, so the maximum amount of data loss (let's say if the server blows up) is 15 minutes, give or take. Of course if you put the backups on the same disk as the database, then it doesn't matter much, if the failure is disk (which is a common enough one that it is best practice to never put databases and backups on the same physical disk).
But you will still need to figure out why you have open transactions, if you expect the log file to maintain itself through backups (this is usually the reason why a log backup will not be able to free up space in the log file, and subsequently the log file keeps growing out of control).
A
Erland Sommarskog - 19 Jul 2008 23:04 GMT > I don't log in and DO STUFF!! I have an application that runs under the > scheduler and that deletes the data. [quoted text clipped - 12 lines] > > All I want to do is have my program work and get paid for my work. Unfortunately, in this case, this means that you need to understand recovery models and what impact they have on your application.
See it this way: when the DELETE statement runs, there is a power failure. Say now that the DELETE was in fact entirely unlogged. Assume further more that while the DELETE was running, SQL Server had deleted a row, but not the corresponding index entries, so that you would now have a corrupt database? SQL Server needs to have a transaction log so it can roll back the statement, to ensure that you have a consistent database.
So if your customer is not able to make the choice, you will need to do it for them. Do they need backups if their database goes belly-up? Of course, they do! But would they be content with restoring the latest backup, or would they need up-to-the point recovery? It's perfectly possible that if you ask your customer you will get blank stare in return. But say that disaster strikes, and they call you to tell you that their database is dead. If you now tell them they need to restore the most recent backup and lose one day of data, maybe the managing director have one or two words to tell you, and they might not be friendly.
If you believe that your customer is content with the most recent backup, set the database in simple recovery, and just add a BACKUP command to your nightly maintenance job, so that they at least have one backup.
If you don't have the guts to set the database in simple recovery, beside the BACKUP command, also add a BACKUP LOG command to the maintenance procedure, and then I mean a real BACKUP command that backs up the log and not just truncates it.
There is one more thing to note: apparently someone has taken a full backup of the database at one point. (Else the log wouldn't grow.) As long as you keep that 90 GB log, this means that they are still able to recover if the database file crashes. Thus, you may be careful with just throwing that log away, until you have a fresh databse backup.
 Signature Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
ChickenCoder - 20 Jul 2008 04:25 GMT Thanks ALL,
I appreciate all your help. I read all the links and spent the evening logged into my client. I set up the database backup to fire after I run my trimmer. After setting the mode to Simple, I was able to manage the Log. I will let it take care of itself.
I have tested eveerything and it works great.
Thanks again.
ChickenCoder - 20 Jul 2008 04:57 GMT Thanks ALL,
I appreciate all your help. I read all the links and spent the evening logged into my client. I set up the database backup to fire after I run my trimmer. After setting the mode to Simple, I was able to manage the Log. I will let it take care of itself.
I have tested eveerything and it works great.
Thanks again.
|
|
|