Category: SQL Server

SQL Server : Database Console Commands (DBCC)

Here are some examples of Database Console Commands (DBCC).

-- Like flush of buffer cache.
dbcc DropCleanBuffers


-- Shrink all files in the database.
-- Parameters: Database name and percentage to leave free.
-- *** You should probably never do this! ***
dbcc ShrinkDatabase(N'MyDatabase', 10)


-- List files.
sp_helpfile


-- Shrink a specific file.
-- Parameters: File name and final size of file in MB.
-- Shrink will not reduce size below the contents of the files. There is no loss of data possible.
-- Remember, shrinks are moving pages around, so they can cause index fragmentation.
-- After a large shrink you should consider index rebuilds.
-- *** You should probably never do this! ***
dbcc ShrinkFile(N'mastlog', 5)

-- Shrink a log file for a database in FULL recovery mode.
-- This is something quite drastic, so don't do it on a regular basis.
-- The size of the log files should be controlled by frequent transaction log backups.
USE MyDatabase
ALTER DATABASE MyDatabase
SET RECOVERY SIMPLE
GO
DBCC SHRINKFILE (N'MyDatabase_log', 100)
GO
ALTER DATABASE MyDatabase
SET RECOVERY FULL
-- Do a full backup.
-- Like flush shared pool.
dbcc FreeProcCache

-- Shrink tempdb
USE [tempdb]
GO
DBCC SHRINKDATABASE(N'tempdb' )
GO 
-- Check whole database. Two methods.
dbcc CheckDB(N'MyDatabase')

use MyDatabase
dbcc CheckDB


-- Repair: Message from check will instruct correct mode.
alter database MyDatabase set single_user
dbcc CheckDB(N'MyDatabase', repair_allow_data_loss)
alter database MyDatabase set multi_user


-- Check individual table.
use MyDatabase
dbcc CheckTable(employees)


-- Check the size of the logs and tht % used.
-- May want to shrink if they are very big and empty from one-off operation.
dbcc SQLPerf(logspace)


-- Last query ran.
-- Check for blocking sessions in blocked column.
-- Use InputBuffer against the blocked and blocking SPID to see the last statement they ran.
SELECT * FROM sys.sysprocesses

dbcc InputBuffer(spid)
dbcc InputBuffer(8)


-- Which commands can you get help on?
dbcc help ('?')


-- Get help on a specific DBCC command.
dbcc help ('cleantable')

For more information see:

SQL Server : Authentication Modes

Not surprisingly, Windows authentication mode allows you to connect to the database using Windows authentication. By default SQL Server will only allow Windows authentication mode. SQL Server authentication mode allows you to connect to the database using an username and password.

To allow both modes of authentication do the following.

  • Right-click on the server and pick the “Properties” menu option.
  • Click on the “Security” page.
  • Pick the “SQL Server and Windows Authentication mode” setting.
  • Click the “OK” button.

You will need to restart SQL Server for the change to take effect.

For more information see:

SQL Server : sp_configure

Put “sp_configure” into a query window and execute and you get all configurable options.
Some are modifiable. Some require restart.

  • config_value : The value that will be used at next restart.
  • run_value: Currently used value.

If you change a setting and the run_value <> config_value, it means you need a restart or reconfigure for it to take effect.

The basic usage is as follows.

  • sp_configure : Display all options and values.
  • sp_configure “option name” : Displays only the requested option details.
  • sp_configure “option name”, value : Sets the named option to the specified value.

The number of visible options is limited by the “show advanced options” option. The output below shows the

sp_configure "show advanced options"

name                                    minimum     maximum config_value   run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options                         0           1            0           0

Set the value to “1” and show the output again. We can see the config value is set, but the run value isn’t.

sp_configure "show advanced options", 1

sp_configure "show advanced options"

name                                    minimum     maximum config_value   run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options                         0           1            1           0

Reconfigure to make the run_value and config_value match.

reconfigure
sp_configure "show advanced options"

name                                    minimum     maximum config_value   run_value
----------------------------------- ----------- ----------- ------------ -----------
show advanced options                         0           1            1           1

Now sp_configure displays lots more options.

Remember, you can only change options you can display!

The normal process us as follows.

  • Turn on advanced options.
  • Make your change.
  • Turn off advanced options.

For example.

sp_configure "show advanced options", 1
reconfigure

sp_configure "an advanced option", value
reconfigure

sp_configure "show advanced options", 0
reconfigure

For more information see:

SQL Server : Partitioning

Partitioning is an Enterprise Edition only feature, but all tables are actually partitioned. A regular table is a table with a single partition.

Create three file groups.

USE MyDatabase;
GO

