SQL Server: Database Configuration Options


Configuration Options
  1. Autogrowth
  2. Recovery Model
  3. Compatibility Level
  4. Auto Shrink
  5. Restrict Access


Autogrowth - Property of each database file.
  • Control how the file expands (FILEGROWTH clause).
  • By default, each file assume values set in the model database.
  • Configure automatic growth rate (percent or fixed-size)
  • Configure maximum file size. Once reached, database goes into read-only mode.
  • Full database generates Event ID 3758.
  • Can be checked with sp_dbhelp, sp_dbhelp dbnamed, sp_helpfile filename
  • If all files have a maxsize, the database has a maxsize.


Recovery model
Determines how the transaction log is used: how transactions are logged, whether the transaction log is backed up, and what restore options are available.
Three values: { SIMPLE | FULL | BULK-LOGGED }


SIMPLE recovery model
  • Minimal logging.
  • Minimizes administrative overhead for the Transaction Log (TL).
  • Backups may need to be performed more often.
  • Here the use of differential backups can help reduce the backup overhead.
  • TL truncated after each checkpoint. During a checkpoint the dirty pages are written from bugger memory to the data file and the TL is truncated.
  • Transaction logs(TL) cannot be backed up or restored.
  • You also cannot restore individual data pages.
  • Recovery possible only to the end of the last backup.
When to use simple recovery model?
  • test environments, development, read-only production scenarios.

BULK-LOGGED recovery model
  • Minimal logging performed for bulk actions: Logs contain only the information that a bulk operation has occurred, but not the data changes incurred with such operation.
  • These include:
    • SELECT INTO,
    • Some INSERT INTO that use SELECT statement (OPENROWSET (bulk...). TABLOCK hint)
    • BULK INSERT operations,
    • Write actions performed by the BCP program
    • UPDATE with WRITE clause
    • CREATE, ALTER or DROP INDEX
  • Intended as temporary to be used when large bulk operations need to be performed in the database.

Restore possible?
  • In Bulk-Logged mode, a backup of the transaction logs also includes all data extents marked as updated by a bulk operation. This guarantees that a restore can be performed, even though not all necessary change information is available in the transaction logs.
  • Point-in-time recovery: possible ONLY if no bulk action have occurred since the last full backup.

why use Bulk-logged model?
  • Allows for high-performance bulk copy operations.
  • If many or large bulk operations are to be performed, this configuration minimizes the use of transaction logs (improving performance with less write operations) while still guaranteeing data availability if a restore is needed.

FULL recovery model
  • Full logging.
  • Logs never truncated.
  • Can be used to recover to an arbitrary point in time.

To view the Recovery model:

(a) Use the Object Explorer on SMSS. Select database; right-click and choose database properties.
(b) Use sp_helpdb [dbname]
(c) Query sys.databases
USE MASTER;
GO
SELECT name, recovery_model_desc 
FROM sys.databases;
name recovery_model_desc ------- -------------- master SIMPLE tempdb SIMPLE model FULL msdb SIMPLE TESTSQL FULL MyDB FULL

(d) To view the recovery mode of the available backup sets use msdb.dbo.backupset catalog table

i.e Change the recovery model of TESTSQL database to perform bulk copy operation:
-- (1) Backup database
BACKUP DATABASE TESTSQL TO DISK='C:\mssqlbackup\TESTSQL.bak'
GO
Processed 304 pages for database 'TESTSQL', file 'TESTSQL' on file 1. Processed 3 pages for database 'TESTSQL', file 'TESTSQL' on file 1. BACKUP DATABASE successfully processed 307 pages in 0.397 seconds (6.022 MB/sec).
-- (2) Change recovery model to BULK_LOGGED
USE MASTER;
GO
SELECT name, recovery_model_desc FROM sys.databases;
name recovery_model_desc ------- -------------- master SIMPLE tempdb SIMPLE model FULL msdb SIMPLE TESTSQL BULK_LOGGED MyDB FULL
--(3) Perform BULK operation

--(4) Change recovery model back to FULL
ALTER DATABASE TESTSQL SET recovery FULL

--(5) Backup database to include new data
BACKUP DATABASE TESTSQL TO DISK='C:\mssqlbackup\testsql.bak'
GO

Compatibility Level.
  • Database-specific.
  • Provides only partial backward compatibility.
  • Default 100.
  • ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }
  • ALTER DATABASE Replaces sp_dbcmptlevel procedure
  • Do not change compatibility level while users are connected to the database.
  • (changes may affect query plan compilation)
  • Set database to single-user access mode before altering compatibility level.
  • i.e. ALTER DATABASE SET SINGLE_USER
Further reading and examples:
  • Books online





  • Database Size: Auto Shrink
    • Available, but perhaps not a good idea to use.
    • Its disk and processor intensive: file reorganization.
    • Just don't: Auto_shrink will increase Index fragmentation enormously.
    • If you need to shrink, do it manually (DBCC SHRINKFILE and DBCC SHRINKDATABASE).
    • When AUTO_SHRINK is on, both data and log files can be automatically shrunk. Files are shrunk when more than 25% of the file contains free space. Files are then shrunk to a size where 25% of the file is unused.
    • Check a good discussion here.
    -- To set AUTO_SHRINK on|off: 
    ALTER DATABASE SET AUTO_SHRINK { ON | OFF }
    
    To check the status of AUTO_SHRINK:
    SELECT name, is_auto_shrink_on  FROM sys.databases;
    go
    
    name       is_auto_shrink_on
    master     0
    tempdb     0
    model      0
    msdb       0
    TESTSQL    0
    
    alter database TESTSQL
       set auto_shrink ON;
    go
    
    select name, is_auto_shrink_on
    from sys.databases;
    go
    
    name       is_auto_shrink_on
    master     0
    tempdb     0
    model      0
    msdb       0
    TESTSQL    1
    

    Restrict Access.

    Specify which users may access the database.
    SINGLE_USER
    • Used for maintenance actions.
    • Any single user may connect, but only one user is accepted at a time.
    • To switch to single_user mode when other sessions are active, you need to disconnect them first.
    -- The command below allow 6 minutes for current sessions to complete. 
    -- After this any transaction will be rolled back: 
    
     ALTER DATABASE Sales
       SET SIGLE_USER
       WITH ROLLBACK AFTER 360;
    

    MULTI_USER
    • Default and normal state for production.
    • Allows multiple user access.

    RESTRICTED
    • Only members of the db_owner, dbcreator, or sysadmin roles.







    No comments:

    Post a Comment