Page Verify Setting

Question

Post reply

Page Verify Setting

imranalaskar

SSC Enthusiast

Points: 190
More actions
June 2, 2014 at 3:09 am

#396846

Hi There,
I want to find out the current page_verify setting of all the databases my environment on all the servers.
If it is sql 2000 i want to set torn page detection on and if it is sql 2005 i want to set checksum enabled.
Can you guys please help me with the query.
Thanks in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

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

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Execute following command to find the status:

select name, page_verify_option_desc from sys.databases

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

The code below generates the commands to change the PAGE_VERIFY option. The CASE statements in the SELECT part is used to determine the correct option depending on the compatibility level of the [master] database. The same CASE statement in the WHERE clause filters out all databases that allready have the correct setting.

select

'ALTER DATABASE ['+name+'] SET PAGE_VERIFY '

+ case when (select compatibility_level from sys.databases where name = 'master') > 80

then 'CHECKSUM'

else 'TORN_PAGE_DETECTION'

end

+ ' WITH NO_WAIT' as alter_database_command

from sys.databases

where page_verify_option_desc <> case when (select compatibility_level from sys.databases where name = 'master') > 80

then 'CHECKSUM'

else 'TORN_PAGE_DETECTION'

end

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Lempster SSCoach Points: 15651 More actions · Answer 3

You could also used Policy-Based Management to check on a scheduled basis (or an ad-hoc basis) for databases that do not have the desired setting for 'Page Verify'.

Regards

Lempster

imranalaskar SSC Enthusiast Points: 190 More actions · Answer 4

Thanks..but I have all my registered server in my SSMS and I want to directly execute the query which will change the page_verify option of all the servers in one query. I hope it is possible to do. Rather than select it should be alter database.

imranalaskar SSC Enthusiast Points: 190 More actions · Answer 5

Thanks..but I have all my registered server in my SSMS and I want to directly execute the query which will change the page_verify option of all the servers in one query. I hope it is possible to do. Rather than select it should be alter database.

Lempster SSCoach Points: 15651 More actions · Answer 6

imranalaskar (6/3/2014)
Thanks..but I have all my registered server in my SSMS and I want to directly execute the query which will change the page_verify option of all the servers in one query. I hope it is possible to do. Rather than select it should be alter database.

Ok, if you have one server designated as your Central Management Server, you can execute the query posted by HanShi on your Central Management Server and it will also be executed on all the servers you have registered under your Central Management Server.

Regards

Lempster

imranalaskar SSC Enthusiast Points: 190 More actions · Answer 7

Actually it should directly change the setting rather than giving me the script as output..

Your reply on this much appreciated.

HanShi SSC-Dedicated Points: 33507 More actions · Answer 8

You have to write some code to loop through and execute the generated commands. Because you want this to be initiated from 'registered servers' it is a bit more complicated, because of the multi-server execution.

I guess you need to do the following:

- open a multi-server query window

- write code to create a (temporary) stored procedure (this stored procedure will be created on each instance and execution will be limited for that instance only)

- within this stored procedure you generate the commands to change the setting (see previous posts)

- next (still within this stored procedure) you loop through and execute the generated commands

- finally you execute the stored procedure

- drop the stored procedure (because it's a one-time-only execution)

So in short the code in the query window will look like this:

CREATE stored PROCEDURE {#name} AS

BEGIN

/****/

{put code here to generate SQL-commands and store in table}

/****/

SELECT @rowcount = COUNT(*)

,@row = 1

FROM TABLE

-- loop through generated commands

WHILE @row <= @rowcount

BEGIN

SELECT @sql_command = {command-column}

FROM TABLE

WHERE rownumber = @row

EXECUTE (@sql_command)

SET @row = @row + 1

END

-- execute the above created stored procedure

EXEC #name

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

free_mascot One Orange Chip Points: 27169 More actions · Answer 9

It would be advisable to create the code and than execute it so that you can have more control. If you directly performing chang in your code it happens in background and you will have less visibility to track it.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."