Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Checking Your Database Fragmentation Level Expand / Collapse
Author
Message
Posted Friday, November 18, 2005 1:04 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 9:35 AM
Points: 1,277, Visits: 1,612

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

 

 




Post #238230
Posted Monday, November 21, 2005 7:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:30 AM
Points: 155, Visits: 148

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
Posted Monday, November 21, 2005 1:58 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031

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
Posted Tuesday, November 22, 2005 4:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:30 AM
Points: 155, Visits: 148

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

Much appreciated,

Stijn

Post #238809
Posted Tuesday, November 22, 2005 1:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:19 AM
Points: 72, Visits: 103

 

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
Posted Wednesday, November 23, 2005 5:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:11 PM
Points: 2,006, Visits: 6,081
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
Posted Friday, May 9, 2008 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, May 21, 2008 1:08 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:56 PM
Points: 282, Visits: 2,212
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 »

Add to briefcase ««12

Permissions Expand / Collapse