Unable to shrink log file in Sql 2005

I am trying to shrink a log file that grew exponentially. From Management console I backed up the DB and  tried to shrink the the log file but nothing is changing. Do i have to do anything different?
SvlssAsked:
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.

arnoldCommented:
The only thing often prevents the shrinking of a log file is an active transaction.
Dbcc opentran(database)
Only when this transaction is terminated will you be able to shrink the LogFiles.
Brian CroweDatabase AdministratorCommented:
Following up on what arnold said.

Typically you will need to put the database in SINGLE_USER mode which may not be feasible for business purposes depending on how your database is used.

USE Master;
GO

ALTER DATABASE <db_name>
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Open in new window


DBCC SHRINKFILE(<logical filename>, <desired size in MB>)

When you are done, don't forget to return the database to MULTI_USER.

ALTER DATABASE <db_name>
SET MULTI_USER

Open in new window

Michael DyerSenior Systems Support AnalystCommented:
Sometimes the indexes for the tables can take a lot of space.  You can reindex each table by running the following command on the tables in the database

dbcc dbreindex ('table_name','',70)

run the shrink after you are done and this may free up more space
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

arnoldCommented:
The shrink is on the transaction log file not on the DB files. Reindex is not advisable as that will add to the transaction log.
SvlssAuthor Commented:
Brian,

I rolled back and tried to shrink with the command but it gave me below message

DBCC SHRINKFILE (mydb_log.LDF, '8968')

Incorrect syntax near '.'.

Michel,
Do i have to run that command on each table?

Arnold,

Yes i am trying to shrink log file (.ldf)
SvlssAuthor Commented:
All,

I was able to shrink the log file by switching the "recovery model" of the database to "simple"

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
arnoldCommented:
Note that simply shriking the log file now, does not resolve the reason it expanded.  The change in recovery model if left permanently could have an adverse impact when a restore to a point in time is needed.
If left unresolved, and recovery mode returned to Full, you will once again run into this same situation as you've not resolved its cause.

A change to simple with an active transaction would not have allowed you to shrink the DB meaning the active transaction preventing the shrinking of the log file had been terminated, i.e. SSMS session you have was closed and reopened.
SvlssAuthor Commented:
Found the solution
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.