Search results matching tag 'SQL Server Agent'

Express Edition revisited, focus on SSMS

TiborKaraszi — Wed, 30 Jan 2013 17:01:00 GMT

(Note: I have re-written parts of this post in the light of the comments that SP1 of 2012 include Complete tools.)

I have decided to revisit the topic of whats included in Express Edition, with focus on the tools. I have a couple of reasons for this:

In my 2011 post, I never tried to connect from Express SSMS to a non-Express database engine.
I want to check if there are any significant differences in SQL Server 2012 Express Edition, compared to SQL Server 2008R2 Express Edition.

It isn't uncommon that people want to have SQL Server Management Studio (SSMS) on their machines; and instead of searching for the install files for the full product, they download the freely available Express Edition and install SSMS from there. This was the main reason for this update post, and the reason I focus on SSMS and the tools in this post.

It turns out that both 2008R2 and 2012 RTM Express editions of SSMS includes a lot, but not quite everyting that the full version of SSMS has. And they don't have Profiler or Database Engine Tuning Advisor. 2012 SP1 Express download does indeed have the Complete tool package.

Basic and Complete
The full SSMS (etc.) is referred to as "Management Tools - Complete". This is only available with the Product you pay for and with 2012 SP1 Express. The only one available with the various free Express downloads (prior to 2012 SP1), is called "Management Tools - Basic". You can explicitly request to install Basic from an install media that includes Complete, but you have to explicitly request that in the setup program. You don't want to do that.

One difference between 2008R2 and 2012 is when you install from a pay-media and select that you want to install Express. For 2008R2, you then only have SSMS Basic available. For 2012, you have Complete. In other words, if you use a 2012 pay-media and select Express to install SSMS, you have the option to have the full-blown SSMS - Complete (including other tools, like Profiler).

The downloads
For SQL Server 2008R2, you have "Express Edition" and "Express Edition with Advanced Services". The former is basically only the database engine, where the later has some Tools (SSMS Basic, primarily). See my earlier blog post for more details about 2008R2.

For 2012, there are bunch of downloads available. Note that if you want Complete tools, you need to download SP1 of the installers. You find SP1 here (and RTM, which you don't want to use, here). SP1 includes Complete tools, and you will see that those downloads are significantly larger compared to RTM. It isn't obvious what each exe files stand for, but scroll down and you will find pretty good explanations. I tried several of these (SSMS only, Express with Tools, Express with Advanced Services). They all have in common that for RTM the tool included is Basic, where for SP1 we have Complete.

So what is the difference between Basic and Complete?

In the table below, my focus was on what isn't in Basic. In general, I don't bother to list functionality which is available in both Basic and Complete. So, if the functionality isn't in the table below, it is likely available in Basic. I might have missed something, of course! And my main focus was on SSMS and the database engine.

Component/Functionality	2008R2	2012 RTM	2012 SP1
Functionality in SSMS
Node for Agent	Y	Y	Y
Graphical Execution Plans	Y	Y	Y
Projects and Solutions	N	Y	Y
Maint Plans, Wizard	Y	Y	Y
Maint Plans, New, designer	N (1)	N (2)	Y
Maint Plans, Modify	N (1)	N (2)	Y
Node for SSIS Catalog	N/A	Y	Y
Tools menu, Profiler	N	N	Y
Tools menu, Tuning Advisor	N	N	Y
Connect Object Explorer to:
Analysis Services	N	N	Y
Reporting Services	N	N	Y
Integration Services	N	N	Y
Tools
Profiler	N	N	Y
Database Engine Tuning Advisor	N	N	Y

(1): The selections are there, but they were dead - nothing happened when you select them.
(2): The selections are there, but I got an error message when selecting any of them.

Backup and the evil RETAINDAYS option

TiborKaraszi — Sun, 08 Jul 2012 12:25:00 GMT

"So what bad has this option done?", you probably as yourself. Well, not much, but I find it evil because it confuses people, especially those new to SQL Server. I have many times seen people specifying something like 3, and expect SQL Server to keep the three most recent backups in the backup file and overwrite everything which is older than that. Well, that is not what the option does.

But before we go into details, let's look at an example backup command which is using this option:

BACKUP DATABASE sqlmaint TO DISK = 'R:\sqlmaint.bak' WITH RETAINDAYS = 3

The RETAINDAYS is also exposed in the backup dialog in SSMS: "Backup set will expire: After x days".

It is also exposed in Maintenance Plans, the backup task. The option is named "Backup set will expire: After x days". It is only enabled if you select the "Back up databases across one or more files" option, which is not the default option. This makes sense.
The default option is "Create a backup files for every database", which means that every time a backup is performed, a new file is created consisting of the database name, date and time. Since we will see that this option is only relevant when we do append, it makes sense in the RETAINDAYS not being enabled for this choice.

So what does this option do? All it does is make SQL Server return an error message of you try to do a backup using the INIT option (which means overwrite) before the date and time has occurred. In other words, it tries to help you in not overwriting a backup file, using the INIT option, before it is time. You can still overwrite earlier by either using the stronger FORMAT option instead of INIT; or by simply deleting the backup file. Btw, the RETAINDAYS parameter has a cousin named EXPIREDATE, which does the same thing but you specify a datetime value instead of number of days.

Backup generations
So, we have seen that RETAINDAYS do not in any way provide any automatic backup generation handling. There is no such functionality built-in in the BACKUP command. This means that when you are looking for functionality such as "keep backup files three days, and remove older than that", you need to look outside the BACKUP command. I.e., some script or tool of some sort.

I have an example (without the "delete old files" part) here, which I mostly created as a starting point for those who want to roll their own and want to have some example to start with. Many of you are probably using Maintenance plans (the "Create a backup files for every database" option in the backup task, along with Maintenance Cleanup task). Another popular script/tool for this is Ola Hallengren's Maintenance Solution, which you find at http://ola.hallengren.com/.

Analyzing the errorlog

TiborKaraszi — Thu, 05 Jul 2012 11:53:00 GMT

How often do you do this? Look over each message (type) in the errorlog file and determine whether this is something you want to act on. Sure, some (but not all) of you have some monitoring solution in place, but are you 100% confident that it really will notify for all messages that you might find interesting? That there isn't even one little message hiding in there that you would find valuable knowing about? Or how about messages that you typically don't are about, but knowing that you have a high frequency can be valuable information?

So, this boils down to actually reading the errorlog file. Some of you probably already have scripts and tool that makes this easier than just reading every simple message from top to bottom. I wanted to share how I do it, and this is why I wrote my Analyze SQL Server logs article. Check it out. And, feedback is always welcome!

Two bugs you should be aware of

AaronBertrand — Wed, 21 Mar 2012 16:43:00 GMT

In the past 24 hours I have come across two bugs that can be quite problematic in certain environments.

LPIM issue with SetFileIoOverlappedRange

Last night the CSS team posted a blog entry detailing a potential issue with Lock Pages in Memory and Windows' SetFileIoOverlappedRange API. I tweeted about it at the time, but thought it could use a little more treatment. The potential symptoms can vary, but include the following (as quoted from the blog post):

Wide ranging in SQL from invalid write location, lost read or write, early access to a page that is not yet fully in memory, I/O list damage such as AVs, incorrect timing reports, and many others. You may not even see the situation until days later.

The fix for this will be issued through Windows Update and through SQL Server 2008 R2 Service Pack 2 (not yet released). In the meantime, which will hopefully be short, I recommend you disable Lock Pages in Memory to avoid these potential problems, if you are using SQL Server 2008 R2 or SQL Server 2012 *unless* you are already running on Windows 8.

SQL Server Agent Log bloat

In the SQL Server 2012 RTM release, there seems to have been some debug code left in that is filling the Agent log with useless status messages. In my case this is happening every 135 seconds or so, though the Connect item indicates it is every 90 seconds:

Now, this isn't a bad thing per se, but it is unnecessarily filling the log with these useless messages, and this can cause performance issues when you are trying to read the log through various methods (e.g. xp_readerrorlog). Note that while you can suppress "Information" messages from the SQL Server Agent Log (right-click SQL Server Agent > Error Logs, choose "Configure", and uncheck "Information"), you should find that this is already unchecked by default, and that this specific information message is ignored by this setting.

If you're using SQL Sentry Event Manager, you can automatically cycle the agent log when it hits 1 MB, by changing the SQL Server Connection setting "Auto-recycle large SQL Server Agent Logs" to true:

However you will still see all of the events on your calendar, as we're currently not filtering these messages out.

If you're not using Event Manager, you might want to consider scheduling a job nightly or weekly that cycles the error log for you, since it will otherwise grow uninhibited as it fills up with these status messages. You can do this by simply calling the following stored procedure (which is exactly what we do behind the scenes when the log file hits the threshold):

EXEC msdb.dbo.sp_cycle_agent_errorlog;

This won't suppress the error messages from the log, but it will at least roll your log over so it doesn't grow unchecked. The workaround listed in the Connect item is to disable the Auto Registry Refresh feature. Using regedit.exe, go to the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.INSTANCE_NAME\SQLServerAgent

And change the value for “AutoRegistryRefresh” from 1 to 0.

Note the disclaimer though: If you change any SQL Server Agent settings, you may need to restart the Agent to see them.

Send email after Agent job execution

TiborKaraszi — Wed, 28 Dec 2011 11:15:00 GMT

After executing a job, I want to have an email with the following information:

Success or failure in the mail subject (not just in the body)
Success or failure and execution time for each job step in mail body
Have output file(s) for the job steps attached to email

Agent has none of above built-in so I created my own procedure for this. This is meant to be added as a final job step for your jobs. You find all details and the procedure here: http://www.karaszi.com/SQLServer/util_MailAfterJob.asp. Feedback is of course very welcome.

Who owns your jobs?

TiborKaraszi — Thu, 06 Oct 2011 16:13:00 GMT

Yep, another Agent blog. The topic of job owner has been discussed here before, but this is a specific situation, which potentially is a ticking bomb. First a quick recap of how agent does things:

When a job is owned by somebody who is sysadmin, then no impersonation is performed.

T-SQL jobsteps will be executed as Agent's own Windows authenticated login.
Other jobsteps (which operates as the OS level) is done in Agent's service account's context.

Above is, assuming you didn't ask for impersonation for the jobstep (Run As).

Last week, at a SQL Server admin class, Andreas Jarbéus from Swedish Match asked about job owned by Windows account and what happens when that person leaves the company. I was about to say "no problem, if the job owner is sysadmin", but I felt there was a bit more to it. The scenario is:

The job owner is member of a Windows/AD group. Say you have a "SQL Server DBA" group in your AD.
This group is added as a login to your SQL Servers. The persons in that group do not have individual logins in SQL Server - only through this group.
This group is sysadmin. (I'm not saying that having jobs in general owned by sysadmins is a best practice. You can for instance have vendors installing some software and you don't want to make them sysadmin. In such a case, clearly we don't want the job to keep running if that person would disappear. So, I'm targeting jobs owned by sysadmin here, and I would bet you have bunch of those.)
Somebody in this group creates an Agent job.
The person in the group leaves the company and is removed from this Windows/AD group.

Above is a pretty common scenario. The thing now is that the job no longer works. And here's the reason:

If the owner was somebody in Windows/AD who's SID exists in SQL Server (the individual was added as a login to SQL Server), then the job will still work. This also applies for SQL Logins which might be disabled (like "sa" for instance). The reason for this is that the SID for the job owner exists in sys.server_principals and SQL Server will know that this SID is sysadmin. Since Agent won't do impersonation for sysadmins, there's no need to "go out to Windows" and see if this SID exists in Windows/AD. Remove the login, and the job will stop working, though - but you have at least don't something explicitly inside your SQL Server to cause this (removed the login).

But when the owner's SID don't exist in sysadmin we have a problem. The problem is that the recorded owner of the job is the SID for the Windows user, even though that SID doesn’t exist in sys.server_principals. As long as this still exists in Windows/AD, the job will work just fine. Agent will run this job owned by ?, ask Windows who this SID is and see that this SID is a member of a group which exists as a login in SQL Server and that is sysadmin. Now, imagine what happen if the SID doesn't exist in Windows anymore. The job is owned by ?, and that is all that SQL Server knows. The SID no longer exist in Windows/AD so you there's nobody to tell SQL Server "Yeah, this used to be a sysadmin in SQL Server - through a group membership in Windows/AD".

Even worse, the job seems to keep working until you re-start the SQL Server service, some caching going on. Makes it even more difficult to determine why the job suddenly stops working: "We deleted this Windows account 4.5 months ago." This is easy to repro, and you don't even need a domain (local Windows accounts and groups work just fine):

Create a Windows group for this.
Create a Windows account for this.
Add the Windows account to above group.
Add above group as a login to SQL Server.
Make this group sysadmin.
Create a job, with two jobsteps:
T-SQL which executes: PRINT ORIGINAL_LOGIN() + ' | ' + SUSER_SNAME() + ' | ' + USER_NAME()
CmdExec which executes: WHOAMI.EXE
(Above isn't really necessary, but useful for testing other impersonation aspects of Agent)
Specify the Windows account as owner of the job.
Run the job, make sure it works fine.
Remove the Windows account from the Windows group.
Run the job and see it fails.

I'm sure this has been blogged before, but I was a bit surprised by it. I have now added a check for this in my "Check best practices" stored procedure I run every day... On the flip side, this doesn't seem to happen for multi-server (MSX) jobs, the ownership seems to have special handling for these - you will see if you check the owner_sid in sysjobs.

Agent Alerts Management Pack updated

TiborKaraszi — Thu, 15 Sep 2011 19:13:00 GMT

I have updated the Agent Alerts Management Pack, cleaned up the T-SQL code which creates the alerts. You can now just copy, paste and execute that code. Earlier, for some of the some of the alert definitions it just generated the calls to the query result window, and you had to copy this to a query window and execute it. Now you just copy the code and execute it. You find it here: http://www.karaszi.com/SQLServer/util_agent_alerts.asp.

Ola Hallengren's maint procedures now supports logging to table

TiborKaraszi — Sat, 16 Jul 2011 01:19:00 GMT

This can for instance be very useful if you want to keep track how long time things take, over time.

Check them out here.Version history here.

Article released about moving databases

TiborKaraszi — Sun, 10 Jul 2011 13:26:00 GMT

Just a short notice that I've released an article about moving databases between SQL Server instances: You find it at http://www.karaszi.com/SQLServer/info_moving_database.asp .

Connect Digest : 2011-03-26

AaronBertrand — Sat, 26 Mar 2011 12:50:00 GMT

This week I have collected five important bugs involving Management Studio, and I hope you'll visit them, provide input and vote on the ones you think are worthwhile.

#651176 : SSMS tries to access disconnected network drives when in the Save File As wizard

Paul Randal (blog | twitter) filed this issue when he experienced significant delays trying to save files from SSMS. The issue: SSMS is spinning its wheels trying to access drives that are currently disconnected. I have seen this issue before, but did not put two and two together at the time. Surely it would be easy enough for Management Studio to poll the properties of all of the drives known to Windows, and only pay any attention to the drives that are online and accessible.

#557402 : SSMS : Can no longer create or edit job steps

This is one of mine that I filed last May - when editing a job step, you may find that the dialog raises a very non-user-friendly error about some IClassFactory, and the only workaround so far (other than modifying all of your jobs with T-SQL) is to close Management Studio and start over. Microsoft originally claimed this was fixed two cumulative updates ago, but many people have confirmed that even the most recent CU for SQL Server 2008 R2 does not resolve the issue. This time for sure?

#349116 : keyboard shortcut Alt+F1 (sp_help) doesn't work for tables belonging to non-default schemas

Michael Swart (blog | twitter) raised this issue in 2008, and it remains in limbo. Personally I think it stands to reason that, as the use of schemas becomes more commonplace, the tools should support them better. In this case, I am not sure that Alt+F1 should be connected directly to sp_help, since Microsoft has already committed to not extending the sp_ procedures for new functionality. But give us an easy way to map F1 or Alt+F1 to some kind of query that *will* give us information about the object in question.

#614130 : IntelliSense doesn't work with CAST

Simon Sabin (blog | twitter) filed this complaint last year, and I wholeheartedly agree. IntelliSense is a mere annoyance in general, and I usually shut it off after any tangible amount of coding; but when it starts pulling this nonsense, I'm ready to throw it out the window entirely. Simon's complaint here is specifically with CAST - when you type "CAST(col AS " it auto-completes with the weirdest stuff you could imagine, and doesn't even offer you valid data types. Now, I'm not going to sugar-coat this: there are a LOT of things to fix with IntelliSense, but I can only pimp so many - and we can only expect so many to be fixed in any reasonable development cycle. Maybe I'll dedicate a future digest to IntelliSense? In the meantime, you may be better off with RedGate's SQLPrompt or the free dbForge SQL Complete Express.

#124842 : 'Remember Password' option is not remembered in Registered Server Properties

David Kean filed this back in 2005, and it's a bug I'm sure we've all experienced. Yes, you can overcome it temporarily with a workaround (delete the SqlStudio.bin file), but this is tedious and may affect other settings as well. Also, this workaround will no longer be valid in the Denali timeframe, since Management Studio is now actually just an extension of the official Visual Studio shell. I filed a related bug, #339592, and Joseph Leathlean filed one about duplicate entries in the connection dialog, #373794. Both are still active; the latter is still very much an issue in Denali: