Monthly Archives: January 2012
Yesterday I attended a Brent Ozar PLF webinar with Kendra Little (blog : twitter). The webinar, Anatomy of the SQL Server Database: Settings and Secrets, was great, as per usual. What transpired at the end of the training was magical. As you know, if you have been reading my blog, I have been contemplating moving forward to pursuing my MCITP for SQL Server 2008. Kendra made an announcement that Brandon Leach (twitter) was forming an MCITP study group of like-minded professionals to get together online and help each other out. Hey that is exactly what I have been looking for! My #SQLFamily is really an amazing group of people, and I wish I had discovered them years ago while banging my head against the wall upgrading SQL 6.5 to 7 and then on to 2000. At the time, I was an accidental DBA. Those days have long passed, thanks to my MCTS. MCITP: Here I Come!
Maybe your users cannot access your database, or maybe your preferred monitoring software is reporting limited or non-existent disk space on your log drive for a particular SQL Server. Maybe you get a call from a Subject Matter Expert telling you their application is wigging out, or maybe you get a call from a user or even better, from your helpdesk. We have all gotten that phone call or e-mail at one point or another. A transaction log has gone awry and wreaked havoc on your limited drive allocated to the log files. What next?
Do we know the database? We could look at the drive and find the culprit. However, we will need the logical name of the log file in order to shrink it, remember it isn’t always the same as the filename. Run the following in SQL Server Management Studio (SSMS) in order to get the log file name:
SELECT name FROM sys.database_files WHERE type_desc = ‘LOG’
At this point, we can try to shrink the database by issuing the following command:
DBCC SHRINKFILE(‘<DB log file name>’, 0)
If this does not shrink the file, then run the following command to see the log_reuse_wait_desc column (way over to the right):
SELECT * FROM sys.databases WHERE name = ‘<DB Name>’
The following values of the log_reuse_wait_desc are the most common reasons that the shrink operation has failed:
- CHECKPOINT: This means that there is no database checkpoint in the transaction logs. You should manually run the CHECKPOINT command or you could also perform a database backup.
- ACTIVE_TRANSACTION: This indicates that there is an open transaction. You can use DBCC OPENTRAN to find out what is going on (I will write another blog on this soon and link back here).
- LOG_BACKUP: This indicates that the log file has wrapped around to the beginning of the file and the free space is somewhere in the middle of the file. Run another log backup in order to move the free space to the beginning or end of the log file.
After completing one of the three afore-mentioned steps, we can now issue the SHRINKFILE command again….And all will be well with the universe. Enjoy!
Below are some common SQL Server Management Studio keyboard shortcuts that I use on a daily basis to make my SQL life much more productive:
- Ctrl-N…..Open a New Query with the Existing Connection
- Ctrl-Alt-G…..Display Registered Servers
- Ctrl-Alt-T…..Display Template Explorer
- Ctrl-Alt-L…..Display Solution Explorer
- F4…..Display the Properties Window
- F8…..Display Object Explorer
- F7…..Display the Object Explorer Details Window
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
I know it is a few weeks past Christmas, but this is way too much fun to pass up….SQL Server – Spatial fun in SQL Server – creating an XMAS tree.
Since that item was Spatial fun, how about this one: More Drawing Fun with SQL Server… this time in Technicolor | Alastair Aitchison.
Enjoy this #SQLAwesomeness!
For the first Satuday of 2012, I thought I would try a new feature for this blog…Saturday SQL Schoolhouse. In the spirit of SQL Saturday, I will discuss some interesting learning websites or send out some training links.
My first offering….The Microsoft TechNet Virtual Labs for SQL Server 2008, a great resource for learning!
I have a thought that has been resonating in my head all week, well probably for a couple of weeks. It was a comment made at the Tampa Bay SQL Server User Group Holiday gathering last month. To set the scene…it was a moment of frivolity where several of us where debating as to who had the best job. Naturally, everyone denied that it was their job and thus pointed to another member at the table and espoused the reasons that they believed that person to have the best job. When the TBSSUG version of Quentin Tarrantino, yes looks and mannerisms but not the height, turns to me and says the following:
Tarrantino: “Hey what about you, do you have the best job?”
Me: “No, I don’t think so….”
Tarrantino: “Why not, what disqualifies your job?”
Me: “My job is not 100% SQL, therefore it cannot be the best job, in my opinion. I have other Windows Server administrative duties, I am a VMWare administrator (which is a positive and not a negative even though it may not seem like that from the tone of this blog), I have to attend numerous pointless meetings, and I have to deploy numerous dot NET applications, which should disqualify me automatically. I am personally responsible for approximately 200 servers. Don’t get me wrong, but I love my job, except for the pointless meetings, but that is a separate blog topic. I just don’t think it would be the best job, that would be a job where I was a SQL DBA for 100 percent of my duties…”
Tarrantino: “100%, that would be the worst job….who would want to do that? SQL and nothing else, that would be boring…no way…you’re crazy…(turns to the next person) What about you, is your job the best?”
It was a silly conversation served up with a delicious beverage and Christmas cookies, so why does it still bother me? Is it the fact that one of my peers does not share the same passion for SQL Server? Or is it the fact that maybe I am Don Quixote and this is my windmill: chasing a dream of a golden job opportunity in a town called Perfect.
What do you think? Enjoy
I have been contemplating my next move for SQL certification. Last spring, I earned my MCTS for SQL Server 2008 Implementaion and Maintenance passing the 70-432 exam after a couple of months of preparation and a TechSherpas course. The next logical step is the 70-450 which is Designing, Optimizing and Maintaining a Database Administrative Solution using Microsoft SQL Server 2008. The title alone is impressive. I think with this budget year I will have the opportunity to take a bootcamp for this certification.
Should I wait before pursuing this level? Is it recommended to have more experience before taking this exam? What do you think?
So yesterday afternoon I received a management request to provide some statistics on our SQL Servers, probably to justify our positions for the next budget year. No problem, I thought, because I know how many total servers, how many clusters, how many physical boxes and how many are virtual. I also know which ones are production, acceptance, and development and I know how much RAM, how many processors and cores, how many drives and how big they all are. I also know how many licenses we own and which ones are Enterprise versus Standard, which ones are 2008R2 and which ones are 2005.
I also have a script (thanks to Pinal Dave) to tell me pretty much everything I need to know when I run it on each of my servers. However, that was not the information they requested. Wait for it, wait for it.
They wanted to know how many records or rows we maintained on how many tables. In all of my years working with SQL Server, Progress RDBMS, MS Access, and MySQL (in order of awesomeness), I do not think I have ever had anyone ask for that level of information. The quick stat that everyone wants to know is usually how many servers and how many terabytes. In our environment, they also want to know how many dot net applications are we supporting on those database servers.
Much to my dismay, my frantic google session showed many people who had a script that was essentially similar to Pinal’s script (although most were not nearly as fabulous). So I did what every DBA should do, I sent up a distress call to the #SQLHelp Twitter tag! Lo and behold who should come to my rescue, why SirSQL ((blog|twitter), who else would you expect? Although, I was not a damsel in distress so maybe that was a poor analogy, but hey it made me giggle, so why not?
Here is his solution, which is really quite elegant. I am thinking of automating it on one of my servers so that I can have this handy in case they ask for the information again. It is inevitable that they will ask for it again when you give it to them once, unless of course I spent hours writing the script myself. Thanks again, SirSQL!
As I was driving in to the office today, I could not help but think of my wife’s cleaning efforts at home yesterday. She decided the holiday was a good time to remove the clutter from our kitchen cabinets and drawers. That inspired me this morning to do the same and hopefully put a smile on my storage guys’ faces, at least until I add my new clusters in a couple of weeks and requests some more terabyte LUNs.
A fresh new year is a good time to go through your SQL servers and look for ancient backups that maybe got moved to a different location and escaped the cycle of retention, prehistoric MDFs and LDFs from failed migrations or temporary restorations to different servers. I know I had MDFs and LDFs from databases that were migrated to other servers that were left on the previous server as an insurance policy during the migration. After a month, I think it is safe to remove these and clean the clutter.
This can also be a good time to review your retention policies on your back up jobs and maintenance plans to make sure you are in compliance with your department’s requirements. Let’s all clean house and start the year off right! Enjoy!
Having posted yesterday about my Personal Development Plan, I read a blog post from Neil Hambly regarding his 2012 goals. Now, I realize that my goals were somewhat vague and easy to worm out of. Here are the goals that I had mentioned yesterday:
- I want to dedicate time everyday to this blog and giving back to the SQL community that has helped me so much over the years.
- I want to continue to dedicate time to the SQL community in social media.
- I want to attend at least ten events this year consisting of SQL Saturdays, training, code camps, and possible SQL Rally and/or SQL PASS.
Let’s revise those goals to the following:
- I will post daily to this blog in order to give back to the SQL community that has helped me so much over the years.
- I will spend time daily helping people on twitter, SQL blogs, and various SQL-related websites such as SQL Server Central.
- I am setting a goal of 300 followers on Twitter which would double my existing followers for 2011.
- I will attend at least ten of the twelve Tampa Bay SQL Server User Group meetings.
- I will atend three to five other SQL User group meetings, such as the Tampa BI group or Orlando’s MagicPASS.
- I will attend every Tampa VMWare User Group meetings which occur quarterly.
- I will attend other events that interface with SQL Server or VMWare such as the Orlando IT Pro Camp scheduled for January 21, 2012.
- I will attend one Microsoft SQL training course this year, but I cannot say at this time what that course will be as that will depend upon my employer ;-). I would like to attend Course 2778a (Writing Queries Using Microsoft SQL Server 2008 Transact-SQL) and Course 6234 (Implementing and Maintaining Microsoft SQL Server 2008 Analysis Services.
- I will attend every SQL Saturday offered in the Orlando, Tampa, and Jacksonville areas. I would also like to attend some others, if possible, within driving distance. I am already registered for SQL Saturday #110 in Tampa on March 10, 2012.
- I will volunteer to help with at least one SQL Saturday this year.
- I would like to attend SQL Rally in Dallas this year and I would definitely like to attend the SQL PASS Summit, but with one child in college this year and two more entering over the next two years, I may have to pass on this one for a couple of years until they are situated and my bank account returns to normal.
- I will continue to take at least one to two classes a semester online in order to complete my degree in Business Administration. All those Engineering and Computer Science classes I took 20 years ago are not relevant today and I would like to possibly return to consulting in five or ten years and that degree would be more beneficial in that long term goal.
- I will continue SQLAndy’s recommendation to meet at least three people at every SQL event. This is a great recommendation for networking. It is hard sometimes to break out of our introverted ways.
- I will attain my VMWare Certified Professional 4 certificate this year for VMWare.
- I would like to speak somewhere this year, my local SSUG, VMUG, or even a training session at work. I need to get over my fear of public speaking. 2012 is the year to step it up and take it to another level.
I think this is an adequate list on my second day of contemplation, but I will review this over the next week or so to decide if it is exhaustive. Stay tuned and enjoy!