The Young DBA

In-Memory OLTP  In-Memory OLTP is an in-memory computing technology developed by Microsoft to speedup the performance of transaction processing applications running on SQL Server databases. In-Memory OLTP is built with two core components: memory-optimized tables and natively compiled stored procedures. In-Memory OLTP, also known as 'Hekaton' and 'In-Memory Optimization', which is Microsoft's latest in-memory processing technology of 2014. In-Memory Tables It is integrated into SQL Server's Database Engine and which can be used in the similar way as other Database Engine component. In-Memory OLTP originally came with SQL Server 2014 . Memory-Optimized Tables Memory-optimized tables are fully durable, like transactions on disk-based tables, transactions on memory-optimized tables are fully ACID compatible . Memory-optimized tables and natively compiled stored procedures support only a subset of Transact-SQL features. The primary storage for memory-o

What is DBCC Clone Database in SQL 2016 ? During Database development many times we required to create exact copy of database without data. This command is used to creating a database clone is fast and easy way for such kind of requirements.  The main important feature and benefits of clone database is it copy entire objects, metadata and statistics from the specified source database without any data. Here is the command to create clone. DBCC CLONEDATABASE (YoungDBA2016, YoungDBA2016_Clone) What are the operation being process when we start cloning database ? When we configure dbcc clone database it does following operations: It Creates a new destination database that uses the same file layout as the source. Then an internal snapshot of the source database. After that it copies the system metadata from the source to the destination database. Then it copies all schema for all objects. Then it copies statistics for all indexes. Does it requires any Services packs for

Sometime before I faced different kind of issue on one of staging SQL Server. I required to change the TCP port of an instance where multiple instance were running, I just open compmgmt.msc and connected SQLServer Configuration Manager , then I started receiving error : “ Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010], SQL Server Cannot Connect to WMI Provider permissions or server is unreachable. ” This may caused by an issue with the compiled SQL Configuration Manager GUI’s communication with WMI. If we have multiple instance running on SQL Server on a single windows server or we have un-installed any instances then this kind of issues we may face to connect.

I was trying to get last server start time through DMV sys . dm_os_sys_info on all sql servers instance where groups was 2005+2008+2008r2+2012+2014. I got the result quickly excluded from one sql server 2005 instance it populated error.. Msg 207, Level 16, State 1, Line 1 Invalid column name 'sqlserver_start_time' . I think Microsoft was forgot to add this column in 2005. I managed it through. SELECT crdate FROM sysdatabases WHERE [name] = 'tempdb'. and xp_readerrorlog  Apart from this SYSDATETIME () was also not present in 2005 but now this is available …