SQL Server Performance

Have SQL email you if your Litespeed (or native) backups fail

MichaelB — Tue, 16 Feb 2010 14:41:00 GMT

If you run Quest Litespeed backups I have the blog post for you! I have a job that I created that uses Database Mail and my Litespeed Central database. It spits out a email in HTML format that tells of any backups that failed for any server the day before. It works great and I know there is a need. There would have to be adjustment of the query if you are using native backup. Litespeed allows for a central repository and that is what we are using currently.
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml =CAST(
(
select
a.ServerName as 'td','',
d.DatabaseName as 'td','',
d.Deleted as 'td','',
t.ActivityTypeName as 'td','',
a.StartTime as 'td','',
a.FinishTime as 'td','',
st.statusname as 'td','',
a.DatabaseSize as 'td','',
a.PercentCompleted as 'td','',
a.ActivityDetail as 'td','',
a.ErrorMessage as 'td'
from
LiteSpeedActivity a
inner join
LitespeedDatabase d ON
a.databaseid = d.databaseid
and a.servername = d.servername
inner join
LitespeedActivityType t ON
t.activityTypeID = a.activitytypeid
inner join
LitespeedStatusType st ON
a.statustypeid = st.statustypeid
where a.finishtime > getdate() - 1
and statusname <> 'completed'

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>The Big Mamoo </H1> <body bgcolor=#FFD9C8> ' +
N'<table border="2">' +

'<tr><th>ServerName</th>
<th>DatabaseName</th>
<th>Deleted</th>
<th>ActivityTypeName</th>
<th>StartTime</th>
<th>FinishTime</th>
<th>statusname</th>
<th>ActivityDetail</th>
<th>ErrorMessage</th>' +
CAST ( ( SELECT
td = a.ServerName,'',
td = d.DatabaseName,'',
td = d.Deleted,'',
td = t.ActivityTypeName ,'',
td = a.StartTime,'',
td = a.FinishTime,'',
td = st.statusname,'',
td = a.ActivityDetail,'',
td = a.ErrorMessage
from
LiteSpeedActivity a
inner join
LitespeedDatabase d ON
a.databaseid = d.databaseid
and a.servername = d.servername
inner join
LitespeedActivityType t ON
t.activityTypeID = a.activitytypeid
inner join
LitespeedStatusType st ON
a.statustypeid = st.statustypeid
where a.finishtime > getdate() - 1
and statusname <> 'completed'

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'me@myisp.com;
@body = @tableHTML,
@body_format ='HTML',
@subject ='Backups',
@profile_name ='MyMailserverProfileName'

Let me know if you find it handy!

SQL Server Training Videos

admin — Tue, 07 Jul 2009 10:19:00 GMT

I'm happy to announce just the launch of the new Training Videos section here . As a flavor of things to come we kick off the section with one introductory video on Installing SQL Server and a more advanced video on Policy Based Management. We should have three videos online by the week's end and plan to add between one and two new videos per month.

Please let us know if you have any suggestions or topics you would like us to cover.

Life Changing Technology

shanetasker — Mon, 02 Mar 2009 20:16:00 GMT

As someone who works in IT, I get to see a lot of different technologies. Often I talk about technology as a life changing tool, such as the ability to access e-mail anywhere or being able to talk to people on the other side of the world. However, is this really life changing technology? I was talking to an engineer today that designs the valves inside pacemakers for people that have suffered a heart attack and I was amazed at how much high-tech wizardry is found in something smaller than a marble. I think that this type of technology is truly lifesaving, as without these devices the lives of hundreds of thousands of people would be different. What technologies have you come across of late that you would consider life changing?

- Peter Ward

Google Scanning Project

shanetasker — Fri, 27 Feb 2009 20:12:00 GMT

Over the last few days I have been driving along the coast of western America from San Francisco to Seattle. I have made it about 70 percent of the way and I am writing this article from a ski lodge in Lake Diamond. Along the way I have been listening to the local radio stations. In many places the only radio station that was available was National Public Radio (NPR). NPR is a commercial-free news and talk radio station. Once of the things I found interesting about this station was the lack of music and the amount of spoken word ranging from radio theatre to current affairs journalism.

One of the stories covered over the last few days was on the Google Books Library Project. I originally heard about this project several years ago when Google was planning to digitally scan books from library collections and over time integrate this content into Google search. This project appears to have been extended to books that are no longer in print. As per the Google ethos of 'Don't be evil,' as part of this project a computer that has free access to this collection will be provided to every public library project. Until listening to the librarians that were interviewed in this radio piece, I have never really thought too much about the impact of one organisation controlling access to information. The reason for public libraries being established in the first place was to provide a collection of useful material for common use. In other words information is not controlled by a single entity. What is your opinion on one entity possibly controlling access to out of print information?

- Peter Ward

SQL Server 2008 SP1

shanetasker — Thu, 26 Feb 2009 19:07:00 GMT

Earlier in the week I wrote about the adoption of SQL Server 2008. One of the biggest reasons that often limits the adoption of a Microsoft product is the release of the first service pack. Many companies have a policy that they will not even look at a new release until Service Pack 1 is available. Well the wait is almost over as the Community Technology Preview (CTP) of Service Pack 1 for SQL Server 2008 can be downloaded from here.

It may appear that Service Pack 1 is being released very quickly for SQL Server 2008 especially with the delay between SP2 and SP3 for SQL Server 2005. SQL Server 2005 was released in November 2005 and the first Service Pack was release about five months later in April 2006. Whereas the first Service Pack will be released over seven months after the release of SQL Server 2008. I would be interested in what your thoughts are regarding this. Does this mean that the RTM build of SQL Server 2008 was better than that of 2005?

- Peter Ward

What a Mess

shanetasker — Wed, 25 Feb 2009 20:39:00 GMT

In the enterprise, Windows Vista is yet to gain a foothold. Many customers are reluctant to install Vista for various reasons and as a result they are still running Windows XP. Microsoft staunchly recommends that when patches are released, such as XP Service Pack 3, that they should be installed sooner rather than later. This is where the fun starts. In the past if a setup process detected an older or new version of the same file it would prompt allowing you to choose whether the file should be replaced or not. With the release of XP Service Pack 3, the individual MSXML6 files were bundled with the release and added as Windows Protected Files. Hence, when you attempt to install SQL Server 2005 or 2008 or even potentially a SQL Server update, the install is unable to update the MSXML files causing the install to fail. More details on this issue can be found at Connect Item #361660.

It appears that there are two issues here, the SQL Server team created setup packages that can't bypass a newer version of a file that already exists on the target system and the Windows team failed to communicate the changes to the MSXML files. However, this does little to appease the fact that there are many people scratching their heads trying to work out why they can't install SQL Server on Windows XP Service Pack 3. I am not sure if Microsoft is learning from the mistakes of the past or simply just coming up with new ways to make the same mistakes.

- Peter Ward

Why Upgrade?

shanetasker — Tue, 24 Feb 2009 20:33:00 GMT

A conversation that I have with a lot of people is around the reasons to upgrade from SQL Server 2005 to SQL Server 2008. The problem is that a lot of organisations run Standard Edition and have no compelling reason or need for Enterprise Edition. Much of the focus in SQL server 2008 was squarely targeted at the Enterprise Edition with many of the new features only available in the more expensive version of the product. The SQL Server 2008 release appears to have almost ignored the large number of installations that are Standard Edition. I think that one of the reasons that SQL Server 2008 has not been widely adopted is that there is no compelling value proposition to upgrade from SQL Server 2005 to 2008 if you are running Standard Edition. I would be interested in what your thoughts are regarding the reason to upgrade.

- Peter Ward

Cell Phone Dependency

shanetasker — Mon, 23 Feb 2009 19:26:00 GMT

If you were to look at the adoption curve, you would see that I am sitting way out on the left, meaning that I am often prepared to put up with buggy software and other strange quirks all to try version one of a new product. I owned my first cell phone in 1993, back when they were still analogue and the size and weight was like carrying a house brick. I remember my backpack all of a sudden weighing twice as much, as there was no way that you could pop the phone in your pocket. Sixteen years later, cell phones are commonplace, and one almost expects everyone to have a cell phone.

The problem is that access to a cell phone is a dependency that is very hard to replace. I remember in the days before cell phones when people would wait patiently to meet someone, as they had no way to contact them. Whereas meeting someone today is like landing an aircraft with messaging back and forth between two people as they approach the designated meeting time and place. I have recently changed mobile phone providers for the first time in over 10 years and found out firsthand what being without a phone is like. I am in the U.S. at the moment and global roaming was not enabled on my phone for about 12 hours. During this time I felt almost like something was missing and it made me think, how did we ever survive before cell phones?

- Peter Ward

Continual Learning

shanetasker — Fri, 20 Feb 2009 20:54:00 GMT

Something that I am a big advocate of is ongoing professional development. Like many people who are in the IT profession, I went to university in order to be educated and receive formal qualifications. The problem is that this education experience could be 5, 10 or maybe even 20 years ago. Skills in products such as PASCAL or COBOL have been replaced with new skills such as C#. Now, although organisations still use legacy products such as those that were tough at university in the past, their relevance is diminishing.

Recent studies have shown that people who keep mentally active through ongoing learning suffer less brain-related diseases and on average live longer than those who are not mentally active. I have started to read a book called 'The Brain that Changes' that explains the phenomenon known as neuroplasticity. The theory is that by exercising and conditioning the brain we can in effect increase the percentage of our brain that can be utilised, just as exercise can be used to improve overall performance in an athlete and help rehabilitate injuries. Do you have a formal plan in place to exercise your brain and continue to seek new knowledge?

- Peter Ward

People Watching

shanetasker — Thu, 19 Feb 2009 19:56:00 GMT

One of the things I enjoy about travel is the ability to experience things that you do not see in your day-to-day environment, for example, waking up to a view of the Eiffel Tower in the distance or watching the sunset over the Grand Canyon. I have been fortunate enough to be able to experience many different cities and sites around the world; however, the one thing that always intrigues me is people watching. That is watching how people respond to every day scenarios.

The one thing that intrigues me is the way that people use their cell phones. For some, it appears to be the most important thing in their lives; others never answer it when you call. I understand that there are people who need to be available to respond to phone calls in an urgent matter. If you are the leader of a country and expected to give executive orders at a moment's notice or an on-call medical specialist then responding to your mobile phone is important. However, do you really need to respond to a phone call when you are in the bathroom? I was in the airport today and there was a gentleman who decided his call was just as important as the reason he was in the bathroom. What are your thoughts about cell phone etiquette and where is the strangest place you have seen someone using a phone?

- Peter Ward

Re-configuring RAID on the server - a detailed approach and best practices

satya — Thu, 19 Feb 2009 10:58:00 GMT

Reconfiguring the hardware or software on the server is a big job and you wouldn't attempt to perform the task on day-to-day basis or even not in your life time. Here is the detailed steps that are involved in this mammoth task refer to this post: http://sql-server-performance.com/Community/forums/t/29471.aspx in the forums. A good one to learn.......(read more)

Looking Busy

shanetasker — Wed, 18 Feb 2009 19:12:00 GMT

I have written a few times about what DBAs do and what their job role is. I actually think that people not knowing what DBAs do is a good thing. You see people only identify with what DBAs do when there are issues. When there is an issue with a database and the DBA fixes the problem then a DBA is recognised and given credit for being able to recover the database. However, very rarely do you see a DBA recognised for doing a great job and ensuring there is no downtime in the first place. I have heard stories of DBAs sabotaging a system just so they could be recognised in the organisation as having a key role. So what do you do in your organisation to ensure that your efforts are recognised and that people know you are busy?

- Peter Ward

Cooking a Database

shanetasker — Tue, 17 Feb 2009 19:55:00 GMT

I have to admit that I am partial to watching the occasional episode of the British chef Nigella Lawson. One of the first things my wife had installed when we moved house last year was an LCD TV in the kitchen so that she could watch TV; however, I use it to watch cooking shows. The one thing that annoys me about a lot of cooking programs is that they contain nothing more than a 30-minute segment with a super star chef throwing ingredients into a pan and then out comes a dish with a fancy name and a generous drizzle of sauce. The problem is, how do you produce a show when the chef is sick or opening another five-star restaurant?

If you think about the design of a database, it is a similar situation. Often there will be a senior DBA or an architect that will design a database. All too often, though, this is then the same person that implements the database without a list of ingredients. In other words there is not sufficient design documentation for another DBA to follow the recipe. Just like a head chef, a senior DBA is responsible for identifying what the customer wants and then coming up with a menu that matches the requirements. As a DBA, when was the last time you stepped out of the kitchen?

- Peter Ward

Good to Be a Friday

shanetasker — Fri, 13 Feb 2009 21:04:00 GMT

It's Friday. For many of us this means a long lunch and then finishing early to head to the nearest drinking establishment. In Australia finishing up early on a Friday afternoon at beer o'clock is a long-standing tradition in many organizations. One of the things that I have found working with various teams is that beer is a great social lubricant. People who are otherwise unwilling to contribute ideas and suggestions in the formal atmosphere of the workplace are often more than happy to open up after a pint or two. One of the important components in any team is frank and open discussion about what is both good and bad so that change can be initiated where necessary to ensure continual improvement. So why not head to the pub with your team this afternoon and build some rapport with your colleagues? As that is where I am heading right now. Have a great weekend.

- Peter Ward

Keeping Up With the Competition

shanetasker — Thu, 12 Feb 2009 19:57:00 GMT

Over the last few weeks I have been doing quite a bit of work with MySQL. I have worked almost predominately with SQL Server since I started as a DBA with little patches of Oracle development to break up the monotony. However, I would consider my Oracle skills only good enough to get myself into trouble and definitely not enough to get myself out of trouble. One of the things I find spending some time working with other DBMS products is the opportunity to take the blinkers off and see how other products solve similar problems.

One area I have been very impressed with in MySQL is the vast array of Date and Time functions that are available. For example, there is a function that takes a date or datetime value and returns the corresponding value for the last day of the month. Although there are ways in SQL Server to retrieve the last day of the month and we have the ability to write our own scalar function there is elegance in the feature being available out of the box. In most large organizations today it is very rare for there to be a single flavor of DBMS so if there is a feature that you have seen in one of the competitors that you wish was in SQL Server I would love to hear about it.

- Peter Ward

What You Do

shanetasker — Wed, 11 Feb 2009 18:24:00 GMT

If we think about the role of a Database Administrator many of the tasks that we are responsible for can seam mundane and almost repetitive. The tasks need to be completed but there is no glory or recognition. Typically, people want to be involved in high profile projects where there is an opportunity to be visible so that others can see what they are working on. I sometimes think that the biggest compliment to a DBA is when someone asks 'What is it you do here?' What this question means is that all of the databases that you mange have not had an outage. Often it is only when there is a disaster of some form or another that an organisation turns to the DBA. If all the correct measures were in place then often the disaster would not occur in the first place. So next time someone asks what it is you do, you should respond, 'It is not what I do, but what I do that allows you to do'.

- Peter Ward

Laziness

shanetasker — Tue, 10 Feb 2009 21:57:00 GMT

Is it just that I am getting older, or is the world becoming lazier and lazier? We now have technology that can reduce the effort required to do many household chores. From microwave ovens to instantly heat our meals to dishwashers to clean our plates and cutlery. There are even companies that sell robotic devices to clean the floor and mow the lawn automatically. Now I am all for reducing the effort required to perform a task; however, there is a point where it becomes laziness.

What I am finding more and more is that people expect answers straight away. Often they will e-mail or call you rather then research the question on their own. Well, I came across a solution today called ‘let me google that for you‘. It is a Web site that shows someone how easy it would have been if they simply fired up their search engine rather than expecting someone else to spoon-feed the answer to them. There is nothing wrong with helping people; however, sometimes people just need to help themselves.

- Peter Ward

SQL Standards

shanetasker — Mon, 09 Feb 2009 18:46:00 GMT

SQL is a programming language that has been standardised by the American National Standards Institute (ANSI) and also the International Organization for Standardization (ISO). The problem is that although the SQL language has been standardised, it is really up to the individual DMBS vendor as to whether they adopt the standards or not. DBMS vendors are also free to add their own extensions to the language that are not part of the standards.

Over the weekend I was writing report specifications to interface with a very popular accounting package and I was using Microsoft Access (I know what you are thinking) to review the schema to define the requirements that could be handed over to a report writer. In doing this I needed to write some SQL statements to present the data. In order to write a simple SELECT statement to do this I must have spent about 20 minutes in order to work out what the specific syntax was to return the desired date format. Even though Access and SQL Server are both Microsoft products they do not even have the same SQL syntax. So what chance is there that other DBMS vendors will adopt a consistent syntax at least for the basic operations?

- Peter Ward

Customer Service

shanetasker — Fri, 06 Feb 2009 20:19:00 GMT

This afternoon I at sat a Microsoft exam so I decided to block out my calendar for the entire afternoon. I finished my exam early so I decided I would do some work from a cafe until I caught up with a friend for a beer. This is where the problem started, as for some reason my wireless data card that I use to connect to the Internet stopped working. I contacted the supplier and managed to waste two hours of my life just getting into contact with someone that could assist me. After being hung up on six times and transferred between countless departments the problem was finally resolved by the provider modifying a configuration on their side.

I understand that issues will occur that affect the availability of a service; however, when a service is sold as a premium service with a call response of five minutes I expect a call response of five minutes. There is a saying about under-promising and over-delivering; however, one of the problems I find in the IT industry is that people are often unable to live up to their promises. Something I see time and time again is people do not keep the customer updated with what is happening. Often a simple phone call or e-mail to let customers know the status of an issue is enough to appease them, as they know that someone is working on the problem. Do you have a strategy for communication when there is an issue that you are working on?

- Peter Ward

Best Practices

shanetasker — Thu, 05 Feb 2009 20:23:00 GMT

My wife and I are about to embark on a renovation project on the house we moved into in September last year. At the moment we are making a lot of cosmetic changes to the house before we start the major works and enclose underneath the house. Hence, while the beams are still exposed downstairs we are running all of the cables to setup the network at home. One of the things I wanted done today was for extra cable runs to be made and then a loop of cable to be left strung up downstairs so that we would not need to make another cable run when the stud walls are constructed; as a result, this led into a debate with the cable contractor about best practices.

The electrician started to tell me that it was not best practice to leave the cable unterminated and that we should make another run when we were ready. However, when you think about what best practices means, it actually means that one process is more effective at delivering a particular outcome than any other. I often find that people blindly follow a best practice when they should be utilising another technique due to the specific situation. Do you have a set of best practices that you follow or do you assess the application of them in each particular situation?

- Peter Ward

What Version Is It?

shanetasker — Wed, 04 Feb 2009 20:04:00 GMT

The January version of SQL Server 2008 Books Online has recently been released and is now available for download. Updates to Books Online are now being made available on a quarterly basis and it is a good idea to make sure that you always have the latest version of the documentation. However, this herein is where the problem lies. Like many people I have SQL Server 2005 and 2008 installed on my workstation. When I type SQL Server Books Online into the Start Search menu in Vista I get two versions listed. Likewise if I type SQL Server Profiler I get the 2008 and 2005 version listed and there is no way to differentiate them as even the icons are the same. Instead, you need to check the path of the shortcut to identify the right one. If you think of any other application such as Microsoft Excel the version is included in the shortcut so that when you search for Excel you can clearly see which version is 2003 and which is 2007. Is having the version of each SQL Server application in the shortcut name important to you?

- Peter Ward

Paying It Forward

shanetasker — Tue, 03 Feb 2009 20:31:00 GMT

Several years ago there was a movie starring Kevin Spacey that was titled Pay It Forward that described a phenomenon of kindness. The basic gist of the theory of Pay It Forward is that you help someone out without expecting anything in return, expect knowing that at some point in time in the future someone else other than the benefactor will help you out. I was standing in the checkout line at the supermarket tonight picking up a few last minute items for dinner and I saw what I thought was one of the kindest acts I had ever seen. There was a lady at the checkout that appeared distressed and was holding up the queue of five people behind her. One of her credit cards had been rejected and then a second card was also rejected due to insufficient funds. All of sudden a gentleman in line stepped out and handed over his credit card and paid for the lady's groceries. Although we aren't all in a financial position to perform a charitable act like this, it did get me thinking about when the last time it was that I went out of my way to help someone else.

- Peter Ward

Does Certification Matter?

shanetasker — Mon, 02 Feb 2009 20:13:00 GMT

I was catching up on my RSS feeds today and I can across a post on SQL Server Certification Statistics. What I found interesting about this post was how few people are certified for SQL Server 2000 versus SQL Server 2005. There are over 150,000 people certified for SQL Server 2000 whereas there are only approximately 50,000 for SQL Server 2005. Back around the dotcom boom, having Microsoft certification was almost a guaranteed ticket in order to get a job in the IT sector. In fact, an entire industry formed to support people gaining Microsoft accreditation. There were even companies that would run boot camp style programmes for certification so that even people that had never used a computer could pass the exams. As a result, the value of having certification was diminished, as it was no longer seen as a way to distinguish people who knew their stuff versus those that simply passed a test. I would be interested to know what your thoughts are towards certification and whether it is still a valuable barometer to assess somebody by.

- Peter Ward

What Is a DBA?

shanetasker — Fri, 30 Jan 2009 20:06:00 GMT

There are certain jobs in life that are more glamorous then others. If someone asks what you do for a living and you answer fire fighter or pilot then the response is a lot different to someone that responds 'I am a tax accountant'. I think one of the reasons for this is that people understand what a fireman does, whereas no one is really sure what a tax accountant does. I have a good friend that was an accountant that moved into IT as he believed it was a more glamorous profession and he thought that it would be easier to meet girls as a computer programmer.

When someone asks what I do, my response is based on the target audience. For example, if someone is in the IT industry I will often introduce myself as a DBA, whereas for other people I will introduce myself as an IT consultant or just a consultant if I think they will ask me to fix their printer. One of the problems about being a DBA is that just like a tax account people do not know what it is we do. When you introduce yourself to a stranger and they ask what you do, how do you respond?

- Peter Ward

PerformancePoint No More

shanetasker — Thu, 29 Jan 2009 19:32:00 GMT

Just over a year ago I attended a lavish presentation to mark the release of version 1.0 of Microsoft Office PerformancePoint Server (PPS). PerformancePoint was released in November 2007 to fill a gap in the Business Intelligence stack by incorporating monitoring, analysis, and planning into a single application. One of the big selling points to justify the $20,000 price tag for the sever component was that PerformancePoint included forecasting that allowed Microsoft to compete with other vendors in the planning space. Just 12 months after the release, PPS has been discontinued as an independent product. It appears that the planning component of PPS is being abandoned just like Microsoft Songsmith. Although score carding will be integrated into SharePoint there has been no official mention of where the budget and forecasting features will end up, although the rumours appear to be that they will end up in the Dynamics product line. So does the fact that Microsoft no longer has a budgeting and forecasting solution really matter?

- Peter Ward