Turn on the MLLP Listeners and the messages will flow.
Once again it’s that time of year. The committee and I have been working on spreading the word, getting sponsors, getting attendees, organizing lunch, and so much more. It takes a lot of work and has a lot of moving pieces. This year is lining up to be the best yet. We have a ridiculous schedule this year and it’s going to be hard for me to decide which sessions I want to attend. Check it out here.
I highly recommend coming this year. Take a Saturday and invest it in yourself by learning something new and meeting some new people who have the same interests about SQL as you do.
Here is the info:
SQLSaturday is a full day FREE training event for SQL Server professionals and those wanting to learn more about SQL Server and network with the SQL Server community. Pick from more than 30 different sessions and learn from the experts.
Don’t know anything about SQL? Back by popular demand, the “Wanna be a DBA” makes its return. Explore the life of a DBA and see if it can be yours too.
Date: October 3rd, 2015
Location: Pittsburgh Technical Institute – 1111 McKee Road, Oakdale, PA 15071.
Time: Check in starts at 7:30am. Sessions throughout the day
Cost: Admittance to this event is free, but we do charge a lunch fee of $10.00
Call for speakers: Call for speakers is still open. To submit a session go here – http://www.sqlsaturday.com/440/speakers/submission.aspx
Seating is limited so register today!
The Premier SQL Server training event in Western Pennsylvania
Join the Amazing Pre Con this year!
Brent Ozar, Jeremiah Peschka
Developer’s Guide to SQL Server Performance – Live!
Want to know more? Of course you do! Go here – http://www.sqlsaturday.com/440/eventhome.aspx
Still have questions? We can answer them. Mail us here – firstname.lastname@example.org
Like twitter? We do too. Follow us here @SqlSaturdayPitt and use the hash tag #SQLSat440
Here is our task:
” How do you feel about SQL Family? Did they help you, or did you help someone in the SQL Family? ”
I feel that the SQL Family is a great online community that helps each other out wherever and whenever they can. I don’t like to use the term SQL Family though. I prefer to keep work and personal life separate so I tend not to use the term “SQL Family”. When talking about my job and why I like what I do, one of the things I always bring up is the great
When I first started learning about databases, I lived on sites like SQL Server Central and MSSQL tips. I loved how that people had tons of questions and other people just answered them…ACTIVELY. What was even better was that people weren’t jerks about answering basic or n00b questions.
Then I was introduced to twitter. I started using twitter when I was working in New Zealand. My boss at the time was encouraging us to use twitter but I never really thought it was useful. Then I met the #SQLHelp hashtag. That is when I really understood what it was all about. For me, it’s like an ongoing discussion in a huge room of DBAs, developers, and Sys Admins. Great use of twitter. Almost always a good discussion on there.
As for me helping someone, I am currently on the committee that puts together Pittsburgh’s SQL Saturday (did you sign up yet?!). For the last 3 years I have helped organize this event. It’s been a great experience working with some great people. We periodically meet to discuss the event. It takes quite a bit of work to pull these off. From organizing speakers and figuring out what is for lunch (no pizza!) to actually getting people to come. The reward comes the day of the event though. It’s great seeing the last couple months worth of work all come together.
Overall I think the community as a whole is the best part about being a SQL DBA. Most of the time every problem you’ve ever had has been seen by someone else and that person will 99% of the time be willing to help you out with it. And if there is a time where you might have a new problem, there will always be about 50 people on #SQLHelp or stackexchange to give you some ideas of what to check. Thanks SQL peoples!
Assignment – Write about a big assumption you encounter at work, one that people are uncomfortable talking about.
I work for a vendor and our application(s) don’t live on a single server but on many different servers. We have message boxes, SQL servers, application servers, and so on.
When we get a call/email about our applications being slow or timing out, we usually log into the server and try to take a look and see where the pain point is. A lot of the blame is usually put onto SQL or the application.
Here is where the assumption comes in.
As a database administrator working for a vendor who deals a lot with the support team, we assume that the hardware was specced out and properly configured. Many times during conference calls this unease topic comes up. Sometimes these conversations go something like:
“Your application isn’t working, it’s so slow. We keep getting timeouts”
“We might need to give it a bit more memory, my laptop has more resources than the database server”
It’s not a conversation a lot of people want to have but sometimes it really isn’t the application or it really isn’t SQL’s fault.
Many users of the system, the administrators, engineers, and executives all assume that all the magical hardware is the appropriate speed, size, etc. But is it really? Is your hardware adequate for what your application is trying to do?
I’m personally looking forward to the session on Statistical Semantic Search. It’s something I haven’t used before.
Here is the current brochure. If you haven’t signed up yet, what are you waiting for?!
Welcome to SQLSaturday #250 in Pittsburgh
SQLSaturday is a full day for SQL Server professionals and those wanting to learn more about SQL Server and network with the SQL Server community. Our event will be held Saturday at ITT Technical Institute, 5460 Campbells Run Road, Pittsburgh, PA 15205. Admittance to this event is free, but we charge a fee of $10.00 to help defer the cost of lunch, coffee/tea and water which will be provided to attendees throughout the day.
One of my previous posts, I covered how to create a really big database with just garbage data in it. Recently, I was given a task to blow up tempdb. I thought about using a similar script and tried it out.
Of course it worked and it blew up TempDb but it also errored out. This wasn’t acceptable. I needed to find a way to blow up the tempdb with out it erroring out.
I tried a couple little things but ended up using the following script:
SET nocount ON USE tempdb IF OBJECT_ID('tempdb..#freespace') IS NOT NULL DROP TABLE #freespace IF OBJECT_ID('tempdb..#BigGuy1') IS NOT NULL DROP TABLE #BigGuy1 IF OBJECT_ID('tempdb..#BigGuy2') IS NOT NULL DROP TABLE #BigGuy2 IF OBJECT_ID('tempdb..#BigGuy3') IS NOT NULL DROP TABLE #BigGuy3 IF OBJECT_ID('tempdb..#BigGuy4') IS NOT NULL DROP TABLE #BigGuy4 IF OBJECT_ID('tempdb..#BigGuy5') IS NOT NULL DROP TABLE #BigGuy5 GO CREATE TABLE #BigGuy1 ( column1 CHAR(7100), column2 CHAR(900)) CREATE TABLE #BigGuy2 ( column1 CHAR(7100), column2 CHAR(900)) CREATE TABLE #BigGuy3 ( column1 CHAR(7100), column2 CHAR(900)) CREATE TABLE #BigGuy4 ( column1 CHAR(7100), column2 CHAR(900)) CREATE TABLE #BigGuy5 ( column1 CHAR(7100), column2 CHAR(900)) CREATE TABLE #freespace ( drive VARCHAR(1), [MB Free] INT) DECLARE @drive VARCHAR(1), @free INT SELECT @drive = LEFT(physical_name, 1) FROM sys.master_files WHERE database_id = DB_ID('tempdb') AND TYPE = 0 INSERT INTO #freespace EXEC master..xp_fixeddrives SELECT @free = [MB Free] FROM #freespace WHERE drive = @drive while @free > 1 BEGIN INSERT INTO #BigGuy1 VALUES('test', 'test') INSERT INTO #BigGuy2 VALUES('test', 'test') INSERT INTO #BigGuy3 VALUES('test', 'test') INSERT INTO #BigGuy4 VALUES('test', 'test') INSERT INTO #BigGuy5 VALUES('test', 'test') INSERT INTO #BigGuy1 VALUES('test', 'test') INSERT INTO #BigGuy2 VALUES('test', 'test') INSERT INTO #BigGuy3 VALUES('test', 'test') INSERT INTO #BigGuy4 VALUES('test', 'test') INSERT INTO #BigGuy5 VALUES('test', 'test') INSERT INTO #BigGuy1 VALUES('test', 'test') INSERT INTO #BigGuy2 VALUES('test', 'test') INSERT INTO #BigGuy3 VALUES('test', 'test') INSERT INTO #BigGuy4 VALUES('test', 'test') INSERT INTO #BigGuy5 VALUES('test', 'test') INSERT INTO #BigGuy1 VALUES('test', 'test') INSERT INTO #BigGuy2 VALUES('test', 'test') INSERT INTO #BigGuy3 VALUES('test', 'test') INSERT INTO #BigGuy4 VALUES('test', 'test') INSERT INTO #BigGuy5 VALUES('test', 'test') INSERT INTO #BigGuy1 VALUES('test', 'test') INSERT INTO #BigGuy2 VALUES('test', 'test') INSERT INTO #BigGuy3 VALUES('test', 'test') INSERT INTO #BigGuy4 VALUES('test', 'test') INSERT INTO #BigGuy5 VALUES('test', 'test') DELETE #freespace INSERT INTO #freespace EXEC master..xp_fixeddrives SELECT @free = [MB Free] FROM #freespace WHERE drive = @drive print @free END
This one uses a loop to check how much free space you have left. You can also set the threshold. For example, if you want to have 100MB free, you would just change @free > 1 to @free > 100.
With a word of caution, do NOT run this on your production system. We used this for a training session in a TEST environment.
The easy part about this one is that when you are finished expanding your tempdb, you can just restart your SQL services and it will reset the size.
This year was the first year Pittsburgh had a SQL Saturday. Luckily, I was able to find out about it a couple months beforehand and was able to get involved. I didn’t help out that much but I was still was able to lend a hand. It was the first time I was able to be part of this kind of event. In the past I have always been the one who went to the sessions but this time I got to see behind the scenes.
It started when I signed up (one of the first days!) and checked the “would you like to volunteer” checkbox during the registration process (which was very easy, btw). I then received a series of emails for in person meetings or phone conference meetings. I was able to attend a couple of the phone calls but didn’t really have much input to offer. The only in person meeting I was able to attend was a meeting after our local user group meeting. It was a great one to go to because it was the one where we picked out all the sessions. It was a long process but I think we all were satisfied with the outcome. We had a solid slammed packed day planned.
After that, I helped out a bit by contacting the former Chicago SQL user group president, Bill Lescher and asking for some words of wisdom. He had some good advice about getting sponsors. I also contacted Pluralsight.com about sponsoring the event (which they did!!!)
So then some more emails and then finally the day came. Volunteers had to be there at 6am so that meant I had to wake up at 5am to get ready and drive up to the north side of Pittsburgh. Of course I had to stop on the way and pick up some caffeine in Monster form.
My duties for the day were to be room proctor. I was in charge of getting the speakers what they needed, handing out and collecting evaluations, and making sure we stayed within the time limits. It was an easy job because I got to attend sessions at the same time.
So how were the sessions? GREAT! It was nice to spend a whole day listening to and learning about a technology that you find interesting.
I started off my day to a nice intro to Service Broker by Adam Belebczuk. Service broker can get a bit complicated if you have never used it before. I thought Adam did a great job cramming a ton of info into 1 hour. Great presentation skills as well (ZOOM IT!).
Next up was Dean Richards talking about VMware. It was another good introduction to VMWare. If you never used VMWare before then this was your course. It was a nice recap for me.
The final morning session was Mike Hiilwig‘s session of 7 SQL Agent jobs that I should be running. It was a very entertaining session that show some great methods for DB maintenance. Personally, I like to use powershell for monitoring errors over multiple servers but Mike’s ideas were also very good. I hope to see more sessions by him in the future.
LUNCH BREAK – The only take away I got from lunch was that college students don’t get dressed on weekends. The event was held at La Roche college. We didn’t eat until around 12:30. While I waited in line to get food with my other SQL Sat-ers, I noticed that every single college student I saw was still in their PJ’s or sweat pants. It was fall break though. ii-na.
After lunch I went to Justin Dearing‘s session on MongoDB. It was very cool to be exposed to noSQL. I’ve read about it before but have never seen it up close. It’s something I doubt I will ever use but now I feel I have a better understanding of it.
The next session was Michael John’s Top Development and Design Mistakes. It was a packed room for some antodotes of Michael’s experience in the field.
My final session of the day might have been my favorite. It was Sam Nasr’s 14 new SQl functions in 2012. It was exactly what the title says it was. We went over them 1 by 1. It was a great session that was more like a class. The whole group was engaged in the session. We actually had some time left over and explored these functions a bit more in depth.
After the sessions finished everybody filed back into the main hall for the giving out of the prizes and the final closing remarks.
After the day was over, some people headed to the bar for a couple SQL drinks. It was a nice way to finish off a great day. Overall, I enjoyed helping out (the little bit I did) and thought it was a great thing for the city of Pittsburgh. I hope we have more of these and the community is continues to support community training.
Thank you to everybody involved!!
I got a request this week from an application engineer. The request was to move ALL physical database files from one physical drive to another physical drive on the same machine.
Hmmmm, I have never moved ALL .mdf files from one place to another at one time. One of my teammates told the application engineer that it may take some time. He want to detach/move/attach all the DBs.
I knew this wouldn’t work because:
1) it would take forever
2) how are you going to detach the master DB?
So my solution contained 3 parts:
- Change the location of all the files inside SQL
- shutdown and move the files
- Change SQL’s startup parameters for the master database
Easy as π.
I used some good old dynamic SQL to create an ALTER DATABASE script for each db (except the master)
--first, let's just take a look and see where our files are living SELECT name, physical_name, CASE WHEN TYPE = 0 THEN 'DATA' WHEN TYPE = 1 THEN 'LOG' END AS FileType FROM sys.master_Files --Now let's create those ALTER DB statements SELECT 'Alter DATABASE ' + d.name + ' MODIFY FILE ( NAME = ' + f.name + ', FILENAME = ' + --here is where you want to put where you want to move your files ' ''c:\NewFolder\'+ right(physical_name, charindex('\',reverse(physical_name))-1 ) + ''' ) ' FROM sys.databases d inner join sys.master_files f on d.database_id = f.database_id where --remove this if you want both DB AND log file type = 0 --and keep this part, dbid 1 is master and d.database_id <> 1 --Check again to see if the physical name is changed select name, physical_name, case when type = 0 then 'DATA' when type = 1 then 'LOG' end as FileType from sys.master_Files
After you run this, you should see this message a bunch of times:
The file “tempdev” has been modified in the system catalog. The new path will be used the next time the database is started.
Now you can shut down your SQL services and move the physical files to their new location.
Once you have all the files moved over (including the master) you’ll need to change the startup parameters.
To do this:
- open up the configuration manager
- Right click on your SQL instance –> properties
- Click on advanced –> startup parameters
- Change the location of the master .mdf
Well it seems I have missed April and allllllmost May. I had a nice 2.5 month vacation from work but just this week started a new position in Pittsburgh. During my time off I monitored #sqlhelp, read some articles, and tried to keep up with the ever-changing world of databases. I also did a lot of relaxing, played too much skyrim, and traveled quite a bit.
The company I started working for is having a conference for their users this week so almost no one is in the office. It gave me a good chance to read through their documentation.
It’s very easy to get lost in documentation when you are new to a company. Especially when most companies these days use lots and lots of acronyms. Luckily, a company acronym dictionary was given to me in my intro packet. It came in handy while reading through documents and meeting with various people. It’s still easy to get lost in conversations involving simple things.
Do you use a lot of acronyms at your company? Do you document them? Love em/Hate em?
It’s been an interesting day. It was the 1 year anniversary of SQLSandwiches today. It was also my last day working in New Zealand. That’s right, I decided to leave my current role as DBA at Foodstuffs. Mrs. Sandwiches and I are headed back to the States to be closer to our family.
Foodstuffs was my second job in New Zealand. I enjoyed my time working there. My managers were great and let me database my day away. I learned a lot and worked with great people. There is a reason people stay there for a long time and after only a short while, I can see why. I also had the best ID ever there.
Back to the sandwiches. I’ve totally slacked off on the blog so far this year in preparation of this big move. Now that I will have some down time, I can catch up on all the half written blogs that I have in my backlog. I’ll be taking the next couple weeks off to travel around Japan and then to the States! Until then it’s going to be lots of reading, running, blogging, and studying for some exams.
So here’s to 1 year blogging under SQLSandwiches and the end of a short but great job in Auckland.
Final words to Foodstuffs…David, you can keep DTM! HA!