Database Options

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 .

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.

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.

SQL 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

To 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 ? Of course we can write a script to make our life easier

/* 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'

Database Space Allocated

As we see from this picture, SQL Server 2000 taskpad used for gathering information of the database about its space allocated, space used and its free space.

On SQL Server 2005 use Microsoft SQL Server Management Studio, just right click on the database and choose properties.

One of DBA task is monitor the database size which is mean if there is a database with large free space, DBA can shrink the database to hinder the server run out of disk space.

use dbcc shrinkfile or dbcc shrinkdatabase to shrink your database. See BOL for further information

Issue
When the DBA manages tens even hundreds database, what should the DBA do to check the free space status of all the databases. Click one by one on the databases if not a pleasant job I think.

Supporting script
Below script should help DBA to list all the databases and will return a result set with the following information.

Column Name	Description
Database Name	The name of the database
Logical Name	The logical name of the data file. You will need this information when using dbcc shrinkfile
Usage	The data file used for Data or Log?
Space Allocated (MB)	Space allocated for the data file in MB
Space Used (MB)	Space used by the data file in MB
Free Space (MB)	Free Space = Space Allocated – Space Used
Space Used (%)	Percentage space used on the data file. 100% mean there is no free space of the data file
Free Space (%)	Percentage free space on the data file

/* This script used for list all database space allocated and its free space revision history : 28 Feb 2007 - Solihin - www.solihinho.wordpress.com compatibility : SQL Server 2000 and SQL 2005 */ SET NOCOUNT ON IF OBJECT_ID('tempdb..#DBSTATS') IS NOT NULL BEGIN DROP TABLE #DBSTATS END CREATE TABLE #DBSTATS ( dbname sysname, lname sysname, usage varchar(20), [size] decimal(9, 2) NULL , [used] decimal(9, 2) NULL ) IF OBJECT_ID('tempdb..#temp_log') IS NOT NULL BEGIN DROP TABLE #temp_log END CREATE TABLE #temp_log ( DBName sysname, LogSize real, LogSpaceUsed real, Status int ) IF OBJECT_ID('tempdb..#temp_sfs') IS NOT NULL BEGIN DROP TABLE #temp_sfs END CREATE TABLE #temp_sfs ( fileid int, filegroup int, totalextents int, usedextents int, name varchar(1024), filename varchar(1024) ) DECLARE @dbname sysname ,@sql varchar(8000) IF OBJECT_ID('tempdb..#temp_db') IS NOT NULL BEGIN DROP TABLE #temp_db END SELECT name INTO #temp_db FROM master.dbo.sysdatabases WHERE DATABASEPROPERTY(name,'IsOffline') = 0 AND has_dbaccess(name) = 1 ORDER BY name WHILE (1 = 1) BEGIN SET @dbname = NULL SELECT TOP 1 @dbname = name FROM #temp_db ORDER BY name IF @dbname IS NULL GOTO _NEXT SET @sql = ' USE ' + @dbname + ' TRUNCATE TABLE #temp_sfs INSERT INTO #temp_sfs EXECUTE(''DBCC SHOWFILESTATS'') INSERT INTO #DBSTATS (DBNAME, LNAME, USAGE, [SIZE], [USED]) SELECT db_name(), NAME, ''Data'' , totalextents * 64.0 / 1024.0 , usedextents * 64.0 / 1024.0 FROM #temp_sfs INSERT INTO #DBSTATS (DBNAME, LNAME, USAGE, [SIZE], [USED]) SELECT db_name(), name, ''Log'', null, null FROM sysfiles WHERE status & 0x40 = 0x40' EXEC(@sql) DELETE FROM #temp_db WHERE name = @dbname END _NEXT: INSERT INTO #Temp_Log EXECUTE ('DBCC SQLPERF(LOGSPACE)') UPDATE #DBSTATS SET SIZE = B.LogSize , USED = LogSize * LogSpaceUsed / 100 FROM #DBSTATS A INNER JOIN #Temp_Log B ON (A.DBNAME = B.DBNAME)AND(A.Usage = 'LOG') SELECT dbname AS [database name], lname AS [logical data name], usage, [size] AS [space allocated (MB)], used AS[space used (MB)], [size] - used AS [free space (MB)], cast(used/[size]*100 AS numeric(9,2)) AS [space used %], cast(100-(used/[size]*100) AS numeric(9,2)) AS [free space %] FROM #DBSTATS ORDER BY dbname, usage DROP TABLE #DBSTATS DROP TABLE #temp_db DROP TABLE #temp_sfs DROP TABLE #temp_log SET NOCOUNT OFF

Best Practice
This script can be modified and keep the result set to one table. Don’t forget to add timestamp column to this table for information when we collect this data. Just create a job scheduler to run this script periodically so you have information about database growth. You will be need this information when you have to do capacity planning.

Clean up MSDB

Problem
I am facing a problem with MSDB database. Right now its size about 8 GB. As we know MSDB is SQL server system database beside master, model and tempdb used for keep backup and restore database log. SQL Server 2000 have provided “sp_delete_backup_and_restore_history” or “sp_delete_backuphistory” on SQL Server 2005. Both SP can be found in MSDB. When I tried use these SP especially “sp_delete_backup_and_restore_history” on our development server, there is no issue on it. But when tried to production which is larger than development, the issue occurred. It take about 48 minutes just for deleting 1 day history data .

Solution
I did a modification on the code such as using looping, inner join etc. Every 1 cycle of loop, the script trying to delete 1000 rows of backupset table as master table.

usage :
    exec dbo.spDeleteBackupRestoreHistory 60 which is mean we just want keep 60 day history data on backupset table

script :

/* created by : solihin ho - July 2008 - www.solihinho.wordpress.com compatibility : SQL Server 2000 and SQL Server 2005 */ CREATE PROC spDeleteBackupRestoreHistory (@RetentionDay INT) AS SET XACT_ABORT ON SET NOCOUNT ON DECLARE @OldDate DateTime, @RowCount INT SELECT @OldDate = DateAdd(dd, @RetentionDay * -1, GetDate()) IF OBJECT_ID('tempdb..#backup') IS NOT NULL DROP TABLE #backup CREATE TABLE #backup ( backup_set_id int PRIMARY KEY, media_set_id int, is_exist char(1) DEFAULT 'N' ) WHILE (1 = 1) BEGIN TRUNCATE TABLE #backup -- 'Preparing...' INSERT INTO #backup(backup_set_id, media_set_id) SELECT TOP 1000 backup_set_id, media_set_id FROM msdb.dbo.backupset bs WHERE bs.backup_finish_date < @OldDate ORDER BY bs.backup_set_id SET @RowCount = @@RowCount IF @RowCount = 0 GOTO _EXIT BEGIN TRAN -- 'delete table restorefile' DELETE FROM msdb.dbo.restorefile FROM msdb.dbo.restorefile rf INNER JOIN msdb.dbo.restorehistory rh ON rf.restore_history_id = rh.restore_history_id INNER JOIN #backup bs ON rh.backup_set_id = bs.backup_set_id -- 'delete restorefilegroup' DELETE FROM msdb.dbo.restorefilegroup FROM msdb.dbo.restorefilegroup rfg INNER JOIN msdb.dbo.restorehistory rh ON rfg.restore_history_id = rh.restore_history_id INNER JOIN #backup bs ON rh.backup_set_id = bs.backup_set_id -- 'delete restorehistory' DELETE FROM msdb.dbo.restorehistory FROM msdb.dbo.restorehistory rh INNER JOIN #backup bs ON bs.backup_set_id = rh.backup_set_id -- 'delete backupfile' DELETE FROM msdb.dbo.backupfile FROM msdb.dbo.backupfile bf INNER JOIN #backup bs ON bs.backup_set_id = bf.backup_set_id -- 'delete backupset' DELETE FROM msdb.dbo.backupset FROM msdb.dbo.backupset bs INNER JOIN #backup b ON bs.backup_set_id = b.backup_set_id -- 'search media_set_id that not exists to backupset' UPDATE #backup SET is_exist = 'Y' FROM #backup b INNER JOIN msdb.dbo.backupset bs ON bs.media_set_id = b.media_set_id -- 'delete backupmediafamily' DELETE FROM msdb.dbo.backupmediafamily FROM msdb.dbo.backupmediafamily bmf INNER JOIN #backup b ON bmf.media_set_id = b.media_set_id WHERE b.is_exist = 'N' -- 'delete backupmediaset' DELETE FROM msdb.dbo.backupmediaset FROM msdb.dbo.backupmediaset bms INNER JOIN #backup b ON bms.media_set_id = b.media_set_id WHERE b.is_exist = 'N' COMMIT TRAN END _EXIT: DROP TABLE #backup

Still have performance issue ?
Perhaps you still have an issue with the performance, just alter the SP and comment the script  from “search media_set_id that not exists to backupset” until “delete backupmediaset”. We can delete backupmediafamily and backupmediaset table lately. After you run the script until determined retention period, you just run below script to delete those 2 tables

DELETE FROM msdb.dbo.backupmediafamily FROM msdb.dbo.backupmediafamily bmf LEFT OUTER JOIN msdb.dbo.backupset b ON b.media_set_id = bmf.media_set_id WHERE b.media_set_id IS NULL DELETE FROM msdb.dbo.backupmediaset FROM msdb.dbo.backupmediaset bmf LEFT OUTER JOIN msdb.dbo.backupset b ON b.media_set_id = bmf.media_set_id WHERE b.media_set_id IS NULL

This performance issue caused by when we want to delete backupmediafamily and backupmediaset need to join to backupset table base on media_set_id column. The rule is we can delete the data on these tables if the media_set_id does not exists anymore in backupset table. The problem is there is no index base on this column on backupset.

Best Practice
After you finish clean up the MSDB until one retention period, just schedule that script whether weekly or even daily up to your backup and restore transaction. Should be there is no issue on MSDB size . Myself successful reduce MSDB size from 8 GB become 1.5 GB with 3 months retention data.

Hello World !

Hi! Right now I am working as System and SQL Server Administrator. I was a developer before. Therefore I learn so much thing about how to manage the server and maintenance the database. I’m not the expert of this area but I think I can share one or two things from what I do in the office. All I need is a comment from you so we can learn from each other. Hope there is a problem that you are facing that can be resolved by this blog .

Regards,
Me