The Return of the Living Dead:Job Owners who Keep Coming Back

Have you ever had the feeling that SQL Server was playing a trick on you? I had those moments. I was updating a database maintenance plan and ran the corresponding job associated with it. I got an error saying that the job owner did not have permissions to execute the task. So I did what I would do as part of my testing procedure- change the job owner to sa and re-ran. And so it completed successfully until I realized I need to change some parameters in the database maintenance plan. After updating the maintenance plan, I again re-ran the same job and unexpectedly, it failed throwing the same error. When I checked the job owner, it reverted back to the original login prior to me changing it to sa. This login happens to be the user account that created the database maintenance plan. The worse part is, the maintenance plan has like 20+ subplans, each creating a SQL Server job. Now, you wouldn't want to change the job owner for each job associated with the maintenance job every time you change something, would you? Well, there's just no way you can do it directly. But there's always a workaround. This blog post provided a workaround for this case. You just modify the sysdtspackages90 table in the msdb database

UPDATE
msdb.dbo.sysdtspackages90
SET
OWNERSID = SUSER_SID('DOMAIN\new_user')
WHERE
OWNERSID = SUSER_SID('DOMAIN\old_user')

For SQL Server 2008, this query should do the trick

UPDATE msdb.dbo.sysssispackages
SET OWNERSID = SUSER_SID('DOMAIN\new_user')
WHERE name = 'MaintenancePlan' AND OWNERSID = SUSER_SID('DOMAIN\old_user')

There's currently a Microsoft Connect item for this case so feel free to vote on it if you think it's worth having a solution that just a workaround

How to immediately shrink the SQL Server log files

Have you ever wanted to shrink your log files but couldn't do it no matter how hard you try? While I and the majority of SQL Server DBAs would not recommend shrinking the transaction log (or any data file in your database), you'll end up doing it one way or another due to emergency situations. Here's what you'll probably end up doing as defined in this Microsoft KB article:

• Backup the transaction log. This will truncate the log
• Shrink the log file

Pretty simple, right? Well, there are times when this might not work because SQL Server does not shrink the log immediately. The DBCC SHRINKFILE operation occurs only at checkpoints or transaction log backups. SQL Server divides each physical log file internally into a number of virtual log files (VLFs), which make up the transaction log. This MSDN article describes virtual log files in SQL Server. SQL Server MVP Tibor Karaszi highlights why you would not want to shrink your log files. This blog by Johnny Hughes has a script that lets you do this task.

USE databaseName
GO

DBCC shrinkfile(<file_id>,NOTRUNCATE)
DBCC shrinkfile(<file_id>TRUNCATEONLY)

CREATE TABLE t1 (CHAR1 CHAR(4000))
GO

DECLARE @i INT
SELECT @i = 0
WHILE (1 = 1)

BEGIN
WHILE (@i < 100)
BEGIN
INSERT INTO t1 VALUES ('a')
SELECT @i = @i +1
END

TRUNCATE TABLE t1

BACKUP LOG databaseName WITH TRUNCATE_ONLY
END

Are you a SQL Server DBA wanting to Learn Windows PowerShell?

I've been working on PowerShell for quite some time but mostly for systems administration tasks. Since Microsoft has decided to make PowerShell as a common engineering criteria for all server applications being released, every SQL Server DBA needs to know at least what it is and what they can do with it.

This article is a first in this series of introducing Windows PowerShell to SQL Server DBAs. Go check it out