[264] An attempt was made to send an email when no email session has been established

1. In SQL Server Management Studio, right-click SQL Server Agent and then select "Properties."
2. Click "Alert System"
3. Click "Enable mail profile"
4. Click "OK"5. Restart "SQL Server Agent"

Now you can test your sql jobs to get a email notification.

SQL Server 2014 CTP1

SQL Server 2014 delivers mission critical performance across all workloads with in-memory built-in, faster insights from any data with familiar tools, and a platform for hybrid cloud

Microsoft SQL Server 2014 builds on the mission-critical capabilities delivered in the prior release by providing breakthrough performance, availability and manageability for your mission critical applications. SQL Server 2014 delivers new in-memory capabilities built into the core database for OLTP and data warehousing, which complement our existing in-memory data warehousing and BI capabilities for the most comprehensive in-memory database solution in the market.

 

SQL Server 2014 also provides new disaster recovery and backup solutions with Windows Azure, enabling customers to use their existing skills with the on-premises product offerings to take advantage of Microsoft’s global data centers. In addition, SQL Server 2014 takes advantage of new Windows Server 2012 and Windows Server 2012 R2 capabilities to give you unparalleled scalability for your database application in a physical or virtual environment.

 

Source : http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

TechEd 2013: Eron Kelly talks about SQL Server 2014

At TechEd 2013 in New Orleans, Megan Keller, SQL Server Pro Editorial Director, and I met with Eron Kelly, General Manager for SQL Server Marketing, to talk about the upcoming release of SQL Server 2014. The new SQL Server 2014 release will provide several significant new features. Related: Microsoft Announces SQL Server 2014

In-Memory OLTP Engine

The biggest feature in the upcoming SQL Server 2014 release is undoubtedly the In-Memory OLTP Engine (formerly code named Hekaton). The new In-Memory OLTP Engine will help you you to choose which tables go in memory. It will also help you to choose the stored procedure that will be compiled into machine code for high performance execution. EdgeNet, an early adopter, saw a 7X performance increase with no code changes.

New Azure Integration Options

There will also be new Azure integration options for backup and AlwaysOn Availability Groups. The new backup option is integrated into SQL Server Management Studio (SSMS) and it allows you to back up a SQL Server database to Azure. You can also use it to quickly restore to an Azure virtual machine (VM). AlwaysOn Availability Groups have also been extended to Azure, providing AlwaysOn in the cloud. This enables you to create asynchronous Availability Group replicas in Azure for disaster recovery. Like the new Azure backup, the ability to create Azure Availability Groups is integrated in SMSS.

Better Resource Management for Big Data

Another improvement that Kelly discussed with Megan and I is the ability to provide better resource management for big data. There's improved integration with Windows Server 2012’s storage enhancements. SQL Server 2014’s Resource Governor can take advantage of the automated storage-tiering provided by Windows Server 2012.

BI Improvements

Kelly also demonstrated some of the upcoming business intelligence (BI) improvements. Kelly illustrated how Data Explorer can provide new data visualizations, as well as how GEOFlow was able to provide a visual mapping of all the different TechEd attendees. He also pointed out that in the SQL Server 2014 release, PowerView will be able to work against multi-dimensional models in addition to tabular data models.

Significant Changes to Database Engine

For those of you following the SQL Server 2014 release cycle, you might note that the SQL Server 2014 release has skipped the traditional R2 release that Microsoft normally releases between major releases. Kelly explained that this was due to the significant changes that Microsoft needed to make to the database engine to support the new In-Memory OLTP Engine.

SQL Server 2014 Preview Expected in June 2013

Kelly told us that we could expect to see a preview of the SQL Server 2014 released this month, with general availability expected to be available in early 2014.

Source : http://sqlmag.com/blog/live-teched-2013-eron-kelly-talks-about-sql-server-2014

Find Backup history for one week

use msdb

go
SELECT
SERVERPROPERTY('Servername') AS Server_Name,
bs.Database_name,
bs.Backup_start_date,
bs.Backup_finish_date,
bs.Expiration_date,
CASE bs.type
WHEN 'D' THEN 'FULL'
WHEN 'I' THEN 'Differential '
WHEN 'L' THEN 'Log'
END AS Backup_type,
(bs.backup_size/1048576) AS Backup_size_MB ,bmf.Logical_device_name,
bmf.Physical_device_name FROM backupmediafamily AS bmf INNER JOIN backupset AS bs ON bmf.media_set_id = bs.media_set_id WHERE (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - 7)
ORDER BY bs.database_name,
bs.backup_finish_date
Reference :http://msdn.microsoft.com/en-us/library/ms186299.aspx

Operating system error 112(error not found) encountered

When you RESTORE database if error message says 112 then you must look at the first point to check enough disk free space on specific drive where database is going to restore.

Message

Error: 17053, Severity: 16, State: 1.

E:\MSSQL \DATA\Adventrueres.mdf: Operating system error 112(error not found) encountered.

Plan Caching and Reuse

Dynamic Management Views and functions are useful when exploring plan reuse and the following objects are most helpful:

sys.dm_exec_cached_plans

sys.dm_exec_query_plan

sys.dm_exec_sql_text

sys.dm_exec_plan_attributes

sys.dm_exec_cached_plan_dependent_object

The following sql query is useful to exploring the current plan cache contents in buffer pool:

 
SELECT DB_NAME( st.dbid) AS DatabaseName,
st.dbid AS Database_ID,
cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.text AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY 

 sys.dm_exec_query_plan(cp.plan_handle) AS qp

CROSS APPLY 

 sys.dm_exec_sql_text(cp.plan_handle) AS st

WHERE st.[dbid] = DB_ID()

ORDER BY UseCounts DESC;

Rebuilding all indexes on a table and specifying options

If you are planning rebuild index  to use this statement below , you must run update statistics statement after rebuild index. Statistics are always updated when you rebuild index.  But STATISTICS_NORECOMPUTE=ON disable the auto update statistics from updating the specific statistics for an index (or column-level statistics)

-- Try to avoid this options
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);GO

UPDATE STATISTICS (Production.Product)
GO

(OR)

Alternatively you can use below options

-- By default STATISTICS_NORECOMPUTE = OFF

USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = OFF);

--  For single index options
ALTER INDEX[IDX_INDEX_NAME] ON [dbo].[Product]   REBUILD WITH (STATISTICS_NORECOMPUTE=OFF)


Source: Microsoft needs to correct this page.
http://technet.microsoft.com/en-us/library/ms188388.aspx