April 16, 2011 at 7:07 am
Hi,
I want asking two question?
1. I have two database 1. PMSPRD and 2. PMSDEV.
First database (PMSPRD) this script was running fine, and get result like
declare @PageCount bigint
set @PageCount = 1000
select
[Table] = object_name(stats.[object_id])
,[Index]= si.[name]
,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,4))
,[Page Count] = stats.[page_count]
,[SQL script] = case
when (stats.[avg_fragmentation_in_percent] < = 30)
then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] reorganize'
when (stats.[avg_fragmentation_in_percent] > 30)
then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] rebuild'
end
from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
join sys.indexes as si on stats.object_id = si.object_id
and stats.[index_id] = si.[index_id]
where stats.[avg_fragmentation_in_percent] > 10.0
and si.index_id > 0
and stats.[page_count] >= @PageCount
order by 3 desc, 1, 2;
output
-------
alter index [PK_PME_Declarati] on [PME_Declaration] rebuild
Second database getting error as below, what could be reason? it was running in first database.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.
----------------------------------------------------------
2. Update statistics
exec sp_MSforeachdb
' IF (''?'' IN (''PMSPRD''))
BEGIN PRINT ''Atualizando estatÃsticas de '' + ''?''
use ? exec sp_updatestats END';
output
--------
Updating [dbo].[Parameter_Master]
[PK_Parameter_Master], update is not necessary...
[_WA_Sys_Para_Code_02DD43D9], update is not necessary...
[_WA_Sys_Para_Name_02DD43D9] has been updated...
[_WA_Sys_Discharge_DIsplay_02DD43D9] has been updated...
[_WA_Sys_Modify_By_02DD43D9] has been updated...
[_WA_Sys_Modify_Dt_02DD43D9] has been updated...
[_WA_Sys_CheckForNormal_02DD43D9] has been updated...
[_WA_Sys_Unit_02DD43D9] has been updated...
[_WA_Sys_UnderNABLScope_02DD43D9] has been updated...
[_WA_Sys_HA_Limit_02DD43D9] has been updated...
8 index(es)/statistic(s) have been updated, 2 did not require update.
This table contain total 10 Index, 8 index(es)/statistic(s) have been updated, but 2 did not require update.
Please suggestion me, why did not require update?
Thanks
ananda
April 17, 2011 at 6:18 am
Not sure about the first one, I suspect there's a different index name in there that is messing it up.
The second question, no update on the stats was needed because it's possible there have been no data changes. If there are no inserts/updates/deletes, then there are no changes to the stats.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 18, 2011 at 5:04 pm
Use [database name]
go
add this in the begining of the code and then check
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 19, 2011 at 6:07 am
What SQL Server version and compatibility mode are the two databases? The first query will only work on SQL 2005 + (and only in 90+ compatibility mode)
April 19, 2011 at 7:12 am
HowardW (4/19/2011)
What SQL Server version and compatibility mode are the two databases? The first query will only work on SQL 2005 + (and only in 90+ compatibility mode)
SQL server compatibility 100 for two database.
one database was working fine and another database not working this query.
rgds
ananda
April 19, 2011 at 7:20 am
ananda.murugesan (4/19/2011)
HowardW (4/19/2011)
What SQL Server version and compatibility mode are the two databases? The first query will only work on SQL 2005 + (and only in 90+ compatibility mode)SQL server compatibility 100 for two database.
one database was working fine and another database not working this query.
rgds
ananda
Hmm, that's the exact error you'd get if it was due to compatibility.
Does just running:
select * from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats
Give the same error?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply