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 Real Sql Server 2005 Bug! A Real Sql Server Bug!

A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing to
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpected
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
Mike
Mike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now we
> are getting the folllowing message every few nights causing our processing to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
> When this simplified query is run from the management studio, it fails about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>

A Real Sql Server 2005 Bug! A Real Sql Server Bug!

A growing Sql Server 2005 database performs several hours of updates each
night. This particular region of code has run fine for over a year. Now we
are getting the folllowing message every few nights causing our processing t
o
abort:
Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
Internal Query Processor Error: The query processor encountered an unexpecte
d
error during execution.
This is a simplified version of the query from line 150:
select
p.PeriodStartDate,
p.PeriodType,
p.ActivityUserNumber,
p.AppNumber,
max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
max( case when ActivityType = 'FUNDED' then 1 else 0 end )
from Activity a, xxxPeriod p
where a.ActivityDate >= p.PeriodStartDate
and a.ActivityDate < p.PeriodEndDate
and a.ActivityUserNumber = p.ActivityUserNumber
and a.AppNumber = p.AppNumber
and ActivityType in (select code from Lookup where SetName =
'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumber
When this simplified query is run from the management studio, it fails about
10-20% of the time.
Some Observations:
- Sometimes we get a few records in the result set prior to the failure.
- This query works on our smaller development database.
- If we change the query in any of the following ways, the query works
(e.g., 15 attempts w/o an error):
- Removing the group by and max()
- Removing one or more max statements
- Hard-code the list of activity types in place of the sub-select from
Lookup table
Version:
Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
Help!
MikeMike wrote:
> A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
we
> are getting the folllowing message every few nights causing our processing
to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an unexpec
ted
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber, p.AppNumb
er
> When this simplified query is run from the management studio, it fails abo
ut
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>
I would suspect a TEMPDB problem. Lack of space? Autogrow timeout?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, please contact Microsoft product support.
Thanks,
Leo
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:168FB0C7-AF8D-4722-B8EF-FC5A8D309F47@.microsoft.com...
>A growing Sql Server 2005 database performs several hours of updates each
> night. This particular region of code has run fine for over a year. Now
> we
> are getting the folllowing message every few nights causing our processing
> to
> abort:
> Msg 8630, Level 17, State 52, Procedure sp_dts_post_activity, Line 150
> Internal Query Processor Error: The query processor encountered an
> unexpected
> error during execution.
> This is a simplified version of the query from line 150:
> select
> p.PeriodStartDate,
> p.PeriodType,
> p.ActivityUserNumber,
> p.AppNumber,
> max( case when ActivityType = 'APP_SUBMITTED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_REVIEWED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_RECEIVED' then 1 else 0 end ),
> max( case when ActivityType = 'DOCS_COMPLETED' then 1 else 0 end ),
> max( case when ActivityType = 'BOOKED' then 1 else 0 end ),
> max( case when ActivityType = 'FUNDED' then 1 else 0 end )
> from Activity a, xxxPeriod p
> where a.ActivityDate >= p.PeriodStartDate
> and a.ActivityDate < p.PeriodEndDate
> and a.ActivityUserNumber = p.ActivityUserNumber
> and a.AppNumber = p.AppNumber
> and ActivityType in (select code from Lookup where SetName =
> 'ACTIVITY_TYPE' and ParentCode = 'ACCOUNT')
> group by p.PeriodStartDate, p.PeriodType, p.ActivityUserNumber,
> p.AppNumber
> When this simplified query is run from the management studio, it fails
> about
> 10-20% of the time.
> Some Observations:
> - Sometimes we get a few records in the result set prior to the failure.
> - This query works on our smaller development database.
> - If we change the query in any of the following ways, the query works
> (e.g., 15 attempts w/o an error):
> - Removing the group by and max()
> - Removing one or more max statements
> - Hard-code the list of activity types in place of the sub-select from
> Lookup table
> Version:
> Sql Server 2005, 9.00.2047.00,SP1, Standard Edition
> Help!
> Mike
>
>
>
>

A Real SQL Mindblower

Hi,

I'm trying to work out how to extract the information that I need from a set of database tables and can't think of a way of doing it with SQL.

The database forms the basis for a diary/calendar system for part-time employees and has two tables:

- One is called 'Availability' and holds info on each available hour slot in the calendar. The table just has fields 'time' and 'date', where the time is an integer representing an hour and date is datetime. The calendar runs from 07.00 to 23.00 each day, so there could be 16 rows in 'Availability' for one day. If any part of a day is unavailable (i.e. the employee doesn't work then) there will be no corresponding rows in the table.

- The second table is 'Appointments', which holds details of appointments that the employee is booked for. The main fields are 'date', 'time' and 'duration' (integer for minutes). All appointments will cover a time span that is also covered by an available period, but they are not actually linked in any way.

I need an SQL query that will return all available time slots that start at least 60 minutes after any appointments have FINISHED and at least 120 minutes before any appointment STARTS.

Since there is no link between the 'Appointments' table and the 'Availability' table, I can't think of any way of doing this.

Any ideas?select ...
from Appointments appt1
inner
join Availability avail
on [pseudocode: appt1 + 60 > avail]
inner
join Appointments appt2
on [pseudocode: appt1 + 120 > appt2]
where ...
separarate date and time columns give me the willies (they should be one, a datetime column)

so you will have to work out the DATEADD expressions yourself|||I guess this should be:

select ...
from Availability avail
inner
join Appointments appt1
on [pseudocode: (appt1 + duration) < avail - 60]
inner
join Appointments appt2
on [pseudocode: appt2 - 120 > appt2]
where ...

And this will basically give me a list of available slots that are at least 60 minutes after ANY appointment finishes and at least 120 minutes before ANY appointment starts??|||this: appt2 - 120 > appt2 (your 2nd join condition) will always be false :)

i would definitely want to see some sample rows to try some of this stuff out

i'm still not sure i understand your tables|||I'm not sure I understand my tables sometimes!!

Basically my tables are structured like this:

TABLE: Appointments
ref int identity(1,1), member int, practitioner int, service int, date datetime, time varchar(5), address text, postcode varchar(10), duration int

Relevant Sample Data:
Date: 17/05/04, Time: 14:00, Duration:60

TABLE: Availability
ref int identity(1,1), practitioner int, date datetime, time varchar(5)

Relevant Sample Data:
Date: 17/05/04, Time: 07:00

An appointment can only be added to the database if there is an available period(s) for the chosen time (so each appointment will cover the periods represented by one or more item in the Available table).

So, before I add an appointment I need some SQL that will give a list of available periods that aren't yet covered by appointments.|||my condoloences

your varchar time fields make a simple query impossible

as for "sample rows," maybe i should have said "as many sample rows as necessary to illustrate one or two scenarios where the conditions you will be seeking with the query are represented in the data"|||And why does this SQL...

SELECT avail1.ref, avail1.time, avail1.date FROM (Availability avail1 INNER JOIN Appointments appt1 ON appt1.date<>avail1.date AND appt1.practitioner=avail1.practitioner) WHERE avail1.practitioner=1 ORDER BY avail1.date, avail1.time

...return an instance of each Availability item for every Appointment item that isn't in the same day as it. Surely it should only return an item if NO APPOINTMENTS are in the same day as it?|||And why does ... return an instance of each Availability item for every Appointment item that isn't in the same day as it.
because you told it to

... ON appt1.date<>avail1.date|||So, how would I alter that SQL to give me Availability items that doesn't have any appointments in the same day?