ALTER DATABASE MyDatabase ADD FILEGROUP part_test_1_fg;
GO

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'part_test_1_df',
FILENAME = N'e:\db\part_test_1_df.ndf',
SIZE = 10MB,
FILEGROWTH = 1GB)
TO FILEGROUP part_test_1_fg
GO


ALTER DATABASE MyDatabase ADD FILEGROUP part_test_2_fg;
GO

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'part_test_2_df',
FILENAME = N'e:\db\part_test_2_df.ndf',
SIZE = 10MB,
FILEGROWTH = 1GB)
TO FILEGROUP part_test_2_fg
GO


ALTER DATABASE MyDatabase ADD FILEGROUP part_test_3_fg;
GO

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'part_test_3_df',
FILENAME = N'e:\db\part_test_3_df.ndf',
SIZE = 10MB,
FILEGROWTH = 1GB)
TO FILEGROUP part_test_3_fg
GO

To partition a table we need a partition function. When defining a partition function we can use RANGE LEFT or RANGE RIGHT in the partition function.

  • RANGE LEFT: Value represents right hand boundary.
  • RANGE RIGHT: Value represents left hand boundary.

As an example, check out this partition function.

CREATE PARTITION FUNCTION myRangePF1 (int)
 AS RANGE LEFT FOR VALUES (1000, 2000) ;
 GO

This means the following.

  • 0-1000 : Partition 1
  • 1001-2000 : Partition 2
  • 2001- : Partition 3

Create a partition scheme using the partition function.

CREATE PARTITION SCHEME myRangePS1
 AS PARTITION myRangePF1
 TO (part_test_1_fg, part_test_2_fg, part_test_3_fg) ;
 GO

Create a table using the partition scheme.

CREATE TABLE MyPartitionTable (
 id int PRIMARY KEY,
 col2 varchar(50)
 )
 ON myRangePS1 (col1) ;
 GO

It’s possible to switch a real table with a partition (partition exchange). In this example partition 3 in the partitioned table is swapped with the regular table (partition 1).

ALTER TABLE schema.part_table
 SWITCH PARTITION 3
 TO schema.normal_table PARTITION 1

The table definitions have to match (structure, keys etc.). Tables have to both be in the same file group.

For more information see:

SQL Server : Compression

The available types of compression are listed below.

  • Row-level Compression : Turns fixed width columns into variable width columns, so space padding is saved. Doesn’t store NULL or 0.
  • Prefix Compression : Looks for repeated patterns in the start of strings.
  • Dictionary Compression : Looks for repeated patterns throughout the page.
  • Page-level Compression : Does row-level compression, then adds prefix and dictionary compression.

You can estimate the benefits of compression using the following commands.

-- Syntax
sp_estimate_data_compression_savings 'schema', 'table', NULL, NULL, 'ROW';
sp_estimate_data_compression_savings 'schema', 'table', NULL, NULL, 'PAGE';

-- Example
sp_estimate_data_compression_savings 'sdowner', 'SDR_ACTIVITY_SETS', NULL, NULL, 'ROW';
sp_estimate_data_compression_savings 'sdowner', 'SDR_ACTIVITY_SETS', NULL, NULL, 'PAGE';

Here are some examples of compressing tables and partitions.

-- Table
ALTER TABLE schema.partition REBUILD
WITH
( DATA_COMPRESSION = row -- none, row, page, columnstore, columnstore_archive
)

-- All partitions.
ALTER TABLE schema.partition REBUILD PARTITION = ALL
WITH
( DATA_COMPRESSION = row -- none, row, page, columnstore, columnstore_archive
)

-- Individual partition.
ALTER TABLE schema.partition REBUILD PARTITION = partition-number
WITH
( DATA_COMPRESSION = row -- none, row, page, columnstore, columnstore_archive
)

For more information see:

SQL Server : Basic Database Configuration

AutoClose

Never enable AutoClose. According to the documentation, “If TRUE, the database is closed, and its resources are freed when no user connection accesses the database.” It’s all opened again when the next user connects. This feature is being removed in future releases.

Check the AutoClose status of the databases.

SELECT name, is_auto_close_on FROM sys.databases

Turn it off for any database where it is turned on.

USE master
GO

ALTER DATABASE MyDatabase SET AUTO_CLOSE OFF

AutoShrink

Never enable AutoShrink. When enabled the server will occasionally check the data files and attempt to release unused space. It represents a was of resources and will be removed form a future release.

Check the AutoShrink status of the databases.

SELECT name, is_auto_shrink_on FROM sys.databases

Turn it off for any database where it is turned on.

USE master
GO

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF

Recovery Model

The recovery model determines how transaction logs are managed. For most important databases you will want to use FULL, allowing point in time recovery.

Check the recovery model for all databases.

SELECT name, recovery_model, recovery_model_desc FROM sys.databases

Set the recovery model to the required level.

USE master
GO

ALTER DATABASE MyDatabase SET RECOVERY SIMPLE
ALTER DATABASE MyDatabase SET RECOVERY BULK_LOGGED
ALTER DATABASE MyDatabase SET RECOVERY FULL

Statistics Collection

Statistics are stale when a sufficient number or percentage of rows of the data have changed. The amounts vary by version, so double check if you want to be accurate. You can leave SQL Server to automatically collect statistics. Statistics collection can be done in synchronous or asynchronous mode.

  • Synchronous – Queries wait for stats collection to be completed.
  • Asynchonous – Allows queries to continue with old stats until the new stats are avaiable.

Typically you will want to use asynchronous statistics gathering.

Check the current statistics settings for all databases.

SELECT name,
       is_auto_create_stats_on,
       is_auto_update_stats_on,
       is_auto_update_stats_async_on
FROM   sys.databases

Amend the statistics settings.

USE master
GO

ALTER DATABASE MyDatabase SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON

Generate the commands to alter the statistics settings for all user-defined databases.

SELECT 'ALTER DATABASE ' + name + ' SET AUTO_UPDATE_STATISTICS_ASYNC ON'
FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')

You can also manually gather statistics for tables, indexes and views using the UPDATE STATISTICS command.

UPDATE STATISTICS mytable

Maintenance Mode

Set the desired maintenance mode.

ALTER DATABASE MyDatabase SET multi_user (normal state)
ALTER DATABASE MyDatabase SET single_user
ALTER DATABASE MyDatabase SET restricted_user

Parallelism

Setting the ‘max degree of parallelism’ (MAXDOP) is dependent on a number of factors. Check out this post.

The default value for the ‘cost threshold for parallelism’ is 5, which most people consider is too low. You will see recommendations of 50 as a reasonable starting point.

exec sp_configure "show advanced options", 1
reconfigure

exec sp_configure "cost threshold for parallelism", 50
reconfigure

exec sp_configure "show advanced options", 0
reconfigure

For more information see:

SQL Server : Moving Database Files

Alter the metadata about the file.

USE master
GO

ALTER DATABASE MyDatabase
MODIFY FILE (
  NAME = N'MyNewDatafile',
  FILENAME = N'e:\db\MyNewDatafile.ndf'
)
GO

Take the database offline, move the physical file and bring the database online again.

ALTER DATABASE MyDatabase SET OFFLINE

-- Move the physical file.

ALTER DATABASE MyDatabase SET ONLINE

SQL Server : File Groups

File groups are similar to Oracle tablespaces. They are a way to group files and separate
load. This allows you to spread storage across a variety of LUNs and perform backup/recovery at file group level.

  • Datafiles: Equal fill out algorithm. All files in a file group are equally full.
  • Logfiles: Logs fill up one at a time. As a result, can’t use file groups.

Create a new file group.

USE master
GO

ALTER DATABASE MyDatabase ADD FILEGROUP MyFileGroup
GO

Add a new datafile to an existing file group.

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'MyNewDatafile',
          FILENAME = N'e:\db\MyNewDatafile.ndf',
          SIZE = 10MB,
          FILEGROWTH = 1GB)
TO FILEGROUP MyFileGroup
GO

Display the datafiles and file groups.

USE MyDatabase
GO

SELECT * from sys.database_files
SELECT * FROM sys.data_spaces

SELECT ds.name, df.name, df.physical_name
FROM sys.database_files df
 JOIN sys.data_spaces ds ON ds.data_space_id = df.data_space_id

SELECT * FROM sys.master_files

Create a table in the file group.

CREATE TABLE employees2
(
 employee_id INT IDENTITY(1,1) PRIMARY KEY,
 first_name VARCHAR(30),
 middle_names VARCHAR(100),
 last_name VARCHAR(30),
 date_of_birth DATE
)
ON MyFileGroup
GO

For more information see:

SQL Server : Detach/Attach Database

You might want to detach a database because you want to move it to a new server.

Detaching a database causes you to lose all the metadata about that database. Detach an existing database using the following command.

USE master
GO

sp_detach_db 'database-name', 'true';
GO

Attach a detached database. This could be a database you just detached on the same server, or one you’ve recently copied from another server.

USE master

GO


CREATE DATABASE database-name
ON (FILENAME = 'C:\path\to\file.mdf')
FOR ATTACH;

Check the databases present using the following query.

SELECT name FROM sys.databases;

To detach a database using the GUI do the following.

  • Right-click on database.
  • Select “Tasks > Detach”

For more information see:

SQL Server

SQL Server related posts on this site.

Server Administration

Security

Creating objects.