Databases(system/user)

WORKING WITH DATABASE

SQL Server supports 3 types of databases.
  • System Dbs
  • Sample Dbs (AdventureWorks, AdventureWorksDW)
  • User Defined dbs
System Dbs
These are mandatory dbs.
At the time of installation 5 system dbs are installed.
  • Master
  • Model
  • Msdb
  • TempDb
  • Resource (Hidden)
-These are mandatory dbs which consists of meta data of their server.
-Once we configure Replication automatically "distribution" db is created
-We cannot configure the following features on system dbs.
  • Replication
  • Log Shipping
  • Db Mirroring
  • Distribution db is created in distributor.
Note:
Don't install Sample dbs in production server.

Note:
Once we install Reporting Services automatically the following 2 dbs are created
          -ReportServer
          -ReportServer Tempdb

-We cannot detach the system dbs directly.
-To detach system dbs the server should be running in single user mode with trace flag 3608
 c:\Net start mssqlserver /m /c /T3608

-Only Model and MSDB can be detached.

To detach MSDB what are the steps

  * Start server with single user mode with trace flag 3608
  * Stop SS agent service.

Master
* It act as an entry point for SQL Server.
* It consists of all system level information.
* It consists of
          * Server level settings
          * System defined error messages   (sysmessages)
          * Linked server's information.(sys.servers)
          * Other db details. (sysdatabases)

* Its recovery model is SIMPLE.
* We cannot detach master db.
* To restore master db we have to run the server in single user mode.
* Its db id is 1.
* By default guest user is enabled.
* daily full backup
* Master db data file and transaction log files are 2 startup parameters of SS.
  (ErrorLog is one more startup parameter)

Model
               
* It acts as a template for new dbs.
* It consists of system defined objects which are copied into every new db.
* It consists of 1788 (1978 in 2008 version) system defined objects.

FAQ:- Once I create a new db there should be Emp table automatically.
Ans:    Create Emp table in Model db.

MsDb

-Consists of total automation information.
-Consists of details of
  • Backups
  • Restores
  • Maintenance Plans
  • Jobs
  • Alerts
  • Operators
  • Db Mail details
  • Log Shipping Details etc.,
-SQL Server agent periodically monitor MsDb and it implement automation according to the schedules present in MsDb.
-It consists of special roles related to SSIS and Db mail.
  • DatabaseMailUserRole
  • db_ssisAdmin
  • db_ssisltdUser
  • db_ssisoperator
  • Regular backups are required.
FAQ:- Automation Fails. What are the reasons?
          * If MSDb goes into suspect mode.
          * If agent is stopped.

TempDB
-It consists of all temporary objects like
  •  Temp tables
  •  Temp S.P
  •  Hash tables
  •  The result of a join
  •  Group by , Order by clause works on tempdb db
  •  Cursors
-It is cleared once we restart SQL Server.

-Consists of Temp objects like
  • The process of ordering, grouping is done here.
  • DBCC Checkdb(Dbname) utilizes more tempdb
  • Rebuilding indexes consumes more space in tempdb.
-No need of backups.
-It is created/cleared when the server is started.
-Add similar no of data files similar to no of CPUs in the server.
-Place tempdb files in highest rpm disk.

FAQ:- TempDb is growing fastly. How u know which transaction is causing the problem?
Sol:
 DBCC OPENTRAN -- or DBCC OPENTRAN('tempdb')

FAQ:- If Tempdb full. How to resolve the issue?

5. Resource
--------------
* One of a hidden database.
* It consists of all system defined objects physically.
* We cannot work on resource db.
* Its data file and log files are present in
          ..........\Data  (2005)
          ..........\Binn  (2008)
* It maintains the service packs changes.
* By using old data file and T.Log file of this db we can rollback any changes (sps).

6. Distribution
-----------------
* It is created once we configure Replication in Distributor.
* SS uses InstDist.sql file present in Install folder to create this db.
* It is create automatically when the instance is qualified as distributor in replication feature.
* It consists of complete replication details.

7.ReportServer
-------------------
Primary database for reporting services to store the meta data and object definitions

Prominent Functionality:
          Reports security
          Job schedules and running jobs
          Report notifications
          Report execution history

8.ReportServerTempdb
----------------------------
Temporary storage for Replication services

Prominent Functionality:
          Session information
          Cache

Resource Database
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Physical Properties of Resource
The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL12.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.

Backing Up and Restoring the Resource Database

SQL Server cannot back up the Resource database. You can perform your own file-based or a disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file, rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful not to overwrite the current Resource database with an out-of-date or potentially insecure version.

User Defined DB's

-Created by the developer and consists of 1788 (2005)
                                                           1978 (2008) system defined objects.
-Every db is
          * Logically collection of objects
          * Physically collection of files.

-Every db consists of 2 types of files
  • Data Files
  • Transaction Log Files
-Default name of data file is = dbName
-Default name of log  file is = dbName_log
-Every file consists of
  • Logical Name
  • Physical Name

1. Filegroup
-To group similar data files logically.
-By default every db consists of PRIMARY Filegroup.
-We can add our own filegroups.
-Filegroups are provided unique ids start from 1.

          use AdventureWorks2012<databasename>
          GO
          select * from sys.filegroups
Advantages

          -Better performance.
          -To take backup of more than one file at a time.
          -To implement data partitioning
          -To store a table data into required file.

2. Data Files
-Consists of actual data, meta data and services.
-No limit for no of data files.
-Max size for a data file : 16TB
-We can view the details of files of a db
          SP_HELPDB AdventureWorks2012

-To view free space present in data file
          USE AdventureWorks2012
          GO
          DBCC SHOWFILESTATS

