I can’t live without you, I can’t live without you SQL

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!

I got 99 problems but Autogrowth ain’t one because of CMS

I recently took over a new environment and have had my hands full exploring and setting up all my checks.  When I attended users group a few weeks ago I realized I was doing it the hard way.  I was connecting to each server one at a time to run my install t-sql scripts and checks.  Mike Tutor gave a fantastic presentation on CMS (Central Management Servers) in SQL Server and how to get started using it.  Today I had another issue with a database logfile that had autogrown to an unruly size.  I started to do the math and realized that if I didn’t learn how to use CMS, I would be fixing autogrowth settings all week.  So let’s begin.  This article assumes that you have already registered your Instances into Groups within CMS.  Right-click on your group and select New Query.  Run this query to see where you are at:

SELECT sd.name AS DBname, smf.name AS Filename, smf.size, smf.max_size, smf.growth, smf.is_percent_growth
FROM sys.master_files smf
INNER JOIN sys.databases sd ON smf.database_id = sd.database_id

This will give you both the Server Name and the database name on all your files if you are running in CMS but will also work if you are just on one local server.

Next, I am going to use Policy Based Management to Apply a Default Value across all my databases. On a test server, you want to create a new condition:
CreateCondition

Whatever you set that @Growth value to is the value that will be set on all your files that you apply this policy on, please use caution and plan a value that will fit your growth needs.

Then pull it into a policy:
CreatePolicy

Right Click on the Policy and Evaluate the policy to make sure it will do what you are expecting it to do. The green ones were already matching what your policy would do. The red ones are the ones the policy would update. Under Details select View to see what the values are now. You can test out how it is going to work by checking a check box or two and selecting Apply.

Export the policy by right clicking on it and selecting “Export Policy”.

Then go back to your CMS Group and right click to Import Policy. Right click on the group again and select Evaluate Policies. Find the one you just imported and check the box for it and run it. This is the same as before, select the check boxes of the ones you want to update and Apply.

Next, I want to be able to control the rate each system database will grow, this is just an example. Please plan your growth and modify as needed. Right click on your CMS group and select New Query and paste this in then modify as needed. (You could also do this with a more specialized policy, but I wanted to use both.)

ALTER DATABASE [master] MODIFY FILE ( NAME = N'master', FILEGROWTH = 240MB )
GO
ALTER DATABASE [master] MODIFY FILE (NAME = N'mastlog', FILEGROWTH = 160MB )
GO

ALTER DATABASE [msdb] MODIFY FILE ( NAME = N'MSDBData', FILEGROWTH = 240MB )
GO
ALTER DATABASE [msdb] MODIFY FILE (NAME = N'MSDBLog', FILEGROWTH = 160MB )
GO

ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 240MB )
GO
ALTER DATABASE [model] MODIFY FILE (NAME = N'modellog', FILEGROWTH = 160MB )
GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILEGROWTH = 240MB )
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME = N'templog', FILEGROWTH = 160MB )
GO

Many Chocolate Covered Gummy Bears gave their lives to bring you this information.

Please don’t stop the music…while I add the last day of the month to a table

Greetings!

I have this table that Finance uses to tell what the last day of any given month is in their reports. Through people coming and going, the keeper of this table didn’t keep it and the updates to it stopped.  Eventually, reports that used it stop moving forward as the dates didn’t exist.  I decided to create a job to update this table so it wouldn’t matter if someone forgot to update it.  It automatically will update each month (thank you SQLAgent).  But I was still left with the small problem of determining the last day of the month.  I found that the table had the last day of the month before it, so I am using that table to get the my next month end date.  The current table has two columns, the MonthEndDate and the Date that entry was created. I turned this select into an insert and ta-da we have the last date of the next month.

I add a day to the month end day so I get the first day of the next month.  Then I add a month to that and subtract to the day before the first of that month.

For Example:

MAX(MonthEndDate) = ‘5/30/2014′

Add a day and you get ‘6/1/2014′

Add a month and you get ‘7/1/2014′

Subtract a day and you get ‘6/30/2014′

SELECT DATEADD(day,-1,(DATEADD(m,1,(DATEADD(day, 1,MAX(MonthEndDate)))))),GETDATE()

FROM [dbo].[MonthEndDates]

*Update* Todd commented and showed me that you can just do this in SQL 2012:

SELECT EOMONTH(GETDATE())

That is super awesome!  Thank you for sharing Todd!

Enjoy!

They’ll never take my body from your side, Love don’t die from partitioning…

New dress for Christmas

New dress for Christmas

So this week there was this small fire around running out of partitions.  Because I had to learn how all of it works really fast, I need to write it down before I forget.  I was getting a range doesn’t exist error so here is what I did:

I already had my file groups created.  If you don’t do that before you move on.

Find what partition schemes and functions you have in your database:

SELECT * FROM sys.partition_schemes

SELECT * FROM sys.partition_functions

(Hint: the fanout is the last range so that can help you identify which one is failing.)

Next, see what your current ranges look like:

SELECT sprv.value AS [Value], sprv.boundary_id AS [ID]

FROM sys.partition_functions AS spf

INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id

WHERE (spf.name = N’PartitionFunctionName’)

ORDER BY [ID] ASC

Now we are going to split out our partitions one at a time.  Even if you need to add 5 or more ranges, this is an easy step to do one at a time.  Also, you can only split a range into to pieces at a time, so just run the whole query for each range you want to add.

ALTER PARTITION SCHEME [PartitionScheme] NEXT USED [YourFileGroup]

ALTER PARTITION FUNCTION [PartitionFunction]() SPLIT RANGE (N’NewRange’)

This should get you back on track.

Happy Trails!

Or you can start speaking up and query the backupset…

Today I needed to get the file name for the latest full back. I am using it so I can restore a database using an SSIS packages onto another server. Since there are full, diffs and logs, I had to specify type “D” in the code. That sounds a little off, but L = Log, I = Diff and D = Full. I debating on putting the subquery into a CTE, but this is such a quick hit, I did it the dirty way. I hope it helps you!

SELECT physical_device_name,
backup_start_date,
backup_finish_date,
backup_size/1024.0 AS BackupSizeKB
FROM msdb.dbo.backupset bINNER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name = 'MyDatabase' AND type = 'D' AND backup_start_date = (SELECT MAX(backup_start_date) FROM dbo.backupset WHERE database_name = 'MyDatabase' AND type = 'D')
ORDER BY backup_finish_date DESC

Let’s get these teen hearts beating faster and send me your PASS 2013 pics!

MattSlocum8

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

Come in closer and learn how to fix SSRS Subscriptions…

Greetings to the Kingdom,

Over the weekend we had a datacenter move and in the the process moved the database for SSRS.  I knew that my subscriptions would automatically move, so imagine my surprise when they didn’t.  Our SSRS databases were located on a new server that had the same name as the old server.  After running down the error “The EXECUTE permission was denied on the object ‘xp_sqlagent_notify’, database ‘mssqlsystemresource’, schema ‘sys’”, I found a super awesome blog post.  It noted that I was missing assignments to my RSExecRole role.  Once I ran the script, I stopped and restart SSRS and all the subscriptions came over.  Huge sigh of relief.  Have a great day!

http://karthikeyandba.wordpress.com/2011/07/15/the-execute-permission-was-denied-on-the-object-xp_sqlagent_notify-database-mssqlsystemresource-schema-sys/#comment-35

 

This error will occur when you are trying to create subscriptions for a reports

 

Execute the below script will solve the issue.

 

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole

GO

 

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole

GO

 

USE msdb

GO

 

– Permissions for SQL Agent SP’s

 

GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole

GO