SQL BASICS AND DBCC COMMANDS

-

Restore Database
ü  “If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database.”

ü  “RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.”

Backup

ü  BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperatorfixed database roles.

ü  Ownership and permission problems on the backup device's physical file can interfere with a backup operation. SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. However, sp_addumpdevice, which adds an entry for a backup device in the system tables, does not check file access permissions. Such problems on the backup device's physical file may not appear until the physical resource is accessed when the backup or restore is attempted.

Shutdown
ü  SHUTDOWN permissions are assigned to members of the sysadmin and serveradmin fixed server roles, and they are not transferable.

CkeckPoint
ü  CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.
CMS
ü  Two database roles in the msdb database grant access to central management servers. Only members of the ServerGroupAdministratorRole role can manage the central management server. Membership in the ServerGroupReaderRole role is required to connect to a central management server.

ü  Because the connections that are maintained by a central management server execute in the context of the user, by using Windows Authentication, the effective permissions on the registered servers might vary. For example, the user might be a member of the sysadmin fixed server role on the instance of SQL Server A, but have limited permissions on the instance of SQL Server B.

Step1: -

DBCC Commands






Category
Uses
Commands
Maintenance statements
Maintenance tasks
DBCC DBREINDEX, DBCC DBREPAIR, DBCC INDEXDEFRAG, DBCC SHRINKDATABASE, DBCC SHRINKFILE, DBCC UPDATEUSAGE, DBCC CLEANTABLE, DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE
Status statements
Status checks
DBCC INPUTBUFFER, DBCC OPENTRAN, DBCC OUTPUTBUFFER, DBCC PROCCACHE, DBCC SHOWCONTIG, DBCC SHOW_STATISTICS, DBCC SQLPERF, DBCC TRACESTATUS, DBCC USEROPTIONS
Validation statements
Validation operations on a database and database components such as table, index, file catalog, etc.
DBCC CHECKALLOC, DBCC CHECKCATALOG, DBCC CHECKCONSTRAINTS, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKIDENT, DBCC CHECKTABLE, DBCC NEWALLOC
Miscellaneous statements
Miscellaneous tasks
DBCC dllname (FREE), DBCC HELP, DBCC PINTABLE, DBCC ROWLOCK, DBCC TRACEOFF, DBCC TRACEON, DBCC UNPINTABLE
Source: MSDN Transact-SQL Reference (SQL Server 2000)

Operation of DBCC statements

DBCC DBREINDEX

This is a statement is used to recreate the indexes for a particular table. This statement rebuilds indexes in a single step. It also assigns fresh pages to reduce internal and external fragmentation.

DBCC DBREPAIR

This statement is used to drop or delete a damaged database. However, this command is no longer available with Microsoft SQL Server 2005 and later versions of Microsoft SQL Server. Instead, it has been replaced by the DROP DATABASE Transact-SQL statement.[7]

DBCC INDEXDEFRAG

This statement is used to defragment the clustered and secondary indexes associated with the particular table. The index defragmentation is carried out using the fill factor specified at the time of creation of indexes. While its operation is strikingly similar to that of DBCC DBREINDEX, unlike DBCC INDEXDEFRAG it does not allow new fill factor to be specified.

DBCC SHRINKDATABASE

This statement is used to reduce the size of a database. This statement reduces the physical size of the database log file. An alternate way to shrink a database is to use the commander ALTER DATABASE.

DBCC SHRINKFILE

This statement is used to reduce the size of a data file or log file of a particular database. The file could also be shrunk by using the SHRINKFILE attribute of the ALTER DATABASE command.

DBCC UPDATEUSAGE

This statement is used to correct inaccuracies in the page and row statistics in the views.

DBCC CLEANTABLE

This statement is used to remove spaces occupied by columns when they are removed. This feature is not available with Microsoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005

DBCC DROPCLEANBUFFERS

This statement is used to drop clean buffers from the buffer pool. This feature is available with Microsoft SQL Server 2000 and in Microsoft SQL Server 2005

DBCC FREEPROCCACHE

This statement is used to remove all elements from the procedure cache. This feature is not available with Microsoft SQL Server 2000 and has been newly introduced in Microsoft SQL Server 2005

DBCC INPUTBUFFER

This statement is used to display the last statement stored in the buffer.

DBCC OPENTRAN

This statement is used to display information about the oldest open transaction.

DBCC OUTPUTBUFFER

This statement is used to return the current value of the output buffer.

DBCC PROCCACHE

This statement is used to display information about procedure cache.

DBCC SHOWCONTIG

This statement is used to display fragmentation information

DBCC SHOW_STATISTICS

This is a statement is used to show current distribution statistics

DBCC SQLPERF

This statement is used to show transaction log statistics

DBCC TRACESTATUS

This statement is used to display status of trace flags

DBCC USEROPTIONS

This statement is used to return set as ACTIVE

DBCC CHECKALLOC

This statement is used to check whether every extent allocated by the system has been allocated and whether there are extents that have not been allocated.

DBCC CHECKCATALOG

This statement is used to check for consistency between system tables in the system catalog. It does so through cross-referencing checks.

DBCC CHECKCONSTRAINTS

This statement is used to check integrity of specific constraints.

DBCC CHECKDB

This statement is used to check integrity and allocation of specific objects in a database. It also performs DBCC CHECKALLOC, DBCC CHECKTABLE and DBCC CHECKCATALOG in that particular order.

DBCC CHECKFILEGROUP

This statement is used to check allocation and structural integrity of tables.

DBCC CHECKIDENT

This statement is used to check identity value of specified table.

DBCC CHECKTABLE

This statement is used to check the integrity of a table and all the pages and structures which comprise the table. Both physical and logical checks are performed in this case. However, a PHYSICAL ONLY option can be used to check for physical consistency alone.

DBCC NEWALLOC

DBCC NEWALLOC is almost similar to DBCC CHECKALLOC. This statement is not supported by recent versions.

DBCC dllname (FREE)

This statement is used to unload a particular stored procedure DLL from memory.

DBCC HELP

This statement is used to return syntax information.

DBCC PINTABLE

This statement is used to mark a particular table to be pinned to SQL memory

DBCC ROWLOCK

This statement is used to enable Insert Row Locking (IRL) operations

DBCC TRACEOFF

This statement is used to disable a trace flag

DBCC TRACEON

This statement is used to turn on a specific trace flag

DBCC UNPINTABLE

This statement is used to mark a table as unpinned. In an unpinned table, the table pages in the cache could be easily removed.


Comments

Post a Comment

Popular posts from this blog

AlwaysOn Overview

-
Image
OVERVIEW An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to eight sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis. Each set of availability database is hosted by an availability replica. Two types of availability replicas exist: a single primary replica. which hosts the primary databases, and one to eight secondary replicas, each of which hosts a set of secondary databases and serves as a potential failover targets for the availability group. The primary replica makes the primary databases available for read-write connections from clients. Also, in a process known as data synchronization, which occurs at the database level. The primary replica sends transaction log records of each primary databa...
Read more

Database Developer vs Database Administrator

-
Image
Hi Geeks, Hope you're all doing good!! Have you ever asked someone or yourself to know who is a database developer and database administrator ? Or Have you ever been confused as to the difference between a DBA and a SQL Developer? Well, then you are not alone. There are number of blurred lines about exactly what is a DBA role, what is a SQL developer role. And thousands of people are still looking for the exact definitions. In this post, I will try my level best to clear all of your doubts about these two simple yet complicated things.  On a lighter note, The database administrator performs the day-to-day task(s) of maintaining the database environment to ensure its availability. Make sure it runs smoothly using database  tools to monitor, fix and maintain the database software, also the hardware on which it runs. On the other hand, the database developer designs and creates new ways of using a software  application. The database developer does this b...
Read more

Long Running queries

-
How do I find a long running query with process ID, process name , login time, user , start time and duration? Long running queries have been found in the plan cache. These may be ETL, reports, or other queries that should run long. Or it could be that someone is searching for all users whose names are LIKE '%%'. Either way, these queries bear investigating. In some cases, this is the total clock time that the query took to execute and in others this is the total CPU time that the query took to execute. Queries with a high max_elapsed_time just take a lot of time to run – they could be slow single threaded queries. Queries with a high max_worker_time (CPU time) may be highly parallel queries. CODE: SELECT  creation_time         ,last_execution_time         ,total_physical_reads         ,total_logical_reads         ,total_logi...
Read more