Tag: database

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 : 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: