SS supports 3 types of dbs * System Dbs * Sample ,, * User Defined Dbs. Note: Don't install Sample dbs in production server. System Databases * These are mandatory dbs which consists of meta data of ther server. * We cannot configure the following features on system dbs. * Replication * Log Shipping * Db Mirroring * By default at the time of installation 5 system dbs are created. * Master * Model * MsDb * TempDb * Resource (Hidden db) * 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. Note: To detach MSDB what are the steps Sol: * Start server with single user mode with trace flag 3608 * Stop SS agent service. 1. Master --------- * It works as entry point for SQL Server. * Total server level settings are stored in master db. * 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. * Its recovery model is SIMPLE. * It consists of * All dbs details (sysdatabases) * Logins (SysLogins) * Linked servers information (sys.servers) * Error Messages (sysmessages) * Server level configuration settings etc FAQ:- How to move master db. Steps ------ 1. Stop server 2. Move the master db data file and TLog file into different location. 3. Provide read/write permissions on the destination folder to SQL Server service account. 4. Change the path in start up parameters. 5. Start the SQL Server service. * We need daily backups. 2. Model -------- * Works as template for new dbs. * It consists of system defined objects (1788/1978) which are copied into every new db. * No need of regular backups. FAQ:- Once I create a new db there should be one user ssrs_user. Sol: Create the user (ssrs_user) in model db. 3. MsDb ------- * Consists of total scheduling information. * Automation details are stored in this db. * It consists of the details * Backup and restore * Db Mail * Log Shipping * SSIS Packages * Maintenance plans * Jobs * Alerts * Operators etc * It consists of speacial roles related to SSIS and Db mail. * DatabaseMailUserRole * db_ssisAdmin * db_ssisltdUser * db_ssisoperator * Regular backups are required. 4. TempDb --------- * Consists of Temp objects like * Temp tables, cursors, resultsets of join * 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? 1. Short term fix ----------------- * Find long running query which is causing tempdb to grow and kill it by user acceptance. * Truncate the T.Log file of tempdb periodically by setting the recovery model to simple. 2. Long Term Fix ---------------- * Restart the server by raising CM with client. 5. Resource ----------- * Consists of all system defined objects physically. * It maintains the service packs changes. * By using old data file and T.Log file of this db we can rollback any changes (sps). * This db files are present in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn 6. Distribution --------------- * It is create automatically when the instance is qualified as distributor in replication feature. * It consists of complete replication details. Note: If we install Reporting Services then 2 more dbs are created automatically * ReportServer * ReportServerTempdb
Monday, January 17, 2011
System Databases in SQL Server
* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...