SQL-Server

T-SQL-Commands

T-SQL: Shrink incremental

First off you don’t ever shrink a database file unless you known it won’t need the space back again. (There’s some caveats to that statement but it mostly holds true) If that’s the space the file has consumed generally that’s…

Read More
T-SQL-Commands

T-SQL: Clear msdb backup history

After a while,the msdb database can be overloaded with backup history, and clearing that history can be a pain. You can use the t-sql code below to clear the msdb backup history, in incremental steps of 1 week.

Read More
T-SQL-Commands

T-SQL: Add/drop pass to open a database master key

When SQL Server needs a database master key to decrypt or encrypt a key, SQL Server tries to decrypt the database master key with the service master key of the instance. If the decryption fails, SQL Server searches the credential…

Read More
T-SQL-Commands

T-SQL: Jobs in suspended state

It is possible that sql jobs are “hanging” in a suspended state. The SQL statements below will give you an indication on what the job is waiting.The first SQL statement will create a small stored procedure in the master database,…

Read More
T-SQL-Commands

SQL server: Slow SSMS

If you are using SSMS 17 or 18, you have maybe noticed that the SSMS startup can be very very very very slow, which is annoying for everyone. There are 2 workaround, which could resolve this issue: Open  Internet Explorer: Options…

Read More
T-SQL-Commands

T-SQL: Kill all inactive sessions from specific user

The SQL statement below will kill all sessions from a specific user, which wasn’t active the last hour.Of course, change ‘my_user’ with your preferred userid. If you want you can also uncomment the ‘program_name’ if you want to filter on…

Read More
T-SQL-Commands

T-SQL: Check status of automatic seeding

Automatic seeding for SQL Always On it nice, but in case of big databases this can take some time. Of course, it can be annoying if something is running, and you don’t see the status of it. The query below…

Read More
T-SQL-Commands

T-SQL: Only run SQL agent job on primary node

In a SQL Always On setup, the SQL agent jobs are not synced between the different nodes. This means you need to manually create them on the other nodes. For read-write jobs, you can only run them on the active…

Read More
T-SQL-Commands

T-SQL: Check progress of DBCC checkdb

Doing a dbcc checkdb can take a lot of time, and in case of corruption you really want to see the progress. You can use the t-sql statement below to find the progress. Keep in mind to replace the YOUR_DB_ID…

Read More