Disable / Enable SQL Jobs by T-SQL

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

Review Fill Factor of The Indexes

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 - http://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 - http://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.

Missing an Index? Check for it ..

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 - http://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.

Query For Listing SQL Server Job Schedule

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

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

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 - http://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 - http://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

Moving Database File To Another Disk

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

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'

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.

spWho3 active

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

Microsoft 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

Database Space Allocated

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

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

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

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

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

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

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

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

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