Archives for September, 2011

SQL Trace: how to replace JobID in ApplicationName column


When you setup a SQL Trace to capture long running transaction, you’ll get a reference to a SQL job that uses the varbinary representation of the JobId like this: SQLAgent – TSQL JobStep (Job 0x08B976420365AF44B78E40C081D79F18 : Step 6) Don’t ask me why. However, you can translate this JobId by its name like this: SELECT DISTINCT  […]

TransactionOptions in SSIS package


Recently I performed some tests on transaction behavior in SSIS; these are my findings Current situation We use a TSQL BEGIN/COMMIT TRAN within a SQL tasks to define the boundaries of a transaction within a package. All SQL tasks within these boundaries will succeed or fail as one unit of work if you define the […]

Buffer provided to read column value is too small


Yesterday received following error when running a simple select: Msg 682, Level 22, State 146, Line 1 Internal error. Buffer provided to read column value is too small. Run DBCC CHECKDB to check for any corruption. Performed a DBCC CHECKDB that gave no errors, but resolved the problem unexpectedly. Tricky stuff

SQL Server Impersonation thru stored procedures


Suppose you have users who by default have only read/write access but in certain circumstances need more permission, you don’t want to give them sysadmin just because it’s easy. Instead, you can create a stored procedure using the security context of the SQL Server service account which is member of the sysadmin server role by […]

Solve deadlocks by creating an index


Recently an SSIS developer can to see me with a deadlock issue. People rarely come to see me for social reasons. What immediately catched my attention was the “Exchange Event” process in the deadlock graph along with the deadlock chain argument: “Parallel query worker thread was involved in a deadlock”. After some investigation parallelism was […]