April 18, 2023 at 7:29 am
The underneath code works on higher sql versions(2016 etc) but throws an error on 2008R2.
Error:Msg 102, Level 15, State 1, Line 19
Incorrect syntax near '('.
Thanks
SET NOCOUNT ON
declare @tmp table(name nvarchar(500),Status int)
insert into @tmp SELECT name,0 as completed
FROM sys.databases
WHERE (state_desc = 'ONLINE')
DECLARE @dbname nvarchar(500)
DECLARE @cmd NVARCHAR(MAX)
declare @result table (ServerName nvarchar(500),dbname nvarchar(500),
SchemaName nvarchar(500),TableName nvarchar(500),ObjectType varchar(100),
IndexName nvarchar(500),Index_Type varchar(100),IsIndexUnique varchar(100),Alloc_Unit_Type_Desc nvarchar(500),
avg_fragmentation_in_percent decimal(38,10),Fill_factor int,page_count bigint)
WHILE EXISTS(SELECT 1 FROM @tmp WHERE Status = 0)
BEGIN
SET @dbname = (SELECT TOP 1 name FROM @tmp WHERE Status = 0)
SET @cmd = 'use ['+ @dbname + '];SELECT @@servername as ServerName,db_name(),dbschemas.[name],
dbtables.[name],
case when t.type= ''U'' then ''Table''
when t.type= ''V'' then ''View''
else ''Unknown''
end as
[ObjectType],
dbindexes.[name],
dbindexes.type_desc,
case when dbindexes.is_unique = 0 then ''Is Unique''
when dbindexes.is_unique = 1 then ''Not Unique''
else ''Unknown''
end as
[IsUnique],
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent as Ifg,
dbindexes.Fill_Factor,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
INNER JOIN sys.objects t on t.object_id = dbindexes.object_id
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc'
insert into @result exec sp_executesql @cmd
UPDATE @tmp SET Status = 1 WHERE name = @dbname
END
insert into [dbM].[dbo].[Ifg]
(
[ServerName]
,[DBName]
,[SchemaName]
,[TableName]
,[ObjectType]
,[IndexName]
,[Index_Type]
,[IsIndexUnique]
,[Alloc_Unit_Type_Desc]
,[Avg_Fragmentation_In_Percent]
,[Fill_Factor]
,[Page_Count]
,[TSMb]
)
select *,(page_count*8/1024) as TSMb from @result
where dbname not in('master','msdb','model','temp','ReportServer','ReportServerTempDB')
AND avg_fragmentation_in_percent > 20 AND page_count > 2000
April 18, 2023 at 8:29 am
your issue is because you have databases on that 2008R2 instance that are compatibility level 80 (sql 2000) where dm_db_index_physical_stats didn't exist.
if you wish to confirm change your code as follows
insert into @tmp SELECT name,0 as completed
FROM sys.databases
WHERE (state_desc = 'ONLINE')
and compatibility_level <= 80 -- this will fail
--and compatibility_level > 80 -- this will work
-- comment and uncomment the above 2 lines to verify what I say
April 18, 2023 at 8:57 am
Ooof on the Compatibility 80 level. SQL Server 2008 ran out of support back in 2019, so you really want to get that instance upgraded, but if you're still running in compatibility 80 then that database is a long way behind. You really need to start looking at that upgrade plan if you haven't already, but you're going to likely need to do a lot of testing to see what breaks as you upgrade that database to more recent compatibility levels.
I would not be surprised if some of your code uses the old ANSI-89 implicit JOIN syntax if it dates that far ago and you're yet to update the compat level. ANSI-89 support was deprecated in SQL Server 2008 and removed in SQL Server 2012 (even compatibility level 100 (2008) doesn't cause the behaviour to return) so if you are using such syntax you'll need to ensure that you update all your code base to use ANSI-92 explicit JOIN syntax. Fortunately, as you update/upgrade, you'll get access to some really useful tools that will make your development life a lot easier.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2023 at 9:00 am
But I need to capture information from databases whose compatibility_level is 80.
April 18, 2023 at 9:18 am
you would need to use a different tool for it (DBCC SHOWCONTIG ) - but how many do you have with compat 80? and can't you change them to compat 90 (2005) - that would solve the issue.
April 18, 2023 at 9:18 am
I never had any exposure to SQL Server 2000, but my Google-Fu tells me you'll need to use DBCC SHOWCONTIG (see SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005). You won't be able to JOIN to that like you have in your attempt, because that isn't how DBCC works. I don't have an instance I could even attempt to use this against either.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 18, 2023 at 10:03 pm
I never had any exposure to SQL Server 2000, but my Google-Fu tells me you'll need to use
DBCC SHOWCONTIG(see SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005). You won't be able toJOINto that like you have in your attempt, because that isn't howDBCCworks. I don't have an instance I could even attempt to use this against either.
If you have a look at the documentation at the following link, you'll see that one option is to produce "table results". I don't remember specifically how I used to do it for sure but Example E: provides a hint on how to capture the output of DBCC SHOWCONTIG. Of course, I didn't use a CURSOR.
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-showcontig-transact-sql
The key, though, is that the more than 2 decades old method that people have made the mistake of advertising as a "Best Practice" because of some inappropriate wording in the original (which they finally changed on 20 April 2021) is wrong. And, although the documentation of what REORGANIZE actually does is technically correct, many people have made it into something that it is not. (in 2000, IIRC, it's DBCC INDEXDEFRAG. I strongly recommend you avoid it no matter what until you learn what it actually does (it actually perpetuates fragmentation for mid index page splits) to your indexes and the incredible amount of log file space it can actually use even for <1% logical fragmentation.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply