One of my client’s has recently downgraded his SQL Servers from Enterprise Edition to Standard Edition to cut costs. The 2 SQL Servers are configured in AlwaysOn Availability Groups for HA and host 26 user databases which on the Enterprise Edition were part of the same single AlwaysOn Group. Now moving to SQL Server Standard …
In this post I would like to share a CREATE INDEX syntax I was not aware of which I came across a few years ago while moving a multi tera VLDB to a new filegroup. Recently I had to use this syntax again and it took me a few seconds to pull it out of …
As a DBA working daily with SQL Servers I use Powershell from time to time to access my sql servers mostly for automation tasks but there are plenty of other reasons as well. In order to shorten my Powershell code and simplify my work I have created the bellow Powershell function years a go and …
Use .net SqlClient with Powershell to access data Read More »
Here are a few scripts that I have recently used to find Azure resources that are no longer needed and can be removed to cut down payments. First you will need to connect to your Azure account using Powershell.
As part of the services we provide our clients around Microsoft SQL Server we also provide a service that uploads database backup files to Amazon S3 or Azure Blob Storage (applies for On Premise servers). This option is really great and gives you lots of confidence letting you sleep better at nights. it had already …
Download files from Amazon s3 to local folder using Powershell Read More »
In this post I will share how to use the SqlCredential object with Powershell when using SQL Authentication in order to pass the password in a secured way connecting to an instance of SQL Server. First step is a one time step creating a secured encrypted password saved in a text file that we latter …
Use SqlCredential with Powershell to connect to an instance of SQL Server Read More »
When installing a Windows Service remotely as a “Local Service” there are no credentials involved and this is a pretty straight forward task. But if you need to have the service installed under the account of a local user or a domain user then this is somewhat tricky. The security credentials you create using the …
Use PowerShell to Install a Windows Service remotely Read More »
Recently when I needed to find the Transaction Log file that can be applied to a log shipping secondary database I decided to take a few moments and automate the process that I usually do in the old fashion manual way and that includes: 1. generating the restore log commands using a query over backupset …
Find the specific sql server t-log to be restored for an unrecovered database Read More »
Here is a technique to improve performance when you come across the task of deleting thousands of files located under many folders This is a common way to get all the files meeting a criteria of “older than a specific date” from a specific folder including any sub folder if exists while filtering on …
Improve performance of files purge with PowerShell Read More »
When maintaining client’s systems, I tend to pay extra attention about SQL Servers backups, being a ware that the data in most cases is the organization’s most important asset. With that in mind I always look for the extra layer of protection I can provide so that data is protected against any potential threat and …
Compressing SQL Server backups using 7Zip4Powershell Read More »
Parsing the IIS logs to get statistics can be very useful as these logs hold many properties for each request that has been served by you application(s). Some of these properties such as the duration, return status,URL and others can be used as key metrics to visualize the overall activity of a web application. The …
As a professional maintaining and managing databases for clients I am very well aware of the fact that Data Integrity in relational databases is absolutely crucial. As DBAs the data we maintain and support, is in many cases the organization’s most important asset. Data Corruption is a topic for it self that I will not …
Perform DBCC CHECKDB Data Integrity checks on Log Shipping Secondary server Read More »
If you have read my previous post on how to capture the Blocked Process Report event then checkout this script that queries the data captured in the event
For the TraceBlockedProcessReport stored procedure presented in this post see my Traces project at GitHub. This is from the Microsoft’s official documentation “The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are …
Capture SQL Server’s Blocked Process Report event Read More »
Years a go when working on a .net project I came across log4net and thought I will give it a shot. Using log4net in my project I immediately saw the advantage of using this logger and adopted it ever since replacing my home made hand crafted logger. Checkout the features page of log4net. As time …
Before moving forward be sure to have read this previous post that is the basis for the current post. This is the link to me GitHub diskspd project containing the relevant files discussed in these 2 blog posts mini serious. Working quiet often with diskspd it was only natural for me to automate the tiring …
If your server is stressed out on CPU I am pretty sure you would benefit using these queries to correctly identify and prioritize your course of action. The first query is the simplest as it only retrieves data from sys.sysprocesses so when a server is really stressed out on CPU and queries take long to …
This is the link to my GitHub diskspd project diskspd was designed as a storage testing tool and that basically mean test your SAN or NAS but not only SAN/NAS based disks, you can test any other disk, be it a DAS, a local drive on your server or your new laptop’s SSD (I always …
Using diskspd to test disk performance with SQL Server Read More »
This is a link to previous related posts that you may find interesting https://sqlserverutilities.com/maximize-sql-trace/ https://sqlserverutilities.com/use-sql-trace-to-capture-database-exceptions/ If we want to capture all the commands being sent to the database engine we should capture just these 2 events: SQL:BatchCompleted and RPC:Completed. By doing so we guarantee to be capturing all executions sent to the database engine which …
In this post I will discuss and present some practical use cases for SQL Trace also known as “Server Side Trace”. Before we move forward I would like to first make the distinction between SQL trace and SQL Server Profiler. SQL Trace is a database engine technology while SQL Server Profiler is a .NET application …
For the previous post discussing SQL Trace see this post For the TraceExceptions stored procedure presented in this post see my Traces project at Github. Capturing all the exceptions that occur in the database engine has been proved to be very useful in my experience. Actually, when thinking about it, I realize most DBAs do …
SQL Server allows marking any stored procedure as a “startup” procedure which means it will get executed upon a service restart. A “startup” procedure has to reside in the master database and cannot accept input parameters – cool, this is something we can live with. I find this option to be very simple and practical …
Working with partitions at different environments has led me to think that a generic unified code to manage partitions can be very useful and practical. Think about it, if you look at a code that manages a partition sliding window implemented by 10 different DBAs you are going to see 10 different variations of the …
Among the many tasks that are under the DBA’s responsibilities is keeping the database performing well and in good health. And when mentioning performance the topic of indexes is Inevitable. The task of index maintenance typically includes the process of handling indexes fragmentation accomplished by the REBUILD and REORGANIZE commands and wile this is a …
SQL Server exposes missing indexes metadata via the following 3 DMVs (Dynamic Management Views) sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups and sys.dm_db_missing_index_group_stats which combined in a simple query return valuable information of indexes that were detected as missing by the Optimizer and logged in those DMVs. The information in these DMVs is updated by the execution of every query …
SQLScripter is a powerful command line utility designed to automate the process of schema generation. SQLScripter simply preserves your schema on the file system at many points in time allowing you to easily access any object that you may need, be it a Stored Procedure, a Job, a Login, Partition Function or any other object …
In this post I talk about SQL Server Agent jobs when implementing AlwaysOn Availability Groups. AlwaysOn Availability Groups synchronizes the databases that are part of an Availability Group but any other object that is not part of these databases do not get synchronized (i.e. logins, linked servers, operators etc.) and this includes SQL Server Agent jobs. For …
Implement SQL Server Agent Jobs with AlwaysOn Availability Groups Read More »
As far as I recall the trigger to code sp_helpindex2 was that with the introduction of INCLUDE columns to indexes that was added in SQL Server 2005 the old way of viewing indexes using sp_helpindex was useless since Microsoft has not modified their procedure to support the recently added INCLUDE columns. I had therefore found myself, when tuning queries, …
This is my very first blog post at my own site after over a year of none blogging. What happened is that the site I use to blog at http://blogs.microsoft.co.il/yaniv_etrogi has gone down…. yes, I know…. with tones of my blog posts, I had so many posts just around the Replication features, Partitioning and lots …
Use Powershell to Multi Thread SQL Server Index Maintenance tasks Read More »
