Solved

SQL Server Transaction Log

Posted on 2012-03-21
4
232 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

23 Experts available now in Live!

Get 1:1 Help Now