October 23, 2002 at 9:37 am
I changed recovery model from full to simple because I wanted to truncate the log file automatically.
Please correct me if I am wrong , transactional log should be truncated 70% automatically if the mode is simple.
I need to know that 70% is the size of the database or the transaction log.
let's say that my datbase size is 103 and transaction log is growing to 5102 MB.
I need to know that when it gets truncated.
appreciated if you any help.
October 23, 2002 at 9:59 am
It is my understanding that setting the database to a simple recovery model does the following:
It will try to truncation the unused portions of the transaction log every time a checkpoint is taken. Checkpoints are taken when the transaction log reach 70% full, or the number of log records has reach the limit set by SQL Server that it can process during a recovery interval.
In your example the log would be truncated when your transaction log reaches 3571 MB, or a specific number of updates have occurred since the last checkpoint.
Hope this helps.
Also if you database is only 103 MB, and the transaction logon is 5 GIG, I think I would shrink your transaction log to be less than the size of DATA, say 25% of that, or 26 MB.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 23, 2002 at 10:14 am
From BOL
A checkpoint will occur when
The active portion of the log exceeds the size that the server could recover in the amount of time specified in the recovery interval server configuration option.
This is likely to occur before your log reaches 3.5Gb.
The 70% is relative to the size of the transaction log. This is to try and avoid it growing.
BOL covers this very well. keywords to look up "chekpoints" "recovery interval" "truncated transaction logs"
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 23, 2002 at 11:34 am
Greg .. Now my transaction has grown to 6521 but it not truncating. can u please tell me how u come up with this number.
Simon .. You mentioned that it will grow to 3.5 gb , how u come up with this number.
October 23, 2002 at 11:50 am
I came up with the number by taking 70% of the size you said the log was. I'm guessing your transaction log is full beyond the 70% range.
I would review the information returned by the following command to determine how much log space is used:
dbcc sqlperf(logspace)
What happens when you run:
backup log <database with problem> with truncate_only
This command should truncate the log.
Is the log still full?
Read the following articles on shrinking your transaction log. I hope these will give some suggestions on how to fix your problem.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q256650
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 23, 2002 at 12:09 pm
Thanks for your reply.
My transaction log is not full. I am just checking the log file and is growing very fast.
I run the dbcc sqlperf(logspace) command and get following result.
database log size(mb) log space used
---------------------------------------
eXpress1.742187540.8071750
October 23, 2002 at 2:27 pm
Sorry about the last reply.
I used the command dbcc sqlperf(logspace)
and I get following result. so it is over 70% ,
database log size log space used
eXpress6760.929790.9167480
October 23, 2002 at 2:50 pm
Run QA and type checkpoint and execute it.
Does this start some activity on your server?
Are you running a huge update or insert, if so and it has modified this amount of data then the log won't trucate because the transaction is still active
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
October 23, 2002 at 3:11 pm
I think you are right. May be these entries are all active.
Actually , we are running Altiris application on the sql server. Altiris application is similar to PC Anywhere and throuth this application you can remotely connect to the sever.
I don't understand one thing that if these entries are active then why it's truncating the transaction log file if I run the following command
use express
backup log eXpress to sql1backup
dbcc shrinkfile (eXpress_log, truncateonly)
October 23, 2002 at 3:23 pm
only non-active parts of the transaction log are truncated.
Basically the transaction log is cut up into a number of pieces call virtual log files, that are used in a circular fashion. only the pieces that don't have non-active transcation are truncated. But there is more. Only the pieces logically before the oldest active transaction are truncated. So bacisally if your transaction log was in 6 virtual pieces (V1, V2, V3, V4, V5, and V6), and the oldest pieces of the circular log was V3, and the current pieces being written to for new transaction was V2 , and the oldest active transaction was contained in V3, then a transction log truncation would not release any transaction log space. This is because the log logically goes v3, v4, v5, v6, v1, v2, and the oldest active transaction is in the first virtual piece.
Not a very good explaination, but I hope it helps.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 24, 2002 at 11:17 am
If the recovery model is set to simple, the backup statement that you ran would have failed...
--backup log eXpress to sql1backup
Server: Msg 4208, Level 16, State 1, Line 1
The statement BACKUP LOG is not allowed while the recovery model is SIMPLE. Use BACKUP DATABASE or change the recovery model using ALTER DATABASE.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
You need to verify that the recovery model is indeed SIMPLE.
--select DATABASEPROPERTYEX ('eXpress', 'recovery')
This select should return "simple". If not, then run the following...
--alter database eXpress
--set recovery SIMPLE
Then run
-- use eXpress
-- CHECKPOINT
to clear the inactive entries.
Finally run the following (be sure to change the db name and the device name)...
-- USE Pubs
-- GO
-- DBCC SHRINKFILE (Pubs_Log)
-- GO
--
This should clear up your problem.
Steve Phelps
SQL Server DBA
American Fidelity Group
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply