Home » SQL Errors

Category Archives: SQL Errors

How Row versioning impact tempDB ?

Scenario: DBA found that tempdDBdatabase usage is getting high and most of the size is consumed by row versioning. DBA raised the issue with application team running that query. Once Application team close the session, tempDB usage comes normal.

Question: Application tea raise concern “How can a select statement on table consume huge amount of resources in tempDB?”

Answer:

As per BOL (https://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx) : Row versioning-based isolation levels (RCSI – Read Committed Snapshot Isolation) reduce the number of locks acquired by transaction by eliminating the use of shared locks on read operations. This increases system performance by reducing the resources used to manage locks. Performance is also increased by reducing the number of times a transaction is blocked by locks acquired by other transactions. Row versioning-based isolation levels increase the resources needed by data modifications.

While updating the row in a table or index, the new row is marked with a value called the transaction sequence number (XSN), XSN is unique number and keeps updating with upcoming new command \ transactions. When updating a row, New version is generated. If DB is already having previous version of this row, previous version of the row is stored in the version store and the new version of the row contains a pointer to the old version of the row in the version store. New row version stores corresponding XSN value for reflecting the time the row was modified.

All version stores are linked in a chain. SQL Server keep running clean up task to remove old versions which are not in use. As long as a transaction is open, all versions of rows that have been modified by that transaction must be kept in the version store. This is the reason due to which long running open transaction can cause huge tempDB row versioning space.

Here, Row X is getting updated by multiple transactions (Tv,Tx,Ty,Tz) and each time new version store is created. When DB engine tries to clear old unused version stores, it found open transaction using version X. DB engine not able to clear any row version from version store after version X. Due to which TempDB size keeps increasing due to row versioning.

–Check Size of tempDB consumed by Version Store

SELECT SUM(VERSION_STORE_RESERVED_PAGE_COUNT) AS [VERSION STORE PAGES USED],

(SUM(VERSION_STORE_RESERVED_PAGE_COUNT)*1.0/128) AS [VERSION STORE SPACE IN MB],

SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT) AS [INTERNAL OBJECT PAGES USED],

(SUM(INTERNAL_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [INTERNAL OBJECT SPACE IN MB],

SUM(USER_OBJECT_RESERVED_PAGE_COUNT) AS [USER OBJECT PAGES USED],

(SUM(USER_OBJECT_RESERVED_PAGE_COUNT)*1.0/128) AS [USER OBJECT SPACE IN MB],

SUM(UNALLOCATED_EXTENT_PAGE_COUNT) AS [FREE PAGES],

(SUM(UNALLOCATED_EXTENT_PAGE_COUNT)*1.0/128) AS [FREE SPACE IN MB]

FROM SYS.DM_DB_FILE_SPACE_USAGE;

–Find session using version store

SELECT A.*,B.KPID,B.BLOCKED,B.LASTWAITTYPE,B.WAITRESOURCE,B.DBID,B.CPU,B.PHYSICAL_IO,B.MEMUSAGE,B.LOGIN_TIME,B.LAST_BATCH,

B.OPEN_TRAN,B.STATUS,B.HOSTNAME,B.PROGRAM_NAME,B.CMD,B.LOGINAME,REQUEST_ID

FROM SYS.DM_TRAN_ACTIVE_SNAPSHOT_DATABASE_TRANSACTIONS A

INNER JOIN SYS.SYSPROCESSES B

ON A.SESSION_ID = B.SPID

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Error while loading Excel data into database

Issue: I am trying to load data from Excel 97 – 2003 sheet into a database table. I have written a simple SSIS package to do that, but unfortunately, it fails with error: –

 

Microsoft (R) SQL Server Execute Package Utility  Version 11.0.6020.0 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  2:02:37 AM  Error: 2016-12-19 02:02:37.91     Code: 0xC0209303     Source: ExcelImpot Connection manager “SourceConnectionExcel”     Description: The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. If the 64-bit driver is not installed, run the package in 32-bit mode. Error code: 0x00000000.  An OLE DB record is available.  Source: “Microsoft OLE DB Service Components”  Hresult: 0x80040154  Description: “Class not registered”.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC001002B     Source: MAPTImpot Connection manager “SourceConnectionExcel”     Description: The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. For more information, see http://go.microsoft.com/fwlink/?LinkId=219816  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC020801C     Source: Data Flow Task 1 Source – Sheet1$ [38]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “SourceConnectionExcel” failed with error code 0xC0209303.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0047017     Source: Data Flow Task 1 SSIS.Pipeline     Description: Source – Sheet1$ failed validation and returned error code 0xC020801C.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC004700C     Source: Data Flow Task 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2016-12-19 02:02:37.91     Code: 0xC0024107     Source: Data Flow Task 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  2:02:37 AM  Finished: 2:02:37 AM  Elapsed:  0.733 seconds.  The package execution failed.  The step failed.

Cause: This error occurs when you try to execute 32-bit environment package in 64-bit system.

Solution 1: If you have project file of SSIS package or If you are running package other than SQL Agent jobs like calling from CMD.

Step 1: Navigate to Project-> [PROJECT_NAME] Properties.

Step 2: Navigate to “Debugging” option from left panel and from Right panel change Run64BitRuntime value to false.

Solution 2: If you need to execute package from SQL agent job only then you have readymade option available. Go to Job step and check below option.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Restrict user to login from single Host

Restricting user to login from single host may be sometimes required from security point and other business requirements. We can achieve it through SERVER LEVEL LOGON TRIGGER.

ALTER TRIGGER TR_CHECK_LOGIN_TEST_HOST
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @HOSTNAME VARCHAR(48)
DECLARE @PROGNAME VARCHAR(100)

SELECT @HOSTNAME = HOST_NAME FROM SYS.DM_EXEC_SESSIONS
WHERE SESSION_ID = @@SPID

IF ORIGINAL_LOGIN() = ‘TEST’ AND @HOSTNAME =’HostName’
ROLLBACK;

END

Once you create above trigger, Login “TEST” will be able to connect from specified host only. Connection from any other host will be failed and face below error: –

You can also find related errors in SQL Server Error log as well.

Error: 17892, Severity: 20, State: 1.

Logon failed for login ‘TEST’ due to trigger execution. [CLIENT: <local machine>]

Error: 3609, Severity: 16, State: 2.

The transaction ended in the trigger. The batch has been aborted.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.asp

Write-Host || Cannot invoke this function because the current host does not implement it

Issue: While running powershell in SQL job step, I am getting below error: –

A job step received an error at line 13 in a PowerShell script. The corresponding line is ‘Write-Host “ServerName: ” $comp’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot invoke this function because the current host does not implement it.  ‘.  Process Exit Code -1.  The step failed.

 

Solution: SQL Server Agent allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell. When SQL Server Agent starts SQLPS.EXE, it does not do this through the console (there is no console attached).

 

When you run Write-Host, PowerShell asked to write on host console but due to no availability of HOST console, we got error.

I removed Write-Host to resolve issue.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

Error While running PowerShell code from SQL Agent job – “Path Does Not Exist”

Issue: When I am running code from PowerShell console its running fine but when I execute it through SQL agent job step, I am getting below error: –

The job script encountered the following errors. These errors did not stop the script:  A job step received an error at line 14 in a PowerShell script. The corresponding line is ‘$files = Get-ChildItem “\\$comp\D$\*.xls”‘. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘An object at the specified path \\ServerName\D$ does not exist.  ‘  A job step received an error at line 43 in a PowerShell script. The corresponding line is ‘    $str = Get-Content $i.FullName | Where-Object { ($_ -match ‘Alive’) }’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Cannot bind argument to parameter ‘Path’ because it is null.  ‘.  Process Exit Code -1.  The step failed.

Solution: SQL Server Agent allows users to directly run PowerShell scripts in SQL Server Agent. Internally this is implemented by reusing the SQLPS.EXE shell. When SQL Server Agent starts SQLPS.EXE, It cannot access file system.

To access file system, you need to change the scope from SQL to filesystem.

You can do it by adding “cd C:” before executing filesystem commands.

 

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

What’s new in SQL Server 2016 installation?

1) As MS is kept increasing number of option while installation. To reduce no. of click, if setup pass Rules it will automatically moves to Next window.

2) On Server Configuration page, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. SQL Services needs this access to perform instant file initialization for data files instead of zero file initialization.  (For information read – https://msdn.microsoft.com/library/ms175935)

3) 2 New Tabs in Database Engine Configuration page. One is TempDB configuration & other is User Instance feature.

4) Separate Acceptance page for Microsoft R.

Step  by Step Installation :-

1. Setup Page, Choose First option

2. Setup checks for Global Rules and moves to next page, if all rules passed

3. Option to choose, if you want setup to download latest update and install

4. Initial Setup files installed

5. Check for installation rules and moves to next page, if all rules passed

6. Accept terms and condition, Click to Next

7. Select features you want to install

8. Check feature rule and moves to next page, if all rules passed

9. Select Instance name

10. Specify service accounts, you can also select collation from collation tab.

Here, MS specifically ask to GRANT PERFORM VOLUME MAINTENANCE TASK permission to service account. (For information read – https://msdn.microsoft.com/library/ms175935)

https://msdn.microsoft.com/library/ms175935

11. Select Authentication type

12. Select Data Directories

13. New Tab introduced to provide TempDB configuration at time of installation only

14. New Tab introduced for “User Instance” feature. Although, MS marked this feature to be removed. After this special tab, It seems MS changed his mind.

Refer – https://technet.microsoft.com/en-us/library/ms143684

15. Option to enable FileStream

16. Option for SSRS installation and configuration

17. New Page added for Microsoft R acceptance

18. Installation on the way

19. Installation Completed Successfully

Reference: Rohit Garg (http://mssqlfun.com/)

You can find and follow MSSQLFUN:-

http://www.facebook.com/mssqlfun

http://mssqlfun.com/feed

https://twitter.com/Rgarg86

Other Linked Profiles :-

http://www.sqlservercentral.com/blogs/mssqlfun/

http://social.msdn.microsoft.com/Profile/rohitgarg

http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

http://beyondrelational.com/members/RohitGarg/default.aspx

SQL Server 2005 || Database backup failed because Full Text catalog files are OFFLINE

Error : Database backup is getting fail with below reason.

The backup of full-text catalog ‘FullTextCatalog’ is not permitted because it is not online. Check errorlog file for the reason that full-text catalog becomes offline and bring it online. Or BACKUP can be performed by using the FILEGROUP or FILE clauses to restrict the selection to include only online data.

Cause : This behavior occurs because a full-text catalog in the database is not online. To perform a full backup, SQL Server 2005 requires all the database files and full-text catalogs in the database to be online.

Possible Reason : The database is attached from somewhere. However, you specify the incorrect location for the full-text catalog folder during the attachment.

Solution 1 (If FTS files are available but SQL server showing offline):

1. Locate the folder that contains the files for the problematic full-text catalog.

2. Run the ALTER DATABASE statement. Specify in the statement the correct location for the full-text catalog.

3. Rebuild the problematic full-text catalog in the database.

4. Perform a full backup of the database in SQL Server 2005 again.

Solution 2 (If FTS files are not available):

1. Detach database & attach again with all MDF, NDF, LDF & FTS files.

Web Reference :

1) http://support.microsoft.com/kb/923355

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

Thanks to Toadworld.com team for recognition !

Thanks to Toadworld.com team for recognition !

Toadworld.com Profile : http://www.toadworld.com/members/rohit-garg/blogs/default.aspx

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

SQL Server 2000 || Disable sa login

Question : How to disable sa user in SQL Server 2000?

Reason : Due to Security requirement of audit, We have to disable sa user for all SQL instances in environment having SQL 2000 \2005\2008 & 2008 R2.

Answer : We can disable sa user from SQL Server 2005 onwards but this is not possible for SQL Server 2000.

We have below system store procedure available in SQL Server 2000 but below is applicable for windows login only.

EXEC sp_denylogin ‘exampleuser’

EXEC sp_revokelogin ‘exampleuser’

The only solution, We can do

1. Change the password to very strong

2. Clear Server Roles

3. Clear all Database Access

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg (http://mssqlfun.com/)

ERROR || The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

Table with clustered index is totally depended on index accessibility.

ERROR : The query processor is unable to produce a plan because the index ‘IND_TABLE’ on table or view ‘Table’ is disabled.

REASON : We find that some disable the cluster index due to which issue occur. Clustered index physically sort & save data in pages. When clustered index is disable, DB engine is not able to access data although data is available with table.

SCREENSHOT :

Note :

· There is no option to ENABLE the Index. You have to REBUILD or DROP & RECREATE it.

· This is not the case with non-clustered index.

If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun

Reference : Rohit Garg(http://mssqlfun.com/)

%d bloggers like this: