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:

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:

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.)





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


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:


That is super awesome!  Thank you for sharing Todd!


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’)


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]


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_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!


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.




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!



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



GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole


GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole



USE msdb



– Permissions for SQL Agent SP’s


GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole


GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole


GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole


GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole


Puttin’ down my story in a popular song and packing for PASS!!!!

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.