Transaction log files keep growing

I can't get the transaction log files in SQL 2005 to shrink unless I do it in SQL manager. I now have one server where SQL manager will not even load so I can't shrink it that way. Shouldn't backing up the database and the transaction log files take care of it. I back it up with a sqlcmd command daily and I also tried backing up the database and the log files with NT backup but neither one works. The log files are now over 12 gig
ajdratchAsked:
Who is Participating?
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.

Faiga DiegelSr Database EngineerCommented:
Since you have already a fullbackup (I assume), try setting the recovery mode to simple then revert back to full.

0
Faiga DiegelSr Database EngineerCommented:
And you have the same inquirywith one memeber:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23144708.html

check it out what experts advice to his inquiry.
0
Aneesh RetnakaranDatabase AdministratorCommented:
Backing up of the Transaction log (dont confused with the regular database backup )  will take care of this
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ajdratchAuthor Commented:
I backed up the ".mdf" and the "_log.mdf." Is there anything else I need to backup? How do I set Auto_shrink mode
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
How are you backing up database?
0
ajdratchAuthor Commented:
I scheduled daily backups with the sqlcmd command and they have been running fine. I also ran an NT backup and backed up the two mdf files
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good :)
Just for remember: You can't shrink to less than the size of the file when it was created.

Can you see how much of the log file are occupied?
0
ajdratchAuthor Commented:
I can't get into SQL manager to see anything and I can't reinstall it. When I try to get in, I get the splash screen and then it goes away
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Did you try to reinstall client tools only?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Anyway you have a command line SQL. Look for OSQL.EXE in SQL Server 2005 TOOLS\BINN directory.
0
ajdratchAuthor Commented:
Yes, I tried client tools. What is the osql.exe command to truncate the log file?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
USE DatabaseName;
GO

DBCC SHRINKFILE(FileName);
GO
0
ajdratchAuthor Commented:
I'm not up on the osql commands (I'm a network guy who messes with SQL when I have to). I tried starting with osql -S servername -U administrator -P password but that did not work. This is in mixed mode.  This was upgraded from sqlexpress so sqlexpress is still the instance. Can you give me all the commands needed.

Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use OSQL -S servername -E (this option uses your current windows credentials)
0
ajdratchAuthor Commented:
I worked with Microsoft and we could not get it to shrink. Luckly the client is getting a new server next week so I'll be moving the database. I assume that will fix the problem for now. I still do not understand how to keep the log files from growing since a backup does not do it
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well if Microsoft didn't resolve the problem then I think you found a big bug there :)
Cheers
0
ajdratchAuthor Commented:
My next concern is that I was told that when I irestore the data to the new server, the log files will still be large. If that is true, is there a way to restore the data and get rid of a 25 gig log file?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's true. When you restore a database, the files will have the size when they was backed up.
You can try to shrink file after restore.
0
ajdratchAuthor Commented:
I copied the database to the new server and attached the database but not the log files. I then ended up with a small log file. If the transaction log file still grows, I guess I can always manually control it now that it is on a server that actually works.

It appears that the old server was just to messed up

Thanks for everyones help
0

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
lseemanCommented:
FYI: I had a LDF growing beyond 1GB which w/o regular backups.  The initial backup did NOT reduce the size.  However, when switching to Simple mode it decreased to approx. 4MB ;-)

Moral of the story for me: Regular full/diff backups of MDF/LDF and Simple mode recovery where applicable.

Ciao!
0
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 2005

From novice to tech pro — start learning today.