Category SQL-Server

You can group datetime fields by year/month by using a simple ‘format’. Of course, there are other possibilities for doing this. See example below:

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…

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,…

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…

The T-SQL code below lists the currently running SQL Agent jobs.

You can monitoring TEMPDB usage by using the following SQL statement.

The code below can be used to query the linked servers & the user assigned to it.

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…

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…

On the distribution database, you can execute the statement below to get the sizes of the internal replication specific tables

You can use the code below on your distributor in order to check the backlog of the replication

You can use the t-sql code to resume/suspend all databases from the always on group.The code below will resume SQL data movement for all databases: The code below will suspend SQL data movement for all databases:

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…

If your SQL Always On having a delay? Use the T-SQL code below to find out the performance of the SQL Always on.

In SSMS you have the “schema changes history” report. You can have the same (or even more) details from T-SQL. You can use the t-sql statement below in order find schema changes the last day, in a particular database.

The T-SQL code below will give you an idea of the memory used by your reporting service. This query should be executed on the “reportserver ” database. Please be aware executing this statement can take some time.

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…

The code below will list the transactions on the instance, and it will also include the size of the log which is used for that transaction.The output is ordered by log size. You can also use the below to find…

You can use the default trace in SQL server in order to track CREATE/DELETE/ALTER DDL operations.The T-sql code below will show the CREATE/DELETE/ALTER DDL operations visible in the default trace.