Script mismatch different database

Question

Script mismatch different database

SQL Galaxy

One Orange Chip

Points: 29472
More actions
April 16, 2011 at 7:07 am

#238611

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

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 Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

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

My Blog
www.aureus-salah.com

HowardW One Orange Chip Points: 29109 More actions · Answer 3

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 Galaxy One Orange Chip Points: 29472 More actions · Answer 4

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

HowardW One Orange Chip Points: 29109 More actions · Answer 5

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?