sqlsplat

[sqlsplat]

New for SQL Server 2022: Use GENERATE_SERIES to generate a series of numbers between two values

Microsoft SQL Server 2022 introduces an exciting new feature: the GENERATE_SERIES function!

There are several different ways to generate a table or series of numbers between two given values. Say that you wanted to populate a table with an integer column and the values should be sequential integers between 1,000 and 1,000,000. Below I cover three different methods of accomplishing this task. The last method covers using the GENERATE_SERIES function that is new to SQL Server…

[sqlsplat]

How to Use Secure Strings in PowerShell Scripts

In PowerShell, a secure string is a way to store sensitive information, like passwords, in a more secure manner. Secure strings encrypt the data in memory so it’s not stored in plain text. On occasion you may have the need to write a PowerShell script that will perform an action that requires it to programmatically login to another application by passing a username and password. When you do this…

[sqlsplat]

Understanding the Risks of SQL Server NOLOCK

Admittedly, I use NOLOCK all the time in my queries. But in my defense, most of the queries that I write ad-hoc are returning information that I’m not that concerned about. Using the NOLOCK table hint in SQL Server can have significant implications, both positive and negative, depending on the use case. While it is commonly used to improve performance by avoiding locks on a table, it has several…

[sqlsplat]

Backup SQL Server Agent Jobs: PowerShell Automation Tips for SQL Server

SQL Server Agent jobs are an essential component of managing and automating various database tasks. Whether you’re scheduling backups, running maintenance plans, or executing custom scripts, these jobs are crucial for keeping your SQL Server environment running smoothly. But what happens when you need to back up and recover these jobs? Automating the process of scripting out SQL Server Agent jobs…

[sqlsplat]

Monitoring SQL Server Drive Usage: Three Queries to Get the Job Done (mostly)

"New blog post! 📊 Learn three SQL Server queries to monitor drive usage effectively. From detailed stats with sys.dm_os_volume_stats to quick checks with xp_fixeddrives, find the best fit for your needs.

Understanding the drive space usage on your SQL Server is essential for maintaining optimal performance and preventing storage-related issues. SQL Server offers several ways to monitor drive usage directly from a query. In this post, we’ll explore three different approaches, each with its unique use case and level of detail. 1. Querying Drive Space Using sys.dm_os_volume_stats The…

[sqlsplat]

Query to get SQL Server Agent job schedules

Learn how to retrieve SQL Server Agent job schedule details with a powerful query. Perfect for auditing, troubleshooting, or documenting your SQL environment. Check it out and simplify your DBA tasks!

Retrieving SQL Agent Job Schedule Details in SQL Server If you’re working with SQL Server Agent jobs, it’s often useful to have a quick way to retrieve schedule details for all jobs in your system. Whether you’re auditing schedules, troubleshooting overlapping jobs, or simply documenting your environment, having this query on hand can save you a lot of time. Below is a SQL query that returns…

[sqlsplat]

SQL Server: Best Practices for deleting and updating data in SQL Server

SQL Server: Best Practices for deleting and updating data in SQL Server

Make Backups: Backing up data is not only best practice, but should be a habit. You should always backup your data before performing one-off data updates. This will allow you to do two things. First, you will be able to perform validation by comparing old and new data and draw a conclusion if everything went as planned. Second, you will be able to easily bring the data back to its original state…

[sqlsplat]

Query to get the SQL Server agent job schedules

Query to get the SQL Server agent job schedules

Ever want to have a query to get the SQL Server Agent job schedules? Well here you go: select a.[name] as [job_name], suser_sname(a.[owner_sid]) as [owner_name] , c.[name] as [schedule_name] , msdb.dbo.agent_datetime(b.next_run_date, b.next_run_time) as [next_run_dttm] , msdb.dbo.agent_datetime(c.active_start_date, c.active_start_time) as [active_start_dttm] ,…

[sqlsplat]

SQL Server - Four different T-SQL scripts to get your SQL Server version

SQL Server – Four different T-SQL scripts to get your SQL Server version

Hello everyone.  I just had someone ask me for a script that will return the version of SQL Server.  Off the top of my head I am aware of 4 different ways.  I describe these four different ways of using a script to get SQL Server version below.

If you have another method please feel free to share in the comments.

  Method #1: serverproperty()

serverproperty() is a built in system function that…

[sqlsplat]

SQL Server - a server trigger to send an email when security changes occur

SQL Server – a server trigger to send an email when security changes occur

So I’ve been tasked with a number of security related tasks lately.  Here is another script that I just finished and, once again, I figured that I would share.

Recently someone asked me if they could get an email alert from SQL Server with the details of the action every time a security related change happened on the server.  He wanted the email to trigger on things like a login being created or…

[sqlsplat]

SQL Server - Script out server permissions

SQL Server – Script out server permissions

Last week I posted a script that would script out all of the database permissions in a particular database in SQL Server.

After going through that exercise I couldn’t resist tackling the server level permissions too.

Here is a script that I created that will script out the server permissions from SQL Server.

If anyone has an approved script or a more streamlined approach I would love to hear…

[sqlsplat]

SQL Server - Script out database permissions

SQL Server – Script out database permissions

So I recently had a need to script out all of the database permissions for a particular database in SQL Server.  So I just threw together a script that will do this for me.

I thought I would share.  Here you go:

  declare @user varchar(128); set @user = ''; -- leave blank to get permissions for all users; select case a.[state] when 'D' then 'DENY' when 'R' then 'REVOKE' when 'G' then 'GRANT' when…

[sqlsplat]

SQL Server - Query to get DDL changes from default trace

SQL Server – Query to get DDL changes from default trace

Occasionally I need to read the default trace to get recent DDL changes from the server. Here is a handy query that I wrote to read the default trace look for recent DDL changes.

Here is the query that I use:

SELECT a.StartTime as [EventTime] , a.EndTime , a.ServerName , b.name as [EventClass_Descr] , d.name as [Category_Descr] , c.subclass_name as [EventSubClass_Descr] , case…

[sqlsplat]

SQL Server - Find the size of database file - Find the size of log file

SQL Server – Find the size of database file – Find the size of log file

As a DBA you may sometimes have the need to gather some stats on the sizes of files in your databases.  Once again Microsoft has been kind enough to provide us with some handy tools get get at this information without having to use the clunky GUI.

If you are just simply looking for the size of the files and are not concerned about finding the used space and free space you can simply use the sys.m…

[sqlsplat]

Query to get drive free space from SQL Server

Query to get drive free space from SQL Server

Question:

How can I query SQL Server to get the drive space free, drive space used and total drive size.

Answer:

Beginning with SQL Server 2008R2 Microsoft introduced a new system DMF (dynamic management function) named sys.dm_os_volume_stats.  This DMF returns information about the operating system volume (directory) on which the specified databases and files are stored in SQL Server. You can…