473,903 Members | 4,817 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

AutoShrink Transaction Log File

Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transactio n Log Backup" is set to remove
files older then 1hr.
5.) The "AutoShrink " option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First – Backup Log <database name> With Truncate_Only
Second – DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.
Jul 20 '05 #1
3 30123

"Bucfan1" <su*****@comcom systems.com> wrote in message
news:c2******** *************** ***@posting.goo gle.com...
Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transactio n Log Backup" is set to remove
files older then 1hr.
5.) The "AutoShrink " option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First - Backup Log <database name> With Truncate_Only
Second - DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.


It's not clear from your post how often you back up the transaction log. You
mention a database backup every night, but not a log backup. Only log
backups will free up space in the log - a full backup will not. When you
backup the log, the space will be reused for future transactions, or the log
will shrink if the space isn't needed. See "Transactio n Log Backups" in
Books Online for more information.

Simon
Jul 20 '05 #2
[posted and mailed, please reply in news]

Bucfan1 (su*****@comcom systems.com) writes:
I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transactio n Log Backup" is set to remove
files older then 1hr.
5.) The "AutoShrink " option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First – Backup Log <database name> With Truncate_Only
Second – DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.


To add to Simon's reponse: if you don't care about the transaction log,
and you don't need up-to-the-point recovery, but it is always to sufficient
from the nightly backup, your best option is simply to switch to
simple recovery.

In any case, I would advice against setting up some automatic shrink from
what you describe above. If you shrink the log, it will autogrow, and
autogrow takes resources and slows down the system.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

"Bucfan1" <su*****@comcom systems.com> wrote in message
news:c2******** *************** ***@posting.goo gle.com...
Hello All,

I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.) OS is Windows 2000 server sp3
2.) SQL Server 2000 (Build 2195) sp3
3.) Database Recovery Mode is set to Full
4.) Maintenance Plan for "Transactio n Log Backup" is set to remove
files older then 1hr.
Remove what files?

I don't see a step here about actually performing a transaction log backup.

5.) The "AutoShrink " option is on for the DB properties.

Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth.
This to me indicates that transaction log backups are not occurring.
When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First - Backup Log <database name> With Truncate_Only
Second - DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.


Note, the minute you do a backup log with truncate_only, you break the
transaction log chain and lose a lot of your recovery opportunities.

Also, avoid where possible shrinking the file, it can ultimately lead to
file level fragmentation.

Jul 20 '05 #4

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

Similar topics

13
1795
by: darkhorse | last post by:
Hi all, While doing a quite big "set of programs" for a university subject I've found myself in the middle of a problem someone surely has had before, so I'm looking for some help. At one point, I call a python cgi that pickle.load's a file, adds or deletes a registry and dumps the result again in the file. I'm worried that the cgi could be called simultaneously from two or more different computers, thus most probably corrupting the...
10
13356
by: TZoner | last post by:
1) Can one find the location of the 'Transaction Log' at: <Hard Disk>\Program Files\Microsoft SQL Server\MSSQL\Data\MyDb_Log.ldf? 2) Is it safe to delete it, as SQL will create a new Transaction Log when it realises none already exist? 3) When trying to do 2) I get error message that file is in use, even though SQL Server is closed. Any suggestions? 4) Can anyone shed light on the error message 'Write on ... <my path
1
5098
by: Krzysztof Rozmus | last post by:
Hi, I have stored procedure (MS SQL Server 2000) which operates on around 600 000 rows (SELECT, UPDATE, INSERT) and executes in 5 minutes, when I put it in SQL transaction it slows down to more than 5 hours (!!) I have to admit that it is not problem with data locks (beside that procedure
16
12648
by: Michael | last post by:
I have a data application in a2k that I need to create two fixed width text files and then combine them to a single file The first file is header information and the second is transaction data. I have tried and tried but just cant seem to get this right, I am using Queries to created my export files with specifications which works fine, I get stumped with the appending the header to my transaction file. What I have so far looks like...
5
5934
by: laststubborn | last post by:
Hi All, I have a dillema that I created 2 transaction file before I started to take log back. Now I changed my recovery model from simple to full. Do I still need to keep my second transaction file? If I remove it would it be a problem for my system and log back up? If somebody responds me I really appreciate it Regards
0
1494
by: vineetkumar | last post by:
dear all, i have a problem to maintain the Transaction Logging Mechanism in postgresql 8.1 so please send reply how i can maintain Rollback Segment, LogFiles, and every day transaction file . is there any facility in postgres because oracle provide archive
0
1060
by: Gerryteague | last post by:
Help My Transaction Log file is 180GB, my database file is 1GB, I am unable to backup the transaction file and I have now out of HD space. Can I delete the lof file and start again, or can I reduce it Gerry
0
3269
by: ramyass | last post by:
Hi all, I have SQL Server 2000 installed on my PC. A database has been created and i need to view the contents of LDF file. It would be great if any one of you let me know how to view the contents of the file or how to take the backup of transaction file alone when the transaction log option is not enabled in Backup database window. Regards, Ramya
0
1210
by: DanInTacoma | last post by:
We have a fairly straightforward maint plan on one of our servers (MSSQL 2K)...it backs up several DB's to a folder on another server (the one with the tape drive) so they can be archived to tape. The .BAK files have a "Keep for" time of 5 days, and that seems to be working Fine Business...the .TRN files have a "Keep for" time of 7 days, but they seem to be not deleting after that timeframe...or at all...when I noticed (after spilling over into...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9845
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
11279
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
10499
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9675
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
8047
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
7205
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();...
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
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.