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

July 21, 2008

Database Space Allocated

Filed under: Script — Tags: , — solihinho @ 10:26 PM

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.

IdeaOn 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.

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

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.

July 19, 2008

Clean up MSDB

Filed under: Maintenance — Tags: , , — solihinho @ 8:33 AM

Clean Up 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 Doh.

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
LoserThis 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 Cool. Myself successful reduce MSDB size from 8 GB become 1.5 GB with 3 months retention data.

July 17, 2008

Hello World !

Filed under: Uncategorized — solihinho @ 3:27 PM
sql2k5

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 Happy.

Regards,
Me

Blog at WordPress.com.