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

The Principal "dbo" Does Not Exist

The  full error message logged in the application log file is:

System.Data.SqlClient.SqlException: Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I run into the error message after restoring a backup that belongs to a client while trying to reproduce the issues they were having.  When a database is restored the ownership of the database is assigned to the account running the restore command but in my case it was not that account.  It was actually blank as seen in the database properties screenshot below.

So to fix the issue I run the command 'Alter Authorization On Database' to make a domain account owner of the database (see the screenshot below).  In  my case the domain account is  the SQL Server  Service account.

After running the command (see the screenshot  below) the database property page shows the domain account as the owner of the database and the error message did not get logged into the application log any longer.

HTH,
Bulent

How to Change SQL Server Authentication Mode

Hello all,

I was asked to fix a connectivity problem for SQL Server 2008 deployed to test virtual machine. I was told that nobody was able to connect neither using sql server login nor windows login (neither SA account with generic password nor the local administrator of the windows server)

Since I have seen this in the past and heard about it, I knew what caused this problem.  Basically it was the result of not granting access to any log in with right credentials.  First thing I tried is to make sure that I am using a log in that has sys admin rights.  For this I followed the steps in my earlier blog post (click this link to read that blog).  Make sure to use strong password for the sql log in you just created.

Upon restarting SQL Server I attempted to use the new account I just created with no success.  I kept getting login failed message.  Then I wanted to check the SQL Server error log to see if any error message logged. In my situation I was able to locate the error logs stored at "C:\Program Files\Microsoft SQL Server\MSSQL10.Test01\MSSQL\Log"   however in your case it might be different based on installation path and version of the SQL Server.  Just by looking at the path you can tell that I am dealing with named instance installation of SQL Server and the name of the instance is 'Test01' and MSSQL10 means it's SQL Server 2008.  If you're dealing with another version like SQL 2008 R2 you would see 'MSSQL10_50' and if you're dealing with SQL Server 2012 it would be 'MSSQL11'.  And if you're dealing with default instance then instead of 'Test01' you would see 'MSSQLSERVER' in the path.

Anyway, I opened the file named 'errorlog' using notepad and started to scanning the entries.  Within seconds I found an error message stating that 'login failed because of server is configured with Windows Authentication mode but the login is sql login'.  At this moment I knew that I had to change the authentication mode to 'SQL Server and Windows Authentication mode' to gain access using the sql login I just created.

If you search the Internet you will find how to change the authentication mode during installation or changing the authentication mode using SSMS.  However I had no way of connecting to SQL server via SSMS to change the authentication mode.  But there is another way that it can be done.  Of course this is not documented anywhere in MS knowledge base since it involves registry hacking!!!.

In my case this was a test server and I was OK if I ended up messing up the server and need to rebuilt it. If you're in the same situation and willing to do the same then keep on reading.

Open the command prompt and type "regedit" (without quotation marks) and hit enter. Make sure to backup the registry before making any changes.  Then browse to the location for your SQL Server installation on the left pane.  In my case it was the named instance of SQL Server which was named 'Test01'.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.Test01\MSSQLServer

And here is the path to default instance:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLServer\MSSQLServer

Then on the right pane find "LoginMode"  make a right click and click  on 'Modify'  then finally change the 'Value data' to 2, click ok and exit the registry editor.

Stop the SQL Server service and then restart it.  Now your instance authentication mode has been changed to 'SQL Server and Windows Authentication mode' and you can use the sql login that you created to access and configure the server.

HTH,
Bulent

Compressing SQL Server Backup Files

Hello Database Professionals,

Starting with SQL Server 2008 Enterprise Edition we have the option of compressing backup files (this includes both BACKUP DATABASE and BACKUP LOG statements which mean not only database backups but log file backups will be compressed) to save disk space and also decrease the duration of the backup and restore operations. The backup compression was an enterprise edition only feature with SQL Server 2008 but with SQL Server 2008 R2 this feature is supported by standard and higher editions. So that was welcome news for me since I support number of SQL Server 2008 R2 Standard Edition.

I have been using T-SQL script to backup databases. The script checks the edition of the SQL Server and then builds the backup command to compress the backup file if it’s enterprise edition or just backups the database if it was a standard edition.

Before the end of 2011 I have completed deploying/upgrading to SQL Server 2008 R2. That meant that my script no longer needed to check the edition of SQL Server 2008 R2 since the compression is already supported in standard and higher editions. So I turned to global configurations and enable the backup compressions. I would like to remind the readers that enabling the backup compression creates additional CPU overhead which depends on you workload might impact your server performance. I suggest that you test your backup process and if necessary using Resource Governor (this is Enterprise Edition only feature) create a low priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs.

Let’s start with checking the backup compression setting for the server by using the script below.

USE master

SELECT *

FROM sys.configurations

WHERE name = 'backup compression default'

ORDER BY name

Let’s check the returned result for the value_in_use column. This shows us the running value curently in effect for this option and is_dynamic column tells us the changes take effect after the RECONFIGURE statement executed. Now it’s time to execute the below script to enable the backup compression since the value_in_use is 0 for the server I am currently working.

USE master

EXEC SP_CONFIGURE 'backup compression default',1

RECONFIGURE

Let’s check the value_in_use by executing the first select statement.

USE master

SELECT *

FROM sys.configurations

WHERE name = 'backup compression default'

Now we should see that the value_in_use is 1 and we don’t need to use the optional keyword COMPRESSION in the backup database command.

By default when backup is compressed checksums are performed to detect media corruptions but if for any reason you need, you can explicitly disable the compression by using NO_COMPRESSION keyword in your backup statement.

I have seen anywhere from 35% to 80% compression ration and backup duration decrease from several hours to 30-45 minutes for the databases I administor. Your mileage will vary and you should TEST, TEST, TEST and deploy your changes for your environments. Here is the link to find out more about backup database command in MSDN.

HTH,

Bulent

Dropping Multiple SQL Server Objects in Single Line

As humans we try to find a way to work faster and efficient. As data professionals, typing less probably is another thing we want. My SQL Server tip today is about dropping multiple objects (tables, views, stored procedures, and even databases) in single drop statement. This is powerful but can be dangerous in production so please use caution. Here is a script that creates couple of tables and then drops both tables in single drop statement.

Sincerely,

Bulent

-- CREATE TABLES

USE tempdb

GO

CREATE TABLE dbo.TableT1 (t1c1 TINYINT)

CREATE TABLE dbo.TableT2 (t2c1 TINYINT)

GO

INSERT INTO dbo.TableT1 VALUES(1)

INSERT INTO dbo.TableT2 VALUES(2)

GO

-- CHECK THE TABLES CREATED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

-- DROP BOTH TABLES IN SINGLE DROP STATEMENT

DROP TABLE dbo.TableT1, dbo.TableT2

GO

-- CHECK THE TABLES DROPPED

SELECT *

FROM sys.tables

WHERE name LIKE 'TableT_'

GO

SQL Function QUOTENAME()

Hello all,

I support third party software databases at work. During the implementation of the application it needed to create a database to store the data. However the application created a database with a name that does not follow the rules for the format of identifiers. In my case we ended up with a database name '20120101_Survey'.

On the same server I have a custom built script that backups up all the databases at night. However, following the database creation my backup process failed and I got an alert about the process. Further looking into the problem I found out that there was a problem with the syntax. As soon as I see the statement I knew what the problem was but never thought about it while deploying the application and database. To keep the story short I used the SQL Server function called QUOTENAME() to fix the problem. Using the function in my custom script help me return the database names with delimiters so that backup process completes without error. Since then I have been using the function with all the object names. This simple function can prevent some headaches if a DBA needs to support 3rd party databases (SharePoint is also a good example because the SharePoint database names don't follow the rules of the identifier format). Here is the link to the msdn and below is simple statement that returns all the databases in your system with delimiters.

SELECT QUOTENAME(name) AS DelimitedDbName

FROM sys.databases

HTH,

Bulent

The distribution agent failed to create temporary files

Hello,

I ran into a replication error right after configuring the transactional replication. The error started after applying the snapshot and the exact the error message was 'The distribution agent failed to create temporary files in C:\Program Files\Microsoft SQL Server\100\COM directory. System returned errorcode 5.' In this specific configuration I was using the sql server agent account as the distribution agent and the AD account was just a simple domain user. In this case the culprit was that the account being used as distribution agent needed write access to folder and giving that permissions fixed the error. One thing to remember is if this is a clustered sql server you're working with you need to grant write access to the account for all the nodes in the cluster. Here is the link to MS KB article.

HTH,

Bulent

70-433 TS: Microsoft SQL Server 2008, Database Development

Another period of time just passed very quickly and no blogs I was able to post. During that time I passed exam 70-433 TS: Microsoft SQL Server 2008, Database Development and I got another certification under my belt.

So how did I prepare for the exam? I used this book and I think it's good book that covers the exam objectives. I have also used Transcender exam to prepare for the final week. I have to warn that you need to know the XML Query in SQL Server.

Good luck if you're preparing for this exam. Soon I will start preparing for 70-450: PRO: Designing, Optimizing and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 and if you have any tips for me please let me know.

HTH,

Bulent

Zemanta" style="border:none;float:right">

SQL Server 2008 R2 Express Edition and Full Text Search

Hello all,

Couple of months ago I installed SQL Server 2008 R2 Express Edition to be used as repository for test tool the QA department needed to use. Everything seems to be fine till end of August when QA team needed to enable Full Text Search and search for some of the databases during their test phase. I got an email stating that the full text did not work. So I dived in to investigate.

I found out that SQL Server 2008 R2 Express edition only does not support the full text search and I had to upgrade to SQL Server 2008 R2 Express Edition with Advanced Features. While searching I found this site that talks about how to upgrade the Edition.

In my case the upgrade did not work. I kept getting run time errors during the upgrade process. That forced me to uninstall but before uninstall I backed up all the user databases and scripted out the logins using sp_help_revlogin stored procedure (you can read all about transferring logins here). Then I installed SQL Server 2008 R2 Express Edition with Advanced Features. During the installation I checked the full text search in the feature selection and continue with the process. Then I executed the script to create the logins. After that I attached all the databases and voila.

I think from now on I will only install SQL Server 2008 R2 Express Edition with Advanced Features but install only the features I need (ie, database only) and if someone needs other features like Full Text Search or Reporting Services then I can easily add the features.

HTH,

Bulent

Dropping Local Temp Table

A collegue needed to create a local temp table in his work to insert records. When executed the script to create and insert records after first try he kept getting error message stating that 'There is already object named #TestTable in the database. I told him that he should clean up the Temp tables at the end when he is done with them and also to be safe in the beginning of the code to check and drop if the table exists. Here is an example.

-- CHECK AND DROP TEMP TABLE IF EXISTS

IF OBJECT_ID ('Tempdb.dbo.#TestTempTable') IS NOT NULL

BEGIN

DROP TABLE #TestTempTable

END

-- CREATE TEMP TABLE

CREATE TABLE #TestTempTable (

RowID tinyint not null,

City varchar(50) not null

)

-- POPULATE WITH DATA

INSERT INTO #TestTempTable

SELECT 1,'London'

UNION

SELECT 2, 'Chicago'

UNION

SELECT 3, 'Paris'

/***

MORE CODE HERE

***/

SELECT * FROM #TestTempTable

-- WHEN DONE CLEAN UP THE TEMP TABLE

DROP TABLE #TestTempTable

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 2008 Variable Declaration

