Microsoft Knowledge Base Email Alertz
All KB/RSS Links  |  Hot Kb!  |  New Kb!  |  Scott Cate  |  FAQ / KB  |  Web Hosting  |  Unsubscribe  

(871237) - Describes an issue where the SQL transaction log file may grow to an unexpected file size, and you may receive an error message when you use PWA or when you log on to Project Server 2002 in Project 2002.

Search KbAlertz

Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.
Click here to create a
FREE account
Already have an account?
[Click here to Login]











Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks
Article ID: 871237 - Last Review: September 18, 2011 - Revision: 5.0

A database transaction log file grows unexpectedly in Microsoft Project Server 2002 and in Microsoft Project Server 2003

View products that this article applies to.

SYMPTOMS

When you use Microsoft Project Web Access or Microsoft Project Professional to log on to Microsoft Office Project Server 2003 or Microsoft Project Server 2002, you may experience one or more of the following symptoms:
  • A very large database transaction log file is created.
  • SQL transactions may not be completed successfully and may start to roll back.
  • SQL transactions may complete very slowly.
  • Performance issues may occur.
  • You receive the following error message:
    Error: 9002, Severity: 17, State: 2
    The log file for database '%.*ls' is full.
Back to the top

CAUSE

These symptoms may occur when a database transaction log is full. The database transaction log may grow quickly because of the many changes that Microsoft Office Project Professional 2003 or Microsoft Project Professional 2002 and Microsoft Project Server 2003 or Microsoft Project Server 2002 record in the transaction log.
Back to the top

RESOLUTION

To resolve this issue, truncate the database transaction log. To do this, follow these steps:

Microsoft SQL Server 2000:
  1. Start SQL Server Enterprise Manager. To do this, click Start, point to All Programs or Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the appropriate server, and then expand Databases.
  3. Right-click the ProjectServer database, point to All Tasks, and then click Shrink Database.
  4. In the Shrink Database - DatabaseName dialog box, click OK to keep the current values.
To reduce the growth of the database transaction log, follow these steps:
  1. Start SQL Server Enterprise Manager. To do this, click Start, point to All Programs or Programs, point to Microsoft SQL Server, and then click Enterprise Manager.
  2. Expand Microsoft SQL Servers, expand SQL Server Group, expand the appropriate server, and then expand Databases.
  3. Right-click the ProjectServer database, and then click Properties.
  4. Click the Options tab, and then under Recovery, click Simple in the Model box.

    Notes
    • After you set the recovery model to use the Simple option, you cannot restore the database to the point of failure or to a specific point in time.
    • For more information about the recovery options that are available in SQL Server, see the "Recovery Options" Help topic. To do this, follow these steps:
      1. Click Start, point to All Programs or Programs, point to Microsoft SQL Server, and then click Online Books.
      2. Click the Index tab, type recovery options, and then click Display.
      3. In the Setting Database Options topic, locate Recovery Options.
  5. Click the Transaction Log tab, and then do the following:
    1. Click to select the Automatically grow file check box.

      Note The default values will appear in the File growth section.
    2. Click Unrestricted file growth.
  6. Click OK to close the ProjectServer Properties dialog box.

Microsoft SQL Server 2005:

  1. Start SQL Server Management Studio. To do this, click Start, point to All Programs or Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
  2. Connect to the database Engine instance that is Hosting the Project Server Database(s).
  3. Expand the Databases.
  4. For Project Server 2003 and 2003, Right-click the ProjectServer database, point to Tasks, Point to Shrink, and then click Database.
  5. For Project Server 2007 and Project Server 2010 these steps should be completed for all 4 Project Server databases (ProjectServer_Archive, ProjectServer_Draft, ProjectServer_Published, ProjectServer_Reporting)
  6. In the Shrink Databae - DatabaseName dialog box, click OK to keep the current values.

To reduce the growth of the database transaction log, follow thest steps:
  1. Start SQL Server Management Studio. To do this, click Start, point to All Programs or Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
  2. Connect to the database Engine instance that is Hosting the Project Server database(s).
  3. Expand Databases
  4. For Project Server 2003 and 2002, right-click the ProjectServer database, and then click Properties.
  5. For Project Server 2007 and 2010, these steps should be completed for all 4 Project Server database (ProjectServer_Archive, ProjectServer_Draft, ProjectServer_Published, ProjectServer_Reporting).
  6. Click the Options tab, and then click Simple in the Recovery model box.
    • After you set the recovery model to use the Simple option, you cannot restore the database to the point of failure or to a specific point in time.
    • For mor information about the recovery options that are available in SQL Server, see the "Recovery Options" Help topic. To do this follow these steps:
      1. Click Start, point to All Programs or Programs, point to Microsoft SQL Server 2005, Point to Documentation and Tutorials, and then click SQL Server Books Online.
      2. Click the Index tab, type recovery options, and the click Display.
      3. In the Setting Database Options topic, locate Recover Options.
  8. Do the following if you are using Project Server 2003, This step is not necessary in Project Server 2007 and 2010. Click the Transaction Log tab, and then do the following:
    1. Click to select hte Automatically grow file check box. NOTE: The default values will appear in the File growth section.
    2. Click Unrestricted file growth.
  9. Click OK to close the ProjectServer Properties dialog box.

Microsoft SQL Server 2008:

  1. Start SQL Server Management Studio. To do this, click Start, point to All Programs or Programs, point to Microsoft SQL Server 2008, and the click SQL Server Managment Studio.
  2. Connect to the database Engine instance that is Hosting the Project Server database(s).
  3. Expand Databases.
  4. For Project Server 2003 and 2002, Right-click the ProjectServer database, point to Tasks, Point to Shrink, and then click Database.
  5. For Project Server 2007 and 2010, these steps should be completed for all 4 ProjectServer databases (ProjectServer_Archive, ProjectServer_Draft, ProjectServer_Published, ProjectServer_Reporting)
  6. In the Shrink Database - DatabaseName dialog box, click OK to keep the current values.

To reduce the growth of the database transaction log, follow these steps:
  1. Start SQL Server Management Studio. To do this, click Start, point to All Programs or Programs, point to Microsoft SQL Server 2008, and then SQL Server Management Studion.
  2. Connect to the database Engine instance that is Hosting the Project Server Database (s).
  3. Expand the databases.
  4. For Project Server 2003 and 2002, right-click the ProjectServer database, and then click Properties.
  5. For Project Server 2007 and 2010, these steps should be completed for all 4 Project Server Databases (ProjectServer_Archive, ProjectServer_Draft, ProjectServer_Published, ProjectServer_Reporting)
  6. Click the Options tab, and then click Simple in the Recovery model box.
    • After you set the recovery model to use the Simple option, you cannot restore the database to the point of failure or to a specific point in time.
    • For more information about the recovery options that are available in SQL Server, see the "Recover Options" Help topic. To do this, follow these steps:
      1. Click Start, point to All Programs or Programs, point to Microsoft SQL Server 2008, point to Documentation and Tutorials, and then click SQL Server Books Online.
      2. Click the Index tab, type recovery options, and then click Display.
      3. In the Setting Database Options topic, locate Recovery Options.
  7. Do the following if you are using Project Server 2003. This step is not necessary in Project Server 2007 and 2010. Click the Transaction Log tab, and then do the following:
    1. Click to select the Automatically grow file check box. NOTE: The default values will appear in the File growth section.
    2. Click Unrestricted file growth.
  8. Click OK to close the ProjectServer Properties dialog box.






For additional information about the steps to follow when the transaction log of a SQL Server database expands to an unacceptable limit, click the following article number to view the article in the Microsoft Knowledge Base:
873235  (http://kbalertz.com/Feedback.aspx?kbNumber=873235/ ) How to stop the transaction log of a SQL Server database from growing unexpectedly
Back to the top

MORE INFORMATION

Besides displaying the symptoms that are listed in the "Symptoms" section, SQL Server may mark databases that are suspect because of a lack of disk space for transaction log expansion.

For information about how to recover from this situation, see the "Insufficient Disk Space" topic in the SQL Server Books Online. Visit the following Microsoft Web site to view the SQL Server Books Online:
http://technet.microsoft.com/en-us/sqlserver/bb331756.aspx (http://technet.microsoft.com/en-us/sqlserver/bb331756.aspx)
Back to the top
APPLIES TO
  • Microsoft Office Project Server 2003
  • Microsoft Project Server 2002
  • Microsoft Office Project Web Access
Back to the top
Keywords: 
kbtshoot kbprb KB871237
Back to the top
        

Community Feedback System

Very often, it takes hours to solve a problem. Very often, you've looked high and low, and have tried a lot of solutions. When you finally found it, chances are, it was because someone else helped you. Here's your chance to give back. Use our community feedback tool to let others know what worked for you and what didn't.

Please also understand that the community feedback system is not warranted to be correct, it's simply a system that we've built to let people try and help each other. If something in a feedback response doesn't make sense to you, or you're not comfortable making changes that the feedback talks about (like registry edits), please consult a professional.

Thank you for using kbAlertz.com Feedback System.

-- Scott Cate

Anonymous User Report As Irrelevant  
Written: 8/30/2004 11:17 AM
If you change the recovery model from Full to Simple, and the server crashes, won't you lose the updates made since the most recent backup?!? I was told to NOT do this by a SQL database analyst. SQL Enterprise Manager states: - Simple Recovery allows the database to be recovered to the most recent backup. - Full Recovery allows the database to be recovered to the point of failure.

User Report As Irrelevant  
Written: 9/16/2005 3:45 AM
I think it is a problem, but its solved. You can make backup manualy. Stop SQL Server, then make archive of your Database. Then start it again. If server fails you extract your archive and Database will be restored.



Copyright © 2002-2007 KBALERTZ.COM.  All Rights Reserved. Terms / Privacy. Discount ASP.NET Hosting