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
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
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
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
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:
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
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
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
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:
Talk to you soon
Patrick LeBlanc, Founder TSQLScripts.com and SQLLunch
SQL Down South
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.
Thanks
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:
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.
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
We have several “Home Grown” applications at my current place of employment. This provides our development staff with a great deal of flexibility. We are allowed to upgrade to new versions of software at our own schedule. We have full control of the source code and can modify it to accommodate the needs of our end-users as needed. Recently, I put in a small change request. I am currently using a suite of data visualization components for SSRS and Visual Studio provided by Nevron. During my development of a small .Net application that will monitor % Processor Times on my SQL Servers using a Gauge component I noticed a property of the SQLConnectionStringBuilder that could be very useful to a SQL Database Administrator.
Currently, when I look at the SQL Profiler for any of our .Net applications the Application name is listed as .Net SqlClient Data Provider. This is because we do not set the ApplicationName Property on our connection string. Therefore, if a particular application is suffering from performance problems, we (the DBA) sometimes will have to sift through very large trace files to identity the problems. We do have other means of other identifying problems, but the Trace is a very helpful tool. So here is the request…
My request was to have the Application Developers set the ApplicationName on their connection strings. I am not sure how soon it will be implemented, however I am sure we will see benefit from the change. The following is a small code snippet of how I set it.
As you can see from the above snippet I set the application name to Nevron SQL Dashboard. As a result, I can filter my trace on that application and it only returns calls to the database from that application. See the sample trace below:
As you can see in the ApplicationName column, only events specific to my filter are returned.
Talk to you soon
Patrick LeBlanc, founder TSQLScripts.com and SQLLunch
SQL Down South
We have been working hard trying to get speakers to complete the year for our SQL Lunch schedule. I am excited to announce the beginning of the Learning Series. Our next meeting is on 9/14/2009 at 11:30 AM. If you would like to receive updates about upcoming SQLLunch meetings go here: http://tsqlscripts.com/sqllunch.aspx. To reserve your seat for this meeting go here: https://www1.gotomeeting.com/join/509742840. The details for the meeting are as follows:
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.
Upcoming Events
| Date | Meeting | Speaker | Topic | Time |
| 9/28/2009 | SQL Lunch | Thomas Leblanc | Historical DMVs | 11:30 AM CST |
| 10/7/2009 | SQL User Group | Barry Ralston | Introduction to MDX | 5:30 PM CST, 5551 Corporate BLVD, Baton Rouge, LA 70808 |
| 10/12/2009 | SQL Lunch | Tim Mitchell | Deploying Report Builder 2.0 for Self Service Reporting | 11:30 AM CST |
| 8/14/2010 | SQL Saturday | Various | .Net and SQL | All Day |
If you are interested in speaking at any of these events or sponsoring any of these events please email me at pleblanc@tsqlscripts.com. To be removed from the mailing please email me at the same address.
SQL User Group site: http://batonrouge.sqlpass.org
SQL Lunch Calendar: http://batonrouge.sqlpass.org/SQLLunch/tabid/1788/Default.aspx
Talk to you soon
Patrick LeBlanc, Founder Tsqlscripts.com and SQL Lunch
SQL Down South
I have not worked with SQL Server 7.0 in a loooong time. So when I received a call from a client complaining about their Transaction Logs not backing up, I was stuck for a few minutes. My initial thought was check the recovery model, but as I jogged my memory about recovery models I remembered that they were a new feature introduced in SQL Server 2000. I did on the other hand, remember that SQL Server 7.0 did have an option, Truncate log on checkpoint, that would prevent Backing up the Transaction Log. See the image below:
After my brief walk down memory lane, I asked the client to check this and as expected it was enabled. Once they disable the option, the backups began to work. So for all of you out there that are still using SQL Server 7.0 here is something for you.
Talk to you soon
Patrick LeBlanc, founder TSQLScripts.com and SQL Lunch
SQL Down South
On Monday August 1st I had the pleasure of speaking to a group of college students that all belong to the AITP. I always enjoy talking to college students because they are always inquisitive. They all want to know how to find jobs. Unfortunately, that is a tough question to answer. My response is usually to distinguish yourself from every other person, by obtaining a certification, doing some volunteering and joining organizations. This will give you an advantage over the numerous individuals that will be graduating and beginning the job search along with you.
I spoke to approximately 50 students and many seem to be attentive and receptive to the presentation. There was a mix of students, about half were IT students and the other half business students.
Talk to you soon
Patrick LeBlanc
SQL Down South
Well looks like I will be packing my bags for Sunny Orlando. I Just found out that my abstract was selected for SQL Saturday #21 in Orlando, FL. Last week or maybe the week before I decided to submit a session, Using the CLR to monitor Disk Space. My family and I usually travel to Orlando about once a year. Unfortunately, this year I had to miss the trip because of knee surgery. Well thanks to Andy and his team I will get to make a trip after all. Even though its an abbreviated trip, its still a trip, maybe I will stay a couple of days extra and visit Mickey and Friends.
If you are in the Orlando area or if you think you can get there come out to SQL Saturday #21. See you there.
Talk to you soon
Patrick LeBlanc, Found TSQLScripts.com and SQL Lunch
SQL Down South
I have been following this series of articles since the first article was published on SQLServerCentral. The author, Arshad Ali, has written several articles on SQL Server 2008 T-SQL Enhancements. The latest of the five articles was one of the featured articles on Monday, August 31st (www.sqlservercentral.com). Arshad not only provides a very extensive list of the enhancements, but he also provides a detailed explanation of each topic and provides examples.
The latest installment in the series highlights the Spatial Data Type. Since I work for an outdoor advertising company, geospatial data is crucial to our daily data needs. As a result, I have begun researching and testing certain aspects of this feature. Arshad provides a few examples that I will definitely add to my list of items to test. Take a look at the entire series I am sure you will find it worthwhile.
Talk to you soon
Patrick LeBlanc, founder TSQLScripts.com and SQL Lunch
SQL Down South
Wow, I just got an email from a registered member of www.tsqlscripts stating that it is not compatible with IE 8 and Firefox. I am going to spend the weekend working on this and I will hopefully have it resolved by Monday. Thanks for the heads up.
Talk to you soon
Patrick LeBlanc Founder TSQLScripts.com and SQLLunch
SQL Down South