-SS supports 2 types of data files
          i   Primary Data File (*.mdf)
          ii  Secondary Data File (*.ndf)
          iii log file  Data File (*.ldf)

1. Primary Data file (*.mdf)

The Primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file.  The recommended file name extension for primary data file is .mdf
-It consists of complete meta data of a db.
-It consists of
          -System defined objects.
          -Permissions granted on various objects.
          -Details of other files.
-Only ONE P. Data file per database.

2. Secondary Data File (*.ndf)

Secondary data file make up all the data files, other than the primary data file.  Some db may not have any secondary data file, while others have several secondary data files.  The recommended file
name extension for Secondary data file is .ndf

* Consists of data and user defined objects.
* No limit for no of .ndf files in a db.

3. Transaction Log File (*.ldf)

Log files hold all the log information that is used to recover the database. 
There must be at least one log file for each database. Although there can be more than one. 
The recommended file name extension for log file is .ldf
  • It consists of both committed as well as un committed transactions.
  • Once checkpoint occurs all the committed transactins are taken into data file.
  • Max size: 2TB.
  • To view the percent log used
          DBCC SQLPERF(LOGSPACE)

To view the transaction present in log file
          DBCC LOG(dbName)
          or
          DBCC LOG(dbName,flag) 1-4

Consists of no pages. Every transaction is recorded with a unique no called LSN.

Advantages
  • To reduce I/O transactions on data file.
  • To undo the transactions
  • To recover data upto point of failure.
  • To implement T.Log shipping, Transactional replication.
Database States
  • ONLINE
  • OFFLINE
  • STANDBY
  • RESTORING...
  • SUSPECT
  • SINGLE USER

Following are main database status: (Reference: BOL Database Status)

ONLINE
Database is available for access.

OFFLINE
Database is unavailable.

RESTORING
One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline.

RECOVERING
Database is being recovered.

RECOVERY PENDING
SQL Server has encountered a resource-related error during recovery.

SUSPECT
At least the primary filegroup is suspect and may be damaged.

EMERGENCY
User has changed the database and set the status to EMERGENCY.


Let us see how we can find out database status using this sys.databases and DATABASEPROPERTYEX.

1) Using T-SQL (My Recommendation)

Example:
SELECT DATABASEPROPERTYEX('AdventureWorks2012', 'Status')
DatabaseStatus_DATABASEPROPERTYEX
GO
SELECT state_desc DatabaseStatus_sysDatabase
FROM sys.databases
WHERE name = 'AdventureWorks2012'
GO

ResultSet:
DatabaseStatus_DATABASEPROPERTYEX
——————————————————
ONLINE

DatabaseStatus_sysDatabase
——————————————————
ONLINE



Moving Master db
-Create two folders and grant read write permissions to service account
          d:\master_data
          e:\master_log
-Find the current path
          sp_helpdb master
-Stop SQL Server
          net stop mssqlserver
-Move the files into new folders
-Go to SSCM --> R.C on respective instance SQL Server Service --> properties -->   Advanced --> Startup Parameters--> Change the path of data and Log file
          -dd:\master_data\master.mdf;
          -le:\master_log\mastlog.ldf
-Apply --> OK
-Start the service.
-Go to SSMS --> check the new path
          sp_helpdb master

Moving MSDB files

-Create two folders and grant read write permissions to the service account.
          d:\msdb_data
          e:\msdb_log
-Stop SQL Server service
          c:\>net stop mssqlserver
          c:\>net start mssqlserver /m /T3608
-Check the current path
          sp_helpdb msdb
-Detach the database
          sp_detach_db msdb
-Move the msdb files into new location
-Attach the files
          use master
          go
          sp_attach_db @dbname='msdb',
                          @filename1='d:\msdb_data\msdbdata.mdf',
                           @filename2='e:\msdb_log\msdblog.ldf'
-Check the current location
          sp_helpdb msdb
-Stop server and start in multi user mode
          net stop mssqlserver
          net start mssqlserver

Moving Tempdb

-We can move tempdb files just by altering the data and T.Log file locations and no need   to start server in multi user mode
-Steps
          -Create two folders
                   d:\tempdb_data
                   e:\tempdb_log
          -Grant read write permissions on the folders to service account
          -Change the file paths
                   use master
                   go
                   ALTER database tempdb modify file
                   (
                   name='tempdev',
                   filename='d:\tempdb_data\tempdb.mdf'
                   )
                  
                   use master
                   go
                   ALTER database tempdb modify file
                   (
                   name='templog',
                   filename='d:\tempdb_data\templog.ldf'
                   )
         
          -Check now new path
                   sp_helpdb tempdb
          -Stop server after some times (when required)

          -Start instance and check that files are created in new path




FAQ:- Reason for suspect mode
If one or more database files are not available.
If the entire database is not available.
If one or more database files are corrupted.
If a database resource is being held by the operating system.
If we are restoring a db and restoration was not completed

How to recover?
Scenario 1: If the file is full
method1:
steps

1. sp_resetstatus database_name
2. ALTER DATABASE to add a data file or log file to the database.
3. Stop and restart SQL Server.

Method2:
EXEC sp_resetstatus 'yourDBname';
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb('yourDBname')
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('yourDBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER


Troubleshooting Insufficient Disk Space in tempdb
tempdb Space Requirements


The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

Diagnosing tempdb Disk Space Problems
The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

Error
Is raised when
1101 or 1105
Any session must allocate space in tempdb.
3959
The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967
The version store is forced to shrink because tempdb is full.
3958 or 3966
A transaction cannot find the required version record in tempdb.
tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.


Monitoring tempdb Disk Space


The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.
Determining the Amount of Free Space in tempdb
The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

No comments:

Post a Comment

Popular Posts