SQLChampion.com — Data • AI • Agents

The most common types of SQL Server backups are complete or full backups, also known as database backups.  These backups create a complete backup of your database as well as part of the transaction log, so the database can be recovered. This allows for the simplest form of database restoration, since all of the contents are contained in one backup. A full backup can be completed either using T-SQL or by using SSMS Create a full backup of the AdventureWorks database to one disk file T-SQL BACKUP DATABASE AdventureWorks TO DISK = ‘C:\AdventureWorks.BAK’ GO SQL Server Management Studio Right click… Read more

The different types of backups that you can create are as follows: Full backups Differential backups File backups File group backups Partial backups Copy-Only backups Mirror backups Transaction log backups   we will discuss one by one in detail in next posts.      

By default, SQL Server system objects are listed in Object Explorer in Management Studio. These system objects include system database, system tables/views/procedures and so on. SQL Server Management Studio provides an option to hide these objects from Object Explorer to prevent *accidental* use. To enable this option follow below steps. Step1: Go to Tools > Options Step2: Navigate to Environment > Startup Step3: Check Hide system objects in Object Explorer and click OK: Step4: An confirmation message will be displayed saying that changes will take effect after Management Studio is restarted, click on OK to continue:   Once you restart SQL Server Management Studio, you will notice that system objects are no longer listed in… Read more

if convert(varchar(20),SERVERPROPERTY(‘productversion’)) like ‘8%’ SELECT [name], fileid, filename, [size]/128 AS ‘Total Size in MB’, [size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’, CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’, (100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’ FROM sysfiles else SELECT [name], file_id, physical_name, [size]/128 AS ‘Total Size in MB’, [size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Available Space In MB’, CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0 AS ‘Used Space In MB’, (100-((([size]/128.0 – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128)/([size]/128))*100)) AS ‘percentage Used’ FROM sys.database_files  

To start troubleshooting, you have to define the symptoms first. The most common SQL Server performance symptoms are CPU, memory, network, and I/O bottlenecks, and slow running queries CPU bottlenecks are caused by insufficient hardware resources. Troubleshooting starts with identifying the biggest CPU resource users. Occasional peaks in processor usage can be ignored, but if the processor is constantly under pressure, investigation is needed. Adding additional processors or using a more powerful one might not fix the problem, as badly designed processes can always use all CPU time. Query tuning, improving execution plans, and system reconfiguration can help. To avoid… Read more

Below query will generated list of indexes to be re organized or rebuild based on 10-40 Rule. >=40 % Rebuild >=10 and <40 ReOrganize — This query is tested on working fine.       SELECT ‘ALTER INDEX [‘ + ix.name + ‘] ON [‘ + s.name + ‘].[‘ + t.name + ‘] ‘ +        CASE WHEN ps.avg_fragmentation_in_percent >= 40 THEN ‘REBUILD’ ELSE ‘REORGANIZE’ END +        CASE WHEN pc.partition_count > 1 THEN ‘ PARTITION = ‘ + cast(ps.partition_number as nvarchar(max)) ELSE ” END FROM   sys.indexes AS ix   INNER JOIN sys.tables t  ON t.object_id = ix.object_id        INNER JOIN… Read more

Every database in SQL Server which is getting read by the user or application is retrieved from the buffer cache. If the pages which are not in buffer cache are read by the user/application, those pages will be loaded into buffer cache first and then retrieved to the user/application.   Database Pages which occupied in the cache are retrieved by the following query:

 select top 10 * from master.dbo.sysprocesses  where status <> ‘background’ AND cmd NOT IN  (‘signal handler’,’lock monitor’,’log writer’,’lazy writer’,’checkpoint sleep’,’awaiting command’,’TASK MANAGER’) and spid > 50  and spid <> @@spid  order by CPU desc

Microsoft SQL Server provides DBAs and Developers with several index related dynamic management views and functions, which they can use to maintain, modify, tune and identify missing indexes. Some of these dynamic management views (DMVs) and functions (DMFs) you might use are listed as follow: sys.dm_db_index_operational_stats — Returns information about low level input/output (I/O) operations, locking and latching contention, and access methods. sys.dm_db_index_usage_stats — Returns an index operations count, identifying operations performed and the last time each operation was performed. sys.dm_db_index_physical_stats — Returns information about table and index size and fragmentation, including both in-row and LOB data. sys.dm_db_missing_index_details — Returns detailed information about missing… Read more

1.Master 2. MSDB 3. mssqlsystemresource 4. User Databases 5.  Distribution 6.  Model 7. TempDB