|
|
Challenges:
- Coding on Project B
- A couple of fires with other projects
Good Reading from the Week:
The Week Ahead:
- Project B coding.
- Project A issues?
Goals for the Year – 3 Ps – (These are my year long goals that I’ll report on each week to keep me focused, read about them here.)
- PASSMN – UG Meeting last week – went well: A- for the past week
- Powershell – Wrote script to replace text in a bunch of files: A- for the past week
- Present – Was inspired to speak at PassMN meeting, no action: D for the past week
Today I realized I made a fairly significant mistake. I had misspelled a column name in a bunch of procedures and views that I had updated. Not a big deal, except each procedure and view was a file and I had about 75 files. Only a few of them contained this change, I don’t know which ones, but most of them had other changes. What to do…
- I could manually check all files with a few routine of Keystrokes – that might take an couple of hours.
- I could check sysdepends for dependancies – nope none of the procedures have been executed.
- I could check syscomments for the column name – that would kind of work, but I would still need to manually update it.
- Powershell!! I could find and replace in the files and execute them as well!
I used the following sites as they have good examples.
Here’s what I came up with:
##Finds all the occurrences in the Views folder##
foreach ($f in Get-ChildItem -path "C:\Temp\Views\" -Filter *.sql | sort-object)
{$Location = $f.fullname
Select-String -path $Location -pattern "Andee"| out-file -filePath "C:\Temp\ListoObjects.txt" -append}
##Replaces all the occurrences in the Views folder##
foreach ($f in Get-ChildItem -path "C:\Temp\Views\" -Filter *.sql | sort-object)
{(Get-Content $f.fullname) |
Foreach-Object {$_ -replace "Andee", "Andy"} |
Set-Content $f.fullname}
Free training from two SQL Server MVPs Presenting tonight in Bloomington!
Location: 8000 Norman Center Drive, Bloomington, MN 55437 – We will be meeting in the 8000 building this month. Note, no live meeting from this location.
Sponsor: Benchmark Learning – also offering a 10% to all PASSMN members to the class “SQL Server 2008 Architecture, Internals and Tuning” with Kalen Delaney
RSVP Here
Agenda:
- 4:30-5:00 : Registration / hospitality / networking
- 5:00-5:15 : Announcements etc.
- 5:15-6:00 : Jason Strate (blog/twitter): Error Logs and Deadlocks, Oh My!
- 6:00-7:00 : Kalen Delaney (blog/twitter): Seeking SQL Server Secrets
Presentation:
Error Logs and Deadlocks, Oh My!
Tired of turning to the SQL Server error log to find out what’s happened? Would you rather just know about a deadlock when it’s occurred rather than later in the day when the manager as you why HRs bonus distribution application failed? Find out how to get this information and more delivered to your inbox so that you can head off issues instead of drowning in them. In this session we’ll look at Event Notifications and find out how to stop having to monitor SQL Server error logs today.
Jason Strate, SQL Server MVP, is a DBA with over twelve years of experience. His experience includes delivering both OLTP and OLAP solutions as well as assessment and implementation of SQL Server environments for best practices, performance, and HADR solutions. He is a SQL Server MCITP and participated in the development of Microsoft Certification exams for SQL Server 2008. Jason has presented at the SSWUG Virtual Conferences, TechFuse, SQL Saturday events, and at PASSMN user group meetings.
Presentation:
Seeking SQL Server Secrets
Many experienced DBAs are aware that there are many undocumented commands and traceflags in SQL Server, and some DBAs may even keep track of all references to such options whenever they read about them in an article or blog posts, or hear about them at a conference. In this session, I’ll not only tell you about some of my favorite undocumented features, I’ll also tell you some of my tricks for discovering more undocumented secrets.
Kalen Delaney has been working with SQL Server for 23 years, and provides advanced SQL Server training to clients around the world, using her own custom-developed curriculum. She has been a SQL Server MVP since 1993 and has been writing about SQL Server almost as long. Kalen has spoken at dozens of technical conferences, including every PASS conference in the US, since the organization’s founding in 1999. Kalen is a contributing editor and columnist for SQL Server Magazine and the author or co-author of several of the most deeply technical books on SQL Server, the most recent being SQL Server 2008 Internals, from Microsoft Press. Kalen blogs at www.sqlblog.com and her personal website and schedule can be found at www.SQLServerInternals.com.
Challenges:
- Coding on Project B – checked a bunch of stuff in, working with developers, ironing details out.
- On call – pager was pretty quiet.
- More tweaking of Powershell script to deploy all DB changes locally
Good Reading from the Week:
The Week Ahead:
- Project B coding.
- Project A2 still needs some of my time.
- PassMN Meeting Tuesday.
Goals for the Year – 3 Ps – (These are my year long goals that I’ll report on each week to keep me focused, read about them here.)
- PASSMN – Meeting this week: C for the past week.
- Powershell – Worked through some issues A- for the past week
- Present – No progress – NA for the past week
Today I was asked which procedure (of two with similar names) is used in production. Looking at the naming convention, I had a guess, but I needed proof as we are looking to clean up old unused procedures. So I found this article Monitoring Stored Procedure Usage at Database Journal. I modified the script to this:
Select DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) = '<<SET DBNAME HERE>>'
and cp.objtype = 'proc'
and OBJECT_NAME(st.objectid,dbid) in ('<<LIST PROCS HERE>>')
Group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by max(cp.usecounts) desc
Challenges:
- PASSMN January Meeting – details are ironed out
- Coding on Project B – altering procedures for schema changes – still going…
- Tweaked Powershell script to deploy all DB changes locally for easier troubleshooting
Good Reading from the Week:
The Week Ahead:
- Project B is still behind (Broken Record).
- Project A2 still needs some of my time (Broken Record).
- On Call for Production – really this week I am.
Goals for the Year – 3 Ps – (These are my year long goals that I’ll report on each week to keep me focused, read about them here.)
Location: 8000 Norman Center Drive, Bloomington, MN 55437 – We will be meeting in the 8000 building this month.
Sponsor: Benchmark Learning – also offering a 10% to all PASSMN members to the class “SQL Server 2008 Architecture, Internals and Tuning” with Kalen Delaney
RSVP Here
Agenda:
- 4:30-5:00 : Registration / hospitality / networking
- 5:00-5:15 : Announcements etc.
- 5:15-6:00 : Jason Strate: Error Logs and Deadlocks, Oh My!
- 6:00-7:00 : Kalen Delaney: Seeking SQL Server Secrets
Presentation:
Error Logs and Deadlocks, Oh My!
Tired of turning to the SQL Server error log to find out what’s happened? Would you rather just know about a deadlock when it’s occurred rather than later in the day when the manager as you why HRs bonus distribution application failed? Find out how to get this information and more delivered to your inbox so that you can head off issues instead of drowning in them. In this session we’ll look at Event Notifications and find out how to stop having to monitor SQL Server error logs today.
Jason Strate, SQL Server MVP, is a DBA with over twelve years of experience. His experience includes delivering both OLTP and OLAP solutions as well as assessment and implementation of SQL Server environments for best practices, performance, and HADR solutions. He is a SQL Server MCITP and participated in the development of Microsoft Certification exams for SQL Server 2008. Jason has presented at the SSWUG Virtual Conferences, TechFuse, SQL Saturday events, and at PASSMN user group meetings.
Presentation:
Seeking SQL Server Secrets
Many experienced DBAs are aware that there are many undocumented commands and traceflags in SQL Server, and some DBAs may even keep track of all references to such options whenever they read about them in an article or blog posts, or hear about them at a conference. In this session, I’ll not only tell you about some of my favorite undocumented features, I’ll also tell you some of my tricks for discovering more undocumented secrets.
Kalen Delaney has been working with SQL Server for 23 years, and provides advanced SQL Server training to clients around the world, using her own custom-developed curriculum. She has been a SQL Server MVP since 1993 and has been writing about SQL Server almost as long. Kalen has spoken at dozens of technical conferences, including every PASS conference in the US, since the organization’s founding in 1999. Kalen is a contributing editor and columnist for SQL Server Magazine and the author or co-author of several of the most deeply technical books on SQL Server, the most recent being SQL Server 2008 Internals, from Microsoft Press. Kalen blogs at www.sqlblog.com and her personal website and schedule can be found atwww.SQLServerInternals.com.
This month’s T-SQL Tuesday’s topic is “Resolutions” and is hosted by Jen McCown (Blog/Twitter).
Here’s how I see it, my 2011 resolutions break down to the 3 Ps: PASSMN, Powershell and Present. If I do all of these, at the end of the year, I’ll say OH, what a year!
I am on the PASSMN Executive Board as the Director of Technology for 2011. I want to do my best in that role to, not only help the PASSMN group, but also to help me meet more people in the SQL world. I’m impressed with the board members and our user community as well. This gets me out from behind the keyboard and gives me a chance to rub elbows with some very interesting people.
Powershell
I want to work more with powershell. I’ve read about it and attended two presentations on it. I dabbled with it on the flight back from PASS. Then my daily work got the better of me and my powershell scripts collected dust. The other day, I started playing around with it and began to realize the power of it when I re-wrote our entire database deploy process in about an hour. I tweeted that and I got the most buzz on twitter I’ve ever had. In the past 3 days I’ve dabbled in it more and I’ve refined a few things. I like the power of it as well as the ease of use. I want to continue to learn about powershell.
Present
I’m going to do it this year – I’m going to present some topic to some people. What the topic is and who the people are, I’m not sure. Some of my ideas are:
- A presentation on using DMVs for performance tuning for my developer co-workers
- A General SQL Performance Series to my developer co-workers
- Present something at a SQL Saturday Event
- Present something at a PASSMN Monthly meeting
I’m on the look out for what the “something”s are. Maybe I’ll do a powershell presentation at the PASSMN meeting – that would kill 3 birds with one stone! Actually, I won’t do that – I’ve got things that I could do a much better job presenting than something new I’m just learning.
Okay, so now, take a look at the bold words in the first paragraph: “See”, “3 P” and “OH” or C3PO – that’s my tool to remember my resolutions. If you are still reading – may the force be with you!
I’ve got a lot of changed altered procedures for a project I’m working on. I’ve been altering a lot of data in my test database, so I needed to restore my database and apply the changes I’ve made. The easy way to do would be a powershell script. Here’s what I found.
From Martin Bell for the post, “Executing all .SQL files in a directory with Powershell” Thank you Martin!
I also used twitter and asked #SQLHELP and SQLSoldier responded with this in about 3 minutes. Very impressive!
Get-ChildItem -Path "C:\SQLScripts" -Filter "*.sql" | % {invoke-sqlcmd -InputFile $_.FullName}
Here’s what I altered Martin’s script to:
foreach ($f in Get-ChildItem -path "C:\SQLScripts\" -Filter *.sql | sort-object)
{
$out = "C:\SQLScripts\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;
invoke-sqlcmd –ServerInstance <<ServerName>> -Database <<DBName>> -InputFile $f.fullname | format-table | out-file -filePath $out
}
Here’s all the info on the Invoke-SQLCMD from MSDN.
Challenges:
- PASSMN January Meeting – details are getting ironed out
- Learning the stuff needed for the PASSMN Website
- Deploy DB analysis – verify incremental deploys match full deploys for Project A2 (smaller project – from here on known as A2)
- Performance Analysis – a few procedures had to be analyzed for performance improvements from A2
- A couple of VT (Verification Testing) procedures needed to be altered
- Coding on Project B – altering procedures for schema changes
- Wrote Powershell script to deploy all DB changes locally
Good Reading from the Week:
The Week Ahead:
- Project B is still behind.
- Project A2 still needs some of my time
- On Call for Production
|
|
