Update SQL Server Agent Job Owner

Hi there,

I have been working on a project to move/migrate lots of jobs to a new server in another domain and we had to update quite a bit of jobs to set the owner to 'sa' prior to migration.  Out of close to 600 jobs, there were about 200 jobs owned by not 'sa' but an account in Active Directory.  So instead of touch all of those jobs manually and change the owner I come up with the following script and all done in less than 10 minutes including time took come up with the script.  I decided to share it here and also use my blog to document it if I ever need it.

HTH,
Bulent

USE MSDB;
GO
SET NOCOUNT ON;
/********************************

This script is used to update the job owner to sa for
the jobs that is not already owned by sa.

********************************/
DECLARE
      @MinRowId INT = 1
    , @MaxRowId INT
    , @jobId UNIQUEIDENTIFIER
    , @owner_login_name NVARCHAR(128) = N'sa';

-- Store the job steps in a temp table to update the in the loop later
IF OBJECT_ID('tempdb..#Jobs') IS NOT NULL
    DROP TABLE #Jobs;
CREATE TABLE #Jobs (
      RowId INT IDENTITY (1,1) NOT NULL
    , job_id UNIQUEIDENTIFIER NOT NULL
    , jobname SYSNAME NOT NULL
    , owner_sid VARBINARY(85)
    );
-- Insert list of jobs not owned by 'sa'
-- Can change the select statement and
-- where clause to your need
INSERT INTO #Jobs (job_id, jobname, owner_sid )
SELECT    j.job_id, j.name, j.owner_sid
FROM    dbo.sysjobs AS j
WHERE    J.owner_sid <> 0x01 -- sid for SA
ORDER BY j.name

SET    @MaxRowId = @@ROWCOUNT;

WHILE @MinRowId <= @MaxRowId
    BEGIN
        SELECT    @jobId = job_id
        FROM    #Jobs
        WHERE    RowId = @MinRowId;
       
        EXEC sp_update_job @job_id = @jobId, @owner_login_name = @owner_login_name;

        SET @MinRowId = @MinRowId + 1;
    END
--Return list of jobs and original owner sid
SELECT * FROM #Jobs;
DROP TABLE #Jobs;

Get Size Information For All Tables in All Databases

Hello there,

I have been working on a small project to log and track the size and growth of the tables in all databases.  So that overtime I can show the growth size and estimate  the disk space allocation requirements for databases in production environment.  Here is the script I used to capture the information.  I used SQL Server Agent job scheduled to run once a day after midnight and store the results in a repository to be monitored and reported on later.  You can uncomment the where  clause to exclude system tables and tables with no records in them if you want.


SET NOCOUNT ON;
-- Get Table Statistics (Row Count, total space used)
IF OBJECT_ID('tempdb..#TableStatistics') IS NOT NULL
    BEGIN
        DROP TABLE #TableStatistics;
    END
CREATE TABLE #TableStatistics (DatabaseName SYSNAME, SchemaName SYSNAME, TableName VARCHAR(128), TableRowCount BIGINT, TotalSpaceKB VARCHAR(20), UsedSpaceKB VARCHAR(20), UnusedSpaceKB VARCHAR(20));

EXEC sp_msforeachdb 'USE [?];
    INSERT INTO #TableStatistics
    SELECT
          ''?'' as DatabaseName
        , s.Name AS SchemaName
        , t.NAME AS TableName
        , p.rows AS TableRowCount
        , SUM(a.total_pages) * 8 AS TotalSpaceKB
        , SUM(a.used_pages) * 8 AS UsedSpaceKB
        , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
    FROM    sys.tables AS t
        INNER JOIN sys.indexes AS i ON t.OBJECT_ID = i.object_id
        INNER JOIN sys.partitions AS p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
        INNER JOIN sys.allocation_units AS a ON p.partition_id = a.container_id
        LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
    --WHERE    p.rows > 0 AND t.is_ms_shipped = 0    AND i.OBJECT_ID > 255
    GROUP BY    t.Name, s.Name, p.Rows
    ORDER BY s.name,  t.name' ;

SELECT    *
FROM    #TableStatistics;

DROP TABLE #TableStatistics;


HTH,
Bulent

More on SQL Server 2008 Variable Enhancements

Let's continue where we left last week. I want to point out another enhancement around assigning values to parameters in SQL 2008 and above. It's called compound assignment operator, starting with SQL 2008 we can declare a parameter and assign value and also we can do mathematical operations.

-- SQL Server 2008 and 2008 R2

DECLARE @DueAmount MONEY = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount *= 0.9

END

SELECT @DueAmount

GO

-- SQL Server 2005 and before

DECLARE @DueAmount MONEY

SELECT @DueAmount = 101

-- 10% off purchases over 100$ promotion

IF @DueAmount > 100

BEGIN

SET @DueAmount = @DueAmount * 0.9

END

SELECT @DueAmount

If you pay attention how the value is being set inside the if logic you will understand how compound assignments works. Here is the link to msdn page for more detailed info around setting the values to variables. It does help with less coding in your T-SQL but if there is sql server 2005 or earlier versions exist in your environment be careful to remember which environments that statement will be used.

HTH,

Bulent

SQL Server Collate Clause And Case Sensitivity

The servers in the current environment I am working has been configured with case insensitive collation by default during the installation. I had to help a developer who was trying to write a clr function to compare values stored in a database. We had to find out all the uppercase first and last names stored in one of the tables. The columns in the table have been created case insensitive. I thought of using built in feature instead of doing it in CLR and finding out the results. As you see in the simple script below I am creating 4 columns 2 case insensitive and 2 case sensitive. Populate it with sample data and finally compare the columns against each other. Note that once you create a columns like this you can not compare case insensitive column against so pay attention the where clause in the select statements. With this simple solution we were able to easily produce all the records stored in uppercase in the table. For more information look in the Books Online and search for collate

HTH,


USE tempdb
GO

CREATE TABLE dbo.CaseSensitiveTest(
RowId TINYINT IDENTITY (1,1),
CIFName VARCHAR (30),
CILName VARCHAR (30),
CSFName VARCHAR (30) COLLATE Latin1_General_CS_AS,
CSLName VARCHAR (30) COLLATE Latin1_General_CS_AS

)

GO

INSERT INTO dbo.CaseSensitiveTest
SELECT 'MICKEY', 'MOUSE', 'Mickey','Mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'mickey','mouse'
UNION ALL
SELECT 'mickey', 'mouse', 'Mickey','Mouse'
UNION ALL
SELECT 'MICKEY', 'MOUSE', 'MICKEY','MOUSE'
UNION ALL
SELECT 'bunny', 'rabbit', 'Bunny','Rabbit'
UNION ALL
SELECT 'BUNNY', 'RABBIT', 'BUNNY', 'RABBIT'

GO

SELECT * FROM dbo.CaseSensitiveTest

SELECT *
FROM dbo.CaseSensitiveTest
WHERE CIFName COLLATE Latin1_General_CS_AS = CSFName
AND CIFName COLLATE Latin1_General_CS_AS = UPPER(CIFname)


DROP TABLE dbo.CaseSensitiveTest

SQL Server 2008 R2 Released To Manufacturing

Here is the announcement from Microsoft Data Platform blog that SQL Server 2008 R2 has been released to manufacturing.