ALTER DATABASE [UserDBInProduction] SET AUTO_CLOSE OFF WITH NO_WAIT
That is all for now, let me know any remarks you may have. Thanks for reading again. Stay tuned.
ALTER DATABASE [UserDBInProduction] SET AUTO_CLOSE OFF WITH NO_WAIT
SELECT S.[name] AS 'Referenced Table' FROM sys.objects S INNER JOIN sys.sysreferences R ON S.OBJECT_ID = R.rkeyid WHERE S.[type] = 'U' AND R.fkeyid = OBJECT_ID('[Production].[Product]')
SELECT S.[name] AS 'Referencing Table' FROM sys.objects S INNER JOIN sys.sysreferences R ON S.OBJECT_ID = R.fkeyid WHERE S.[type] = 'U' AND R.fkeyid = OBJECT_ID('[Production].[Product]')
USE [master] GO CREATE PROCEDURE [dbo].[sp_DBA_Backup_FilesCleanup] ( @DatabaseName VARCHAR(200), @BackupType VARCHAR(100), @DriveName VARCHAR(1), @RetentionDays VARCHAR(4)) WITH ENCRYPTION AS BEGIN SET NOCOUNT ON DECLARE @strcmd VARCHAR(4000) DECLARE @directory VARCHAR(4000) SET @directory=@DriveName + ':\SQLBackup\' + @DatabaseName --+ '\' + @BackupType SET @strcmd='forfiles /p "'+@directory+'" /s /d -'+ @RetentionDays +' /c "cmd /c del /q @path"' -- print @strcmd EXEC master.dbo.xp_cmdshell @strcmd SET NOCOUNT OFF END GO
USE [master] GO EXEC dbo.sp_DBA_Backup_FilesCleanup @DatabaseName='MyDB' , @BackupType='FULL',@DriveName='G', @RetentionDays='7' GO EXEC dbo.sp_DBA_Backup_FilesCleanup @DatabaseName='MyDB' , @BackupType='Diff',@DriveName='G', @RetentionDays='7' GO EXEC dbo.sp_DBA_Backup_FilesCleanup @DatabaseName='MyDB' , @BackupType='Log',@DriveName='G', @RetentionDays='7'
USE master GO IF EXISTS (SELECT name FROM master.sys.tables WHERE name='TmpFileSpace') DROP TABLE TmpFileSpace CREATE TABLE [dbo].[TmpFileSpace]( [DatabaseName] [nvarchar](128) NULL, [FileName] [sysname] NOT NULL, [FileSizeGB] [decimal](10, 2) NULL, [SpaceUsedGB] [decimal](10, 1) NULL, [SpaceFreeGB] [decimal](10, 1) NULL, [SpaceFree%] [decimal](10, 1) NULL ) ON [PRIMARY] EXEC sp_MSforeachdb ' USE [?] INSERT INTO master.dbo.[TmpFileSpace]([DatabaseName], [FileName], [FileSizeGB], [SpaceUsedGB], [SpaceFreeGB], [SpaceFree%]) SELECT DB_NAME() DatabaseName, name FileName, CAST(size/128.0/1024.0 AS DECIMAL(10,2)) SizeGB, CAST(FILEPROPERTY(name,''SpaceUsed'') /128.0/1024.0 AS DECIMAL(10,1)) SpaceUsedGB, CAST((size - FILEPROPERTY(name,''SpaceUsed'')) /128.0/1024.0 AS DECIMAL(10,1)) SpaceFreeGB, CAST(((size - FILEPROPERTY(name,''SpaceUsed''))/(size*1.0)) *100 AS DECIMAL(10,1)) [SpaceFree%] FROM sys.database_files' SELECT db.name DatabaseName,db.collation_name,db.compatibility_level, SUSER_SNAME(owner_sid) OwnerName, db.page_verify_option_desc, db.is_auto_close_on, db.is_auto_create_stats_on,db.is_auto_shrink_on, db.is_auto_update_stats_on, db.is_auto_update_stats_async_on,db.name DatabaseName, fs.FileName, fs.FileSizeGB , fs.SpaceUsedGB, fs.SpaceFreeGB, fs.[SpaceFree%], physical_name, cast(size/128.0/1024.0 as decimal(10,2)) FileSizeGB, db.state_desc,max_size,growth,is_percent_growth FROM sys.master_files mf INNER JOIN sys.databases db ON mf.database_id = db.database_id INNER JOIN TmpFileSpace FS ON mf.database_id=db_id(Fs.DatabaseName) AND mf.name=fs.FileName DROP TABLE [TmpFileSpace]
USE [YourDatabaseName] SELECT DB_NAME() DatabaseName, name FileName, CAST(size/128.0/1024.0 AS DECIMAL(10,2)) SizeGB, CAST(FILEPROPERTY(name,'SpaceUsed') /128.0/1024.0 AS DECIMAL(10,1)) SpaceUsedGB, CAST((size - FILEPROPERTY(name,'SpaceUsed') ) /128.0/1024.0 AS DECIMAL(10,1)) SpaceFreeGB, CAST(((size - FILEPROPERTY(name,'SpaceUsed') )/(size*1.0)) *100 AS DECIMAL(10,1)) [SpaceFree%] FROM SYS.database_files
select c.* from sys.dm_exec_sessions s cross apply sys.dm_exec_cursors(con.session_id) as c where cur.fetch_buffer_size = 1 and cur.properties LIKE 'API%'
select s.session_id, cn.client_net_address, s.login_name, s.status,s.client_interface_name, s.program_name, c.cursor_id, c.name, c.properties, c.plan_generation_num, c.creation_time, c.is_open, c.fetch_status, c.fetch_buffer_size, c.worker_time, c.reads, c.writes, c.dormant_duration from sys.dm_exec_connections cn inner join sys.dm_exec_sessions s on cn.session_id = s.session_id cross apply sys.dm_exec_cursors(s.session_id) as c where c.fetch_buffer_size = 1 and c.properties LIKE 'API%'
SELECT cast( cast( COUNT(*) /128.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' AS TotalUsageBufferPool FROM sys.dm_os_buffer_descriptors
SELECT CASE database_id WHEN 32767 THEN 'ResourceDb' ELSE db_name(database_id) END AS DatabaseName, cast( COUNT(*) /128.0 as decimal(10,2)) AS [BufferPool(MB)] FROM sys.dm_os_buffer_descriptors GROUP BY DB_NAME(database_id) ,database_id ORDER BY [BufferPool(MB)] DESC
-- SQL2012/2014/2016/2017 select cast(cast(physical_memory_kb /1024.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' TotalPhysicalRAM, cast(cast(visible_target_kb /1024.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' MaxRAM, -- max memory configure at sql server level cast(cast(committed_target_kb /1024.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' ReservedRAM, --memory reserved cast(cast(committed_kb /1024.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' UsedRAM --memory used currently FROM sys.dm_os_sys_info -- for SQL2005/2008/2008R2 SELECT cast(cast(physical_memory_in_bytes /1024.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' TotalPhysicalRAM, cast(cast(bpool_visible /128.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' MaxRAM, -- max memory configure at sql server level cast(cast(bpool_commit_target /128.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' ReservedRAM, --memory reserved cast(cast(bpool_committed /128.0/1024.0 as decimal(10,2)) as varchar(10)) + 'GB' UsedRAM --memory used currently FROM sys.dm_os_sys_info
SET NOCOUNT ON DECLARE @schema_name varchar(max) DECLARE @table_name varchar(max) DECLARE @stat_name varchar(max) DECLARE @update_stat_cmd varchar(max) DECLARE @update_stat_msg_header varchar(max) DECLARE update_stat_cursor CURSOR FOR select schema_name(o.[schema_id]), object_name(s1.[object_id]) , s1.name from ( select s.[object_id], s.name from sys.stats s left join sys.indexes i on s.name=i.name where i.name is null) s1 inner join sys.objects o on o.[object_id]=s1.[object_id] where o.type='U' order by schema_name(o.[schema_id]), object_name(s1.[object_id]) , s1.name OPEN update_stat_cursor FETCH NEXT FROM update_stat_cursor INTO @schema_name, @table_name, @stat_name WHILE (@@fetch_status = 0) BEGIN DECLARE @ini DATETIME, @fin DATETIME SET @update_stat_msg_header = '->Updating ['+ RTRIM(@schema_name) +'].[' + RTRIM(@table_name) + ']([' +@stat_name + '])' PRINT @update_stat_msg_header SET @update_stat_cmd ='UPDATE STATISTICS ['+ RTRIM(@schema_name) +'].[' + RTRIM(@table_name) + ']([' +@stat_name + '])' SET @ini=GETDATE() EXEC (@update_stat_cmd) SET @fin=GETDATE() FETCH NEXT FROM update_stat_cursor INTO @schema_name, @table_name, @stat_name END PRINT ' ' PRINT '----------------------------------------------------------------------------- ' SET @update_stat_msg_header = '************* THERE ARE NO MORE STATISTICS TO BE UPDATED **************' PRINT @update_stat_msg_header PRINT ' ' PRINT 'All statistics not linked to any index were rebuilt!' CLOSE update_stat_cursor DEALLOCATE update_stat_cursor SET NOCOUNT OFF