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.
It’s possible that you want to change routes on a system which point to a certain gateway, and replace them with a new gateway. The only thing this silly powershell is doing, is getting the list of routes, filter out the ‘old gateway’, replace that gateway, and put the deletes/add…
You can check the status of SAP Hana using the following command line:
You can use the T-SQL code below within your database to give permissions to ‘MYUSER’ for viewing all stored procedures. (of course, replace “MYUSER” with your own user) Once executed, you can copy and paste the output to a new query window an execute it.
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 to the id of your database. (find out in sys.databases)
Step 1: If block corruption was found, first find the corrupted blocks: Stzp 2: Check to which table/index/etc that block belongs to. Step 3: Resolve the block corruption using RMAN (which is one of the possibilities):
If you do a df, and you compare those values with ‘du’, it is possible that those values don’t match. There are multiple reasons why this can happen, and one of those reasons if that files where deleted, but that the files are still in use by an application.In that…
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 more info regarding a transaction
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.
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.
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 B. It is using the EXEC sp_change_users_login ‘Auto_Fix’, which inks…
Not all SQL scripts should be short 😉 The T-SQL code below will list the SQL Agent job history.
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 / session cache. See the SQL below:
When you are looking for the creation date/time of certain tables, you can easily find that in the sys.tables.You can use the code below in order to list all tables, and the creation date of those tables.
Sometimes you need to know when a SQL login was created. The SQL code below will list the SQL logins, when this was created, and if this is enabled or disabled.
The t-sql code below will give you the top 10 most CPU consuming SQL statements in your database, sorted by CPU time.
Disk I/O is most of the time the bottleneck of your SQL database, even when using SSD. The SQL statement below will show you the most heavy disk I/O statements, which you should improve.
The code below will give you the top 10 most executed stored procedures in your database, sorted by execution count.
Interested to see how long your SQL server agent is up and running, or you want to see if that agent is operational? You can use the t-sql code below to find out:
The SQL statement below will give you the top 100 longest running SQL statements in your database, sorted by duration.
Looking for a table, but you don’t remember in which database it’s located? The code below will create a stored procedure, which you can use in order to find a table in multiple databases.
You can use the t-sql code below in order display the size of each table in a specific database.
Sometimes you need to do maintenance on a database, and you don’t really want to use single user mode or disable accounts. In that situation you could kill all connections to a specific database.In order to achieve that, you can use the t-sql code below.
You can use the following shell commands in order to redirect a port to another port.
Using the “stress” application, you can simulate high memory usage of your system.You may use the following command line: