Stream Processing Frame Works Compared

Read Stefan Papp's answer to What are the differences between Apache Spark, Storm, Samza, Flink, Beam, Apex? on Quorahttps://www.quora.com/widgets/content

Advertisements

Truncate table process blocked by select with(nolock)

I had to kill a user Query today since it was causing blocking. The user did just what was advised, use NOLOCK.. yet the user’s Query had to be killed because of this.

no-nonsense SQL Server

Where you would expect that writers don’t get blocked by readers using dirty reads, this is not the case when the writer executes a TRUNCATE TABLE command. This is because a truncate is regarded as a DDL operation (not DML). More technically, a select statement holds a sch-s(schema stability) lock (even using isolation level read uncommitted) on the object where a truncate will hold a sch-m(schema modification) lock on the object and these locks are not compatible.

View original post

Not every error can be handled..!

Did you know that there are a whole bunch of errors that cannot be trappedby a TSQL ‘TRY – CATCH ‘ block?

Behold ..!

Microsoft Docs says…

Errors Unaffected by a TRY…CATCH Construct

TRY…CATCH constructs do not trap the following conditions:+

  • Warnings or informational messages that have a severity of 10 or lower.
  • Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  • Attentions, such as client-interrupt requests or broken client connections.
  • When the session is ended by a system administrator by using the KILL statement.The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:
  • Compile errors, such as syntax errors, that prevent a batch from running.
  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.These errors are returned to the level that ran the batch, stored procedure, or trigger.

    If an error occurs during compilation or statement-level recompilation at a lower execution level (for example, when executing sp_executesql or a user-defined stored procedure) inside the TRY block, the error occurs at a lower level than the TRY…CATCH construct and will be handled by the associated CATCH block.

     

Kill Negative SPID -2 in SQL Server 2014

There can be a rare scenario when database restore or a query is being blocked by negative SPIDs when trying to find the lead blocker by running SP_WHO2 or using Dynamic Management Views. Negative SPIDs in SQL Server cannot be killed using KILL Command. This article explains how to kill Negative SPIDs in SQL Server and what they mean in SQL Server.

Source: Kill Negative SPID in SQL Server – MyTechMantra.com

I had an ugly case of persistent blocking on a sunday morning at 7 AM. I had database blocking going on with -2 SPID as lead blocker. -2 is an orphaned MS DTC transaction.

You cannot just do a

KILL -2

I found out as much information as I could about the offending query for blaming purposes 😉 like which database this is happening on using the resource_database_id from request_session_id etc. Now its time to kill the offending DTC orphan transaction.

Identify the offending transaction:

SELECT 
 DISTINCT(request_owner_guid)
FROM sys.dm_tran_locks
 WHERE request_session_id IN (-2)
GO

guess what I had multiple Unit of Work IDs (UOW):

00767C6D-5981-4488-B151-43E0A2DC5C31
3AFCD1C4-581E-4F68-A11F-5D8C6B5EABB4
B36C240B-EEF5-41A1-9863-C4DD8C313F8A
D826E956-39ED-45FB-9B57-DBF5DB236644
185E7533-6532-4639-A023-EC0371EE952E

and this how I killed’em all:

kill '00000000-0000-0000-0000-000000000000'
--The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.
kill '00767C6D-5981-4488-B151-43E0A2DC5C31'
kill '3AFCD1C4-581E-4F68-A11F-5D8C6B5EABB4'
kill 'B36C240B-EEF5-41A1-9863-C4DD8C313F8A'
kill 'D826E956-39ED-45FB-9B57-DBF5DB236644'
kill '185E7533-6532-4639-A023-EC0371EE952E'

Once I killed the orphaned -2 things got back to normal and the application started behaving normally.

Who deleted my database?

Context: SQL Server RDBMS

Often times in database environments where more than one person possesses sysadmin role things could get messy. Especially when you realize an object (Database,SP, Table etc) was deleted and you have no idea by whom and when this object was deleted. Unfortunately SQL server error log

EXEC XP_READERRORLOG

doesn’t do a very good job of showing logs related to deleted objects. There are answers; you just need to know where to look. SQL server default trace comes to your rescue.  The TSQL below helps you identify who deleted the object and when. This can be used in the both events of knowing or not knowing the database/object name.

SELECT DatabaseID,NTUserName,HostName,LoginName,StartTime
FROM
sys.fn_trace_gettable(CONVERT(VARCHAR(150),
( SELECT TOP 1
f.[value]
FROM    sys.fn_trace_getinfo(NULL) f
WHERE   f.property = 2
)), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE TE.trace_event_id =47 AND T.DatabaseName = 'delete'
-- 47 Represents event for deleting objects.

 

The result will looks like this:

Trace_results

 

Thanks for reading.

Hadoop to analyze President Barack Obama’s State of the Union Address Speeches

Hadoop_Obama

Idea

The State of the Union is the address presented by the President of the United States to a joint session of the United States Congress, typically delivered annually. The goal of this project is to use Hadoop’s map reduce programming model to analyze all the speeches delivered by the current president of united states Mr. Barack Obama and find out the most commonly used words while filtering for some common stop words. And present the results using histogram or similar graph.

Data/References

Speech Data

http://www.whitehouse.gov

Stop Words

https://code.google.com/p/stop-words/

SQL Server 2012 silent install

SQL Server 2012 silent install – Install Dozens of them with one click.

This post is for SQL server DBAs who have done SQL server installations before but would like to make it more efficient. Before we get started let’s take a look at the following:

  • Background
  • Why Choose Silent Install
  • Preparation
  • Be careful with

If you can’t wait let’s get down to business here.

Background

One of the responsibilities of a SQL Server DBA is to install & configure Microsoft SQL server. SQL Server stores configuration information in a Configuration.INI file typically in

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log

We can use this file directly or as a template to install SQL server via CMD without GUI. Especially if you are a DBA in a very large enterprise you might be required to suggest a customization for SQL server install so that it can be installed on dozens of servers. In such situations where you have to install it on multiple servers you can go with silent install and a smart DBA always saves time to invest the same on more challenging issues.

Why Choose Silent Install

  • Unattended

You don’t have to sit there clicking next or configuring database file locations, service accounts etc.

  • Consistency

Often times DBAs receive requests to install SQL server on multiple servers with exact same configuration.

  • Time

You can save a lot of time compared to the traditional installation.

Preparation

Here is some basic prep work you need to be prepared for the silent install

1. SQL Server 2012 Binaries

2. Configuration.ini file

3. Service Account/s Credentials

4. SA password

Here is a template of configuration file for you. You can download this file and edit the file as per your needs.

You can get very specific with configuration file but here are some high level settings you should consider

· Features – Database Engine, BIDS, Services ect

· Data, Log & Backup file locations

· All service accounts & their passwords

Be careful with

Service account getting locked: Please test this install thoroughly because there is a risk of service account getting locked if password is mistyped and the installation is re-tried.

Secure the passwords: You will be invoking the setup.exe of SQL server 2012 via CMD and pass service accounts and SA passwords as parameters; though the passwords are encrypted in the final configuration file is created after the install, the invoking part will expose the passwords so be advised.

SQL server 2012 Silent Install

Prepare INI file

You can use this file or you can go to an existing SQL server installation and locate the INI file here:

INIFilelocaTION

Edit the INI file as follows

; Remove/comment this parameter when using Quiet mode
;UIMODE="AutoAdvance"
A prefix of ; comments the line
; Setup will not display any user interface.
; You can make either QUIET or QUIETSIMPLE to be TRUE not BOTH
QUIET="True"
; Setup will display progress only, without any user interaction.
QUIETSIMPLE="False"

Agree to the terms & conditions by adding this line at the end of the file

; Accept the terms & conditions
IACCEPTSQLSERVERLICENSETERMS="True"

Invoke Setup.exe from command prompt

Once your config file is ready open command prompt & browse to the location of SQL server binaries and when you are in the directory which contains setup.exe, start the install by running the following command:

Setup.exe /SAPWD="Z87C0ptr@n34" /SQLSVCPASSWORD="password" /AGTSVCPASSWORD="password" /ISSVCPASSWORD="password" /ASSVCPASSWORD="password" /RSSVCPASSWORD="password" /ConfigurationFile=C:\ConfigurationFile.ini

The parameters we are providing via above command are

SAPWD Password for System Administrator
SQLSVCPASSWORD SQL Server Service Account’s password
AGTSVCPASSWORD SQL Agent Service Account’s password
ISSVCPASSWORD Integration Services Service Account’s password
ASSVCPASSWORD Analysis Services Service Account’s password
RSSVCPASSWORD Reporting Services Service Account’s password

Monitor the progress

Well, there is no point in monitoring the progress because we did so much just for the reason that our future installs are accurate and unattended. Anyone who installed SQL server knows how much time it takes and how many windows it goes through I would guess approximately a dozen different screens, this is all you get with silent install:

CMDP

Of course INI file you prepared can be used for future installs. So your next install is as simple as copy INI file & binaries on the server and run one command and reboot the server.

For those who do not trust that the install is actually going on, here is what you see when the install is going on:

Screenshot_3

And this is what you should see once the install is complete:

TSKMGR

Enjoy your installation 🙂

More

· Consider putting the command in a BAT file that way

o You can get before & after time to tell you the time taken.

o You can schedule it to run at a time of your choice.

· Getting errors?

o Go to

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt
[\code]
to check for the detailed error messages.

· Want to know the A-Z of Silent install?

o Go here: http://msdn.microsoft.com/en-us/library/dd239405.aspx

Let me know here from your comments if it worked or you had problems. Feel free to post any suggestions for improvement.