Solved

SQL Server Transaction Log

Posted on 2012-03-21
4
229 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
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Anuj earned 250 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 250 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now