Monthly Archives: April 2011

Pittsburgh SQL User Group is Back!

It was an honor to be the first presenter for the reboot of the Pittsburgh SQL Server User Group on Tuesday.  I gave a sneak peek into my SQL Rally presentation, “Evaluate your daily checklist against 100+ servers while you get a cup of coffee.” I knew it was a success when I got home and saw the following tweet.

This tweet is purely SQLWinning

This tweet is purely SQLWinning

If you attended the session feel free to download the slide deck and check out my reference material.

Question and Answers

I get a kick out of helping people solve problems. If I don’t know the answer this gives me the opportunity to learn something new. Here are a few questions that were asked during my session. I didn’t have the answers of the top of my head so I am including them in this post.

Q: “How can I automate the process of shrinking a T-Log?”

A: First I highly recommend reviewing your backup and recovery plan. Frequent backups of the transactional log is key to free up your VLF’s. If there isn’t a free VLF available the log grows. With that said, if you just want to automate the process of shrinking T-Logs check out Jeremiha Peschka’s script

Q: “How do I run a T-SQL script against all databases on a single instance?”

A: First, I was thinking about doing a simple loop in PowerShell to execute the T-SQL (see Aaron Nelson’s second script) but then I remembered an undocumented stored procedure. The undocumented stored procedure is sp_msforeachdb. Both options should work.

Photos

Below is a few photos taken before I started the presentation.

Right side say, "cheese"

Right side say, "cheese"

Left side say, "gouda"

Left side say, "gouda"

Woot! I am in InWheeling Magazine

 

Woot! I am in InWheeling Magazine. Get your copy today!

Woot! I am in InWheeling Magazine. Get your copy today!

Recently I was recommended to take part in the “Digital Generation” article in a very popular magazine in the Ohio Valley. This was my first invite to participate in a magazine so I was all in.  The article profiled several people in the community and explained how digital technologies  impacted their lives. I was shocked to find out I was the only IT Pro selected for this article. We have a lot of great IT Pro’s in the Ohio Valley so this makes the invite extra sweet.

For fun I am including the published profile and the questions I answered. If you enjoy the clip below and are intersted in how technology has improved Wheeling buy a copy.

Q:   We need some basic information: Name as you wish it to appear in the article, Birthplace, Occupation (if willing to share) and Current city of residence.

A: John Sterrett is proud to call Wheeling, WV home. He currently resides in Elm Grove, but was born in Glenwood Springs, CO and moved around a lot as a kid while his dad worked in the oil and gas industry.  John was raised in Bakersfield, CA and went to four high schools in Farmington, NM, Dallas, TX and Houston, TX. He received a Bachelor of Science degree in Computer Engineering at the University of Oklahoma. In his professional career John has had jobs as a software developer and database administrator at Deloitte, highschoolsports.net and Orrick Herrington & Sutcliffe.

Q:   Describe in detail how the digital revolution has affected your life (were you asked to participate in this because of an online business, job, social medium, meeting spouse online, was this the reason you moved to the area, etc.).

A:In 2006 I moved to Wheeling, WV to work for ScheduleStar, the company that ran highschoolsports.net, as a software developer. I learned a lot about the dotcom industry.  I did well and had a great time. I will always remember Adolph Santorine bringing in investors and introducing me as his Senior Developer.

Recently I have leveraged new technologies and social media to progress my career, professional and personal life more than I would have ever imagined. At work I rely on Twitter to connect, share and learn from database administrators around the world. I can immediately solicit advice to solve problems that arise. I use my online blog to share what I have learned with others in my field. Twitter, Facebook, and blogging also help get the word out about events hosted by the Greater Wheeling Chapter of AITP; without them it would have been impossible to attract professionals to our first SQL Saturday event, a free conference for SQL users hosted in Wheeling last spring. SQL Saturday brought in 75 attendees, 9 speakers for 13 sessions. In my personal life, we rely on Skype to connect with our family members who live all over the country. It has allowed our parents to share in our excitement as my wife’s pregnancy progresses. Frankly, it’s amazing how much technology is changing how I do things and connect with people. I am constantly plugged in and I’m not sure how I would function without technology.

Q:  Tell us how you stumbled into this?  What were you doing before this impacted your life?

As a software developer and database administrator I relied on the internet to read about new technologies and innovators within my field. About two years ago I noticed many of my peers were using Twitter and blogging to communicate their ideas and problems. I want to be the best at what I do and beginning to Tweet and starting my own blog followed naturally as I aimed to strengthen my own presence in our field.

From my early youth I have always been interested in computers and despite my parents warnings not to touch our new computer I quickly became the family computer expert at the age of eight; I’ve been hooked on technology ever since. I later taught myself how to develop software by reading books at the public library and used my mother’s business phone at night to run a bulletin board system before the internet became mainstream.

Q:  What would you be doing or where would you be if it were not for the digital impact on you life?

A:Without Twitter, Facebook, Skype, message boards and blogging I would still be working with technology but I wouldn’t be as efficient or as valuable as I am today.  These technologies have given me a voice in my community and without them I might be the typical IT geek stereotype, a quiet guy with some computer skills who keeps to himself. I would probably need to limit my participation in my professional community because I would have to spend significantly more time, energy and money to promote and attend fewer activities.

Changing SQL Server Job Owners against the SQL Server Enterprise

Last night on twitter an interesting question was asked by @just_samson using the #SQLHelp hash tag. He asked, “can you change the owner for jobs &dbs across instances using Policy Based Management and Central Management Server?”  The answer to this question is no because an agent facet doesn’t exist. Users cannot create facets.  Does this mean he is dead out of the water? No, because good old T-SQL and CMS can be leveraged to get the job done.

Today we are going to focus on changing SQL Agent Job owners.

How do we find owners?


SELECT
    sv.name AS [Name],
    sv.job_id AS [JobID],
    l.name AS UserName
    FROM
    msdb.dbo.sysjobs_view AS sv
    INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
    ORDER BY
    sv.[Name] ASC

Here is a screen shot from my demo so you can follow along.

image

How do we change the job owner for all jobs?

You can see that the first job “Device by Zero” has the owner name set to “PBMDEMO\Administrator.” In this example we will want to change the owner to be “sa”. The following script below will do this. The only problem is you would have to run this script against every instance. That’s where the Central Management Server comes into play.

** This script is used as demo. It will only work against SQL 2005\2008\2008R2. Run it on development before you consider using it in production. If you decide to run it in production you are on your own. Run it at your own risk. **


DECLARE @JobID uniqueidentifier
DECLARE @NewOwner varchar(200)
DECLARE @OldName varchar(200)

SET @NewOwner = 'sa'
SET @OldName = 'PBMDEMO\Administrator'

SELECT
sv.name AS [Name],
sv.job_id AS [JobID],
l.name AS [OwnerName]
INTO #SQLJobs
FROM
msdb.dbo.sysjobs_view AS sv
INNER JOIN [master].[sys].[syslogins] l ON sv.owner_sid = l.sid
WHERE l.name like @OldName
ORDER BY
sv.[Name] ASC

SELECT * FROM #SQLJobs
WHILE (SELECT COUNT(*) FROM #SQLJobs ) > 0 BEGIN
    SELECT TOP 1 @JobID = JobID FROM #SQLJobs
    EXEC msdb.dbo.sp_update_job @job_id= @JobID,
        @owner_login_name=@NewOwner
        DELETE FROM #SQLJobs WHERE JobID = @JobID

END

DROP TABLE #SQLJobs

Now assuming you have Central Management Server configured just right click on the group of servers you want the change to be applied to and select “New Query.” Copy and paste the code and you should be able to execute the query.  Below are some screenshots from my demo incase you are new to Central Management Server. We will apply the script against the Production group.

image

image

Finally if we execute the initial query we will see that all jobs have “sa” as the owner.

image

Other Related Articles:

Being proactive with Central Management Server

As promised in previous blog posts I am cranking out some videos to prepare for SQL Rally.  If you aren’t registered for SQL Rally register now. The price jumps up after April 30th. Alright everyone its time to get Proactive.

The goal for this video is very simple. I just want to show you how easy it is to execute a query against your SQL Server farm.  In this example I get proactive by finding database files that are low on space. Once, I find them I can extend them so we aren’t fragmenting our drives by depending on the autogrowth settings.

Central Management Server is a very simple but powerful tool.  To get a taste check out my video below.

[UPDATE 9/14/2011]

The query used in the video above can be found here.  If you liked this video checkout my tip on automating data collection with PowerShell and Central Management Server

SQL Saturday #80 date changed

We change the date of SQL Saturday #80 from July 16th to July 23rd.

We are still early in the game so we hope this will not be an inconvenience to anyone. We sent out an email to everyone who signed up on the website. With that said, I still wanted to explain why we changed our date.

So.. Why did you change the date?

If you are like me you love SQLSaturday events and you are willing to travel to get your learn on. We changed the date of our event for the people who are willing to travel. Looking at our attendance roster from SQLSaturday #36 we noticed the majority of the attendees were travelers.

The event planners have a secret for you. We are not country music fans so we forgot about the Super Bowl of Country Music.

SQL Saturday comes back to Wheeling, WV

SQL Saturday comes back to Wheeling, WV

Its almost impossible to get hotel rooms that weekend so we pinged our host facility West Virginia Northern Community College and PASS. Once we got permission from both we pushed our event back one week.

This works out great because you can now experience our best festival while you are in town. If you like Italian food or carnivals be prepared to get a side order of fun with your free SQL Server training.

Lesson Learned

In the future we will make sure we coordinate with several group in the community to make sure we select the best date for our next SQL Saturday.

Meme Monday #1 – Proactive Monitoring

Tom LaRock (Blog |@SQLRockstar) a good friend of mine is starting meme monday. The concept is simple, try to get people writing. The first installment is write a SQL blog post that tells a story in 11 words or less.  

Proactive monitoring is key, policy-based management and CMS is easy.

Tom asked that we tag people to get this party started. I am tagging Matthew Velic (Blog |@mvelic)  and David Stien (Blog | @Made2Mentor)

Stay tuned as I plan to flush out some blog posts in April to help make you be proactive with your whole SQL Server farm using native tools.