DBA and SysAdmin World

February 10, 2009

Review Fill Factor of The Indexes

Filed under: Script — Tags: , , — solihinho @ 12:53 AM
analyze Background
When I worked as DBA in my office, It has already hundreds of databases. I have to know the functionality of each databases. How often the tables of the database are accessed, inserted and updated by users. Once you already know, it is easier for DBA to decide the fill factor for the index of the tables.

The right value of the fill factor will upgrade the performance of the application. The fill factor value is a percentage between 0 to 100. This percentage specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.

The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. Trying to maintain the extra space on the data pages would defeat the purpose of originally using the fill factor because SQL Server would have to perform page splits to maintain the percentage of free space, specified by the fill factor, on each page as data is entered. Therefore, if the data in the table is significantly modified and new data added, the empty space in the data pages can fill. In this situation, the index can be re-created and the fill factor specified again to redistribute the data (SQL Server BOL 2000).

Therefore I created script for listing all indexes’s fill factor. There are 2 types of script. One for SQL 2000 and other for SQL 2005. For SQL 2005 also list value of the index fragmentation.

Script
SQL Server 2000

/*
  Created by : Solihin ho - https://solihinho.wordpress.com
  
  Compatibility : SQL 2000

*/

IF object_id('tempdb..#result') IS NOT NULL
   DROP TABLE #result

CREATE TABLE #result
(
   DBName       sysname,
   TableName    sysname,
   IndexName    sysname,
   [Rows]       int,
   [FillFactor] tinyint,
   [TimeStamp]  datetime
)

GO

sp_msforeachdb 'USE ? 
INSERT #result (DbName, TableName, IndexName, [Rows], [FillFactor], [TimeStamp])
SELECT db_name() as DbName
,o.name as TableName
,i.name as IndexName
,i.rows as RowsCount
,i.OrigFillFactor
,GetDate() as [TimeStamp]
FROM sysindexes i
INNER JOIN sysobjects o ON i.id = o.id
WHERE i.indid > 0 and i.indid < 255
AND i.name NOT LIKE ''_WA_Sys_%'''

SELECT * FROM #Result

SQL Server 2005 and Next Version

/*
  Created by : Solihin ho - https://solihinho.wordpress.com
  
  Compatibility : SQL 2005 and next version

*/

IF object_id('tempdb..#result') IS NOT NULL
   DROP TABLE #result

CREATE TABLE #result
(
   DBName       sysname,
   TableName    sysname,
   IndexName    sysname,
   [Rows]       int,
   [FillFactor] tinyint,
   Index_Fragmentation float,
   page_count   int, 
   [TimeStamp]  datetime
)

GO


sp_msforeachdb 'USE ?
INSERT INTO #Result (DBName, TableName, IndexName
    , [FillFactor], [Rows], Index_Fragmentation
    , page_count, [TimeStamp])
SELECT
  db_name() AS DbName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
, GetDate() as [TimeStamp]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B 
   ON A.object_id = B.object_id
INNER JOIN sys.indexes C 
   ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D 
   ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0'

SELECT * FROM #Result

What next?
You can schedule above script and keep the result in a table for reviewing next time. You can review the row count to see whether the table grow or not. Just decide the value of the table’s fill factor e.g.: 100% for read only table. For SQL 2005 script, you also review of the index fragmentation. You can review how fast the index become fragmented. You can set the fill factor until you get a best value for the index.

January 31, 2009

Missing an Index? Check for it ..

Filed under: Script — Tags: , , — solihinho @ 12:14 AM
SqlServer2005
Behind the scene
There was a story when I created this script. There was an issue on our production server. After investigated, there was no index on one table but the table has an index on testing environment. Looks like when published the table, the DBA missed publish the index. After this issue, my supervisor asked me to create a script for listing all tables that do not have an index. Every table should be have an index obviously. Below script could not run on SQL Server 2000 and previous version.

Script
Below the script for list index availability of the tables.

Result of the script

Column Name Description
TableName Name of the table
SchemaName Name of the schema
HasIndex Yes if the table has an index vice versa
IndexName Name of the index
IndexKeys Keys of the index
IsPrimaryKey Is this index a primary key?
IndexType Type of the index whether clustered or non clustered
/*
Created by : Solihin ho - https://solihinho.wordpress.com

Compatibility : SQL Server 2005 and next
*/


DECLARE @ObjectID INT, @IndexID INT
DECLARE @ObjectName SYSNAME, @SchemaName SYSNAME
DECLARE @ColumnName SYSNAME
DECLARE @IndexKeys  NVARCHAR(1000)

IF OBJECT_ID ('tempdb..#Result') IS NOT NULL 
  DROP TABLE #Result

CREATE TABLE #Result
(	
   ObjectID     INT,
   ObjectName   SYSNAME,
   SchemaName   SYSNAME,
   IndexId      INT,
   IndexName    SYSNAME NULL,
   IndexType    NVARCHAR(60),
   IndexKeys    NVARCHAR(1000),
   HasIndex     VARCHAR(3),
   IsPrimaryKey BIT
)

INSERT INTO #Result (ObjectID, ObjectName, SchemaName, IndexId
           , IndexName, IndexType, HasIndex, IsPrimaryKey)
SELECT o.object_id, o.name AS ObjectName
  , s.name as SchemaName
  , i.index_id, i.name AS IndexName
  , i.type_desc
  , 'No'
  , i.is_primary_key
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.type = 'U'

--Index Keys
IF (OBJECT_ID('tempdb..#keys_temp') IS NOT NULL) 
   DROP TABLE #keys_temp

SELECT r.ObjectID, r.IndexId, c.name AS ColumnName 
INTO #keys_temp
FROM #Result r
LEFT OUTER JOIN sys.index_columns ic 
   ON r.ObjectID = ic.object_id AND r.IndexID = ic.index_id
LEFT OUTER JOIN sys.columns c 
   ON c.column_id = ic.column_id AND r.ObjectID = c.object_id 
WHERE r.IndexID > 0


WHILE (1 = 1)
BEGIN

SET @IndexKeys = ''
SET @ObjectID = NULL
SET @IndexID = NULL

SELECT TOP 1 @ObjectID = ObjectID, @IndexID = IndexID
FROM #keys_temp
ORDER BY ObjectID, IndexID

IF @ObjectID IS NULL
   GOTO _Loop1

WHILE (1 = 1)
BEGIN
 
   SET @ColumnName = NULL
   
   SELECT TOP 1 @ColumnName = ColumnName 
   FROM #keys_temp
   WHERE ObjectID = @ObjectID AND IndexID = @IndexID
   ORDER BY ColumnName
   
   IF @ColumnName IS NULL
     GOTO _Loop2

   SET @IndexKeys = @IndexKeys + @ColumnName + ', '

   DELETE FROM #keys_temp 
   WHERE ObjectID = @ObjectID AND IndexID = @IndexID 
     AND ColumnName = @ColumnName
END

_Loop2:
   UPDATE #Result SET IndexKeys = LEFT(@IndexKeys, LEN(@IndexKeys)-1) 
   WHERE ObjectID = @ObjectID AND IndexID = @IndexID

END
_Loop1:
   DROP TABLE #keys_temp
   
   IF OBJECT_ID('tempdb..#object_temp') IS NOT NULL 
      DROP TABLE #object_temp

   SELECT DISTINCT ObjectName, SchemaName INTO #object_temp 
   FROM #Result
   
   WHILE (1 = 1)
   BEGIN
      SET @ObjectName = NULL
 
      SELECT TOP 1 @ObjectName = ObjectName, @SchemaName = SchemaName
      FROM #object_temp
      ORDER BY ObjectName, SchemaName

      IF @ObjectName IS NULL
         GOTO _Loop3

      IF EXISTS (SELECT * FROM #Result WHERE ObjectName = @ObjectName
                        AND SchemaName = @SchemaName
                        AND IndexID > 0)
      BEGIN
         UPDATE #Result SET HasIndex = 'Yes' 
         WHERE ObjectName = @ObjectName AND SchemaName = @SchemaName                                        
      END 

      DELETE FROM #object_temp WHERE ObjectName = @ObjectName
               AND SchemaName = @SchemaName
   END

_Loop3:
   
   SELECT ObjectName AS TableName
   , SchemaName
   , HasIndex
   , IndexName
   , IndexKeys
   , CASE WHEN IsPrimaryKey = 1 Then 'Yes' ELSE 'No' END AS IsPrimaryKey
   , IndexType
   FROM #Result
   ORDER BY ObjectName, SchemaName


This is the result when I ran the script on AdventureWorks database.
result

January 1, 2009

Query For Listing SQL Server Job Schedule

Filed under: Script — Tags: , , — solihinho @ 10:03 AM
scheduler Background
On this occasion I want to share TSQL script to list Job Scheduler in SQL Server 2000 and next version. If using Enterprise Manager (EM), we can access under Management -> SQL Server Agent -> Jobs (see below picture).
 Job

If using SQL Server Management Studio, it can be accessed under SQL Server Agent -> Jobs (see below picture)
schedule 2005

The issue is little difficult for me to review the schedule of the jobs. I have to click the each job and take note of each schedule. Since all these jobs keep on MSDB database, we are able to query the jobs within its schedule. When we have many jobs both maintenance or monitoring job etc, as a DBA we have to review the time when the jobs will be running. It is important to make our SQL Server load is balance which is mean not very heavy in one time but very light in another time.

The jobs general information keep in msdb.dbo.sysjobs table and the schedule keep in msdb.dbo.sysjobschedules (SQL 2000) or msdb.dbo.sysschedules (SQL 2005 and next). Read SQL Server Book Online (BOL) for further information about the columns of these tables.

Script

To make the main script simpler, I create 3 functions. Just run below script on master database. Actually you can it to another database and don’t forget to change the main query. These 3 functions valid for SQL Server 2000 and next. But there is little differences on the main query since there is a difference schema on table msdb.dbo.sysjobschedules.

USE master

GO

CREATE  FUNCTION fn_freq_interval_desc(@freq_interval INT)  
RETURNS VARCHAR(1000)  
AS  
BEGIN  
   DECLARE @result VARCHAR(1000)  

   SET @result = ''
	   
   IF (@freq_interval & 1 = 1)  
      SET @result = 'Sunday, '  
   IF (@freq_interval & 2 = 2)  
      SET @result = @result + 'Monday, '  
   IF (@freq_interval & 4 = 4)  
      SET @result = @result + 'Tuesday, '  
   IF (@freq_interval & 8 = 8)  
      SET @result = @result + 'Wednesday, '  
   IF (@freq_interval & 16 = 16)  
      SET @result = @result + 'Thursday, '  
   IF (@freq_interval & 32 = 32)  
      SET @result = @result + 'Friday, '  
   IF (@freq_interval & 64 = 64)  
      SET @result = @result + 'Saturday, '  

   RETURN(LEFT(@result,LEN(@result)-1))  
END   

GO

CREATE FUNCTION fn_Time2Str(@time INT)
RETURNS VARCHAR(10)
AS
BEGIN
   DECLARE @strtime CHAR(6)
   SET @strtime = RIGHT('000000' + CONVERT(VARCHAR,@time),6)

   RETURN LEFT(@strtime,2) + ':' + SUBSTRING(@strtime,3,2) + ':' + RIGHT(@strtime,2)
END

GO	

CREATE FUNCTION fn_Date2Str(@date INT)
RETURNS VARCHAR(10)
AS
BEGIN
   DECLARE @strdate CHAR(8)
   SET @strdate = LEFT(CONVERT(VARCHAR,@date) + '00000000', 8)

   RETURN RIGHT(@strdate,2) + '/' + SUBSTRING(@strdate,5,2) + '/' + LEFT(@strdate,4) 
END

Main query for SQL Server 2000
revise: May 18, 2009 : fixed inaccurate next run date

/*
  Created by Solihin Ho - https://solihinho.wordpress.com

  Usage : Change the value of variable @Filter
          'Y' --> display only enabled job
          'N' --> display only disabled job
          'A' --> display all job
          'X' --> display job which is duration already end
*/

DECLARE @Filter CHAR(1)
SET @Filter = 'A'


DECLARE @sql VARCHAR(8000)
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
BEGIN
    DROP TABLE #xp_results
END

CREATE TABLE #xp_results (
     job_id                UNIQUEIDENTIFIER NOT NULL,
     last_run_date         INT              NOT NULL,
     last_run_time         INT              NOT NULL,
     next_run_date         INT              NOT NULL,
     next_run_time         INT              NOT NULL,
     next_run_schedule_id  INT              NOT NULL,
     requested_to_run      INT              NOT NULL, 
     request_source        INT              NOT NULL,
     request_source_id     sysname          COLLATE database_default NULL,
     running               INT              NOT NULL, 
     current_step          INT              NOT NULL,
     current_retry_attempt INT              NOT NULL,
     job_state             INT              NOT NULL
)

SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner	

SET @sql = '
SELECT 
  j.Name AS JobName
, c.Name AS Category
, CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled
, CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled
, j.Description 
, CASE s.freq_type 
     WHEN  1 THEN ''Once''
     WHEN  4 THEN ''Daily''
     WHEN  8 THEN ''Weekly''
     WHEN 16 THEN ''Monthly''
     WHEN 32 THEN ''Monthly relative''
     WHEN 64 THEN ''When SQL Server Agent starts'' 
     WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs 	
, CASE s.freq_type 
     WHEN  1 THEN ''O''
     WHEN  4 THEN ''Every '' 
        + convert(varchar,s.freq_interval) 
        + '' day(s)''
     WHEN  8 THEN ''Every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' weeks(s) on '' 
        + master.dbo.fn_freq_interval_desc(s.freq_interval)					  
     WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval) 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' 
     WHEN 32 THEN ''The '' 
        + CASE s.freq_relative_interval	
            WHEN  1 THEN ''First''
            WHEN  2 THEN ''Second''
            WHEN  4 THEN ''Third''	
            WHEN  8 THEN ''Fourth''
            WHEN 16 THEN ''Last'' END 
        + CASE s.freq_interval 
            WHEN  1 THEN '' Sunday''
            WHEN  2 THEN '' Monday''
            WHEN  3 THEN '' Tuesday''
            WHEN  4 THEN '' Wednesday''
            WHEN  5 THEN '' Thursday''
            WHEN  6 THEN '' Friday''
            WHEN  7 THEN '' Saturday''
            WHEN  8 THEN '' Day''
            WHEN  9 THEN '' Weekday''
            WHEN 10 THEN '' Weekend Day'' END 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' END AS Occurs_detail	
, CASE s.freq_subday_type 
     WHEN 1 THEN ''Occurs once at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
     WHEN 2 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Seconds(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 4 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Minute(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 8 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Hour(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency
, CASE WHEN s.freq_type =  1 THEN ''On date: '' 
          + master.dbo.fn_Date2Str(active_start_date) 
          + '' At time: '' 
          + master.dbo.fn_Time2Str(s.active_start_time)
       WHEN s.freq_type < 64 THEN ''Start date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' end date: '' 
          + master.dbo.fn_Date2Str(s.active_end_date) END as Duration
, master.dbo.fn_Date2Str(xp.next_run_date) + '' '' 
    + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date
FROM  msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules s (nolock) ON j.job_id = s.job_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id
WHERE 1 = 1 
@Filter
ORDER BY j.name'

IF @Filter = 'Y'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ')
ELSE
IF @Filter = 'N'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ')
ELSE
IF @Filter = 'X'
   SET @sql = REPLACE(@sql,'@Filter', 
                            'AND s.active_end_date < convert(varchar(8),GetDate(),112) ')
ELSE
   SET @sql = REPLACE(@sql,'@Filter','')

EXEC(@sql)

Main Query for SQL Server 2005 and next version

/*
  Created by Solihin Ho - https://solihinho.wordpress.com

  Usage : Change the value of variable @Filter
          'Y' --> display only enabled job
          'N' --> display only disabled job
          'A' --> display all job
          'X' --> display job which is duration already end
*/

DECLARE @Filter CHAR(1)
SET @Filter = 'A'


DECLARE @sql VARCHAR(8000)
DECLARE @is_sysadmin INT
DECLARE @job_owner   sysname

IF OBJECT_ID('tempdb..#xp_results') IS NOT NULL
BEGIN
    DROP TABLE #xp_results
END


CREATE TABLE #xp_results (
     job_id                UNIQUEIDENTIFIER NOT NULL,
     last_run_date         INT              NOT NULL,
     last_run_time         INT              NOT NULL,
     next_run_date         INT              NOT NULL,
     next_run_time         INT              NOT NULL,
     next_run_schedule_id  INT              NOT NULL,
     requested_to_run      INT              NOT NULL, 
     request_source        INT              NOT NULL,
     request_source_id     sysname          COLLATE database_default NULL,
     running               INT              NOT NULL, 
     current_step          INT              NOT NULL,
     current_retry_attempt INT              NOT NULL,
     job_state             INT              NOT NULL)



SELECT @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
    EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
	
SET @sql = '
SELECT 
  j.Name AS JobName
, c.Name AS Category
, CASE j.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Enabled
, CASE s.enabled WHEN 1 THEN ''Yes'' else ''No'' END as Scheduled
, j.Description 
, CASE s.freq_type 
     WHEN  1 THEN ''Once''
     WHEN  4 THEN ''Daily''
     WHEN  8 THEN ''Weekly''
     WHEN 16 THEN ''Monthly''
     WHEN 32 THEN ''Monthly relative''
     WHEN 64 THEN ''When SQL Server Agent starts'' 
     WHEN 128 THEN ''Start whenever the CPU(s) become idle'' END as Occurs 	
, CASE s.freq_type 
     WHEN  1 THEN ''O''
     WHEN  4 THEN ''Every '' 
        + convert(varchar,s.freq_interval) 
        + '' day(s)''
     WHEN  8 THEN ''Every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' weeks(s) on '' 
        + master.dbo.fn_freq_interval_desc(s.freq_interval)					  
     WHEN 16 THEN ''Day '' + convert(varchar,s.freq_interval) 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' 
     WHEN 32 THEN ''The '' 
        + CASE s.freq_relative_interval	
            WHEN  1 THEN ''First''
            WHEN  2 THEN ''Second''
            WHEN  4 THEN ''Third''	
            WHEN  8 THEN ''Fourth''
            WHEN 16 THEN ''Last'' END 
        + CASE s.freq_interval 
            WHEN  1 THEN '' Sunday''
            WHEN  2 THEN '' Monday''
            WHEN  3 THEN '' Tuesday''
            WHEN  4 THEN '' Wednesday''
            WHEN  5 THEN '' Thursday''
            WHEN  6 THEN '' Friday''
            WHEN  7 THEN '' Saturday''
            WHEN  8 THEN '' Day''
            WHEN  9 THEN '' Weekday''
            WHEN 10 THEN '' Weekend Day'' END 
        + '' of every '' 
        + convert(varchar,s.freq_recurrence_factor) 
        + '' month(s)'' END AS Occurs_detail	
, CASE s.freq_subday_type 
     WHEN 1 THEN ''Occurs once at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
     WHEN 2 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Seconds(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 4 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Minute(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) 
     WHEN 8 THEN ''Occurs every '' 
        + convert(varchar,s.freq_subday_interval) 
        + '' Hour(s) Starting at '' 
        + master.dbo.fn_Time2Str(s.active_start_time) 
        + '' ending at '' 
        + master.dbo.fn_Time2Str(s.active_end_time) END AS Frequency
, CASE WHEN s.freq_type =  1 THEN ''On date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' At time: '' 
          + master.dbo.fn_Time2Str(s.active_start_time)
       WHEN s.freq_type < 64 THEN ''Start date: '' 
          + master.dbo.fn_Date2Str(s.active_start_date) 
          + '' end date: '' 
          + master.dbo.fn_Date2Str(s.active_end_date) END as Duration
, master.dbo.fn_Date2Str(xp.next_run_date) + '' '' 
    + master.dbo.fn_Time2Str(xp.next_run_time) AS Next_Run_Date
FROM  msdb.dbo.sysjobs j (NOLOCK)
INNER JOIN msdb.dbo.sysjobschedules js (NOLOCK) ON j.job_id = js.job_id
INNER JOIN msdb.dbo.sysschedules s (NOLOCK) ON js.schedule_id = s.schedule_id
INNER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
INNER JOIN #xp_results xp (NOLOCK) ON j.job_id = xp.job_id
WHERE 1 = 1 
@Filter
ORDER BY j.name'

IF @Filter = 'Y'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 1 ')
ELSE
IF @Filter = 'N'
   SET @sql = REPLACE(@sql,'@Filter',' AND j.enabled = 0 ')
ELSE
IF @Filter = 'X'
   SET @sql = REPLACE(@sql,'@Filter', 
                            'AND s.active_end_date < convert(varchar(8),GetDate(),112) ')
ELSE
   SET @sql = REPLACE(@sql,'@Filter','')

EXEC(@sql)

Below sample result of above script
result

December 20, 2008

Disable or Enable Constraint

Filed under: SQL Server — Tags: , — solihinho @ 11:33 PM
constraint Issue
As there are 3 database environments such as development, testing and production, sometimes there is a request to refresh master or transaction tables in testing from production with specific period.

1st issue is sometimes some data already exists in testing. So I need to delete the existing data. For deleting data we have to delete starting from detail table to master table.

2nd issue is when inserting data from production to testing, we need to insert data starting from master table to detail table. If we do not follow this rule, the action will be failed due to foreign key constraint. It is little bit take time for DBA to analysis which the master table or detail table. For data movement from one to another environment which have identical schema, the fastest way is disable the constraints during the data movement and enable it back after it is finish. Below script only valid for check and foreign key constraint.

Script

Disable Constraints All Tables

sp_msforeachtable @command1 = "print 'Disable constraint on ? '"
                , @command2 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL"

Enable Constraints All Tables

sp_msforeachtable @command1 = "print 'Enable constraint on ? '"
                , @command2 = "ALTER TABLE ? CHECK CONSTRAINT ALL"

Time outAfter you move the data and enable the constraints, this enabling constraint doesn’t check the data that already exists in the table. So you will not receive any violation message though there is a violated data. Use DBCC CHECKCONSTRAINTS to check whether there is a violated data or not and just clean it manually.
Time out Since the script enable/disable all the constraints and triggers, just make sure before you run the script, note the constraint already disabled before you want to enable all the constraints vice versa by running below script.

List All Constraints Status 

SELECT
    CASE WHEN OBJECTPROPERTY(constid, 'cnstisdisabled') = 0 THEN 'Enabled'
       ELSE 'Disabled' END AS Status 
   ,O1.Name AS ConstraintName
   ,O2.Name AS TableName
   ,C1.name AS ColumnName
   ,O3.name AS ReferencedTable
   ,C2.name AS ReferencedColumn
FROM sysforeignkeys FK
INNER JOIN sysobjects O1 ON O1.id = FK.constid
INNER JOIN sysobjects O2 ON O2.id = FK.fkeyid
INNER JOIN syscolumns C1 ON C1.id = FK.fkeyid AND C1.colid = FK.fkey
INNER JOIN sysobjects O3 ON O3.id = FK.rkeyid
INNER JOIN syscolumns C2 ON C2.id = FK.rkeyid AND C2.colid = FK.rkey
ORDER BY TableName, ConstraintName, FK.KeyNo

December 8, 2008

Moving Database File To Another Disk

Filed under: Script, SQL Server — Tags: , , — solihinho @ 3:09 PM
moveAll our databases server using SAN (Storage Area Network) for the disk storage. Last time we used DELL AX100 for the storage. After that we upgraded to DELL MD3000.  DBA was asked to move the databases to the new disk.

We have hundred of databases on one server. The best way to do this is using detach and attach method. In this case, we want to move all of user databases, so what we did is just detach all databases, stop the SQL Server, move the data file to the new disk, start the SQL Server and attach all the data files. We need downtime quite long to copy the files to new storage. If you does not allow to downtime quite long, just detach and attach the databases one by one. In my case, it is fine to downtime about 1 hour Winking.

What I want to share is a script to generate T-SQL for detaching and attaching the database. Can you imagine if we use Enterprise Manager to detach and attach thousand or hundred databases ?

Step by step to move the data file

1. Before you stop the SQL Server, run below script to generate attach script for all user databases.

SET NOCOUNT ON

DECLARE @dbname         SYSNAME
DECLARE @sqlstr         VARCHAR(4000)
DECLARE @filename       VARCHAR(4000)
DECLARE @result         VARCHAR(8000)
DECLARE @Counter        TINYINT
DECLARE @NFiles         TINYINT

IF OBJECT_ID('tempdb..#db') IS NOT NULL
   DROP TABLE #db

--exclude system databases
SELECT name INTO #db
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','tempdb','msdb')
ORDER BY name

IF OBJECT_ID('tempdb..#files') IS NOT NULL
   DROP TABLE #files

CREATE TABLE #files
(
   DbName       SYSNAME,
   LogicalName  SYSNAME,
   FileId       INT,
   FileName     VARCHAR(1000),
)

IF OBJECT_ID('tempdb..#result') IS NOT NULL
   DROP TABLE #result

CREATE TABLE #result
(
   sql VARCHAR(8000)
)

--get all files
WHILE (1 = 1)
BEGIN
   SET @dbname = NULL

   SELECT TOP 1 @dbname = name FROM #db	

   IF @dbname IS NULL
      GOTO _FINISH1

   SET @sqlstr =  ' USE ' + @dbname
                + ' INSERT INTO #files (DbName, LogicalName, FileId, FileName)'
                + ' SELECT  db_name(), name,  fileid, filename '
                + ' FROM sysfiles  '
                + ' ORDER BY fileid ' 

   EXEC (@sqlstr)

   DELETE FROM #db WHERE name = @dbname
END

_FINISH1:

--generate attach script
WHILE (1 = 1)
BEGIN
   SET @dbname = NULL

   SELECT TOP 1 @dbname = DbName
   FROM #files
   ORDER BY DbName 

   IF @dbname IS NULL
      GOTO _FINISH2

      SET @Counter = 1
      SELECT @NFiles = COUNT(*) FROM #files WHERE DbName = @dbname

      SET @result = 'exec sp_attach_db N''' + @dbname + ''''
      WHILE (@Counter <= @NFiles)
      BEGIN
         SELECT @Filename = FileName
         FROM #files
         WHERE DbName = @DbName AND FileId = @Counter

         SET @result = @result + ',@filename' + CAST(@counter as VARCHAR)
                               + ' = N''' + RTRIM(@FileName) + ''''		

         SET @Counter = @Counter + 1
      END

      INSERT INTO #result
         SELECT @result

      DELETE FROM #files WHERE DbName = @dbname
END

_FINISH2:
      SELECT * FROM #result

      DROP TABLE #db

SET NOCOUNT OFF

This is an example of the result of above script for AdventureWorks and AdventureWorksDW.

exec sp_attach_db N'AdventureWorks',@filename1 = N'D:DataAdventureWorks_Data.mdf'
                                   ,@filename2 = N'E:LogAdventureWorks_Log.ldf'
exec sp_attach_db N'AdventureWorksDW',@filename1 = N'D:DataAdventureWorksDW_Data.mdf'
                                     ,@filename2 = N'E:LogAdventureWorksDW_Log.ldf'
Pumpkin In my local PC, I put all data files (*.mdf, *.ndf) in D:DATA and log files (*.ndf) in E:LOG

Keep this result to another text file

2. Detach All User Databases
Run below query than run the result of this query to detach all user databases. To remainder, detach process will fail if there is an application that using this database. Just make sure there is no process hold this database if detach process is failed. If you detach database using Enterprise Manager, you will see how many connection that holding specified the database. Just clear the connection if you want to detach that database immediately without want to wait the process is done.

--exclude system databases
SELECT replace('exec sp_detach_db ''@dbname'',''true''','@dbname', name) AS result
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','msdb','tempdb','model')

3. Stop SQL Server Database Engine

4. Move all data files and log files to the new disk

5. Start SQL Server Database Engine

6. From result of step 1, change old path with new path e.g.: D:DATA be F:SQLDATA than run this script to attach back all databases.

December 6, 2008

Index Fragmentation in SQL 2005

Filed under: SQL Server — Tags: , , — solihinho @ 11:40 PM
fragment Last Tuesday, our email administrators had a problem. One of their 3rd tools became slow from one day to another day. After they contacted the vendor, the vendor asked to check the database which is using SQL 2005. Because our email administrators do not have knowledge about database, it’s time for DBA take an action Cool.

Money Eyes Some companies think that they don’t need a DBA. These days almost all windows applications using SQL Server as their database. Unfortunately almost of these applications do not provide a task for maintenance their own database. My company has many 3rd party applications that using SQL Server. If there is a performance issue, the vendor always ask the customer to check the database and the box health. So as DBA is possible that we have to maintenance an application of another section like email, citrix, proxy, web application, sharepoint, K2 workflow, Business Object etc besides our in house database application .

After this issue came up, I ask the email admin to granted me as sysadmin role to their database which is located on their box. When I checked the index fragmentation, some of indexes were very fragmented.

Detecting Index Fragmentation
In versions prior to SQL Server 2005, for displaying fragmentation information for the data and indexes of specified table or view using DBCC SHOWCONTIG command. We still can use this command for SQL Server 2005 with some limitations but this feature will be removed in the next version according to MSDN. For the replacement, SQL Server 2005 and next version provide dynamic management function to display information about index fragmentation is sys.dm_db_index_physical_stats. This function need parameters such as database id, table id, index id, partition number and the mode. For further information just visit the MSDN or see SQL Server book online. There are many enhancement from this function than DBCC SHOWCONTIG command such as handle index partition information which is a new feature on SQL Server 2005.