Another day a developer approached me stating that the code works in local development server but it does not work when deployed to QA. I know that QA server is running SQL Server 2005. I heard that lately the development team was getting new workstations with new set up. So I was sure that they were running something other than SQL server 2005. I asked the developer if the sql server running on the workstation was SQL 2008. The answer was yes and I then asked how variables were declared and assigned the valued. It was just the first thing that came to my mind. And I was right.

So, starting with SQL Server 2008 Microsoft made improvements on declaring variables and assigning values to them. Here is an example for SQL Server 2008 and newer versions.

DECLARE @Startdate DATETIME = GETDATE()

DECLARE @AccountID INT = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

However if you run the above statement in SQL 2005 or earlier you will get an error message. The only way above statement will work on SQL 2005 and earlier versions is;

DECLARE @Startdate DATETIME

DECLARE @AccountID INT

SELECT @Startdate = GETDATE(),

@AccountID = 12345

SELECT @Startdate AS StartDate,

@AccountID AS AccountID

As you see it is less of t-sql code for SQL Server 2008 and newer versions. There is some more enhancements but I will cover that in my next blog.

HTH,

Bulent

70-432 TS: Microsoft SQL Server 2008, Implementation and Maintenance

Hello all,

I have been pretty quite lately. There are many reasons for that. One of them is I was preparing for the 70-432 exam. I see a lot of questions in the net about the preparation and books. So I wanted to blog something around that.

Good news is I passed the exam and scored 833 out of 1000. It's not too high of a score but if you score 700 or above, you're certified.

Now the preparation part. I have used MS Press book by Mike Hotek. I like how each chapter is divided into lessons that are not too long to complete. From my experience standpoint the book pretty much covers the material in the exam. The study gave me a chance to look at the features of SQL Server that I don't use day in and day out. I have been working with SQL Server since 2005 including version 2000/2005/2008/2008 R2. I did pass the 2000 Admin and developer certifications back in 2007. At the time where I work we did not have anything but SQL Server 2000. Since then a lot changed. However I still wanted to grab a book and study. However I do think that this book is not just enough. The questions at the end of each chapter are quite easy so it may give you a false sense of confidence (at least for me it might have been). So I turned to practice exam by Transcender. When I started the practice exam it was a lot tougher than the questions in the book. Transcender exam practice really helped me tune more towards the exam format. I definitely recommend the exam practice since it also helps with the explanation of the answers for each question. I also kept the BOL (Books On Line) open.

So one exam is down and I am trying to decide which MS SQL Server certification exam will be the next. Let me know about your certification thoughts. By the way I don't work for Transcender.

Good day,

Bulent

Hello all,

Microsoft released the Service Pack 4 for SQL Server 2005 to RTM on 12/17/2010 kind of quietly before the holidays. The version number is 9.00.5000.00 once the sp4 is installed. Here is the link that will take you to download site and this link is for what's fixed with sp4.

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 Failed to Alert Operator

I just deployed a new SQL Server Server and set up some jobs. Couple of days later wanted to check the server and see how jobs were executing. I was surprised to find out one job has been failing and I remember not seeing any alert. I did remember that I set up database mail and operators. Job history tells me that sql server failed to notify the operator. I started to look around and finally thought of checking the sql server agent properties. To my surprise I found out that I forgot to set up the properties under alert and putting check for enable email profile, selecting the profile and another check for enabling fail safe operator did all the trick. After changing the setting started to work and sql server agent started to notify me with emails.

HTH

SQL Server 2008 Invalid SKU error

I was trying to set up SQL Server 2008 Cluster on Windows 2008 OS. Creating the cluster with a single node went pretty smooth. However when I wanted to add the second node to cluster I kept getting 'Invalid SKU error' and installation kept aborting the operation. I downloaded the new ISO of SQL Server 2008 from MSDN website with no help. Searching the net came out with the link below which stated that it was a know bug and ways to workaround it. I started the setup process from command line using the command below. At the end the node has been added with success.

Here is the command that I started the installation:

setup.exe /q /ACTION=AddNode /INSTANCENAME=""

Microsoft Connect bug report site:

HTH