Pythian Group

So HP Cloud is finally yielding for some beta rain since their announcement late Sep.

It started late September when I signed up for private beta testing. They sent me this when I signed up

Thanks for your interest in HP Cloud Services. We will be sure to follow up with you when access becomes available. Since we’re only accepting a limited number of customers for our private beta based on customer profiles, we cannot guarantee that every request will be accepted.

However, we will review your submission and follow-up with you directly.

Two weeks ago, I got invited to join the beta and start testing the platform. Read the rest of this entry . . .

A frequent inquiry concerning databases’ security is to retrieve the database role(s) associated with each user for auditing or troubleshooting purposes.

Each database user (principal) can be retrieved from sys.database_principals and the associated database roles can be retrieved from sys.database_role_members

The following code runs against ALL the databases using SP_MSForeachdb and all roles for one principal is concatenated in one row Read the rest of this entry . . .

A DBA is kinda like a chauffeur, he doesn’t own the car but it would be nice to drive one that has Electronic stability control or Anti-lock braking system or airbags, etc…

For SQL server editions, it’s nice to have the Enterprise edition especially with big databases, we can use online index operations, fast recovery, peer to peer transactional replication, partitioning, etc…

Its the same for small databases, less than 10GB, standard/workgroup edition will be better than Express edition since more CPU cores can be used, more RAM can be added, you get SQL server agent, etc… Read the rest of this entry . . .

If you ever used SQL server Database Engine Tuning Advisor then you know you have few options as a source of analysis workload:

- SQL server Script file *.sql

Includes a query or set of queries targeting one or more databases. Tuning advisor will evaluate the script against target databases(s) and attempt to provide any recommendations.

- Trace file using SQL server profiler *.trc

You can use SQL server Profiler to capture different events happening at the instance and record these events on disk.

You can use the GUI to capture the events and watch them in real time then LATER save that trace to a file on disk or use server-side traces and the trace will automatically be saved to the file mentioned in the trace definition.

Read the rest of this entry . . .

If you do any performance tuning , and everyone do some day, then you’ll come to a point where you want to know if your tables have sufficient indexes to serve the queries fast.

Starting with SQL server 2005 , engine tracks indexes usage , through query optimizer, and can now determine if a query can benefit from adding indexes; this information can be identified using few Dynamic Management Views (DMV) including

sys.dm_db_missing_index_details
sys.dm_db_missing_index_group_stats
sys.dm_db_missing_index_groups
sys.dm_db_missing_index_columns

You can determine an ***estimate*** of the number of missing indexes per database by running following query

SELECT DB_NAME(database_id) Database_name
, count(*) No_Missing_indexes
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY count(*) DESC;

One important fact is that missing Indexes DMVs can track a maximum of 500 indexes so if you have many databases with many active tables then there’s a chance not all of the info will be present.

I’ve seen plenty are useful queries about creating statements to create these missing indexes but they just give vague names to indexes ,based on sys.dm_db_missing_index_groups fields.

Since it’s a very good practice to standardize your objects’ names , the following query will give names to indexes in following format Read the rest of this entry . . .

Gentlemen (and no Ladies),

Let’s go hairy!!

The 60s are NOT back but during the month of November , some gents , including myself, will grow a stache to raise the awareness of prostate cancer, an insidious disease that afflicts one in seven males.

Movember

Movember (a slang word “Mo” for moustache and “November”) is a worldwide movement in which men, known as Mo Bros, start November clean-shaven and then grow a mustache to bring awareness to prostate and testicular cancer.

The goal is to get family , friends and others to donate money to your Movember cause, which is then donated to the Prostate Cancer Foundation, LiveStrong and other men’s health research and awareness programs.

According to the American Cancer Society, one in every six men will get prostate cancer during his lifetime, and one in every 36 will die from the disease. Behind lung cancer, prostate cancer is the second leading cause of cancer death in men.

There are around 14 countries participating and you can participate through your region or country website.

It’s rather simple: register online as either an individual or a team, start inviting others to participate and maybe create a team then ask others “SHOW ME THE MONEY” . There are prizes for those who raise the most funds.

Grooming is the key

Initially ,the registered participant must start November 1 clean-shaven. Second, maintain your mustache: Grooming is key.

You don’t want to look ridiculous for an entire month or anything, you can do Mexican, Dali, Imperial, Fu Manchu, Pancho Villa, Handlebar, Pencil, Chevron, Walrus…etc.

Here’s a summary of some moustache styles and you can pick one, one of them is NO longer wearable though (You can get arrested in some countries for it)!!

You can go further and even get a copy of The Moustache Grower’s Guide for $9.95

MO Sistas are in too

Although,some say not necessarily, the gents are the ones who grow the MOs but the women play a role too. Some become team captains ; they recruit the “Bros” , endorse them and encourage them grow moustaches.

Statistics shows that lots of donors , up to 60%, of donors are women so ladies you are TOTALLY IN.

Where donations go

The money is used to fund great research projects around the world to fight prostate cancer and other male diseases.

In USA ,$7.5 million were raised for Movember. Worldwide, $174 million were raised and, according to Movember, this makes the group the largest non-government funder of prostate cancer research in the world.

A geeky Movember

I was just looking around for something to welcome Movember and then came something in mind that SQL server 2008/2008 R2 made possible (2005 doesn’t).

SQL server folks, could you please run the attached query in any 2008/2008 R2 SSMS and see what you get in the “Spatial result” tab? you may need to zoom in a bit to get something like this

Now, can you tell me who’s that famous Stache ??!!

Link to Code

Happy Movember,

(Mo)hamed

After an in place upgrade of SQL server 2005 to SQL server 2008 R2 , I tried to open SQL errorlog from Management> SQL server logs but I got the following error

Refreshing the node will pop the error up again.

Maybe we try T-SQL!

EXEC xp_readerrorlog


Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found


As far as I know , SSMS calls a system stored procedure xp_enumerrorlogs to return the list of error logs and associated last change date.

The location of the SQL server errorlog ,and master database files, is stored in registry under following key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.instance_name\MSSQLServer\Parameters

Instance_name is SQL server instance name currently being accessed ; it was default in my case so it was MSSQLSERVER

This is the value of SQL sever errorlog entry

-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

I then verified that the folder exists and SQL server service account has enough permissions to read/write the file.

Looked at Event viewer , Found following three events

Event Type: Failure Audit
Event Source: MSSQLSERVER
Description:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: local machine]

Event Type: Error
Event Source: MSSQLSERVER
Description:
The description for Event ID ( 17052 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Severity: 16 Error:18452, OS: 18452 [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication..

Event Type: Error
Event Source: MSSQLSERVER
Description:
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. [CLIENT: local machine].

What’s causing these login failures? the connection is certainly local so “untrusted domain” error looks misleading.

Xp_readerrorlog calls a DLL ,xpstar.dll, but there’s not much info how xpstar.dll works. However,the original error carried some guidance represented in “Failed to open loopback connection” part.

To me, the word loopback used to mean local communication on same server when addressing IP address 127.0.0.1 or Localhost computer name (I see it has more uses now).

ALL I did was to open Windows® hosts file C:\WINDOWS\system32\drivers\etc\hosts

and that was it , I didn’t find a map for LOCALHOST nor servername; all I did was to add that map

127.0.0.1 localhost server_name

After that , I could open the errorlog from SSMS and Xp_readerrorlog came back with results.

Hope this helps someone , It can be really annoying.

Cheers,
M

If you ever participated in some forum or message board , then you know what’s BBCode.

What’s BBCode ?

BBCode is short for Bulletin Board Code and used to format posts in forums or message boards as a fast way to achieve formating without the need to add any complex HTML code beside allowing safer posts (no JS code for example). BBCode uses tags indicated by rectangular brackets surrounding a keyword. At the time of displaying as part of a webpage , those tags are converted to HTML.

A simple example of BBCode would be to make some text bold , this can be simply written like that [b]Bold Text[/b] or italic [i]italic text[/i]. In HTML , this can be done by font tags (deprecated in newer HTML versions) or CSS text properties. I tell you what! I’m writing this blog and I’m even using some BBCode tags to format text. Read the rest of this entry . . .

One of our clients has a public web page and they needed to ensure that it is always up and accessible. Pythian already has a monitoring stack that includes website and pages monitoring.

I wondered if this could be done from SQL Server using built-in Windows modules to access external web resources. There are various the methods we can use.

Object Automation extended stored procedures

Adam Machanic has a nice blog describing how to use Object Automation extended stored procedures. That code, however, was designed for SQL Server 2000; to use it in SQL Server 2005 and after, you will need to turn on OLE Automation using sp_configure.

Adam also recommended using CLR for SQL 2005 and later as it is safer, more stable, and more capable, which is completely true.

VBSCRIPT

Another old-fashioned method is using a VBSCRIPT file, then calling it using XP_CMDSHELL and Windows Script Host (WSH).

The VBScript should work like this: Read the rest of this entry . . .

SQL server folks may find the “non-yielding scheduler” warning familiar and it is usually (if not always) associated with a stack dump.

The “non-yielding scheduler” message comes with warnings like the following:

Process %ld:%ld:%ld (0x%lx) Worker 0x%p appears to be non-yielding on Scheduler %ld. Thread creation time: %time. Approx Thread CPU Used: kernel 0x ms, user 0x ms. Process Utilization 0x%. System Idle 0x%. Interval: 0x ms.

This means that there is a background thread that keeps running and making other worker threads yielding back, in time.

There are many causes of the “non-yielding scheduler” error. Memory dump analysis is necessary to determine the cause. Look up the error in MS Support Advanced Search and you will find plenty of KBs dedicated to it.

You may also find (not all cases) errors 17883, 17884 logged in the event viewer, containing information like this: Read the rest of this entry . . .