RSS

Category Archives: Disaster Recovery

Get valuable Virtual Log File (VLF) information for all your databases log files

Get valuable Virtual Log File (VLF) information for all your databases log files

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:


exec sp_MSforeachdb 'Use [?] select db_name(); DBCC LogInfo'

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.

Read the rest of this entry »

 

Tags: , , ,

SQL Saturday 294 Philadelphia, Nostalgic!

SQL Saturday 294 Philadelphia, Nostalgic!

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:

Read the rest of this entry »

 

Tags: , , , , ,

[SQL Snacks Video] SQL Server Table Partitioning 103

[SQL Snacks Video] SQL Server Table Partitioning 103

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.

Read the rest of this entry »

 

Tags: , , , , , , , , ,

[SQL Snacks Video] Importance of COPY_ONLY Backups


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:

Read the rest of this entry »

 

Tags: , , , , , ,

[SQL Snacks Video] Tail Log Backup and Recovery Demo

[SQL Snacks Video] Tail Log Backup and Recovery Demo

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***

Code for demo below.
Read the rest of this entry »

 

Tags: , , , , , , ,

I’m speaking at SQL Server Saturday #233 – Washington DC!

I’m speaking at SQL Server Saturday #233 – Washington DC!

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.

Read the rest of this entry »

 

Tags: , , , , , , , ,

Spend some quality time with your Database using Database Snapshots for testing

Spend some quality time with your Database using Database Snapshots for testing

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.

Read the rest of this entry »

 

Tags: , , , , , , , , ,

 
%d bloggers like this: