ALTER CREDENTIAL

Change properties of a credential.

Syntax
ALTER CREDENTIAL credential WITH IDENTITY = 'identity'
[, SECRET = 'secret']

A credential is a record of the authentication information required to connect to a resource outside SQL Server. Most credentials are a Windows username/password.

When IDENTITY is a Windows username, the SECRET can be the password. Secret is encrypted using the service master key.

Example

ALTER CREDENTIAL MyCredential WITH IDENTITY = 'JDoe';
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP CREDENTIAL

Remove a credential from the server.

Syntax
DROP CREDENTIAL credential_name

Requires ALTER ANY CREDENTIAL permission or to drop a system credential, CONTROL SERVER permission.

Example

DROP CREDENTIAL MyCredential;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE DATABASE

Create a new database, create database storage files, create snapshot, attach db to data files.

Syntax
Create database
CREATE DATABASE database
[ON [PRIMARY ]
[
filespec [,...n ]
[, filegroup [,...n] ]
[LOG ON {filespec [,...n] } ]
]
[COLLATE collation ]
[WITH external_access_option ]
] [;]

Attach a database
CREATE DATABASE database
ON filespec [ ,...n ]
FOR { ATTACH [ WITH service_broker_option ]
| ATTACH_REBUILD_LOG } [;]

Create a database snapshot
CREATE DATABASE snapshot
ON (
NAME = logical_file_name,
FILENAME = 'os_file_name'
) [,...n]
AS SNAPSHOT OF source_database_name [;]

Key:
filespec:
(
NAME = logical_file_name ,
FILENAME = 'os_file_name'
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
) [ ,...n ]


filegroup:
FILEGROUP filegroup [ DEFAULT ]
filespec [,...n]

external_access_option:
DB_CHAINING {ON | OFF}
TRUSTWORTHY {ON | OFF}

service_broker_option:
ENABLE_BROKER
NEW_BROKER
ERROR_BROKER_CONVERSATIONS

PRIMARY - Identifies the (at most one) <filespec> list which defines the primary file.

LOG ON - The disk files used to store the database log

FOR ATTACH - Create database by attaching an existing set of OS files.

FOR ATTACH_REBUILD_LOG - Create database by attaching an existing set of OS files and Rebuild any missing transaction log files.

DB_CHAINING - Allow cross-database ownership chaining.

TRUSTWORTHY - Allow database modules to access resources outside the database while using an impersonation context.

ENABLE_BROKER - Enable the Service Broker for the database.

NEW_BROKER - Create a new service_broker_guid, end all conversation endpoints with clean up.

ERROR_BROKER_CONVERSATIONS - End all conversations, re-enable when operation is completed.

Example

CREATE DATABASE SS64
ON
( NAME = SS64_dat,
FILENAME = 'E:\DATA\ss64database\SS64_data.mdf',
SIZE = 100MB,
MAXSIZE = 150MB,
FILEGROWTH = 25MB )
LOG ON
( NAME = SS64_log,
FILENAME = 'E:\DATA\ss64database\SS64_log.ldf',
SIZE = 50MB,
MAXSIZE = 75MB,
FILEGROWTH = 15MB )'
);
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER DATABASE

Modify a database, or the database files and filegroups.

Syntax
ALTER DATABASE database
ADD FILE filespec [ ,...n ]
[TO FILEGROUP {filegroup | DEFAULT} ] [;]

ALTER DATABASE database
ADD LOG FILE filespec [ ,...n ] [;]

ALTER DATABASE database
REMOVE FILE logical_file_name [;]

ALTER DATABASE database
MODIFY FILE filespec [;]

ALTER DATABASE database
ADD FILEGROUP filegroup [;]

ALTER DATABASE database
REMOVE FILEGROUP filegroup [;]

ALTER DATABASE database
MODIFY FILEGROUP filegroup [;]
{ filegroup_updatability_option
| DEFAULT
| NAME = new_filegroup
} [;]

ALTER DATABASE database
SET optionspec [ ,...n ] [ WITH termination ] [;]

ALTER DATABASE database
MODIFY NAME = new_database_name [;]

ALTER DATABASE database
COLLATE collation [;]


filespec:
(
NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB| % ] ]
[ , OFFLINE ]
)

filegroup_updatability_option:
READ_ONLY
READ_WRITE

optionspec:
ONLINE
OFFLINE
EMERGENCY

SINGLE_USER
RESTRICTED_USER
MULTI_USER

READ_ONLY
READ_WRITE

DB_CHAINING {ON | OFF}
TRUSTWORTHY {ON | OFF}

CURSOR_CLOSE_ON_COMMIT {ON | OFF}
CURSOR_DEFAULT {LOCAL | GLOBAL}

AUTO_CLOSE {ON | OFF}
AUTO_CREATE_STATISTICS {ON | OFF}
AUTO_SHRINK {ON | OFF}
AUTO_UPDATE_STATISTICS {ON | OFF}
AUTO_UPDATE_STATISTICS_ASYNC {ON | OFF}

ANSI_NULL_DEFAULT {ON | OFF}
ANSI_NULLS {ON | OFF}
ANSI_PADDING {ON | OFF}
ANSI_WARNINGS {ON | OFF}

ARITHABORT {ON | OFF}
CONCAT_NULL_YIELDS_NULL {ON | OFF}
NUMERIC_ROUNDABORT {ON | OFF}
QUOTED_IDENTIFIER {ON | OFF}
RECURSIVE_TRIGGERS {ON | OFF}

RECOVERY {FULL | BULK_LOGGED | SIMPLE}
TORN_PAGE_DETECTION {ON | OFF}
PAGE_VERIFY {CHECKSUM | TORN_PAGE_DETECTION | NONE}

PARTNER = 'partner_server'
PARTNER FAILOVER
PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
PARTNER OFF
PARTNER RESUME
PARTNER SAFETY {FULL | OFF}
PARTNER SUSPEND
PARTNER TIMEOUT integer
WITNESS = 'witness_server'
WITNESS OFF

DATE_CORRELATION_OPTIMIZATION {ON | OFF}

PARAMETERIZATION {SIMPLE | FORCED}

ENABLE_BROKER
DISABLE_BROKER
NEW_BROKER
ERROR_BROKER_CONVERSATIONS

ALLOW_SNAPSHOT_ISOLATION {ON | OFF}
READ_COMMITTED_SNAPSHOT {ON | OFF}

termination:
ROLLBACK AFTER integer [SECONDS]
ROLLBACK IMMEDIATE
NO_WAIT

Key:
MODIFY FILE Modify file location or properties, Only one <filespec> property
can be changed at a time.

FILEGROWTH Add new space in increments of x, MB, KB, GB, TB, or percent (%)
A value of 0 will set automatic growth to off.

filespec OFFLINE Set the file offline, make all objects in the filegroup inaccessible.
To set the file back online restore the file from a backup.

optionspec OFFLINE Close the database, clean shut down.

EMERGENCY Mark the database as READ_ONLY, disable logging, and restrict access
to members of the sysadmin fixed server role.

DB_CHAINING Database can be accessed by external resources (objects from another database)

AUTO_CLOSE Cleanly shut down the database when no users are connected,
this will free up its resources. Database mirroring requires AUTO_CLOSE OFF.

AUTO_SHRINK he database files are candidates for periodic shrinking.


Examples

-- Rename a database
USE master;
GO
ALTER DATABASE MyDatabase
MODIFY NAME = SalesDatabase;
GO

-- Move a file
USE master;
GO
ALTER DATABASE MyDatabase
MODIFY FILE ( NAME = MyData1, FILENAME = 'c:\demo\data_01.mdf' );
GO

-- Drop a file
USE master;
GO
ALTER DATABASE MyDatabase
REMOVE FILE MyData1;

-- Add a filegroup and 2 datafiles
USE master;
GO

ALTER DATABASE SS64
ADD FILEGROUP SS64FG1;

ALTER DATABASE SS64
ADD FILE
( NAME = SS64_dat2,
FILENAME = 'E:\DATA\ss64database\SS64_data2.mdf',
SIZE = 100MB,
MAXSIZE = 150MB,
FILEGROWTH = 25MB ),
( NAME = SS64_dat3,
FILENAME = 'E:\DATA\ss64database\SS64_data3.mdf',
SIZE = 100MB,
MAXSIZE = 150MB,
FILEGROWTH = 25MB )
)
TO FILEGROUP SS64FG1;
GO

-- Gain exclusive access, rollback all incomplete transactions.
USE master;
GO

ALTER DATABASE SS64
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP DATABASE

Remove a database from the server.

Syntax
DROP DATABASE database_name [ ,...n ] [;]

DROP DATABASE database_snapshot_name [ ,...n ] [;]

Key:
database_name
Name of the database. To display a list of databases, use the sys.databases catalog view.

database_snapshot_name
Name of a database snapshot to be removed.

The database context of the session runnning this command cannot be the same as the database about to be dropped.

You cannot drop a database if any users are currently reading or writing data, see ALTER DATABASE SINGLE_USER.

Before dropping the database: make a backup, drop any database snapshots and, if the database is involved in log shipping, remove log shipping.

Requires CONTROL permission on the database.

Example

DROP DATABASE MyDatabase;

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKALLOC - Check the consistency of disk space allocation.

Syntax
DBCC CHECKALLOC
[ ( 'database' | database_id | 0 [REPAIR_ALLOW_DATA_LOSS])
[WITH
{ [ ALL_ERRORMSGS ] [ , NO_INFOMSGS ] [ , TABLOCK ] [ , ESTIMATEONLY ]
}
]
]

