Earlier this month, I had the opportunity to present at Big Mountain Data and Dev with the awesome Matt DeWitt. We covered a bunch of the topics that I have been posted about over the last few months with regards to query tuning. We had a lot of fun dressing as super heroes and talking about how to be the hero of performance tuning. Mine is part one and Matt’s is part two. Here are the requested slides:
The song for this post is Fallin’ with Me by The Struts.
In January, the awesome Tim Radney (b|t) talked to the Utah user group about best practices. One that he mentioned was rolling over your error logs everyday and keeping 35 logs (a month plus 3 reboots). I loved this idea and implemented it using what I had done here and adding it to an agent job.
Then I realized we didn’t have any alerts on if our logs were rolling too much. Way back in my career, it used to be something that I would watch and it could mean someone was trying to hack your system and cover their tracks by rolling your logs over a bunch. I fought so much with figuring how to tell if my logs are rolling over, I had to save it for the future.
DROP TABLE IF EXISTS #EnumErrorLog;
CREATE TABLE #EnumErrorLog
(
[Archive#] varchar(3) NOT NULL
PRIMARY KEY CLUSTERED
, [Date] datetime NOT NULL
, [LogFileSizeByte] int NOT NULL
);
INSERT INTO #EnumErrorLog ([Archive#], [Date], [LogFileSizeByte])
EXEC sys.sp_enumerrorlogs;
SELECT CASE WHEN COUNT([Archive#]) > = 5 THEN 1 ELSE 0 END
FROM #EnumErrorLog
WHERE Date > DATEADD(hour, -3, GETDATE())
I create a temp table so I can execute a system stored proc to pull the information into a table and select it back out. I run this alert check once an hour, which means that for 3 hours if the alert condition has been met, it will alert me that something has rolled over too much (1 means to alert, 0 means to not do anything). I am using a third party tool right now, but I bet this could be set up with native SQL alerts or agent jobs.
The song for this post is Little Bit of Love by JP Cooper, it makes me smile even on the toughest days. *hugs*
“You made a mistake, now let it go and move one and don’t make it again”. It is a super easy thing to say to someone that has just had a bad day. But when you are the one that made the mistake, it becomes a lot harder to move on. You think about it, and assume everyone else is thinking about it. “Look at that loser over there, they brought the whole system down.” You know they are thinking it.
Last week, I was in the situation of being the friend of someone that made a mistake. I honestly had forgotten about it within an hour, but he kept bringing it up, over and over again. All day long I was reminded of what he had done wrong because he wouldn’t let it go. At the end of the second day, I told him that if he kept telling me he was terrible at his job, I would start to believe him. I told him to let it go and if nothing else, to stop talking about it because we all had forgotten. We all had forgiven him, he just couldn’t forgive himself.
This week, I was the one that made the mistake. It was big and everyone saw it. I really wanted a time machine or an “Undo” button. Instead, I went home, cried, ate some chocolate and watched cartoons to feel better. My husband, and biggest cheerleader, asked me, “Are you perfect?” I have been tricked by this one before so I replied that I wasn’t. He looked at me and said, “Then stop beating yourself up for being human”. This made me think about how I handle things everyday. I also realized I had take all the words I had said to my friend and apply them to myself. I made a goal to not bring up the mistake to anyone after one day. If they mention it, we can talk about it or I can answer questions about the incident if needed. Also, at the end of the day, I have to forgive myself and move on. My entire team was AMAZING and kind when they learned it was my fault. They knew how bad it hurt to make a mistake and knew I would be my harshest critic. This is not to say that I am taking it lightly, it will be something I don’t do again and am disappointed in myself. I am really lucky to have an awesome boss that doesn’t hold mistakes over my head.
Every Technology person I have met, has a great story of how they messed up big. If they don’t have one, they are either lying or don’t push themselves to grow. I have a whole bag of them, but each one is something I learned and grew from, and thankfully, made me better.
So here goes, end of the day, time to forgive myself and move forward.
*Hugs*
The song today is: Take it all Back by Judah & the Lion
Greetings friends! This is a feelings post (you have been warned). Tomorrow I start my new job. Those that see me on a regular basis were shocked to hear I had accepted a new position. It was kind of a shock to me too. I have loved my time with my now previous employer. I was happy, challenged and most importantly appreciated. But then I got a call, an opportunity to work for one of the companies on my list of “Dream Places to Work”. I talked to my boss, he is a coach at heart and told me that I had to at least meet the team and know what I would be turning down or accepting so that I wouldn’t look back and wonder what might have been. I will forever be grateful for the lessons he taught me and the encouragement that he so kindly provided daily.
I did what he said, I met the team, toured the facility, asked questions and finally accepted the offer. It was such a hard choice to leave, if I could work at two places at once, I would have done that. I have been asked a bunch what made me choose to take the offer, so here goes:
1. Opportunity to work with advanced systems, HA and DR that is mature and the chance to learn from it. I won’t lie, I am nervous about learning new stuff, but I also love it, so I am sure it will be good.
2. Opportunity to learn the advanced features of SQL Server that only Enterprise level systems can provide.
3. An awesome team. I have become a bit of a feral DBA, so we will see how this goes. I very much hope that they are understanding of my crazy ideas, weird habits and kind in teaching me how to work with a team again. I also am so excited to learn new things, and be a part of something awesome. I have a lot of information in my brain, but I don’t always trust it, so it is good to have people to bounce ideas off and to gut check my ideas.
4. An awesome boss, his team spoke so highly of him, I knew I would be in good hands. This was also key. I had such an amazing boss already, there is a lot for him to live up to, but seeing the way the team admired and listened to him, plus tease him, helped me see that this could work for me.
5. A chance to learn ASL and make a difference. I have always wanted to learn American Sign Language, now I have a chance. I am super nervous about signing the wrong thing or offending someone (classic Andrea) but I know the only way to learn and get better is to try. Also, I get a sign name, which was kind of the seal on the deal. I am excited to learn what it is tomorrow. Helping people is something that has always been important to me. I make the joke that I am like Mary Poppins in that I only stay as long as I am needed. But making a difference is something that I need. I need to know that I am helping people live better lives, and this is definitely something that helps people.
My princess dresses are also a way to help people. I volunteer work for children’s charities and my personality is a big part of that work. I recently had a discussion with someone about not being just a character. He asked if I should stop wearing the princess dresses because it hides who I am and makes me a character instead of a person. I thought about this question a lot for a few days. I even talked to people about it and asked if they thought that I was more of a joke doing it. But it always came back to me and what I wanted. I feel that me dressing as a princess shows how I feel on the inside but am too shy to show. It is me trying to bring joy to other people. It is a reminder that we all have something special that we can share with the world to make the world better. We can help build up each other and also make the difference in the life of a child. I give a lot of reasons why I do what I do, but at the end of the day, it is because it builds me up so I can keep building other people up. I hope you have felt that love and support and if not, send me a tweet so I can tell you what I see in you.
I spent a lot of time trying to decide what to do. I think a specific song by The Struts made me feel that I had to do it:
I wanna taste love and pain
I wanna feel pride and shame
I don’t wanna take my time
I don’t wanna waste one line
I wanna live better days
Never look back and say
It could have been me
It could have been me
So, tomorrow, it’s me and I am hoping for the best.
Greetings,
I am approaching my last day at my current job. I love it here and will be really sad to leave, but have an awesome opportunity to grow my knowledge and career with a company on my “want to work for” list.
There are a lot of things to take care of before I leave. I have been updating documentation (with meme’s) so that it is useful and fun. I am trying to wrap up all my tickets and outstanding items and last night I woke up and realized, I was the owner of some databases. This is how I fixed it:
I launched a query window on my Central Management Server to save time, but you can run this on one server at a time if you want. I used the syntax from sp_helpdb to find out what I wanted to query:
select name, isnull(suser_sname(sid),'~~UNKNOWN~~') AS Owner, convert(nvarchar(11), crdate),dbid, cmptlevel from master.dbo.sysdatabases WHERE suser_sname(sid) = 'domain\MyUserName'
Some of the applications in my environment run under a special user and I didn’t want to interfere with those, I just wanted to fix the ones that use me. Then I borrowed some code from Brent Ozar:
SELECT 'ALTER AUTHORIZATION ON DATABASE:: ['+ name +'] to sa;' FROM master.dbo.sysdatabases WHERE suser_sname(sid) = 'domain\MyUserName'
Here’s one I run on the CMS to find any SQL Agent Jobs that I own across my enterprise and then I can run the update scripts that are generated on the individual servers.
SELECT 'EXEC MSDB.dbo.sp_update_job ' + char(13) + '@job_name = ' + char(39) + [Name] + char(39) + ',' + char(13) + '@owner_login_name = ' + char(39) + 'sa' + char(39) + char(13) + char(13)+';' FROM msdb.dbo.sysjobs WHERE SUSER_SNAME(owner_sid) = 'domain\MyUserName'
Yesterday I had the awesome opportunity to present at Big Mountain Data and SQL Saturday Salt Lake City. I was super nervous, but I think it went well over all. Huge thank you to the kind friends that sat in the audience to help build my confidence and for everyone that attended. Here are the scripts that I promised to post. If you would like the slide deck, it is posted on the Utah Geek Events website here: http://www.utahgeekevents.com/Downloads
The first script is the one that gets the row counts on each table so you can see what tables you want to look at and what tables you want to skip.
-- Shows all user tables and row counts for the current database -- Remove is_ms_shipped = 0 check to include system objects -- i.index_id < 2 indicates clustered index (1) or hash table (0) SELECT o.name, ddps.row_count FROM sys.indexes AS i INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID AND i.index_id = ddps.index_id WHERE i.index_id = 2 AND o.is_ms_shipped = 0 ORDER BY ddps.row_count DESC
This next part is the second demo I did about digging through the database.
--What columns are in the Sales Tables?
SELECT A.name, B.name
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE A.name LIKE '%Sales%'
--Column called "Order" something with amount?
SELECT A.name, B.name
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE B.name LIKE '%Order%'
--OrderQty is the column I am looking for...
SELECT A.name, B.name
FROM sys.tables A
INNER JOIN sys.columns B ON A.object_id = B.object_id
WHERE B.name LIKE '%OrderQty%'
--How do I know for sure it is the table I want?
SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
--c.scale ,
--c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE c.object_id = OBJECT_ID('Sales.SalesOrderDetail')
This is the code from the third demo where I was looking for the foreign keys. I got this off stack overflow and it has been very helpful.
SELECT obj.name AS FK_NAME, sch.name AS [schema_name], tab1.name AS [table], col1.name AS [column], tab2.name AS [referenced_table], col2.name AS [referenced_column] FROM sys.foreign_key_columns fkc INNER JOIN sys.objects obj ON obj.object_id = fkc.constraint_object_id INNER JOIN sys.tables tab1 ON tab1.object_id = fkc.parent_object_id INNER JOIN sys.schemas sch ON tab1.schema_id = sch.schema_id INNER JOIN sys.columns col1 ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id INNER JOIN sys.tables tab2 ON tab2.object_id = fkc.referenced_object_id INNER JOIN sys.columns col2 ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id WHERE tab1.name = 'SalesOrderDetail'
The other demos that I did were opening Views and stored procedures and a walk through of how to use the Database Diagram feature.
Hope you all had a wonderful time at the event like I did!
By now you know I love Microsoft SQL Server. I get a lot of questions about how I got into database and why I got into database. One of the new people I am working with suggested I add the story to my blog. So here goes…
Life should be fun and money is a big part of life. I wanted to be able to work hard so I could play hard, but I also wanted to love what I did. I use the phrase all the time “When it stops being fun, it is time to be done.” Basically, when I don’t love what I do, I am miserable.
My first real job was as an Administrative assistant. I was terrible at it. I didn’t type well, hated writing memos, and dictation was a nightmare for me. The only think I really loved was when I got to do some mail merges (it was tables, I just didn’t know it yet) and changing the copier toner. You should have seen the day I opened the toner wrong and it went all over the mail room. It was such a mess. I would come home crying everyday because I knew I wasn’t good at it. I didn’t have fun with it. I finally decided that if I couldn’t love my job, I would get a dumb job to so I could go play more and call in sick when I needed a break.
I went to work at a call center selling long distance. I was good at it, and it was fun, but still not perfect. The fun part was the computer program we got to use. I learned it super fast and would help the IT guys test new upgrades. They were super nice to me, and one day suggested that I help train new people on the software we had been testing for roll out. After the training, working on the phone was so boring! The IT guys suggested I come work with them as a Business Analyst. I had no idea what that was or what they did, but it was a new adventure so I went for it. They had me learn Access and when I started pointing Access as the production database (because no one had heard of a snapshot reporting databases back then) it would slow the databases down. The awesome IT guys asked me to learn T-SQL. They gave me new tools and showed me a few things, pointed me to a few websites and it was like I had found the best chocolate ever! I continued to learn working as an Operations Admin and really loved learning SQL Server. I even started to dream in T-SQL. You can imagine my disappointment when I would wake up and my house hadn’t been cleaned by a T-SQL Query. Truncate Table dbo.Dust anyone?
Life changes and so did my opportunities, I worked as an Information Manager, Reporting Manager, and finally I got a gig as a DBA. It was one of the happiest days of my life. I felt like I had finally arrived. Little did I know that just having the title, wouldn’t give me all the answers. There have been a lot of moments of joy and may times my sweet husband has found me crying in my closet because I felt like I wasn’t good enough. I can be loved by 99 out of 100 people in a room and will see that 1 person that doesn’t like me or thinks I am not good enough. I will focus on them and try to change their opinion. When that happens, I go back to what my dear friend Tara said when I was first starting out: “Andrea if they don’t like you, it is because they don’t know you.” I think that is true for so many people.
Today as I watched Brent Ozar, Kendra Little and Doug Lane answer questions about presenting, I realized that many of us don’t feel like we are good enough to teach someone else. We don’t have every perfect answer, but that is part of the fun of SQL Server. There is always more to learn, a different way to do something and someone you can help. There will be people who are mean along the way, but if we focus on our passion and forget the rest we can find joy in the work we do. As Pat Wright says: “When you do what you love, you won’t work a day in your life.” My hope for you is that you find the thing that you love and make it work for you, so you never have to work a day again.
May you feel joy in the work you do today!
Hi All,
I love the PASS Summit. One of my favorite parts is posting all of the pictures after so I can remember all of the happy memories. I would love if you sent me some of your pictures that I can include on my Magic Mirror Page. I have posted anything that was put out on twitter, but would be happy to credit you if you were the photographer. Thank you to everyone who shared and big hugs to you all!
Here are my pictures and you are welcome to share. You can also see the ones I have collection on the Magic mirrors page. Thank you to everyone who has let me post their pictures and if I missed a credit on them, please let me know and I will add it.
http://www.flickr.com/photos/29241535@N02/
Enjoy!
Andrea
Greetings!
It is that time of year again. PASS Summit 2013 is next week and I want to share my packing tips. I am a carry on only person and with all the princess gowns to pack, I have learned a couple of small tricks to help me fly with less.
Sample Size stuff – I have a clear zipper pack that I fill with sample size shampoos. It makes it super easy to pull out at airports to be checked by TSA. Sometimes when I stay at hotels and I like the shampoo bottles, I will bring them home and fill them with the shampoo and conditioner I like (Disney ones are my favorite). This allows me to have the shampoo I like that fits in my small zipper bag.
Space Travel Bags – These are super cool. You can roll the air out of them so your clothes compress down. SQL Compression in a bag for your clothes!!! Make sure your clothes are comfortable. You will be sitting and socializing a ton and it isn’t fun to be uncomfortable. Jeans and a nice shirt are perfect for all the events I have attended. (There might be a fancy event that I have been missing)
Two pairs of shoes tops – I love shoes, but they are a pain to pack. My rule is to wear one pair of shoes and pack the other. This allows me to have a comfortable pair and a pair that can be dressed up if I want. My comfy shoes also double for my exercise shoes. I have also found the “purse flats” shoes are nice to have and they can fit in tight spaces in case I need a dressy pair of shoes.
Simple hair care – I have super frizzy hair. This makes traveling to humid climates difficult. When traveling, I don’t try to curl it or make it super straight. Instead I have a flat-iron that can also curl the ends of my hair (if you want to see how, I can show you). I also take this opportunity to try out some really fun braids. If my hair is a little wacky at the Summit, you will know that the humidity has gotten to my hair.
Snacks – I have a gluten intolerance so it is super important for me to pack a snack in case I can’t find food. I usually throw some crackers in a ziplock and push all the air out. Last year I had a special banana bread that was super filling and served as a few late night snacks after a night out of fun.
Second bag – Don’t bring one. Plan to bring your laptop in your carry on. PASS usually gives out backpacks so you are going to end up with an additional bag. Last year I had already brought a laptop bag and thankfully it packed down really well. If you bring a second bag, plan to pack it on the way home.
Extra space – You might want to pack light on t-shirts and just pick up shirts to wear from the vendors. There is so much swag given away that you can absolutely skip bringing enough shirts to get you through the conference. I have had to wear more than one shirt on the way home before to make it all fit. Also, be sure to have room for souvenirs if you are the type of person that likes to collect those.
I am super excited to see everyone, please be sure to come say hi at the conference.
Guess who presented for the first time at a SQL Saturday? It was also the Utah Big Mountain Data Conference. I wanted to share my presentation in case anyone else wants to learn Power Pivot.
I love Power Pivot and think it can make a difference in how you look at data everyday. If you have questions that you need answers to, I am happy to help.
Big Thanks to everyone that was so supportive with this presentation!
Andrea Allred presents RoyalSQL 