Scripting SQL Agent Jobs 2005 and 2000

So I started working on a web site that would list out all the SQL Server Agent jobs for easy access. After several revisions I came up with a set of DTS packages that would hit each SQL server to store the Job list (sysjobs), Job Step Information (sysjobsteps), and Job History Information (sysjobhistory) into a single central server. From there I just created an ASP web page that allows me to see all jobs from a single source. I keep the last 10 days of history in my central repository.

So far it has been pretty handy. One cool thing about the web site is that it contains the actual command of the job step. So if I am in a pinch and I need to recreate a job for some reason, I can quickly pull the job up and check it out. Good for database restores where I do not bring the msdb database over in the restore.

I was also thinking that it would be handy to have the actual .sql script stored off in a location so I could actually just run a script to recreate the jobs. Handy for those servers that have two dozen complex jobs. Not such a big deal for servers that have a single backup job, but to recreate two dozen jobs on a server would take some time.

SQL Server 2000: Just browse down the the SQL Agent in Enterprise Manager and right click on Jobs…Choose the All Tasks option and Generate SQL Script. This will let you generate a single SQL Script for all the jobs. I just store this SQL off on a separate server. I run this periodically, especially when jobs are added/deleted, etc.

SQL Server 2005: Thanks to the helpful tip here: http://www.sqlservercentral.com/Forums/Topic452319-146-1.aspx from Adam. You can just click on the jobs folder under SQL Server Agent, then on the right hand side in the Object Explorer Details you can highlight all the jobs and then Right Click, Script Job as…Create To…File, or New Query Window.

So now I have the web site to see a quick view of all my jobs and I also have the .sql Script to actually build the jobs if necessary. All of this to ease the recovery steps.

Epic quest: Cannot generate SSPI context

I think I made this way too difficult. The problem first started when one of our Windows 2003 servers crapped out. Total hardware failure. The fix was to replace the server and re-configure. After the new server was in place and working fine, the SQL backup job started failing. The SQL Server was a separate server using a share on the file server via UNC. For the article I will call the File Server FILESERVER1 and the SQL Server SQLSERVER1.

I noticed that the SQL Server Service was using LocalSystem to start the service. The odd thing is that the SQL Server backup job was working fine before the FILESERVER1 failed using the LocalSystem account. I thought this was odd because I have read several articles stating that if you want to backup to a UNC the SQL Server Service must be a domain user and have the appopriate permissions to the share. So I thought, no sweat. I will just change the SQL Server Service to use a domain user with privledges and away we go. I changed the service and got approved for a quick Service Restart. The Service started fine and I could browse the server fine locally using Enterprise Manager.

I then try to hit the server from my laptop using Windows Authentication from Enterprise Manager and received the following error: Cannot generate SSPI context error. I quickly just changed the service back to LocalSystem so I could do some testing on a test box, trying to reproduce the error.

Found this article from Microsoft: http://support.microsoft.com/kb/811889. This is a very good description of the problem and some possible solutions. The article links off to this article which contained the solution to the problem: http://msdn.microsoft.com/en-us/library/aa905162%28SQL.80%29.aspx

Using the setspn resource tool was an interesting adventure. First off I am not a Domain Admin so I had to have some assistance from our Server team. Had a Domain Admin run the following command

setspn -A MSSQLSvc/SQLSERVER1.domain.com sqlaccount

You will need to use the Fully Qualified Name here. You will need to run this for every server you would like to start using a Domain User account. The sqlaccount is the actual Domain User account you want to use. Refer to the Security Account Delegation Article for more details such as what to do if the domains do not match, etc. One gotcha is that it took a little while for the setspn command to make the change to the domain user. I assume it was some kind of Synchronization delay.

While browsing around for the error I sumbled on the ADSI Edit MSC. This is helpful to look at to review the setspn change. You can find the user in the domain and view the property. If you are a Domain Admin you can actually make the change from the ADSIEdit console. see below for screenshots from ADSIEdit:

So after the user was given the attribute for SQLSERVER1, I then hoped back on the server and restarted the SQL Service. Service Started fine, I was able to browse to it from Enterprise Manager on my laptop, and the UNC backup to FILESERVER1 started working again.

Here is another good Microsoft KB that shows how to setup Windows Services Accounts for SQL Server: http://msdn.microsoft.com/en-us/library/aa176564%28SQL.80%29.aspx

SQL Job Monitor

Stumbled on this cool little tool yesterday: SQLJobVis. SQLJobVis is a desktop client application that allows you to view the SQL Agent jobs on your servers. You just add the server you want to connect to and it will connect and show you a timeline of the jobs. Pretty easy.

The one thing I wish the app  would do is allow you to view multiple servers. You have to view the servers one by one. It would be pretty gangster if the allowed multiple servers and then a way to export the timeline. Then you could have a timeline of all your SQL Agent jobs. Anyway, it is still a pretty cool tool and worth checking out.

http://www.sqlsoft.co.uk/

Lazywriter detailed

Based on this article from sql-serverperformance.com, I created this graphical representation the Lazywriter and how it interacts with the Memory Pool.

From SQLServerPedia.com:

The Lazy writer serves two purposes:

  1. Ensure that a specified number of buffers are free in the Buffer Pool so they can be allocated for use by the server.
  2. Monitor the usage of committed memory by the Buffer Pool and adjust it as necessary so that enough physical memory remains free to prevent Windows from paging.

The Lazy writer can adjust the number of buffers in the buffer pool if dynamic memory management is enabled. SQL Server estimates the number of necessary Buffer Pool buffers based on system activity and based on the number of stalls. A stall occurs when a request for memory has to wait on a free buffer page.

To monitor the Lazy Writer use this perf counter: SQLServer:Buffer Manager\Lazy writes/sec. This counter represents the number of buffers written by buffer manager’s lazy writer. This counter tracks how many times a second that the Lazy Writer process is moving dirty pages from the buffer to disk in order to free up buffer space. Usually this should not be a high value, say more than 20 per second or so. Of course you will need to compare it with your established baseline. Ideally, it should be close to zero. If it is zero, then the buffer cache is big enough and SQL Server doesn’t have to free up dirty pages. If this value is high, then you may be experiencing a memory bottleneck.

What are you WAITing on?

So I got tired of hitting google and opening up that Word Doc from Microsoft (Performance Tuning and Waits). So I grabbed the table of waits from this word document and converted the table to a Tab Delimited Text file. From there I was able to import the data into a table in my DBA catch-all admin database. Then I created a little web front end to search for Waits. Now when I have a wait I can just hit my web page and do a quick search. I also created a little notes table so I can make notes and increase my skill and magic on troubleshooting waits.

Here is the waits.txt file that contains the tab-delimited data to import: waits.txt (had to give it a .doc extension so I could upload it to wordpress. Just change the extension back to .txt).

Here is my waits table:

CREATE TABLE [dbo].[sqlwaits](
[waitid] [int] IDENTITY(1,1) NOT NULL,
[waittype] [varchar](50) NULL,
[category] [varchar](50) NULL,
[intable] [varchar](10) NULL,
[description] [varchar](4000) NULL,
[correlation] [varchar](2000) NULL,
CONSTRAINT [PK_sqlwaits] PRIMARY KEY CLUSTERED
(
[waitid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

And the notes table:

CREATE TABLE [dbo].[sqlwaits_notes](
[noteid] [int] IDENTITY(1,1) NOT NULL,
[waitid] [int] NULL,
[notebody] [varchar](2000) NULL,
CONSTRAINT [PK_sqlwaits_notes] PRIMARY KEY CLUSTERED
(
[noteid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]