how can increase log file size?

Question

HI All,

How i can increase my log file size ? I try to delete 7 millions record and there is an error message not allow me to delete successfully.

Transaction log file is full.

Thanks.

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST

Answer 1

Divide  your deletion with small batches something like that

WHILE 1 = 1
BEGIN

   DELETE TOP (500000)
   FROM tbl;

   IF @@ROWCOUNT < 500000 BREAK;

END

---

Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Set the autogrowth property of your log filet o unrestricted if not, check the incremental ratio.

if you still get the issue check out the free space on the disk... if there is no enough space available move the log file to additional disk.

(*** be very cautious with Production environment)

Answer 3

--The following is the T-SQL to create a new database called “MyDB”
--This database is stored in a folder called “Databases” on the local “D:” drive
--The database file is “MyDB.mdf”, the initial size of the file is 3 MB (3072 KB)
--The transaction log file is “MyDB_log.ldf”, the initial size of the file is 1 MB (1024 KB)
--NOTE: I’ve changed the FILEGROWTH of the log to a set size; this setting’s default is 10%

CREATE DATABASE [MyDB] ON PRIMARY(
 NAME = N'MyDB',FILENAME = N'D:\Databases\MyDB.mdf', SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON(
 NAME = N'MyDB_log',FILENAME = N'D:\Databases\MyDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB);
GO

--The following is T-SQL to grow the transaction log file from 1 MB to 20 MB
--In practice you would set this to the optimal size based on your expected transactional usages
--Use the following Query to increase your log size as per your expectation

ALTER DATABASE MyDB 
 MODIFY FILE (NAME = MyDB_log,SIZE = 20MB);
GO

you can find out how much space is used in my log by using the following:
DBCC SQLPERF (LOGSPACE)

Refer this also help  :-   http://support.microsoft.com/kb/873235/en-us

Answer 4

BACKUP LOG file of the database and the run DBCC SHRINKFILE to reduce the physical size and then see my previous post to run DELETE in small batches

---

Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

BACKUP LOG file of the database and the run DBCC SHRINKFILE to reduce the physical size and then see my previous post to run DELETE in small batches

---

Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Hi Uri,

 

Let me try this out by tomorrow. Thanks.

 

Sorry for late reply as too busy with other project. Yes it work now if delete in small batches. thanks for all reply. :)

---

MCP - SQL SERVER 2k/ WINDOWS XP , PROGRAMMER ANALYST