August 28, 2008 at 8:07 am
I have a database that is set to simple recovery mode (therefore no transaction log backups are required). The database is 36G in size, the transaction log is 34G in size. This has been constant for as long as I know. My question is why would the log be almost the same size as the database? I understand if there was a long transaction that is holding the space, but that is not the case here.
Thanks.
August 28, 2008 at 8:12 am
Also the initial size of the log file was set to 537MB.
August 28, 2008 at 8:16 am
Try this...
USE master
go
EXEC sp_dboption 'YourDatabaseName','trunc. log on chkpt.',true
go
USE YourDatabaseName
go
CHECKPOINT
go
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 28, 2008 at 8:19 am
Thanks Paul for your reply. Do you mind telling me what this is going to do? This is a production server and I don't like taking chances 😉
August 28, 2008 at 8:27 am
Well... it sets 'YourDatabaseName' 'trunc. log on chkpt.' option to true then performs a checkpoint 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 28, 2008 at 8:32 am
The SIMPLE recovery model does the truncate log on checkpoint. Truncate Log on Checkpoint was removed in 2000 and replaced by the SIMPLE recovery model. So you would only have to issue the CHECKPOINT. Truncate does not release the space to the OS it only makes the space available so that it can be re-used instead of growing the log.
Has the database ALWAYS been in SIMPLE mode? It does not sound like it was and then when log growth was noticed it was changed to stop the growth.
You can do a DBCC ShrinkFile if you think the Log is way too big and reset it to an appropriate size. With a 36GB database the recommendation would be about an 8GB log (20%), but depending on the activity you may want more or less. You'd have to look at growth events on the log after shrinking it.
The other question is, if this is a database with many changes being made, are you sure you want it in SIMPLE mode? You can't restore to a point in time without log backups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 8:37 am
Thanks. Actually, my system can handle the size, so I really don't need to truncate at this point.
What I'm really wanting is to understand why/how the transaction log can be as big as the database when recovery is set to simple mode and the system is suppose to manage the trans log.
I appreciate the help.
August 28, 2008 at 8:46 am
There are a lot of articles and blog posts out there about this topic. My gut is telling me that this database was not originally in SIMPLE mode and that is when the log file grew to that size and when it was noticed the recovery model was changed to SIMPLE.
Another reason could be that the database was originally loaded with data in a very large transaction or transactions before a checkpoint was issued and the log could not "wrap", but had to grow.
Check out this blog post about the transaction log: http://sqlblogcasts.com/blogs/sureshbarathan/archive/2008/04/17/know-the-transaction-log-part-1.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 28, 2008 at 8:49 am
Jack Corbett (8/28/2008)
Has the database ALWAYS been in SIMPLE mode?
I cannot say for sure since I was not the one that set it up and just recently was assigned to administer.
The other question is, if this is a database with many changes being made, are you sure you want it in SIMPLE mode?
It does have many inserts, the db is for our system monitoring, so they are ok with loosing 24 hrs worth of data.
I will go ahead and shrink the log down to 8GB and then monitor if it grows. That will then tell me for sure.
Thanks for all the good information!
August 28, 2008 at 2:21 pm
ang
what is the output of the following query run against your database?
DBCC LOGINFO
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 2:34 pm
Looks like the active portion of the log is 3/4 of the way through the log (status is 2 from row 352-361). I have attached the output.
August 28, 2008 at 2:58 pm
yes, that is correct. The first 70% of the log is unused, the active portion (status of 2) would prevent the log from shrinking to a realistic size. What is the output of the following run against the database
DBCC OPENTRAN
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 28, 2008 at 3:09 pm
No active open transactions.
August 28, 2008 at 3:28 pm
the following will forcefully shrink the logfile, set recovery model to FULL first
--Forcefully Reducing the Size of the SQL Server Log File
--(These instructions are rooted in SQL Server 7, but may work in SQL Server 2000\2005)
--Occasionally the log file on a database will grow to an alarming size, or disk space will be short,
--and you find that running DBCC SHRINKFILE and BACKUP LOG do not reduce the log file's size.
--Run:
DBCC LOGINFO (database name)
--and check the last entry; if the last entry's status is 2, then the end of the transaction log
--is the active portion. This can have the effect of preventing the log file's reduction.
--To reduce the log file we need to somehow force the active part of the log back to the
--beginning, thus setting the end of the transaction log as being unused.
--To reduce the file, follow these steps:
--Step 1
--run:
DBCC SHRINKFILE (log file, TRUNCATEONLY )
--run:
BACKUP LOG database WITH TRUNCATE_ONLY
--Step 2
--Create a dummy table and insert a record to MyTable
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
--Step 3
--Create and run the following script:
SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
UPDATE MyTable SET MyField = MyField WHERE PK = 1
SELECT @Index = @Index + 1
END
SET NOCOUNT OFF
--If this is run successfully, the log rows with a status of 2 will wrap around to the beginning
--(or some other unused part) of the log file. The later parts of the log file will become marked as unused,
--and the next DBCC SHRINKFILE and BACKUP LOG will truncate the log:
--Step 4
--Run these commands again:
DBCC SHRINKFILE (logfile, truncateonly )
BACKUP LOG database name WITH TRUNCATE_ONLY
--The log file should now be smaller.
-- delete the table you created and take full backup of the database immediately
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 29, 2008 at 9:53 am
I'll try that. I wonder why sql decided to start 3/4 of the way through the log. Doesn't make much sense!
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply