Find the database file growth for all the databases. It is not a good practice to keep your database file growth limited. In that case, you may get an error like “The Transaction log for database XXXXX is full.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- ============================ -- -- Owner: SOYELUDDIN BISWAS -- -- Created Date: 07/10/2019 -- -- Email: st.biswas99@gmail.com -- -- ============================ -- SELECT DBS.name as [Database Name] ,DBS.recovery_model_desc as [Recovery Model] ,DBMF.name as [File Name] ,CASE WHEN DBMF.is_percent_growth=1 THEN CAST(DBMF.growth as VARCHAR(10))+' %' WHEN DBMF.is_percent_growth=0 THEN CAST(((DBMF.growth*8)/1024) as VARCHAR(10))+' MB' END as [File Growth type and Value], CASE WHEN DBMF.max_size=-1 THEN 'Unlimited' WHEN DBMF.max_size!=-1 THEN CAST((CAST (DBMF.max_size AS BIGINT)) * 8 / 1024 AS VARCHAR(10))+' MB' END as [File Growth] ,DBMF.physical_name as [File Location] FROM sys.databases DBS INNER JOIN sys.master_files DBMF ON DBS.database_id = DBMF.database_id WHERE DBS.database_id>4 ORDER BY 1 ASC |
Also, you clear the transaction log by taking backup which will allow you to perform further transactions. Run the below script to see if there is any log backup pending. If you find “LOG_BACKUP” in log_reuse_wait_desc column then you can take backup else you will need to set the file auto-growth unlimited and extend the storage if you have less storage.
1 |
select name, log_reuse_wait_desc from sys.databases |
DMV ‘sys.dm_exec_requests’ provides details on all of the processes running in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT qs.Session_ID, Blocking_Session_ID, qs.Status, Wait_Type, Wait_Time, Wait_Resource, SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text, GetDate() SnapshotDateTime, --Open_Transaction_Count, ss.PROGRAM_NAME, ss.HOST_NAME, ss.Login_Name FROM sys.dm_exec_requests AS qs INNER JOIN sys.dm_exec_sessions ss ON qs.session_id = ss.session_id CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE Wait_Time > 0 ORDER BY Wait_Time DESC |
“Please let us know if there…