Solved

How can I create a SP to Shrink the log? to be able to call it from a SQL Job.

Posted on 2008-10-28
9
220 Views
Last Modified: 2013-11-30
Hello:

I'd like to create a SP to shrink an SQLlog and then call it from a job, in the step after backing up the log.

create PROCEDURE truncatelog
DBCC SHRINKFILE (CEGE_SQL_Log, 1)

This gives me a syntax error.

Regards,
Joseph
0
Comment
Question by:CEGE
9 Comments
 
LVL 1

Author Comment

by:CEGE
ID: 22826827
hello:

What I'd like to do is do a database backup once a day and do log backups 3 times per day, and after each log backup I'd like to truncate the log, I have the log file limit set to 5GB, not unlimited growth. This seems to cover all of the bases should something bad happen. Is this correct? and not freeze up the server should the limit be breached.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 22826900
You should be able to use dbcc directly in the job steps. However, if you want to use a procedure, you will have to use something like this:

create PROCEDURE truncatelog

begin

  DBCC SHRINKFILE (CEGE_SQL_Log, 1)

end

Open in new window

0
 
LVL 1

Author Comment

by:CEGE
ID: 22827344
1) in the job step, it gave me this error:

Cannot shrink log file 2 (Goldmine_Log) because all logical log files are in use.

2) and it gave me a syntax error with Begin trying to create the SP:

What am I missing here?

thanks,
Joseph
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22827482
More importantly you should not be shrinking your log file.  Constantly growing and shrinking your file is not only extremely inefficient, it will fragment the ldf file across the disk making use of it by SQL server slower.

Your best bet is to find the maximum size you will EVER need, add 10%, and set it to a fixed size.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22827668
Since you are truncating the log, it would appear you have no interest in point-in-time restore, so why not set your Recovery Model to Simple, that way you
A) Don't have to backup your Transaction Log and more importantly
B) Don't have to shrink it repeatedly, either, which is a big no, no.
0
 
LVL 1

Author Comment

by:CEGE
ID: 22829204
hello acperkins:

The deal is that I do want a point in time restore and I also do want to do backups of the logs every 3 hours. But what I am not so sure about is the maximum size of the log, recently I set it the 1GB and it got hit after 2 weeks. I somehow would like to do the log backups and then have the file be as small as possible, but I don't want to repeatedly shrink it either. Does it get done automatically so that the max is never reached¿ What is the Best Practice for dealing with this? What do you do?
thanks,
Joseph
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 22829666
0
 
LVL 41

Accepted Solution

by:
graye earned 500 total points
ID: 22830496
From what I've read here, my guess is that real issue isn't "How do I shrink the log file"... the real issue is "How to I keep the log file from growing"
If you're up for some light reading, I'd suggest the following article as it explains the steps required to manage log file growth: http://home.hot.rr.com/graye/Articles/SQL_LogMaintenance.htm
0
 
LVL 1

Author Closing Comment

by:CEGE
ID: 31510960
Finally, this is just what I was looking for. Thank you.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 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

20 Experts available now in Live!

Get 1:1 Help Now