How to shrink the log file

Greetings,

Can anyone advise me how to shrink the log file of a SQL Server database?
I have tried to follow the Microsoft article 272318 "Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE" but to no avail.
When I run the code:
 BACKUP LOG Projects WITH TRUNCATE_ONLY
the system tells me that the command has been run successfully but the log file size does not change.  Then if I run the code
 DBCC SHRINKFILE(Projects_log,2)
I get the error message:
"Server: Msg 8985, Level 16, State 1, Line 2
Could not locate file 'Projects_log' in sysfiles."

Can anyone please help?
IAmTheEggmanAsked:
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.

Thandava VallepalliCommented:
Hi,

USE xxxxx                              
BACKUP LOG xxxxxx WITH TRUNCATE_ONLY
DBCC SHRINKFILE(fileName, 20)  

Note: xxxx - Database Name
fileName: *.ldb file name of your database... not "Project_log".  
FileName, you can find from properties of your database, select Transaction Log Tab in that you can find ....

itsvtk
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
Thandava VallepalliCommented:
sorry its not *.ldb file,   its *.ldf file  

itsvtk
0
Thandava VallepalliCommented:
This is not for u, but others who have same problem in future...

http://support.microsoft.com/default.aspx?kbid=272318
http://support.microsoft.com/kb/256650/EN-US/

itsvtk
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sigmaconCommented:
itsvtk seems to describe what you already did. The commands both of you used will truncate your log file ONLY if your database recovery mode is set to SIMPLE. Otherwise, any backup will only reduce the log size to the last checkpoint (MinLSN). If you want more details about this, see Books Online (BOL). Find the BACKUP LOG index entry and then select 'truncating the transaction log' topic. You can check this option in Enterprise Manager, DB > Properties > Options > Recovery Model.

see also http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20651271.html
0
mcmonapCommented:
Hi IAmTheEggman,

I believe you need to run the DBCC SHRINKFILE from within the database you are shrinking, this may be the problem with not finding the file (this is as itsvtk mentioned).  The below should confirm the name you need to use with the dbcc command:

exec sp_helpdb Projects
0
IAmTheEggmanAuthor Commented:
Many thanks itsvtk, your solution worked just fine.
I appreciate the swift response.
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.