sp_recompile

Causes stored procedures, triggers, and user-defined functions to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. In a SQL Server Profiler collection, the event SP:CacheInsert is logged instead of the event SP:Recompile.

https://msdn.microsoft.com/en-us/library/ms181647(v=sql.110).aspx

sp_refreshview

Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view depends.

https://msdn.microsoft.com/en-us/library/ms187821(v=sql.110).aspx

Log Reuse Waits

   SELECT name
        , log_reuse_wait_desc
     FROM sys.databases;

• NOTHING - vlfs available
• CHECKPOINT - dirty pages in buffer pool, awaiting checkpoint, issue checkpoint and/or adjust RECOVERY INTERVAL setting
• LOG_BACKUP - waiting for next transaction log backup
• ACTIVE_BACKUP_OR_RESTORE - transaction log backup in progress or is "hung"
• ACTIVE_TRANSACTION - open transaction, use DBCC OPENTRAN to identify
• DATABASE_MIRRORING - secondary falls behind
• REPLICATION - subscriber falls behind
• DATABASE_SNAPSHOT_CREATION - transient; snapshot creation in progress
• LOG_SCAN -  transient; fn_dblog, during a checkpoint initiates a log scan to synchronize log sequence numbers, or other process that causes a log scan
• OTHER_TRANSIENT - currently not in use

DATABASEPROPERTYEX

DATABASEPROPERTYEX ( database, property )

database is a name of the database. It is of type nvarchar(128) 

property is an option or property setting to be returned. It is of type nvarchar(128). Below are the possible property names.

Value	Description	Returned Value
Collation	Default collation name for the database.	Collation name
IsAnsiNullDefault	Database follows SQL-92 rules for allowing null values.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAnsiNullsEnabled	All comparisons to a null evaluate to unknown.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAnsiPaddingEnabled	Strings are padded to the same length before comparison or insert.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAnsiWarningsEnabled	Error or warning messages are issued when standard error conditions occur.	1 = TRUE 0 = FALSE NULL = Invalid input
IsArithmeticAbortEnabled	Queries are terminated when an overflow or divide-by-zero error occurs during query execution.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoClose	Database shuts down cleanly and frees resources after the last user exits.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoCreateStatistics	Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoShrink	Database files are candidates for automatic periodic shrinking.	1 = TRUE 0 = FALSE NULL = Invalid input
IsAutoUpdateStatistics	Auto update statistics database option is enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsCloseCursorsOnCommitEnabled	Cursors that are open when a transaction is committed are closed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsFulltextEnabled	Database is full-text enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsInStandBy	Database is online as read-only, with restore log allowed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsLocalCursorsDefault	Cursor declarations default to LOCAL.	1 = TRUE 0 = FALSE NULL = Invalid input
IsMergePublished	The tables of a database can be published for replication, if replication is installed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsNullConcat	Null concatenation operand yields NULL.	1 = TRUE 0 = FALSE NULL = Invalid input
IsNumericRoundAbortEnabled	Errors are generated when loss of precision occurs in expressions.	1 = TRUE 0 = FALSE NULL = Invalid input
IsPublished	The tables of the database can be published for snapshot or transactional replication, if replication is installed.	1 = TRUE 0 = FALSE NULL = Invalid input
IsQuotedIdentifiersEnabled	Double quotation marks can be used on identifiers.	1 = TRUE 0 = FALSE NULL = Invalid input
IsRecursiveTriggersEnabled	Recursive firing of triggers is enabled.	1 = TRUE 0 = FALSE NULL = Invalid input
IsSubscribed	Database can be subscribed for publication.	1 = TRUE 0 = FALSE NULL = Invalid input
IsTornPageDetectionEnabled	Microsoft® SQL Server™ detects incomplete I/O operations caused by power failures or other system outages.	1 = TRUE 0 = FALSE NULL = Invalid input
Recovery	Recovery model for the database.	FULL = full recovery model BULK_LOGGED = bulk logged model SIMPLE = simple recovery model
SQLSortOrder	SQL Server sort order ID supported in previous versions of SQL Server.	0 = Database is using Windows collation >0 = SQL Server sort order ID
Status	Database status.	ONLINE = database is available for query OFFLINE = database was explicitly taken offline RESTORING = database is being restored RECOVERING = database is recovering and not yet ready for queries SUSPECT = database cannot be recovered
Updateability	Indicates whether data can be modified.	READ_ONLY = data can be read but not modified READ_WRITE = data can be read and modified
UserAccess	Indicates which users can access the database.	SINGLE_USER = only onedb_owner, dbcreator, orsysadmin user at a time RESTRICTED_USER = only members of db_owner,dbcreator, and sysadminroles MULTI_USER = all users
Version	Internal version number of the Microsoft SQL Server code with which the database was created. For internal use only by SQL Server tools and in upgrade processing.	Version number = Database is open NULL = Database is closed

Return type of DATABASEPROPERTYEX function is a sql_variant.

PARTNER TIMEOUT

SQL Server uses PARTNER TIMEOUT to determine the maximum period of time an instance waits to get a "ping" message from another instance of SQL Server before determining whether a failover should occur.

To see the current setting, run this query:

   SELECT db_name(database_id) "database_name"
        , mirroring_connection_timeout
     FROM sys.database_mirroring
GO



To change the current setting, run this query:

ALTER DATABASE [databasename] SET PARTNER TIMEOUT 10
GO


lowest value: 5
default value: 10

To generate change sql:

   SELECT db_name(database_id)
        , mirroring_connection_timeout
        , 'alter database [' + db_name(database_id) 
        + '] set partner timeout 30'
     FROM sys.database_mirroring
    WHERE mirroring_role_desc = 'PRINCIPAL'

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