Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Programming / SQL / July 2008

Tip: Looking for answers? Try searching our database.

deletion cause transacation log to grow

Thread view: 
Enable EMail Alerts  Start New Thread
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.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.