How to shrink tempdb database in SQL Server

This article discusses different methods that you can use to shrink the tempdb database in Microsoft SQL Server. Before you shrink the tempdb database using the methods described in this article, note the following: The tempdb size is reset to the final configured size (i.e. to the default size or the final size that was […]

Space allocated to the databases by disk letters

Found this useful for database migrations. This helps to identify how much space will need to be allocated for a new server and how a SQL Sever instance is using discs currently. Note. This won't show correct information on Linux instances and if you use mounting points in Windows.

A varchar(MAX) STRING_SPLIT function for SQL 2012 and above

An alternative for Microsoft's STRING_SPLIT function that will work on SQL Server 2012 and higher.

Execute on DMVs without user having View Server State

Wanted to share this script to the community just in case anyone out there may be search for a way to allow a hosted customer the ability to query certain DMVs for information without granting them View Server State. There are some other options out there, but this worked better for me after going through […]

Searching for orphaned DB files on the SQL server

See how to find those files that are not in use, but are taking up space on your instance.

Fiscal/Retail 4-5-4 Calendar Function

This function returns a 3 year calendar based on a 4-5-4/5-4-4/4-4-5 calendar, also known as a 52/53 week calendar.  The basis of this calendar function was derived from the NRF retail calendar published at https://nrf.com/resources/4-5-4-calendar and the Wikipedia article published at https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar. The calendar function requires 2 helper functions.  The helper functions were derived from […]

Table Variable Deferred Compilation (SQL 2019)

I have explored the SQL Server 2019, Intelligent Query Processing Feature – “Table Variable Deferred Compilation”.

The script contains some theory at the top and links to read.
After that, there are required queries to run on SQL Server 2019.
This way we can see the feature in action and look at it's strengths and caveats.

Post SQL Server Installation steps with PowerShell

I bet, each DBA would change some settings after the SQL Server installation as per their company standard . I wrote this PowerShell script for one of my client who has below standards: Port should be 1433, static Disable SQL telemetry* & SQLWriter services Change recovery model to FUll for all databases except tempdb and […]

move db files to different location dynamically

Lately, I'm dealing with lots of DB migrations and came across situation that I have no proper dynamic script to move DB files to different location. There are an option to proceed manually scripting one by one DB and execute it. Another option is detach attach DB - I'm not a big fan of this […]

Insert Multiple Rows

Insert multiple rows in the table using insert syntax which is the same as single insert rows. So, we have to use value likewise, single rows than there is the same process but include coma and insert the value of another row in your query. In this paragraph, we were given a syntax with insert […]