Below script will give the command to execute to change the database auto-growth. If you need to execute for only a single database, then please copy the relevant rows and execute.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
--================================ -- -- Owner: SOYELUDDIN BISWAS -- Created Date: 24/09/2019 -- -- Email: st.biswas99@gmail.com -- ================================== -- USE [tempdb/] GO IF OBJECT_ID('tempdb..#DBGrowth') IS NOT NULL DROP TABLE #DBGrowth Declare @GrowthType NVARCHAR(10) Declare @DatabaseName NVARCHAR(300) Declare @logicalName NVARCHAR(20) Declare @GrowthCMD NVARCHAR(300) --######### Growth Type and Value ############ --Example, If you want set growth in % then please mention your value in % such as @GrowthType='10%' --If want set growth in MB then please mention your value in KB such as @GrowthType='1024KB' -- Copy the below Growth Set @GrowthType='1024KB' Create Table #DBGrowth ( DatabaseName NVARCHAR(300), logicalName NVARCHAR(20), GrowthCMD NVARCHAR(300) ) INSERT INTO #DBGrowth select DBS.name, DBF.name, ' ALTER DATABASE ['+DBS.name+'] MODIFY FILE ( NAME = N'''+DBF.name+''', MAXSIZE = UNLIMITED, FILEGROWTH = '+@GrowthType+' )' from sys.databases DBS Inner Join sys.sysaltfiles DBF ON DBS.database_id=dbf.dbid WHERE DBS.name NOT IN ('master','tempdb','model','msdb') SELECT DatabaseName as [Database Name], logicalName as [Logical Name], GrowthCMD as [Command to execute] FROM #DBGrowth |
The same result can be obtain by using GUI SSMS also. Set Auto growth for SQL database using GUI
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…