back log dbname with no_log

Hi,

I'm issuing the following statements to reduce the size of transaction log, My database is in simple recovery mode:

CHECKPOINT

GO

backup log TMDATABASE with truncate_only

go

dbcc shrinkfile (TMDATABASE_Log,2)

GO

The log file size still remains the same. Even though there are no active or open transactions...

Any insight greatly appreciated....

Thanks,

Razi

Perhaps the Log file is at either its defined size, or there is no free space.

These are useful sources:

FileSize -How to stop the log file from growing
http://www.support.microsoft.com/?id=873235

FileSize -Log file filling up
http://www.support.microsoft.com/?id=110139

FileSize -Log File Grows too big
http://www.support.microsoft.com/?id=317375

FileSize -Log File issues
http://www.nigelrivett.net/TransactionLogFileGrows_1.html

Also refer to Books Online, Topic: 'DBCC ShrinkFile'.

The File Does Not Shrink

If the shrink operation runs without error, but the file does not appear to have changed in size, verify that the file has adequate free space to remove by performing one of the following operations:

Run the following query.

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files;

Run the DBCC SQLPERF command to return the space used in the transaction log.

|||

Hi,

Thanks for the reply but my log is still not shrinking. I tried using

"DBCC SQLPERF(logspace)" which shows Logsize = 499.875 and Log used = 0.04083052 0

This database in under compatibilty mode 80.

Also tried altering database file sizes using the GUI and ALTER DATABASE command but that didnt work since the initial size was larger.

There are no current connections to the database. Any insight?

Regards,

Razi

Autoshrink Log

Hello: I have a Database with a file log of 75 Mb which I have increased to 150 MB, but when i execute a "backup log with no_log" the size is returned to the initial size, I must mention that autoshrink is disabled.
:confused:with no_log or truncate_only they will only remove the checkpointed transactions in the log, it will not reduce the footprint (file size). If you need to shrink the file use dbcc shrinkfile. But be careful in this as if your log regularly has to grow, you are hurting your performance by constantly shrinking it.

HTH|||Hi :

I know that, but my problem is that i dont want to "shrink" the file but when i execute "Backup log" the file log is "shrinked".|||What syntax did you use to increase it?|||I'm executing "Backup Log NAME with no_log".

Results : Truncate the log and my file log that i increased to 150 Mb is decresed to the initial size of 75 Mb.

:confused:|||What syntax did you use to increase the transaction log?|||I'm using Enterprise Manager.|||I ran this multiple times and with simple and full recovery mode and can not duplicate what you are experiencing. If you can do what you are trying to do in SQL, plese post it, if you do not, run profiler while you make your changes in EM. See if this helps at all:

create database testing
use testing
dbcc sqlperf('logspace')

alter database testing
modify file
(NAME = testing_log,
SIZE = 20MB)

dbcc sqlperf ('logspace')

backup log testing with no_log

dbcc sqlperf ('logspace')|||I know that you can't duplicate my experience, that's why is a problem. but thats happen sometimes in SQL 7.0.