Saturday, June 30, 2012

Jobs Execution Status Report

I came across new two new reports in SSMS. (This report is available in SQL Server 2008 R2 and onwards). Those are coming under SQL Server Agent as shown below.



This report gave me this.


You will see in the key, there are two of # Successful Executions in green and in purple color!. I have never seen a chart key like this. Have you?

Difference between the purple and green is, Green “# Successful execution” for the successfully completed jobs while the purple one is for the canceled jobs. Should it be nice, if we have a different text for this, rather than having the same name for both.

Thursday, June 28, 2012

Paul's Ponderings

Following is something I got from news letter. Being an accidental DBA and on going struggle to find DBAs, I know I am not alone! Smile

I am for home-growing DBAs. Read this. What do you think.

‘Daddy, mommy – I want to be a DBA when I grow up!’

I’d be surprised if any of you who have kids have ever heard them say the words above – I certainly haven’t. Our girls say things like paleontologist and rock star when asked what they’d like to be in life.

My point is that very few of us really wanted to get into the field of storing, controlling, and managing data. I certainly didn’t – I wanted to work on file systems, which is what I did at DEC before joining Microsoft (my story is here) – and I kind of accidentally fell into the SQL Server world in 1999.

With that in mind, it’s really no surprise that there are very few degrees available in being a DBA and those that exist seem to be online or evenings only (such as this one at the University of Denver where Glenn teaches).
So how does one become a DBA? I don’t see job adverts looking for ‘someone with no knowledge of being a DBA so we can train them’ – all the adverts I see want someone with experience.

I think the route that most people take is by becoming an ‘involuntary’ or ‘accidental’ DBA first, and then moving to becoming a DBA with a job title that reflects the work being done. This means people are learning for themselves how to do the DBA job, and then getting some training afterwards.

Given that so many companies rely on data these days, and there’s a shortage of experienced DBAs in the industry, I think it’s time that some companies come up with formal plans for home-growing DBAs rather than relying on people to gather DBA experience somewhere else.

Call to Action: The next time you’re trying to hire a DBA, consider finding someone in your company that’s looking for a job change and training them to be a DBA.
I’m really interested to know your thoughts on home-grown DBAs—feel free to
drop me a line, confidentially as always.

REPAIR_FAST another flop

I posted about REPAIR_FAST around few days back. I got a feed back from Glen Joseph.

But, this requires the DB to be in Single User Mode ( am i correct ? ) these days you cannot do this in many Production environments.         

I just tried out it.


Yes Glen you are absolutely right!!!

Tuesday, June 19, 2012

New SSIS Features in SQL Server 2012

SQL Server Integration Services (SSIS) has under-gone through some significant changes in SQL Server 2012 which I will outline in this article.

Monday, June 18, 2012


DBCC CHECKDB is somewhat famous for DBA to run in case of database corruptions.

This is the syntax for it.


So incase of a corruption, you can have either of three parameters, REPAIR_FAST, REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS. Out of these parameters let us focus on REPAIR_FAST. This is the documentation for REPAIR_FAST in SQL Server 2000

Performs minor, nontime-consuming repair actions such as repairing extra keys in nonclustered indexes. These repairs can be done quickly and without risk of data loss.

However, do you know that this is no more with SQL Server 2005 onwards.

This is the documentation for the REPAIR_FAST.

Maintains syntax for backward compatibility only. No repair actions are performed. Hot smile

Keep sending your comments.!!!

Monday, June 11, 2012

Extended Events in SQL Server 2012

Extended Events were introduced with SQL Server 2008 and gone into few changes with SQL Server 2012. This latest article give you an overview of Extended Events in SQL Server 2012.

Sunday, June 10, 2012

SQL Server 2012 Data Mining add-in for Excel 2010 Released

Microsoft has released SQL Server 2012 Data Mining add-in for Excel 2010. This has been a much awaited release for those vested in Data Mining.

The previous release was only available for Excel 2007 and if you tried to use
it in Excel 2010 it only worked for 32 bit machines. This was a major issue in the previous release. You’ll find now a 32 bit and 64 bit version available now.

Go download and start playing with the latest release here.

Saturday, June 9, 2012

Collation Conflict 2

I posted a blog post about Collation Conflict some time back. With the discussions on Temp tables and table variables are on let us do this with a table variables.

Let us prepare the data set needed.


Then run the following using a table variable instead of temp table.


and this it is a success ( With using temp table, this will fail since tempDB in different collation)

So another place to use table variable and the debut continues …

Top 12 Features of SQL Server 2012

Microsoft has introduced SQL Server 2012 to the world and it's time for IT professionals to start to come to speed on what's new in this highly anticipated version of SQL Server.

1. AlwaysOn Availability Groups

2. Windows Server Core Support

3. Columnstore Indexes

4. User-Defined Server Roles

5. Enhanced Auditing Features

6. BI Semantic Model

7. Sequence Objects

8. Enhanced PowerShell Support

9. Distributed Replay

10. PowerView

11. SQL Azure Enhancements

12. Big Data Support


Wednesday, June 6, 2012

Audit Feature Enhancement in SQL Server 2012

Major improvement in the SQL Server Audit is expanding this feature to all the editions where previously it is limited to Enterprise, Datacenter, Developer, and Evaluation editions.
In case there is a failure to write into the Audit Log, for example if the Audit Log is in a remote server and if the network is down, SQL Server Audit will recover data once the connection is established.

Tuesday, June 5, 2012

Hacker group hits Warner Bros and China Telecom

A group calling itself SwaggSec is claiming to have hacked the networks of Warner Bros and China Telecom, and has released documents and logins online.

In a statement on Pastebin, the group says that both companies had severe vulnerabilities.

"China Telecom's SQL Server had an extremely low processing capacity, and with us being impatient, after about a month straight of downloading, we stopped. However, a few times we accidentally DDoS'd their SQL Server. I guess they thought nothing of it, until we left them a little message signed by SwaggSec," it says.

"They realized they were hacked, and simply moved their SQL server. No changing of admin passwords, or alerting the media. At any moment, we could have and still could destroy their communication infrastructure leaving millions without communication."

SwaggSec has also released the details of what it claims are over 900 admin users for China Telecom. It's published the login for this, and is encouraging people to access and tamper with its data.

As for Warner Bros, it says, hacking the company's intranet revealed that the company was aware of 'critical vulnerabilities' - but had done nothing about them, giving Swaggsec 'complete access to their servers'.

It includes a recent report titled 'Content Security Status Update', which includes a list of the company's top 10 medium-to-high-risk vulnerabilities. The top two are cross-site scripting and unsupported SSL.

Source :

Monday, June 4, 2012

Data Viewer in SSIS 2012

Can you remember how you enabled data viewers in previous versions of SSIS?

Here are those steps.

1. Right click the data flow path and select data viewer.

2. Then Select Add.

3. Select OK.

4. Select OK again.



Thought there so many options, we hardly use them.

Those few clicks which seems like unnecessary. With SQL Server 2012 SSIS, simply right click the data flow path and select Enable Data Viewer and you are done.