transaction log is full

hi,

if my transaction log is full, do i need to execute only this command to truncate old logs

Full recovery model.

Hi,

What if i want to retain it to Full Recovery Model. You mentioned about "increasing the TL backup frequency .... you dont need to truncate the log then." Do you mean that with this i need to execute the below command several times during my dts process just for log not to pile up Is this what you mean by TL backup frequency you mentioned that i dont need to truncate the log. But executing this statement will truncate my logs right Sorry, I'm just new to this stuff.

DBCC SHRINKFILE(<dbname>, 1)

BACKUP LOG <dbname> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<dbname>, 1)

Also, how can i modify the allocated space for my TL I want to change it to 5GB since it is set now to 2GB. I tried modifying it in the Database properties > Files. But I can't modify it.

cherriesh

No... This command will truncate and shrink the file.. there are two issue when you do this

(a) The backup chain breaks. First step after truncating and shrinking the db should be Full Backup otherwise the TL backup chain breaks and the TL Backup becomes invalid

(b) System needs space for normal function and that too in Full recovery model the Inactive part of Log is not reused. System has to allocate space and its an overhead

If you take TL backup , then only you need full recovery model. What is the current frequency of TL backup (like every hour/two hour etc)

To take TL backup :

BACKUP LOG MyNwind TO disk='F:\yourTL.bak'

Refer this KB

http://support.microsoft.com/kb/873235

How to increase TL Size

ALTER DATABASE YourDatabaseName

MODIFY FILE

(NAME = YourDatbase_log,

SIZE = 5000MB);

Madhu

Hi,

I will set the frequency of the TL log backup to daily since this project is a data warehouse, and the dts runs only once a day. then after i execute the TL log backup, i will shrink the log file through DBCC shrinkfile. Then I will have a full backup of the database.

I have already altered the log file size up to 5000MB. When I checked the Database properties > Files, the mydb_log's initial size (MB) was changed to 5000.

I noticed another column which is the autogrowth and it's set to enable. (File Growth in Megabytes = 1; Maximum File Size Restricted File Growth (MB) = 2,097,152. If autogrowth is enabled upto 2GB and my log size just reached up to 700MB, i shouldn't have been prompted with "Transaction log is full" right

your response is greatly appreciated!

cherriesh

Hi,

I will not do any truncate and shrink process before the Backup LOG. but i will do a shrink just after the Backup log is executed.

cherriesh

Dear cherriesh,

i had the same problem and solved it.

the log is growing because of bulk copy of data. transactions are not deleted from log file at the end of importing data.

i used the same Microsoft KB article mentioned in a previous reply http://support.microsoft.com/kb/873235 and used the same commands as a daily scheduled windows task(batch file).

i have also configured sql to send me an email when size of log file is greater than 1GB, this way i can control the growth.

you could add at the end:

DBCC ShrinkDatabase(dbname)

P.S: you can leave the new size of the log and database blank.