General question on Transaction Logs

[dwight1]

I recently had a problem of transaction logs occupying the entire space on the disk. Every time i run a DTS package it gave me an error "Transaction log full, backup the log and create space for the log".

I did research and came to know that i can reduce it by code
DBCC SHRINKFILE (2) it does clean up the logs but when i run the DTS, it gives me the same error message. Can any one please tell me how to control this ever increasing transaction logs as a best practice rules and also what is the standard log space alloted for a 100 mb datafile. How to overwrite this logs? I am using a simple recovery model.

Thanks for your help.

Dwight

 

[DrSql]

There are two way you can approch this.
What type of syntax you used in your package (Insert, delete).
One way is DBCC SHRINKFILE for a certain number of records. Option two is create a second Transaction log file on a bigger drive and set the max growth size on first Transaction Log (Drive Size-100 Mb).


Dr.Sql
Good Luck.

 

[dwight1]

DrSql,

I used Delete syntax in my package.

I got your idea, but did not understand how we create a second transaction log because i was under the impression that for each database we have only one tlog automatically generated by sql called db_log. So i create a second log file, when the first gets full tlog files occupy the second tlog drive space.

Thanks for your help.

Dwight

 

[DrSql]

Right Click on DB==> Properties
goto Transaction Log Tab.
You should see your TransLog.
FileName Location
DB_Log C:\Mssql\DB_Log.ldf
DB_Log1 D:\Mssql\DB_Log1.ldf

Dr.Sql
Good Luck.