how to shrink the transaction log file

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

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

.. and of course, disable the autogrow for the transaction log!!!!
Cheers

I would go for the sp_detach_db sp_attach_single_file_db option.

Make sure you backup the database first.

Both of these methods defeat the meaning of the transaction loggin.

???

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

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.

> 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.

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.

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

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.

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

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.

angellll, you mean that the whole logging is a crap that has no other use but to decorate SQL Server with some file.
Cheers.

There should be a better way. It would be nice if we could find a better way to do it. Wouldn't it?
Cheers.

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

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.

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.

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

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

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

i suspect you are using the database on Query Analyzer, did u ?
sp_detach_db should be able to work...

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.

Have you been helped here?  Is more needed?
":0)
Asta

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

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.

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