Skip to main content

Posts

DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server. DBCC commands take input parameters and return values. Database Console Command statements are grouped into the following categories. Category Maintenance -  Maintenance tasks on a database, index, or filegroup. Miscellaneous -  Miscellaneous tasks such as enabling trace flags or removing a DLL from memory. Informational -  Tasks that gather and display various types of information. Validation  - Validation operations on a database, table, index, catalog, filegroup, or allocation of database pages. Maintenance Statements DBCC CLEANTABLE Reclaims space from dropped variable-length columns in tables or indexed views. Syntax: DBCC CLEANTABLE   (       { database_name | database_id | 0 }       , { table_name | table_id | view_name | view_id }       [ , batch_size ]   )...
Read more

Data Files - SQL Server

SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes. Primary -  Contains startup information for the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf. Secondary -  Optional user-defined data files. Data can be spread across multiple disks by putting each file on a different disk drive. The recommended file name extension for secondary data files is .ndf. Transaction Log -  The log holds information used to recover the database. There must be at least one log file for each database. The recommended file name extension for transaction logs is .ldf. Logical an...
Read more

System Databases - SQL Server

Types of Databases in SQL Server 1. System Databases 2. User Databases Following are SQL Server System Databases: master - Records all the system-level information for an instance of SQL Server. msdb - Is used by SQL Server Agent for scheduling alerts and jobs. model - Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward. Resource - Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database. tempdb - Is a workspace for holding temporary objects or intermediate result sets. System Database Detailing: master -  The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such a...
Read more

Linked Server - SQL Server

Linked server is configured to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server.  Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.  Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages: 1. The ability to access data from outside of SQL Server. 2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise. 3. The ability to address diverse data sources similarly. Managing Linked Server Definitions When you are setting up a linked server, register the connection information and data source information with SQL Server. After being registered, that data source can be referred to with a ...
Read more

Resource Governance - SQL Server

SQL Server provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing. The following three concepts are fundamental to understanding and using Resource Governor: Resource pools :  Two resource pools (internal and default) are created when SQL Server 2012 is installed. Resource Governor also supports user-defined resource pools. Workload groups : Two workload groups (internal and default) are create...
Read more

Popular posts from this blog

SQL Server Services

Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during and after SQL Server installation.  Most services and their properties can be configured by using SQL Server Configuration Manager. SQL Server Version and its last versions when Windows is installed on the C drive. Version Path SQL Server 2019 C:\Windows\SysWOW64\SQLServerManager15.msc SQL Server 2017 C:\Windows\SysWOW64\SQLServerManager14.msc SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc SQL Server 2014 (12.x) C:\Windows\SysWOW64\SQLServerManager12.msc SQL Server 2012 (11.x) C:\Windows\SysWOW64\SQLServerManager11.msc SQL Server 200...
Read more

What is SQL Server?

SQL Server SQL Server is a relational database management system (RDBMS) developed by Microsoft. SQL Server supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL. Why SQL Server? Microsoft SQL Server is a comprehensive database server and information platform offering a complete set of enterprise-ready technologies and tools that help people derive the most value from information at the lowest total-cost-of-ownership. Enjoy high levels of performance, availability, and security; employ more productive management and development tools; and deliver pervasive insight with self-service business intelligence (BI). A complete and integrated platform, Microsoft SQL Server brings it all together to get more value out of existing IT skills and asse...
Read more

Download SQL Server Management Studio (SSMS)

Summary:  In this post, you will learn &  able to download  SSMS. About  SQL Server Management Studio ( SSMS ):  SSMS is Free!! SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts. Use SSMS to query, design and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud.   SSMS -  Prerequisites for Installation: SSMS supports the following 64-bit platforms when used with the latest available service pack: Supported Operating Systems: Windows 10 (64-bit) version 1607 (10.0.14393) or later Windows 8.1 (64-bit) Windows Server 2019 (64-bit) Windows Server 2016 (64-bit) Windows Server 2012 R2 (64-bit) Window...
Read more