Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Checking Your Database Fragmentation Level


Checking Your Database Fragmentation Level

Author
Message
RML51
RML51
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1329 Visits: 1612

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





Stijn977
Stijn977
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 364

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


noeld
noeld
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7000 Visits: 2048

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
Stijn977
Stijn977
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 364

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

Much appreciated,

Stijn


nmargol
nmargol
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 107

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?


Nicholas Cain
Nicholas Cain
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2286 Visits: 6200
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
Aspurr
Aspurr
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Jason Crider
Jason Crider
Old Hand
Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)Old Hand (367 reputation)

Group: General Forum Members
Points: 367 Visits: 2229
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search