SQL Server Database Administrator Questions & Answers
Q. How to Rebuild System databases?
A: System databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases or to modify the default server-level collation.
The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system databases to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.
This procedure does not rebuild the resource database.
This is very critical task especially if you are performing on Production Server, so ensure you list out all the pre/post steps. Follow the below steps to rebuild system databases:
1. Run Command Prompt as Administrator
1.1. From a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.
2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges.
Syntax:
If using Windows Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=DOMAIN\user_name [ /SQLCOLLATION=CollationName]
If using SQL Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Example:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=sa /SAPWD= ******
3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.
Post Steps: After rebuilding the database you may need to perform the following additional tasks:
1. Restore your most recent full backups of the master, model, and msdb databases. For more information, see Back Up and Restore of System Databases (SQL Server).
Note: If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.
If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them.
2. If the instance of SQL Server is configured as a replication Distributor, you must restore the distribution database. For more information, see Back Up and Restore Replicated Databases.
3. Move the system databases to the locations you recorded previously. For more information, see Move System Databases.
4. Verify the server-wide configuration values match the values you recorded previously.
Q. How to Rebuild Resource Database in SQL Server?
A: The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
1. Launch the SQL Server Setup program (setup.exe) from the distribution media.
2. In the left navigation area, click Maintenance, and then click Repair.
3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
4. On the Select Instance page, select the instance to repair, and then click Next.
5. The repair rules will run to validate the operation. To continue, click Next.
5. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
Q. If the msdb database is damaged and you do not have a backup of the msdb database, can you to create new msdb database in SQL Server?
A: Yes, you can create new msdb by using the instmsdb script. Follow the following steps:
Note:Rebuilding the msdb database using the instmsdb script will eliminate all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.
1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.
2. Start SQL Server from the command line using the command:
NET START MSSQLSERVER /T3608
3. In another command line window, detach the msdb database by executing the following command, replacing with the instance of SQL Server:
SQLCMD -E -S -dmaster -Q"EXEC sp_detach_db msdb"
4. Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance. 5. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally. 6. In a command line window, connect to SQL Server and execute the command:
SQLCMD -E -S -i"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o" C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install\instmsdb.out"
Replace with the instance of the Database Engine. Use the file system path of the instance of SQL Server.
7. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
8. Re-apply any service packs or hotfix installed on the instance.
9. Recreate the user content stored in the msdb database, such as jobs, alert, etc.
10. Backup the msdb database.
Q. What are main performance monitor counters to be monitor for SQL Server?
A:Following few perfmon counters to be monitor for SQL Server:
Processor\%Processor Time: Monitoring CPU consumption allows you to check for a bottleneck on the server (indicated by high sustained usage).
High percentage of Signal Wait: Signal wait is the time a worker spends waiting for CPU time after it has finished waiting on something else (such as a lock, a latch or some other wait). Time spent waiting on the CPU is indicative of a CPU bottleneck. Signal wait can be found by executing DBCC SQLPERF (waitstats) on SQL Server 2000 or by querying sys.dm_os_wait_stats on SQL Server 2005.
Physical Disk\Avg. Disk Queue Length: Check for disk bottlenecks: if the value exceeds 2 then it is likely that a disk bottleneck exists.
MSSQL$Instance: Buffer Manager\Page Life Expectancy: Page Life Expectancy is the number of seconds a page stays in the buffer cache. A low number indicates that pages are being evicted without spending much time in the cache, which reduces the effectiveness of the cache.
MSSQL$Instance: Plan Cache\Cache Hit Ratio: A low Plan Cache hit ratio means that plans are not being reused.
MSSQL$Instance:General Statistics\Processes Blocked: Long blocks indicate contention for resources.
Q. What is “Lock Pages in Memory” option? How it will impact\useful to SQL Server?
A:Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.
Following issues may rise when “Lock Pages in Memory” is not turned on:
--SQL Server performance suddenly decreases.
--Application that connects to SQL Server may encounter timeouts.
--The hardware running SQL Server may not respond for a short time periods.
Q. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?
A:To know the exact memory usage relay on column “physical_memory_in_use_kb” from DMV “sys.dm_os_process_memory”.
OR
Using performance counters also we can find the usage.
Performance object: Process
Counter: Private Bytes
Instance: sqlservr
Performance object: Process
Counter: Working Set
Instance: sqlservr
The Private Bytes counter measures the memory that is currently committed. The Working Set counter measures the physical memory that is currently occupied by the process.
For 64-bit sql servers we can also check the current memory usage using the below performance counter.
Performance object: SQL Server:Memory Manager
Counter: Total Server Memory (KB)
Q.I wanted to know what are the maximum worker threads setting and active worker thread count on sql server. Can you tell me how to capture this info? What’s the default value for max thread count?
A: We can check the current settings and thread allocation using the below queries.
–Thread setting
select max_workers_count from sys.dm_os_sys_info
–Active threads
select count(*) from sys.dm_os_threads
Default value is 255.
Increasing the number of worker threads may actually decrease the performance because too many threads causes context switching which could take so much of the resources that the OS starts to degrade in overall performance.
Q. How to Rebuild System databases?
A: System databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases or to modify the default server-level collation.
The following procedure rebuilds the master, model, msdb, and tempdb system databases. You cannot specify the system databases to be rebuilt. For clustered instances, this procedure must be performed on the active node and the SQL Server resource in the corresponding cluster application group must be taken offline before performing the procedure.
This procedure does not rebuild the resource database.
This is very critical task especially if you are performing on Production Server, so ensure you list out all the pre/post steps. Follow the below steps to rebuild system databases:
1. Run Command Prompt as Administrator
1.1. From a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Release.
2. From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges.
Syntax:
If using Windows Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=DOMAIN\user_name [ /SQLCOLLATION=CollationName]
If using SQL Authentication:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Example:
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=sa /SAPWD= ******
3. When Setup has completed rebuilding the system databases, it returns to the command prompt with no messages. Examine the Summary.txt log file to verify that the process completed successfully. This file is located at C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Logs.
Post Steps: After rebuilding the database you may need to perform the following additional tasks:
1. Restore your most recent full backups of the master, model, and msdb databases. For more information, see Back Up and Restore of System Databases (SQL Server).
Note: If you have changed the server collation, do not restore the system databases. Doing so will replace the new collation with the previous collation setting.
If a backup is not available or if the restored backup is not current, re-create any missing entries. For example, re-create all missing entries for your user databases, backup devices, SQL Server logins, end points, and so on. The best way to re-create entries is to run the original scripts that created them.
2. If the instance of SQL Server is configured as a replication Distributor, you must restore the distribution database. For more information, see Back Up and Restore Replicated Databases.
3. Move the system databases to the locations you recorded previously. For more information, see Move System Databases.
4. Verify the server-wide configuration values match the values you recorded previously.
Q. How to Rebuild Resource Database in SQL Server?
A: The following procedure rebuilds the resource system database. When you rebuild the resource database, all service packs and hot fixes are lost, and therefore must be reapplied.
To rebuild the resource system database:
1. Launch the SQL Server Setup program (setup.exe) from the distribution media.
2. In the left navigation area, click Maintenance, and then click Repair.
3. Setup support rule and file routines run to ensure that your system has prerequisites installed and that the computer passes Setup validation rules. Click OK or Install to continue.
4. On the Select Instance page, select the instance to repair, and then click Next.
5. The repair rules will run to validate the operation. To continue, click Next.
5. From the Ready to Repair page, click Repair. The Complete page indicates that the operation is finished.
Q. If the msdb database is damaged and you do not have a backup of the msdb database, can you to create new msdb database in SQL Server?
A: Yes, you can create new msdb by using the instmsdb script. Follow the following steps:
Note:Rebuilding the msdb database using the instmsdb script will eliminate all the information stored in msdb such as jobs, alert, operators, maintenance plans, backup history, Policy-Based Management settings, Database Mail, Performance Data Warehouse, etc.
1. Stop all services connecting to the Database Engine, including SQL Server Agent, SSRS, SSIS, and all applications using SQL Server as data store.
2. Start SQL Server from the command line using the command:
NET START MSSQLSERVER /T3608
3. In another command line window, detach the msdb database by executing the following command, replacing
SQLCMD -E -S
4. Using the Windows Explorer, rename the msdb database files. By default these are in the DATA sub-folder for the SQL Server instance. 5. Using SQL Server Configuration Manager, stop and restart the Database Engine service normally. 6. In a command line window, connect to SQL Server and execute the command:
SQLCMD -E -S
Replace
7. Using the Windows Notepad, open the instmsdb.out file and check the output for any errors.
8. Re-apply any service packs or hotfix installed on the instance.
9. Recreate the user content stored in the msdb database, such as jobs, alert, etc.
10. Backup the msdb database.
Q. What are main performance monitor counters to be monitor for SQL Server?
A:Following few perfmon counters to be monitor for SQL Server:
Processor\%Processor Time: Monitoring CPU consumption allows you to check for a bottleneck on the server (indicated by high sustained usage).
High percentage of Signal Wait: Signal wait is the time a worker spends waiting for CPU time after it has finished waiting on something else (such as a lock, a latch or some other wait). Time spent waiting on the CPU is indicative of a CPU bottleneck. Signal wait can be found by executing DBCC SQLPERF (waitstats) on SQL Server 2000 or by querying sys.dm_os_wait_stats on SQL Server 2005.
Physical Disk\Avg. Disk Queue Length: Check for disk bottlenecks: if the value exceeds 2 then it is likely that a disk bottleneck exists.
MSSQL$Instance: Buffer Manager\Page Life Expectancy: Page Life Expectancy is the number of seconds a page stays in the buffer cache. A low number indicates that pages are being evicted without spending much time in the cache, which reduces the effectiveness of the cache.
MSSQL$Instance: Plan Cache\Cache Hit Ratio: A low Plan Cache hit ratio means that plans are not being reused.
MSSQL$Instance:General Statistics\Processes Blocked: Long blocks indicate contention for resources.
Q. What is “Lock Pages in Memory” option? How it will impact\useful to SQL Server?
A:Lock Pages in Memory is a setting that can be set on 64-bit operating systems that essentially tell Windows not to swap out SQL Server memory to disk. By default, this setting is turned off on 64-bit systems, but depends on various conditions this option needs to be turned on.
We must be very careful in dealing with this option. One can enable this after a detailed analysis of current environment.
Following issues may rise when “Lock Pages in Memory” is not turned on:
--SQL Server performance suddenly decreases.
--Application that connects to SQL Server may encounter timeouts.
--The hardware running SQL Server may not respond for a short time periods.
Q. Task manager is not showing the correct memory usage by SQL Server. How to identify the exact memory usage from SQL Server?
A:To know the exact memory usage relay on column “physical_memory_in_use_kb” from DMV “sys.dm_os_process_memory”.
OR
Using performance counters also we can find the usage.
Performance object: Process
Counter: Private Bytes
Instance: sqlservr
Performance object: Process
Counter: Working Set
Instance: sqlservr
The Private Bytes counter measures the memory that is currently committed. The Working Set counter measures the physical memory that is currently occupied by the process.
For 64-bit sql servers we can also check the current memory usage using the below performance counter.
Performance object: SQL Server:Memory Manager
Counter: Total Server Memory (KB)
Q.I wanted to know what are the maximum worker threads setting and active worker thread count on sql server. Can you tell me how to capture this info? What’s the default value for max thread count?
A: We can check the current settings and thread allocation using the below queries.
–Thread setting
select max_workers_count from sys.dm_os_sys_info
–Active threads
select count(*) from sys.dm_os_threads
Default value is 255.
Increasing the number of worker threads may actually decrease the performance because too many threads causes context switching which could take so much of the resources that the OS starts to degrade in overall performance.
it is a nice blog
ReplyDeleteSQL Server DBA Online Training hyderabad
nice blog thank you for sharing
ReplyDeleteSQL Server DBA Online course hyderabad