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;

Find Actively Running SQL Server Agent Jobs

Hi there,

An ETL developer asked my help recently with a SQL Server Agent job needed to start running if the main job finished running.  Occasionally main job run longer than expected and interfered with the secondary job which should run after the main job.  In the first step of secondary job we added a step to continue if the following query returned no rows if it returns a row then we set the job to retry again in 10  minutes for 3 times which solved our issue.

HTH,
Bulent


USE msdb;
GO
SELECT
    j.name AS job_name,
    ja.start_execution_date,     
    ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
    Js.step_name
FROM dbo.sysjobactivity AS ja
    LEFT OUTER JOIN dbo.sysjobhistory AS jh ON ja.job_history_id = jh.instance_id
    INNER JOIN dbo.sysjobs AS j ON ja.job_id = j.job_id
    INNER JOIN dbo.sysjobsteps AS js ON ja.job_id = js.job_id AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND ja.start_execution_date is not null
AND ja.stop_execution_date is null
--AND j.name = 'NameOfTheJob' -- Uncomment and supply the name of the job you need to find out if it's actively running
GO

Error 'The network address is invalid' Intalling Clustered SQL Server on VirtualBox VMs

Hi there,

While I was trying to set up a lab for testing out SQL Server 2016 on clustered two node Windows Server Failover cluster running Windows Server 2012 R2, I kept getting the error in the screen shot below.  I had no problem setting up the AD Domain controller, and two windows server nodes which will take part in the cluster.  DNS worked just fine, long story short there was no issue at all.  I tried two options where one was granting additional permissions  to windows cluster network object (CNO) which did not work, and other one was to pre-stage  the SQL Cluster  prior to installation which did not work either.

Further search came up with the solution from Jonathan Kehayias's blog dated September 19, 2011. The simple solution was  to remove 'Oracle VM VirtualBox Guest Additions'.  I un-installed that software rebooted the servers and was able to install the SQL Server Cluster successfully.  If you're not already following not only Jonathan's blog but everyone at SQLskills, I recommend to do so since you will benefit from their contribution immensely.

HTH,
Bulent

Hyper-V Manager Error 'General access denied error'

Hello there,

Recently I started using Hyper-V to set up a lab in my workstation.  I created many virtual machines in a domain environment to test traditional Window Server Fail over Cluster with SQL Server 2016 and also other nodes for testing availability groups.  Long story short I started running low on free space on my 256GB SSD so I ended up buying crucial MX300 750GB SSD for replacement.  After copying the contents from the existing 256GB SSD into 750GB SSD I started checking everything out to make sure that copy process worked as expected.  When I attempted to start the virtual machines in the Hyper-V Manager I kept getting errors for all the virtual machines and it basically said that the access denied.  The screenshot of the error message captured below.

I thought somehow the file system permissions did not transfer properly and tried granting my account full access to the D: drive where I store the virtual machine files.  To my suprise it did not change anything and the same error message appeared again.  Then I turned to my favorite search engine and looked for a solution.   Which was very easy to implement and I wanted to share it and keep a record of it in my blog in case I run into it again in the future.

The solution was to simple opening a Command Prompt (Admin) and gather couple of information for the command to execute and grant the necessary privileges to the virtual machine file. The command is;

icacls "path to vhd or vhdx file" /grant "NT VIRTUAL MACHINE\Virtual Machine ID":F

I knew the virtual machine file name and full path to it but I needed to find the virtual machine SID.  To get the SID you need just click on 'See details' link on the error message window and it will expand the error window like below.

The last paragraph has the full path of my virtual machine file and the SID I needed for the command.  So I just opened the Command Prompt (Admin) and I typed the command as seen below and then I was able to start the virtual machine and connect to it.

Happy virtualizations.
HTH,
Bulent

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

How To Extend VirtualBox Dynamic Disk For Windows Server 2012 R2

I wanted to set up a lab environment using VirtualBox to test SQL Server 2016.  I  first started with the installation of Windows Server 2012 R2 and 15GB space configured as dynamic disk.  Installation went smooth and I then started the windows update process. After a while I started getting low disk free space and updates failed.  Here are the steps I took to extend the disk and screenshots below them.

1. Shutdown the virtual machine that needs additional space
2. Open up a command prompt with administrator privileges
3. Change your folder path to where VirtualBox is installed
4. Type the command above the first screenshot and provide the full path of the virtual disk you want to extend and the size you want to extend  the disk to, command provided below extends  the dynamic disk to 20GB
5. Start the virtual machine and log in  to windows
6. Open the 'Computer Management' and under the 'Storage' section on the left side of the pane and click on 'Disk Management' and you will see the unallocated space for the disk you just extended using the command in step  4
7. Right click on the disk you want want to extend to bring up the context menu where you will see 'Extend Volume' option
8. Click on 'Extend Volume'
9. This will bring the 'Extend Volume Wizard' and follow the steps in the wizard

VirtualBox command to extend the dynamic disk:
vboxmanage modifyhd "pathToYourVHDfile" --resize 20480

And here are the screenshots.

 

 

HTH,
Bulent

Find Size of the Indexes for Indexed Views

Hello all,

I have several scripts in my tool box to look into the indexes for tables, but did not have one for indexed views.  I wanted to share a script that return the size of clustered and non clustered indexes for indexed views if exist in the context of the database where the script is executed.

HTH,
Bulent

SELECT
      OBJECT_SCHEMA_NAME(v.object_id) AS 'SchemaName'
    , v.NAME AS 'ViewName'
    , i.index_id
    , i.name AS 'IndexName'
    , p.rows AS 'RowCounts'
    , SUM(a.total_pages) * 8 AS 'TotalSpaceKB'
    , SUM(a.used_pages) * 8 AS 'UsedSpaceKB'
    , SUM(a.data_pages) * 8 AS 'DataSpaceKB'
FROM    sys.views AS v
    INNER JOIN sys.indexes AS i ON v.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
GROUP BY v.object_id, v.NAME, i.object_id, i.index_id, i.name, p.Rows;