DBA and SysAdmin World

July 30, 2008

Database Options

Filed under: SQL Server — Tags: , — solihinho @ 8:48 PM
homer_simpson_doh_02
Background
Yesterday, one of the batch suddenly failed with error message “command time out”. Usually this batch was running about 4-5 minutes. When I tried to run the SP using query analyzer and activated “Show Execution Plan”, on the execution plan there was a message that this query missing a statistic. After I created the statistic, this batch is running properly right now.

After that I checked the “auto create statistic” database option for this database. I thought every databases (there are more than 200 databases in 3 servers for production) already set to “auto create statistic”. Unfortunately for this database, the option was off. That’s why this issue can come out Striaght Face.

How To Check and Change Database Option?
On SQL Server 2000, you can do it by SQL Server Enterprise Manager. Just right click on the relevant database and choose properties. Click on “options” tab” as show on below picture. For change the value, you can easily check or uncheck the option and just click OK to confirm your change.

Time out To change this option, DBA must understand what this options are for. Sometimes default installation value is not the best option for the database.

On SQL Server 2005, use Microsoft SQL Server Management Studio. Similar with Enterprise Manager, just right click on relevant database and choose properties. Click on options page as show on below picture. To change the value just click on the combo box and choose the listed value.

As I said before, in our production server there are hundreds of database. After that case, I have to check all the databases option to make sure option “auto create statistic” is already on. Therefore I write a script to make this task easier.

IdeaSQL Server both 2000 and 2005 have provided function DATABASEPROPERTY and DATABASEPROPERTYEX to view database options. For further information about these functions, you can read it from SQL Server BOL.

Supporting Script

/*
   This script used for list all database options
   revision history :
   ???? - Solihin - www.solihinho.wordpress.com 

   compatibility : SQL Server 2000 and SQL 2005
*/

SELECT s.name AS DatabaseName
	, DATABASEPROPERTYEX(s.name, 'Recovery') AS RecoveryModel
	, DATABASEPROPERTYEX(s.name, 'Status') AS Status
	, CASE DATABASEPROPERTY(s.name, 'IsAutoShrink')
			when 1 then 'Yes'
			when 0 then 'No'
			else 'Invalid input' END AS IsAutoShrink
	, CASE DATABASEPROPERTY(s.name, 'IsAutoUpdateStatistics')
			when 1 then 'Yes'
			when 0 then 'No'
			else 'Invalid input' END AS IsAutoUpdateStatistics
	, CASE DATABASEPROPERTY(s.name, 'IsAutoCreateStatistics')
			when 1 then 'Yes'
			when 0 then 'No'
		         else 'Invalid input' END AS IsAutoCreateStatistics
	, CASE DATABASEPROPERTY(s.name, 'IsReadOnly')
			when 1 then 'Yes'
		         when 0 then 'No'
			else 'Invalid input' END AS IsReadOnly
	, CASE DATABASEPROPERTY(s.name, 'IsTruncLog')
			when 1 then 'Yes'
			when 0 then 'No'
			else 'Invalid input' END AS IsTruncLog
	, CASE DATABASEPROPERTY(s.name, 'IsSuspect')
			when 1 then 'Yes'
 			when 0 then 'No'
			else 'Invalid input' END AS IsSuspect
	, CASE DATABASEPROPERTY(s.name, 'IsAutoClose')
			when 1 then 'Yes'
			when 0 then 'No'
			else 'Invalid input' END AS IsAutoClose
FROM master..sysdatabases s
ORDER BY s.name
IdeaTo list the databases with definite options, you just add where clause on above script. i.e : to list all read-only database add following T-SQL “where DATABASEPROPERTY(s.name, ‘IsReadOnly’) = 1

How To Change DB Option Using T-SQL
There are 2 ways that you can use to change DB options using T-SQL
1. use DDL statement ALTER database
2. use SP sp_dboptions

I think you just can read from SQL Server BOL for further information about how to use these T-SQL statement but I will give you some examples so that you have little illustration about these statements.

What to do? using sp_dboptions using ALTER DATABASE
Turn off auto shrink
sp_dboption '[dbname]' , 'trunc. log on chkpt.', 'FALSE'
ALTER DATABASE dbname SET AUTO_SHRINK OFF
Turn on auto update statistic
sp_dboption '[dbname]' , 'auto update statistics', 'TRUE'
ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON
Change Recovery Model become SIMPLE not available ALTER DATABASE kamorodb SET RECOVERY SIMPLE

Yesterday I just changed all my development databases recovery model become “SIMPLE” since there is no necessity to recover the disaster database until point in time failure. Should I change the recovery model one at a time Sigh? Of course we can write a script to make our life easier Whew

/*
   compatibility : SQL Server 2000 and SQL 2005
*/
use master
GO
sp_msforeachdb '
   declare @sql VARCHAR(1000)
   IF DATABASEPROPERTYEX(''?'', ''Recovery'') <> ''SIMPLE''
   begin
     SET @sql = ''ALTER DATABASE ? SET RECOVERY SIMPLE''
     exec(@sql)
   end'
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: