Home » Script

Category Archives: Script

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

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

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/)

2013 with You & SQL Server

The WordPress.com stats helper monkeys prepared a 2013 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 41,000 times in 2013. If it were a concert at Sydney Opera House, it would take about 15 sold-out performances for that many people to see it.

Click here to see the complete report.

DMV-14 : Which Table has Max. rows & Size……..sys.dm_db_partition_stats

sys.dm_db_partition_stats DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms187737.aspx

Returns page and row-count information for every partition in the current database.

This DMV gives useful information about rowcount, free & reserved space of each table in database. It can be used for analysis purpose while adding new indexes, doing index Maintenance or checking the biggest table of database.

Query 1 : Details of table with row count & space used

SELECT

SS.NAME AS SCHEMANAME,

SO.NAME AS OBJECTNAME,

SI.NAME AS INDEXNAME,

PS.RESERVED_PAGE_COUNT*8 TOTAL_SPACE_CONSUMED_KB,

PS.USED_PAGE_COUNT*8 USED_SPACE_KB,

(PS.RESERVED_PAGE_COUNT – PS.USED_PAGE_COUNT)*8 FREE_SPACE_KB,

CASE

WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT

ELSE NULL

END AS ROW_COUNT

FROM

SYS.DM_DB_PARTITION_STATS PS

INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID

INNER JOIN SYS.SCHEMAS SS ON SS.SCHEMA_ID = SO.SCHEMA_ID

LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID

AND SI.INDEX_ID = PS.INDEX_ID

WHERE

SO.IS_MS_SHIPPED = 0

Sample Result

Remarks

1. To use this DMV, User required VIEW DATABASE STATE permission on the server.

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

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

DMV-13 : Finding locking & blocking……..sys.dm_tran_locks

sys.dm_tran_locks DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms190345.aspx

Returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted.

The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request.

This DMV is very useful in helping to identify locking and blocking issues on your data­base instances.

Query 1 : Details of currently active locks

SELECT

CASE DTL.REQUEST_SESSION_ID

WHEN -2 THEN ‘ORPHANED DISTRIBUTED TRANSACTION’

WHEN -3 THEN ‘DEFERRED RECOVERY TRANSACTION’

ELSE DTL.REQUEST_SESSION_ID END AS SPID,

DB_NAME(DTL.RESOURCE_DATABASE_ID) AS DATABASENAME,

SO.NAME AS LOCKEDOBJECTNAME,

DTL.RESOURCE_TYPE AS LOCKEDRESOURCE,

DTL.REQUEST_MODE AS LOCKTYPE,

ST.TEXT AS SQLSTATEMENTTEXT,

ES.LOGIN_NAME AS LOGINNAME,

ES.HOST_NAME AS HOSTNAME,

CASE TST.IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

AT.NAME AS TRANSACTIONNAME,

DTL.REQUEST_STATUS

FROM

SYS.DM_TRAN_LOCKS DTL

JOIN SYS.PARTITIONS SP ON SP.HOBT_ID = DTL.RESOURCE_ASSOCIATED_ENTITY_ID

JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = SP.OBJECT_ID

JOIN SYS.DM_EXEC_SESSIONS ES ON ES.SESSION_ID = DTL.REQUEST_SESSION_ID

JOIN SYS.DM_TRAN_SESSION_TRANSACTIONS TST ON ES.SESSION_ID = TST.SESSION_ID

JOIN SYS.DM_TRAN_ACTIVE_TRANSACTIONS AT ON TST.TRANSACTION_ID = AT.TRANSACTION_ID

JOIN SYS.DM_EXEC_CONNECTIONS EC ON EC.SESSION_ID = ES.SESSION_ID

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(EC.MOST_RECENT_SQL_HANDLE) AS ST

WHERE

RESOURCE_DATABASE_ID = DB_ID()

ORDER BY DTL.REQUEST_SESSION_ID

Sample Result

Remarks

1. Column request_session_id display the session ID owns the lock or raise request.

If value display is -2 indicates that the request belongs to an orphaned distributed transaction.

If value display is -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

2. Column request_owner_type details :-

· TRANSACTION = The request is owned by a transaction.

· CURSOR = The request is owned by a cursor.

· SESSION = The request is owned by a user session.

· SHARED_TRANSACTION_WORKSPACE = The request is owned by the shared part of the transaction workspace.

· EXCLUSIVE_TRANSACTION_WORKSPACE = The request is owned by the exclusive part of the transaction workspace.

· NOTIFICATION_OBJECT = The request is owned by an internal SQL Server component.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

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

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

DMV-12 : Retaion in Session & Transaction……..sys.dm_tran_session_transactions

sys.dm_tran_session_transactions DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms188739.aspx

Returns correlation information for associated transactions and sessions.

This DMV helps in correlating the relation between session & transaction. We can get several details about transaction by correcting it will other DMVs & system catalogs.

Query 1 : Query to check Transaction & Session details

SELECT

ST.SESSION_ID,

ST.TRANSACTION_ID,

DB_NAME(SP.DBID) DB_NAME,

CASE IS_USER_TRANSACTION

WHEN 0 THEN ‘SYSTEM TRANSACTION’

WHEN 1 THEN ‘USER TRANSACTION’ END AS USER_OR_SYSTEM_TRANSACTION,

CASE IS_LOCAL

WHEN 0 THEN ‘DISTRIBUTED TRANSACTION’

WHEN 1 THEN ‘LOCAL TRANSACTION’ END AS TRANSACTION_ORIGIN,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT

FROM

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

Sample Result

Remarks

1. Meaning of int value for is_user_transaction column

a. 1 = The transaction was initiated by a user request.

b. 0 = System transaction.

2. All possible state of transactions for is_local coulmn

a. 1 = Local transaction.

b. 0 = Distributed transaction or an enlisted bound session transaction.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

4. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

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

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

DMV-11 : T-Log space used by transaction……..sys.dm_tran_database_transactions

sys.dm_tran_database_transactions DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms186957.aspx

Returns information about transactions at the database level.

Some time , T-log space is hugely consumed by database & we are not aware that which transaction is culprit for this. This is useful DMV in such conditions to check T-log space used by each transaction on database.

Query 1 : Query to check Transaction log space used by each transaction will complete detail

SELECT

ST.SESSION_ID,

DT.TRANSACTION_ID,

DB_NAME(DT.DATABASE_ID) DB_NAME,

DATABASE_TRANSACTION_BEGIN_TIME TRANSACTION_BEGIN_TIME,

CASE DATABASE_TRANSACTION_TYPE

WHEN 1 THEN ‘READ/WRITE’

WHEN 2 THEN ‘READ ONLY’

WHEN 3 THEN ‘SYSTEM’ END AS TRANSACTION_TYPE,

CASE DATABASE_TRANSACTION_STATE

WHEN 1 THEN ‘NOT INITIALIZED’

WHEN 3 THEN ‘TRANSACTION NO LOG’

WHEN 4 THEN ‘TRANSACTION WITH LOG’

WHEN 5 THEN ‘TRANSACTION PREPARED’

WHEN 10 THEN ‘COMMITED’

WHEN 11 THEN ‘ROLLED BACK’

WHEN 12 THEN ‘COMMITED AND LOG GENERATED’ END AS TRANSACTION_STATE,

SP.HOSTNAME,

SP.LOGINAME,

SP.STATUS,

SP.LASTWAITTYPE,

SQLT.TEXT,

DATABASE_TRANSACTION_LOG_RECORD_COUNT LOG_RECORD_COUNT,

(DATABASE_TRANSACTION_LOG_BYTES_USED + DATABASE_TRANSACTION_LOG_BYTES_RESERVED )/1024 TOTAL_LOG_SPACE_USED_KB,

DATABASE_TRANSACTION_LOG_BYTES_USED LOG_BYTES_USED,

DATABASE_TRANSACTION_LOG_BYTES_RESERVED LOG_BYTES_RESERVED

FROM

SYS.DM_TRAN_DATABASE_TRANSACTIONS DT JOIN

SYS.DM_TRAN_SESSION_TRANSACTIONS ST

ON DT.TRANSACTION_ID=ST.TRANSACTION_ID

JOIN

SYS.SYSPROCESSES SP

ON SP.SPID = ST.SESSION_ID

CROSS APPLY

SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT

WHERE

DT.TRANSACTION_ID > 1000 AND ST.SESSION_ID >50

Sample Result

Remarks

1. Meaning of int value for database_transaction_type column

· 1 = Read/write transaction

· 2 = Read-only transaction

· 3 = System transaction

2. All possible state of transactions for database_transaction_state coulmn

· 1 = The transaction has not been initialized.

· 3 = The transaction has been initialized but has not generated any log records.

· 4 = The transaction has generated log records.

· 5 = The transaction has been prepared.

· 10 = The transaction has been committed.

· 11 = The transaction has been rolled back.

· 12 = The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted.

3. To use this DMV, User required VIEW SERVER STATE permission on the server.

4. If column database_transaction_begin_time has NULL then transaction is read only otherwise read write.

5. TRANSACTION_ID > 1000 & SESSION_ID > 50 is used for user transactions & sessions.

 

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

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

LinkedIn Auto Publish Powered By : XYZScripts.com
%d bloggers like this: