Notes from the Field #077: Mike Walsh asks every DBA to watch their vendors, and asks vendors to try a little harder. Lessons for both sides.
How to Identify Cross-Database Transactions in SQL Server
To find cross-database transactions, search the code for references to other databases, watch a live transaction with a DMV, or read special records in the transaction log. This post tests all three on two demo databases. It also shows why a popular log script flags a brand new database.
SQL SERVER – Split Comma Separated List Without Using a Function
I have shown how to build a comma separated list many times. This time a reader wanted the reverse: split a comma separated list into rows without a function.
SQL SERVER – Create Login with SID – Way to Synchronize Logins on Secondary Server
Create login with SID to keep SQL logins in sync on a secondary server, so users connect after log shipping, AlwaysOn or mirroring failover.
Forced Parameterization: When It Cuts CPU and When It Hurts
Test forced parameterization against ad hoc compile pressure with query_hash counts, compilations per second, CPU, and parameter sensitivity.
SQL SERVER – Is XP_CMDSHELL Enabled on the Server?
Is XP_CMDSHELL enabled on your server? I recommend avoiding it for security. Here are two ways to check and turn it off, starting with T-SQL.
SQL SERVER – Using MaxTransferSize parameter with SQL Server Backups
A DBA asked whether backups on new EMC storage should use a 256 KB transfer size. Here is what I learned about the MaxTransferSize parameter.