Truncate Log in 2005 Maintenance plan always fails.

I have the typical need to bring down the size of a log file from 17 gig to maybe 2.0 gig   Heavy deletions in overnight processes.

My maintenance task is to call :
1) Backup DB to backup object.  Completes
2) Backup Log to backup object  -- Completes
3) Execute Script :
-- Get your space back to 2.  gig
DBCC SHRINKFILE (db_log, 2000)      << FAIL

Message in history is cut off telling me why?  

Any ideas on this?  

TIA
 
LVL 1
srussell705Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel_PLDB Expert/ArchitectCommented:
It's not good to shrink log files at all. It creates fragmentation, etc. Nevertheless, if you need to do it, did you try that command manually? What error did you get?
Have you tried?

-backup database,
-backup log,
-shrink log,
-backup log,
-shrink log
srussell705Author Commented:

thanks for the reply.


I think I found that I needed to add:
Use DBname
Go

On my local machine it is the only way to point the execute TSQL to the proper db.  

Wondering if anyone else can verify it?  This is a Fri night script so I have some time till it runs again.  
Umesh_MadapCommented:
if u r unable to shrink the logfile then, u can change the recovery model to simple then shrink log file and change the recovery model to full and take full backup.


Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

srussell705Author Commented:
Are you saying to :

1- backup Log
2- backup DB
3- Shrink Log?  

This is a weekly maintenance run for Full backups.  

I don't have room for

1- backup DB
2- backup Log
3- backup DB
3- Shrink Log?  

currently.  db is 65 gig and free space is 84 so I do not have room for two fulls.  
 
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Backup DB is not related to the log file backup, both are independent from each other (in most regards). You only need to backup log to be able to truncate it.

However, as already said - it is a bad idea. If you transaction log gets big, you need either to rethink procedures applied and causing a lot of logging not occuring in all-day use, or doing more log backups if it grows continuously.
Anthony PerkinsCommented:
Since you are already butchering your backups, which will render any attempt at doing a point-in time-restore futile, just change your Recovery Model to Simple.

Once you have done that, you can focus on getting your transaction log down to a more manageble size and defragment it (I suspect the resultset of DBCC LOGINFO() must be hitting the roof: In the hundreds of rows)  . What's more, you will no longer have to worry about backing up the Transaction Log.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Any ideas on this?  >>
Do as acperkins said.  Go to simple recovery mode and hope that will slow down your log growth.

Hope this helps..
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
No points please...
VENKAT KOKULLASQL Server DBACommented:
Hi,

Keeping in simple model doesn't slow down the performance/log growth, the only thing happens in between simple and full is:

In Full Model the SQL Engine doesnt truncates the log file

But in Simple Model SQL Engine is going to truncate the log file if its gets filled up to 80% depends upon the size that has been given.

--Kokulla
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Kokulla,
Keeping in simple model doesn't slow down the performance/log growth
Not true! Simple model allows reuse of previously allocated, no longer used transaction log space. Full model requires to leave all transactions stored in transaction log until backed up. There is a big difference in growth for that reason:
  simple = for all transactions: max(size of single transaction)
  full =  for all transactions: sum(size of single transaction).

The log file is never truncated just because of the recovery model.
Daniel_PLDB Expert/ArchitectCommented:
I'd put my 5 cents also. In simple recovery model checkpoint would not remove too much from t-log if there is long running transaction started earlier, so this still may be an issue in some cases ;)
Anthony PerkinsCommented:
VENKAT1242,
>>In Full Model the SQL Engine doesnt truncates the log file<<
Huh? Pray tell what does the Recovery model have to do with truncating the log file?

>>But in Simple Model SQL Engine is going to truncate the log file if its gets filled up to 80% depends upon the size that has been given.<<
Where in the world did you get that from?  May I suggest you read up on CHECKPOINT?

VENKAT KOKULLASQL Server DBACommented:
acperkins,

Please mind your words when talking with others, ok. First learn apart from studying the subject.
What i have heard thats what i mentioned above, dnt wnat to want divert others many un necessary comments likt you.

Hope u understand how to behave with others.....hehehheee
VENKAT KOKULLASQL Server DBACommented:
Please go though the lik provided, Mentioning Genius will not words dude...hahhaha

http://msdn.microsoft.com/en-us/library/ms189085.aspx
VENKAT KOKULLASQL Server DBACommented:

thank you... but i will accept that acperkins statement is true as i have already posted a link on the same....
srussell705Author Commented:
All we needed was a shrinkfile while the log was not being written to to reduce the size by 1000%
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.