Transaction Logs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Transaction Logs

Author

Topic

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-26 : 17:14:41

I have defragmented last night and got the backup (full) then the following morning along with other databases the transaction logs for every 15-minutes should happen.

All other databases got the normal 15-min backup but not this which was defragmented.

Any Ideas please. Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-26 : 17:39:57

Are the log backups failing? If so, with what error?

--
Gail Shaw
SQL Server MVP

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-26 : 17:58:51

The Log backups have been failed.

However, I have corrected the error on taking the Full Backup followed by the Differential ( Log backup) fortunately it began.

Problem Solved.

My other question is what is the seqential of events for a database to be defragmented and getting back to online.

Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-26 : 18:40:29

Differential and log backups are totally different things. You didn't give the error, but I'd guess that the problem was caused by either an explicit log truncation or the database being set to simple recovery for some job.

What do you mean by defragmented? Index rebuilds or file system defragmentation?

--
Gail Shaw
SQL Server MVP

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-26 : 22:32:53

I was informed that there is Fragmentation on the database and I have used the script to defragment. All is well till this point and I have set the Database into SIMPLE from FULL recovery for which you have predicted it is now changed and transaction logs are working.

Now I ask of what are the chronological steps which involve with defragmentation of database and thereafter do I need to do INDEX REBUILD or what ?

The reason is after doing the defragmentation the fragmentation still shows.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-08-27 : 02:57:51

There is no need to change to simple recovery.

Change to bulk logged if necessary
Rebuild indexes that need rebuilding (> 30% fragmentation, > 1000 pages). Note there are lots and lots of good index maintenance scripts available that do this
Update statistics
Change back to full recovery
Run log backup

Rebuilding indexes won't always reduce fragmentation to 0, especially for smaller indexes.

--
Gail Shaw
SQL Server MVP

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-29 : 19:01:58

UPDATE STATISTICS is part of the Reindex run weekly basis. No Problem.

RUN THE LOG BACKUP, The database on 15-minute basis the log backup is automated. No Problem.

CHANGE BACK TO FULL RECOVERY the database is already in the FULL Recovery mode. No Problem.

[ What happened otherday was, while doing the detach & attach the database converted to SIMPLE recovery mode, then I have changed to FULL recovery mode then everything was fine].

NOW,

quote:
Rebuild indexes that need rebuilding (> 30% fragmentation, > 1000 pages). Note there are lots and lots of good index

Based on the OUTPUT which I have above, do you see any realistic problem in the output of the defragmentation report?

After doing the defragmentation when I check the Fragementation if any, I still do get some of the major indexes being reported with over 80 % or 100 %, which is why I do have a doubt.

So what I do is after defragmenting I do follow these things:

As a part of the defragmentation, the LDF file has grown so I do the shrink on it, But I was advised by the SQL community this will give rise to fragmentation again. But in this case I have got the Fragmentation report, well before doing any SHRINK on the Data Files.
So after Doing Defragmentation then I ran Fragmentation Report if any then I got 100 % on several indexes ( As posted above) then I did the shrink and shrinking was done, then again I ran second time to check the Fragmentation report, and there is no change in fragmentation to the first and now. This is my observation.

However, you have mentioned REINDEX will not reduce fragmentation, which is true, but I do the REINDEX for all those changed and unchanged transactions which are built on all indexes during the past week. So I dont see any indirect or direct effect on the fragmentation.

can you please advice me. Thanks a Lot Gail Shaw.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-30 : 18:00:56

Perhaps under my UID the most friendly replies so far, for which I am thankful for the overall Assitance.

However, I do have this question:

I could able to see some Fragmentation here after Defragmenting like
After doing the defragmentation when I check the Fragementation if any, I still do get some of the major indexes being reported with over 80 % or 100 %.

Can it be please analyse this situation, and Many thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 18:22:58

You are confused about the fragmentation that is caused by shrinking. The fragmentation will be at the file level and not at the database level.

Please post the command that you wrote to get that fragmentation report. You've likely got some things that need to be changed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-30 : 19:13:28

To Get Fragmentation Report I have queried using the Views sys.dm_db_index_physical_stats and sys.indexes

I am not confused of the report which was generated (knowing that Fragment caused to the file system), but I have done this way.

First, I got the fragmentation percentage on each index;

then Defragmented;

then again I have checked with the above said views for any fragmentation then I got again some fragmentation percent on some of the Indexes

