I had this problem where I needed to gather Transaction Log information on multiple databases and check for valuable statistics on them. Running the command [DBCC Loginfo] brings back a number of rows for each Virtual Log File (VLF) in your Log File. It is really hard to do anything useful with that information on a larger scale. Each row returned gives you an estimate for the number of VLFs per Log File. Sure you could use the internal (and hidden) system stored procedure sp_msforeachdb to get the information for all database but it looks horrible. Here try it out for yourself before you read the rest of the post:
So why not make it better? That’s what I thought to myself, and I have recently been playing with storing DBCC command output to tables for analysis. I’ve put some together some code that allows you to capture the output of DBCC LogInfo into a Temp Table and then get some interesting information about the number of VLFs per database and other valuable information; see the comments for more information. Just by storing some of this data temporarily, I was able to write queries against it and discovered a major inconsistency in the size of my VLFs in a Log File that could potentially cause performance issues.
Feel free to create a permanent table for this data and run it on a regular basis to get an understanding of what your system is doing for troubleshooting. I also commented out the date field since I deemed it unnecessary, but if you’re looking for trending it maybe a good option to have that additional data.
Less than a week left and I’m extremely excited about SQL Saturday in Philly on June 7th, 2014 and the Precon the day before (I signed up for Allan Hirt’s). I lived in Philadelphia for about 10 years during which I went to college, had my first two full-time jobs, and my first to kids were born in that area. This SQL Saturday is going to be a blast from the past for me. The actual event takes place in Malvern PA which is off of 202 in the Northwestern Region of the Philadelphia suburbs. It is part of the “mainline” and close to Valley Forge, King of Prussia and other historic/tourist attractions. I used to work in the Mainline area for Johnson Matthey in Wayne (and part time in Malvern) so I’m very excited about taking this trip back to visit friends and family.
For those of you that don’t know about SQL Saturday it is a fantastic event. Here are some of the reasons I’ve encouraged people to attend SQL Saturday events:
Welcome back for part 3 of my SQL Snack Pack on Table Partitioning! If you have not watched the first two videos, I would highly encourage you to do so.
There are many features/options we sometimes overlook and then wonder later what went wrong. The COPY_ONLY option with backups is one that I felt is important to highlight to SQL Server DBAs. This backup is independent backup that is not part of the regular cycled backups that you perform; hint if you’re not performing regular backups please get up and schedule them NOW! Sorry for yelling 🙂
Using this option when doing backups allows you to take backups that do not interfere with your regularly scheduled backups in order to move them off to a QA, Development, or Staging area where you can test against that database or fix bugs without interrupting your production environment. Many times, I have seen that off-cycle backups are taken which become part of the backup set and then are deleted. This can cause negative consequences when doing restores as I will demonstrate in today’s SQL Snack:
Code is provided below if you would like to test it yourself. Please watch the video in order to understand how to test this yourself:
In December 2013 I presented at a SQL Saturday event in Washington DC. My presentation was about Backup and Recovery Fundamentals which I had done before for the PASS DBA Fundamentals Virtual Chapter. This time around, I decided to add a Tail Log (Active Log) Backup and Recovery demo to enhance my Presentation. The presentation went quite well, and so I’ve decided to put a short video together to demonstrate how to do a Tail Log Backup and Recovery.
***Make sure you change the backup, database, and log file paths to match your configuration***
It’s that time of the year again when SQL Saturday makes its way back to the Washington DC Area. Here is a link to register and to get all the details you need http://www.sqlsaturday.com/233/eventhome.aspx. I hope to meet a lot of new SQL Server professionals this year. Last year, I met a gentleman that now works with me, so as you can see networking pays off 🙂
Now this year’s SQL Saturday is a little different for me because I will be one of the speakers! I am very excited, as well as a little nervous. I think having some nervousness helps me prepare because I mentally tell myself “makes sure you know what you are talking about so you don’t look like an idiot!” My topic will be about the fundamentals of Backup and Recovery with SQL Server. It is targeted towards beginners and here is a brief abstract:
Backup and Recovery are essential parts of and DBA’s job. After all, if you lose your data you lose your job so you need a great backup strategy. In this session we will cover Recovery Models, Backup Types, Backup Strategy, Restores, RTO, RPO, and understanding how your Transaction Log is maintained through backups.
Finally, I wanted to share my thoughts on some of the other interesting sessions that are being held that I will be trying to attend. My disclaimer is that there are so many really great sessions that it is almost impossible to pick sometimes.
First let’s get things straight, this post is not an encouragement for anyone to spend more time with his/her Database or with Database Snapshots for that matter. If you’re married to your Database, that’s a different story all together and I don’t think I could help you. As database professionals we are always looking to improve the quality of our code and data. This post is intended to show an easy way to use Database Snapshots in order to test new code or change data and revert changes back quickly and easily.
Ever wanted to know the reason behind the Database color scheme in SQL Server Management Studio?
I have always wondered why all the Databases in management studio were Yellow (Online), Grey (Read-Only), Lighter Yellow with text next to it (something like Restoring), or Yellow with a Red Icon (Offline). Have you ever wondered why there aren’t any more useful colors like Red for when a Transaction Log is Full or Purple when your Statistics are Outdated. I have a theory on this that I have entitled “The Theory that you have to actually work to call yourself a DBA.”
For the past couple of months I have been working with Ben Weiss a Digital Marketing Strategist at Infusive Solutions (http://www.infusivesolutions.com/). Our partnership was based on a mutual understanding that there is much needed career growth and guidance among IT Professionals. Since my background is primarily with Database Management Systems I have been helping him with writing articles, guides, and even interview questions for top professionals in the field, all for the purpose of furthering the careers of IT professionals.
I received an email from a connection on LinkedIn seeking some advice for architecture on a new reporting system. Although I don’t know the gentleman personally, he is a SQL Server professional and I felt obliged to help out a fellow colleague in the field. The email is included below and my suggested solutions following it.
Everyone’s favorite four letter word is back, FREE! Yes, it is time I gave back to the community since I’ve been mooching off them them for so long. I’ve put together a short Installation Guide for those interested in getting their feet wet with SQL Server. I’m trying to encourage those that have no experience to get a little curious and try to install SQL Server and play with it. Maybe afterward you can even try to set up your own Clustered SQL Server Virtual Lab with Hyper-V. Just trying to rub off some of the enthusiasm and passion, did it work?
Anyway you can view the guide Here on Google Docs or Download it Here as PDF. It is just a first draft, so please feel free to add your suggestions or point out mistakes and I will try to fix it ASAP.
You can also read it right off of this page… check it out by clicking “Read the rest of this entry”
After having a great opportunity to attend a Beta course for Hyper-V with Windows 2012 at Learning Tree, I decided to finally do what I have been planning to do for a while; build a fully working SQL Server Clustered Virtual Lab.
SQL Server Backup and Recovery Fundamentals – February 5th, 2013
A copy of the recording is available here. Code and slides are under the Presentations section of this website.
Correction:Compression on Backups is available on the Standard Edition of SQL 2008R2 and SQL 2012 but not SQL 2008. Thanks to Mark Harrison for pointing that out to me.
Recently in the news an article circulated about a coordinated attack by a team of hackers known as Anonymous. Information about this incident is all over the internet here is one article with details RT News.
Part of that attack included deleting a database belonging to the Israeli Foreign Ministry. The details are not clear, but I wanted to write about what may have happened and how you can prevent it or recover from it in your own environment.
An interesting post by Gethyn Ellis about an issue he encountered using Availability Groups. Follow the link above to read more.
I run into an interesting issue with an Availability Group database recently. I was running a data load against a database in an Always On Availability group database in SQL Server 2012. The load was SSIS package migrating data from staging database when the package fell over with the following error
“The transaction log for database DBName is full due to ‘AVAILABILITY_REPLICA’”
SQL Server 2012 introduces Database Recovery Advisor that provides significant user experience improvements to the ways DBAs can restore databases with SQL Server Management Studio. As we know, SQL Server provides a variety of backup types, so creating the proper recovery sequence for each point in time can be tricky at times. This is where Database Recovery Advisor is useful, because it makes the database restoration process more agile by helping the customers to create more predictable and optimal recovery sequence.
The Database Recovery Advisor provides a visual timeline from the backup history of the database and then presents the available points in time at which the user can restore the database, algorithms to streamline the identification of the appropriate sets of backup media to restore the database back to a specific point in time. By default, the Database Recovery Advisor tries to restore the database from the last backup…
The whole “7 Habits” title is so cliché so I decided to make it eight! Plus that will probably relieve me from any copyright violations as well. So anyway, these are the eight habits/skills of Highly effective Database Administrators in my humble opinion. Although I come from the Microsoft SQL Server world, I believe most of these traits or skills can be adapted cross-platform.
Feel free to add your feedback in the comments section, this is after all just my own opinion.
I’ve been wanting to write a series of articles on deploying a SQL Server 2012 on a multi-subnet cluster for quite some time now. This was driven by the fact that my series of articles on SQL Server 2008 Failover Clustering had been in the Top 10 Tips for more than 2 years since being published three years ago. I guess more and more systems administrators and SQL Server DBAs are being tasked with deploying failover cluster instances. Ever since I had my hands on the beta version of Denali (codename for SQL Server 2012) last year, I’ve been testing some configurations for the multi-subnet clustering feature. I think I’ve built like 3 test environments prior to Denali going RTM just so I can wrap my head around the concepts (plus the fact that Windows Clustering Experts like Microsoft MVP Allan Hirt (blog | Twitter) have been gracious…
One of the biggest issues we have in health is poor monitoring. This is also the case in health of many of our systems. As a DBA, I’m always concerned about the health of my database servers; mainly because I would like to keep my job and not get the 3am call that something has gone terribly wrong. However, with that said, no matter how careful we are there is always the probability of something bad going wrong.
This is part three of my lessons learned and will have more of a focus on Database monitoring and Disaster recovery.
![[SQL Snacks Video] SQL Server Table Partitioning 103](https://thesqlpro.files.wordpress.com/2014/01/sql-snacks.jpg?w=150 "[SQL Snacks Video] SQL Server Table Partitioning 103")
The SQL Pro 