BlackBerry Forums Support Community               

Closed Thread
 
LinkBack Thread Tools
Old 06-20-2005, 04:45 PM   #1 (permalink)
Talking BlackBerry Encyclopedia
 
udontknowjack's Avatar
 
Join Date: Aug 2004
Location: State of Confusion
Model: 9000
Carrier: T-Mobile
Posts: 483
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default My Transaction log is over 3GB!!

Please Login to Remove!

Anyone know why my SQL transaction log would be over 3gb. We did a full backup over the weekend which should have shrunk the log. The database is only 150mb.
We are running BES 4.0 SP1, Exchange 2003, with SQL 2000 on a seperate Box.
Offline  
Old 06-20-2005, 04:48 PM   #2 (permalink)
Thumbs Must Hurt
 
Join Date: Mar 2005
Location: Denmark
Model: 7230
Carrier: TDC
Posts: 102
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by udontknowjack
Anyone know why my SQL transaction log would be over 3gb. We did a full backup over the weekend which should have shrunk the log. The database is only 150mb.
We are running BES 4.0 SP1, Exchange 2003, with SQL 2000 on a seperate Box.
If you have full logging enabled it will get huge.

Check properties on the database and make sure its set to "simple logging"

Change to simple if needed and shrink the database manually
Offline  
Old 06-20-2005, 04:53 PM   #3 (permalink)
Talking BlackBerry Encyclopedia
 
udontknowjack's Avatar
 
Join Date: Aug 2004
Location: State of Confusion
Model: 9000
Carrier: T-Mobile
Posts: 483
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

So what is the difference between the two?
Offline  
Old 06-20-2005, 05:24 PM   #4 (permalink)
Thumbs Must Hurt
 
Join Date: Mar 2005
Location: Denmark
Model: 7230
Carrier: TDC
Posts: 102
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by udontknowjack
So what is the difference between the two?

Stolen from MSSQL online help.


The Full Recovery model uses database backups and transaction log backups to provide complete protection against media failure. If one or more data files is damaged, media recovery can restore all committed transactions. In-process transactions are rolled back.

Full Recovery provides the ability to recover the database to the point of failure or to a specific point in time. To guarantee this degree of recoverability, all operations, including bulk operations such as SELECT INTO, CREATE INDEX, and bulk loading data, are fully logged.

--

Simple Recovery
With the Simple Recovery model, the database can be recovered to the point of the last backup. However, you cannot restore the database to the point of failure or to a specific point in time. To do that, choose either the Full Recovery or Bulk-Logged Recovery model.

The backup strategy for simple recovery consists of:

Database backups.


Differential backups (optional).


Note This model is similar to setting the trunc. log on chkpt. database option in Microsoft® SQL Server™ version 7.0 or earlier.


To recover in the event of media failure

Restore the most recent full database backup.


If differential backups exist, restore the most recent one.
Changes since the last database or differential backup are lost.

--
Offline  
Old 06-21-2005, 08:35 AM   #5 (permalink)
BBF Veteran User
 
Join Date: Aug 2004
Location: Hotwiring another Cessna
Model: OU812
Carrier: Nintendo
Posts: 3,492
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

With MSDE there was a way to limit the size of the log file, then set it to unlimited or something. IE, you set the size to 1.5Gb then something else to unlimited. That way when it reached 1.5Gb, it would start appending to the end while deleting the oldest info at the same time.

Is there something similar in SQL?
Offline  
Old 06-21-2005, 09:05 AM   #6 (permalink)
Talking BlackBerry Encyclopedia
 
udontknowjack's Avatar
 
Join Date: Aug 2004
Location: State of Confusion
Model: 9000
Carrier: T-Mobile
Posts: 483
Post Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by bfrye
With MSDE there was a way to limit the size of the log file, then set it to unlimited or something. IE, you set the size to 1.5Gb then something else to unlimited. That way when it reached 1.5Gb, it would start appending to the end while deleting the oldest info at the same time.

Is there something similar in SQL?
Yes, in SQL, you can set the Transaction log to "unlimited restricton" it allows the log file to grow as big as it needs. BTW, this is the setting you want in SQL because if the transaction log reaches its limit, you can't add users or add people to policies or anything in the MMC.
Offline  
Closed Thread


Thread Tools

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

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





Copyright © 2004-2014 BlackBerryForums.com.
The names RIM © and BlackBerry © are registered Trademarks of BlackBerry Inc.