The Multifunctioning DBA

September 24, 2009  4:13 PM

Powershell Remove files

Colin Smith Colin Smith Profile: Colin Smith

I have a server that attempts to backup up the SQL Server databases and it always fails because not enough space is available. This usually is not a big deal as I would run a SQL Clean up task before the backups run to remove the old files. In this case though I am using a third party utility to run the backups and it does not have that functionality. So, since I am no sqljockey yet, I have chosen Powershell as my tool. Here is what I have done.

$days = [datetime]::now.adddays(-7) E: cd \ cd Directory ls | where {$ -like "*.bak"} | rm -force cd tranlogs ls | where {$ -like "*trn"} | rm -force cd ../../Directory ls | where {$ -like "*.bak"} | rm -force cd tranlogs ls | where {$ -like "*bak"} | rm -force ls | where {$ -like "*txt"} | where{$_.creationtime -lt $days} | rm -force c:

You can see that I simple cd to the directory that I have my backupfiles in and do an rm or Remove-object. I am also keeping 7 days worth of the txt log files that the backups create. Just in case I need them. Hope that helps. Enjoy

September 24, 2009  7:30 AM

AD Audit in Powershell Script Part 2

Colin Smith Colin Smith Profile: Colin Smith

Now that we have some idea about what this script is about and what we would like to accomplish with the script lets get into it.

I have the code in the box below and I will just go over what I am doing . This is where the script will start so I am setting up my variables and then I will start to call the other functions of the script.
First I place on doing my own error checking where I care if an error occurs and I do not want to be notified or have the script stop if an error does occur so I Silently Continue. I get the time that the script started so I can log that for a few reasons. One is so that I know when the script ran last and two so I can know how long the script runs. I set up the variables that I care about such as $holdingdays, $disabledays, and $deletedays. Now I take those values to determine the dates for holdin, disable, and delete. In my company we have other companies that are on our domain and we have them separated out with OU’s that have a company name designation. that is what my $companies variable holds. Now I can start to call my functions.

########################################################## ## Main ## Set up variables and call functions of script. ########################################################## $ErrorActionPreference = "SilentlyContinue" $starttime = [datetime]::Now echo "Start 90-180 Script run at $starttime" >> "c:\90-180\90-180.log" $holdingdays = -30 $disabledays = -90 $deletedays = -180 $disabledate = [datetime]::Now.AddDays($disabledays) $deletedate = [datetime]::Now.AddDays($deletedays) $holdingdate = [datetime]::Now.AddDays($holdingdays) $companies = "name1", "name2", "name3" clear-content "c:\90-180\homedirs.txt" #echo "calling Get_Users" Get_Users #echo "calling Disable_Acconts" Disable_Accounts #Echo "Calling Get Disabled Accounts" Get_Disabled_Accounts #Echo "Calling Delete_Accounts" Delete_Accounts #Echo "Calling Mail" Mail $endtime = [datetime]::Now echo "End 90-180 Script run at $endtime" >> "c:\90-180\90-180.log" echo "#################################`n" >> "c:\90-180\90-180.log"

My next post will be about the get_users function. This is a fun one since it has to query all the DC’s.

September 23, 2009  4:52 PM

Oracle Dataguard Replication Scare

Colin Smith Colin Smith Profile: Colin Smith

I had to bring down the standby server for the power outage in our DR location a few nights agon. No big deal, shut off replication from the primary, go to the standby and do a shutdown imediate, since you can not do a shutdown on the standby server. When things cam up I go to the secondary and do a startup nomount; and then alter database mount standby database; Well I thougt that was all I needed. About 10 minutes later I get some pages about the primary not being able to connect to the standby… DUH I forgot to start the listener on the standby. No Problem. I run lsnrctl start on the standby host and I am in business now. No more pages all is good. Well I thought so until after the cold backup that we run overnight from the standby database. Now I get a page saying that recover managed standby database cancel can not execute since this is not a managed standby database. I think that is odd and I check to see if archive logs are being applied and they are not and have not been for over 13 huors now. I find that I forgot to run alter database recover managed standby database; I run that command and look at that. Now my Archive logs are being applied. It took about 4 hours to get caught up but it did. So when bringing up the standby just do the following:




If you have enabled the primary to send archive logs then you are all set.

September 23, 2009  4:35 PM

AD Audit in Powershell Script Part 1

Colin Smith Colin Smith Profile: Colin Smith

As I said before I have completed my re-write of my Active Directory Audit into Powershell. Man is it better. more functional and less code. I love it. This script goes out and searches my domain for user accounts that are old and stale. By old and stale I mean that they were both created, and have not been logged into in 90 days or more. If they meet that criteria then I go ahead and disable them, move the object to a disabled OU, and send out notifications about the account being disabled to the appropriate people so the action can be documented. That is only part one of the script.

Second is the script will now look at all the accounts that reside in the disabled OU and determine if the account has been created and that the last logon date was more than 180 days ago. If the account meets that criteria then I gather a list of all the groups that user is a member of, remove the user from the groups, delete the account, and finally I go out and see if they have a home directory folder. If they do then I move that folder and all contents to another file share so that managers and other top level employees can access that data. (I have another script that deletes that data after 30 days of inavtivity.)

So lets get into it a bit. Before we go over any code though I want to talk about AD and Replication. If you have more than one DC then you have an issue with getting accurate last logon timestamps. When a user logs in to the domain they can authenticate using any of the domain controllers but AD does not replicate that property to all the other DC’s you have. This is an issue with this script since I want to make sure I do not disable or delete an account based on information from a DC that may not have the latest information. This means that we must query all DC’s and look for the newest logon timestamp and use that.

Also just a side note, this script does require having the Quest AD Management cmdlets installed and available. You can get them at

So next post we dive into the begining part of the script.

September 22, 2009  4:55 PM

Sybase Instance Scare

Colin Smith Colin Smith Profile: Colin Smith

Monday morning my company was putting in a new UPS at our DR facility. This is great but it means that all database instances that are running at that location are coming down as power to the entire site was down for 2 hours. No big deal right, I got a list of the instances and shut them all down properly before the host machines were shutdown. A couple hours later I get a call saying that all the power is back on and that all my hosts are ready as well. I login and I start to run my startup scripts for all my Sybase instances. I run into two servers that are not starting up and this is what I find in the log files.
The configuration area in master device appears to be corrupt.
NOT COOL… I know that I have backups so that is OK but Master really in two instances… I dig around a bit more and I find that the two instances that are having issues run on the same physical host but are in Solaris Containers that use Veritas Disk mounts. I google the error to see if it may be disk related and not corruption in Master. I found it.

I called my storage guy and he looked into it. Called me about 30 minutes later and asked me to try again. WOW everything came up and I was a happy camper. Sure made for a long morning though and also made me realize the benefit to practicing DR situations. I think I will work with my team to have outages on out Test instances just so we can rebuild Master. It will be good to know and be practiced up on if it is ever not the disk.

September 18, 2009  4:37 PM

AD Audit in Powershell

Colin Smith Colin Smith Profile: Colin Smith

I have mentioned this before but I have finally done what I set out to do and re-wrote my old vbscript that audits AD Accounts based on Create Date and Last LogonDate in Powershell. This is another great example of how much better powershell is and why everyone should now it. My VB Script was just under 800 lines of code and that was without many comments that documented the process. Now I feel that documenting scripts is very important. This is because I have had to try to fix other peoples scripts when they did not document it well and it is not fun. My powershell version of the same script with added functionality as well as over 100 lines of comments is only just over 400 lines. That means that I was able to remove about 500 lines of actual code from the script as well as add functionality to the script.
I will break the script down and post each one of the functions with a good explanation of each in my next few posts. It is a very cool script and it could be added to and customized to fit any situation that you may need. My version is based on my companies policy of disabling an account after 90 days of inactivity and deleting the account after 180 days of inactivity. My company runs the script weekly and the script sends out emails about the accounts that have been disabled and deleted to the needed personal so that the appropriate paperwork can be completed for those accounts. The script is soon to be posted. I am still testing but I will have it very soon.

September 16, 2009  4:37 PM

SQL Class

Colin Smith Colin Smith Profile: Colin Smith

Found a nice article on some basic SQL. Check it out, it is never bad to have the knowledge even if you are not a DBA or SQL Developer.,289483,sid87_gci1368296,00.html?track=sy41

September 16, 2009  3:23 PM

Installing SQL Server 2008

Colin Smith Colin Smith Profile: Colin Smith

Yesterday I had to install SQL Server 2008 for the first time. I went through it on a development server first before installing on a production server. As I went through the setup process I noticed one thing that I think is long overdue. The Database Configuration step is a great addition to the install process. It is about time that MS allowed us to seperate out where all the Database and Log files will be before we actually do the install. This makes it easy to break out tempdb from the other databases and also to separate your data and log files. Make sure that you take advantage of this and plan out where you want all of the data files and log files to be on the file system. This will save some headache in the future when you have to move all of your databases as well as the system databases.

September 8, 2009  5:33 PM

MS Certification Test

Colin Smith Colin Smith Profile: Colin Smith

I have scheduled my first certification test for late October. I think that by putting the date on the calendar it will push me to finish my study more quickly. I have not been as vigilent as I would have liked to be so I am lighting the fire so to speak. Also with my new position I think that I will have some big SQL Server Projects down the road pretty quickly. That is a bigger fire for me but all the extra motivation is good for me. I have learned a lot over the last 8 months or so that I have been doing DBA work with Sybase, Oracle, and SQL Server but I still feel like I have so much to learn. Now I will be focusing more on SQL Server so I am really going to start getting my hands dirty on some fun big national projects. I am very excited to be in the mix more than I have been to this point. I am sure that I will have some good stories for you very soon.
Anyway I do not have much time now to get ready for that first test. I think that I am pretty much ready but I want to study more and play in SQL Server more before taking the test. I just do better once the pressure is on. Wish me luck and I will keep you in the loop.

August 31, 2009  4:15 PM

Support Fallen Troop Families

Colin Smith Colin Smith Profile: Colin Smith

This holiday weekend is Patriot Golf Day. You can check it out here:
Please check this out and go play some golf over the long weekend. Find a participating course near you and go play for a good cause.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: