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

  • Updated sp_indexinfo

    It was time to give sp_indexinfo some love. The procedure is meant to be the "ultimate" index information procedure, providing lots of information about all indexes in a database or all indexes for a certain table. Here is what I did in this update:

    • Changed the second query that retrieves missing index information so it generates the index name (based on schema name, table name and column named - limited to 128 characters).
    • Re-arranged and shortened column names to make output more compact and more commonly used column moved to the right.
    • Uncommented some columns that were previously commented. (At least one, filter, has to be commented if you want to run this on 2005.)
    • Added support for columnstore indexes.
    • Decoded the type for columnstore indexes to col-store.

    You find the procedure here. 

  • Does your 3:rd party backup software ruin your SQL Server backup plan?

    Sounds scary, huh? And it surely can be! Here’s an example of one such case:

     

    A client of mine had implemented a 3:rd party backup solution in their VM Ware environment. This shouldn't affect the SQL Server backups. Or that was the plan. I won’t mention what backup software this is, since I’m sure that there are other software also doing strange things. And in the end, it is up to you to verify that your backup and restore strategy is solid. So let us just call this software “BACK”.

     

    BACK performs a snapshot backup of the virtual machine. The client had scheduled this so it happened at about 4 am each day.

     

    Our SQL Server backups were implements so that 7 pm we did a full backup and every hour we did a log backup.

     

    Nothing strange here and these should be independent of each other, right? Wrong!

     

    When looking at the backup history, I realized that SQL Server would see the snapshot backup as a full backup. OK, that will possibly affect out plan to go for differential backups, but it shouldn't affect our transaction log backups. We all know that a database backup doesn't break or affect the log backup chain.

     

    But what I did find was that BACKUP performed a log backup immediately after the snapshot database backup. And the log backup was taken to the file name “nul”. Yes, the binary wasteland.

     

    The end result of this was that the log backups that we performed were usable between 7 pm and 4 am. After that, the log backups are useless. No option to restore anything for the work performed during the working day. It would have been more honest if BACK would set the recovery model to simple instead. That would at least give us a more fair chance to catch the problem (sooner then we did).

     

    Now, we did find an option in BACK to not truncate the log (or whatever they called the checkbox), but by default it did perform this disastrous log backup to nul.

     

    The next step was to consider implementation of the differential backup plan. But time was running out for my assignment so we only managed a quick check. And from what we found out, it seemed that BACK doesn't have an option to produce its snapshot backup to be seen as a COPY_ONLY backup. This means that it prohibits us to implements SQL Server differential backup and saving some 500 GB backup storage produced each week (for only one of the servers). Now, let me leave a disclaimer here since I didn't have time to investigate this much as all, but this is what it looks like at the moment. I will dig deeper into the differential backup strategy next time I’m with this client.

     

    The moral of the story? You already know that. Test your restore strategy. You might just be (unpleasantly) surprised!

  • Using whoami to check for instant file initialization

    Read this if you want to read more about instant file initialization (IFI). In an earlier blog post, I posted a script that uses trace flags, created a dummy-database and then sp_readerrorlog to check for IFI.

    Another option is to use the OS command whoami, as documented here. Below script uses whoami to check for IFI, or more precisely SQL Server having the "Perform Volume Maintenance Tasks" policy. It uses xp_cmdshell, meaning you have to be sysadmin in order to use it.


    IF OBJECT_ID('tempdb..#res') IS NOT NULL DROP TABLE #res
    SET NOCOUNT ON
    GO

    DECLARE
    @was_xp_cmd_on tinyint
    ,@was_show_adv_on tinyint
    ,@is_ifi_enabled tinyint

    --Exit if we aren't sysadmin
    IF IS_SRVROLEMEMBER('sysadmin') <> 1
    BEGIN
      RAISERROR
    ('You must be sysadmin to execute this script', 16, 1)
      
    RETURN
    END

    --Save state for show advanced options
    SET @was_show_adv_on =
    (
    SELECT CAST(value_in_use AS tinyint)
    FROM sys.configurations
    WHERE name = 'show advanced options'
    )

    --Turn on show advanced options, if neccesary
    IF @was_show_adv_on = 0
    BEGIN
       EXEC
    sp_configure 'show advanced options', 1
      
    RECONFIGURE
    END

    --Save state for xp_cmdshell
    SET @was_xp_cmd_on =
    (
    SELECT CAST(value_in_use AS tinyint)
    FROM sys.configurations
    WHERE name = 'xp_cmdshell'
    )

    --Turn on xp_cmdshell, if neccesary
    IF @was_xp_cmd_on = 0
    BEGIN
       EXEC
    sp_configure 'xp_cmdshell', 1
      
    RECONFIGURE
    END

    CREATE TABLE
    #res (col VARCHAR(255))

    INSERT INTO #res(col)
    EXEC xp_cmdshell 'whoami /priv /nh'

    SET @is_ifi_enabled =
    (
    SELECT CASE WHEN PATINDEX('%Enabled%', col) > 0 THEN 1 ELSE 0 END
    FROM
    #res
    WHERE col LIKE '%SeManageVolumePrivilege%'
    )

    IF @is_ifi_enabled = 1
    SELECT 'Instant file initialization is enabled'
    ELSE
    SELECT
    'Instant file initialization is NOT enabled'

    --Reset state for xp_cmdshell
    IF @was_xp_cmd_on = 0
    BEGIN
      EXEC
    sp_configure 'xp_cmdshell', 0
      
    RECONFIGURE
    END

    --Reset state for show advanced options
    IF @was_show_adv_on = 0
    BEGIN
      EXEC
    sp_configure 'show advanced options', 0
      
    RECONFIGURE
    END
  • How often do you rebuild your heaps?

    Never? You are not alone. None of the maintenance solutions I use includes functionality to rebuild a heap, and that includes Maintanance Plans and Ola Hallengren's maintenance solution.

    "So why would you want to rebuild a heap, when it isn't sorted in the first place?", you ask. The answer is to get rid of Forwarding Pointers, and these can really hurt performance by adding lots more logical and physical reads, and random I/O. See for instance this from Kalen Delaney, this from Hugo Kornelis and this from the SQLCAT team.

    SQL Server 2008 added the ALTER TABLE command, with the REBUILD clause. And this is what I'm using in my procedure rebuild_heaps which rebuilds all fragmented heaps on a SQL Server.

    You find the procedure here: http://www.karaszi.com/SQLServer/util_rebuild_heaps.asp.

  • Setting max server memory

    If there is one server setting that is close to universal to configure, then it is probably the "max server memory" setting. The setting is documented here. There are plenty of articles out there on this subject. The purpose for this article is for me to have somewhere to point when I get the question: "What value should I set this to?". I usually refer to Jonathan Kehayias' blog post when I get this question: http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/. For starters you want a simple formula to begin with, and then some hints on what to monitor if you want to fine-tune the value. Jonathan's articles provide both. The simple formula for how much to reserve for the OS is:

    1 GB
    Plus 1 GB for every 4 GB in the machine, between 4 and 16 GB
    Plus 1 GB for every 8 GB in the machine, above 16 GB

    And here's a TSQL script if you don't want to do the math yourself. Note that you need to specify how much memory you have in the machine.

     


    --Based on Jonathan Kehayias' blog post:
    --http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

    IF OBJECT_ID('tempdb..#mem') IS NOT NULL DROP TABLE #mem
    GO

    DECLARE
    @memInMachine DECIMAL(9,2)
    ,
    @memOsBase DECIMAL(9,2)
    ,
    @memOs4_16GB DECIMAL(9,2)
    ,
    @memOsOver_16GB DECIMAL(9,2)
    ,
    @memOsTot DECIMAL(9,2)
    ,
    @memForSql DECIMAL(9,2)
    ,
    @CurrentMem DECIMAL(9,2)
    ,
    @sql VARCHAR(1000)

    CREATE TABLE #mem(mem DECIMAL(9,2))

    --Get current mem setting----------------------------------------------------------------------------------------------
    SET @CurrentMem = (SELECT CAST(value AS INT)/1024. FROM sys.configurations WHERE name = 'max server memory (MB)')

    --Get memory in machine------------------------------------------------------------------------------------------------
    IF CAST(LEFT(CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR(20)), 1) AS INT) = 9
      
    SET @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'
    ELSE
       IF
    CAST(LEFT(CAST(SERVERPROPERTY('ResourceVersion') AS VARCHAR(20)), 2) AS INT) >= 11
        
    SET @sql = 'SELECT physical_memory_kb/(1024*1024.) FROM sys.dm_os_sys_info'
      
    ELSE
         SET
    @sql = 'SELECT physical_memory_in_bytes/(1024*1024*1024.) FROM sys.dm_os_sys_info'

    SET @sql = 'DECLARE @mem decimal(9,2) SET @mem = (' + @sql + ') INSERT INTO #mem(mem) VALUES(@mem)'
    PRINT @sql
    EXEC(@sql)
    SET @memInMachine = (SELECT MAX(mem) FROM #mem)

    --Calculate recommended memory setting---------------------------------------------------------------------------------
    SET @memOsBase = 1

    SET @memOs4_16GB =
      
    CASE
        
    WHEN @memInMachine <= 4 THEN 0
      
    WHEN @memInMachine > 4 AND @memInMachine <= 16 THEN (@memInMachine - 4) / 4
        
    WHEN @memInMachine >= 16 THEN 3
      
    END

    SET
    @memOsOver_16GB =
      
    CASE
        
    WHEN @memInMachine <= 16 THEN 0
      
    ELSE (@memInMachine - 16) / 8
      
    END

    SET
    @memOsTot = @memOsBase + @memOs4_16GB + @memOsOver_16GB
    SET @memForSql = @memInMachine - @memOsTot

    --Output findings------------------------------------------------------------------------------------------------------
    SELECT
    @CurrentMem AS CurrentMemConfig
    , @memInMachine AS MemInMachine
    , @memOsTot AS MemForOS
    , @memForSql AS memForSql
    ,'EXEC sp_configure ''max server memory'', ' + CAST(CAST(@memForSql * 1024 AS INT) AS VARCHAR(10)) + ' RECONFIGURE' AS CommandToExecute
    ,'Assumes dedicated instance. Only use the value after you verify it is reasonable.' AS Comment

     

     

    Edit 1 2014-03-06: Got the memory in the machine from sys.dm_os_sys_info, suggested by Ola Hallengren.

    Edit 2 2014-03-20: Adjusted script to work on 2008R2 and lower, as suggested by Shanky. Also added current mem config to output. Changed output from PRINT to SELECT (to facilitate multi-server query window).

    Edit 3 2014-03-22: Adjusted script to support 2005, as suggested by Steve Meder. Also changed to only one resultset.

    Edit 4 2014-05-30: Fixed some bugs for 2005, reported by Lee Linares.

     

  • 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.

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement