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')
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
-To
view free space present in data file
USE
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.
- 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
-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
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