We tend to think of ourselves as self reliant. Sit and think for a second, how many people do you rely on?
Help Desk
Our angry callers hit them first. They let us know if something's down and our monitor doesn't see it. The more information we can give them, the easier they can make our lives. If you know you have a planned outage, give them a heads up. They will appreciate it immensely.
Developers
These are the people that build the applications your business is making it's money from. They connect customers with the data you're protecting. We need to get along with them. If we put ourselves on an even level and make sure we keep a good balance, we can get so much more done. Protect the data, but not at the cost of the business.
Network Admin
When you try and hit a server and can't because the routes were updated again, instead of just being mad at the Network Admin, consider this. All those times you had no problems? It's because they had it working. They're in the same boat as us. Noone notices how good of a job we're doing until it all catches fire.
System Admin
I haven't built a production system from the ground up in years. I love my Sys Admins. They get tasked to setup 50 new SQL boxes, 100 new IIS servers and half a dozen DC's for various projects. Once they do all the heavy lifting, we can use a single PS script to install SQL for us across all the new servers and just go to lunch. Give them credit for working on all the things they do so we can concentrate on what we actually want too.
Management
Good managers go to bat for you. They are the ones that argue for new budgets to get more tools, people, hardware and even that soda fountain in the break room. They're the ones trying to make sure no one calls you while you're on vacation or tries to get the SQL Saturday hotel room paid for when you sneak off on the weekends. The more you work for them, the more they'll work for you. (Bad managers excluded, terms and conditions apply, not available at all jobs, see supervisor for details)
Mend The Fence!
Take the time now. Think of some way you can make it easier to work with them and get the projects completed that needed to be done. The easier we make it to work together, the better we can make our companies. I hate seeing posts of an SA or NetAdmin trying to lock down the DBA just as much as I hate seeing the DBA lock out the Developers from even staging. There are times when it's required, but others it just simply isn't. I'm not saying give the DBA's Domain Admin or the password to the proxy servers, or even giving the Developers SA on production... But sometimes a little read only helps a lot when isolating a problem at 2 am on Christmas morning.
Final Thought
I'll end with this. Where I work now, our Network Admins are great. They go the extra mile to take care of us. Our first line support staff goes out of their way to get all the information they can. Our SA does what he can to make our lives easier and our Developers talk with us when they have a question. This may sound like everyone doing their job, but let's be honest. This is the exception. This helps raise retention. Get your people involved, make them feel equal and make sure to take a day and let them enjoy it. Maybe a little Dave and Busters with a long lunch. You'd be surprised what wonders that'll do for your staff.
20.1.14
15.1.14
Restore Master From Backup
There are a few posts running around talking about restoring master from backup. Thomas LaRock has a fantastic "How To: Restore The Master Database In SQL Server 2012" post. It's very well organized and has a lot of great examples and directions. SQLSkills also has a survey up referring to a survey about restoring or rebuilding master. I'm really curious what their post will entail.
I'm going to throw something in the hat here. It's not as detailed as what you'd see from SQLRockstar or SQLSkills. This is a document I put together about a year ago for my group so that when we had to restore from master, someone had a step by step on how to do so.
The word doc is located here.
This is not a training item or a deep dive into how the rebuilds are done and how you can map the stars with them. This is just simply a doc that you throw in your Disaster Recovery Plan folder and let it collect dust until something major happens. If your people know where you keep this... hopefully the instructions are simple enough that they can follow it. You may modify it some to fit your org better.
This is simply a run doc that sits and collects dust until a fire brews. Have your people do the training, have them read through SQLRockstar's post. It's great. Keep this document on the file share so if they forget ,you won't need to search through websites trying to remember something you've done once or twice a career.
I'm going to throw something in the hat here. It's not as detailed as what you'd see from SQLRockstar or SQLSkills. This is a document I put together about a year ago for my group so that when we had to restore from master, someone had a step by step on how to do so.
The word doc is located here.
This is not a training item or a deep dive into how the rebuilds are done and how you can map the stars with them. This is just simply a doc that you throw in your Disaster Recovery Plan folder and let it collect dust until something major happens. If your people know where you keep this... hopefully the instructions are simple enough that they can follow it. You may modify it some to fit your org better.
This is simply a run doc that sits and collects dust until a fire brews. Have your people do the training, have them read through SQLRockstar's post. It's great. Keep this document on the file share so if they forget ,you won't need to search through websites trying to remember something you've done once or twice a career.
13.1.14
What Tables Are Wasted?
Wasted space still seems to be on everyone's mind to some extent. I've wondered what tables do we use? We have our offenders, those are easy to track. What about that little database that's been around for 4 years that no one left even really knows what it does? I have a set of scripts that may help some with that. I'll include a download link as well.
It's listed in the comments, I'll mention it here as well. This list is only accurate to the last reboot of services. Do not base your delete decisions on this solely. If you track it over time, it should be reviewed to see about removing, then only remove it with a good backup so you can restore it if needed. Take caution any time you remove parts of your database.
You can download the full script Here. Any comments or suggestions are appreciated. Thanks again!
/*
Author Bill Barnes
Created 01/10/2014
Use: The purpose of this script is to show indexes and tables that have not been used
since the last restart of SQL services. This script can be quickly modified to show what
tables have been used and provide more useful data. This will ignore all system schema
based tables.
Note: an update or scan is counted per instance set not per scan. If you update a row once
you get one addition to update. If you update a table and change 50,000 records, that still
is only 1 update. Keep that in mind when reading the numbers provided.
*/
--This Version will only pull a list of tables that have shown no use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name
-- This version provides a list of all tables that are in use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name
--This version shows a sum of all activity on these tables.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
sum(usage.user_lookups) UserLookups,sum(usage.user_scans) UserScans, sum(usage.user_seeks) UserSeeks,
sum(usage.user_updates) UserUpdates, sum(usage.system_lookups) SystemLookups,
sum (usage.system_scans) SystemScans, sum(usage.system_seeks) SystemSeeks, sum(usage.system_updates) SystemUpdates
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
group by sch.name, obj.name, idx.name, obj.object_id
order by obj.name
It's listed in the comments, I'll mention it here as well. This list is only accurate to the last reboot of services. Do not base your delete decisions on this solely. If you track it over time, it should be reviewed to see about removing, then only remove it with a good backup so you can restore it if needed. Take caution any time you remove parts of your database.
You can download the full script Here. Any comments or suggestions are appreciated. Thanks again!
/*
Author Bill Barnes
Created 01/10/2014
Use: The purpose of this script is to show indexes and tables that have not been used
since the last restart of SQL services. This script can be quickly modified to show what
tables have been used and provide more useful data. This will ignore all system schema
based tables.
Note: an update or scan is counted per instance set not per scan. If you update a row once
you get one addition to update. If you update a table and change 50,000 records, that still
is only 1 update. Keep that in mind when reading the numbers provided.
*/
--This Version will only pull a list of tables that have shown no use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name
-- This version provides a list of all tables that are in use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name
--This version shows a sum of all activity on these tables.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
sum(usage.user_lookups) UserLookups,sum(usage.user_scans) UserScans, sum(usage.user_seeks) UserSeeks,
sum(usage.user_updates) UserUpdates, sum(usage.system_lookups) SystemLookups,
sum (usage.system_scans) SystemScans, sum(usage.system_seeks) SystemSeeks, sum(usage.system_updates) SystemUpdates
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
group by sch.name, obj.name, idx.name, obj.object_id
order by obj.name
9.1.14
New Baby Girl!
On the 7th of January, 2014 my baby girl Kaitlyn was born. She came in about a month early.
This is my mother and my wife using face time so that my sick grandmother can see the new grand baby. It wasn't an intentional 4 generation picture.
Sleep exists... but only for the one that cries the loudest. I swear she can cry and snore at the same time. She's personally adorable. She's got two older sisters who seem both interested and afraid of the "crying burrito".
A lot of my friends have said that you cannot be prepared for a newborn, you can only try. I was also told to sleep any chance I got just in case. So far though... she's fussy a little on eating, but she's sleeping well and not overly crying.
This year is starting off rather well so far. She's healthy and happy. The wife is doing well now. We didn't get attacked by snowmagedon. Lot's of good things so far. I'll be back with more posts once things calm down. For now though, I'm going to take a short break. If she's realllly good, I'll be back next week. ^.^
6.1.14
Posts I've Found Interesting:
This is a question I had until I came across a great script by The Scripting Guys. The script below is unaltered and entirely their creation. I just found it useful enough to forward it along. This is a short post. Prepping for a baby on the way has cut into my sleep. ^.^
select 'table_name'=object_name(i.id) ,i.indid ,'index_name'=i.name ,i.groupid ,'filegroup'=f.name ,'file_name'=d.physical_name ,'dataspace'=s.name from sys.sysindexes i ,sys.filegroups f ,sys.database_files d ,sys.data_spaces s where objectproperty(i.id,'IsUserTable') = 1 and f.data_space_id = i.groupid and f.data_space_id = d.data_space_id and f.data_space_id = s.data_space_id order by f.name,object_name(i.id),groupid go
SQLSoldier wrote a post recently on how DMV's cost him his job by helping him find a better one. I found it rather interesting and thought I should forward it along. A similar situation happened when the company I was with showed no interest in providing better training, adjusting pay any, and was overall just difficult to get a hold of. I was a contractor working as a DBA. A good friend of mine had an opening where he worked and I ran for happier times. Some times the grass really is greener.
There's an older post (2007) about joins. Let me say first, I'm not a coder. I've worked with some Java, C++, VB, and obviously T-SQL. I always prefer a visual I can work with or semi working code to build off of. Coding Horror had a post explaining SQL joins visually. It's worth looking into.
1.1.14
Final Giveaway And Some Stats
Today I'm honored to give the final prize in a month of free giveaways here, the book; Professional SQL Server 2012 Internals and Troubleshooting to Robert! I have been lucky enough to give away 4 months of PluralSight to Komal, Annapu Reddy Gayathri, Aadhar Joshi, and Daniel.
Things I've Learned Blogging
This is my 40th post so far. I've noticed that most of my audience is coming from India at almost a 2 to 1 ratio over America. I think that's amazing. I honestly thought it would take months or even a year for this to be read out of the states.
Most of my readers come on around 0600 Central time and again around 1400 Central time. I'm not sure if it's just that time of day or what. I normally schedule my post to show up at 0300 or 0500. I only post after that on days I'm announcing something such as a winner.
I don't think I can maintain a post a day and have anything worth reading. I Plan to do a Monday, Wednesday release with an optional Friday. I'm the sole poster on this site and that takes a tole. I know the wife is tired of hearing, "I plan on working some tonight". I'd like to get a second person who's interested in posting on here sooner or later. Maybe even guest posts. I wouldn't mind hosting a few posts for someone who wants to tell the world something interesting but doesn't want to start a blog.
Commentators and people in general are not that vicious. I was honestly nervous when this blog started getting posted to SQL Server Central. I was expecting some negative feed back such as my information was wrong, too low level, basic stuff, or even just not needed. Instead I've gotten a rather good response and helpful advice. Anyone who's considering writing... go for it.
Thank you all for your support. Next week I'll get back to more technical posts. I really appreciate the people who have come here, who have decided to start following the site and those that commented. Even if it was just to win something. ^.^
Things I've Learned Blogging
This is my 40th post so far. I've noticed that most of my audience is coming from India at almost a 2 to 1 ratio over America. I think that's amazing. I honestly thought it would take months or even a year for this to be read out of the states.
Most of my readers come on around 0600 Central time and again around 1400 Central time. I'm not sure if it's just that time of day or what. I normally schedule my post to show up at 0300 or 0500. I only post after that on days I'm announcing something such as a winner.
I don't think I can maintain a post a day and have anything worth reading. I Plan to do a Monday, Wednesday release with an optional Friday. I'm the sole poster on this site and that takes a tole. I know the wife is tired of hearing, "I plan on working some tonight". I'd like to get a second person who's interested in posting on here sooner or later. Maybe even guest posts. I wouldn't mind hosting a few posts for someone who wants to tell the world something interesting but doesn't want to start a blog.
Commentators and people in general are not that vicious. I was honestly nervous when this blog started getting posted to SQL Server Central. I was expecting some negative feed back such as my information was wrong, too low level, basic stuff, or even just not needed. Instead I've gotten a rather good response and helpful advice. Anyone who's considering writing... go for it.
Thank you all for your support. Next week I'll get back to more technical posts. I really appreciate the people who have come here, who have decided to start following the site and those that commented. Even if it was just to win something. ^.^
30.12.13
Final December Giveaway!
Today I'm giving away the book Professional SQL Server 2012 Internals and Troubleshooting. If you live outside the continental US, I'll instead send you a 25$ card to amazon to use however you wish. Congratulations to all the winners we've had so far! Do not forget to post here! If you win, I'll need to have an E-Mail address to contact you for either your address to mail the book or with what country you're in to E-Mail you the gift card. Good luck to you all and have a happy New Year!
Let's talk about some New Years resolutions? I've had a few this year.
Build a presentation and get it ready for my local SQL Users Group.
I have a presentation I plan on doing over Fill Factor and some of the Pros and Cons about it. There is a demo component, but it will stay out of the 300 level realm. This is more for generalization and concept understanding of something people far too often change hoping it fixes something it won't.
Start blogging and trying to speak in a public setting.
I will have over 40 posts this year. That's good considering I started really blogging in November. I have proven to myself that I can write. This coming year I plan on increasing the quality of what I write.
Start getting more involved in my SQL community.
I was made a Vice President at my local User Group recently. I've either been involved as a volunteer or helped setup the last 3 years of SQL Saturdays here in OKC. Each year we find new things that are troublesome, but I've yet had an experience bad enough to ever compare to all the good we get from the event.
Try and give something back.
With the help of Pluralsight, I've been able to give away 4 months of free training. The best part was, I didn't ask them for that. I asked to purchase the cards to give away. I think that's a good sign for our community.
Better my T-SQL skills.
I'm rather happy of how far my coding has come... that is until I look at my normal blog posts of the day. I have a lot to work on here still, but It's nice to see improvement anyways.
Get back into training more.
I broke down and got the year subscription to PluralSight during the black Friday sale they had. I am buying the pass 2013 summit videos as well soon. Now if only I had more time to actually watch them.
What are some of your New Years resolutions? What ones did you complete?
Let's talk about some New Years resolutions? I've had a few this year.
Build a presentation and get it ready for my local SQL Users Group.
I have a presentation I plan on doing over Fill Factor and some of the Pros and Cons about it. There is a demo component, but it will stay out of the 300 level realm. This is more for generalization and concept understanding of something people far too often change hoping it fixes something it won't.
Start blogging and trying to speak in a public setting.
I will have over 40 posts this year. That's good considering I started really blogging in November. I have proven to myself that I can write. This coming year I plan on increasing the quality of what I write.
Start getting more involved in my SQL community.
I was made a Vice President at my local User Group recently. I've either been involved as a volunteer or helped setup the last 3 years of SQL Saturdays here in OKC. Each year we find new things that are troublesome, but I've yet had an experience bad enough to ever compare to all the good we get from the event.
Try and give something back.
With the help of Pluralsight, I've been able to give away 4 months of free training. The best part was, I didn't ask them for that. I asked to purchase the cards to give away. I think that's a good sign for our community.
Better my T-SQL skills.
I'm rather happy of how far my coding has come... that is until I look at my normal blog posts of the day. I have a lot to work on here still, but It's nice to see improvement anyways.
Get back into training more.
I broke down and got the year subscription to PluralSight during the black Friday sale they had. I am buying the pass 2013 summit videos as well soon. Now if only I had more time to actually watch them.
What are some of your New Years resolutions? What ones did you complete?
27.12.13
SQL Saturdays Coming Up!
Don't forget! Monday is the final give away! I will be giving away a free copy of the Professional SQL Server 2012 Internals and Troubleshooting. Any previous winner can win this as well. If you live outside of the continental US, I will send you a code for a 25% gift card for Amazon. The only requirement is to have an E-Mail address that I can either use to get your address or to E-Mail you the gift card code.
2014 SQL Saturdays
There are SQL Saturdays all over the world. A full list can be found here.Now I'm from Oklahoma. My company does not pay for my mileage or airfare. They do occasionally give me the Friday off to make the trip and if my lead is going, I can steal a bed in there. I'm grateful for it since my last company wouldn't even do that much.
Things I'd Love To See
I normally see a lot of 100 level classes. I Think these are great. My one want would be to see a better ratio of 300's as well. something like, 50% 100, 25% 200, 25% 300 level classes. I'd like to see some summit material at the SQL Saturdays so that those who are a long way off from affording to go to the summit can get that level of training.
SQL Saturdays I Plan On Attending
Apr 05, 2014 - LasVegas
Apr 26, 2014 - Chicago
May 03, 2014 - Alanta
Jun 21, 2014 - Louisville
TBA - OKC
TBA - Kansas
TBA - Dallas
What I Normally Bring
2 Changes of clothes. You never know what will happen.
Swim Suit.
Jogging clothes. (weather dependent)
Travel toiletries.
Laptop
Tablet (If the site has no power in the rooms, A tablet may be the only thing that will live.)
Headphones
Random movies
Cell Charging cord
If I Were Presenting, What I Would Bring
External HD and large USB Drive
HDMI cable
mini-HDMI cable
USB to VGA connector
Presentation Mouse
Cell Phone cable for hot-spot use
Spare Power Cable
VGA to DVI converter
2014 SQL Saturdays
There are SQL Saturdays all over the world. A full list can be found here.Now I'm from Oklahoma. My company does not pay for my mileage or airfare. They do occasionally give me the Friday off to make the trip and if my lead is going, I can steal a bed in there. I'm grateful for it since my last company wouldn't even do that much.
Things I'd Love To See
I normally see a lot of 100 level classes. I Think these are great. My one want would be to see a better ratio of 300's as well. something like, 50% 100, 25% 200, 25% 300 level classes. I'd like to see some summit material at the SQL Saturdays so that those who are a long way off from affording to go to the summit can get that level of training.
SQL Saturdays I Plan On Attending
Apr 05, 2014 - LasVegas
Apr 26, 2014 - Chicago
May 03, 2014 - Alanta
Jun 21, 2014 - Louisville
TBA - OKC
TBA - Kansas
TBA - Dallas
What I Normally Bring
2 Changes of clothes. You never know what will happen.
Swim Suit.
Jogging clothes. (weather dependent)
Travel toiletries.
Laptop
Tablet (If the site has no power in the rooms, A tablet may be the only thing that will live.)
Headphones
Random movies
Cell Charging cord
If I Were Presenting, What I Would Bring
External HD and large USB Drive
HDMI cable
mini-HDMI cable
USB to VGA connector
Presentation Mouse
Cell Phone cable for hot-spot use
Spare Power Cable
VGA to DVI converter
26.12.13
Powershell Backups and Post Frequency Change
It's been a good run doing a post every day. I'm scaling it back a bit. I liked getting these 36 posts out one a week day for some time now. I'm going to scale back. I"m not sure yet if I'll go down to one or two a week. I have the book giveaway post on the 30th and the winner announcement on the 1st. It's been a fun month, that's for sure. I want to hope for more quality and less rushing to push something out. I'm really curious what I can do with proper time.
Simple PowerShell Backup Script
There are better ways to do this. I welcome your responses and advice. I'm just now dabbling in powershell. I know Sean McCown has a large library of powershell videos. I think that will be my new years resolution. Below is my first day working with powershell.
This is my powershell script that I've added to the windows task manager. Before we shred the idea here... We needed backups taken for an Express box. I'm sure scripts exist out there and no one should reinvent the wheel, but I wanted to take a stab at it to learn a bit more.
sqlcmd -S .\servername -i E:\Tasks\Backupalldatabases.sql
$Path = "E:\Backups\master"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\msdb"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\model"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Main"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Restore"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Test"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
This calls a SQL script that is a bit more basic.
exec sp_MSforeachdb @command1 = '
use ?
if ''?'' not like ''tempdb''
begin
declare @backup varchar (1000)
declare @date varchar (100)
set @date = (select cast(cast(Getdate() as char(11)) as date))
set @backup =
(''BACKUP DATABASE ['' +
db_name() + ''] TO DISK = N''''E:\Backups\'' +
DB_NAME() + ''\'' +
db_name() +
'' '' +
@date +
''.bak'''' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'''''' +
db_name() + ''-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'')
exec (@backup)
end'
I have found the way to have something delete anything in all sub folders with a date older than 3 days which would would work easier than hand scripting all this out.... but I haven't put it in play yet.
Simple PowerShell Backup Script
There are better ways to do this. I welcome your responses and advice. I'm just now dabbling in powershell. I know Sean McCown has a large library of powershell videos. I think that will be my new years resolution. Below is my first day working with powershell.
This is my powershell script that I've added to the windows task manager. Before we shred the idea here... We needed backups taken for an Express box. I'm sure scripts exist out there and no one should reinvent the wheel, but I wanted to take a stab at it to learn a bit more.
sqlcmd -S .\servername -i E:\Tasks\Backupalldatabases.sql
$Path = "E:\Backups\master"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\msdb"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\model"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Main"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Restore"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
$Path = "E:\Backups\Test"
$Daysback = "-3"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($DaysBack)
Get-ChildItem $Path | Where-Object {$_.lastWriteTime -lt $DatetoDelete } | Remove-Item
This calls a SQL script that is a bit more basic.
exec sp_MSforeachdb @command1 = '
use ?
if ''?'' not like ''tempdb''
begin
declare @backup varchar (1000)
declare @date varchar (100)
set @date = (select cast(cast(Getdate() as char(11)) as date))
set @backup =
(''BACKUP DATABASE ['' +
db_name() + ''] TO DISK = N''''E:\Backups\'' +
DB_NAME() + ''\'' +
db_name() +
'' '' +
@date +
''.bak'''' WITH RETAINDAYS = 1, NOFORMAT, NOINIT, NAME = N'''''' +
db_name() + ''-Full Database Backup'''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'')
exec (@backup)
end'
I have found the way to have something delete anything in all sub folders with a date older than 3 days which would would work easier than hand scripting all this out.... but I haven't put it in play yet.
25.12.13
Christmas Day Winner and RDC Clients: Day 13
Finally we'll see who won the final training prize! The winner is Komal! Merry Christmas and Happy Holidays to everyone! The free book post is going up Dec 30th and the free book winner will be announced New Years day! As always, I will need an E-Mail to do the drawing and you will need to give me your shipping address if you win. For anyone who lives outside of continental US, you're welcome to keep your name in the hat for the final prize this month. I will be sending a 25$ Amazon gift card in place of the book due to high shipping costs.
Server Access Anywhere!
There was a post recently about remote connections from phones. That got me thinking. I tend to keep a stronger set of RDC type tools than most people do. I like toys, I think that's the simple answer. The long one is that I like the convenience of being at my Sisters watching a movie with all our families and fixing the server without driving in. This will be a short list today.
Remote connections
You can get these a couple of ways. The free ways is to be aware of the fast food places around. Other places have internet for free... but we all know where the McDonald's is. The places I've seen around here that have free internet would be as follows; Denny's, IHoP, Starbucks, McDonald's, Library, Panera Bread, Steak n' Shake, Taco Bell, A & W, and a number of other places.
The one's I'm familiar with that are more portable but not free would be Wireless Hot Spots and just getting a wireless tether app on your phone.
Remote Programs
What to use to get in? The main two I'm familiar with are LogMeIn and Splashtop. There's also the normal Remote Desktop Connection, but most of those require a VPN for work and I haven't found a good VPN for a phone yet. I normally log in to my home desktop through splashtop and remote my server if it's just starting a job. If I need to do a bit more work, I tend to lean towards LogMeIn and break out the tablet.
This is just a small list of what I use to make my life easier. I hope it makes your life easier as well.
Server Access Anywhere!
There was a post recently about remote connections from phones. That got me thinking. I tend to keep a stronger set of RDC type tools than most people do. I like toys, I think that's the simple answer. The long one is that I like the convenience of being at my Sisters watching a movie with all our families and fixing the server without driving in. This will be a short list today.
Remote connections
You can get these a couple of ways. The free ways is to be aware of the fast food places around. Other places have internet for free... but we all know where the McDonald's is. The places I've seen around here that have free internet would be as follows; Denny's, IHoP, Starbucks, McDonald's, Library, Panera Bread, Steak n' Shake, Taco Bell, A & W, and a number of other places.
The one's I'm familiar with that are more portable but not free would be Wireless Hot Spots and just getting a wireless tether app on your phone.
Remote Programs
What to use to get in? The main two I'm familiar with are LogMeIn and Splashtop. There's also the normal Remote Desktop Connection, but most of those require a VPN for work and I haven't found a good VPN for a phone yet. I normally log in to my home desktop through splashtop and remote my server if it's just starting a job. If I need to do a bit more work, I tend to lean towards LogMeIn and break out the tablet.
This is just a small list of what I use to make my life easier. I hope it makes your life easier as well.
24.12.13
Pre-Holiday Checks: Day 12
Expecting that great Christmas Day dinner, fire roaring, kids screaming, Excedrin stock prices skyrocketing and maybe a little something for you? I hope your servers are in good shape. No one likes being called in on such a day. This isn't things to check on your server. This is just a list of things I've found painful not to listen to over the years.
Check list!
Have your backups failed in the last week?
Verify that you have good backups. Nothing is worse than taking the day off based off hopes that nothing breaks. If your backups are bad or missing then the idea of rest or relaxation may be a bit harder to grasp.
Do you know if your restores will work?
Hopefully you have an area to test your restores. It's difficult to do so without adequate hardware. Knowing that your backups work won't save you from being called... But it does make the turkey dinner taste better. Ham for my house.
Have you made any changes this week?
Don't create self hating servers. Don't do that to yourself if you can avoid it. It's just basic bad practice to make major changes when most of your support staff is on vacation. If it's unavoidable due to business reasons, do it of course, but double, triple check everything and have a back-out plan and maybe an emergency bare metal recovery ready.
Do you have replication setup?
Maybe you have replication in your environment, maybe you don't. Not everything needs that level of protection and not everyone can afford spare servers. If your environment meets the needs of having replication... Verify if that server is also in good running condition and make sure to check that the mirror is synced or logs are being successfully sent.
Is your DR plan ready?
Having a Disaster Recovery Plan ready will expedite downtime and get you home quicker. If your desk has the plan, they know who to call when problem A happens and can get everyone on site with you to fix it quicker and get everyone back home.
Is your VPN setup and ready if you need it at home?
It does make it difficult to to work from home if you cannot connect. Before you go home today, it would be good to test if you can hit all your servers from your house. Take lunch and go to Denny's if you need to test it on their WI-FI.
Do you have all of your contact lists?
Driving to work just to get someone's phone number because you need someone to check on one thing can ruin your day. Don't leave that contact list at work.
Do you have extra trash bags?
This one is just because I hate cleaning up wrapping paper all day long. This isn't database related... I'm just proactively lazy.
Check list!
Have your backups failed in the last week?
Verify that you have good backups. Nothing is worse than taking the day off based off hopes that nothing breaks. If your backups are bad or missing then the idea of rest or relaxation may be a bit harder to grasp.
Do you know if your restores will work?
Hopefully you have an area to test your restores. It's difficult to do so without adequate hardware. Knowing that your backups work won't save you from being called... But it does make the turkey dinner taste better. Ham for my house.
Have you made any changes this week?
Don't create self hating servers. Don't do that to yourself if you can avoid it. It's just basic bad practice to make major changes when most of your support staff is on vacation. If it's unavoidable due to business reasons, do it of course, but double, triple check everything and have a back-out plan and maybe an emergency bare metal recovery ready.
Do you have replication setup?
Maybe you have replication in your environment, maybe you don't. Not everything needs that level of protection and not everyone can afford spare servers. If your environment meets the needs of having replication... Verify if that server is also in good running condition and make sure to check that the mirror is synced or logs are being successfully sent.
Is your DR plan ready?
Having a Disaster Recovery Plan ready will expedite downtime and get you home quicker. If your desk has the plan, they know who to call when problem A happens and can get everyone on site with you to fix it quicker and get everyone back home.
Is your VPN setup and ready if you need it at home?
It does make it difficult to to work from home if you cannot connect. Before you go home today, it would be good to test if you can hit all your servers from your house. Take lunch and go to Denny's if you need to test it on their WI-FI.
Do you have all of your contact lists?
Driving to work just to get someone's phone number because you need someone to check on one thing can ruin your day. Don't leave that contact list at work.
Do you have extra trash bags?
This one is just because I hate cleaning up wrapping paper all day long. This isn't database related... I'm just proactively lazy.
23.12.13
Can Your Database Survive A Bus? Final Training Giveaway!: Day 11
This is the final training give away for a free month of Pluralsight! A comment on this post gets your name in the hat. The only rule is that you have an E-Mail address for me to mail the winner. Next week is the final giveaway this month in the form of this awesome book. I'll ship it for free to anywhere in the continental US. If you live outside of the US, I'll send you a code for 25$ at Amazon instead. Good luck to the winner!
Can You Survive A Bus?
Odd Title I know. Let's talk about the bus theory. You my good Sir (and Ma'am) hold the keys to the kingdom. You have a locked down fist protecting the companies data and helped keep everyone gainfully employed. That's right, stand back and give yourself a round of applause!
Now let's take the bus theory for a ride. You're out and about and BAM! Cross town 2:30 nails you. With you gone, who keeps the kingdom afloat? Do you have a Jr in place with good documentation to fix that job that breaks every third Friday when the moon is full? Do you have a break the glass password stored somewhere in case of such an emergency? How are you contingency plans looking?
Let's address the cheapest ones first.
Break-The-Glass Passwords
This one is good for protection as well as showing the company you're rational and responsible. You need to get a small envelope and put an account name and password in it sealed. Give it to your CIO or anyone with a safe in their office you trust. For your own sanity, put a trigger on that login name that sends you an email if it's ever used. Now if you get hit by a bus, the world for your company doesn't end. If someone opens it and logs in, you know who to point at for something breaking.
Documentation
If something breaks out of the normal google searches... Help yourself out by writing down what to do. Document odd hot fixes or code changes. If you ever get the funds to hire a second DBA or bring that SA over who's been interested... Documentation may make training easier as well. If you have a big red button you don't want pushed, it's best to hang a sign on it.
Contingency Plans
If the world comes crashing down now, can you fix it? Of course you can. Can your network admin? Maybe... but let's improve those odds.This ties in with a Break-The-Glass password as well as good documentation. If we have this all in place we can rest easy and maybe even take a vacation! If the server crashes and they need to reboot the IIS box before they bring up the database... having that information is a lifesaver. Consider it free insurance for your time off.
Hire A Jr. DBA
Now if you're really lucky... you can prove you need a second DBA. This may be a direct hire Sr., A Jr. being brought in, or even Ted from the SA shop. You now have at your disposal an emergency password, full documentation on how to fix the most common issues and a step by step plan. I love sleeping at night, I'm sure you do as well. With a second hand to answer that on-call phone, maybe we can make this dream a reality.
Can You Survive A Bus?
Odd Title I know. Let's talk about the bus theory. You my good Sir (and Ma'am) hold the keys to the kingdom. You have a locked down fist protecting the companies data and helped keep everyone gainfully employed. That's right, stand back and give yourself a round of applause!
Now let's take the bus theory for a ride. You're out and about and BAM! Cross town 2:30 nails you. With you gone, who keeps the kingdom afloat? Do you have a Jr in place with good documentation to fix that job that breaks every third Friday when the moon is full? Do you have a break the glass password stored somewhere in case of such an emergency? How are you contingency plans looking?
Let's address the cheapest ones first.
Break-The-Glass Passwords
This one is good for protection as well as showing the company you're rational and responsible. You need to get a small envelope and put an account name and password in it sealed. Give it to your CIO or anyone with a safe in their office you trust. For your own sanity, put a trigger on that login name that sends you an email if it's ever used. Now if you get hit by a bus, the world for your company doesn't end. If someone opens it and logs in, you know who to point at for something breaking.
Documentation
If something breaks out of the normal google searches... Help yourself out by writing down what to do. Document odd hot fixes or code changes. If you ever get the funds to hire a second DBA or bring that SA over who's been interested... Documentation may make training easier as well. If you have a big red button you don't want pushed, it's best to hang a sign on it.
Contingency Plans
If the world comes crashing down now, can you fix it? Of course you can. Can your network admin? Maybe... but let's improve those odds.This ties in with a Break-The-Glass password as well as good documentation. If we have this all in place we can rest easy and maybe even take a vacation! If the server crashes and they need to reboot the IIS box before they bring up the database... having that information is a lifesaver. Consider it free insurance for your time off.
Hire A Jr. DBA
Now if you're really lucky... you can prove you need a second DBA. This may be a direct hire Sr., A Jr. being brought in, or even Ted from the SA shop. You now have at your disposal an emergency password, full documentation on how to fix the most common issues and a step by step plan. I love sleeping at night, I'm sure you do as well. With a second hand to answer that on-call phone, maybe we can make this dream a reality.
20.12.13
Unused Heaps? - Quick Tips: Day 10
Don't forget! This coming Monday is the last Monday to win a free month of Pluralsight! All it takes is a single comment on the post that day and some way to get your E-Mail address to give you the code. The Monday following that, December 30th, will have a different prize. I will be giving away a free book and mailing it to your house. If you live out of the continental US, I will still send you a 25$ Amazon card to be used however you want. It's at least something to ring in the new year. ^.^
A question came up recently. How can you find heap tables that have not been used? This is the best answer I have so far. This will allow you to pull a list of all unused heap tables since last reboot. Keep that mind. This will only show you since last reboot or last time the dmv's were cleared for any reason.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is null
order by page_count desc
This will work for multiple databases. Be careful though. This will take some time to run on larger systems.
--multiple database use
exec sp_msforeachdb @command1 = '
use ?
begin
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
end'
Alternately, you can also see what heap tables you have that are in use and how big they are.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select distinct db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
order by page_count desc
A question came up recently. How can you find heap tables that have not been used? This is the best answer I have so far. This will allow you to pull a list of all unused heap tables since last reboot. Keep that mind. This will only show you since last reboot or last time the dmv's were cleared for any reason.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is null
order by page_count desc
This will work for multiple databases. Be careful though. This will take some time to run on larger systems.
--multiple database use
exec sp_msforeachdb @command1 = '
use ?
begin
declare @db_id smallint
set @db_id = DB_ID(db_name())
select db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
end'
Alternately, you can also see what heap tables you have that are in use and how big they are.
--single database use
declare @db_id smallint
set @db_id = DB_ID(db_name())
select distinct db.name as DatabaseName, sch.schema_id as SchemaName, tbl.name as TableName, idx.name as Indexname,
stats.index_type_desc as IndexType, stats.page_count as PageCount
from sys.dm_db_index_physical_stats (@db_id, null,null,null,null) stats
left outer join sys.dm_db_index_usage_stats as usage on usage.object_id = stats.object_id
inner join sys.tables as tbl on tbl.object_id = stats.object_id
inner join sys.indexes as idx on idx.object_id = stats.object_id and idx.index_id = stats.index_id
inner join sys.schemas as sch on sch.schema_id = tbl.schema_id
inner join sys.databases as db on db.database_id = stats.database_id
where stats.index_id = 0
and usage.object_id is not null
order by page_count desc
19.12.13
To All Recruiters - Canned Emails Kill Kittens: Day 9
Small disclaimer
I'm going to file this one under rant. Let me first say that there are some wonderful recruiting agencies out there. In OKC there are actually a few. Two specifically stick out as they've helped our SQL community without asking for much spotlight time. I would highly recommend GDH Consulting. Travis Warner has been an excellent host and very good to us. Augustine Wiah from TekSystems has shown a genuine interest in making a better link here and helping get rid of some of the bad blood between IT and consulting in general.
With the niceties covered...
Why... Why do I get 10 E-Mails a day blind marked asking If I'm interested in a fantastic help desk position? the exact quote is below:
"I am interested in speaking with you as I have been impressed with your Help Desk experience. I have helped countless professionals advance their career with top companies and wanted to have a conversation with you. Please give me a call at the number below or just reply to this email and let me know a good time to reach out to you."
Don't get me wrong. I love having help desks. The great ones are essential for any business. There is no way my help desk experience is impressive. I've got SA background, DBA background, Security background... Why target me for a help desk?
Now, now... I know. It was an email with a great generated response canned in there with just my name auto filled into the TO: line and the Subject: line. Still, this is the entirety of my complaint! They want a number, meet time, face time, new resume that they can forward along... and we still don't get anything semi personalized? At least skim the E-Mail before you send it.
We can help the healing begin...
This post was supposed to be about what DMV's I think are very useful. Instead I choose to rant. Oh well, I'll just get that post out later. You all could post any recruiter horror stories you may have. Come on, let it out.
Don't forget! only two more Mondays left this month! That means two more prizes!
I'm going to file this one under rant. Let me first say that there are some wonderful recruiting agencies out there. In OKC there are actually a few. Two specifically stick out as they've helped our SQL community without asking for much spotlight time. I would highly recommend GDH Consulting. Travis Warner has been an excellent host and very good to us. Augustine Wiah from TekSystems has shown a genuine interest in making a better link here and helping get rid of some of the bad blood between IT and consulting in general.
With the niceties covered...
Why... Why do I get 10 E-Mails a day blind marked asking If I'm interested in a fantastic help desk position? the exact quote is below:
"I am interested in speaking with you as I have been impressed with your Help Desk experience. I have helped countless professionals advance their career with top companies and wanted to have a conversation with you. Please give me a call at the number below or just reply to this email and let me know a good time to reach out to you."
Don't get me wrong. I love having help desks. The great ones are essential for any business. There is no way my help desk experience is impressive. I've got SA background, DBA background, Security background... Why target me for a help desk?
Now, now... I know. It was an email with a great generated response canned in there with just my name auto filled into the TO: line and the Subject: line. Still, this is the entirety of my complaint! They want a number, meet time, face time, new resume that they can forward along... and we still don't get anything semi personalized? At least skim the E-Mail before you send it.
We can help the healing begin...
This post was supposed to be about what DMV's I think are very useful. Instead I choose to rant. Oh well, I'll just get that post out later. You all could post any recruiter horror stories you may have. Come on, let it out.
Don't forget! only two more Mondays left this month! That means two more prizes!
18.12.13
Popular DBCC's And Our Third Winner Of The Month!: Day 8
Winner Announcement!
The third winner of this month is Annapu Reddy Gayathri! I've seen a nice increase of participants so far. I have one more month of Pluralsight to give away then it's on to the book! Dec 30th I will be mailing a book to one lucky winner. Something that just came to my mind... Mailing the book will be for continental US only. I don't want to stop anyone out of country from joining.... If you win and you're from another country I will instead send you a code for 25$ to amazon to purchase what ever you wish.
Popular DBCC Commands!
These are the DBCC commands I catch myself using the most. I'm including the reasons I use them. Do you have any suggestions to throw in here?
DBCC CheckDB - I mainly use it to check for any consistency issues. I prefer to have this running daily, or when I do my weekly restores to test my backups, I run DBCC CHECKDB on that database. It can let you know of problems that you can fix prior to losing data.
DBCC INPUTBUFFER - Basic use is to show what the last command sent from a specific spid did.Good for tracking down what a blocking query is doing.
DBCC OPENTRAN - This shows what the oldest active transaction is. You can use this to see if you have active transactions or even to see what may be holding replication up between servers.
DBCC FREEPROCCACHE - This removes your plan cache. It's not something I'd advise unless you're isolating a specific issue, but it's good to test query times when you don't want the whole thing reliant on ram. Best on a test box though.
DBCC FREESESSIONCACHE - This flushes the distributed query connection cache.This is another one that I'd advise against using unless you're isolating a specific issue. I use this for testing queries as well.
DBCC FREESYSTEMCACHE - This removes all unused cache entries. SQL does this automatically, but this is the manual version. I like to use this when I'm testing queries. It's nice to at least think I have a clean slate.
DBCC SHRINKFILE - This allows you to shrink database files one by one to either specified sizes or to as much as it will allow. Now now, let me defend myself first. Shrinks are not all bad. They have a good use occasionally! I wouldn't use them daily... but if you're moving a lot of data around and building out partitions, you have a use to shrink the old files. Maybe you ran out of disk space by accident and your logs are huge since the database in full recovery didn't have a place to backup? These are good uses.
DBCC SHRINKDATABASE - This shrinks a database as a whole. Again like with shrink file... there are uses. If you're moving the database to a small test box, you may need the space before you can fit that restore there.
DBCC UPDATEUSAGE - This helps report accurate row counts. My understanding is that this was most useful coming from 2000 to any higher version. It still catches some that aren't listed else where.
The third winner of this month is Annapu Reddy Gayathri! I've seen a nice increase of participants so far. I have one more month of Pluralsight to give away then it's on to the book! Dec 30th I will be mailing a book to one lucky winner. Something that just came to my mind... Mailing the book will be for continental US only. I don't want to stop anyone out of country from joining.... If you win and you're from another country I will instead send you a code for 25$ to amazon to purchase what ever you wish.
Popular DBCC Commands!
These are the DBCC commands I catch myself using the most. I'm including the reasons I use them. Do you have any suggestions to throw in here?
DBCC CheckDB - I mainly use it to check for any consistency issues. I prefer to have this running daily, or when I do my weekly restores to test my backups, I run DBCC CHECKDB on that database. It can let you know of problems that you can fix prior to losing data.
DBCC INPUTBUFFER - Basic use is to show what the last command sent from a specific spid did.Good for tracking down what a blocking query is doing.
DBCC OPENTRAN - This shows what the oldest active transaction is. You can use this to see if you have active transactions or even to see what may be holding replication up between servers.
DBCC FREEPROCCACHE - This removes your plan cache. It's not something I'd advise unless you're isolating a specific issue, but it's good to test query times when you don't want the whole thing reliant on ram. Best on a test box though.
DBCC FREESESSIONCACHE - This flushes the distributed query connection cache.This is another one that I'd advise against using unless you're isolating a specific issue. I use this for testing queries as well.
DBCC FREESYSTEMCACHE - This removes all unused cache entries. SQL does this automatically, but this is the manual version. I like to use this when I'm testing queries. It's nice to at least think I have a clean slate.
DBCC SHRINKFILE - This allows you to shrink database files one by one to either specified sizes or to as much as it will allow. Now now, let me defend myself first. Shrinks are not all bad. They have a good use occasionally! I wouldn't use them daily... but if you're moving a lot of data around and building out partitions, you have a use to shrink the old files. Maybe you ran out of disk space by accident and your logs are huge since the database in full recovery didn't have a place to backup? These are good uses.
DBCC SHRINKDATABASE - This shrinks a database as a whole. Again like with shrink file... there are uses. If you're moving the database to a small test box, you may need the space before you can fit that restore there.
DBCC UPDATEUSAGE - This helps report accurate row counts. My understanding is that this was most useful coming from 2000 to any higher version. It still catches some that aren't listed else where.
17.12.13
Database Recovery: Day 7
Recently we were talking about Backups and how you need to test them often. The most complete way to test a backup is to restore from it. The syntax is already documented out there. Also don't forget that our contest giveaway is tomorrow! All you need is a single comment on this post containing an E-Mail address!
What plan to use?
This relates more to the backups themselves, not the restores... but it all factors in. Do you only take full backups on Monday at 0100? Do you have it in Full mode? If so, how many files do you get to restore when it fails Sunday at 1900? Don't get caught in that. Something to consider would be running differentials.My personal rule is to only use differentials if the full backup is over 3x the size.once it reaches 50% or higher, that adds a lot of time to your restore.I like having full backups on Fridays right after main business hours, Differentials taken every day at that same time and on Saturday or Sunday, script out a full restore to a secondary server. You'll have your backups running daily, tested every week, and all of it will be automated. You can just check for that nasty E-Mail saying something failed.
Secondary Server
You can restore your backups to a secondary server. Good targets for this are both your development and test servers.If you have one, ask your developers if they have any issues with weekly or even monthly restores of current data. It may be a great way to help them work on what you all are currently pushing out. De-identification of data may be required, keep that in mind.
Virtual
Do you not have a spare server laying around? Ask for enough storage on your san or if it fits, even an external drive and restore it to something like VMWorkstation. Something is better than nothing here. If your backups cannot fit on a 3 TB external, You're probably at the size that helps you push to get a spare server for testing backups. If they don't want a whole server for just backups, offer its use for your developers if they don't have a server currently.
How Often?
I prefer to test weekly if possible, monthly if you have too. The real question is, how much is your data worth to you? Can you lose a month of data and noone care? If so, do it every other week or so. Remember the SLA's you may have in place. If you have to be able to restore 24/7, testing your backups is the only way to be sure.
What plan to use?
This relates more to the backups themselves, not the restores... but it all factors in. Do you only take full backups on Monday at 0100? Do you have it in Full mode? If so, how many files do you get to restore when it fails Sunday at 1900? Don't get caught in that. Something to consider would be running differentials.My personal rule is to only use differentials if the full backup is over 3x the size.once it reaches 50% or higher, that adds a lot of time to your restore.I like having full backups on Fridays right after main business hours, Differentials taken every day at that same time and on Saturday or Sunday, script out a full restore to a secondary server. You'll have your backups running daily, tested every week, and all of it will be automated. You can just check for that nasty E-Mail saying something failed.
Secondary Server
You can restore your backups to a secondary server. Good targets for this are both your development and test servers.If you have one, ask your developers if they have any issues with weekly or even monthly restores of current data. It may be a great way to help them work on what you all are currently pushing out. De-identification of data may be required, keep that in mind.
Virtual
Do you not have a spare server laying around? Ask for enough storage on your san or if it fits, even an external drive and restore it to something like VMWorkstation. Something is better than nothing here. If your backups cannot fit on a 3 TB external, You're probably at the size that helps you push to get a spare server for testing backups. If they don't want a whole server for just backups, offer its use for your developers if they don't have a server currently.
How Often?
I prefer to test weekly if possible, monthly if you have too. The real question is, how much is your data worth to you? Can you lose a month of data and noone care? If so, do it every other week or so. Remember the SLA's you may have in place. If you have to be able to restore 24/7, testing your backups is the only way to be sure.
16.12.13
Talk Your Boss Into A Free Lunch and Giveaways!: Day 6
Monday Giveaway!
It's Monday again! I know we all hate Mondays, but let's have a Fun Monday!It's another week to give away a free month of Pluralsight! We've had Two Winners so far! Today is chance number three! Remember, you need to post here with an E-Mail address either on the site or in your profile so I can give you the prize. Moving on....
Free Lunch From Your Boss?! No Way..
With all these lunch webcasts, why not talk your boss into letting you watch them in a meeting room once or twice a week? Let's throw a twist in it. Ask if they'd consider inviting the Devs and DBAs into a conference room for their lunch and throw some cheap pizza at them. Think about it!? We can start building a stronger bridge between our groups and get free training for us at the cost of 6 meat lovers and a veggie pizza for Steve! Second thought, how about some ribs?
How Can We Hide The Cost?
We can mark this one under a training budget, personal development, group building or a number of things. Maybe you could even get them to bill HR for the pizza or ribs since we're creating harmony in the work place. There are plenty of places to get free webcasts live for your group. You can also play some canned old ones for your group. There's a good list of free webcasts and videos Here.
Gather Around, Group Hug!
I'm not talking about badgering your boss into giving you free food, but it's something you can bring up as a good idea. You're a team player, one that's interested in making your co-workers better. Show it off! This can stair-step you into asking for room and board to get to SQL Saturdays or even to get into the Summit! Getting these lunch meetings together is a great way to share what you learn when you get back. Don't forget to give out some swag you got. It helps the team be happier you got to go instead of just sad they didn't.
Here's a good one for comments! Free PluralSight account for the comment I pull out of the hat. How about you tell me how you get your boss to cover expenses. Do you get a free lunch? How about just mileage to get to training out of state? Maybe even just a packed lunch? A bit of a left turn, but if your company doesn't help promote you, you might consider using that network you've been building to get that job you really want.
It's Monday again! I know we all hate Mondays, but let's have a Fun Monday!It's another week to give away a free month of Pluralsight! We've had Two Winners so far! Today is chance number three! Remember, you need to post here with an E-Mail address either on the site or in your profile so I can give you the prize. Moving on....
Free Lunch From Your Boss?! No Way..
With all these lunch webcasts, why not talk your boss into letting you watch them in a meeting room once or twice a week? Let's throw a twist in it. Ask if they'd consider inviting the Devs and DBAs into a conference room for their lunch and throw some cheap pizza at them. Think about it!? We can start building a stronger bridge between our groups and get free training for us at the cost of 6 meat lovers and a veggie pizza for Steve! Second thought, how about some ribs?
How Can We Hide The Cost?
We can mark this one under a training budget, personal development, group building or a number of things. Maybe you could even get them to bill HR for the pizza or ribs since we're creating harmony in the work place. There are plenty of places to get free webcasts live for your group. You can also play some canned old ones for your group. There's a good list of free webcasts and videos Here.
Gather Around, Group Hug!
I'm not talking about badgering your boss into giving you free food, but it's something you can bring up as a good idea. You're a team player, one that's interested in making your co-workers better. Show it off! This can stair-step you into asking for room and board to get to SQL Saturdays or even to get into the Summit! Getting these lunch meetings together is a great way to share what you learn when you get back. Don't forget to give out some swag you got. It helps the team be happier you got to go instead of just sad they didn't.
Here's a good one for comments! Free PluralSight account for the comment I pull out of the hat. How about you tell me how you get your boss to cover expenses. Do you get a free lunch? How about just mileage to get to training out of state? Maybe even just a packed lunch? A bit of a left turn, but if your company doesn't help promote you, you might consider using that network you've been building to get that job you really want.
13.12.13
Keeping Up With Training: Day 5
Let's keep this talk going about what you can do. We know I'm rather big on user groups and SQL Saturdays... I also love books, virtual chapters and most online training. I still have another give away for this coming Monday. It's a free month of PluralSight. I think it's great training. I even have a whole post of training. Let's get more to the point.
How Do You Train?
I prefer to have some good music on when I'm working. I love to have music up on my phone, SQL on one screen and a bunch of powerpoints or blogs up on the other. If it's a more targeted SQL training item, I tend to put it on one screen and keep the blogs to a minimum using them only to support what I'm looking up.
When Do You Train?
I get much more out of my mornings. I tend to get more writing done and videos watched. As far as making code work... I tend to build things closer to the end of the day or right before bed. The tired stress seems to help me push stuff out faster. I'll correct my mistakes in the morning. ^.^;
Ideal Conditions?
I think this is dependent on the person. I prefer a warm hammock, a tablet with some videos and a cold drink in the summer. The next best is sitting at my desk with a game on one screen, video on another and notes I'm taking on the last. I don't know why, splitting my thinking tends to make me rewind and watch a video a few times. It's perfectly normal to want a single screen or TV and some quite time. I just don't function like that.
Do You Do Best In A Group?
Why not set that training video up in the conference and have a Tuesday / Thursday lunch meeting and get some training done? Who can complain that you and the Devs are training together in the conference room? It'll be such a strange sight that they'll all just avoid that area for fear war will break out. It's a great way to find some common ground and figure out how your co-workers think on both sides.
How Do You Train?
Feedback is key to me knowing the working world as a whole! Help me out here.
How Do You Train?
I prefer to have some good music on when I'm working. I love to have music up on my phone, SQL on one screen and a bunch of powerpoints or blogs up on the other. If it's a more targeted SQL training item, I tend to put it on one screen and keep the blogs to a minimum using them only to support what I'm looking up.
When Do You Train?
I get much more out of my mornings. I tend to get more writing done and videos watched. As far as making code work... I tend to build things closer to the end of the day or right before bed. The tired stress seems to help me push stuff out faster. I'll correct my mistakes in the morning. ^.^;
Ideal Conditions?
I think this is dependent on the person. I prefer a warm hammock, a tablet with some videos and a cold drink in the summer. The next best is sitting at my desk with a game on one screen, video on another and notes I'm taking on the last. I don't know why, splitting my thinking tends to make me rewind and watch a video a few times. It's perfectly normal to want a single screen or TV and some quite time. I just don't function like that.
Do You Do Best In A Group?
Why not set that training video up in the conference and have a Tuesday / Thursday lunch meeting and get some training done? Who can complain that you and the Devs are training together in the conference room? It'll be such a strange sight that they'll all just avoid that area for fear war will break out. It's a great way to find some common ground and figure out how your co-workers think on both sides.
How Do You Train?
Feedback is key to me knowing the working world as a whole! Help me out here.
12.12.13
SQL Saturdays: Day 4
Well, we talked about user groups... Let's talk about their big brother; SQL Saturdays. @SQLCenturion runs them here in OKC. They are fantastic ways to get lots of good training for a great price... FREE! Let's break this up like the user group post.
8 Hours of High Quality Training
That's right. One full day of training on multiple SQL subjects from DBA to Dev to BI. Have a specific pain point at work? Target those classes! Normally there's 1-2 classes on about every subject. The people that pick out the classes do a good job getting a good range of 100 level to 300 level classes for you. There may be a 10$ lunch fee... but it's not required.
They're All Over The World!
Do you not have time to make it to the SQL Saturday in New Mexico? Don't worry! There's one in Texas, Kansas and Oklahoma, even some to the West! Don't think that just because you're not from Oklahoma that you won't be welcome. We love seeing people from all over the country. When you're there, you'll even get the insider track on good places to eat and cool places to visit.
Swag
No, not that kind of swag. Alot of venders give away free toys or tools just to keep their name on your mind. Who knows, maybe they'll be selling a product you could really use! This is a great time to get documentation to beg for that tool that will help the fact your boss still hasn't hired those two missing DBAs.
Networking
Here's a great place to meet hundreds of people in your same field who may be looking to hire you! It's a great place to get out and meet people. Even if you aren't looking for a job... the networking aspect will help when you have questions only that BI expert can answer. Give her a call! She needs help with some policies you have answers to.
International Preparations
You want to be an international speaker? The best places to start is with your own user groups or with your office. The next step is these wonderful SQLSaturdays. After you make the regional step, you can move on to National! This is a good stepping stone to get your name known and eventually have you speaking at the Summit and possibly all over the world. A SQL person who travels the world at the drop of a hat... Saving unsuspecting databases from suffering only you can prevent. They will call you... Su.... eh, let's not. Still, the idea is pretty cool.
8 Hours of High Quality Training
That's right. One full day of training on multiple SQL subjects from DBA to Dev to BI. Have a specific pain point at work? Target those classes! Normally there's 1-2 classes on about every subject. The people that pick out the classes do a good job getting a good range of 100 level to 300 level classes for you. There may be a 10$ lunch fee... but it's not required.
They're All Over The World!
Do you not have time to make it to the SQL Saturday in New Mexico? Don't worry! There's one in Texas, Kansas and Oklahoma, even some to the West! Don't think that just because you're not from Oklahoma that you won't be welcome. We love seeing people from all over the country. When you're there, you'll even get the insider track on good places to eat and cool places to visit.
Swag
No, not that kind of swag. Alot of venders give away free toys or tools just to keep their name on your mind. Who knows, maybe they'll be selling a product you could really use! This is a great time to get documentation to beg for that tool that will help the fact your boss still hasn't hired those two missing DBAs.
Networking
Here's a great place to meet hundreds of people in your same field who may be looking to hire you! It's a great place to get out and meet people. Even if you aren't looking for a job... the networking aspect will help when you have questions only that BI expert can answer. Give her a call! She needs help with some policies you have answers to.
International Preparations
You want to be an international speaker? The best places to start is with your own user groups or with your office. The next step is these wonderful SQLSaturdays. After you make the regional step, you can move on to National! This is a good stepping stone to get your name known and eventually have you speaking at the Summit and possibly all over the world. A SQL person who travels the world at the drop of a hat... Saving unsuspecting databases from suffering only you can prevent. They will call you... Su.... eh, let's not. Still, the idea is pretty cool.
11.12.13
User Groups and Winners!: Day 3
Today's winner is! Aadhar Joshi!
Congratulations for winning a free month of PluralSight. I'm E-Mailing you the code right now.
We still have two free months to give away and a great book on the 30th!
Update: Everyone is eligible for the book drawing on the 30th. You can enter your name every Monday for the Pluralsight give away as long as you have not already won one. There are two more Pluralsight codes to give away and the book at the end of the month. Good luck to all who try! Week 1 only had one person competing. Week 2 had six people.
User Groups
There are multiple user groups around the country online and offline. I'm mostly going to concern myself with the offline user groups for a moment. A great place to find them is to go to the SQLPass site.It's a great place for a few reasons.
Training
Who doesn't love free training!? Well... some people I'm sure. But seriously, user groups are fantastic ways to get free training. Depending on what's going on, you can get very involved in the discussions. User group meetings don't have to be boring presentations only. The OKC SQL user group even hosted a Jeopardy style presentation with prizes. Even with the ice on the ground, the turnout was amazing.
Networking
I know, you love your job. I've heard you talking about how much you love your boss and all your co-workers. Have they stopped reading? Good. Not all jobs are permanent. I'm not saying quit... I'm saying it's good to know what's around and who's looking. Maybe they need a DBA, but after talking to them you find out they just have a simple problem you can fix over the weekend. Who doesn't like a bit of quick consulting money? If nothing else, building a network will help you when you decide to start looking.
First Time Presentation
So you think you can dance? Well let's not jump to the big stage right away. Here's where you can shine and get some good pier review before you end up on YouTube. These are people who have presented before or just starting out who need to learn. It's a great place to open up some 101 information. You do not need to present internals and talk about MCM level items just to get some light. Let's keep the community strong and get our friends trained. You may know something they don't.
Preparation for SQL Saturdays
Do you want to start becoming a national or even international speaker? User groups are a great way to prepare to speak at a SQL Saturday. They're great to debut a new presentation you want to get out into the world. Do you know who you need to talk to about SQL Saturday? I'm guessing your local user group has some information for you. ^.~
Prizes
We all love prizes. Most user groups at least have some prizes to give away. Why not go for a free PluralSight card or some books. If they have no prizes... at least you win free training!
Don't have a user group?
Contact someone in the community to help you set one up! If you're really interested, getting sponsorship is easier than you think. If you need help setting one up, I can try and get you in contact with some good people.
Congratulations for winning a free month of PluralSight. I'm E-Mailing you the code right now.
We still have two free months to give away and a great book on the 30th!
Update: Everyone is eligible for the book drawing on the 30th. You can enter your name every Monday for the Pluralsight give away as long as you have not already won one. There are two more Pluralsight codes to give away and the book at the end of the month. Good luck to all who try! Week 1 only had one person competing. Week 2 had six people.
User Groups
There are multiple user groups around the country online and offline. I'm mostly going to concern myself with the offline user groups for a moment. A great place to find them is to go to the SQLPass site.It's a great place for a few reasons.
Training
Who doesn't love free training!? Well... some people I'm sure. But seriously, user groups are fantastic ways to get free training. Depending on what's going on, you can get very involved in the discussions. User group meetings don't have to be boring presentations only. The OKC SQL user group even hosted a Jeopardy style presentation with prizes. Even with the ice on the ground, the turnout was amazing.
Networking
I know, you love your job. I've heard you talking about how much you love your boss and all your co-workers. Have they stopped reading? Good. Not all jobs are permanent. I'm not saying quit... I'm saying it's good to know what's around and who's looking. Maybe they need a DBA, but after talking to them you find out they just have a simple problem you can fix over the weekend. Who doesn't like a bit of quick consulting money? If nothing else, building a network will help you when you decide to start looking.
First Time Presentation
So you think you can dance? Well let's not jump to the big stage right away. Here's where you can shine and get some good pier review before you end up on YouTube. These are people who have presented before or just starting out who need to learn. It's a great place to open up some 101 information. You do not need to present internals and talk about MCM level items just to get some light. Let's keep the community strong and get our friends trained. You may know something they don't.
Preparation for SQL Saturdays
Do you want to start becoming a national or even international speaker? User groups are a great way to prepare to speak at a SQL Saturday. They're great to debut a new presentation you want to get out into the world. Do you know who you need to talk to about SQL Saturday? I'm guessing your local user group has some information for you. ^.~
Prizes
We all love prizes. Most user groups at least have some prizes to give away. Why not go for a free PluralSight card or some books. If they have no prizes... at least you win free training!
Don't have a user group?
Contact someone in the community to help you set one up! If you're really interested, getting sponsorship is easier than you think. If you need help setting one up, I can try and get you in contact with some good people.
Subscribe to:
Comments (Atom)