The Multifunctioning DBA

August 25, 2010  8:00 AM

SSIS issue connecting to database

Colin Smith Colin Smith Profile: Colin Smith

I have been welcomed back from vacation with an SSIS package that is no longer able to connect to an instance. I have been troubleshooting the issue and I have found that the location where the developer was storing connection objects no longer exists. I had the developer save the connection properties again into a new location, migrate the package and now all is working again.

August 24, 2010  4:52 PM

SQL Server Performance issues

Colin Smith Colin Smith Profile: Colin Smith

I have a newly created SQL Server instance that is hosting one database that is not very large, about 10 gigs, and nothing else. The Server is configured to use 4Gigs or Memory and lock the pages in memory. The server is running SQL Server 2005 and I am seeing a large number of cxpacket waits. I ran a profile and just used the standard profiler template. I then ran that through the Database Tuning Advisor and found 10 indexes and a large number of statistics that are recommended. The Tuning Advisor says that by implementing these changes I will see a 93% improvement in perfomance. I plan on working with the application vendor and hopefully implementing at least some of the suggestions in order to resolve the issue. My hope is that this will succesfully resolve the issue and that the core of the issue is just poor design and planning on the vendors part.

August 24, 2010  4:29 PM

Back from vacation

Colin Smith Colin Smith Profile: Colin Smith

I am back from vacation. I did not go do anything fun but I just needed to get away for a while. I took a week off and man I feel refreshed. That is what a wek of mindless TV and some good video games will do for me. I am back at it now and it is a good thing that I feel refreshed. I got back into the office and I got hit hard with some cool projects and some issues that we need to run down and resolve. I am excited to be back and ready to dig in hard now. I have already started one big project and that is to modify almost every smtp address that we have in the company. I am doing this via PowerShell and I should be able to share most of it with you as I go. It is a cool project because I will have to dig into the exchange console in order to get this done and we have some pretty complex rules about who is affected and how they are affected. Any way it was good to be away and it is now good to be back.

July 30, 2010  10:00 PM

SQL Server Test Instances

Colin Smith Colin Smith Profile: Colin Smith

Lately I have had a number of test databases where the log file has just gotten way out of control and I have been close to running out of disk space. This is a problem and it is caused by someone, including me, restoring a database from production to the test instance. If the database in production uses the Full Recovery Model, then the restored database on the test instance will also be using that recovery model. Since we do not do any tran log backups on test systems, and rarely do full backups either, the log will just continue to grow. To fix the issue I always go in and change the db to the simple recovery model and then I will shrink the log file so that I can reclaim my disk. In my monitoring script that I am writing I will be adding some code in to change the recovery model to simple if it is using any other model and the instance is marked as test or dev. This should prevent me from running into this problem when databases are restored to test.

July 30, 2010  10:00 AM

Backup and Restore

Colin Smith Colin Smith Profile: Colin Smith

I am fighting a battle right now of whether or not I, the DBA, or the backup team at my company should be in charge of backups and restores of the SQL Server Databases in the company. We use Netbackup with a SQL Agent to back up the databases and I am still not sure that I am a huge fan of that. I see the things that it brings to the table and some of it is great. Some not so much. My biggest complaints is that Veritas says that the client needs to have Sysadmin role on the SQL Server. That always throws up a red flag and I hate that when I ask why they can not give me a good answer. Also I do not like that it limits my control over the backups and the restores. I am not apposed to using the tool but I am opposed to not being in control of it. I am the DBA and backups and restores are one of my primary responsibilities. I will fight this until the end and if I lose I will get in writing that I am not responsible in any way for backups and restores and therefore not held responsible for when a restore can not be completed.

Wish me luck

July 29, 2010  5:00 PM

SQL Query Troubleshooting

Colin Smith Colin Smith Profile: Colin Smith

So I am by no means good at wtiting T-SQL or optimizing the code. But, Yesterday one of our devs called me and said that he had a SSIS job that was running i about 45 minutes start taking 10 hours. Ouch that can not be good I said. I asked him what tables were involved and I opened up my Professioanl SQL Serveer 2008 Internals and Troubleshooting and found a DMV that would show missing indexes. After taking a look at that I found that if I created three indexes on the table that the dev was joining on I might be better off. I created the three indexes and asked him to run the job again. What do you know, the job was back down to the time expected. All I can say is thank you SQL Server for being smart enough to tell me what to do.

July 29, 2010  10:00 AM

SSHD in my laptop

Colin Smith Colin Smith Profile: Colin Smith

About three years ago work got me a new laptop and wanted me to be the first to try out the Solid State Hard Drive. I eagerly agreed and got the laptop. It was fast, I could tell a difference in boot time and some applications. I loved it except that the drive was only 30 Gigs. Not a big deal I thought, I will just go get an external drive and keep all my files and stuff on it and just use the internal drive for the OS and apps. Well that worked for a while. In Fact I even got another new laptop and swapped out the hard drive it came with for my SSHD. It did not last much longer though. After installing Win7 and some other new applications I was always running with less then a gig free. I finally broke down and changed it out to a 150 Gig 7200 RPM drive. I love the space that I have now but I can tell a difference on the speed. SSHD is good if you have the money to spend on one that is of a good size.

July 28, 2010  4:00 PM

PCI Compliance

Colin Smith Colin Smith Profile: Colin Smith

Right now we have a big push for PCI Compliance. They require us to encrypt all network traffic that is coming to or from any server with protected information. This is fine and makes sense, I mean we do not want sensative information flowing across the wire in plain text. I have to hand it to the architects at my office. They have come up with a nice solution to a big part of the problem. Still have some issues like how to encrypt FTP but for the most part they solved it. They are moving all these systems into a new VLAN that is segmented from the rest of the world, even our internal network can not get to it. Then they can all talk as normal since they are segregated. Then when internal customers need to connect to the server the network will automatically create an ssl vpn tunnel for all that traffic to move over. I think that is pretty cool but it is a big hassle having to move a lot of my DB Servers into this new segment. It will be nice when it is done.

July 28, 2010  10:00 AM

SQL Saturday 47 Phoenix

Colin Smith Colin Smith Profile: Colin Smith

SQL Saturday in Phoenix was originally scheduled for July 17th but it was put on hold. I am not sure of a new date at this time but I hear that the organizers are looking at sometime in February. This will be a better time of year for Phoenix to get attendees and speakers to come out. It is just so HOT in Phoenix right now. I hate the summers here and if I was not living here I would not come out for even a day during the summer for any event. I will try to keep you informed as to when and where the event will be taking place.

July 27, 2010  8:36 PM

IT Audit

Colin Smith Colin Smith Profile: Colin Smith

Recently my company went through an internal audit of our financial systems. First let me say that I am proud because we did well and do not have many issues to resolve, and no issues on the Database Side. Any way I also want to point out that the auditors are not IT people. They are accountants and they have no idea what they are asking for from us or what we give them. An example of this is that I was working in Powershell when the auditor came over to me and asked for a screenshot of all sysadmin role users on a particular server. I pulled it up in powershell and printed it out. No he said this is no good. I need the SQL Server screen that will tell me. OK I say and I go into SSMS and pull it up and print it out for him. To me this said that he does not know what he is looking at and I could, not that I ever would, give him the same screenshot from last year and he would not know.  For the remaining time that he was around I tried to point out what was what and help him learn abit about what he was asking for. Hope that will help in in future audits.

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: