Gargantual Transaction Files

Can I delete them or otherwise make them smaller?
DB is to be read only, no deletions, no additions.open up Enterprise Manager, right click on the db name "All Tasks" -> "Srink Database" -> Check "Move pages to beginning" Click on the "OK" button.|||The Shrink DB taks in Enterprise Manager does work, but it does not give you much control. Also, it tends to be less than effective with regard to log files.

there are scripts out there that you can run through query analyzer that will work very effectively in shrinking the log file.

essentially, what you need to do is generate several small transactions on the log file. It may not be shrinking properly if there are some large transactions there. As you perform the small transactions, the larger ones will me moved down and eventually they will reside in a place where the log file will allow for them to be removed.|||Paul and BkBlitz2,
thanks for your reply, but "Shrink" did not work" at all.

First: it took some 4-5 min. to post nice message that "Database was shrinked successfully" which is less than 1/4 of the time it takes to create one lousy index on the same file which in turn tells me that nothing worth mentioning really happened.
Second: close to 40 GB of transaction files - which is more than data itself - is still close to 40 GB.
I don't need any of the transaction files. SQL Server assumes that I do but I am sure that I don't.
I need the space it occupies and time it takes to access it. The DB really is "read only" and a backup of if is good enough.

So, my (ignorant) question remains: how to shrink, delete, sell or give away my truckload of transaction files?|||I believe that when you issue a DBCC SHRINKDB only the non-active or unused protion will be shrunk. To see how much of the log can be shrunk issue DBCC SQLPERF ( LOGSPACE ) . This will return a recordset of all databases with their log file size and percentage used, so from here you should get an idea of how much it will shrink. Now you stated that this is a READ ONLY database, if true than issue BACKUP LOG <database_name> WITH TRUNCATE_ONLY. This will clean out the log. Only do this for readonly, since you won't be able to recover the database up to the minute. You would issue a BACKUP DATABASE after if this was a production database. Now try the DBCC SHRINKDB. Again since this is readonly you should then use sp_dboption with trunc. log on chkpt., so that the log will periodically truncate itself.|||The only way you can handle this is to

--1)
use <db_name_here>
go

--2)
/* truncation, i.e. cleaning old transactions stuff out of the file*/
BACKUP LOG <db_name_here> WITH truncate_only
go

--3)
/* real shrinking - does not always work for there might be an active transaction in the file, and it is somewhere at the very end*/
dbcc shrinkfile(<name_of_logfile_here>,1)
// note: you can find the log file name by running: select * from sysfiles

Thanks, this will shrink log file very quickly, and possibly right to 1,024k only!|||Hi

Thx for the tip, of how to backup a transaction logfile within 5 seconds to 1.024K !

My biggest problem in develloping my data warehouse !

Greetings

J