Tuesday, December 1, 2009

December 2009 – Baton Rouge SQL Server User Group

December SQL Server User Group Meeting

Location: At Lamar Advertising

Wednesday, December 2, 2009

6:00 PM - 8:00 PM

Sponsored by: EMC

Topic

An Overview of Business Intelligence

Presenter

Brandon McMillon

Brandon McMillon is a Senior Solutions Principal at EMC.  He has 10 years of experience at Microsoft, working on the Visual Studio & Windows development teams.  He was also a Chief Software Architect for a Microsoft Gold Partner before coming to EMC.

Abstract:

This talk will first examine the current marketplace for Business Intelligence, and how it has gotten there.  We’ll look at how EMC is leveraging some of the newer tools and products to lower the costs and effort for implementing BI, in the context of a real-world EMC BI project.  We’ll also share best practices & lessons we’ve learned in these projects.  Finally, we’ll look at the next generation of BI tools and products, and talk about how they will impact the market and what problems they’re attempting to solve.

Agenda

5:45 pm - 6:00 pm:

General Introduction/Food and Drinks

6:00 pm - 7:30 pm:

Overview of Business Intelligence

7:30 pm - until:

Open forum for questions

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

Linear Gauge Control: A Practical Use

SQL Server Reporting Services 2008 introduced several new features. One key feature was the inclusion of the Gauge Controls. I was recently asked to provide a real-world example of the Linear Gauge Control. Since I am always up for a challenge, I delve head deep into the gauge controls. After doing a little reading and testing of the linear control I chose my path. I started with the following report:

clip_image002

The data in the report can be reproduced using the stored procedure provided at the end of the posting. As you can see the report contains Sales data from each country. Looking at the report I noticed, after a little time, that the United States has the largest amount of sales. In the past I would have added a parameter that allowed dynamic sorts based on the Total Sales column, which is not a bad solution. This helps the end-user to quickly identify the BIG winners and losers in regards to sales. However, what about all the countries that fall in between? As a result, I decided to add a linear gauge control that would graphically depict sales as a percentage.

When you initially add the Linear Gauge control to the report, there are several items (ranges, pointers and labels) that are unnecessary.

clip_image004

Therefore before attempting to associate data with the control, I remove three things:

1. LinearPointer2

2. Two right-most Ranges, which are shaded in the lighter two shades of gray

I also hide the labels and any tick marks, which can be done by selecting the entire scale and un-checking the items labeled Show Labels and Show Major Tick Marks.

clip_image006

In then end you are left with a control that looks like this:

clip_image008

The next thing I did was to set the End range at scale value for the remaining Range to 100. Since I am attempting to graphically depict sales as a percentage, comparing the values to 100 should provide a meaningful representation of the data. To calculate the percentages I used the following expression:

=(Fields!TotalSales.Value/SUM(Fields!TotalSales.Value,"Tablix1"))*100

In the expression I divide the Sales for each country by the total sales for all countries. Note that the sum for all countries is calculated by using the sum function, including the scope value. Finally the value is multiplied by 100 to ensure that the result is between 0 and 100, which is the start and end values of the controls only range (the remaining dark grey bar in the control).

Now the only thing left is to assign the calculated value to LiinearPointer1. To do this, select the orange part of the control only. Then right click, the following dialogue box will appear:

clip_image010

Click Pointer Properties, then the Linear Pointer Properties dialogue box will appear. Choose the expression button next to the textbox labeled value and insert the above calculation. Once complete run the report and you will be presented with a result similar to the following:

clip_image012

As you can see from the above image, an end user can easily identify which country is selling and which is not. If you have any questions or comments concerning this topic please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon

Patrick LeBlanc, founder www.sqlscripts.com and www.sqllunch.com

Tuesday, November 24, 2009

SQL Server 2008: Table-valued parameters

A new feature of SQL Server 2008 is Table-valued parameters (TVP).  This feature will allow Developers and DBAs to pass tables as parameters to stored procedures.  You cannot pass a variable table or temp table, you can only pass a Table Type, which is an alias data type or a user-defined type.  So how do you use it?  The first step is to create  a Table Type.  See the following script:

 

USE AdventureWorks2008

GO

IF EXISTS (SELECT * FROM sys.types WHERE name = 'CountryCodes' AND schema_id = SCHEMA_ID('Sales'))

DROP TYPE Sales.CountryCodes

GO

CREATE TYPE Sales.CountryCodes

AS TABLE

(

CountryCode nvarchar(3)

)

 

The next step is to create a Stored Procedure that will include a variable of the aforementioned Table Type. The following Stored Procedure uses the AdventureWorks2008 database to select Sales by Date and Country Region Code, using the TVP to limit the result to specified Country Region Codes:

USE AdventureWorks2008

GO

IF(OBJECT_ID('Sales.GetSalesByDateAndRegion')) IS NOT NULL

DROP PROC Sales.GetSalesByDateAndRegion

GO

CREATE PROC Sales.GetSalesByDateAndRegion

@Month varchar(20),

@Year int,

@CountryRegions Sales.CountryCodes readonly

AS

SET NOCOUNT OFF

SELECT

   cr.Name,

   SUM(TotalDue) TotalDue

FROM Sales.SalesOrderHeader sod

INNER JOIN Sales.SalesTerritory st

   ON sod.TerritoryID = st.TerritoryID

INNER JOIN Person.CountryRegion cr

   ON st.CountryRegionCode = cr.CountryRegionCode

INNER JOIN @CountryRegions c

   ON cr.CountryRegionCode = c.CountryRegionCode

WHERE

   DATENAME(MONTH,sod.OrderDate) = @Month AND

   YEAR(sod.OrderDate) = @Year

GROUP BY

    cr.Name,

    DATENAME(MONTH,sod.OrderDate),

    YEAR(sod.OrderDate)

SET NOCOUNT ON

GO

 

In the variable declaration of in the above stored procedure, the last variable is declared as the Table Type (Sales.CountryCodes) created in the first script.  Then the Table-valued Parameter (TVP) is used in the last JOIN of the query to limit the result to the items or Country Region Codes contained with the TVP.  On thing to be aware of is that the Table Type is read only.  The contents of the table cannot be modified.  Now that all of the formalities are out of the way, how do you use this in T-SQL?  The following example is a script of how to call a stored procedure that has a TVP as a parameter:

USE AdventureWorks2008

GO

DECLARE

@StartDate datetime,

@EndDate datetime,

@CountryRegions Sales.CountryCodes

SELECT

@StartDate = '4/1/2002',

@EndDate = '4/30/2002'

INSERT INTO @CountryRegions

VALUES('US'), ('CA')

EXEC Sales.GetSalesByDateAndRegion

@StartDate,

@EndDate,

