May 9, 2008 at 1:57 am
Every morning I get this message regarding one of our production DBs:
The transaction log for database 'ABC' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.
This is SQL 2005. The recovery model is FULL. Full DB backups occur every night at 01:00. Transactional log backups occur every 10 minutes. Auto_shrink is set to false and max file size is Unlimited.
I don't understand why the log would become full as transactional log backups happen every 10 minutes - which of course then truncates the log, correct?
It is also not a case of running out of disk space, as the volume has over 70GB of free space, (the DB data file is 17 GB and the ldf file is about 2GB when I get this error).
Is there any one who can help with this problem?
Thanks,
M
May 9, 2008 at 2:28 am
What does the log_reuse_wait_desc column in sys.databases say?
Are you doing index rebuilds or other large transactions around the time of the error?
Do you have replication active on that DB? Mirroring?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2008 at 2:28 am
Hi
I would suggest looking at the following, is your tran log set to a fixed size that is not able to accommodate the amount of transactions in the ten minutes until a log backup runs. So maybe increase the frequency of the log backup of increase the log file size or set it to auto grow whichever suites you.
Also you may have some long running transactions that fill up the log file before a backup can take place and clear it, that can be resolved by doing the above.
May 9, 2008 at 2:42 am
I don't have mirroring or replication active but there is a maintenance task of reorganizing indexes, which is when the error occured. Any suggestions on this?
I will increase the frequency of the log backups and see how that goes -thank you!
May 9, 2008 at 2:51 am
Rebuilding indexes creates very large transactions. When the backup log occurs, active (uncommitted) transactions cannot be removed from the log.
You may find it's necessary to grow the log a bit for the index rebuilds.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2008 at 9:07 am
i get full logs when running alter index sometimes
only solution i found is to increase the threshhold so less indexes are rebuilt
May 12, 2008 at 3:58 am
Thanks for all your replies, and it seems that the ALTER indexes were indeed the cause of all my problems.
I just have one final question:
How often would you suggest I do the following:
Reorganize index, and Rebuild index.
Is this something that should occur every night or rather once a week? What would you suggest?
May 12, 2008 at 4:16 am
Once a week, reorganise an index if logical fragmentation < 30%, rebuild if fragmentation > 30%
Don't do both to the same index. It's redundant
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2008 at 4:44 am
Would it be possible to build this into a maintenance plan using the precedence constraint expression?
Darryl
May 26, 2008 at 4:58 pm
Put this into a Scheduled SQL Job. It will rebuild your indexes if they are over 30% fragmented. I run this once a week on my production DB's.
-- Declare variables
SET NOCOUNT ON;
DECLARE @tablename varchar(128);
DECLARE @execstr varchar(255);
DECLARE @objectid int;
DECLARE @indexname varchar(255);
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, Indexname, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC DBREINDEX (''' + RTRIM(@tablename) + ''',
' + RTRIM(@indexname) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%';
SELECT @execstr = 'DBCC DBREINDEX (''' + RTRIM(@tablename) + ''',
' + RTRIM(@indexname) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexname, @frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
May 26, 2008 at 5:30 pm
I have included all of the maintenance procedure I wrote. These procedure have been tested with SQL Server 7, 2000 and 2005 with both case-sensitive and insensitive collations. There also example jobs.
For the fragementation procedures.
Description: Defragments each table index in a database from most fragmented to least fragemented.
Privileges: System Adminstrator only (DB Owner is insufficient)
Run Time Stops at a specified end or elasped time is reached but after the current index rebuild completes.
Install: master database only as a system procedure
After each index is defragemented, a log backup will occur when all of these condistion are true
1 The database is not in simple recovery mode
2. The transaction log has not been backed-up in the last 60 seconds
3. The transaction log space used is greater than either of 2 threshold parameters - absolute or percent used
SQL = Scarcely Qualifies as a Language
May 27, 2008 at 12:57 pm
Good Script, But you can schedule from MP as well.
MCP, MCTS (GDBA/EDA)
May 27, 2008 at 1:02 pm
maybe it's me, but i like to only do rebuilds. i mostly support EE so online rebuilds aren't a big deal.
rebuilds also rebuild the statistics and with reorganize i have to rebuild statistics manually. i've seen some weird things when you do an index rebuild and then update the statistics. sometimes it makes the optimizer go crazy and do index scans and not seeks when it should be doing a seek.
May 27, 2008 at 1:12 pm
I completly agree with you Mr or Mrs. 500
MCP, MCTS (GDBA/EDA)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply