Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 22nd, 2004, 08:56 PM
Authorized User
 
Join Date: Jun 2003
Location: Shah Alam, Selangor, Malaysia.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Server - System log full

Hi,

My SQL Server appear system log full why it was happen and what i need to do if i facing this kind of problem? I using paltform Microsoft Windows 2000 server. it cause the SQL server slow down and cannot open at client workstation.



Thank and Regards
Reply With Quote
  #2 (permalink)  
Old December 23rd, 2004, 12:08 AM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

You have to make a choice on which recovery model to use for your database. For now backup your database read information about
Selecting a Recovery Model from books online. This will give you an idea on what you have to do to stop your logs from getting full

Microsoft® SQL Server™ provides three recovery models to:

Simplify recovery planning.

Simplify backup and recovery procedures.


Clarify tradeoffs between system operational requirements.
These models each address different needs for performance, disk and tape space, and protection against data loss. For example, when you choose a recovery model, you must consider the tradeoffs between the following business requirements:

Performance of large-scale operation (for example, index creation or bulk loads).


Data loss exposure (for example, the loss of committed transactions).


Transaction log space consumption.


Simplicity of backup and recovery procedures.
Depending on what operations you are performing, more than one model may be appropriate. After you have chosen a recovery model or models, plan the required backup and recovery procedures.

This table provides an overview of the benefits and implications of the three recovery models.

Recovery model
Benefits
Work loss exposure Recover to point in time?
Simple Permits high-performance bulk copy operations.
Reclaims log space to keep space requirements small.
 Changes since the most recent database or differential backup must be redone. Can recover to the end of any backup. Then changes must be redone.
Full No work is lost due to a lost or damaged data file.
Can recover to an arbitrary point in time (for example, prior to application or user error).
 Normally none.
If the log is damaged, changes since the most recent log backup must be redone.
 Can recover to any point in time.
Bulk-Logged Permits high-performance bulk copy operations.
Minimal log space is used by bulk operations.
 If the log is damaged, or bulk operations occurred since the most recent log backup, changes since that last backup must be redone.
Otherwise, no work is lost.
 Can recover to the end of any backup. Then changes must be redone.


When a database is created, it has the same recovery model as the model database. To alter the default recovery model, use ALTER DATABASE to change the recovery model of the model database. You set the recovery model with the RECOVERY clause of the ALTER DATABASE statement. For more information, see ALTER DATABASE.

Simple Recovery
Simple Recovery requires the least administration. In the Simple Recovery model, data is recoverable only to the most recent full database or differential backup. Transaction log backups are not used, and minimal transaction log space is used. After the log space is no longer needed for recovery from server failure, it is reused.

The Simple Recovery model is easier to manage than the Full or Bulk-Logged models, but at the expense of higher data loss exposure if a data file is damaged.



Important Simple Recovery is not an appropriate choice for production systems where loss of recent changes is unacceptable.


When using Simple Recovery, the backup interval should be long enough to keep the backup overhead from affecting production work, yet short enough to prevent the loss of significant amounts of data.

For more information, see Simple Recovery.

Full and Bulk-Logged Recovery
Full Recovery and Bulk-Logged Recovery models provide the greatest protection for data. These models rely on the transaction log to provide full recoverability and to prevent work loss in the broadest range of failure scenarios.

The Full Recovery model provides the most flexibility for recovering databases to an earlier point in time. For more information, see Full Recovery.

The Bulk-Logged model provides higher performance and lower log space consumption for certain large-scale operations (for example, create index or bulk copy). It does this at the expense of some flexibility of point-in-time recovery. For more information, see Bulk-Logged Recovery.

Because many databases undergo periods of bulk loading or index creation, you may want to switch between Bulk-Logged and Full Recovery models. For more information, see Switching Recovery Models.





Jaime E. Maccou
Reply With Quote
  #3 (permalink)  
Old December 23rd, 2004, 08:09 PM
Authorized User
 
Join Date: Jun 2003
Location: Shah Alam, Selangor, Malaysia.
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

just want to clarify it is not a database log full, system log full happen on Microsoft Window 2000 server. it appear message said event viewier is full.
Reply With Quote
  #4 (permalink)  
Old December 23rd, 2004, 10:10 PM
Friend of Wrox
 
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc
Default

In the event viewer you can set file size limitation and also you have an option to on what actions to take when the log files reach maximum size set. Right click on the log file and select properties and the option will be available.

Jaime E. Maccou
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
cannot attach to databases with SQL Server Full dhx10000 BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 0 October 30th, 2007 04:28 PM
Connection problems with Full Sql Server Lightwaver BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 13 February 12th, 2007 08:30 AM
SQL Server services Log On fizzerchris SQL Server 2005 1 July 7th, 2006 08:56 AM
transaction log full Jane SQL Server 2000 1 January 28th, 2004 09:59 AM
SQL server log is too big. khautinh SQL Server 2000 3 September 26th, 2003 11:51 AM



All times are GMT -4. The time now is 11:25 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.