If have a SQL Server database and change the Recovery Model from Full to Bulk Logged whilst a transaction is open what happens?
If the ongoing transaction which was started under Recovery Model Full does an operation which can be minimally logged what happens?
Does the operation become minimally logged which then means the VLF is tagged as a minimally logged logfile which then does not allow certain operations e.g. restores from the next log backup with STOPAT.
We test with STOPAT and also use fn_dump_dblog to see exactly what ends up in the log backup and how we can identify minimally logged operations in a log backup file.
SQL Server 2016 – Database Scoped Configuration Parameters and Always On Availability Group failovers.Posted: May 10, 2017
In SQL Server 2016 we have database scoped parameters.
With an AlwaysOn Availability Groups we can have different database scoped parameter values on the primary compare to the secondaries.
How does this work with an Always On Availability Group failover ?
SQL Server 2016, upgrading to compatability level 130,Trace flag 139 and additional one-off dbcc checks.Posted: February 27, 2017
When upgrading to SQL Server 2016 RTM CU3/SP1 and upgrading to database compatablity leve 130 there are additional DBCC checks which should be performed.
These are hidden behind Trace flag 139 which should be temporarily enabled as part of the process of changing database compability level to 130.
- Enable trace flag 139 by running DBCC TRACEON(139, -1).
- Run DBCC CHECKDB/TABLE..WITH EXTENDED_LOGICAL_CHECKS to validate persisted structures
- Run DBCC CHECKCONSTRAINTS commands (if rows are affected the associated where clause to identify the row will be returned).
- Disable trace flag 139 by running DBCC TRACEOFF(139, -1)
- Change the database compatibility level to 130.
- REBUILD any structures that you identified in step 1.
There are impovements to expression evaluation in database level 130 and this affects persisted structures
- Check constraints
- Persisted Computed columns
- Indexes using computing columns whether as part of the key or as included columns
- Filtered indexes
- Indexed views
Upgrade to compatability level 130 BEFORE attempting to fix issues so the new expression evaluation logic is used for the fixed.
- Check constraints – change data or drop/recreate constraint with new expression
- Persisted Computed columns – Update a column referenced by the computed column to the same value to force recalcuation of the computed column
- Index/filtered index/indexed views – Either A) Put db in single user mode and run DBCC CHECKTABLE with REPAIR_REBUILD B) ALTER INDEX…REBUILD and if supported in your edition of sql server consider adding the WITH (ONLINE=ON) clause.
NOTE: There are some queries in the Appendix C/D of the article above which can be used to help identify affected objects.
To run SSMS remotely:
runas /netonly /user:domain\username “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”
runas /netonly /user:WIN-P9OQ9GF24HV\justdave “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\ManagementStudio\Ssms.exe”
MIcrosoft Certification second shot is back for 2013 – http://www.microsoft.com/learning/en-us/second-shot.aspx
So we are hearing that Microsoft has cancelled the Master and Architect levels of certification! http://blogs.technet.com/b/neiljohn/archive/2013/08/31/retiring-the-microsoft-master-certifications-and-training.aspx
Whilst the wikipedia site does indicate the MCM is going – http://blogs.technet.com/b/neiljohn/archive/2013/08/31/retiring-the-microsoft-master-certifications-and-training.aspx
with technet also going this seems to indicate the Microsoft want to run everything themselves..