Convert varbinary plan_handle to varchar

Here is how to do it in SQL 2008+

     SELECT cp.plan_handle
          , st.text
          , 'DBCC FREEPROCCACHE (' + convert(varchar(max), cp.plan_handle, 2) + ');'
       FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st



In SQL 2005

Could use undocumented function

master.dbo.fn_varbintohexstr

or XML

cast('' as xml).value('xs:hexBinary(sql:variable("column_or_variable"))', 'varchar(max)');

VEEAM database backups

VEEAM's vmware backup management software often touts how it can "backup SQL Server databases and truncate the transaction log".

This is probably significant for many corporations that do not have a DBA on staff and only have system administrators and helpdesk folks minding the database servers.

Out of the box, databases are not backed up unless someone does something to back them up.  That and the transaction logs for FULL and BULK_LOGGED recovery model databases continue to grow until they get backed up and then are automatically truncated once they are backed up.

This can result in poor performing databases that have disks filling up and transaction log files that are many times larger than the actual data files.

VEEAM allows system administrators a way to trigger native database and transaction log backups.   A sysadmin would probably think it is enough that now those unruly transaction logs aren't filling up disks anymore, pat themselves on the back, and think they are done.

Unfortunately, folks tend to stop short of asking questions "What are transaction logs, are they important?", "I wonder why transaction logs grow until they are backed up?', etc...

The trouble with this is that if database and transaction log backups are being performed by other processes, then if VEEAM initiates a backup, it can break the continuity of your backup chain and cripple your ability to do point in time restores to a point in time between full backups.

Having a mixture of tools managing database backups is a recipe for disaster.

If you have squirrelly sysadmins who insist on using VEEAM to back up databases, you will need to do one of two things:

1.  Tell them to perform only COPY-ONLY full backups and not to perform any transaction log backups.  Then assure them that you are performing your own full and transaction log backups using your own backup management tools and that the transaction logs are getting truncated.

-OR-

2.  Use VEEAM to manage ALL of your full and transaction log backups

linked server data integrations

While linked servers are a convenient feature that developers like because they are easy to implement, they are not very efficient at moving large amounts of data

General recommendations for any data integration include:

1. Do not “push” data over a link
- Do not perform inserts or updates over link

2. Use linked servers only for “pulling” small and medium amounts of data
- Simple select statement against a view in remote system -- Execute stored procedure in remote system that returns a data set
- This allows for remote system to optimize the query plan
- Alternative bulk copy methods should be used for really large amounts of data, remember that data pulled across linked servers will be eating up memory that your production SQL Server needs

3. The system that is being updated (written to) should perform a read-only pull
- this is better for security in that it only needs read-only connection to remote system
- DML is transactional and causes additional overhead that a select statement does not

4. Data pulls should be a single dataset
- There should not be “cross system” joins in the where clause
- Do not perform looping or cursors across a connection
- Either of these make it very difficult for query optimizer to develop query plan across systems
- If have to, it is better to pull more data than needed from remote system, and then filter it with join in local system than to try and join across systems

5. Also, did I mention “Do not perform inserts or updates over links”?