Scripts

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.

3.67 (3)

2020-08-03

813 reads

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 […]

5 (2)

2020-07-02 (first published: )

1,385 reads

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 […]

3 (2)

2020-07-01 (first published: )

1,007 reads

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 […]

2020-06-29

806 reads

How to register SPN for SQL service account

Check if the SPN is already registered: setspn -l domain\xxxxx If not, run below commands: setspn -A MSSQLSvc/abc.xx.companyname.com:1433 domain\xxxxx setspn -A MSSQLSvc/abc.xx.companyname.com domain\xxxxx setspn -A MSSQLSvc/abc:1433 domain\xxxxx setspn -A MSSQLSvc/ abc domain\xxxxx   Verify again, setspn -l domain\xxxxx

5 (1)

2020-06-17 (first published: )

2,097 reads

Monitor basic Database parameters like Recovery Model, Page Verify, Auto Close, Auto Shrink, DB Owner, Auto Create Statistics Enabled, DB Create Date etc with PowerShell command.

I have created this script for one of our client, the requirement was all the databases should be in full recovery model and should be monitored. Also, I added other important parameters like AutoShrink, AutoClose etc. Output:

5 (1)

2020-06-16

544 reads

Getting more info about who or what connect to your SQL Server

A couple of days ago I was playing with my small SQL Server enviroment (test) and auditting login events. I went ahead and created the audit for FAILED_LOGIN_GROUP and SUCCESSFULL_LOGIN_GROUP (let it run for a while). USE [master] GO CREATE SERVER AUDIT [Audit_LoginEvents] TO FILE ( FILEPATH = N'<PathToStoreData>' ,MAXSIZE = 100 MB ,MAX_FILES = […]

2020-06-03 (first published: )

1,089 reads

Blogs

Goal Progress for July 2021

By

I set goals at the beginning of the year, and I’m tracking my progress...

Data Modeling at your fingertips

By

(2021-July-30) A picture is worth a thousand words, the same way a visual database schema...

How to put a SQL Server Database into Recovery Pending

By

You might be asking why on earth would you want to get a database...

Read the latest Blogs

Forums

How is Throughput optimized HDD (st 1)

By kratosrules99

Hi Everyone. i am planning to shift my website to AWS   I want...

Alternative to LAG(SUM) - Can a Column Reference The Sum of the Values Above It

By John O'Connor

Hi Everyone, I'm trying to do something that seems pretty simple, but SQL Server...

Transaction log shrink w/ logshipping

By mavelzen

Hello everyone, Again with this question. 🙂 I've got a log-shipped database, and I...

Visit the forum

Ask SSC

SQL Server Q&A from the SQLServerCentral community

Get answers