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
ALTER DATABASE MyEnterpriseDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE; GO DBCC CHECKDB(MyEnterpriseDB) WITH PHYSICAL_ONLY
ALTER DATABASE MyEnterpriseDB SET EMERGENCY; GO DBCC CHECKDB (MyEnterpriseDB, REPAIR_ALLOW_DATA_LOSS) WITH PHYSICAL_ONLY GO ALTER DATABASE MyEnterpriseDB SET ONLINE WITH ROLLBACK IMMEDIATE; GO ALTER DATABASE MyEnterpriseDB SET MULTI_USER WITH ROLLBACK IMMEDIATE;
USE [msdb] GO CREATE PROC[dbo].[usp_change_owner_job] @jobname varchar(max), @newowner varchar(max) with execute as owner as begin declare @old_owner varchar(max) select @old_owner= s.name from msdb.dbo.sysjobs j inner join sys.server_principals s on j.owner_sid= s.sid where j.name=@jobname --declare @newowner varchar(max) --set @newowner=ORIGINAL_LOGIN() EXEC msdb.dbo.sp_update_job @job_name=@jobname, @owner_login_name=@newowner --print @newowner declare @msg varchar(max) set @msg= 'The owner of ' +@jobname + ' job was changed from ''' + @old_owner + ''' to ''' + @newowner + '''' print @msg end
EXEC msdb.dbo.[usp_change_owner_job] 'BusinessJob01', 'User2'
The owner of BusinessJob01 job was changed from 'User2' to 'sa'
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.In order to get it fixed, firstly, developers should check .NET code within applications. In some cases, the developers I worked with found infinite loops or slow codes which spent much time before sending SQL queries to the database engine, as a result of this, the timeout threshold was reached at application level and, obviously, the error was raised. By the way, the following error could also be related to timeout:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Unable to connect to SQL Server session database.Having checked the application, you can increase the 'CommandTimeout' property value to 60 seconds (if it is not enough then change to 120 or 180, and also considerate using 0 (unlimited) which should be a workaround while the problem is being traced and fixed). That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.