MS SQL Server 2005 Transaction log file seem to be too large

The database file is around 1 GB but the transaction log is 16 GB.  I've backup both, but how can shrink the transaction log file?  Do the transaction log need to be applied to the database?
Jack SeamanAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
DBCC SHRINKFILE (LogFileNamr, TRUNCATEONLY);
0
BrandonGalderisiCommented:
This is something that comes up so much that chapmandew wrote about it here:

http://sqlservernation.com/blogs/admin/archive/2009/05/17/why-is-my-log-file-so-big.aspx

Basically, you need to evaluate your recovery mode and your backup scheme AFTER reading the article.
0
Jack SeamanAuthor Commented:
Still unable to shrink the log file.  Always get:
Cannot shrink logfile 2 (Altiris_log) because all logical log files are in use.
Ran this command
DBCC OPENTRAN
No active open transactions.

Any ideas?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you only run the dbcc shrinkfile command, or the full article of Brandon?
0
Jack SeamanAuthor Commented:
Use the full article.  Nothing seems to work correctly.
0
Jack SeamanAuthor Commented:
I stopped and restarted the sql server and ran:
DBCC SHRINKFILE('Altiris_Log', 0)
go this message.  
Cannot shrink log file 2 (Altiris_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now the log file is bigger.  Any ideas
0
Jack SeamanAuthor Commented:
When I run
SELECT name, log_reuse_wait_desc FROM sys.databases
where name = 'Altiris'
I get
Name           Log_reuse_wait_desc
Altiris      REPLICATION

I am not running replication for any databases.
0
Jeyakumar_mcpCommented:
Right Click the database in Sql server Management Studio(SSMS).
Tasks -> Shrink -> select Files.
In File Type Dropdown, select log and click on OK with default options. It will resolve your problem.
0
Jack SeamanAuthor Commented:
Did not work.  Still will not release unused space.
0
Jack SeamanAuthor Commented:
Still unable to fix the problem.  I backup transaction log hourly, and the database daily.  Anyone have any ideas how to fix????
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please show the output of
DBCC OPENTRAN
0
Jack SeamanAuthor Commented:
Transaction information for database 'Altiris'.

Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (23474:26683:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, that explains it.
you have (had) replication in place, and the replication agent stopped/failed.
you have to solve that, by either getting the replication back up running, or remove the complete replication scenario.
0
Jack SeamanAuthor Commented:
I never had replication setup for this or any other database.  So, how do I fix this if replication was never set up.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I never had replication setup for this or any other database.
the output tells you that there was :)

anyhow, rtfm: http://support.microsoft.com/kb/324401
0
Jack SeamanAuthor Commented:
Ran sp_removedbreplication 'altiris'
successfully.
Ran DBCC SHRINKFILE('Altiris_Log', 500)
and got this error:
Cannot shrink log file 2 (Altiris_log) because all logical log files are in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any ideas how to fix?
0
Jack SeamanAuthor Commented:
And
output of DBCC opentran

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
now the old open transaction is gone, the db in full recovery mode, you have to FIRST run a transaction log backup (or 2), before the shrink will work (aka the error message)
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
Jack SeamanAuthor Commented:
Well, it finally worked.  Thanks for all your help.  Had the same issue with another database and was able to fix it by the same method.  
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.