473,883 Members | 1,639 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Transaction log keeps growing

Hi,

We have created a SQL server 2000 database. We observe that the
transaction log keeps growing over time. We are now about to run out of
space. We have been periodically shrinking the database. Nevertheless
the size has increased. I would imagine that a transaction log can be
eliminated if we stop the database. Can that be done? Is there a way to
completely wipe off the transaction log?

Thanks,
Yash

Aug 24 '06 #1
4 13227
One thing you can do is:
1)Full backup of db
2)DETACH db
3)change the actual name of the ONLY the log file
4)Do a REATTACH , and when the pop up occurs enter a new name for a
transaction log file. This will create a new log file

----
Jack Vamvas
_______________ _______________ _____
Receive free SQL tips - www.ciquery.com/sqlserver.htm
Make SQL Server faster - www.quicksqlserver.com
_______________ _______________ _____
<ya****@gmail.c omwrote in message
news:11******** **************@ m73g2000cwd.goo glegroups.com.. .
Hi,

We have created a SQL server 2000 database. We observe that the
transaction log keeps growing over time. We are now about to run out of
space. We have been periodically shrinking the database. Nevertheless
the size has increased. I would imagine that a transaction log can be
eliminated if we stop the database. Can that be done? Is there a way to
completely wipe off the transaction log?

Thanks,
Yash

Aug 24 '06 #2
On 24.08.2006 17:01, Jack Vamvas wrote:
One thing you can do is:
1)Full backup of db
2)DETACH db
3)change the actual name of the ONLY the log file
4)Do a REATTACH , and when the pop up occurs enter a new name for a
transaction log file. This will create a new log file
Why do you not recommend a BACKUP LOG WITH NO_LOG (or a normal backup)
and then DBCC SHRINKFILE? Deleting the TX log seems a rather drastic
measure here.

Btw, please don't top post.

robert
Aug 24 '06 #3
On 24 Aug 2006 07:24:43 -0700, ya****@gmail.co m wrote:
>Hi,

We have created a SQL server 2000 database. We observe that the
transaction log keeps growing over time. We are now about to run out of
space. We have been periodically shrinking the database. Nevertheless
the size has increased. I would imagine that a transaction log can be
eliminated if we stop the database. Can that be done? Is there a way to
completely wipe off the transaction log?

Thanks,
Yash
Hi Yash,

Don't follow Jack's advice. This process is not completely fail-safe,
AFAIK, and not needed either. Besides, it would only address the
symptom, not the cause.

First, decide the amount of data loss your application can bear. Then
set the right options to ensure that the transaction log doesn't grow
endlessly. These options depend on how much data you're prepared to lose
in the event of a disaster.

If you're satisfied with the ability to restore the last full or
differential backup and lose changes made sinice then, the only thing
you have to do is change the recovery model to "simple". Once that is
done, the transaction log will stop growing and start reusing existing
space instead. See below for how (and if!) to shrink it.

If you can't afford to lose data but need the ability to restore to the
moment in time just before the disk crashed or you accidentally dropped
the orders table, you'll have to use the "full" recovery model (the
default). But you'll also have to schedule regular transaction log
backups, since full recovery prevents transaction log data from being
overwritten until it has been backed up. After scheduling log backups,
the transaction log will stop growing and start reusing existing space
instead. See below for how (and if!) to shrink it.

You might consider shrinking the transaction log. But you can also keep
it as it is, if you don't need the disk space. Be aware that after
shrinking the file, SQL Server will have to grow it (using autogrow)
back to it's normal working size. Autogrow is slow, and always kicks in
when your most critical process is executing. Shrinking a database or a
log should reallly only be done when something has caused it to grow
well beyond it's normal size, and only if you really have to reclaim the
disk space. For more information on how shrinking works, why you should
use it only sparingly and how to do it, read Tibor's article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp

--
Hugo Kornelis, SQL Server MVP
Aug 24 '06 #4
Thats an excellent reply. Here is the link that was mentioned above
about SHRINKING DATABASE IN SQL SERVER

Thanks to Tibor Karaszi's article

Overview

Introduced in SQL Server 7.0 was the ability automatically grow and to
shrink the physical size of database data and transaction log files.
Auto grow of files doesn't happen before the file is full, it happens
when new space is needed (like when an insert is performed), so the
user will wait the time it takes to grow until the modification is
completed.

Auto grow and shrink can be very useful under special circumstances,
for example after archiving data to some other location. However, we
often see DBA's doing shrink on a regular basis and the purpose of this
article is to explain some of the downsides of shrink and what actually
happens when you shrink a database file. Also, it is worth noticing
that the auto grow functionality was mainly added so the dba wouldn't
be paged in the middle of the night because the database is full. It
was never intended to be a high performance feature or to replace the
need to manage space usage, especially at the high end

Acknowledgement s
I like to thank SQL Server MVP Mark Allison,
http://www.markallison.co.uk, who has provided valuable suggestions and
input for this article.

Versions of SQL Server
This article applies to SQL Server 7.0, 2000 and 2005, where not noted
otherwise.

More information
You can shrink a database file using either DBCC SHRINKDATABASE (which
targets all files for the database) or DBCC SHRINKFILE (which targets a
particular database file). I prefer SHRINKFILE. I will not go through
the details of the commands here; they are documented in SQL Server
Books Online. Let us first determine what actually happens when you
shrink a database file:

Shrinking of data file
When you shrink a data file, SQL Server will first move pages towards
the beginning of the file. This frees up space at the end of the file
and the file can then be shrunk (or as I like to view it: "cut off at
the end").

Shrinking of transaction log file
SQL Server cannot move log records from the end of the log file toward
the beginning of the log file. This means that SQL Server can only cut
down the file size if the file is empty at the end of the file. The
end-most log record sets the limit of how much the transaction log can
be shrunk. A transaction log file is shrunk in units of Virtual Log
Files (VLF). You can see the VLF layout using the undocumented DBCC
LOGINFO command, which returns one row per virtual log file for the
database:

DBCC LOGINFO('myData base')

FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
2 253952 8192 11 0 128 0
2 253952 262144 13 0 128 0
2 270336 516096 12 0 128 700000002500028 8
2 262144 786432 14 2 128 900000000840024 6

The interesting column is "Status". 0 means that the VLF is not in use
and 2 means that it is in use. In my example, I have 2 at the end of
the file (read result from top to bottom) and this means that the file
cannot currently be shrunk.
In 7.0, you have to generate dummy transactions so that the usage of
the log file wraps toward the beginning of the file. You can then empty
the log file using BACKUP LOG and then shrink the file.
In SQL Server 2000, the generation of dummy log records is done for you
when you execute the DBCC SHRINKFILE command.
What you end up doing is BACKUP LOG, DBCC SHRINKFILE several times.
Investigate the layout of the log file using DBCC LOGINFO in between.

If you have loads of VLF (many rows returned from DBCC LOGINFO), you
probably had a small file size for the log initially and then had lots
of small autogrow. Having lots of VLF is a bit of a performance hit. If
this is your case, consider shrinking the log file to a very small size
and then expand the file size to something comfortable (a bigger size).
Here are some articles specifically about management of log file size:
How to Shrink the SQL Server 7.0 Transaction Log
Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
Log File Grows too big
Log file filling up
Considerations for Autogrow and AutoShrink

So what is the problem? Why shouldn't I shrink database files on a
regular basis?
Have a look at below list and then you can determine for yourself
whether or not you want to shrink database files regularly:
Each page moved will be logged to the transaction log. Say you have a
database using 3GB of data and indexes, the log file will need 3GB
space for the shrink. This 3GB will also be included in the following
transaction log backup. This doesn't seem to happen if the database is
in simple recovery mode. (Applies to shrinking of data files.)
After the shrink, as users add rows etc in the database, the file has
to grow again. Growing a database file is an expensive operation, it
hurts performance. During the grow operation, some modifications will
be blocked until the grow operation has finished. (Applies to shrinking
of both data and log files.)

SQL Server 2005:
SQL Server 2005 has "instant file initialization" which means that
database files can be created and also grow very fast; Windows doesn't
"zero out" the data in the database file. Instant file initialization
is only available for data files, not log files. Also, instance file
initialization requires that the service account for the SQL Server
service has the SE_MANAGE_VOLUM E_NAME windows privilige.
There are situations where autogrow doesn't "catch up" with the space
usage requirements. This will result in an error message from SQL
Server when the modification is performed, returned to the client
application: error 1105 if data is full and 9002 if log is full.
(Applies to shrinking of both data and log files.)
Moving datapages around will fragment your database. (Applies to
shrinking of data files.)
Heavy shrinking and growing of database files will fragment your file
system, which will hurt performance even more. (Applies to shrinking of
both data and log files.)
Conclusion
Only you can determine in the end whether you want to shrink or not.
With above information, you hopefully have enough to go on when making
that decision.
Transact-SQL code
Below you find the T-SQL code I ran to prove that shrinking of a
database will generate the same amount of transaction log records as
the amount of data which was moved:

--Script to show that shrink produces a lot of log record, about same
amount as the data which is moved.
USE master
GO
DROP DATABASE shrink_test
GO
CREATE DATABASE shrink_test
ON PRIMARY
( NAME = shrink_test_dat a,
FILENAME = N'c:\shrink_tes t_data.mdf',
SIZE = 2MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)
LOG ON
( NAME = shrink_test_log ,
FILENAME = N'c:\shrink_tes t_log.ldf',
SIZE = 3MB,
MAXSIZE = 200MB,
FILEGROWTH = 10%)

GO

SET NOCOUNT ON
USE shrink_test
CREATE TABLE t(c1 int identity CONSTRAINT PK_shrink_test PRIMARY KEY,
c2 char(3000) default 'hello')

DECLARE @i int
SET @i = 1
WHILE @i <= 40000
BEGIN
INSERT t DEFAULT VALUES
IF @i%100 = 0 --Truncate log on every 100'th row
BACKUP LOG shrink_test WITH NO_LOG
SET @i = @i + 1
END
SELECT COUNT(c1) AS "Number of rows before delete, should be 40000"
FROM t

--Delete every other row in the table, in a loop so log doesn't grow!
--DECLARE @i int --Remove comment if not run all in one batch
SET @i = 1
WHILE @i <= 40000
BEGIN
-- IF @i%2 = 0
IF @i<20000
DELETE FROM t WHERE c1 = @i
SET @i = @i + 1
END
SELECT COUNT(c1) AS "Number of rows after delete, shuld be 20000" FROM
t

--Make sure the tlog file doesn't auto-truncate
BACKUP DATABASE shrink_test TO DISK = 'NUL'

--Should be large
EXEC master..xp_getf iledetails 'c:\shrink_test _data.mdf'
--Should be small
EXEC master..xp_getf iledetails 'c:\shrink_test _log.ldf'

GO
--Seems we need a waitfor in order for the shrink to do its job
WAITFOR DELAY '00:02:00'

GO
--This shrink might now produce a lot of log record as 20000 rows will
be moved!
DBCC SHRINKFILE (shrink_test_da ta, 40)

--So, did the log grow?
EXEC master..xp_getf iledetails 'c:\shrink_test _log.ldf'

--My result, the data file shrunk to 80MB and the log file grew from
3MB to 76MB!!!
GO
USE master
GO
DROP DATABASE shrink_test

Aug 25 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
7535
by: noah | last post by:
Does PHP have a feature to associate Cookie sessions with a persistent database connection that will allow a single transaction across multiple HTTP requests? Here is how I imagine my process: I have an series of interactive HTML forms. The user begins a Cookie session. A database connection is opened and a transaction is begun. After the user goes through any number of pages where they update the database they finish on a page where...
10
13287
by: Jay Chan | last post by:
I keep getting the following error message when I run a serie of SQL commands: Server: Msg 9002, Level 17, State 6, Line 15 The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space. I have tried "dump transaction tempdb with no_log" right before I run the SQL command. But that doesn't help.
1
12990
by: Rittercorp | last post by:
I am debugging an app which blocks many processes in a SQL7 server DB. The app log writes every transaction "open" and "close". The weird thing is : when the app logfile says the transaction is dropped (object closed) the db keeps showing the process "running", in a sleeping mode, with open_tran in 2 or even 3 and in an awaiting command status. We are working on NT4.0, SP6a (all fixes up to date, MDAC 2.7SP1) on IIS side, an equal box...
1
465
by: m3ckon | last post by:
Can someone please help me ... the transaction Log on my database is getting ridiculously large and I'm at a loss as to what to do?? The Db in question is in SQLServer 2000 and is on a web server. The data in the database is sent new data from access on a 15 minute basis from our company (the access mdb is uploaded then imported using the DTS) Because of this, the log file in SQL Server is growing at an alarming rate and I neeed to get...
6
3653
by: Prince Kumar | last post by:
I have a requirement where I have to generate sequnce like numbers within another key. My table is like, custrec ------- cust# seq# 10 1 10 2
1
1606
by: Hoon | last post by:
It seems that asp.net worker process keeps growing and it slows the web server down. Does anybody have an idea what could trigger this kind of problem??? thanks in advance, Hoon
3
2278
by: perspolis | last post by:
Hi all I have a master detail table which I use them in Transaction to Update them. in some cases the detail table generates error and I Rollback transaction but RowState of master table dosen't go back to previous state and no data has entered in database yet but in my master table in memory it has data.. ???? how can I change RowState of master table to previous RowState before begining Save. thanks in advance
3
2703
by: sifrah | last post by:
Hi All, My SQL server transaction log is getting bigger every day and my HDD if running out of space. So i follow the MS KB about how to Shrinking the Transaction Log. After doing so the log is much much smaller as i can see the size of it under enterprise manager. The problem is that the HDD still shows the same size. If i shrink the DB why the and reduce its size why the HDD does not
4
4223
by: Trevor Best | last post by:
I have a client with 150MB database, the transaction log file is nearly 23GB. It's a PITA for me to backup his data and restore it on my server as it takes about 30 minutes to restore as it re-creates a 23GB file with no transactions in it :-\ As well as the space taken up by it, does it do any harm being that size? e.g. does it decrease performance? While it's there, considering the database is backed up daily, does it actually do...
0
9793
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11151
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10752
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9582
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7974
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7134
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5804
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4619
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3238
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.