@CountryRegions

 

As you can see in the above script, you will populate the TVP the same way that any other table is populated.  Once it is populated it can be passed to a stored procedure. 

Talk to you soon,

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

Monday, November 23, 2009

Deploying Reports on Windows 7

Recently I tried to deploy a report to a Report Server on my laptop, which is running Windows 7 Ultimate. Unfortunately I received the following error:

The permissions granted to user ‘MachineName\SomeUser ‘ are insufficient for performing this operations.

This confused me a bit since my account was an administrator. After digging a bit I found a very simple solution. Right-click on the Visual Studio icon or the Business Intelligence Development Studio icon and click Properties. Then go to the Compatibility tab. On that tab, in the Privilege Level section select the checkbox next to the item labeled Run this program as administrator. See the below screen shot example:

clip_image002

Once done, restart the application and Deploy your report.

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

Friday, November 20, 2009

How to Create a Reporting Services 2005/2008 Template

 

At most large companies one business requirement is that all reports have the same look and feel.  This may vary by department, but there is typically some level of standardization amongst the business entities.  In most cases there is a header and footer template that needs to be seen on all reports.  Often developers I have seen developers start from scratch or copy and paste and existing report.  Those days are gone.  For all of you still using Reporting Services 2005, don’t worry this method is available to you also.  Here are the steps:

1.  Create a template report, maybe and .rdl that contains only the header and footer information.  These are items that are typically used throughout a company or department.

2.  Copy the .rdl file to one of the following directories

(SSRS 2005) - C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

(SSRS 2008) - C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

The next time you create a report, right click on the Reports folder in the Solution Explorer of your Reporting Services Project.  Then choose Add –> New Item.  A dialogue box will open, similar to the one below:

image

In the above screen shot, my template is named Dashboard Template.  Choose that item and click Add.  When the report is added to you project it will look exactly like the template you created.  Happy Report Writing!

Talk to you soon

Patrick LeBlanc, founder www.TSQLScripts.com and www.SQLLunch.com

Wednesday, November 18, 2009

SQL Lunch Recordings Now Available

After several tries I think we finally have it working.  If you were unable to attend the last two SQLLunches, go to www.SQLLunch.com and go to the Archived November meetings.  They are both available for your viewing.  If you have any questions or comments about the SQL Lunch, please send an email to webmaster@sqllunch.com.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQLLunch.com

SQLDownSouth

Monday, November 16, 2009

Article of the Week – Kimberly L. Tripp (Partitioned Tables and Indexes)

When partitioning was first introduced in SQL Server 2005, I thought what a great idea.  However, when I started reading Books Online I thought, this is going to be a big Pain in the you know what.  After reading Kimberly’s article, Partitioned Tables and Indexes, I was convinced of how easy it would be to implement Partitioning and how it would help manage and maintain Very Large Databases, specifically warehouses.  The article is a little dated, but if you have any questions about Partitioning from a management and maintainability stand point this article is a must read.  She discusses topics that include, planning, defining, steps to create, etc…  This article should definitely help jump start your design and implementation of partitioning in your SQL Server environment.

To read the article in its entirety click here.

Talk to you soon

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

Today’s SQL Lunch – Integrating Table Valued Parameters with Reporting Services 2008

Meeting URL: Join Meeting

Click the above Meeting URL around 11:30 AM CST on 11/16/2009 to join the meeting

Date: 11/16/2009

Time: 11:30 AM

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=NHHGB9&role=attend

Presenter: Patrick LeBlanc, currently the Sr. Database Administrator for Lamar Advertising. I have worked as a SQL Server DBA for the past 9 years. My experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. I am the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Area SQL Server User Group.

Topic: Integrating Table-Valued Parameters with Reporting Services

Patrick LeBlanc, currently the Sr. Database Administrator for Lamar Advertising. I have worked as a SQL Server DBA for the past 9 years. My experience includes working in the Educational, Advertising, Mortgage, Medical and Financial Industries. I am the founder of TSQLScripts.com, SQLLunch.com and the President of the Baton Area SQL Server User Group.

If you would like to receive email notification about upcoming SQL Lunches go here:

http://www.sqllunch.com/Register.aspx

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South

Thursday, November 12, 2009

How did I become a DBA

This past Tuesday night I had the honor of speaking to a small group of college students who are all members of the Association of Information Technology Professionals (AITP). This was my third opportunity speaking to a group of college students and my second time speaking to an AITP group. The structure and order of the meetings are very similar. I start with a short introduction and continue by discussing some trends in Information Technology and my current career. This meeting began in a very similar fashion; however this time I was asked a question that I had never been asked. One student asked me, “How did you become a DBA?” It’s strange that the question had never been posed before. Nevertheless, I was up to the challenge.

In the mid 1990’s I began my career in corporate America as a mortgage underwriter. This was a very monotonous job. A few variations to the type of income documentation, credit level or appraisal, but overall the same set of information. One primary part of the job was traveling to various branch offices within the United States. During the travels an underwriter was expected to teach the Loan Originators employed at the branch how to build a loan package that contained only the information that was needed for the approval of the loan.

When the underwriter returned to his or her home office, the person was required to send emails to the each branch, listing each loan underwritten in the branch and a list of pended items for each loan. Typically an underwriter could spend a day or two composing the emails for each branch. At the time I was not a very good typist. So for me, this was a very daunting task. During my undergraduate studies I had taken an Introductory Information Technology course. One of the requirements was to develop an Access Database. I remembered how Access stored information that was later used for reporting and making decisions. At that moment I decided that I was going to design an Access database that would store this information and I would later use that information to somehow compose these emails.

The idea was great but there was one problem, I did not own a portable computer, or as we know them today as a LAPTOP. Fortunately my mother-in-law was always purchasing things she did not need, and fortunately during our last visit she had given me a TOSHIBA Satellite PRO (T2400CT). Don’t believe me check this out:

image image   I could not believe that it still worked.  Running Windows 95 nonetheless. 

Coupling my little laptop with an Access Bible, I developed a nice Access Database, forms and reports included, that I used to collect data when I was traveling.  I assigned the Macro to a button, and when the button was clicked an Email message was opened with a Word document attached that only contained data for a specified branch. The only thing left was to add a recipient and click the Send button. This reduced the time I spent composing these emails from 2 or 3 days to a couple of hours. In the end the CEO found out about my little database and decided that it should be used by all Underwriters. I explained to him that my book stated that Access should not be used by no more than 5 or 10 people concurrently. He suggested that we upsize it to SQL Server 7.0 for the backend and keep the front end in Access, which led to the beginning of my obsession with databases.  From that project I started developing larger databases and moved on to become a DBA for a small Mortgage Company in Baton Rouge, and the rest is HISTORY!!

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

