SQL 2008 cannot shrink transaction log

Hi Experts,

I have a reporting services 2008 database and some reports. When I run the reports in the report manager I get the following error message:

An error occurred during client rendering.
An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
The transaction log for database 'ReportServer' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I have tried all kinds of things to shrink the log, to no avail.

I tried swapping the database over to simple, then doing a manual shrink file, but it says:
Cannot shrink file '2' in database 'ReportServer2k8' to 128 pages as it only contains 72 pages.

I have set the database back to full, and taken a manual backup. But log_reuse_wait remains as Nothing, and thereby I think it's prventing the log to be truncated.

Any ideas?

Many thanks!!!

Who is Participating?

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

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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
how big is the transaction log file, actually?
Megan BrooksSQL Server ConsultantCommented:
Shrinking does not always occur immediately, and setting the recovery model to "simple" does not always shrink immediately either. You may need to shrink more than once. Here are two SQL Server 2008 Books Online topics that may help:
Factors that may delay log truncation: http://msdn.microsoft.com/en-us/library/ms345414.aspx (explains log_reuse_wait)
Shrinking the transaction log: http://msdn.microsoft.com/en-us/library/ms178037.aspx
I seem to remember that if the disk on which the transaction log resides is completely full, there may not be enough room to do a shrink. If that is the case, you might have to move something (else) to another drive first.

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
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 2008

From novice to tech pro — start learning today.