Transaction Log file increasing

Question

Transaction Log file increasing

Simha24

SSCommitted

Points: 1984
More actions
January 25, 2012 at 3:29 am

#391398

Hi...
We are using SQL Server 2005 and having 2 userdb's in an instance.
We have a restoration job it will restore the backup file in salesdb and in Next step it will run procedure make some insertions into another purchasedb.
After completing the restoration job.. Log file size of purchasedb is getting increased to 13GB(getting diskspace issue). we are shining log file every day to gain disk space.
Recovery model of purchasedb :Simple
we have a backup plan on purchasedb.
Log is showing very huge size but it was not priniting any open transactions when i run DBCC OPENTRAN().
--where as i am inserting very huge amount of data (40to50lacks records).
what type necessary things i need to taken care to resolve this issue.
-- My developer are not build any indexes on those tables.
--how can i optimize there logic.
--Weather indexes make useful while inserting data

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

umasingh Ten Centuries Points: 1182 More actions · Answer 1

Simha24 (1/25/2012)
--where as i am inserting very huge amount of data (40to50lacks records).
what type necessary things i need to taken care to resolve this issue.
-- My developer are not build any indexes on those tables.
--how can i optimize there logic.
--Weather indexes make useful while inserting data

This is happening due to large size of data insertion and its obvious. if you are using bulk insert then I will suggest you to change the recovery model to bulk-logged. if not you can try with inserting data in batches so it wont increase your log file in that much extend. indexing won't help you in data insertion it will cause you more performance degradation.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

umasingh (1/25/2012)
if you are using bulk insert then I will suggest you to change the recovery model to bulk-logged.

He's already in simple recovery model, so he's already getting minimal logging is the operation supports it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Simha24 SSCommitted Points: 1984 More actions · Answer 3

I have an idea!!!!!

i would link to split the job.

I will mention only 1step in restore job--- it will do only restoration.

Then i will try to do insertion by batches or by bulk insertion through cmdshell .