Insufficient System Memory - Failed Allocate Pages

Unable to Start SQL Server

In my own local SQL Server I ran across a problem starting the SQL instance. I went to SQL Server configuration Manager and manually started the SQL Server instance. The UI showed the instance had started. I opened SSMS and tried to connect. And I waited, waited and waited some more until it didn’t connect and threw an error.

How To Locate the SQL Server Error Log

I knew I could look at the SQL Server Error Log to get more information on what had gone wrong.  I browsed to the directory where the log file is. For any system that is found by going to the drive where the binaries have been installed and go to \Program Files\Microsoft SQL Server\SQLServerVersionNumber.InstanceName\MSSQL\Log. In that location you will look for files like Errorlog, errorlog.1 through errorlog.6 The file with no number at the end is the most recent Error Log. Double click it and open in your favorite viewer. As I read through the file, I came across the below.

Continue reading

How Do I Measure My DBA Skills Part 9

Production and Development DBA Skills and Things We Didn’t Touch On

We’ve covered a lot of ground for Production and Development DBAs in terms of the skills and applicable career topics. We’ve mentioned necessary skills with T-SQL, backup/restore, automation, PowerShell, troubleshooting, SSIS, SSRS, patching, Query Store, HA/DR, leadership and emotional intelligence. That’s quite a list! However, looking back at the series, there are still a number of things that weren’t mentioned.

Continue reading

Introduction To PowerShell For The DBA Part 3

Hello dear reader! This is the third post in a series to help you get started automating things with PowerShell. You will be looking at how to start and stop SQL Server using PowerShell as well as setting the Max Server Memory setting, measuring free disk space and more.

This series is just an introduction to PowerShell and basic functionality you might be interested in as a SQL Server Database Administrator or as a System Administrator responsible for SQL Server. Because it is introductory I don’t have examples of long scripts that do a series of complex things as part of these posts. What the series does have are one or two line scripts that are still powerful and help you explore what is available to you in PowerShell.

Continue reading

How Do I Measure My DBA Skills Part 8

Senior Development DBA

You’ve now likely reached the last stop in your career as a Development DBA. You have arrived at the Senior level. What skills do you have or should you be working on as a Senior Development DBA? I’m glad you asked. Let’s find out.

Senior Development  DBA – 7+ years of experience

1. 1. Most competencies from the previous levels.
   2. Advanced analysis of SQL Server Performance issues using advanced techniques to increase performance and/or stability. This could involve server level monitoring and analysis to identify issues or concerns.
   3. Makes decisions regarding SQL Server architecture, development strategies and overarching performance considerations, often in tandem with Development team or even the Infrastructure team.
   4. Regularly participates in code reviews of mid-level to Senior Devs and other DBAs, offering best practices and guidance and may assist in implementation.
   5. Fosters and protects healthy development environment, including Source control, controlled or automated migrations, and advanced development/QA Data curation via data masking technologies and potentially other methods.
   6. Makes significant contributions to the design of migrations and upgrades for SQL Server and may provide assistance to other, more junior members of the team, when it comes to implementation.
   7. Leads knowledge sharing in four or more areas.
   8. May contain competencies from Production DBA levels up to level 2 or 3.
   9. Regularly demonstrates good self-awareness, self-management, social awareness and relationship management skills (emotional intelligence

Automating SQL Server Processes

Continue reading

How Do I Measure My DBA Skills Part 7

Lead Development DBA

Over the past few weeks I have been writing about DBA skills for various career levels, and for a couple of different DBA types - the Production DBA and the Development DBA. Today, we’re examining the Lead Development DBA. You’ll discover what this person needs to know.

DEV DBA III/Lead Development DBA – 5 to 7 Years

1. All competencies from previous level.
2. Manages most administrative aspects of non-prod environments without assistance from other DBAs.
3. Participate in T-SQL development and database design of advanced complexity.
4. Investigates potentially complex data issues within SQL Server.
5. May have several years of progressive experience with SSIS, SSRS or SSAS.
6. Administers source control systems for the environment.
7. Regularly automates tasks. This should include the use of T-SQL, SQL Server Agent, and PowerShell.  May include the use of other languages like Python or C#.
8. Creates and manage design documentation related to development work.
9. Regularly explores, learns and implements new SQL Server development features. Examples would include new T-SQL enhancements and new feature sets like Temporal Tables for SQL Server 2016 and Data Virtualization for SQL Serer 2019..
10. Advanced understanding of execution plans, indexes, and query tuning.
11. Investigate complex data integrity/repair issues within SQL Server.
12. Leads in knowledge sharing in two or more areas from levels I, II, or III.
13. Assists with SQL Server migrations and upgrades in DEV/QA or production.
14. Mentors Junior DBA’s and Developers.
15. Participates in code reviews.
16. May contain competencies from Production DBA levels up to level 1 or 2.
17. Demonstrates emotional intelligence and may take an interest in leadership roles.

So let’s dive into a few of these points in the skill list.

Continue reading

Introduction To PowerShell For The DBA Part 2

In Part 1 of this series we began to look at how you can leverage PowerShell as a DBA and there was a brief discussion about automation. Today you will experiment with the DBATools command Copy-DbaLogin and learn about Out-Gridview and the -whatif switch.

Let’s suppose you have one, or more logins you need to get from one SQL instance to another.  Perhaps you are setting up a new test environment for SQL Server 2019 and you need  to move this login to that environment. Maybe this is one node of a SQL Server AlawaysOn Availability Group and you need to get this login, with its SID, on each node of the AG. Below you see I have a screenshot of a login called mysqltestlogin. It’s on a SQL Server 2017 instance.

Continue reading

Introduction To PowerShell for the DBA Part 1

Automating Tasks With PowerShell

What we’re really talking about in this series is automation, specifically by using PowerShell. Now, in simple terms, automation is about removing the human, manual element in doing tasks. Why should you care about that? Automation will provide the following benefits:

• Automation saves you time
• Automation provides consistency
• Automation results in reduced and easier troubleshooting
• Automation can use your current inventory of T-SQL Scripts
• Automation is easier to get started with than you think
• Automation skills are a mostly required skill for career advancement

Continue reading

How Do I Measure My DBA Skills Part 6

Development DBA II

With the last post, we began talking about the Development DBA and the skills needed at the beginning of this career path. Now it’s time for you to ponder what the next level looks like.

Development DBA II – 2 to 4 Years of Experience

1. All competencies from level 1
2. Develop basic to intermediately complex stored procedures, triggers, views and other database objects.
3. Will have some experience with SSRS, SSIS, and/or SSAS development, deployment and possibly administration.
4. May administer source control systems for the environment.
5. Promotes SQL changes from Dev up through all environments, including production.
6. Contributes to automation, particularly using native SQL Server methods, but could also leverage PowerShell or other languages for automating tasks (Python, C#, etc.)
7. Designs pre-prod testing of SQL code prior to production upgrades or migrations. This includes performance and regression testing.
8. Basic to mid-level understanding of execution plans, indexes, SQL Server statistics, and query tuning.
9. Investigate basic to moderately complex data integrity/repair issues within SQL Server
10. May begin leading in knowledge sharing in some capacity in one or more areas from level I or II.
11. May take an active interest in leadership and in development of leadership skills, including emotional intelligence.

In years 2 through 4 the Dev DBA will work with more complex T-SQL such as recursive CTE’s, and error handling with TRY CATCH or the THROW syntax will likely be introduced at this stage. You may begin writing database views during this part of your career and leveraging them to make query writing a bit simpler for yourself and others. You are likely to be exposed to T-SQL Triggers and you’ll need to understand them and even maybe write a few triggers of your own.

Continue reading

How Do I Measure My DBA Skills Part 5

Development DBA

We’ve been on a journey recently in this series as we discuss measuring your DBA skills. Previous posts have been intriguing to you. You like where this is going and yet, your skills are different from what we’ve been discussing in this initial part of the series.

Maybe you are a Developer who works with databases and you find SQL Server very interesting. You build some tables and write some queries as part of your work and you really like doing it. You like trying to figure out how to make your initial draft query for your project run faster. You have conversations about this with other Developers or maybe even a DBA or two at your company. What you hear resonates with you and deepens the interest in SQL Server. You wonder, “Is there a place for me in the SQL Server world? What would I do if I did a career pivot to work with SQL Server almost exclusively?” Enter the Development Database Administrator.

Continue reading

How Do I Measure My DBA Skills Part 4

Dear reader, we’ve been on a bit of a journey recently so that you can discover what skills are needed at various levels or phases of your career.  The first three phases have been covered in previous blog posts and you have now come to the Senior level for the Production DBA. So, how do you measure the skills a Senior Production DBA should have?

Senior Production DBA – 7+ years of experience

1. Most competencies from the previous levels.
2. Advanced analysis of SQL Server issues using native tools such as Extended Events, Perfmon, Query Store and 3rd party monitoring tools.
3. Makes decisions regarding SQL Server architecture, hardware, virtualization and storage, often in tandem with an infrastructure team.
4. Designs high availability and DR solutions and may provide guidance to other team members during implementation.
5. Plans SQL Server migrations.
6. Leads knowledge sharing in four or more areas.
7. May possess some competencies from DEV DBA levels up to level 2 or 3.
8. Regularly demonstrates good self-awareness, self-management, social awareness and relationship management skills (emotional intelligence)

You will notice that in previous lists of skills, I have listed skill 1 as “All competencies from the previous level.” However, I have not done that in this case, why? There are at least two reasons that I will detail.

Continue reading