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.

 

SQL Server 2014 BI دورة تمهيدية شاملة في

الزملاء الأعزاء من العالم!
السلام عليكم و رحمه الله و بركاته
أقدم لكم دورة تمهيدية شاملة في SQL Server 2014 BI. وتقدم هذه الدورة التدريبية باللغة العربية، وتهدف إلى المساعدة فى التقديم للمفاهيم الأساسية للمهنيين المتخصصين فى قواعد البيانات عامه وبخاصه SQL Server BI في محاولة متواضعه منى لنشر هذه المعرفة والعلم بين اخوانى الناطقين باللغه العربية.
تهدف هذة الدورة التدريبية الى تلبيه فضول واثاره اهتمام المتخصصين فى مجال SQL Server BI وعلى وجه الخصوص  BI Developers كما ان هذة الدورة مبسطه الشرح خاصة فى الدروس الاولى لكى يسهل على المبتدئين فى مجال SQL Server BI متابعتها واضعا فى اعتبارى انه لا يوجد الا القليل من المواد فى هذا المجال باللغة العربية. فقررت بعون الله وتوفيقه ان ايسر هذا العلم بين ايديكم لعل الله ينفع به احد من اخوانى و اخواتى فى اي مكان فى العالم. اسال الله عز وجل ان تنال هذه الدروس اعجاب الجميع و خاصه المهتمين بمجال SQL Server BI ارجو من جميع الأخوة والأخوات ان يذكرونى فى صالح دعائهم
و لاتترددوا فى مراسلتى و ابداء ارائكم و مقترحاتكم البناءة لتطوير هذا العمل الخيرى باءذن الله تعالى

أخوكم
أيمن الغزالى
3/2015 فى واشنطن- الولايات المتحدة الأمريكية

Link to Videos

Dear Colleagues of the Database World!
I present to you a comprehensive introductory course in SQL Server
2014 BI. This course is presented in the Arabic language, and is
intended to introduce core concepts to Database Professionals that are
trying to acquire knowledge in SQL Server BI. The course is geared
towards those that aspire to become BI Developers, or those just
interesting in learning the basics of SQL Server BI. Since there is very
little material in Arabic, I decided to try to use my skills to bridge
the knowledge gap for my SQL Family that communicates in Arabic. I
hope you enjoy the classes and please feel free to share and leave
constructive feedback.

Thank you and good luck future SQL Server Professionals world wide!

Special thanks to my friends Mohamed Elsharkawy for his help and support with this production.

 

[SQL Snacks Video] Performance Tuning 103 – T-Logs and VLFs

Finally the arrival of Part 3 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. Also, if you missed part two from yesterday you can review it here.

If you are still interested in learning more about Performance tuning with SQL Server, I will be giving an hour long presentation with the PASS DBA Fundamentals Virtual Chapter on January 6, 2015 (11 am Central Time/Noon Eastern Time).  For more information please visit http://dbafundamentals.sqlpass.org/ and join PASS for a great way to learn more about SQL Server.

Code from Demo below:

 

[SQL Snacks Video] Performance Tuning 102 – Files, FileGroups, and TempDB

Finally the arrival of Part 2 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. The first video was about performing a baseline using the PAL tool.I would highly recommend you review that video as well as my SQL Snack on Instant File Initialization. This second video discusses the importance of properly sizing Data files, placement, and how the Proportional Fill-Algorithm works for data insertion. I’m hoping you get some last minute Performance tuning in before 2015 and so I will be posting the third video within the next 24 hours.

Code from Demo below:

 

[SQL Snacks Video] Performance Tuning 101 – Baseline with PAL Tools

Welcome to Part 1 of my SQL Snack Pack on Performance Tuning! The series is dedicated to help beginners understand how to start performance tuning with SQL Server. This first video describes how to setup a baseline for your system using the PAL tools. It is essential to get a baseline before you start performance tuning so that you can determine how effective the efforts done in trying to tune your SQL Server have been.  The PAL tools at first look a little intimidating but they are really very easy to use and extremely helpful for performance analysis. Enjoy and happy baselining!

A special thanks to Edgardo Valdez for showing me the how to use this tool.

Link to download PAL Tools and Prerequisites: https://pal.codeplex.com/

 

[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.

 

[SQL Snacks Video] SQL Server Table Partitioning 102

Welcome back for part 2 of my SQL Snack Pack on Table Partitioning! If you have not watched the first video, I would highly encourage you to do so.

 

[SQL Snacks Video] SQL Server Table Partitioning 101

I hope you’re hungry for another SQL Snack! In fact, this will be one of a series of snacks (dare I call it a SQL Snack pack?). Table partitioning is a fantastic feature that is easy to learn and can significantly improve your OLTP and Data-warehouse environments. It can be a little intimidating because it is tricky to get started with, but once you get the basics down you’ll realize it’s pretty straight forward and a very useful feature to have.  I will be providing the code and outline for each of the SQL Snacks related to table partitioning so that you have a chance to practice on your own.  Happy partitioning!

 

[SQL Snacks Video] The What, Why, and How of Instant File Initialization with SQL Server

Instant File Initialization (IFI) is an interesting topic with regards to how SQL Server works with storage. It is an easy feature to turn on and can improve the performance of your server; specifically with creation and growth of data files including TempDB rebuilds with SQL Server restarts.  There is a slight security risk where a professional data thief could potentially recover bits of data that have not been over written since IFI was turned on, but the chances of that happening are slim.  Plus, if they have physical access to the hard drives on your server, you will have bigger problems to fix.

So without further ado here is the next delicious SQL Snack for Instant File Initialization:

 

[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.

 

[Video] ApexSQL Log – Software Review

Today I will be reviewing the product ApexSQL Log which is a tool designed for Transaction Log discovery and recovery.

The team at ApexSQL were very friendly and offered me lots of support and help in using the product.  I opted to do everything myself just to see how easy it is to learn and use the product.  It took me about 30 minutes to get fully acquainted with it; although I’m not an expert now I know my way around the product very well. It’s always great to have a product with an easy to use interface that does not have a steep learning curve.

Note: this is not a product endorsement

 

SQL Server 2012 دورة تمهيدية شاملة في

الزملاء الأعزاء من العالم!
السلام عليكم و رحمه الله و بركاته
أقدم لكم دورة تمهيدية شاملة في SQL Server 2012. وتقدم هذه الدورة التدريبية باللغة العربية، وتهدف إلى المساعدة فى التقديم للمفاهيم الأساسية للمهنيين المتخصصين فى قواعد البيانات عامه وبخاصه SQL Server في محاولة متواضعه منى لنشر هذه المعرفة والعلم بين اخوانى الناطقين باللغه العربية.
تهدف هذة الدورة التدريبية الى تلبيه فضول واثاره اهتمام المتخصصين فى مجال SQL Server وعلى وجه الخصوص DBAs و كذلك Developers كما ان هذة الدورة مبسطه الشرح خاصة فى الدروس الاولى لكى يسهل على المبتدئين فى مجال SQL Server متابعتها واضعا فى اعتبارى انه لا يوجد الا القليل من المواد فى هذا المجال باللغة العربية. فقررت بعون الله وتوفيقه ان ايسر هذا العلم بين ايديكم لعل الله ينفع به احد من اخوانى و اخواتى فى اي مكان فى العالم. اسال الله عز وجل ان تنال هذه الدروس اعجاب الجميع و خاصه المهتمين بمجال SQL Server ارجو من جميع الأخوة والأخوات ان يذكرونى فى صالح دعائهم
و لاتترددوا فى مراسلتى و ابداء ارائكم و مقترحاتكم البناءة لتطوير هذا العمل الخيرى باءذن الله تعالى

أخوكم
أيمن الغزالى
ديسمبر2013 فى واشنطن- الولايات المتحدة الأمريكية

Link to Videos

Dear Colleagues of the Database World!
I present to you a comprehensive introductory course in SQL Server
2012. This course is presented in the Arabic language, and is
intended to introduce core concepts to Database Professionals that are
trying to acquire knowledge in SQL Server. The course is geared
towards those that aspire to become DBAs, Developers, or those just
interesting in learning the basics of SQL Server. Since there is very
little material in Arabic, I decided to try to use my skills to bridge
the knowledge gap for my SQL Family that communicates in Arabic. I
hope you enjoy the classes and please feel free to share and leave
constructive feedback.

Thank you and good luck future SQL Server Professionals world wide!

Special thanks to my friends Mohamed Elsharkawy, Jihad Abouhatab and my brother Islam El-Ghazali for their help and support with this production.

 

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.

 

SQL Server Management Studio Rainbow Edition

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.”

 

How to Avoid a Database “Heart Attack”

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.

Our latest project is a Lessons Learned pamphlet based on several articles I wrote about a year ago after attending open heart surgery (as an observer, not a patient). Here is the link to the final product, I hope you enjoy and benefit from it.

Here is a link to a summary of the project
http://www.infusivesolutions.com/blog/bid/98928/3-Ways-Business-Observation-Catalyzes-SQL-Server-DBA-Job-Growth

 

Email Response: SQL Server 2012 Planning and Architecture

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.

 

Estimate Table Size based on the Metadata from INFORMATION_SCHEMA.COLUMNS

A problem I face often is not knowing the size of a new table that I’m creating. I put some basic code together that pulls the column information from INFORMATION_SCHEMA.COLUMNS and with some basic math gets the approximate size of the table based on the estimated number of rows.

 

SQL Server Backup and Recovery Fundamentals Presentation

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.

http://www.techrepublic.com/blog/datacenter/configure-compressed-backups-with-sql-server-2008-r2-standard-edition/2788

 

 

 

What if your database server was hacked and all your data was deleted?

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.

 

Open Heart Surgery Lessons Learned for IT Part 3 – Monitoring Health and Disaster Recovery

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.