Solved

how to shrink the transaction log file

Posted on 2001-09-11
29
388 Views
Last Modified: 2008-02-26
Anyone know how to without destroying the system cut the transactional log down to size in SQL server V7.

manon
0
Comment
Question by:manonng
  • 9
  • 9
  • 3
  • +7
29 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6473883
SQL:
exec sp_detachdb 'yourdatabase'

OS:
delete the .ldf file(s)

SQL:
exec sp_attachdb 'yourdatabase', 'c:\mssql7\data\yourdatabase.mdf'

If you database has more than 1 datafile (*.mdf), you should make the list (select * from yourdatabase..sysfiles)

Cheers


0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6473889
If you need to keep the system online, you can also do some TRUNCATE LOG statements, and then DBCC ShrinkFile (yourlogfile)...

BTW, if you have set your transaction log to be AUTOGROW, i recommend to give it a fixed size of approx 20-30% of the size of the data files, and then:
# either set the Truncate Log on Checkpoint to true
# use Transacation Log Backup regulary (every hour)

Cheers
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6473891
.. and of course, disable the autogrow for the transaction log!!!!
Cheers
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6474009
I would go for the sp_detach_db sp_attach_single_file_db option.

Make sure you backup the database first.
0
 
LVL 9

Expert Comment

by:miron
ID: 6474057
Both of these methods defeat the meaning of the transaction loggin.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6474093
???

The question is to reduce the size, i guess that it has grown to some GB... If you want to reduce the filesize, and then implement the transaction log backup, everything is fine.

Anyway, there are cases where transaction logging is really overhead that can be let aside... For example Datawarehouse database are typical candidates for this.

Cheers
0
 
LVL 9

Expert Comment

by:miron
ID: 6474124
Well, if the log is not needed for SQL 2K you can set to logging to simple recovery, or for SQL Server 7.0 truncate log on check point. But if you have ongoing transaction logging detaching the log file and forcing a new log file to be generated is somewhat means loosing all the logged data. It is not just the size - it is the data that matters. And of course, a used log space is desirable to be under control, but this is the trouble that we are asked to address.
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6474221
> But if you have ongoing transaction logging detaching the log file and forcing a new log file to be generated is somewhat means loosing all the logged data.

No, SQL Server prevents this. You can only detach a database if it's not in use (ie, no open transactions) and all dirty log pages will be flushed to disk before it is detached.
0
 
LVL 9

Expert Comment

by:miron
ID: 6474252
why would you keep transaction logging, if all the purpose of the log in this scenario is to be sacrificed in some ritual fashion to the sp_detach_db.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6474273
miron, the sp_detach_db is only used to reduce the size of the transaction log file ONCE, if the file has grown because of the AutoGrow option turned on. If that file is now 10 GB, and the datafiles are only 1 GB, you do the procedure ONCE to cut down the transaction log file to let's say 500 MB max...
After that, you won't use it again...
Of course, a backup should be done just before this operation for security...

Cheers
0
 
LVL 9

Expert Comment

by:miron
ID: 6474320
angellll, I think some different procedure or chain of procedures should exist to enable both, control of log file size and its use as intended, transaction log backup / up to the point in time recovery.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6474345
miron, the problem for SQL Server is that if the Autogrow option is enabled, the file will increase, even if the log has been truncated or backed up. That's crap, but's it's like that...
Cheers
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6474417
miron: SQL Server 7.0 is notoriously difficult about shrinking files. Any acceptable (read: immediate) method in existence can be considered a 'hack', just as this one. The sp_detach_db method just happens to be the easiest and quickest one provided you are in a scenario where you can take the database offline for a short while.

Of course the recommended methods are:
1. Don't let the database files grow to large in the first place
2. Use DBCC SHRINKFILE

Obviously it's to late for 1. The problem with 2 is that it does not immidiately shrink the file, but rather markes it for shrinking in the future. Especially in Log-files that have grown far to large the actual shrinking can be quite some time in the future, and is therefor not an acceptable method when you are pressed for disk-space.
0
 
LVL 9

Expert Comment

by:miron
ID: 6474422
angellll, you mean that the whole logging is a crap that has no other use but to decorate SQL Server with some file.
Cheers.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 9

Expert Comment

by:miron
ID: 6474493
There should be a better way. It would be nice if we could find a better way to do it. Wouldn't it?
Cheers.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6474542
miron,
no, of course not. Why do you think i mean this?

My knowledge of the transaction log in SQL Server is this:
The transaction log file is written to using a file pointer, and this pointer moves as there are transaction logged. If this pointer comes to the end of the files, it tries to grow the file, which can be done if the Autogrow option is turned on. If this isn't the case, SQL Server will try to move the pointer to the start of the file, but this could fail if there are still transactions not backed up nor truncated in the beginning of the file.
Now, using the backup transaction log, all committed transaction are marked as cleared in the transaction log file, and the log write pointer can "overwrite" these entries. If the pointer comes to the point where neither the file can be enlarger nor entries can be overwritten, SQL Server will fail to commit any new transactions and SQL code... The option Truncate Log On Checkpoint will clear any committed transaction automatically, so they are cleared as soon as the transaction are committed.
If the server "crashes" or needs to be recovered up to time X, this log files (and the transaction log backups) are NECESSARY, and i don't think is decoration.

The sp_detachdb will ONLY be used to create a new and clean transaction log file ONCE, thus i recommend that a full backup is performed...

If you don't see things the same way, i would like to hear where you disagree with me.

Cheers

Cheers

0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 6474569
Couldn't have said it better, AngelIII. In addition, SQL Server can only shrink the file by 'cutting off' a part from the end. Ergo, after a DBCC SHRINKFILE command, SQL Server has to wait for the pointer to recycle to the beginning of the file in order to cut off a large portion from the end - unless, of course, the pointer is already far enough to the beginning - which, according to Murphy, is never the case when you actually need the diskspace right away.
0
 
LVL 9

Expert Comment

by:miron
ID: 6474707
I am working with SQL Server 2000 from July 2000, and have seen SQL Server 7.0 only on occasions since than.
I would like to make sure that the DBCC SHRINCKFILE()
can be used with SQL Server 7.0 the same way I use it with SQL Server 2000 to reduce the used logspace and the logfile itself. I am pretty sure it is, but prefer to double check on it before posting a final answer.

Thanks.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6474738
As far as i know, SQL 7 works the same way regarding the DBCC shrinkfile.
Now, only if i understand correctly, you want to post an answer to this question? I think, prior to doing that, you should review the EE guidelines about posting comments/answers at the bottom of the page.

Regards
0
 
LVL 9

Expert Comment

by:miron
ID: 6474908
Here is my answer.

this code should shrink the log file size, leave a log backup file, and provide for continuous log backup with zero downtime / disaster recovery path up to the point of failure.

-- begin SQL code.
BACKUP LOG <db_name> TO DISK = 'C:\log'
UPDATE <t_name> set x = y where 0 = 1
UPDATE <t_name> set x = y where 0 = 1
UPDATE <t_name> set x = y where 0 = 1
UPDATE <t_name> set x = y where 0 = 1
UPDATE <t_name> set x = y where 0 = 1
UPDATE <t_name> set x = y where 0 = 1

DBCC SHRINKFILE ( <file_id>, NOTRUNCATE )
DBCC SHRINKFILE( <file_id>, # )
-- where # is desired size of log file
-- end of SQL code.

to locate log file id(s),
-- begin SQL
select fileid
from sysfiles
where FILEPROPERTY( [name], 'IsLogFile') = 1
-- end SQL
0
 

Author Comment

by:manonng
ID: 6474959
hi

I tries both method, but none of these work!

I have received an error message 'current database is in use' when I was performing the sp_detach_db command, but I sure I have shut down all the related apps.

then I performed the dbcc shrinkfile command, but the file size still remaining the same.

any help?

manon
0
 
LVL 6

Expert Comment

by:setiawan
ID: 6475050
i suspect you are using the database on Query Analyzer, did u ?
sp_detach_db should be able to work...
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 6475296
select * from sysprocesses where dbid=db_id('yourdatabase')

or also the exec sp_who2

will show you all the necessary information who is currently logged in to your database...
CHeers
0
 
LVL 9

Expert Comment

by:miron
ID: 6475300
did you try to run it like this

use master
ALTER DATABASE <db_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE <db_name> SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
execute sp_detach_db <db_name>


For shrinkfile not working, let me take a look at it. Shrinkfile will not work by itself, the block of code needs to be executed as a whole to make it work.

Thanks.
0
 
LVL 27

Expert Comment

by:Asta Cu
ID: 6836821
Have you been helped here?  Is more needed?
":0)
Asta
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 7147737
yeah all you have to do is to take a backup of the transaction log and it will truncate the log by default
0
 

Expert Comment

by:CleanupPing
ID: 9281601
manonng:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
LVL 34

Expert Comment

by:arbert
ID: 9552155
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Select AngelIII's comment

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Arbert
EE Cleanup Volunteer
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now