SQL Down South

Wednesday, November 11, 2009

Baton Rouge SQL Server User Group

If you are in the Baton Rouge Area stop by and join our user group meeting this evening.  The details are below:

Topic:
Introduction to MDX

Date:
Wednesday, November 11, 2009

Speaker:

Barry Ralston

Barry is currently Vice President for Technical Solutions with Birmingham-based ComFrame Software. Since joining ComFrame in 2001, his client successes include Aflac, Honda, and the Children's Hospital of Alabama. In addition to speaking at the Alabama .Net Code Camps 1, 4 and 5, Barry has delivered presentations on Business Intelligence with Microsoft technologies at SQL Saturday 1 and 4.

Location:
Lamar Advertising, 5551 Corporate BLVD, Baton Rouge, LA 70808

Time:
5:30 pm

Overview:

Introduction to MDX

Prizes:
Wireless Keyboard and Mouse, Books, T-Shirts

Talk to you soon.

Patrick LeBlanc, founder TSQLScripts.com and SQLLunch.com

Tuesday, November 10, 2009

Article of the Week – Sanjay Mirsha (Data Compression)

Data Compression was introduced in SQL Server 2008.  This feature helps compress data inside the database, thus potentially reducing the size of the database.  In the article titled, Data Compression:  Strategy, Capacity, Planning and Best Practices, the author outlines several pertinent details regarding the implementation of this new feature.  Some topics discussed included:  What to Compress, Estimating Space Savings, Resource Requirements and the Side Effects of Compressing a Table or Index.  The article also includes a couple of scripts that will help you determine what level of compression should be used. 

To read the article in its entirety click here.

Talk to you soon

Patrick LeBlanc, Founder www.tsqlscripts.com and www.sqllunch.com

Monday, November 9, 2009

New Job with Pragmatic Works

Well if you attended PASS, you already know that I am changing jobs.  I recently accepted a position with Pragmatic Works.  I am honored to have the opportunity to work with Brian Knight and his staff over at Pragmatic Works.  For me this is somewhat of a career change.  Most of my career I primarily focused on the OLTP side of things.  In my new position I will be moving into the OLAP realm.  This is an exciting change for me and I look forward to the many challenges.

Talk to you soon

Patrick LeBlanc, founder, www.tsqlscripts.com and www.sqllunch.com

SQL Down South

Today’s SQL Lunch – Understanding and Preventing SQL Injection with Kevin Kline

Meeting URL: Join Meeting

Click Here to Add to Outlook Calendar

Click the above Meeting URL around 11:00 AM CST on 10/12/2009 to join the meeting

Date: 11/9/2009

Time: 11:30 AM

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=95MQQM&role=attend

Presenter: Kevin Kline: Kevin is the Technical Strategy Manager for SQL Server Solutions at Quest Software. A Microsoft SQL Server MVP, Kevin is a founding board member of PASS and the author of several books including “SQL in a Nutshell” (O’Reilly & Associates). Kevin is a top-rated speaker at industry trade shows and has been active in the IT industry since 1986.

Topic: Understanding and Preventing SQL Injection

SQL Injection attacks are one of the most common hacker tricks used on the web. Learn what a SQL injection attack is and why you should be concerned about them. Through demonstrations, witness different types of SQL injection attacks, how to find them, and how to block them.

If you would like to receive email notification about upcoming SQL Lunches go here:

http://www.sqllunch.com/Register.aspx

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South

Friday, October 30, 2009

PASS Presentation Rehearsal

My friend Trevor Barkhouse called me up a couple of nights ago and asked if I could host a Live Meeting Rehearsal of his PASS Presentation.  Is that a question, of course I can.  So if you have some time this morning join in a for a sneak peek.  The meeting details are as follows:

NOTE: The Live Meeting is set up from 9:00 a.m. through 11:30 a.m. (CDT), however the presentation is actually from 9:30 a.m. through 10:45 a.m. Please do not attempt to join the meeting until 2009-10-30T09:30:00.000-05:00!

SpeakerTrevor Barkhouse.  Trevor is a DBA on the Database Escalations and Implementations team for Terremark Worldwide, Inc.  He is also a volunteer on the board of the NTSSUG and maintains the groups website

Topic:  Leveraging PSSDiag/SQLDiag for Efficient Troubleshooting

Overview:  Over the years, Microsoft Customer Service and Support has developed a number of amazing tools for troubleshooting SQL Server. Thankfully many of these tools have been shared with the public. In this session I will demonstrate the configuration and usage of PSSDiag (for SQL Server 2000) and SQLDiag (for SQL Server 2005 and 2008), which collect valuable diagnostic data. We will then analyze the data using RML Utilities as well as a few scripts of my own. Come and see how these tools can save you massive amounts of troubleshooting time!

Meeting URL:  https://www.livemeeting.com/cc/usergroups/join?id=HD8QCW&role=attend

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

SQL Down South

Friday, October 23, 2009

SQLLunch – Accidental DBA and Performance Data Collector

If you haven’t had a chance to attend the SQL Lunch Live Meeting learning series then sign in Monday at 11:30 CST for our next meeting.  Barry Ralston will be presenting on the Data Collector. 

Speaker:  Barry Ralston

Time: 11:30 AM CST

Meeting URL:  https://www.livemeeting.com/cc/usergroups/join?id=3GBFMJ&role=attend

Add to Outlook Calendar

BIO: Barry is currently Vice President for Technical Solutions with Birmingham-based ComFrame Software. Since joining ComFrame in 2001, his client successes include Aflac, Honda, and the Children's Hospital of Alabama. In addition to speaking at the Alabama .Net Code Camps 1, 4 and 5, Barry has delivered presentations on Business Intelligence with Microsoft technologies at SQL Saturday 1 and 4.


Topic: #2-Accidental DBA and Performance Data Collector
A new feature in SQL 2008 may well improve the ability of “accidental DBA’s” to monitor, manage and tune SQL Server. This presentation will outline how to setup Performance Data Collector (PDC) and use the included Management Data Warehouse reports to take action on your database server based on information and not ‘gut feel’ or hunch.

If you would like to receive updates about upcoming meetings click here to sign up.

SQL lunch1 

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqllunch.com

SQL Down South

Wednesday, October 21, 2009

Wildcard – Match One Character using Underscore (_)

I was recently contacted by a developer with what I would consider to be a very simple question.  How do you search for string of the same length starting and ending with given characters.  My response was simple, use the underscore(_) Wildcard Character Match.  The person looked very puzzled from my response.  As a result, I wrote a quick example to clarify.  For some of you this may be second nature, but for many developers and some DBAs that I have spoken to, this was a new concept.  Therefore, I am sharing this information.

Scenario:  Suppose you have the following table

CustomerID FirstName LastName
1 Patrick First
2 Patrick Second
3 Pick Third
4 Luke Fourth
5 Patrick Tenth
6 Park Last

and you wanted to return all the customers whose name started with P and ended with a K.  One last thing, you only wanted customers whose first name contained Seven characters.  How would you accomplish this task.  If you used the following query:

SELECT * FROM Customers WHERE FirstName LIKE ‘P%k’

your results set would return the following rows:

CustomerID FirstName LastName
1 Patrick First
2 Patrick Second
3 Pick Third
5 Patrick Tenth
6 Park Last

This is because the % wild card character matches any string of characters.  If you modified the query to look like this:

SELECT * FROM Customers WHERE FirstName LIKE ‘P_____k’

then you result would be correct, returning only the rows.

CustomerID FirstName LastName
1 Patrick First
2 Patrick Second
5 Patrick Tenth

This is because I included 5 underscores as part of the character string.  These 5 underscores restricts the search to that number plus any additional characters that are specified within the character string.  In my case 2.  Therefore, my keyword search was limited to character strings that started with P, ended with K and strings that contained seven characters.

Monday, October 19, 2009

SQL Saturday #21 Orlando – Recap

This past weekend I had the pleasure of not only attending, but speaking at SQL Saturday in Orlando, FL.  When I arrived in Orlando on Friday I drove over to the SQL Share office and met Andy Warren and Jack Corbett.  Two great guys.  When I arrived at the office Buck Woody was giving a training session on Performance Tuning.  I spent most of the afternoon with Andy and Jack having lunch and picking up a few items for the event.  We discussed many topics, including the recent Board of Directors election, which I will leave for another blog posting.

I left Andy and Jack around 4:30 pm and decided to go and check in and answer a few emails before the speaker event.  The hotel was nice and centrally located to each event location, but about 36 miles from the airport.  When I arrived at the speaker event many of the speakers were already there.  I am not going to try and name each speaker, but Andy and Jack had a great list that included MVPs, book authors and columnists.  I had a chance to meet all the speakers and have some very interesting conversations throughout the evening.  After a long evening of talk I decided to call it a night.

The next morning I arrived at the event around 7:45AM.  There were several people already in line.  My session was not until 4pm, therefore I had time to do a lot of networking and attend other sessions.  I attended Brian Knights sessions on SSAS and MDX.  Both sessions provided me with some good information to get started building my own cubes and writing some MDX.  I also had an opportunity to spend about an hour chatting with Joe Celko, details of that conversation is forthcoming.  I also attended a session on Partitioning given by Elijah Baker, which was one of the best partitioning sessions that I have ever attended.  He has a new take on partitioning, at least new to me, that he is writing a white paper on. 

I started my session promptly at 4pm.  My turnout was very low, but the talk went on.  This was my second time presenting this topic, Using the CLR to Monitor Disk Space.  The attendees all seemed intrigued by the concept.  In this session I provide the attendees scripts, which include CLR Functions, that will allow them to proactively monitor Disk space from a central location.  If a drive on the server is running low on disk space an email notification will be sent to the DBA.  If you would like a copy of the scripts and the slides please email me at pleblanc@tsqlscripts.com

Overall the event was one of the best that I have attended.  My only criticism is that a few of the speakers went over on their time.  Everything else was great. 

Thanks Andy and Jack

Talk to you soon

Patrick LeBlanc, founder www.tsqlscripts.com and www.sqlllunch.com

SQL Down South

Monday, October 12, 2009

SQL Server Lunch Milestone (100+ Attendees)

Today’s SQL Lunch was a huge success, even though we experienced a few technical difficulties.  Tim Mitchell spoke on Deploying Report Builder 2.0 for Self-Service Reporting, which apparently is a very hot topic.  There were 100 people in attendance.  When I initially started the SQL Lunch I always hoped to reach as many people as possible.  Well it looks like it is paying off.  Thanks Tim for such a great presentation.  If you have yet to attend a SQL Lunch Meeting our next meeting is on October 26, 2009 at 11:30 AM CST time.  Here are the details:

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=3GBFMJ&role=attend

Topic: Accidental DBA and Performance Data Collector

Presenter: Barry Ralston. Barry is currently Vice President for Technical Solutions with Birmingham-based ComFrame Software. Since joining ComFrame in 2001, his client successes include Aflac, Honda, and the Children's Hospital of Alabama. In addition to speaking at the Alabama .Net Code Camps 1, 4 and 5, Barry has delivered presentations on Business Intelligence with Microsoft technologies at SQL Saturday 1 and 4.

Abstract: A new feature in SQL 2008 may well improve the ability of “accidental DBA’s” to monitor, manage and tune SQL Server.  This presentation will outline how to setup Performance Data Collector (PDC) and use

Click here to receive updates about upcoming SQL Lunches. 

If you would like to speak at upcoming SQL Lunch events please email me at pleblanc@tsqlscripts.com.  In your email include a short Bio, Title and Abstract.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQL Lunch

SQL Down South

Friday, October 9, 2009

Next SQL Lunch - Deploying Report Builder 2.0 for Self-Service Reporting

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=95MQQM&role=attend

Click Here to Add to Outlook Calendar

Click the above Meeting URL around 11:00 AM CST on 10/12/2009 to join the meeting

Date: 10/12/2009

Time: 11:30 AM

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=95MQQM&role=attend

Presenter: Tim Mitchell .Tim is a Microsoft SQL Server consultant, developer, speaker, and trainer.  He has been working with SQL Server for over 6 years, working primarily in database development, business administration, data integration, and automation.  He has earned a number of industry certifications and holds a Bachelor's Degree in Computer Science from Texas A&M at Commerce.

Tim is principal and senior data engineer for Tyleris Data Solutions, a business intelligence and data management consulting firm.  As an active member of the community, Tim has spoken at venues including SQL Saturday and the PASS Business Intelligence SIG, and is an active volunteer and speaker at the North Texas SQL Server User Group in Dallas.  Tim is an author and forum contributor on SQLServerCentral.com and has published dozens of SQL Server training videos on JumpstartTV.com.

Topic: Deploying Report Builder 2.0 for Self-Service Reporting

For the DBA, creating and maintaining reports can take up a significant amount of time.  Constantly changing requirements, an endless stream of ad-hoc requests, and simple formatting changes all add up to a good deal of maintenance.  To lighten the load, Microsoft offers SQL Server Report Buider 2.0, an easy-to-use reporting tool for building published and ad-hoc reports using SQL Server Reporting Services.  In this SQL Lunch presentation, we'll look at the capabilities of Report Builder 2.0 and how it can be used with SSRS to allow users to create and maintain many of their own reports.

If you would like to receive email notification about upcoming SQL Lunches go here:

http://www.tsqlscripts.com/sqllunch.aspx

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South

Baton Rouge October SQL Server User Group

Last night I presented, Monitoring Disk Space using CLR, to the Baton Rouge Area SQL Server User Group.  There were approximately 20 people in attendance.  I will be giving this presentation again next week at SQL Saturday #21 in Orlando, FL.  I was surprised that so many attendees were unfamiliar with the SQL CLR.  This really gave me a chance to identify anything that I may have omitted and any mistakes that were in the presentation or demonstrations. 

Overall the attendees appeared to follow the presentation and understand the concept.  I have added a few slides and rearranged a few things to accommodate a few requests and include some missing items.  If you are in the Baton Rouge Area and would like to attend our meeting register at http://batonrouge.sqlpass.org.

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQL Lunch

SQL Down South

Thursday, October 8, 2009

MSDE – Database Size Limit

Most of you are aware that the Microsoft SQL Server 2000 Desktop Engine (MSDE) limits database size to 2 gigabytes.  However, how do you know when it reaches that limit.  Well, the database becomes inaccessible.  Yesterday one of my clients called and said, “My SQL Server is not running, can you take a look?”.  Of course I could.  I logged into the server and the SQL Server was running.  Hmmmm, I thought.  The server did not have any client tools installed, so I had to logon to another machine and connect.

Before I did that I asked the client what was the specific error?  He sent me the following screen shot:

image

While reading the error I realized one thing, the SQL Server was running as a named instance.  Typically in our environment, this usually indicates that someone in the department installed an application that’s using a Desktop version of SQL Server.  I connected to the instance via Management Studio and ran, SELECT @@version, which returned the following:

Microsoft SQL Server  2000 - 8.00.760 (Intel X86)   Dec 17 2002 14:22:05   Copyright (c) 1988-2003 Microsoft Corporation  Desktop Engine on Windows NT 5.2 (Build 3790: Service Pack 2). 

I quickly noticed that it was an MSDE install.  A quick look in the error log validated my suspicions. 

2009-10-06 16:54:01.98 spid3     CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.
2009-10-06 16:54:02.00 spid3     Could not allocate space for object 'StatisticalMemory' in database 'WhatsUp' because the 'DEFAULT' filegroup is full.

The database had reached  the 2 gigabyte limit.  As a result, I stopped the SQL Server, copied the .mdf and .ldf files to another SQL Server (SQL Server 2005 Standard Edition) and attached it to that server.

Just a little blast from the past for all of you have that have ever experienced this.

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch

SQL Down South

Monday, October 5, 2009

SQL Lunch Starting at 11:30 AM CST 10/5/2009

TOPIC:  SQL Lunch - Memory Management in SQL Server Analysis Services

If you haven’t already, go out and register for today’s SQL Lunch.  Remember every attendee in the meeting will be entered in a drawing for a FREE full license of full license of SQL Sentry Performance Advisor (a $1,495 value)

clip_image001

SQL Lunch - Memory Management in SQL Server Analysis Services

Join us for a Webinar on October 5

Space is limited.
Reserve your Webinar seat now at:
https://www2.gotomeeting.com/register/958730107

In this presentation Steve Wright will discuss methods used to manage memory in SQL Server Analysis Services focusing primarily on memory limits.  He will also present some examples from the field of performance issues due to mis-configuration, how they were identified, and ultimately resolved.
About The Presenter:  Steve Wright - Steve is the Director of Product Support at SQL Sentry, which is located in the suburbs of Charlotte, North Carolina. He is a critical member of the development team behind the award winning SQL Sentry Event Manager and Performance Advisor. Steve holds MCSD, MCDBA, OCDBA 8i, and IT Project+ certifications. Prior to joining SQL Sentry in 2005, Steve developed and presented web based training courses for Wachovia Bank. Previous experience includes education positions at Techskills and Charlotte Mecklenburg Schools.

 

Title:

 

SQL Lunch - Memory Management in SQL Server Analysis Services

     

Date:

 

Monday, October 5, 2009

     

Time:

 

11:30 AM - 12:05 PM CDT

SQL Sentry is providing a door prize of a full license of SQL Sentry Performance Advisor (a $1,495 value).  SQL Sentry Performance Advisor® is a revolutionary new performance monitoring and tuning tool for Microsoft SQL Server 2000, 2005 and 2008. The software provides unparalleled insight, awareness and control over the true source of SQL Server performance issues. Performance Advisor is packed with many ground-breaking features, not found in any other software, such as Graphical Blocking and Deadlock Analysis, Patent-pending Graphical Monitoring of Disk Activity, Latency and Capacity, etc… all designed to simplify the process of optimizing SQL Server performance. Performance Advisor represents over two years of research and development by the same team that brought you the innovative, award-winning SQL Sentry Event Manager®.

After registering you will receive a confirmation email containing information about joining the Webinar.

 

System Requirements
PC-based attendees
Required: Windows® 2000, XP Home, XP Pro, 2003 Server, Vista

 

Macintosh®-based attendees
Required: Mac OS® X 10.4 (Tiger®) or newer

 

clip_image001[4]

My Top 10 SQL Saturday Tips

Top 10 SQL Saturday Coordination Tips

1.  First and probably the most important thing is to assemble a very responsible and dependable group of people to assist in coordinating the event.  These individuals must share the same level of commitment as the Primary Coordinator.  This I think is the most difficult task, but will prove to me the most vital component.

2. I recommend using a College campus as the venue for your event.  If possible, make sure that something is taking place in the building you are using.  This is to ensure that the facilities personnel and tech support is available.

3. Always obtain a tech support phone number for the facilities.

4. Arrive at the facilities as early as possible.  If you can, attempt to gain access to the facilities the day prior to your event.  Also, if you live in a very warm and humid area of the country, ask if the air conditioner can remain on overnight or can someone turn it on hours before your event.

5. Begin contacting sponsors and speakers as soon as you have secured a venue.  Be willing to accept SWAG in the place of money.

6. If you are offering a free lunch, order about 70% of the number of registered attendees.  It is also a good idea to make arrangement with the business to be on call.  If you have more attendees than lunches you can call them around 9 or 10 am and make a request for the additional lunches.

7. Never schedule a speaker for more than 3 sessions.  Always have back up speakers, a minimum of one backups per track.

8. Be sure to have really good directional signs strategically place on and around campus.  Especially if you are on a large college campus.

9. Prior to the start of each session, have someone visit each classroom to do a check.  Checking to make sure that everything is working properly and offering the speaker something to drink and any other assistance he or she may need.

10. Provide a very simple and self-contained lunch.  Something like a box lunch from your local Subway or sandwich shop.  A box that includes a sandwich, chips and cookie.  Don’t forget to order some Veggie sandwiches.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQL Lunch

SQL Down South

SQL Saturday #21 – Orlando

Well we are less than two weeks away from SQL Saturday #21 in Orlando, FL.  My bags are packed and I can’t wait to join so many SQL Professionals in a day of learning, sharing and networking.  If you are in the area and have not registered please do so.  The list of speakers is very impressive, which includes myself, and you don’t want to miss out on this great day of free learning.  Click here to register.

Event URL: http://sqlsaturday.com/eventhome.aspx?eventid=32

See you in Orlando

Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch

SQL Down South

Friday, October 2, 2009

SQL Lunch - Memory Management in SQL Server Analysis Services

EVERY ATTENDEE IN THE MEETING WILL BE ENTERED IN A DRAWING FOR A FREE full license of SQL Sentry Performance Advisor (a $1,495 value)

SQL Lunch - Memory Management in SQL Server Analysis Services

Sponsored By

clip_image001

Space is limited.
Reserve your Webinar seat now at:
https://www2.gotomeeting.com/register/958730107

In this presentation Steve Wright will discuss methods used to manage memory in SQL Server Analysis Services focusing primarily on memory limits.  He will also present some examples from the field of performance issues due to mis-configuration, how they were identified, and ultimately resolved.
About The Presenter:  Steve Wright - Steve is the Director of Product Support at SQL Sentry, which is located in the suburbs of Charlotte, North Carolina. He is a critical member of the development team behind the award winning SQL Sentry Event Manager and Performance Advisor. Steve holds MCSD, MCDBA, OCDBA 8i, and IT Project+ certifications. Prior to joining SQL Sentry in 2005, Steve developed and presented web based training courses for Wachovia Bank. Previous experience includes education positions at Techskills and Charlotte Mecklenburg Schools.

 

Title:

 

SQL Lunch - Memory Management in SQL Server Analysis Services

     

Date:

 

Monday, October 5, 2009

     

Time:

 

11:30 AM - 12:05 PM CDT

SQL Sentry is providing a door prize of a full license of SQL Sentry Performance Advisor (a $1,495 value).  SQL Sentry Performance Advisor® is a revolutionary new performance monitoring and tuning tool for Microsoft SQL Server 2000, 2005 and 2008. The software provides unparalleled insight, awareness and control over the true source of SQL Server performance issues. Performance Advisor is packed with many ground-breaking features, not found in any other software, such as Graphical Blocking and Deadlock Analysis, Patent-pending Graphical Monitoring of Disk Activity, Latency and Capacity, etc… all designed to simplify the process of optimizing SQL Server performance. Performance Advisor represents over two years of research and development by the same team that brought you the innovative, award-winning SQL Sentry Event Manager®.

After registering you will receive a confirmation email containing information about joining the Webinar.

 

System Requirements
PC-based attendees
Required: Windows® 2000, XP Home, XP Pro, 2003 Server, Vista

 

Macintosh®-based attendees
Required: Mac OS® X 10.4 (Tiger®) or newer

 

clip_image001[5]

Installer Cache – C:\Windows\Installer

Over the past few months I have been upgrading most of my SQL Server Reporting Services (SSRS) 2005 installs to 2008.  Recently, a developer found some issues with the upgrade and we concluded that SQL Server 2008 Service Pack 1 and a recent Cumulative Hotfix would resolve the problem.  About 1 minute into the service pack install the following dialogue box appeared:

image

After browsing to that directory I realized that not only was that file missing, but so was anything else that belonged in that directory.  This directory contains information that is needed when you upgrade, patch or remove several Microsoft Products.  In my case SQL Server Reporting Services.  To make things even worse, the contents of this folder is unique to each server and each SQL Server Reporting Instance that you install on that server.  As a result, the contents cannot be copied from one server to the other.

Unfortunately, I am not going to provide you the steps to solve this problem, because we found out that our server was in a very inconsistent state.  Therefore, it could not be fixed correctly.  I would like to applaud the efforts of the Microsoft Engineers.  They were relentless at their efforts. What I will tell you is that the Installer folder is hidden and it should stay that way.  Protect the contents of that folder with your life :). 

Talk to you soon

Patrick LeBlanc, TSQLScripts.com and SQL Lunch

SQL Down South

Thursday, October 1, 2009

Tips for Networking at PASS

This year will be my third trip to the PASS conference.  If you have met me or had any conversation with me, you know that I LIKE TO TALK.  If time permitted I would try to meet and talk with everyone in attendance at the conference.  When I speak at or attend a User Group meeting I try to meet everyone that was in attendance.  At PASS its almost impossible to accomplish this because there are so many people at the event.  As a result, I usually just meet as many people as possible.  So how do I meet these people?  I am so happy you asked.  This is what I do:

1.  If you want to meet someone like Andy Warren, Steve Jones, Kalen Delaney or Kevin Kline, you almost have to make an appointment, but we all know that is not possible.  What I do is arrive early to their session or I may even attend the session prior to their session.  Sometimes I sit outside the room prior to the start of the session.  What I have found is that these guys like to talk as much as I do, and the usually welcome a good conversation.

2.  Always have a stack of business cards and a notepad.  The first thing I do when I meet someone is give them my business card and ask for one in return.  If they don’t have one I hand them my note pad and ask that they write their contact information down.  Some people will and some will not, but all they can say is yes or no.

3.  Go to lunch!  There are so many opportunities for networking at lunch.  Most of the time when I sit at a lunch table there are 7 or 8 people sitting there in silence.  I quickly change that, because I introduce myself to each person and give them a business card, asking for one in return.  Believe it or not I do this everyday that I am there.  During lunch at my first PASS conference I met a group of DBAs that worked for Progressive Auto Insurance.  I actually was offered a job with Progressive.

4.  During a session I always sit next to someone that I don’t know.  Most of the time that person will start a conversation with you.  If not, simply introduce yourself and a conversation usually begins.

5.  Finally, if you are invited to a mixer at the end of the day, GO!  The Casino Night hosted by SQL Server Central is a great event and great opportunity to meet lots of people.

At the end of the day I return to my hotel and add all the people that I meet to my contacts list.  When I get back to the office I usually send out a follow-up email to each person that I met.  So if you see me at the conference please stop me,  I am always looking for a good conversation.  Be prepared to write your name down on my notepad if you don’t have a business card.

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch

SQL Down South

Monday, September 28, 2009

SQL Lunch Starting at 11:30 CST

If you are available for lunch today don’t forget to sign in and watch Thomas LeBlanc talk about Historical DMV information.  To attend the meeting use this URL:  https://www.livemeeting.com/cc/usergroups/join?id=F7DRFD&role=attend.  Also, if you would like to receive notifications about upcoming SQL Lunches register here:  http://www.tsqlscripts.com/SQLLunch.aspx

See you at Lunch

Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch

SQL Down South

Reporting Services - Disable Loop Back Check

I have been installing and configuring SSRS since the initial release.  I have always encountered several different caveats when upgrading to a newer version or adding a new server to a scale-out deployment.  In some cases there have been consistencies, but in many these differences vary.  These variances may have been attributed to the different installations and configurations.  One for example is disabling loop back check.  I have upgraded several SSRS 2005 instances to SSRS 2008 in the past couple of months and they were all very similar installations and configurations. 

My most recent upgrade required me to make a change to the registry.  I have performed this task in the past on my 2005 instances, but I had not encountered it in 2008.  I do realize that this is a setting at the OS level.  If you perform an upgrade and the Report Server URL is accessible, but you receive a 401 when trying to access the Report Manager URL follow the steps in Method 2 of the Solution section in this KB article.

url:  http://support.microsoft.com/kb/896861

Talk to you Soon

Patrick LeBlanc, Founder TSQLScripts.com and SQLLunch

SQL Down South

Friday, September 25, 2009

Upcoming SQL Lunch – Historical DMV Information

Date: 9/28/2009

Time: 11:30 AM

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=F7DRFD&role=attend

Presenter: Thomas LeBlanc. Thomas is a Database Administrator for Amedisys, Inc in Baton Rouge, LA. He has been in the IT field for 20 years starting as a COBOL programmer, graduating to dBase, FoxPro, Visual FoxPro, VB 3 thru 6 and even some .Net(C#). Designing and developing normalized databases has become his passion. Full-time DBA work started about 8 years ago for Thomas while working at a Paper Mill in St. Francisville, LA continuing with IEM, then Amedisys. Current activity at Amedisys involves 4 other DBAs

Topic: Historical DMV Information

DMV are great to find information about performance without running a trace. But there information is lost once the SQL Server service is restarted. After finding some useful scripts online (Thanks SQL Server community!!!) , I decided to schedule a job to run the night before to store Index Usage, Least Used Indexes, SP Usage, Missing Index and Expensive CPU Usage. We will review the scripts and historical data captured.

Click HERE to add to Outlook Calendar

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com and SQLLunch

SQL Down South

Trace Flag 1118

Yesterday morning around 6AM, my Blackberry started going off.  The subject line of every email was, “SQL Timeouts”.  In the past, this was a direct result of my Index Rebuild or Defrag nightly process.  However, since I have implemented a process that is able to recognize when the Index job is blocking, I knew this could not be it.  I quickly ran, the following script:


SELECT
    r.scheduler_id,
    r.cpu_time,
    r.session_id,
    r.command Command,
    t.text SQL_Statment,
    r.blocking_session_id Blocking_Session_ID,
    r.total_elapsed_time/1000 Total_Elapsed_Time_Seconds,
    r.cpu_time CPU_Time,
    s.login_name Login_Name,
    s.[host_name] [Host_Name],
    s.[program_name] [Program_name],
    s.memory_usage Memory_Usage,
    r.status [Status],
    db_name(r.database_id) Database_Name,
    r.wait_type Wait_Type,
    r.wait_time Wait_time,
    r.wait_resource,
    r.reads Reads,
    r.writes Writes,
    r.logical_reads Logical_Reads
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE
    r.session_id <> @@spid  --and blocking_session_id = -4
ORDER BY 2 DESC

As expected, there was a lot of blocking.  The strange thing was the –4 session_id.  I have experienced –2 session_ids and I understand how to fix this problem.  Click here for more information on –2 session_ids.  I had never experienced a –4.  After a few minutes I noticed (2:1:3) in the wait_resource column of the above query, which represents to DatabaseID, FileNum, and PageNum.  I stumbled upon KB328551, which suggest adding a Trace Flag 1118 and Increasing the number of data files in TempDB.  Increasing the number of data files was not a problem, however I was skeptical about adding the trace flag since the article applied to SQL Server 2000 and we are currently running SQL Server 2008.  Adding the trace flag forces SQL Server to use uniform extent allocations instead of mixed page allocations.

SQL Server uses a special allocation page called a Shared Global Allocation Map (SGAM) to find unallocated pages in mixed extents.  When there are several concurrent processes that need to access the SGAM, in our case 4000 instead of 100, the SGAM could become the bottleneck.  This is because each process will need to access the SGAM several times. 

Since this was my production system I decided to place a call to Microsoft.  While I was on hold I found out that a new process that increased calls to the database by a magnitude of 100 was placed into production on the previous evening.  When the Microsoft Engineer came on he poked around for about 15 minutes and then he suggested adding, what else, Trace Flag 1118 as a startup parameter.  I was still a little skeptical about adding the trace flag because the article was so specific to 2000, but the engineer pointed me to a section of the KB that also suggested to use of the trace flag for SQL 2005 and 2008.  Even further, I found a page by Microsoft Customer Server Support (CSS) recommending the usage of the trace flag to assist in TEMPDB scalability.  We added the trace flag as a startup parameter for SQL Server and restarted the SQL Service.  Before we added the trace flag I had the developer stop the process that was making the thousands of calls to the database.  When I restarted the Service everything was up and running.  I asked the developer to restart his service.  After a few minutes the –4’s returned.  They were not producing as much contention as before and all the systems were still available, but it was slightly slower than normal.  As a result, we disabled only the part of the process that was causing the contention, reworked it and redeployed.  Plans have been made to add additional database files to TEMPDB also.

Explaining why this problem occurred and how the Trace Flag and additional data files helped solve the problem is beyond the scope of a blog posting. However, reading the KB article, the Blog posting from CSS and this article http://technet.microsoft.com/en-us/library/cc966545.aspx will provide good information regarding these issues.

Talk to you soon

Patrick LeBlanc Founder TSQLScripts.com and SQLLunch

SQL Down South

Wednesday, September 23, 2009

SQL Lunch – Pick your favorite Logo

We have been working on the logo for a month now, but instead of making the final decision I have decided to leave it up to the public.  I have posted it out on my FaceBook page and sent out a few emails.  We have narrowed the choices to the following two images.  Choose your favorite:

 

SQL lunch1-bw SQL lunchRead

The quality of the images are much better.  Also the first image has a transparent background, so you will see the entire knife.

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com and SQL Lunch

SQL Down South

Monday, September 21, 2009

TSQLScripts.com – Script of the Week

After about 3 weeks of digging through .css files and html code I finally found the problem with the site.  The Master page was missing a small tag at the end that stopped the Content Area from expanding to 100% in height.  Now that all of that is fixed, I will try my best to provide you all with a weekly script.  This weeks script comes from the Latest publication of TechNet.  In an article written by Paul S. Randall, he provides a script that tells you how many 8KB pages are in the buffer pool for each database. 

Go here to see the script.  It has been modified just a little to allow for further calculations.  He discuss this more in his blog posting, What’s in the Buffer Pool.

Check both out.

Talk to you soon

Patrick LeBlanc, founder TSQLScripts.com and SQLLunch

SQL Down South

Upcoming SQL Lunch (Live Meeting) – Historical DMV Information

The next SQL Lunch is scheduled for Monday, September 28.  See below for details

Date: 9/28/2009

Time: 11:30 AM

Meeting URL: https://www.livemeeting.com/cc/usergroups/join?id=F7DRFD&role=attend

Presenter: Thomas LeBlanc. Thomas is a Database Administrator for Amedisys, Inc in Baton Rouge, LA. He has been in the IT field for 20 years starting as a COBOL programmer, graduating to dBase, FoxPro, Visual FoxPro, VB 3 thru 6 and even some .Net(C#). Designing and developing normalized databases has become his passion. Full-time DBA work started about 8 years ago for Thomas while working at a Paper Mill in St. Francisville, LA continuing with IEM, then Amedisys. Current activity at Amedisys involves 4 other DBAs

Topic: Historical DMV Information

DMV are great to find information about performance without running a trace. But there information is lost once the SQL Server service is restarted. After finding some useful scripts online (Thanks SQL Server community!!!) , I decided to schedule a job to run the night before to store Index Usage, Least Used Indexes, SP Usage, Missing Index and Expensive CPU Usage. We will review the scripts and historical data captured.

As always, if you would like to receive notifications about upcoming SQL Lunch Live Meetings please register here:  http://www.tsqlscripts.com/sqllunch.aspx.  Also, if you would like to speak at a SQL Lunch please email me at pleblanc@tsqlscripts.com.  Remember these are short meetings designed to help new speakers improve their skills.

Talk to you soon

Patrick LeBlanc, founder SQL Lunch and TSQLScripts.com

SQL Down South

Friday, September 18, 2009

SQL Server 2008 R2 August CTP – Utility Control Point (UTP)

I finally carved out some time in my day to try and get the August CTP of SQL Server 2008 R2 installed.  I really decided to get this done after a read the short blurb in the latest eWeek Magazine titled, First Look At Microsoft SQL Server’s Future, which led me here http://tinyurl.com/lsx9qn.  The slide show provided screen shots of SQL Azure, new Reporting Builder Features and my favorite, the Utility Control Point (UTP).  UTP provides new feature that helps DBAs deal with the challenge of managing a multi-server environment.  After the installation complete I could not wait to get started testing the UTP out on some servers in my environment. 

I am always looking for a great tool that provides centralized server monitoring and data collection.  We have owned licenses to a couple, but recently discontinued them.  I have been running the Data Collector, but it still requires me to go to every server and enable and configure data collection.  The UTP allows you to Enroll an Instance of SQL from the Control Point.  So, if you haven’t installed SQL Server 2008 R2, give a try and look at the UTP.  There is one known bug that I encountered.  When you open the Storage Utilization for a Managed Instance the Utility throws and exception.  You can find the Workaround here:

http://social.msdn.microsoft.com/Forums/en-US/sqlkjappmsmgmt/thread/03506a9b-e9b1-4d77-a3e1-28627e8f4867

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com and SQLLunch

SQL Down South

Wednesday, September 16, 2009

Article of the Week – Kevin Kline “Social Media Scene”

This weeks article is written by Kevin Kline, which is titled “Getting Up-to-Speed on the SQL Server Social Medial Scene”.  This article is published in the latest edition of Database Trends and Applications.  In the article Kevin discusses the new social media or networking sites that are available.  Kevin discusses his initial skepticism about social networking, and how the tools could be a great way to “dither away some valuable time.  However, as he suggests, they can provide great value-add by means of connecting with other individuals and at the very least, see what everyone else is up to.  I to had my own concerns about social networking, similar to what Steve Jones posts in his blog posting about Face Book

After poking around on Face Book, I realized that it was a great tool that allowed me to connect with other people that share my many interests.  A great example for me is how it helped my spread the word about our recent SQL Saturday.  Kevin discusses several other social networking tools like, Twitter and Linked In.  I have not become part of the Twitter wave, but I am looking into joining.  When you have sometime go and read the article.

http://www.dbta.com/Articles/Columns/SQL-Server-Drill-Down/Getting-Up-to-Speed-on-the-SQL-Server-Social-Media-Scene-56042.aspx

Thanks

Monday, September 14, 2009

Windows Internal Database

This past Friday I received a call from our systems team stating that they were running low on Disk Space on a SQL Server server that their team used for Share Point.  He stated that there was an .LDF file that was really large, and could he just delete it.  Yeah, I said jokingly, but quickly retracted the statement because he was on his way.  I knew that the offending databases recovery model was set to Full and that they were not backing up the Transaction Log.

After getting a little information about the system, I determined that I could change the recover model to simple.  I logged into the machine to find the SQL Server instance name.  To my surprise I did not see a SQL Server running on the box.  I did however notice that a Windows Internal Database service was running as a Service on the Machine:

 

image

I immediately did a web search (Windows Internal Database), which resulted in several great listings.  I am not going to repeat what the articles stated, but I am going to provide a great link from MSSQLTips.com that helped me solve my problem.

Administering your Windows Internal Databases (Microsoft##SSEE) instance.

I installed SSMS Express, connected to the instance and changed the recovery model to Simple, problem solved.

Talk to you soon

Patrick LeBlanc, found TSQLScripts.com and SQLLunch

SQL Down South.

Today’s SQL Lunch - Oracle-CDC for SQL Server Integration Services

Join us today for a SQL Lunch

Date: 9/14/2009

Time: 11:30 AM

Reserve your seat: https://www1.gotomeeting.com/join/509742840

Presenter: Jeff Cole. Jeff has been designing and building information technology systems for over 20 years. He has experience across many industries including banking & finance, health care and telephony using a variety of mainframe languages as well as the gamut of Microsoft development environments. He has been working lately in the middleware space developing systems on multiple platforms using integration technology to solve complex connectivity problems. Jeff is currently working with Attunity to help customers modernize legacy systems and make the most of their technology investments with an emphasis on the Microsoft technology stack including SQL Server and Integration Services.

Topic: Oracle-CDC for SQL Server Integration Services.

Leverage your SQL Server skills and software to reduce cost and accelerate Oracle data replication, synchronization, and real-time integration and enable operational reporting, business intelligence and data warehousing projects.

 

Talk to you soon

Patrick LeBlanc, Found TSQLScripts.com and SQL Lunch

SQL Down South