The most important information from this function is column avg_fragmentation_in_percent. The range value from 0 to 100. 100 means the index is fragmented and DBA should reorganize or rebuild the index.

Here is an example how to display the index fragmentation for active database. From function sys.dm_index_physical_stats join to table sys.objects to get the table name, join to sys.indexes to get the index name and current fill factor setting and join to sys.partitions to get the approximate number of rows in the index partition.

SELECT
  db_name() AS DatabaseName
, B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B ON A.object_id = B.object_id
INNER JOIN sys.indexes C ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D ON B.object_id = D.object_id AND A.index_id = D.index_id

Reorganize OR Rebuild Index
In SQL Server 2000, we use DBCC INDEXDEFRAG to reorganize index and DBCC DBREINDEX to rebuild index. This feature is still available on SQL Server 2005 but this feature will be removed in future version of Microsoft SQL Server. In SQL 2005 and next version, these commands will be replaced with ALTER INDEX command. For reorganize the index use REORGANIZE option and for rebuild the index use REBUILD option with ALTER INDEX command.

Rebuild :
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;

Reorganize :

ALTER INDEX [IndexName] ON [SchemaName].[TableName] REORGANIZE;

If you have reorganized or rebuilt the index, you can check again avg_fragmentation_in_percent and the page_count value will reduce.
Reorganize index uses minimal system resources and other processes such as update, insert and delete on this table won’t be blocked.  But if you rebuild the index, other processes on this table won’t be proceeded until the rebuild process is finished.
Rebuild the index is better rather than reorganize the index. But if it is hard to find the right time to rebuild the index especially the huge table so just reorganize the index. It is much better rather doing nothing Batting Eyelashes.

Online Rebuild Index
When working on SQL Server 2000, as our production server must online 24×7, it is little difficult to do rebuild index on big table because during this process other processes will not be able to do DML activities on this table. This is a major hindrance of rebuilding index.

SQL Server 2005 introduces the ability for DBA to do online rebuild index so other processes can access the table when the rebuild is occurring. This feature is really helpful so we can rebuild our index any time. But of course it is better to do it on off peak hours Winking. When doing online rebuild index, just make sure that you have enough disk space on your box because during this process, actually SQL Server create a new index that mimics of current index. After this processing is finished, the old index will be dropped and replace with the new one.

Coffee Online rebuild index is available only on SQL Server 2005 Enterprise Edition.
Online Rebuild Index :
ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD WITH (ONLINE = ON);

Best Practice
When collecting index fragmentation status, it is recommended to keep the data in table. Add timestamp column to this table. Just schedule this task to do it either weekly or monthly.

IdeaJust modified above query for detecting index fragmentation. You can make it loop to all database and keep the result on DBA database. We have a database called SQLDBAdb to keep all information that we need to keep and review.

From this data we can review how fast the indexes become fragmented so we can adjust the fill factor of the index. (See fill factor information from BOL for further information). To see current setting fill factor of the index, we can query from table sys.indexes. To change fill factor value of the index, use ALTER INDEX with FILLFACTOR option.

