T-SQL- check TEMPDB usage
You can monitoring TEMPDB usage using the following SQL statement.
SCHELSTRAETE.ORG
I.T. minded
SCHELSTRAETE.ORG
I.T. minded
Category SQL-Server
T-SQL: Select userids from linked servers
The code below can be used to query the linked servers & the user assigned to it.
T-SQL: Kill all inactive sessions from specific user
T-SQL: Check status of automatic seeding
T-SQL: size of replication specific tables
On the distribution database, you can execute the statement below to get the sizes of the internal replication specific tables
T-SQL: check replication backlog
You can use the code below on your distributor in order to check the backlog of the replication
T-SQL: Resume or suspend all always on databases
You can use the t-sql code to resume/suspend all databases from the always on group.
T-SQL: only run job on primary node
T-SQL: check seeding speed
T-SQL: Getting lag for sync always on replication
T-SQL: Getting lag for async always on replication
t-sql : find schema modifications which occurred the last day
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.
SSRS: Find out memory used by reporting service.
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.
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…
T-SQL: List transactions & log used
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…
T-SQL: List modified objects using default trace
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.
T-SQL: Check progress of backup/restore
Backups/restores can take a lot of time, but when it’s running, you want so see the progress.By executing the handy T-SQL code below, you can see the progress of the backups/restores on your system.
T-SQL: Remap SQL users
If you copy (backup/restore) a database from system A to system B, it is possible that the SQL users won’t be able login and this because the GUID/SID of the users on system A doesn’t match the GUID/SID of system…
T-SQL: List SQL Agent jobs history
Not all SQL scripts should be short 😉 The T-SQL code below will list the SQL Agent job history.
T-SQL: Flush memory cache for testing purposes
SQL server is of course caching data, in order to minimize access to disks. Sometimes you want to clear that memory, for testing purposes for example. You can use DBCC commands in order to clear the system / proc /…