Tips for using tempdb database in SQL Server 2016 Use TempDB section of SQL Server Installation Wizard. SQL Server 2016 introduces the new UI input control on the Database Engine Configuration – TempDB section of SQL Server Installation Wizard. Now during setup, you can configure the number of tempdb database files, initial size, autogrowth and directory placement. You can specify […]
Author: Alexander Chigrik
Tips for using SQL Server 2016 triggers
Tips for using SQL Server 2016 triggers Try to minimize the number of rows affected in a trigger. The more number of rows affected in a trigger, the more time a trigger takes to run. So, try to reduce the number of rows affected in a trigger. Consider using triggers on memory-optimized tables. SQL Server 2016 introduces supporting triggers on […]
Three Undocumented SQL Server 2014 XML Stored Procedures
Three Undocumented SQL Server 2014 XML Stored Procedures SQL Server 2014 supports the following three undocumented XML system stored procedures: sp_syscollector_validate_xml sp_xml_schema_rowset sp_xml_schema_rowset2 sp_syscollector_validate_xml The sp_syscollector_validate_xml system stored procedure is used to validate the XML collector. Syntax sp_syscollector_validate_xml [ @collector_type_uid = ] ‘collector_type_uid’, [ @name = ] ‘name’, [ @parameters = ] ‘parameters’ Arguments [ @collector_type_uid = ] ‘collector_type_uid’ Is […]
Four Undocumented SQL Server 2014 Agent Procedures
Four Undocumented SQL Server 2014 Agent Procedures SQL Server 2014 supports the following useful undocumented SQL Server Agent stored procedures: sp_MSgetalertinfo sp_readerrorlog sp_sqlagent_is_srvrolemember sp_sqlagent_get_startup_info Two of these stored procedures exist in the master database (sp_MSgetalertinfo and sp_readerrorlog), other exist in the msdb database. sp_MSgetalertinfo The sp_MSgetalertinfo stored procedure is used to get information about alerts from Windows registry. Syntax sp_MSgetalertinfo […]
Five Undocumented SQL Server 2014 Job Stored Procedures
Five Undocumented SQL Server 2014 Job Stored Procedures SQL Server 2014 supports the following useful undocumented SQL Server job stored procedures: sp_MSget_jobstate sp_delete_all_msx_jobs sp_get_job_alerts sp_multi_server_job_summary sp_help_operator_jobs One of these stored procedures exist in the master database (sp_MSget_jobstate), other exist in the msdb database. sp_MSget_jobstate The sp_MSget_jobstate stored procedure returns the job state for the specified job. Syntax sp_MSget_jobstate [ @job_id […]
Five Undocumented SQL Server 2014 log shipping stored procedures
Five Undocumented SQL Server 2014 log shipping stored procedures In this article, I want to describe the following five undocumented log shipping stored procedures shipped with SQL Server 2014: sp_check_log_shipping_monitor_alert sp_create_log_shipping_monitor_account sp_delete_log_shipping_monitor_info sp_delete_log_shipping_monitor_jobs sp_log_shipping_get_date_from_file sp_check_log_shipping_monitor_alert The sp_check_log_shipping_monitor_alert log shipping stored procedure is used to check all monitor alerts in the primary database and in the secondary database in log shipping […]
Six Undocumented SQL Server 2014 Database Engine Stored Procedures
Six Undocumented SQL Server 2014 Database Engine Stored Procedures SQL Server 2014 supports the following useful undocumented database engine stored procedures: sp_bcp_dbcmptlevel sp_MSforeachdb sp_MSforeachtable sp_MSindexspace sp_MStablespace sp_objectfilegroup sp_bcp_dbcmptlevel The sp_bcp_dbcmptlevel database engine stored procedure is used to get the database compatibility level for the specified database. Syntax sp_bcp_dbcmptlevel [ @dbname = ] ‘dbname’ Arguments [ @dbname = ] ‘dbname’ The […]
Five Undocumented SQL Server 2014 OLEDB Stored Procedures
Five Undocumented SQL Server 2014 OLEDB Stored Procedures SQL Server 2014 supports the following useful undocumented oledb stored procedures: sp_oledb_database sp_oledb_defdb sp_oledb_deflang sp_oledb_language sp_oledb_ro_usrname sp_oledb_database The sp_oledb_database stored procedure returns the oledb database name. Syntax sp_oledb_database Return Code Values None. Result Sets Column name Type Description name sysname The name of the oledb database Remarks This stored procedure exists in […]
Five Undocumented SQL Server 2014 Database Mail Procedures
Five Undocumented SQL Server 2014 Database Mail Procedures SQL Server 2014 supports the following useful undocumented database mail stored procedures: sp_RunMailQuery sp_SendMailMessage sp_sysmail_activate sysmail_verify_account_sp sysmail_verify_profile_sp Note. These stored procedures exist in the msdb database, not in master. sp_RunMailQuery The sp_RunMailQuery database mail stored procedure is used to run the mail query. This stored procedure is used in the sp_send_dbmail stored […]
Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures
Nine Undocumented SQL Server 2014 Database Maintenance Plan Stored Procedures SQL Server 2014 supports the following useful undocumented database maintenance plan stored procedures: sp_clear_dbmaintplan_by_db sp_maintplan_close_logentry sp_maintplan_delete_log sp_maintplan_delete_plan sp_maintplan_delete_subplan sp_maintplan_open_logentry sp_maintplan_start sp_maintplan_subplans_by_job sp_maintplan_update_subplan_tsx Note. These stored procedures exist in the msdb database, not in master. sp_clear_dbmaintplan_by_db The sp_clear_dbmaintplan_by_db database maintenance plan stored procedure is used to delete the maintenance plans, the […]