September 18, 2008

spWho3 active

Filed under: Script — Tags: , — solihinho @ 12:15 AM
question Background
Here’s some common conversations that DBA faces almost everyday
User : What’s going on the database? Why does my report be longer than usually?
DBA : Okay, just let me check. Please wait a second.

What should DBA do?
First thing that DBA should do is check what processes are running in the server.I believe SQL Server DBA know  these SPs that provide from SQL Server is sp_who or sp_who2 (undocumented in SQL Server BOL). sp_who2 is enhancement from sp_who. Both of these stored procedures provide information about processes in the SQL server. We can filter the information to show not the idle processes by using active parameter.
Usage : sp_who2 active

After you type sp_who2 active and run it, it will show us the SP id, user who run this process, host name, program name, CPU time, disk IO etc. The culprit which must be checked is the process with biggest CPU time. Not always but nice to check. Unfortunately, sp_who or sp_who2 does not provide what SP name or query statement is running. To know the SP or query statement, we use DBCC INPUTBUFFER (SPID).

Let me repeat the steps, first we run sp_who2 active. From this SP we get all the processes which is running on that server. Look at the biggest CPU time or disk IO process. Note the SP ID, than run DBCC INPUTBUFFER with parameter SP ID of the process.  You will get the statement which is running on that server. So you can analyze this statement if possibly a culprit or not. i.e : look at the parameter of the statement. Sometimes user run a report with big period.

The issue is sometimes we are not so lucky to find the culprit. We have to run the sp_who2 active and DBCC INPUTBUFFER several times. So why we don’t enhance sp_who2 so that can display the statement when we run sp_who2. In our office, we call it spWho3

IdeaMicrosoft doesn’t recommend to use the prefix “sp_” when we create SP in user-created database because SQL Server look for SP with prefix “sp_” in the following order : master database than user-created database.

Script
This script is enhancement of sp_who2. This script for display active processes only and data order by CPU time descending. You can modified this script depend on your necessity.

SET NOCOUNT ON    

DECLARE
    @SPID	    SMALLINT
   ,@SQLID          SMALLINT
   ,@SqlStatement   VARCHAR(4000) 

IF object_id('tempdb..#tbl_sysprocesses') IS NOT NULL
	DROP TABLE #tbl_sysprocesses     

IF object_id('tempdb..#spid_cmds') IS NOT NULL
	DROP TABLE #spid_cmds     

IF object_id('tempdb..#spid') IS NOT NULL
	DROP TABLE #spid

SELECT
  spid
 ,CAST(NULL AS VARCHAR(8000)) AS commandtext
 ,status
 ,sid
 ,hostname
 ,program_name
 ,cmd
 ,cpu
 ,physical_io
 ,blocked
 ,dbid
 ,convert(SYSNAME, rtrim(loginame)) AS loginname
 ,substring(convert(VARCHAR,last_batch,111) ,6  ,5 )
		+ ' ' +  substring( convert(VARCHAR,last_batch,113) ,13 ,8 )
       as 'last_batch_char'
 INTO   #tbl_sysprocesses
 FROM   master.dbo.sysprocesses (NOLOCK)
 WHERE  NOT(lower(status)  = 'sleeping'
         	AND upper(cmd) IN (
             'AWAITING COMMAND'
            ,'MIRROR HANDLER'
            ,'LAZY WRITER'
            ,'CHECKPOINT SLEEP'
            ,'RA MANAGER' )
         	AND blocked = 0)

