SQL Server 2016 release date is June 1


Microsoft SQL team announced today morning at 9:00 am EST that the the release date for SQL Server 2016 is June 1st.

Important points to note here is the following features are Enterprise only.

(1) Enterprise data management (Master Data Services, Data Quality Services)
(2) Advanced OLTP (In-memory OLTP, Operational analytics)
(3) Advanced HA (Always On – multi-node, multi-db failover, readable secondaries)
(4) Advanced security (Transparent Data Encryption, Always Encrypted)
(5) Advanced data integration (Fuzzy grouping and look ups, change data capture)
(6) Data warehousing (In-Memory ColumnStore, Partitioning)
(7) Mobile BI (Datazen)
(8) Advanced Corporate Business Intelligence (Advanced tabular model, Direct query,
in-memory analytics, advanced data mining)
(9) Advanced “R” integration (Full parallelism for RRE)

Reference : https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/

 

Posted in Uncategorized | Leave a comment

When was the last time DBCC finished successfully


/*
When was the last time DBCC finished successfully?
DBCC CHECKDB checks databases for corruption. You won’t know
Script is from http://sqlserverpedia.com/wiki/Last_clean_DBCC_CHECKDB_date
To get sample corrupt databases – http://sqlskills.com/pastConferences.asp

*/

CREATE TABLE #temp (
ParentObject VARCHAR(255)
, [Object] VARCHAR(255)
, Field VARCHAR(255)
, [Value] VARCHAR(255)
)

CREATE TABLE #DBCCResults (
ServerName VARCHAR(255)
, DBName VARCHAR(255)
, LastCleanDBCCDate DATETIME
)

EXEC master.dbo.sp_MSforeachdb
@command1 = ‘USE [?] INSERT INTO #temp EXECUTE (”DBCC DBINFO WITH TABLERESULTS”)’
, @command2 = ‘INSERT INTO #DBCCResults SELECT @@SERVERNAME, ”?”, Value FROM #temp WHERE Field = ”dbi_dbccLastKnownGood”’
, @command3 = ‘TRUNCATE TABLE #temp’

–Delete duplicates due to a bug in SQL Server 2008

;WITH DBCC_CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DBName, LastCleanDBCCDate ORDER BY LastCleanDBCCDate) RowID
FROM #DBCCResults
)
DELETE FROM DBCC_CTE WHERE RowID > 1;

SELECT
ServerName
, DBName
, CASE LastCleanDBCCDate
WHEN ‘1900-01-01 00:00:00.000’ THEN ‘Never ran DBCC CHECKDB’
ELSE CAST(LastCleanDBCCDate AS VARCHAR) END AS LastCleanDBCCDate
FROM #DBCCResults
ORDER BY 3

DROP TABLE #temp, #DBCCResults;

Posted in Uncategorized | Leave a comment

Largest SQL Server and SQL Azure projects in the world


How do the SQL Server scale; do they scale up or scale out? How are High Availabilty and Disaster Recovery architected? Are there any common techniques that bring the largest gain?

Largest on premises SQL Server and SQL Azure projects in the world

Reference :http://channel9.msdn.com/even…/TechEd/NewZealand/2013/DBI309

Largest On AZURE SQL Server Largest On Premises SQL Serverjpg

Posted in Uncategorized | Leave a comment

Query time out


Below are the questions to ask and work on when we have Query time out issues

what kind of crime is this ?

Query time out is client side time out.

Is it my SQL server setting

EXEC SP_CONFIGURE ‘query timeout’

Time out

Note : Default is 60 secs in .net code

Questions to Ask

People who managing the system engineers, any middle tier.

users- super users.

Initial Questions for users

(1) Are the timeouts consistent, or random ?
(2) Are the timeouts for all users, or some users ?
(3) if some which users ?
(4) Is there a pattern of activity around when the timeout happens ?
(5) can someone reproduce this right now ? yes or no
(6) How critical is this issue ? -Red flag. how many users are getting effected.

Time out Questions

(1) Where is the timeout set ?
(2) What is the current value of the timeout ? ————seconds
(3) Has the timeout changed recently ?
(4) can the application run in a mode where detailed error information goes to the log ? YES OR NO
(5) Under which host name run this query ?
(6) Under which login is used ?
(7) which application name is used on the connection ?

Database related Questions

(1) Under which database(s) does this query use ?—
(2) Is this query part of a distributed transaction ? YES OR NO
(3) If this a stored procedure or if the materialized view , What are sample runtime parameters ?
(4) Does the query is text/stored procequery is procedure call ?
Profier to fix the timeouts

(1) SQL: Batchstarting
(2) SQL: Statstarting
(3) SQL: StmptCompleted
(4) SQL: Batchcompleted

Columns : Duration , SPID, Database ID, DatabaseName , LoginName

who else was the crime scene ?

May be due to contentions issues.

May be jobs are running ? check the acitve SpID’s and check blocking.

Reference : http://www.brentozar.com/archive/2011/07/mystery-of-query-timeouts-video/

Posted in Uncategorized | Leave a comment

Replication-Distribution Agent is already running


Very rarely should you acquire the following error, which can be viewed when looking at the job activity monitor failure history:

“Agent message code 21036. Another distribution agent for the subscription(s) is running or the server is working on a previous request by the same agent. “

In order to identify this error message maybe occurring is by trying to start agents that are offline and they fail right away.  At this point you will need to look at the Job Activity Monitor and review the error history for one of the failed agents.   This is where the error message will be displayed.  The error message is indicating that the agent executable, dist.exe, is currently running on the server even though SQL Server is displaying the job is stopped.

To verify if the agent is running run the following select statement on distributor

SELECT    spid

, program_name

, loginame

FROM   Master..sysprocesses

Within the results of the query you will be able to see the name of the distribution agent under the column ‘program_name’.   If you agent is listed than that means it is still running on the server and the process will need to be killed.  At this point you can issue a ‘Kill Spid’ statement on the distributor for that agent.  Once the process has been killed go back to the Job Activity Monitor and try starting the agent again.

Posted in Replication | Leave a comment

Replication : Can’t find stored procedure


Occasionally, an error will display such as one of the following:

“Could not find stored procedure ‘sp_MSdel_ARTICLE_msrepl_ccsr’“

“Could not find stored procedure ‘sp_MSins_ARTICLE_msrepl_ccsl’“

“Could not find stored procedure ‘sp_MSupd_ARTICLE_msrepl_ccsl’“

It is unclear how the stored procedure gets dropped from the subscriber.  This typically means the normal initialization process did occur.  Normal initialization not only propagates the schema, but replication stored procedures are also created at the subscriber.  In this case, they are missing.

To correct the error message run the stored procedure Usp_CreateReplicationStoredProcsForGivenPublication at the publisher.  This will create (in text format) 3 stored procedure creation scripts. Execute these scripts on the subscriber and then execute the distribution agent to remove the error and propagate the insert normally.

Posted in Uncategorized | Leave a comment

Backup Duration FInd


The following query gives the recent backup duration by user, Database name, server name, Backup started , Backup Finished and Total Time.

DECLARE @dbname sysname
SET @dbname = NULL –set this to be whatever dbname you want
SELECT bup.user_name AS [User],
bup.database_name AS [Database],
bup.server_name AS [Server],
bup.backup_start_date AS [Backup Started],
bup.backup_finish_date AS [Backup Finished]
,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ‘ hours, ‘
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ‘ minutes, ‘
+ CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ‘ seconds’
AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
(SELECT MAX(backup_set_id) FROM msdb.dbo.backupset
WHERE database_name = ISNULL(@dbname, database_name) –if no dbname, then return all
AND type = ‘D’ –only interested in the time of last full backup
GROUP BY database_name)
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

Posted in Uncategorized | Leave a comment