THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

  • Do you clean up your Database Mail log tables?

    Database Mail has a couple of log tables in the msdb database. These can become large over time. I've seen MSDB databases over 1 GB in size, where normal size is less than 50 MB (heavy usage of old SSIS deployment model excluded).

    Unfortunately Maintenance Plans do not have built-in functionality for this, nor does Ola Hallengren's excellent maintenance solution ( http://ola.hallengren.com/ ). All you have to do is to schedule an agent job to be executed, say, every week, having one T-SQL jobstep containing: 

    DECLARE @DeleteOlder DATETIME

    SET
    @DeleteOlder = DATEADD(MONTH, -1, CURRENT_TIMESTAMP)

    EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DeleteOlder

    EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @DeleteOlder

    Above removes mail history older than one month. Adjust to your liking, using the values in the DATEADD function.

    As always, remember to comment your job and to specify appropriate database for the T-SQL jobstep (for documentation purposes, msdb in this case). 

  • Do you want improved performance?

    Can you survive a few lost transactions if your server does a "hard shutdown"? If so, check out SQL Server 2014 and "Delayed Durability".

    A cornerstone in SQL Server's transaction handling has up until 2014 been "durability" for a committed transaction. Durability is by the way the "D" in the ACID acronym: Atomicity, Consistency, Isolation and Durability.

    Durability means that SQL Server has do perform a synchronous write to the LDF file for each transaction. This so that SQL Server can re-construct all committed transactions up until the point of a (potentially hard) shutdown. 

    In SQL Server 2014, MS has planned for a database setting called "Delayed Durability". Setting this means that SQL Server can bath writes to the ldf file, meaning a potentially significant improved performance for applications where you have many small transactions.

    I did a quick test, using a bench from an earlier blog post of mine (http://sqlblog.com/blogs/tibor_karaszi/archive/2008/08/14/are-inserts-quicker-to-heap-or-clustered-tables.aspx) to test what difference I would see for that workload. Roughly (for 50000 rows, on a PC with single spinning disk HD):

    All inserts in one transaction averaged about 0.3 seconds.

    One transaction per row with Delayed Durability set to OFF approx 12 seconds. 

    One transaction per row with delayed durability set to Forced approx 1.2 seconds. 

     As you can see, for this workload we got about a tenfold performance improvement by letting SQL Server batch the write operations to the ldf file. The question is how much improvement you get for your workload and if you can tolerate to lose some modifications in case of a hard shutdown? 

  • Check for Instant File Initialization

    Instant File initialization, IFI, is generally a good thing to have. Check out this earlier blog post of mine f you don't know what IFI is and why it is a good thing: blog. The purpose of this blog post is to provide a simple script you can use to check if you have IFI turned on.

    Note that the script below uses undocumented commands, and might take a while if you have a large errorlog file...

     
    USE MASTER;
    SET NOCOUNT ON

    -- *** WARNING: Undocumented commands used in this script !!! *** --

    --Exit if a database named DummyTestDB exists
    IF DB_ID('DummyTestDB') IS NOT NULL
    BEGIN
      RAISERROR
    ('A database named DummyTestDB already exists, exiting script', 20, 1) WITH LOG
    END

    --Temptable to hold output from sp_readerrorlog
    IF OBJECT_ID('tempdb..#SqlLogs') IS NOT NULL DROP TABLE #SqlLogs
    GO
    CREATE TABLE #SqlLogs(LogDate datetime2(0), ProcessInfo VARCHAR(20), TEXT VARCHAR(MAX))

    --Turn on trace flags 3004 and 3605
    DBCC TRACEON(3004, 3605, -1) WITH NO_INFOMSGS

    --Create a dummy database to see the output in the SQL Server Errorlog
    CREATE DATABASE DummyTestDB
    GO

    --Turn off trace flags 3004 and 3605
    DBCC TRACEOFF(3004, 3605, -1) WITH NO_INFOMSGS

    --Remove the DummyDB
    DROP DATABASE DummyTestDB;

    --Now go check the output in the SQL Server Error Log File
    --This can take a while if you have a large errorlog file
    INSERT INTO #SqlLogs(LogDate, ProcessInfo, TEXT)
    EXEC sp_readerrorlog 0, 1, 'Zeroing'

    IF EXISTS(
              
    SELECT * FROM #SqlLogs
              
    WHERE TEXT LIKE 'Zeroing completed%'
                
    AND TEXT LIKE '%DummyTestDB.mdf%'
                
    AND LogDate > DATEADD(HOUR, -1, LogDate)
            )
      
    BEGIN
        PRINT
    'We do NOT have instant file initialization.'
        
    PRINT 'Grant the SQL Server services account the ''Perform Volume Maintenance Tasks'' security policy.'
      
    END
    ELSE
       BEGIN
        PRINT
    'We have instant file initialization.'
      
    END

  • Wait random number of minutes

    Why on earth would you want to do that? you ask. Say you have a job that is scheduled to start at the same time over a number of servers. This might be because you have an SQL Server Master/Target server environment (MSX/TSX) or you quite simply script a job and execute that script on several servers. You probably want to spread the load on your SAN and virtual machine host a bit. This is the exact reason I use this procedure. I frequently use MSX servers and I usually add a job step (executing this procedure) to wait a random number of minutes between 0 and 30.

    You find the procedure here.   

  • Resynchronizing a target server (MSX - TSX)

    I often use SQL Server Agent master / target servers (MSX/TSX). I find it so convenient to create the job once and then just add whatever targetservers (TSX) should have this job. Especially when you later modify the job. Again, just modify it once. The usage of MSX in general, and how I use it, can easily become a series of blog posts in itself. But that is not the point here.

    Sometimes a TSX goes out-of-sync with its master. I've never understood exactly under what circumstances, but it feels like it happened when you do things "too quickly". Like change a job, push it out, and before the push has finished, you change it again. Or something like that. A TSX going out-of-sync doesn't happen frequently. I've had it a handful of times. And every time, I have spent time searching etc. on how to fix it.

    A couple of days ago, a client of mine had this case, and he had himself tracked down a possible way to fix this. We decided to go ahead with this, and it worked just fine. So, the purpose here is to document the (very easy) fix, for whenever this happens again. And for all of you out there who might benefit, of course. The error you see is something like:

    [291] An unresolved problem exists with the download instructions (sysdownloadlist) for target server 'Y' at MSX 'X'

    X here is obviously the master and Y the target. And the solution was quite simply (in the msdb database):

    EXEC dbo.sp_resync_targetserver, N'Y'

    Are you using MSX/TSX? Have you had sync issues? How did you handle them?

  • Express Edition revisited, focus on SSMS

     (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

    "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

    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!

  • List columns where collation doesn't match database collation

    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 sysname, column_name sysname, column_collation sysname)

    DECLARE c CURSOR FOR
    SELECT
    name FROM sys.databases WHERE NAME NOT IN('master', 'model', 'tempdb', 'msdb') AND state_desc = 'ONLINE'

    OPEN c
    WHILE 1 = 1
    BEGIN
    FETCH
    NEXT FROM c INTO @db
    IF @@FETCH_STATUS <> 0
        
    BREAK
    SET
    @sql =
        
    'SELECT
       @@SERVERNAME AS server_name
      ,'''
    + @db + ''' AS db_name
      ,CAST(DATABASEPROPERTYEX('''
    + @db + ''', ''Collation'') AS sysname) AS db_collation
      ,OBJECT_NAME(c.object_id, '
    + CAST(DB_ID(@db) AS sysname) + ') AS table_name
      ,c.name AS column_name
      ,c.collation_name AS column_collation
    FROM '
    + QUOTENAME(@db) + '.sys.columns AS c
      INNER JOIN '
    + QUOTENAME(@db) + '.sys.tables AS t ON t.object_id = c.object_id
    WHERE t.type = ''U''
      AND c.collation_name IS NOT NULL
      AND c.collation_name <> CAST(DATABASEPROPERTYEX('''
    + @db + ''', ''Collation'') AS sysname)
    '
    --PRINT @sql
    INSERT INTO #res
    EXEC(@sql)
    END
    CLOSE
    c
    DEALLOCATE c
    SELECT * FROM #res
  • Send email after Agent job execution

    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.

  • SQL Live Monitor

    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:

    SQL Live Monitor

    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 I liked about it is that there is no installation, just an exe file to download and run. Here is where you find it: http://sqlmonitor.codeplex.com/.

  • Who owns your jobs?

    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

    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

    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

    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.

This Blog

Syndication

Privacy Statement