Tibor Karaszi

Express Edition revisited, focus on SSMS

2013-01-30T17:01:00Z

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

Backup and the evil RETAINDAYS option

2012-07-08T12:25:00Z

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

Analyzing the errorlog

2012-07-05T11:53:00Z

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

List columns where collation doesn't match database collation

2012-05-23T16:58:00Z

Below script lists all database/table/column where the column collation doesn't match the database collation. I just wrote it for a migration project and thought I'd share it. I'm sure lots of tings can be improved, but below worked just fine for me for a one-time execution on a number of servers. IF OBJECT_ID ( 'tempdb..#res' ) IS NOT NULL DROP TABLE #res GO DECLARE @db sysname , @sql nvarchar ( 2000 ) CREATE TABLE #res ( server_name sysname , db_name sysname , db_collation sysname , table_name...(read more)

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)