 |
| Configuration Options |
|
 | 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 |
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.
- 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' GOProcessed 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_dbcmptlevelprocedure - 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
| 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.