Here, I see this fragmentation report is not different to the one at the first time before doing defragmentation (both were at the Database level

TableName TableIndexName avg_fragmentation_in_percent rows
CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 SOE_CHT5_IDX1 100 1667812
CHT5 SOE_CHT5_IDX1 100 1667812
CHT5 SOE_CHT5_IDX1 100 1667812
CHT5 SOE_CHT5_IDX1 100 1667812
CHT5 SOE_CHT5_IDX1 100 1667812

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-30 : 19:15:33

Please post all columns for that result set and the exact query that you ran. Also please post the commands that you used to defragment.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-08-31 : 19:28:44

Here is the Code:

SELECT
OBJECT_NAME(ind.object_id) AS TableName,
ind.name AS TableIndexName,
phystat.avg_fragmentation_in_percent, rows
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'DETAILED') p_stat
inner JOIN sys.indexes ind WITH(NOLOCK)
ON ind.object_id = p_stat.object_id
AND ind.index_id = p_stat.index_id
INNER JOIN sys.partitions par WITH(NOLOCK)
ON par.OBJECT_ID = ind.object_id
WHERE p_stat.avg_fragmentation_in_percent > 10 AND ROWS > 100000

Output 1:

TableName TableIndexName Avg_frag_in_% Rows
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX2 100 157812
CHT5 CHT5 SOE_CHT5_IDX2 100 157812
CHT5 CHT5 SOE_CHT5_IDX2 100 157812
CHT5 CHT5 SOE_CHT5_IDX2 100 157812
CHT5 CHT5 SOE_CHT5_IDX2 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
CHT5 CHT5 SOE_CHT5_IDX3 100 157812
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX4 100 1888359
APTP4 APTP5 SOE_APTP5_IDX4 100 1888359
APTP4 APTP5 SOE_APTP5_IDX4 100 1888359
APTP4 APTP5 SOE_APTP5_IDX4 100 1888359
APTP4 APTP5 SOE_APTP5_IDX4 100 1888359
APTP4 APTP5 SOE_APTP5_IDX5 100 1888359
APTP4 APTP5 SOE_APTP5_IDX5 100 1888359
APTP4 APTP5 SOE_APTP5_IDX5 100 1888359
APTP4 APTP5 SOE_APTP5_IDX5 100 1888359

The Above output is same Before the DEFRAGMENTATION and AFTER DEFRAGMENTATION (but Some of its OUTPUT Rows were reduced

OUTPUT 2:

TableName TableIndexName Avg_frag_in_% Rows
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359

In Between this I haven't Shrinked them.

However, after getting the Second time OUTPUT on the Fragmentation report using the Query.

I have Shrinked the Data files and eventually the Data files got shrunk.

Now again I have executed and Fragmentation Report.

OUTPUT 3:

TableName TableIndexName Avg_frag_in_% Rows
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX0 100 1667812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
CHT5 CHT5 SOE_CHT5_IDX1 100 157812
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359
APTP4 APTP5 SOE_APTP5_IDX3 100 1888359

No Change with Output 2 and Output 3.

The Defragmentation Code is not mine but this time I have used the Borrowed Code from the Scripts as follows:

USE <dbname>;
GO

-- Declare variables

SET NOCOUNT ON;
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 20.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT CAST(TABLE_SCHEMA AS VARCHAR(100))
+'.'+CAST(TABLE_NAME AS VARCHAR(100))
AS 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, IndexId, 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, @indexid, @frag;
WHILE @@FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')';
EXEC (@execstr);
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
END;

-- Close and deallocate the cursor.

CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO

Now my question is, though the defragmentation did work, and data transactions thereafter became smooth, but why I am still getting the OUTPUT 2 and OUTPUT 3.

And there is no difference with OUTPUT 2 and OUTPUT 3.

Thanks for your time and hope to get some real meaning for what is meant to be and should be done, hereafterwards....

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-08-31 : 23:24:46

First, STOP SHRINKING your data files. All you are doing is causing your indexes to become fragmented again. It is a waste of time and resources and will cause performance issues.

Now, I would bet anything that those tables are actually HEAPS. They probably do not have a clustered index, and your report is including them because of that. You cannot defragment a heap - so your script will never fix these tables.

pdset
Constraint Violating Yak Guru

310 Posts

Posted - 2010-09-01 : 00:23:00

The tables are made of 1- Clustered index and 2 -3 Non Clustered indexes on Small and less populated tables, but on Largely populated tables have 1 Clustered Index and 6 -8 Non-Clustered Indexes. Moreover, this Clustered Index is built on Concatenated Primary Key OR Single Primary Key.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-01 : 03:04:19

You haven't shown the page count of those tables. If it's small (under 1000 pages) there's little point in rebuilding and no worries if they're fragmented

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources