Checking Your Database Fragmentation Level

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Nicholas Cain » Checking Your Database Fragmentation Level

18 posts, Page 2 of 2

««1 2

Checking Your Database Fragmentation Level

Rate Topic

Display Mode

Topic Options

Author

Message

RML51

Posted Friday, November 18, 2005 1:04 PM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 11:22 AM
Points: 1,268, Visits: 1,477

No luck getting this to output. Eventually got everything to run, but email output simply reads: THETEXT

Post #238230

Stijn Vervaet

Posted Monday, November 21, 2005 7:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:58 AM
Points: 155, Visits: 141

Hi,

I am using a similar approach (running a SHOWCONTIG and use the output to decide which indexes need to be defragmented). However, i am always running into rapid growing log files once i start reindexing or DBCC INDEXDEFRAG.

Does anyone know a "safe" way on how to defragment those fragmentated ones without risking blowing up the logs?

Greets,

Stijn

Post #238487

noeld

Posted Monday, November 21, 2005 1:58 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

There are two schools in this matter. Pick what suit you best

1. Run parallel Tlog backups at the same time the reindexing is happening eg. Run a Tlog backup every 2 min while the reindexing is going on

2. Make a Tlog Backup, Switch the recovery model to Bulk_logged, perform the Reindexing, switch back to Full and when performing the next TLog backup do it on a drive that have plenty of space

Cheers,

* Noel

Post #238660

Stijn Vervaet

Posted Tuesday, November 22, 2005 4:17 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, February 08, 2013 5:58 AM
Points: 155, Visits: 141

Thanks ... i'll see how it goes when i up the backup frequency when i'm reindexing.

Much appreciated,

Stijn

Post #238809

nmargol

Posted Tuesday, November 22, 2005 1:46 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, March 20, 2013 2:35 PM
Points: 72, Visits: 101

Still can't get usp_dbshowcontig_single_db to work. I added the fifth quote but am still getting this error message: Invalid object name '#tablename'

Has anyone else had this error message and solved the problem?

Post #238978

Nicholas Cain

Posted Wednesday, November 23, 2005 5:28 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:27 PM
Points: 2,007, Visits: 6,040

Below is the correct code, looks like a typo in the article, somewhat embarassing, but no more than MS crashing SQL 2k5 and having to reboot in the NY launch event yesterday.

-----------------------------------------------

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE USP_DBshowcontig_single_db @name varchar(50)

AS

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @dbname VARCHAR(20)

DECLARE @sql VARCHAR(1000)

DECLARE @inserttable VARCHAR(3200)

-- Create the table

CREATE TABLE #DBFRAGMENT (

ObjectName VARCHAR (50),

ObjectId INT,

IndexName VARCHAR (100),

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)

create table #tablename (table_name varchar(400))

--DECLARE DB Cursor

DECLARE databases CURSOR FOR

SELECT NAME

FROM MASTER.DBO.SYSDATABASES

WHERE NAME = @NAME

--Open the cursor

OPEN databases

FETCH NEXT FROM databases INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql = 'SELECT TABLE_NAME = NAME FROM ' + @dbname + '..SYSOBJECTS WHERE XTYPE =' + '''' + 'U' + ''''

print @sql

insert into #tablename exec(@sql)

-- Declare cursor

DECLARE tables CURSOR FOR

SELECT TABLE_NAME

FROM #tablename

-- Open the cursor

OPEN tables

-- Loop through all the tables in the database

FETCH NEXT FROM tables INTO @tablename

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @TABLENAME

-- Do the showcontig of all indexes of the table

INSERT INTO #DBFRAGMENT

EXEC ('USE ' + @dbname + ' DBCC SHOWCONTIG (''' + @tablename + ''') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT FROM tables INTO @tablename

END

set @inserttable ='INSERT INTO ADMINDB.DBO.INDEX_INFO_'+@NAME+'(ObjectName,

ObjectId,

IndexName,

IndexId,

Lvl,

CountPages,

CountRows,

MinRecSize,

MaxRecSize,

AvgRecSize,

ForRecCount,

Extents,

ExtentSwitches,

AvgFreeBytes,

AvgPageDensity,

ScanDensity,

BestCount,

ActualCount,

LogicalFrag,

ExtentFrag)

select ObjectName,

ObjectId,

IndexName,

IndexId,

Lvl,

CountPages,

CountRows,

MinRecSize,

MaxRecSize,

AvgRecSize,

ForRecCount,

Extents,

ExtentSwitches,

AvgFreeBytes,

AvgPageDensity,

ScanDensity,

BestCount,

ActualCount,

LogicalFrag,

ExtentFrag

FROM #DBFRAGMENT where ltrim(rtrim(#DBFRAGMENT.indexname))<> '''''

--PRINT @INSERTTABLE

EXEC (@inserttable)

-- Close and deallocate the cursor

CLOSE tables

DEALLOCATE tables

delete from #tablename

delete from #DBFRAGMENT

FETCH NEXT FROM databases INTO @dbname

END

CLOSE databases

DEALLOCATE databases

drop table #tablename

--Delete the temporary table

DROP TABLE #DBFRAGMENT

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

Shamless self promotion - read my blog http://sirsql.net

Post #239126

Aspurr

Posted Friday, May 09, 2008 4:55 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 18, 2011 12:29 AM
Points: 9, Visits: 35

Hello Nicholas,

I know this subject was discussed some time ago, but I am new to SQL and was wondering if you had enhanced the original script yet. I am in the process of trying to understand how it works and would value any new information about running on multiple databases. I was also wondering if it is possible to email using SMTP, when you do not have SQL configured to use a mapi applicaton (outlook e.t.c.)

I am very new to becoming a SQL DB Admin. I have been around SQL for a while, but never got into the scripting side

Any help appreciated

Post #497729

Jason Crider

Posted Wednesday, May 21, 2008 1:08 PM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 9:24 PM
Points: 282, Visits: 2,125

It is possible to email using SMTP.

Try xpsmtp.

We are using it successfully.

MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter

Post #504776

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions