Tibor Karaszi

Send email after Agent job execution

2011-12-28T11:15:00Z

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 ....(read more)

SQL Live Monitor

2011-11-19T08:46:00Z

I just found this one out there and wanted to share it. It connects to an instance and show you a bunch of figures. Nothing you can't extract yourself with SQL queries, but sometimes it is just nice to have one tool which is very easy to use. Here's what it looks like when connecting to an instance with no load on it: As you can see, there are some hyperlinked pages as well, and there are also some interesting options (like logging to CSV or for PAL analysis) under the "Option" button. One more thing...(read more)

Who owns your jobs?

2011-10-06T16:13:00Z

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...(read more)

Agent Alerts Management Pack updated

2011-09-15T19:13:00Z

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 ....(read more)

Ola Hallengren's maint procedures now supports logging to table

2011-07-16T01:19:00Z

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 ....(read more)

Article released about moving databases

2011-07-10T13:26:00Z

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 ....(read more)

Restricting memory using Resource Governor

2011-04-19T11:13:00Z

You might know that Resource Governor (RG) can be used to allow you to affect resource allocation inside SQL Server. RG was introduced with SQL Server 2008 and requires Enterprise Edition or similar/higher. There are two things you can govern using RG: cpu and memory. For introductory information on RG, see for instance these BOL topics . A misconception I see from time to time is that the memory setting is to restrict the Buffer Pool, BP, (mainly used for caching pages from disk). This seems difficult...(read more)

What does this Express Edition look like, anyhow?

2011-02-10T17:40:00Z

Chances are that you rarely get to see the Express Edition of SQL Server if you mainly work with servers. This blog post is inspired from a forum discussion where some functionality is missing in SQL Server Management Studio (SSMS) and I suspected that the tools were installed as a Basic installation instead of Complete installation. So how do I check that? Difficult if I don’t have such an install to look at. I’m training this week and have a few spare machines to play with so I went ahead and installed...(read more)

Are we Borg?

2011-02-09T20:47:00Z

Is it time to stop remeber things? For real, this time? Today I needed to find two pieces of SQL Server related information. One was straight foward, I posted it on #sqlhelp on twitter and a minute or so later I had the answer. The other was a bit more involved and I asked around in our MVP mail list - a couple of hours later I had bunch of suggestions and tips. These are only examples, it could just as well have been some web-forum, newsgroup, or some other forum. We've all had cases like this,...(read more)

Upgrading from Express Edition to Standard Edition

2011-01-27T12:14:00Z

Say you encounter an SQL Server which is Express Edition, and it really should have been some higher edition. Sounds familiar? It is common for me as a consultant to find plenty of SQL Servers at a customer's site. Many of the databases in these will be moved (typically using backup and restore) to a "real" SQL Server. But in some cases, this might not be desirable. You want to convert the whole instance, from Express to a "real" SQL Server edition. I'm attending a great SharePoint course for Daniel...(read more)

Point-in-time restore of database backup?

2010-12-28T16:42:00Z

SQL Server 2005 added the STOPAT option for the RESTORE DATABASE command. This sounds great - we can stop at some point in time during the database backup process was running! Or? No, we can't. Here follows some tech stuff why not, and then what the option is really meant for: A database backup includes all used extents and also all log records that were produced while the backup process was running (possibly older as well, to handle open transactions). When you restore such a backup, SQL Server...(read more)

Another bugfix for sp_indexinfo

2010-11-09T08:26:00Z

JackMcC reported correctly at http://sqlblog.com/blogs/tibor_karaszi/archive/2009/02/18/sp-indexinfo-updated.aspx that sp_indexinfo will count rows double (or even tripple) if there are lob or row overflow allocations. Now fixed. Thanks Jack....(read more)

Why we never want to trick SQL Server into rebuilding a log file

2010-10-14T17:04:00Z

"Never" is a strong word, so let me say that we really really want to avoid it, if at all humanly possible. In short, we will have a (potentially) broken database, both at the physical level and at the logical level. Read on. Just to be clear, when I refer to a "log file" here, I'm talking about a transaction log file, an .ldf file. Ever so often we see in forums how log files are "lost", "disappears" or so. Now, SQL Server relies on the ldf file at startup to bring the database to a consistent state....(read more)

New util proc: sp_tableinfo

2010-10-10T11:59:00Z

Some of you might use my sp_indexinfo to easily get lots of details about the indexes in a database. I added an sp_tableinfo with info rolled up to table level. This allow to quickly see the largest tables in a database and things like that. Check it out at http://www.karaszi.com/SQLServer/util_sp_tableinfo.asp ....(read more)

Why can't we have column alias in ORDER BY?

2010-10-03T09:43:00Z

You probably think something like "What! Of course we can!". And so did I when I read the title for a post about this in a forum. Let us first establish that you can have a column alias in ORDER BY: SELECT p.ProductID AS pid , p.ListPrice AS pr FROM Production.Product AS p ORDER BY pr The problem was that a table name was also in the ORDER BY clause. And this is when it becomes weird. The problem isn't the column alias, it is the table alias. Basically the desire is to write something like: SELECT...(read more)