DBA and SysAdmin World

April 27, 2009

Disable / Enable SQL Jobs by T-SQL

Filed under: Script — Tags: , — solihinho @ 12:23 AM
coding2 Objective
Sometimes on certain situation e.g.:month end closing, we need to disable all our SQL Server jobs then enable it back after that process was finished. If we have tens of jobs even more, I believe it is quite take time if we do it through SQL Server Management Studio / Enterprise Manager.  SQL Server already provide a stored procedure “sp_update_job” on msdb database to change the status of the job (see SQL Server BOL for further information). We just provide the job_id / job_name and the new status (1 = enable, 0 = disable).

Usage
Just run below script to get the TSQL script. You can review the result first before run the result to disable/ enable SQL jobs. FYI, when trying to disable the jobs the script only execute the jobs that status is enable. To enable it back, just keep the result and replace  @enable = 0 become @enable = 1. I just prevent not to enable job which status is disable before the script was executed.

Script

SET NOCOUNT ON

DECLARE @job_id  UNIQUEIDENTIFIER
DECLARE @disable_job CHAR(1)

SET @disable_job = 'Y' -- Fill 'Y' to disable job .. 'N' to enable job

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


SELECT job_id INTO #sysjobs
FROM msdb..sysjobs
WHERE enabled = CASE @disable_job WHEN 'Y' THEN 1 ELSE 0 END

WHILE (1 = 1)
BEGIN
   SELECT @job_id = job_id FROM #sysjobs

   IF @@rowcount = 0
      GOTO _EXIT

   
   PRINT 'exec msdb..sp_update_job @job_id = ''' + CAST(@job_id AS VARCHAR(36))+ ''', @enabled = ' 
         + CASE @disable_job WHEN 'Y' THEN '0' ELSE '1' END
   

   PRINT 'GO'   

   DELETE FROM #sysjobs WHERE job_id = @job_id
END

_EXIT:
   DROP TABLE #sysjobs

SET NOCOUNT OFF

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

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

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.

Blog at WordPress.com.