RSS

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 2016 Row-Level Security Demo: Users with various clearance levels

SQL 2016 Row-Level Security Demo: Users with various clearance levels

SQL 2016 is right around the corner and one of the new security enhancements promised is Row-Level security for tables.  It’s a great new feature and pretty easy to implement.  I have created a simple demo that gives various users access to data based on specific clearance level to the data.  Feel free to modify the code and play around with it how to see fit.  There are many different ways to setup Row-Level security and this is just one scenario.  One of the things you’ll notice if you go through the scripts below is that the dbo user does not have access to the data after the the security policy is applied.  This is key for many environments where customers do not want administrators to have access to sensitive data.  Of course anyone with good coding skills and the proper permissions could circumvent that, but that’s why we put auditing measures in place :)

I’ve broken up the code into three sections.  The first is for setting up the database and permissions.  The second section creates the tables in the database and puts test data in them.  The third section is for the creation of the function and security policy which enables Row-Level Security. After creating the function and security policy, go back to the second section and re-run the select statements to see the security policy in action.  This demo was created on SQL 2016 CTP 2.2.  If you are interested in learning more about Row-Level Security and to see some other demos please refer to this webinar from PASS.

Read the rest of this entry »

 

Tags: , , ,

Another Career Podcast – Working at Microsoft

Another Career Podcast – Working at Microsoft

I have to admit, I had a lot of fun at SQL Saturday in Philly this last June; I mean two podcasts recorded at one event!! It’s also convenient to do a Podcast with other professionals because they end up doing all the work :P

Before I post the information, I wanted to take a short break here and explain my “absence” from blogging recently. Currently with a new job, more of my kids going starting schooling, and moving, things have been busy. In fact the computer I use to record my video training material is in a box while we’re in between moves. I love to create material where I am satisfied with the quality, and so I’ve been taking a step back to realign myself and I hope to get back to blogging and creating video training soon. I hope that what I’ve provided for the community has been valuable so far and I hope to keep up the trend to help people better their own careers.

Now to the good stuff! This was another fun podcast to do with my good friend Carlos Chacon. I had the pleasure of doing the interview with my colleague and good friend Tim McAliley. Hopefully you enjoy listening as much as we enjoyed preparing this for you!

http://sqldatapartners.com/podcast/  (Episode 01)
Direct Download

 
2 Comments

Posted by on August 7, 2015 in Other

 

Tags:

Podcast about my career and the SQL Server Community with Chris Bell

Podcast about my career and the SQL Server Community with Chris Bell

During my time at SQL Saturday in Philadelphia (Early June), Chris Bell invited me to be a guest on his Podcast.  Here is the result of that fun interaction :)

https://wateroxconsulting.com/archives/podcast/010-ayman-el-ghazali/

I just want to reiterate my thanks to everyone who ever helped me on my career path from my colleagues at work to my colleagues in the field. HUGE THANK YOU!

I hope you enjoy it!

 

Tags:

Working at Microsoft – A Year in Reflection

Working at Microsoft – A Year in Reflection

Last year after starting my new job with Microsoft as a Senior Consultant I wrote an article about my interviewing experience with many companies during my tenure at my last employer (Click here for article).  I realized it is now time to talk about my journey to Microsoft.

It has been a very busy year for me both on the personal and career front.  Having four growing boys is not an easy task to manager; I end up breaking fights regularly over who squirted who first with a water pistol.  Never-the-less it is exciting to be a father!  My year has been an interesting one of challenges and growth career wise. I spent a good number of years and several interviews in order to finally land a job at Microsoft. One of my interviews was with a SQL MCM which went decently well. Although I did not have all the answers for him, he appreciated my enthusiasm and method of trying to solve problems with the knowledge I have.

Read the rest of this entry »

 
1 Comment

Posted by on June 24, 2015 in Other

 

Tags:

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

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.

 
 

Tags: , , , , , , , , , , , ,

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

Read the rest of this entry »

 

Tags: , , , , , ,

 
Follow

Get every new post delivered to your Inbox.

Join 1,294 other followers

%d bloggers like this: