Increasing transaction log with limited size

[Former Member]

How can we increase the size of transaction log .if we increase would it come as LOG2 in the new place.I want to set auto growth size as well but it should not affect existing LOG1 .A bit procedure would be helpful

[Matt_Fraser]

You can also just increase the size of the existing logfile. It's not necessary to add a second transaction log file. If you are using SQL2005, then in the Management Studio, right-click your database and choose Properties. In the Database Properties dialog box, select Files. Find the log file, and in the Initial Size (MB) column, set the size you want it to be. In the Autogrowth column click the button with the ellipsis and choose the options you want (default is "By 10 percent, unrestricted growth").

Usually your transaction log should be alone on its own dedicated disk drive, so 'unrestricted growth' works well since you want to give it as much headroom as possible in case it fills up (if it fills and can't grow any more, the database stops, whether it's filled the whole drive or not). The best plan, of course, is to monitor the usage of the log so that it never actually fills, making sure it's large enough to handle the expected maximum usage, and that it's backed up (and truncated) frequently enough to keep the size down. You can also set alerts in SQL Server to send an email and automatically kick off a transaction log backup job if it fills within a certain percentage of being full.

If you are in SQL2000, you can do the same thing from Enterprise Manager, choosing the Properties of the database, then selecting the Transaction Log tab and setting the Space Allocated and File Properties.

--Matt

[Former Member]

Hi,

In SQL Server Management Studio > right click on the Database <instance> > properties > filegroups > Click on Add

Here you enter the details of the new logfile you want to add.

You can look at the LOG1 parameters and copy the same parameters.

Thanks

Adil