Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Server Transaction Log

Posted on 2012-03-21
4
Medium Priority
?
252 Views
Last Modified: 2012-04-05
Hi,

We are putting around 7 million (70 lac) rows data into the SQL server from .net application using a single transactions. This process happens frequently in a single day and we cant use SSIS for this.

The issue is that after this process our database transaction log gets increased by 3 gb, due to this we are constantly running out of disk space.
To solve the disk space issue, temporarily we backup and restoring database (to clear the log files) but this is very unprofessional way and needs human intervention frequently.

Now what we are thinking of is below:
1) Database population starts
2) Log File gets increased automatically
3) Process Finishes (with transaction)
4) Now the Log file should get reduced to original size (of 100MB) with some sql command.

Please suggest some way to solve this problem.

Thanks
Ajay Sharma
0
Comment
Question by:Ajay Sharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 1000 total points
ID: 37746772
if you are in SQL Server 2008, a transaction log backup after the transaction will truncate the inactive part of the log and you have the option to shrink the log file using DBCC SHRINKFILE('logfile').

Is there any specific reasons for not using SSIS package? because SSIS is the most efficient way for transferring millions of rows, and as this is a bulk insert method, bulk insert process will not be logged in the transaction log file under BULK RECOVERY Model.
0
 
LVL 6

Assisted Solution

by:SJCFL-Admin
SJCFL-Admin earned 1000 total points
ID: 37747163
You have two choices.  Get the extra DASD or change your procedure to partition the inserts into smaller transaction groups.  Constantly expanding and shrinking your logfile is not recommended.  it is costly in terms of execution time and leads to DASD fragmentation (also costly in terms of execution time).  you will be shooting yourself in the foot if you do not deal with the root cause of the problem.

I give you this advice with the best of all intentions.  So that you do not constantly suffer from a quick fix...
0
 
LVL 18

Author Closing Comment

by:Ajay Sharma
ID: 37809536
Thanks for your inputs. I solved the issue by using below code:

alter database dbName set recovery simple
DBCC SHRINKFILE(@DatabaseLogFileName)
alter database dbName set recovery full

This seems a bit unprofessional way but it suits my requirements for time being.

One thing for future readers :
For SQL 2005, line number 2 will be sufficient
For SQL 2008, use all 3 lines
0
 
LVL 6

Expert Comment

by:SJCFL-Admin
ID: 37810807
Just one additional note for scheduling purposes.  Once the recovery model is flipped like this, you need to immediately take a full backup before you start your transaction log backups again.  :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question