--Show only active processes
DELETE #tbl_sysprocesses
WHERE lower(status)  = 'sleeping'
   AND upper(cmd) IN  (
        'AWAITING COMMAND'
       ,'LAZY WRITER'
      ,'CHECKPOINT SLEEP' )     AND blocked = 0  

CREATE TABLE #spid_cmds
(
     SQLID       INT IDENTITY
    ,SPID        INT
    ,EventType   VARCHAR(30)
    ,Parameters  INT
    ,Command     VARCHAR(8000)
)       

SELECT DISTINCT spid INTO #spid FROM #tbl_sysprocesses    

WHILE (1 = 1)
BEGIN
   SET @SPID = NULL
   SELECT TOP 1 @SPID = spid FROM #spid 	     

   IF (@SPID IS NULL) GOTO _NEXT

   INSERT INTO #spid_cmds (EventType, Parameters, Command)
      EXEC('DBCC INPUTBUFFER( ' + @SPID + ')')    

   SELECT @SQLID = MAX(SQLID) FROM #spid_cmds    

   UPDATE #spid_cmds SET spid = @SPID WHERE SQLID = @SQLID      

   DELETE FROM #spid WHERE spid = @SPID
END    

_NEXT:

UPDATE p
SET p.commandtext = s.command
FROM #tbl_sysprocesses p
INNER JOIN #spid_cmds s ON p.spid = s.spid    

SET @SqlStatement =
'
   SELECT     SPID          = spid
     	    ,CommandText
             ,Status        =
                  rtrim(CASE lower(status)
                     When ''sleeping'' THEN lower(status)
                     Else                   upper(status)
                  END)
            ,Login         = loginname
            ,HostName      =
                  CASE hostname
                     When NULL  THEN ''  .''
                     When '' '' THEN ''  .''
                     Else    hostname
                  END
            ,BlkBy         =
                  CASE ISNULL(blocked,0)
                     When 0 THEN 0
                     Else blocked
                  END
            ,DBName        = case when dbid = 0 THEN NULL
			    when dbid <> 0 THEN db_name(dbid) END
            ,Command       = cmd
            ,CPUTime       = cpu
            ,DiskIO        = physical_io
            ,LastBatch     = last_batch_char
            ,ProgramName   = program_name
      FROM  #tbl_sysprocesses
      ORDER BY CPUTime DESC
'
EXEC(@SqlStatement)

DROP TABLE #tbl_sysprocesses
DROP TABLE #spid_cmds
DROP TABLE #spid  

SET NOCOUNT ON

September 8, 2008

Useful SQL Server Commands

Filed under: SQL Server — Tags: , — solihinho @ 1:08 AM
SQL Server
Since I am be a DBA, there are some system SPs (Stored Procedures) and DBCC (Database Console Commands) that I use regularly on my daily activities for display some information and for maintain the database.

Commands For Display Information

  • Reports information about a specified database or all databases
    use : sp_helpdb
  • Reports information about the indexes on a table or view
    use : sp_helpindex
  • Returns statistics information about columns and indexes on the specified table
    use : sp_helpstats
  • Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure
    use : sp_helptext
  • Reports information about a database object
    use : sp_help
  • Returns the physical names and attributes of files associated with the current database. use : sp_helpfile
  • Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause
    use : sp_tables
  • Returns column information for the specified tables or views that can be queried in the current environment
    use : sp_columns
  • Reports information about locks
    use : sp_lock
  • Displays the number of rows, disk space reserved, and disk space used by a table
    use : sp_spaceused
  • Displays or changes global configuration settings for the current server
    use : sp_configure
  • Provides information about current users and processes
    use : sp_who or sp_who2 (undocumented on BOL Rolling Eyes)
  • Displays the current distribution statistics for the specified target on the specified table
    use : DBCC SHOW_STATISTICS
  • Displays fragmentation information for the data and indexes of the specified table
    use : DBCC SHOWCONTIG
  • Provides statistics about how the transaction-log space was used in all databases
    use : DBCC SQLPERF
  • Displays the last statement sent from a client
    use : DBCC INPUTBUFFER

Commands For Database And Server Maintenance

  • Rebuilds one or more indexes for a table in the specified database
    use : DBCC DBREINDEX
  • Defragments indexes of the specified table or view
    use : DBCC INDEXDEFRAG
  • Runs UPDATE STATISTICS against all user-defined and internal tables in the current database
    use : sp_updatestats
  • Creates single-column statistics for all eligible columns for all user tables and internal tables in the current database. The new statistic has the same name as the column where it is created.
    use : sp_createstats
  • Shrinks the size of the data files in the specified database
    use : DBCC SHRINKDATABASE
  • Shrinks the size of the specified data file or log file for the related database
    use : DBCC SHRINKFILE
  • Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created
    use : sp_cycle_errorlog or DBCC ERRORLOG
Idea We can look into the code of the system SP by using sp_helptext ‘SP name’ on master database. Sometimes we need add some information that not provide on those SP i.e : we can combine sp_who2 and DBCC INPUTBUFFER become more useful command (I will discuss about this later). So just look into on the script, modified it and save as become another SP. Don’t replace current SP.

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'

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.

Older Posts »

Create a free website or blog at WordPress.com.