Forcing garbage collection with in memory oltpPosted: September 27, 2015 Filed under: Microsoft SQL Server, Microsoft SQL Server 2014, Microsoft SQL Server 2016 | Tags: Microsoft SQL Server, Microsoft SQL Server 2014, Microsoft SQL Server 2016 Leave a comment
To force manual garbage collection:
- First we check the status of the checkpoint files:
select container_id,state_desc,lower_bound_tsn,upper_bound_tsn from sys.dm_db_xtp_checkpoint_files
Anything that is MERGE TARGET is pending a merge.
In SQL Server 2014 we can run:
sys.sp_xtp_merge_checkpoint_files database_name, @transaction_lower_bound, @transaction_upper_bound
to force a merge, manual merges are not possible in SQL Server 2016!
2. Once the merge is complete we have to run a checkpoint to ensure everything is persisted on disk.
3. Next we need to run a log backup otherwise dm_db_xtp_checkpoint_files.state_desc would be ‘WAITING FOR LOG TRUNCATION’
4. Finally we can force garbage collection using
sys.sp_xtp_checkpoint_force_garbage_collection [[ @dbname=database_name]
SQL Server 2014 SP1Posted: May 19, 2015 Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: Microsoft SQL Server, Microsoft SQL Server 2014 Leave a comment
First off the SQL Server 2014 SP1 installer complains that fdhost.exe is running…
http://www.smooth1.co.uk/installs/dbinstalls.html#3.1.3 – Updated to also mention 2014 SP1!
Cumulative Update 7 for SQL Server 2014Posted: April 24, 2015 Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: Microsoft SQL Server, Microsoft SQL Server 2014 Leave a comment
Cumulative Update 7 for SQL Server 2014
Interesting fixes include:
FIX: A query that requires nested loops join takes longer to complete in SQL Server 2014
An AlwaysOn secondary replica crashes or raises error 3961 when the AlwaysOn database has CLR UDT in SQL Server 2014
FIX: Access violation and “No exceptions should be raised by this code” error occur when you use SQL Server 2012 or SQL Server 2014
FIX: MDX query returns incorrect results when you run it with subselect after an UPDATE CUBE statement in SSAS 2012 or SSAS 2014
FIX: Performance issue occurs when you use sqlncli11.dll ODBC driver to access SQL Server in an application
FIX: Data-driven subscription with email notification shows “0 errors” when email delivery fails in SSRS 2012 or SQL Server 2014
FIX: “Access Denied” error occurs when you run an XML validation task in SSIS 2012 or SSIS 2012
FIX: Improved memory management for columnstore indexes to deliver better query performance in SQL Server 2014
FIX: SQL Server takes long time to open database after recovery to add or remove a data file in SQL Server 2014
FIX: A query that uses the DECRYPTBYKEY function takes longer to be completed in SQL Server 2014
FIX: A SELECT query run as a parallel batch-mode scan may cause a deadlock situation in SQL Server 2014
FIX: “Cannot bulk load” error when you run a query that contains INSERT statements when trace flags 4199 and 610 are enabled on a server that is running SQL Server 2014
FIX: You cannot use the Transport Layer Security protocol version 1.2 to connect to a server that is running SQL Server 2014
FIX: Incorrect result when the “must be unique” action is evaluated for an attribute by using SQL Server 2014 Master Data Services
FIX: Table values change after dividing and partitioning the table in SQL Server Analysis Services
FIX: “8156: A database error has occurred” when you run a business rule in SQL Server 2014 Master Data Services
FIX: Error 3624 occurs when you execute a query that contains multiple bulk insert statements in SQL Server 2014
SQL Server 2014 Service Pack 1 release informationPosted: April 15, 2015 Filed under: Microsoft SQL Server, Microsoft SQL Server 2014 | Tags: Microsoft SQL Server, Microsoft SQL Server 2014 Leave a comment
SQL Server 2014 Service Pack 1 release information
FIX: Access violation occurs on the I/O Completion routine when you enable AutoClose in SQL Server 2014
FIX: Rollback causes database to go to suspect mode in SQL Server 2012 or SQL Server 2014
FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014
SSAS 2012 logging feature improvement in SQL Server 2012 Service Pack 2 and SQL Server 2014 Service Pack 1- displays machine information and product version information on startup
New DMF sys.dm_fts_index_keywords_position_by_document in SQL Server 2012 SP2 and SQL Server 2014 SP1
FIX: “Database ‘model’ cannot be opened” error when you restart SQL Server after tail-log backup for model database
FIX: Can’t connect to the server when you run a cube creation MDX query in SSAS 2012 or SSAS 2014
FIX: SQL Server crashes when the log file of tempdb database is full in SQL Server 2012 or SQL Server 2014
FIX: Rollback recovery on a snapshot fails when you run DBCC CHECKDB and then SQL Server shuts down unexpectedly
FIX: Incorrect data returned when you query the DATE column in SQL Server 2014
FIX: Non-yielding error occurs when you execute DML statements in SQL Server 2014
Error 17066 or 17310 during SQL Server startup
FIX: Non-yielding scheduler issue when you write pages to buffer pool extension file in SQL Server 2014
FIX: OS error 665 when you execute DBCC CHECKDB command for database that contains columnstore index in SQL Server 2014
FIX: Duplicate sequence value is generated when you run sp_sequence_get_range in parallel with NEXT VALUE FOR function
FIX: SOS_CACHESTORE spinlock contention on system table rowset cache causes high CPU usage in SQL Server 2012 or 2014
FIX: AlwaysOn availability groups are reported as NOT SYNCHRONIZING
FIX: Error 8646 when you run DML statements on a table with clustered columnstore index in SQL Server 2014
FIX: Issues when you enable the buffer pool extension feature in SQL Server 2014
FIX: Query performance issues when new cardinality estimator is enabled in SQL Server 2014
FIX: DBCC CHECKDB and DBCC CHECKTABLE take longer to run when SQL CLR UDTs are involved in SQL Server 2012 or SQL Server 2014
Also the following VSTS bug numbers are fixed:
1957464 RESTORE HEADERONLY for an Encrypted Backup file of the Database does not show whether the backup is encrypted or not. After you apply SP1, the output of RESTORE HEADONLY will include three additional columns: KeyAlgorithm, EncryptorThumbprint and EncryptorType that can give additional details about the encrypted backup.
2580641 Invalid query syntax in stored procedure when you drop an article that is published in
more than one publication.
2580651Peer-to-peer (P2P) conflict messages include Table Name, Primary Key (s), Current Version, Pre Version and Post Version.
2580686 Update-Update conflicts in P2P replication when updates are made to text column by using UPDATE or WRITE.
2580693 Incorrect results when you work with block mode unary operators.
2580746 DPM’s full backup on the server with AlwaysOn secondary will be converted to copy_only backup
2581191 Merge Replication logging improvement:
2581192 Transaction Replication logging improvement:
2581382 Provides platform information in the log of SSRS 2014
3143194 LOB reads are shown as zero when “SET STATISTICS IO” is on during executing a query with clustered columnstore index.
3731350 When you use features that rely on log pool cache (for example, Always On) on systems with multiple sockets, you may notice high values for “log write waits” counter. Traceflag T9024 is not longer needed to activate the fix.
3732057 When your instance of SQL Server is handling thousands of connection resets because of connection pooling, performance problems occur when database lock activity increases in SQL Server. Traceflag T1236 is not longer need to activate the fix
3909490 Error 4819 occurs for some combination of database schema and data when the fast load context (TF610) for bulk load and TF4199 are enabled.
There are also some community connect items fixed, many ssms related.
Database product installation FAQPosted: August 20, 2014 Filed under: DB2 10, DB2 LUW, Informix 12, Microsoft SQL Server, Microsoft SQL Server 2014, Oracle 12c, SAP Sybase ASE | Tags: DB2 10.5, DB2 LUW, Informix, Informix 12, Microsoft SQL Server, Microsoft SQL Server 2014, Oracle 12c, SAP Sybase ASE Leave a comment
Database product installation FAQ http://www.smooth1.co.uk/installs.html
Currently SQL Server,DB2 10.5,Oracle 188.8.131.52,SAP ASE 16.0,Informix 12.10.FC4