Increase Transaction Log size

SQL Server Database Engine

cherriesh


Hi,

How can I increase the transaction log size of my sql server 2005 database i can execute the shrink and backup log which can shrink the .ldf file to 1MB. However, since my dts involves a lot of inserts and updates, the my transaction log grows to 700MB in the middle of my dts execution. How can i increase the transaction log size maximum capacity to probably 1.5GB just so it can accommodate my full dts execution

BTW, this is my commands to shrink the log file:

DBCC SHRINKFILE(<dbname>_log, 1)

BACKUP LOG <dbname> WITH NO_LOG

DBCC SHRINKFILE(<dbname>_log, 1)

cherriesh

Top

Re: Increase Transaction Log size

Madhu K Nair


ALTER DATABASE YourDatabaseName

MODIFY FILE

(NAME = YourDatbase_log,

SIZE = 1500MB);

Or you can do it from SQL Server Management Studio -- Database Property WIndow

Madhu






Top

Re: Increase Transaction Log size

Alessandro Ventura

Hi,

If your transaction log doesn't autogrowth and you have enough space, you can increase the size of file with the following command:

ALTER DATABASE [{DBNAME}] MODIFY FILE ( NAME = N'{DBNAME}_log', SIZE = {nesizeinmbytes}MB)

You can modify it also by using management studio on database properties

Also i suggest you:

* if you use all the logfile during the batch don't shrink it continuisly (it affect the performance)

* your database is recovery model simple if not and you don't require transaction log for point in time restore, change it to simple in order to truncate automatically log on checkpoint

* if your database is small, recovery simple and you don't have a lot of storage, verify reason of transaction log growing (sometimes may be caused by insert task without batchsize parameters configured, or by massive updates that can be rewritten)

Regards,

Alessandro






Top