MS SQL SERVER DBA

There are many reasons which cause the SQL Server Log file to grow to unbelievable size i.e., the space available in the Disk. 1. whenever we have not scheduled a Log_backup on the server the log file size grows bigger and occupies the disk on the specific drive. 2. If we have any repplication setup. Then we will be have log file growth. 3. Due to long running transaction also that causes the Log file grows. All the transactions that are performed on the SQL are done at the log files, after a commit transaction i.e.., from code or the SQL Server check point the data is updated to the Data file of the database. To Check the Growth of the Database Log files. DBCC SQLPERF(LOGSPACE) The Log Size (MB) column shows the size of the log files The Log Space Used(%) column shows how much of the log space is used by the SQL Server Log Files. Example: In some environment we will be able to see the below Log Size (MB): 43562.19 Log Space Used: 1000.45 When ever we take ...

SELECT DISTINCT TOP 10 t.TEXT QueryName, s.execution_count AS ExecutionCount, s.max_elapsed_time AS MaxElapsedTime, ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime, s.creation_time AS LogCreatedOn, ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY s.max_elapsed_time DESC GO To find the Active Running Transactions on SQL Server. https://youtu.be/6jPl1jKRfvU Please Like, Subscribe, share the video for more videos...!!!

The below query shows the output of the transactions that are actively running on the Server. SELECT sqltext.TEXT, dm.session_id, dm.status, dm.command, dm.cpu_time, dm.total_elapsed_time FROM sys.dm_exec_requests dm CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext   When we run the above query it shows the output what are the transactions that are running on the server. And it varies from time to time. As we refresh the query we will find the new query's executing on the server. This can be experienced when we run the query on the Production environment.  The same can be implemented on the stand-alone system i..e., personal computer where we schedule some jobs with SQL Script that inserts some information and some query's to retrieved the data.