Sql Server Materials

 Databases can use a lot of storage space. It is always good to know how the space is distributed among the tables and indexes. SQL Server allows us to query all relevant data and create our desired reports. In this article I will show different queries and alternative options to access the storage sizes at various levels of detail. What requires storage space? A database has a total size which includes the actual data, transaction log data and reserved storage space, since the database is expected to grow. The data is stored in one primary data file with the file extension .mdf, optional user-defined data files with the file extension .ndf and at least one transaction log file with the file extension .ldf. See this article about database files and filegroups to learn more about it. The data is basically separated into two types: tables and indexes. A table has either a clustered index with all data or it is a heap table. Indexes are copies of the data and need space too. Note that...

  To solve slowness of SQL Server database, you should find which wait events exists in the database. You can find wait events of database with following query. with waits as (select wait_type, wait_time_ms / 1000.0 as waits, (wait_time_ms - signal_wait_time_ms) / 1000.0 as resources, signal_wait_time_ms / 1000.0 as signals, waiting_tasks_count as waitcount, 100.0 * wait_time_ms / sum (COALESCE (wait_time_ms,1)) over() as percentage, row_number() over(order by wait_time_ms desc) as rownum from sys.dm_os_wait_stats where wait_type not in ( N'CLR_SEMAPHORE', N'LAZYWRITER_SLEEP', N'RESOURCE_QUEUE', N'SQLTRACE_BUFFER_FLUSH', N'SLEEP_TASK', N'SLEEP_SYSTEMTASK', N'WAITFOR', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH', N'XE_TIMER_EVENT', N'XE_DISPATCHER_JOIN', N'LOGMGR_QUEUE', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'BROKER_TASK_STOP',...

  -- Alter  script set  compatibility y to sql server  2022 DECLARE @DBName SYSNAME; DECLARE @SQL NVARCHAR(MAX); -- Table variable to hold database names DECLARE @DBList TABLE (DBName SYSNAME); -- Insert all user databases except system ones INSERT INTO @DBList (DBName) SELECT name FROM sys.databases WHERE database_id > 4 -- Exclude system DBs AND state_desc = 'ONLINE'; -- Initialize loop WHILE EXISTS (SELECT 1 FROM @DBList) BEGIN     -- Get one database name     SELECT TOP 1 @DBName = DBName FROM @DBList;     -- Build and execute the ALTER statement     SET @SQL = 'ALTER DATABASE [' + @DBName + '] SET COMPATIBILITY_LEVEL = 160;';     PRINT 'Updating compatibility for: ' + @DBName; PRINT @SQL     --EXEC sp_executesql @SQL;     -- Remove processed DB     DELETE FROM @DBList WHERE DBName = @DBName; END --Set  compatability  to  SQL server  2022  SELECT 'ALTER D...

  Script to Loop through all the transaction log file and restore them automatically. Below script is generating the restoring the  transactional log backups.  Initially  we have inserted transaction files into  temp table and then we have generating restoring the tractional log backup. Method1: USE master; DECLARE @t AS TABLE (fname VARCHAR(max)); INSERT @t EXEC xp_cmdshell 'dir /b /S E:\dirname\*.trn'; SELECT 'RESTORE LOG [DBNAME] FROM DISK = ''' + fname + ''' WITH NORECOVERY;' FROM @t WHERE fname IS NOT NULL Method2: Powershell  Script; To automatically loop through and restore all SQL Server transaction log files ( .trn ), you can use a PowerShell script or T-SQL script depending on your environment and preferences. Here's a PowerShell script that reads all .trn files from a folder and restores them in sequence. # Define variables $serverName = "YourSQLServerInstance" $databaseName = "YourDatabase" $backupFolder ...

  -- Find the job names with user name with running as job owner.  SELECT j.name AS JobName,        j.owner_sid,        s.name AS JobOwner,        a.start_execution_date FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobactivity AS a     ON j.job_id = a.job_id INNER JOIN sys.server_principals AS s     ON j.owner_sid = s.sid WHERE a.run_requested_date IS NOT NULL   AND a.stop_execution_date IS NULL  -- Job is still running   AND s.name LIKE  '%SVC_%';       -- Replace with the specific user -- Find the job name, with command context .  SELECT j.name AS JobName,        js.step_id,        js.step_name,        js.database_name,        js.command,        js.subsystem FROM msdb.dbo.sysjobs AS j INNER JOIN msdb.dbo.sysjobsteps AS js     ON j.job_id =...