A Second Set of Eyes

This is more of a "does anyone see something I'm missing" post versus a real problem.

What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.

I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

SET NOCOUNT ON

DECLARE @.SQLSTRING VARCHAR(2000)
DECLARE @.DBNAME VARCHAR(64)
DECLARE @.tablename varchar(128)
DECLARE @.execstr varchar(255)
DECLARE @.objectid int
DECLARE @.indexid int
DECLARE @.frag decimal
DECLARE @.maxfrag decimal
DECLARE @.maxextfrag decimal

-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0
SELECT @.maxextfrag = 40.0

DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')

-- 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 db

-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @.DBNAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.execstr = 'USE ' + @.dbname + ';' + char(13)
PRINT @.execstr
EXEC (@.execstr)


-- 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 TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @.tablename
END

-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables

SELECT @.SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @.SQLSTRING = @.SQLSTRING + @.DBNAME
SELECT @.SQLSTRING = @.SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag'

PRINT @.SQLSTRING

EXEC(@.SQLSTRING)

FETCH NEXT
FROM db
INTO @.DBNAME
END

CLOSE db
DEALLOCATE db

-- 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
OR ExtentFrag >= @.maxextfrag
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

Again, thanks!!there r quite a few problems
1) u cannot change the database context by executing dynamic sql exec('use dbname').
2) the cursor tables is opened outside the loop and closed inside the loop
3) the table Fragmentation is not defined anywhere
ther could be more...|||I addressed the issue withthe tables curosr - works fine now.

The table Fragmentation is actually a permanent table, not a temp table.

..is there any way to actually change database context via SQL besides doing an "in line"|||..is there any way to actually change database context via SQL besides doing an "in line"

Undocumented, but do a search on ms_foreachdb (and ms_foreachtable).

Regards,

hmscott

PS. Undocumented means undocumented, ymmv.|||no. even sp_msforeachdb will not change the context permanently. all that it will do is provide u an option to execute sql in a different db and for all db. the same can be done by

exec ('use mydb select * from mytable')

A lil confused here from BOL

From BOL : How Transactional Replication Works
"After all Subscribers have received transactions, the Distribution Cleanup
Agent removes delivered transactions in the distribution database. Delivered
transactions are kept in the distribution database for a defined period
known as the retention period. Setting a retention period while scheduling
backups can ensure that information required to recover a destination
database automatically is available within the distribution database.
For example, if a Subscriber has scheduled a transaction log dump of a
destination database every 24 hours, you could set the retention period to
48 hours. Even if the Subscriber experiences a failure immediately before a
scheduled backup, all transactions necessary to restore the replicated
tables automatically will still be available to the distribution process of
the Distributor."
So im confused at the first 2 statements where it says that the cleanup
agent job removes the delivered transaction in the distrib database but then
it also states that those delivered transactions are kept in the distrib
database for a defined period and then gives an example that we could set
the retention period to 48 hrs.. So if the cleanup agent cleans them, how is
it retained ?
Hassan,
they're retained until all subscribers have subscribed or the retention
period is reached, whichever is shorter. In the case of anonymous
subscribers this changes and they're retained until the end of the retention
period even if subscribers have picked them up.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

A lil confused from BOL

These are 2 different things.
For (A) this is the length of time that transactions are
retained in MSrepl_commands before the cleanup agent can
remove them. If all subscribers have synchronized, the
commands are removed before this period. This is
different if you have anonymous subscribers of course.
For (B) this means that if a subscription doesn't
synchronize within the said period, it is marked as
inactive and must be reinitialized.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
So if (A) time is up.i.e. after 72 hrs , the cleanup agent would anyways
come in and delete all transactions hence making the subscription in a way
inactive and if you look at the publication properties, it has the 72 hrs
instead of the 336 hrs that (B) talks about..
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0c5b01c4d917$3ba52390$a301280a@.phx.gbl...
> These are 2 different things.
> For (A) this is the length of time that transactions are
> retained in MSrepl_commands before the cleanup agent can
> remove them. If all subscribers have synchronized, the
> commands are removed before this period. This is
> different if you have anonymous subscribers of course.
> For (B) this means that if a subscription doesn't
> synchronize within the said period, it is marked as
> inactive and must be reinitialized.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>