Category: SQL Server Administration

Database Mirror TimeOut

Here is SQL Server Error Log Message I detected on one of SQL Server 2008R2

“The mirroring connection to “TCP://server_name:5022″ has timed out for database “<dbname> ” after 10 seconds without a response. Check the service and network connections.”

 

 

 

database mirroring timeout message

 

The message is related to Database Mirroring failover procedure.

With the High Availability mode of synchronous Database mirroring with automatic failover there are three servers that are involved: Principal, Mirror and Witness.

The three servers constantly ping each other, forming a quorum, and if one of the machines is not available the other machines determine how to handle the failover.

In this particular instance the Principal instance didn’t not receive any responses to “Ping” for 10 seconds from Witness, as a result the failover was initiated after 10 sec without a response. 10 sec is a default Partner Time out settings.

Most common reasons why the pinging response failed

  • slow network connection or network reliability
  • geographically dispersed mirror partners
  • hardware/SAN/Vm failure

What can be done to avoid accidental database failover?

Increase Partner Time Out value.

 

What is Partner timeout?

  • The maximum period of time a server instance waits to get a “ping” message from another SQL Server instance in the mirroring session before considering that other instance to be disconnected
  • The TIMEOUT option only can be defined on the principal server
  • Specifies the time-out period in seconds
  • Option can not be set through the GUI
  • The default time period is 10 seconds.
  • If you set it for anything between 0 and 4 seconds it will automatically set the timeout to 5 seconds.  If you set it for 5 seconds or greater it will be set to that value.
  • Number specified must be an integer

 

To change Partner Timeout to 20 sec

ALTER DATABASE <dbname> SET PARTNER TIMEOUT 20

 

Other Useful queries while troubleshooting Database Mirroring

Checking Timeout value

SELECT Mirroring_Connection_Timeout

FROM sys.database_mirroring

WHERE database_id = db_id(‘<dbname> ‘)

 

Query to check the associated ports with DB Mirroring

SELECT type_desc, port FROM sys.tcp_endpoints where type_desc like ‘%MIRROR%’;

 

Query to check the state of the DB Mirroring

SELECT state_desc FROM sys.database_mirroring_endpoints

Type In-row data is incorrect. Run DBCC UPDATEUSAGE.

The Issue:

Integrity Check (DBCC CHECKDB) produces the error

The In-row data USED page count for object “my_table”, index ID 9, partition ID 2609137898356736, alloc unit ID 2609137898356736 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

CHECKDB found 0 allocation errors and 2 consistency errors in table ‘my_table’ (object ID 1157579162).

CHECKDB found 0 allocation errors and 2 consistency errors in database ‘my_database’.
Outcome: Failed

BACKGROUND:

Database was just moved/upgraded from SQL Server 2000. SQL Server 2000 used to update the page space used metadata. After SQL Server 2005 the part of the migration/upgrade plan was to run DBCC UPDATEUSAGE. If you didn’t the error will appear when you run DBCC CHECKDB.

The output message above tell us that there is 1 consistency error in the database “my_database” and is a logical inconsistency. The error indicates that there are pages and row counts inaccuracies for that table and this type of errors can be fixed by running DBCC UPDATEUSAGE command.

HOW TO FIX:

Execute the DBCC UPDATEUSAGE(my_database) command in the context of your database you will see the following message indicating that were fixed the inaccuracies.

DBCC UPDATEUSAGE: Usage counts updated for table ‘my_table’ (index ‘my_table’ , partition 1):        RSVD pages (In-row Data): changed from … to … pages.DBCC execution completed. If DBCC printed error messages, contact your system administrator

 

After the page counts have been corrected your SQL Server will be back to normal. You can run DBCC CHECKDB after and it should not return any error messages.

 

Querying Linked Server with OPENQUERY

If you try to invoke a user-defined function (UDF) through a linked server in SQL Server by using a “four-part naming” convention (SQLserver.SQLdatabase.dbo.SQLfunction), you may receive error message.

 

Linked Server Error

The reason is User-defined function calls inside a four-part linked server query are not supported in SQL Server. That’s why error message indicates that the syntax of a Transact-SQL statement is incorrect.  

To work around this problem, use the OPENQUERY function instead of the four-part naming convention.

What is OPENQUERY?

OPENQUERY executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.

Principles

OPENQUERY ( linked_server ,'query' )

Arguments

linked_server: the name of the linked server.

 query ‘: string executed in the linked server. The maximum length of the string is 8 KB.

Note

OPENQUERY does not accept variables for its arguments.

OPENQUERY cannot be used to execute extended stored procedures on a linked server. However, an extended stored procedure can be executed on a linked server by using a four-part name

Permissions

Any user can execute OPENQUERY. The permissions that are used to connect to the remote server are obtained from the settings defined for the linked server.

Usage

    • pass-through UPDATE query against the linked server
UPDATE OPENQUERY (Link_Server, 'SELECT address FROM person.addresses WHERE id = 101') SET city = 'Melbourne';
    • pass-through INSERT query against the linked server 
INSERT OPENQUERY (Link_Server, 'SELECT address FROM person.addresses ') VALUES ('NewAddress');
    • pass-through DELETE query 
DELETE OPENQUERY (Link_Server, 'SELECT address FROM person.addresses WHERE address = ''NewAddress''');
    • SELECT pass-through query
SELECT * FROM OPENQUERY (Link_Server, 'SELECT SQLdatabase.dbo.SQLfunction(functionParam1, functionParam2)');



Microsoft’s SQL Server 2014 new features

HotLap Melbourne was a great opportunity to find out what’s new in the next release of SQL Server, SQL Server 2014. It was nicely presented by Peter Ward from www.wardyit.com. 2 major topics were covered Database Engine Improvements & Power BI.

 

Key notes summary

SQL Server 2014 Benefits

Achieved through

SQL Server 2014 Features

Mission Critical Performance (getting high reliability as well as being fast
  • In-Memory Built-In for OLTP, DW, In memory analytics
  • Enhanced Security & Scalability
  • High Availability using AlwaysOn
  • Live support for mission critical solutions
  • Memory Optimised tables & Indexes
  • Compiling TSQL to dll
  • ColumnStore Indexes Clustered and Updateable
  • Buffer poll extension using SSD drives
  • Managing locks priority for online operations when rebuild index or switching table partitions
  • Enhancements to AlwaysOn, readable secondary replicas (up to 8)
  • New security roles for non sysadmin DBA
  • I/O Resource Governor
Platform for Hybrid Cloud (reduced cost per compute)

 

  • Hybrid Cloud Solutions
  • On-ramp to cloud
  • Complete & consistent platform
  • Cloud backup manual/auto with point in time restore
  • Cloud DR easy deploy and recovery
  • Cloud bursting – Ease to migrate and run SQL server in the cloud
  • Migration Wizards to Windows Azure VM
Faster Insight from Any Data (consume it from anywhere)

 

  • Easy data access
  • Familiar tools
  • Complete BI platform
  • Ability to combine external/internal/unstructured data
  • Excel/Office 365 enhancements
  • BI models spanning cloud/on-premises

 

Power BI explained

 

SQL Server Power BI

 

SQL Saturday Melbourne

April-5th saw the first SQL Saturday in Melbourne. With more than 300 people registered and even more attended, international and local speakers and exhibitors it was really good staged and organised event for SQL Server community in Melbourne.

Here is the schedule of the event. There was a good mix of topics for all skill levels.

20 sessions on SQL Server 2014, BI, Data Warehousing, DBA, Database/Application Development and Database Administration.

 

Start Time

Keynote & Sponsor Sessions – Room: H.116

Track 1 – Room: H.125

Track 2 – Room: H.126

Track 3 – Room: H.235

Track 4 – Room: H.237

Track 5 – Room: H.238

08:45 AM Mike Ward

Keynote –

Level: Non-Technical

         
10:00 AM   Darren Gosbell

Drop your DAX

Level: Intermediate

Peter Myers

Introduction to SQL Server Data Mining

Level: Intermediate

Grant Paisley

Kick-ass Ad-hoc Cube Browser build in SSRS 2014

Level: Intermediate

Peter Ward

Hybrid Cloud Scenarios with SQL Server

Level: Intermediate

Shehap El-Nagar

T-SQL Performance Guidelines for better DB stress powers

Level: Advanced

11:30 AM   Henry Rooney

I have the data now what!

Level: Beginner

Julie Koesmarno

Building Your Myth Busting Lab With Power BI

Level: Beginner

Mohamed Kabiruddin

Agile BI with SQL Server 2014 and TFS 2013

Level: Intermediate

Matthew Winter

Introduction to SQL Server 2012 Parallel Data Warehouse

Level: Beginner

Greg Low

Why compression matters so much for SQL Server and BI

Level: Intermediate

01:15 PM   SQL Server MVPs

MVP Round Table Discussion

Level: Beginner

Peter Avenant

VARIGENCE AUSTRALIA – Build an Integrated Metadata Framework

Level: Beginner

     
02:15 PM   Paul te Braak

Analytical Data Mining – Using Data Mining to Understand Data

Level: Intermediate

Rod Colledge

Hour of Power BI

Level: Beginner

Warwick Rudd

Performance Eye for the SQL Guy

Level: Beginner

Konstantin Khomyakov

Data Quality Services (Deep dive)

Level: Intermediate

Rob Farley

The Incredible Shrinking Execution Plan

Level: Intermediate

03:45 PM   Rolf Tesmer

Leveraging SQL Spatial Analytics for Making Business Decisions

Level: Intermediate

Craig Bryden

Introducing Microsoft Power Query

Level: Intermediate

Victor Isakov

Understanding SQL Server 2014’s In-Memory OLTP Table Technology

Level: Intermediate

Martin Cairney

Automating Deployments with Powershell

Level: Advanced

Martin Catherall

Event notifications for the proactive DBA

Level: Intermediate

 

 

SSIS “Multiple-step OLE DB operation generated errors”

Product: SQL Server Integration Services SSIS

Package description: Processing text files by importing them into database. The final step in the process was to execute stored procedure to set various flags in the database for all file names processed.

 

SSIS control flow extract

 

I used “for each loop container” with ADO enumerator and Execute SQL task with OLE DB type connection.

For each loop container with ADO enumerator

 

SQL statement – direct input, stored procedure with 1 parameter where parameter mapped to the proper variable name & data type.

Problem:

Error: [Execute SQL Task] Error: Executing the query “EXEC dbo.StoredProcedire ?” failed with the following error: “Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.” Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

 

Possible Solution

In order for SSIS to properly map the query parameter to the variable, it needs the correct naming convention.  In my case, I’m using an OLEDB connection type so the first Parameter Name in the list it’s looking for is a 0

My confusion came that the parameter I used in SQL Task was assigned in For each loop container and it had index of 1.

So the set up should be like below to avoid any errors

 

Execute SQL Task parameter mapping

For Each Loop Variable Mappings

 

Here is the link from Microsoft technet site: http://technet.microsoft.com/en-us/library/ms140355.aspx

The parameter markers that you use in parameterized SQL statements depend on the connection type that the Execute SQL task uses.

Connection type Parameter marker
ADO ?
ADO.NET and SQLMOBILE @<parameter name>
ODBC ?
EXCEL and OLE DB ?

Parameter names that you use depend on the connection type that the Execute SQL task uses.

Connection type Parameter name
ADO Param1, Param2, …
ADO.NET and SQLMOBILE @<parameter name>
ODBC 1, 2, 3, …
EXCEL and OLE DB 0, 1, 2, 3, …

 

Other reasons this particular error could come up:

  1. Parameter data type is set incorrectly comparing with the datatypes of the parameters in the store procedure in the database
  2. Number of ? passed in the store procedure in Execute SQL Task don’t match with the number of parameters in the store procedure in the database
  3. Number of query parameters don’t match the number of variables mapped
  4. Order of parameters don’t match order of the variables

Database integrity check

What is database integrity check?

In general the database integrity task includes checking the allocation and structural integrity of all the objects in the specified database. It can contain checking the database indexes.

It’s a good practice to run database integrity check on a set schedule, however there are some occasions when you will need to run check in some other times eg after hardware failure, electrical surge, the server unexpected shut down.

SQL Server provides number of commands to check the logical and physical integrity of all the objects in the specified database:

 

Command

Description

Command Level

Usage

DBCC CHECKDB Check for consistency errors, validates the integrity of everything in a database Database level Run every day during low usage times
DBCC CHECKALLOC Checks the consistency of disk space allocation structures for a specified database Database level DBCC CHECKDB is a superset of DBCC CHECKALLOC (includes allocation checks in addition to checks of index structure and data integrity), DBCC CHECKTABLE (for all tables in the database) and DBCC CHECKCATALOG.

 

DBCC CHECKTABLE Checks the integrity of all the pages and structures that make up the table or indexed view. Including pages linkage, sort order, pointers, page offsets Run for table and view in the database
DBCC CHECKCATALOG Checks for catalog consistency within the specified database. Checks that every data type in syscolumns has a matching entry in systypes and that every table and view in sysobjects has at least one column in syscolumn Runs on the database

 

NOTE: Do integrity check for all system databases such as master, model, msdb, distribution. The TEMPDB database does not need an integrity check, as it gets created from scratch at each SQL Server service restart.

 

For the majority of my SQL Servers I use Ola database maintenance solution see more details here http://ola.hallengren.com/sql-server-integrity-check.html

The command I execute looks similar to below

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES',
@CheckCommands = 'CHECKDB'

 

What you do if you find CHECKDB error?

The answer is get on to it as soon as possible.

  1. Try to restore your database from good backup
  2. In case if no good backup available use REPAIR OPTION with DBCC CHECKDB. Only do this if you can’t find good backup as repair option may lead to the data loss

 

So what are the REPAIR OPTIONS?

  • REPAIR_FAST – does minor fast repairs on database without risk of data loss such as repairing extra keys in nonclustered indexes
  • REPAIR_REBUILD – does minor repairs and also rebuilds indexes without risk of data loss
  • REPAIR_ALLOW_DATA_LOSS – this is a time consuming process and can result in some data loss. It includes allocation and deallocation of rows and pages for correcting allocation errors, structural row or page errors, and deletion of corrupted text objects. Back up the database after repairs are completed

Note: All of these repair options require the database to be in single user mode. Hence downtime is necessary.

Use the following command

 

ALTER DATABASE AdventureWorks2008R2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

BEGIN TRANSACTION;

  DBCC CHECKDB ('AdventureWorks2008R2',  REPAIR_ALLOW_DATA_LOSS);

  ALTER DATABASE AdventureWorks2008R2 SET MULTI_USER;

COMMIT TRANSACTION;
GO

Linked Server Error “Cannot obtain the schema rowset”

I come across the Error Message while trying to run a linked server query for SQL Server 2000 on SQL Server 2008  & SQL server 2008 R2.

The error stated “Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10″ for linked server ‘LinkedServerName’”

After little Google search I came across two possible solution to fix this error message. Apparently that is well know issue when you try to run distributed queries from a 64-bit SQL Server client to a linked 32-bit SQL Server.

 

Main Cause of this issue:

  • Problem may occur because the system stored procedures were not upgraded to SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • May not have manually upgraded the system stored procedures after installing SQL Server 2000 SP3 or SQL Server 2000 SP4.
  • Whenever MDAC is upgraded to a newer version during SQL Server SP3 upgrade, one needs to run INSTCAT.SQL on all SQL Servers to which it connects to, including the Server that is being upgraded. I don’t think I ever done this.

Possible resolution Solution 1:

  1. Log on to the computer (SQL server 2000 server) by using a Windows account, which is a member of the SQL Server sysadmin fixed server role.
  2. Back up the master database
  3. Click Run, type cmd.exe, and then click OK.
  4. At the command prompt, type one of the following commands, and then press ENTER
 osql -E -S <LinkedServerName> -i <Location>\instcat.sql (For windows account with default SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName > -i <Location>\instcat.sql (For SQL account with default SQL server) OR osql -E -S <LinkedServerName>\<InstanceName> -i <Location>\instcat.sql (For windows account with Named Instance of SQL server) OR osql -U <AdminLogin> -P <AdminPassword> -S <LinkedServerName >\<InstanceName> -i <Location>\instcat.sql(For SQL account with Named Instance of SQL server) 
Linked server error
  • Test to see that, you can run all distributed queries

Reference Links

http://support.microsoft.com/default.aspx?scid=kb;en-us;906954#appliesto

http://technet.microsoft.com/en-us/library/aa215459%28SQL.80%29.aspx

 

Possible resolution Solution 2:

  1. Create the following stored procedure on your linked SQL Server 2000 instance
USE master

CREATE PROCEDURE sp_tables_info_rowset_64
@table_name SYSNAME,
@table_schema SYSNAME = NULL,
@table_type nvarchar(255) = NULL
AS
DECLARE @Result INT SET @Result = 0
EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

2. GRANT EXECUTE the procedure to the Public in the MASTER database

USE master
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Reference Links

http://blogs.xtivia.com/home/-/blogs/21924

I actually tried both suggested resolutions, however only Solution 2 successfully worked for me

fixing link server problem for SQL Server 2000

Introducing Microsoft SQL Server 2014

SQL Server 2014 was announced during Mcrosoft TechEd 2013 North America keynote.

The biggest selling points were

  • In-Memory capabilities built into the core database for OLTP and data warehousing. “Memory-optimized” tables are completely in memory and they don’t use pages for storage.
  • Increase performance by extending SQL Server in-memory buffer pool to SSDs
  • Hybrid Cloud Platform where cloud can be leveraged to extend the scalability and availability of on-premises database applications as well as reduce costs
  • Backup to Azure Storage and AlwaysOn integration with Windows Azure Infrastructure Services
  • BI Insights which simplifies access to internal or external data, analysing the data and unlocking it with powerful BI tools built into Excel and SharePoint.

It’s good to see SQL2014 is not for cloud only release. It brings new features and improvements for developers, administrators and power users. SQL Server 2014 will likely arrive early next year.

 

Sql Server Evolution through the ages

Ref: http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/03/sql-server-2014-unlocking-real-time-insights.aspx

 

Read more about the new features of the SQL Server 2014: