Bulk insert fails with error 4861 Cannot bulk load because the file could not be opened

When you do bulk insert in SQL Server it may fail with below error because of double hop .

Error :

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file “\\path\” could not be opened. Operating system error code 5(failed to retrieve text for this error. Reason: 15105).

Steps to fix the above error

1. Connect to SQL Server using SSMS (With account you run bulk insert) and execute below query and check if it is using Kerberos authentication

select net_transport,auth_scheme from sys.dm_exec_connections where session_id=@@spid

2. If the session is not using Kerberos authentication then fix the SPN issues (startup account of SQL Server should have read and write SPN permissions). Account which is running bulk inser should have read SPN permission. Setpn exe or adsiedit can be used to add or display all the SPN’s. If the SPn’s are registered properly and still connection fails to NTLM then get the output of SSPIClient.exe and verify why Kerberos authentication fails.

3. Make sure the account which is running bulk insert is trusted for delegation in Active Directory.

4. Account which is running bulk insert should have access to the shared directory in which BCP files are placed.

Reference  BULK INSERT (Transact-SQL) 

http://msdn.microsoft.com/en-us/library/ms188365.aspx

Security Account Delegation (Impersonation)

If a SQL Server user is logged in using Windows Authentication, the user can read only the files accessible to the user account, independent of the security profile of the SQL Server process.

When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.

To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.

For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK…) (SQL Server).

Advertisements

How to create backups using database maintenance plan

You can create a database maintenance plan to automate the SQL Server database backups. SQL Server backup maintenance plan can be scheduled to backup the databases automatically or executed manually.

Follow the steps below to create a database backup maintenance plan and schedule it to execute automatically

1.Open SQL Server Management Studio, expand the Management node, and then expand the Management Plans node.

 

image

2.Right-click Maintenance Plans, click Maintenance Plan Wizard.

image 

3.SQL Server Maintenance Plan Wizard window will appear.click next.

image

4.Then type  a name for this database backup plan.

image

 

  • Select schedule according to your need.Generally daily,weekly,monthly or hourly.

 

image

 

5.Select  the maintenance tasks,which you wanted to plan.

image

6.select the order of your plan.Click Next.

image

7.On the Define Back Up Database (Full) Task dialog box, specify information about the full backup. Specify database where this plan has to be applied.Press ok.Then Click Next.

image

image

8.On the Define Back Up Database (Transaction Log) Task dialog box, configure the transaction log backup. Click Next.

image

9. Choose the backup destination

image

10.On the Define Maintenance Cleanup Task dialog box, configure the cleanup tasks.specify the folder name where you take backups.Then specify the backup folders extension.Click Next.

image

11.On the Select Report Options dialog box, select whether to write the report to text file or send the report through email. Click Next.

image

  • Plan is in progress

image

 

image

12.Maintenance plan wizard is successfully created.Click Finish

image

How to set Max degree of parallelism (MAXDOP)

Max degrees of parallelism (a.k.a MAXDOP) in SQL Server is used to control the maximum number of threads per execution plan operators work.MAXDOP does not restrict the number of CPU’s/Threads used by single batch or Query.

Ideally MAXDOP should be equal to number of online schedulers per node. You can use the below query to get the number of  online schedulers per node. All the parallel threads for the tasks of the query will be assigned from schedulers of same node so having MAXDOP beyond the number of online schedulers per node may not really improve the performance (With some exceptions).  

select count(*) as Maxdopcount, parent_node_id from sys.dm_os_schedulers
 where status='VISIBLE ONLINE' group by parent_node_id

Depending upon the workload in your environment you may increase or decrease the value.

Note: Always ensure you have same number of online schedulers in each node else you may face uneven workload and memory distribution among the SQL Server schedulers more details in SQL Server NUMA load distribution.

To configure Max degree of parallelism follow the below steps.

1.Connect to the Database Engine.

2.From the Standard bar, click New Query.

3.Then execute the following query.

Syntax:

</pre>
sp_configure 'show advanced options', 1;

GO

RECONFIGURE WITH OVERRIDE;

GO

sp_configure 'max degree of parallelism', 1; /*Replace 1 with your preferred MAXDOP value */

GO

RECONFIGURE WITH OVERRIDE;

GO

<span style="font-family: Consolas, Monaco, monospace; font-size: 12px; line-height: 18px;">

 

image

Method2

1.In Object Explorer, right-click a server and select Properties.

2.Click  Advanced  from select a page.

3.In the Max Degree of Parallelism box, select the maximum number of processors to use in parallel plan execution.

image

Capture context switches from dm_os_ring_buffers

You can use the below query to extract the context switches information from ring buffers and time each thread spend owning the scheduler.

SELECT  
dateadd (ms, (a.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
a.*
FROM
(SELECT 
	  y as timestamp,	
      x.value('(//Record/@id)[1]', 'bigint') AS [Record Id],
      x.value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
      x.value('(//Record/@time)[1]', 'bigint') AS [Time],
      x.value('(//Record/Scheduler/@address)[1]', 'varchar(30)') AS [Scheduler Address],
      x.value('(//Record/Scheduler/Action)[1]', 'varchar(30)') AS [Scheduler Action],
      x.value('(//Record/Scheduler/CPUTicks)[1]', 'bigint') AS [Scheduler CPUTicks],
      x.value('(//Record/Scheduler/TickCount)[1]', 'bigint') AS [Scheduler TickCount],
      x.value('(//Record/Scheduler/SourceWorker)[1]', 'varchar(30)') AS [Scheduler SourceWorker],
      x.value('(//Record/Scheduler/TargetWorker)[1]', 'varchar(30)') AS [Scheduler TargetWorker],
      x.value('(//Record/Scheduler/WorkerSignalTime)[1]', 'bigint') AS [Scheduler WorkerSignalTime],
      x.value('(//Record/Scheduler/DiskIOCompleted)[1]', 'bigint') AS [Scheduler DiskIOCompleted],
      x.value('(//Record/Scheduler/TimersExpired)[1]', 'bigint') AS [Scheduler TimersExpired]
FROM
      (SELECT CAST (record as xml),timestamp  
      FROM sys.dm_os_ring_buffers 
      WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER' ) AS R(x,y)) a
	  cross join sys.dm_os_sys_info tme 
WHERE a.[Scheduler Action] = 'SCHEDULER_SWITCH_CONTEXT'
ORDER BY       a.[Scheduler Address] , [Time_stamp]

Error 601 : Could not continue scan with NOLOCK due to data movement.

When there is corruption in database (or) When scanning the data with the NOLOCK locking hint (or) with the transaction isolation level set to READ UNCOMMITTED, it is possible for the page at the current position of the scan would have deleted or moved by page splits caused by Inserts/Updates/Deletes making SQL Server not able to scan further and cause Error 601 : Could not continue scan with NOLOCK due to data movement.

Resolution
Unless the database has been explicitly marked as ‘read only’ there is no way to guarantee that there are no data modification operations going on.
The possible solutions are:
1. Check for 601 errors from the application and retry the query automatically if the error occurs.
2. Improve the indexes supporting the query or modify the query so that it has a smaller lock footprint and runs more quickly. If the query touches less data it will be less likely to encounter the problem.
3. Avoid use of NOLOCK hint and if necessary have a retry logic 601 error . Improving the indexes as mentioned above might make it possible to get this data without doing large scans that would be likely to cause blocking.
If you don’t use NOLOCK hint or to READ UNCOMMITTED Isolation level then check the database for corruption (Dbcc checkdb)

SQL Server monitoring

Every SQL Server DBA would have faced situations similar to SQL Server not accepting connections for few minutes, SQL Server not responding for few minute or Applications not able to connect with SQL Server for few minutes. Before DBA’s gets alerted about the situation and starts troubleshooting the issue. Everything becomes normal. Challenge in this situations is it becomes very difficult to understand where the underlying problem was, It could be a network connectivity, Application server problem or It might be an issue with SQL Server itself. How do we collect diagnostic data to prove that SQL Server was stable at the time of issue (or) If the issue is with SQL Server then how to collect data we need for diagnosing the issue when there is issue?

SQL Monitor to monitor SQL Server Services

SQL Monitor monitors the SQL Server services and creates log if SQL Server service is down (or) If SQL Server is not accepting (or) SQL Server is not responding to Queries

How it works?

SQL Monitor checks the SQL Server in 3-Phases

1. Check the status of SQL Server service through the windows service control manager

2. If the service is running then check if SQL Server is accepting connections

3. If SQL Server is accepting Connections then probe to perform a simple query and see if SQL Server is responding properly.

4. If SQL Server is not accepting Connections then connect to SQL Server using DAC and take a stack dump.

How to Configure

1. Create a folder call SQLMonitor in C:\

2. Create a Text file called serverlist.txt to fill all the SQLServer information in your account.

Format:

Servername [TAB] Servicename;

Ex:

Server1 MSSQLServer;

Server2 MSSQL$Prod;

3. Invoke command prompt and open attached SQLmonitor.EXE.

Advantage:

1. Multi-threaded . Each server and service is verified using its own thread so retrieving information from one server will not affect the pooling interval to other server.

2. Single exe can be scaled to monitor more than 1000 servers and 1000 services.

3. Uses few MB of memory and system resources.

You can Download SQL Monitor from this link

SQL Server assert in Location: purecall.cpp:51

SQL Server assert in purecall.cpp:51

BEGIN STACK DUMP:

spid 231

Location: purecall.cpp:51

Expression: !”purecall”

SPID: 200

Process ID: 5125

Description: Pure virtual function call

Server Error: 17065, Severity: 16, State: 1.

Server SQL Server Assertion: File: <purecall.cpp>, line = 51 Failed Assertion = ‘!”purecall”‘ Pure virtual function call. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Possible causes for above assert are

1. Antivirus softwares which detours in sqlserver address space can inject their instruction in sqlserver modules and can cause this Ex. Sophos etc..

Run select * from sys.dm_os_loaded_modules and check if there are DLL’d loaded from Antivirus (Company column will have the AV company name). If you see any antivrus exclude SQLServer from them.

(or)

Run lm command in the dump and see if there are any Antivirus DLL’s loaded in sqlserver process memory.

2. If you don’t see any Antivirus dll then run windows memory diagnostic tool and check if there are any memory problems on your system( %windir%\system32\MdSched.exe).

3. If there is no antivirus or memory errors follow the steps in http://mssqlwiki.com/2012/10/16/sql-server-exception_access_violation-and-sql-server-assertion/