In SQL Server vNext there is an indication that you will be able to pause index maintenance.
The view sys.index_resumable_operations verifies this, but at this time there does not appear to be any available documentation on how to use pausing.
Update: info is now available
sys.dm_os_host_info vs sys.dm_os_windows_info
Failed sysadmin Permission Elevation Attempts
Consider this scenario.
You have a user called Bob who thinks today is a good day to become a sysadmin.
He runs the following command to escalate his privileges:
ALTER SERVER ROLE [sysadmin] ADD MEMBER [bob];
Thankfully this fails and world domination will need to wait another day.
Now as a DBA you’ll be thinking, this activity will be all over the SQL ERRORLOG and/or Windows Event logs so you can have a chat with Bob about his intensions, just as you would with login failures.
Allais, there is nothing recorded in either of these.
Fortunately, the Default Trace does capture this event and you can have that chat with Bob after all.
SELECT StartTime, TextData, ApplicationName, LoginName ,CASE WHEN a.Success = 0 THEN 'No' ELSE 'Yes' END AS 'Was Successful' FROM master.sys.fn_trace_gettable( ( SELECT REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) + 'log.trc' FROM sys.traces WHERE is_default = 1 ), DEFAULT) a WHERE a.Success = 0
Cumulative Update 2 for SQL Server 2016 SP1 released
This CU has just been released and with well over 90 fixes it is definitely worth reviewing if you are using SQL Server 2016.
https://support.microsoft.com/en-us/help/4013106/cumulative-update-2-for-sql-server-2016-sp1
Brain teaser
0x57656C6C2D646F6E6520666F7220666967757
2696E67206F75742074686520736F6C7574696F
6E2E20546869732077617320746F2064656D6F6
E73747261746520746861742062696E61727920
6973206A757374206F6E65206F66206D616E792
07761797320746F2073746F726520646174612E
2054686520616E73776572206973203432203A29
SQL Server Backup Throughput Monitoring
xp_readerrorlog Msg 22004
SQL Server Product Key
Changes to SQL Server configuration options
Continuing on from the previous post on sys.databases, DBAs may/may not have noticed the adds/removes/tweaks occurring behind the scenes as each version changes.
A nice one of note is the allowing the backup checksums by default from SQL Server 2014.
Below is a breakdown of sys.configurations changes since SQL Server 2005.
SQL Server 2008
Setting | Change |
Web Assistant Procedures | Removed |
access check cache bucket count | Added |
access check cache quota | Added |
affinity64 mask | Added |
affinity64 I/O mask | Added |
backup compression default | Added |
EKM provider enabled | Added |
filestream access level | Added |
optimize for ad hoc workloads | Added |
max text repl size (B) | Maximum value changed from 0 to -1 |
min server memory (MB) | Default value changed from 8 to 16 |
SQL Server 2008 R2
Setting | Change |
max degree of parallelism | Maximum value changed from 64 to 1024 |
SQL Server 2012
Setting | Change |
awe enabled | Removed |
SQL Mail XPs | Removed |
contained database authentication | Added |
EKM provider enabled | Is dynamic changed from 0 to 1 |
max degree of parallelism | Maximum value changed from 1024 to 32767 |
max server memory (MB) | Minimum value changed from 16 to 128 |
max worker threads | Is dynamic changed from 0 to 1 |
max worker threads | Maximum value changed from 32767 to 65535 |
remote login timeout (s) | Default value changed from 20 to 10 |
SQL Server 2014
Setting | Change |
backup checksum default | Added |
SQL Server 2016
Setting | Change |
allow polybase export | Added |
automatic soft-NUMA disabled | Added |
external scripts enabled | Added |
hadoop connectivity | Added |
polybase network encryption | Added |
remote data archive | Added |
SQL Server vNext (CTP 1.3)
No changes at this time.
Continual Evolution of SQL Server sys.databases
With each version of SQL Server the list of new features grows and keeping pace with them as a DBA can be a challenge.
As an example, the addition of new columns in sys.databases since SQL Server 2005 has been slowly climbing:
SQL Server 2008/R2 |
is_cdc_enabled |
is_encrypted |
is_honor_broker_priority_on |
SQL Server 2012 |
replica_id |
group_database_id |
default_language_lcid |
default_language_name |
default_fulltext_language_lcid |
default_fulltext_language_name |
is_nested_triggers_on |
is_transform_noise_words_on |
two_digit_year_cutoff |
containment |
containment_desc |
target_recovery_time_in_seconds |
SQL Server 2014 |
is_auto_create_stats_incremental_on |
is_query_store_on |
resource_pool_id |
delayed_durability |
delayed_durability_desc |
is_memory_optimized_elevate_to_snapshot_on |
SQL Server 2016 |
is_federation_member |
is_remote_data_archive_enabled |
is_mixed_page_allocation_on |
SQL Server vNext (CTP 1.3) |
is_temporal_history_retention_enabled |