Key:
REPAIR_FAST | REPAIR_REBUILD | NOINDEX - deprecated options
REPAIR_ALLOW_DATA_LOSS - Use only as a last resort
- back up the database before you run this option.

To find the repair level to use run DBCC CHECKDB without a repair option. The best and easiest way to repair errors is to restore from a backup.

Examples

-- Check disc space allocation for the current database.
DBCC CHECKALLOC;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKCATALOG - Check for catalog consistency

Syntax
DBCC CHECKCATALOG
[('database_name' | database_id | 0 )]
[WITH NO_INFOMSGS]

Key:
WITH NO_INFOMSGS - Suppresses all informational messages.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKCONSTRAINTS - Check the integrity of table constraints.

Syntax
DBCC CHECKCONSTRAINTS
[('table' | table_id | 'constraint' | constraint_id) ]
[WITH
[ { ALL_CONSTRAINTS | ALL_ERRORMSGS } ]
[ , ] [ NO_INFOMSGS ]
]
Key:
ALL_CONSTRAINTS - Check disabled constraints in addition to enabled constraints
has no effect when a constraint name is specified.
ALL_ERRORMSGS - Return all rows that violate constraints in the table being checked.
default = first 200 rows

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKDB - Check the allocation, and integrity of all objects in a database.

Syntax
DBCC CHECKDB
[( 'database' | database_id | 0
[ , NOINDEX
REPAIR_ALLOW_DATA_LOSS
REPAIR_FAST
REPAIR_REBUILD]
)]
[WITH
{
[ALL_ERRORMSGS ] [ , NO_INFOMSGS ] [ , TABLOCK ]
[, ESTIMATEONLY ] [ , { PHYSICAL_ONLY | DATA_PURITY } ]
}
]

Key:
NOINDEX - Skip intensive checks of nonclustered indexes for user tables
REPAIR_ALLOW_DATA_LOSS - Try to repair all reported errors.
REPAIR_REBUILD - Perform all repairs that can be performed without risk of data loss.
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
this limits the checks that are performed.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure
DATA_PURITY - Check the database for column values that are not valid or out-of-range.

Example

-- Check the current database.
DBCC CHECKDB;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKFILEGROUP - Check the allocation and structural integrity of all tables and indexed views in a filegroup.

Syntax
DBCC CHECKFILEGROUP
[( 'filegroup' | filegroup_id | 0
[, NOINDEX ]
)]
[WITH
[ALL_ERRORMSGS
NO_INFOMSGS]
[, TABLOCK ] [ , ESTIMATEONLY ]
]
Key:
filegroup_name - The name of the filegroup to be checked.
default (or if 0 is specified) = the primary filegroup.
NOINDEX - Skip intensive checks of nonclustered indexes.
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.

Examples

-- Check the primary filegroup in 'MyDatabase'
USE MyDatabase;
GO
DBCC CHECKFILEGROUP;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKIDENT - Check and/or reseed the current identity value for a table.

Syntax
DBCC CHECKIDENT
( 'table'
[ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
) [WITH NO_INFOMSGS]

Key:
NORESEED - The current identity value should not be changed.
RESEED - Change the identity value.
new_reseed_value - The new seed value to be used for the identity column.
WITH NO_INFOMSGS - Suppresses all information messages.

Example

-- Reset the current identity value
USE MyDatabase;
GO
DBCC CHECKIDENT ('MySchema.MyTable', RESEED, 5000);
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CHECKTABLE - Check the integrity of a table or indexed view.

Syntax
DBCC CHECKTABLE
('table' | 'view'
[ , { NOINDEX | index_id }
|, { REPAIR_ALLOW_DATA_LOSS | REPAIR_REBUILD }
]
)
[WITH
[ALL_ERRORMSGS
NO_INFOMSGS ]
[ , TABLOCK ] [ , ESTIMATEONLY ] [ , PHYSICAL_ONLY ]
]
Key:
NOINDEX - Skip intensive checks of nonclustered indexes.
REPAIR_ALLOW_DATA_LOSS - Try to repair all reported errors.
REPAIR_REBUILD - Perform all repairs that can be performed without risk of data loss.
REPAIR_FAST - deprecated option
ALL_ERRORMSGS - Return all reported errors per object, default = first 200 errors.
TABLOCK - Obtain locks instead of using an internal database snapshot.
ESTIMATEONLY - Display the estimated amount of tempdb space that would be required.
PHYSICAL_ONLY - Limits the checking to the integrity of the physical structure

To repair errors restore from a backup, use the REPAIR options only as a last resort.

Examples

-- Check the integrity of MyTable in 'MyDatabase'
USE MyDatabase;
GO
DBCC CHECKTABLE ('MySchema.MyTable')
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC CLEANTABLE

Reclaim space from dropped variable-length columns in a table or indexed view.

Syntax
DBCC CLEANTABLE
( { 'database' | database_id | 0 }
, { 'table' | table_id | 'view' | view_id }
[ , batch_size]
) [WITH NO_INFOMSGS]

Key:
batch_size - The no. of rows to process per transaction.
default (or if 0 is specified) = whole table
NO_INFOMSGS - Suppress all information messages.

If 0 is specified, the current database will be used.

Example

DBCC CLEANTABLE ('MyDatabase','MySchema.MyTable', 0)
WITH NO_INFOMSGS;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC dllname

Unload a DLL from memory.

Syntax
DBCC dllname (FREE) [ WITH NO_INFOMSGS ]


Key:
dllname - Name of the DLL to release from memory.
WITH NO_INFOMSGS - Suppress all information messages.


When an extended stored procedure is executed, the DLL will remain loaded until the server is shut down (or DBCC dllname is used to unload it)

Example

DBCC xp_my_stored_proc (FREE)

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC DROPCLEANBUFFERS

Remove all clean buffers from the buffer pool.

Syntax
DBCC DROPCLEANBUFFERS [WITH NO_INFOMSGS]


Key:
WITH NO_INFOMSGS - Suppress all information messages.


This command is useful to test queries with a cold buffer cache without shutting down and restarting the server.

To produce a 'cold' buffer cache with all dirty pages written to disk, first use CHECKPOIN

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC FREE... CACHE

DBCC FREEPROCCACHE - Remove all elements from the procedure cache.
DBCC FREESESSIONCACHE - Flush the distributed query connection cache.
DBCC FREESYSTEMCACHE - Release all unused cache entries from all caches.

Syntax
DBCC FREEPROCCACHE [WITH NO_INFOMSGS]

DBCC FREESESSIONCACHE [WITH NO_INFOMSGS]

DBCC FREESYSTEMCACHE
('ALL')
[WITH [MARK_IN_USE_FOR_REMOVAL] , [NO_INFOMSGS] ]

Key:
NO_INFOMSGS - Suppress all information messages
ALL - All supported caches
MARK_IN_USE_FOR_REMOVAL - Free up currently used entries asap (asynchronous)

MARK_IN_USE_FOR_REMOVAL will not prevent new entries being created in the cache.
Requires ALTER SERVER STATE permission on the server.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC HELP

Help and syntax information for DBCC commands.

Syntax
DBCC HELP ('dbcc_command') [WITH NO_INFOMSGS ]

DBCC HELP (@dbcc_command_var) [WITH NO_INFOMSGS ]

DBCC HELP ('?') [WITH NO_INFOMSGS ]

Key:
WITH NO_INFOMSGS - Suppress all information messages (severity levels 0-10)
dbcc_command - The DBCC 'xyz' command

Examples

DBCC HELP ('?')
GO

DBCC HELP ('USEROPTIONS')
GO

DECLARE @help_var sysname
SET @help_var = 'CHECKALLOC'
DBCC HELP (@help_var)
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC INPUTBUFFER / OUTPUTBUFFER

Display the last statement sent from a client to a database instance.

Syntax
DBCC INPUTBUFFER (session_id [, request_id ])
[WITH NO_INFOMSGS ]

DBCC OUTPUTBUFFER (session_id [, request_id ])
[WITH NO_INFOMSGS ]

Key:
session_id - The session ID
request_id - A specific request (batch) within the session.
NO_INFOMSGS - Suppress all information messages (severity 0-10)

To find the request_ids for a given session id (@@spid = current session):

SELECT request_id
FROM sys.dm_exec_requests
WHERE session_id = @@spid

Example

-- Display session # 52
DBCC INPUTBUFFER (52);
DBCC OUTPUTBUFFER (52);

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC OPENTRAN

Display information about the oldest active transaction and the oldest replicated transactions.

Syntax
DBCC OPENTRAN
[( [ 'database' | database_id | 0 ] ) ]
[WITH TABLERESULTS] [, [NO_INFOMSGS] ]
]

Key:
TABLERESULTS - Output in a tabular format that can be loaded into a table.
NO_INFOMSGS - Suppress all information messages (severity 0-10)

Example

BEGIN TRAN
...Insert/Update/Delete
GO
DBCC OPENTRAN;
ROLLBACK TRAN;

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DBCC PROCCACHE

Display information about the procedure cache (in table format)

Syntax
DBCC PROCCACHE [WITH NO_INFOMSGS]

Key:
NO_INFOMSGS - Suppress all information messages (severity 0-10)

All output figures are Totals:

proc cache size
Entries in the procedure cache.

proc cache used
Entries that are currently being used.

num proc buffs
Pages used by all entries in the procedure cache.

num proc buffs used
Pages used by all entries that are currently being used.

proc cache active / num proc buffs active
For backward compatibility only.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)