What happens when a SQL Server instance is restarted?

What happens when a SQL Server instance is restarted?

Have you ever wondered or got curious to know what will happen or what are the activities carried out when an SQL Server instance get a restart request?

SQL Server instance will stop and then start again. Yes, this is obvious and there are lot more things that happen when a restart command is issued on an SQL Server instance.

With this post I am trying to list down the activities that happen during the restart of a SQL server instance, may be the sequence is not correct and the list might be incomplete. In that case, you can always correct me and complete the list. J

First of all, the service stops and before the service stops,

1. Checkpoint is issued on all databases
2. Check for the jobs that are running and stop them
3. Release the locks on database files to Operating System
4. Release the memory used by SQL Server instance
5. Flush the metadata collected for DMV’s and DMF’s
6. Record an event in default trace and event viewer regarding the SQL Server instance shutdown

During the starting of SQL Server service, 

1. The service is authenticated by verifying the credentials provided in the logon account and the service is started.
2. Startup parameters (master database data file path, log file path and error log file path, etc… if any) are verified
3. The port on which SQL server is listening is opened.
4. Memory is allocated
5. Read master database metadata for information about user databases
6. Attach all the user database
7. Undergo database recovery phases (Analysis, redo and undo phases.)
8. Obtain lock on the database files
9. tempdb files are allocated based on the initial size settings and other setting like collation are copied from model database.
10. An entry to default trace is recorded about the start of SQL Server instance
11. All the events are recorded to SQL Server log file and event viewer
12. Accept connections to databases
13. Start the metadata collection for DMV’s and DMF’s
14. Recompile Stored Procedures

Phases of Database recovery

From my previous post “What happens when a SQL Server instance is restarted?” we know what activities will be carried out when the SQL Server instance gets restart request.

Now, it’s time to understand what recovery phases the database will undergo.
The databases undergo recovery phases in two scenarios

1. When the SQL server  or service is restarted
2. When the database is being restored.

There are 3 Phases of Recovery and are based on the last checkpoint in the transaction log.

Recovery Phases - Drill Down

Recovery Phases - Graphical

Posted by Principal at Thursday, August 02, 2012

General SQL SERVER DBA Monitoring Scripts

General SQL SERVER DBA Monitoring Scripts

Tsql Script to know Failed JOBS & DISK SPACE MONITORING SCRIPT

 

 use msdb        

 go       

 select  j.[name]as 'Failed Job' from dbo.sysjobs as j (NoLOCK)          

 inner join dbo.sysjobservers as s (NOLOCK)

 on s.job_id = j.job_id

 where enabled=1 and s.last_run_outcome = 0 

 order by j.[name]         

 go

 exec  master..xp_fixeddrives

********************************************************************************

Tsql script to get Sql job owner info

select name as Job_Name, SUSER_SNAME(owner_sid) as Job_Owner

from sysjobs

*********************************************************************************

TSQL SCRIPT TO KNOW SQL JOBS ENABLED OR DISABLED

SELECT job_id, [name] FROM msdb.dbo.sysjobs

**********************************************************************************

List of all the jobs currently running on server

 SELECT server as ServerName,

 database_name as DBName,

 name as Job_Name,enabled,

 description as JobDescription,

 step_name,command

 FROM msdb.dbo.sysjobs job JOIN

 msdb.dbo.sysjobsteps steps       

 ON job.job_id = steps.job_id

 WHERE job.enabled = 1 -- remove this if you wish to return all jobs

*********************************************************************************

Tsql script to get Job Name, Category, Job Description

SELECT  sysjobs.name 'Job Name',

        syscategories.name 'Category',

        CASE [description]

          WHEN 'No Description available.' THEN ''

          ELSE [description]

        END AS 'Description'

FROM    msdb.dbo.sysjobs

        INNER JOIN msdb.dbo.syscategories

ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id

WHERE   syscategories.name <> 'Report Server'

ORDER BY sysjobs.name

*********************************************************************************

TSQL SCRIPT TO GET ALL DATABASE PROPERTIES

select name, compatibility_level,

user_access_desc, state_desc,

recovery_model_desc,log_reuse_wait_desc

from sys.databases

TSQL SCRIPT TO GET LINKED SERVER INFORMATION IN SQL SERVER

--get a list of linked servers

-- with remote logins

SELECT s.srvname as linked_servername,

 u.rmtloginame as lnk_svr_loginname,

 '----' as sep, s.* , u.*

FROM sysservers s, sysoledbusers u

where srvid = rmtsrvid

order by srvname

OR

select s.srvname as linked_servername,

u.rmtloginame as lnk_svr_loginname,

u.rmtloginame as lnk_svr_loginname

FROM sysservers s, sysoledbusers u

where srvid = rmtsrvid

order by srvname

**************************************************************************************

Find Service Pack patch status, CPU, memory and more

Here's a quick query you can run across all your servers (2005+) to find a wealth of information like service pack, edition, number of CPUs and RAM. Even more is available if you want to add additional SERVERPROPERTY attributes or fields from one of the DMVs.

SELECT SERVERPROPERTY('ServerName') AS [SQLServer],

               --@@microsoftversion/0x01000000 AS [MajorVersion],

               SERVERPROPERTY('ProductVersion') AS [VersionBuild],

               SERVERPROPERTY('ProductLevel') AS [Product],

               SERVERPROPERTY ('Edition') AS [Edition],

               --SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsWindowsAuthOnly],

               --SERVERPROPERTY('IsClustered') AS [IsClustered],

               [cpu_count] AS [CPUs],

               [physical_memory_in_bytes]/1048576 AS [RAM (MB)]

FROM    [sys].[dm_os_sys_info]

Note, if you're trying to run this against a 2000 instance, just eliminate the last 3 lines and final comma and run the SELECT portion only.

http://sqltidbits.com/scripts/find-service-pack-patch-status-cpu-memory-and-more

CPU Usage showing 100 Percent

CPU Usage showing 100 Percent

Troubleshooting CPU Usage

This article describes how to troubleshoot the CPU usage issue.

Troubleshooting generally involves the use of a series of steps to isolate and determine the cause. Some of the possible causes include:

* Blocking.
* System resource contention.
* A particular set of queries or stored procedures with long execution times.

- Check for Blocking: Run the command exec sp_who system stored procedure, to see if blocking is occurring.

This output will contain a blk column wherein you need to check the output for any non-zero entries that indicates that blocking is occurring.

Run this procedure periodically to check for blockings, if any.

- System resource contention: Try using various monitoring tool to determine if it’s a system resource issue, such as:

* Try downloading Process Explorer, looking at the threads for sqlservr.exe and figure out who is consuming all of the CPU time?

- Long SQL statements require a large amount of CPU to processes regardless of the actual record amount being fetched.

- I would also look in the SQL Server error log, to see if there are any messages and of course also make sure with sp_who2 that there are no active processes.

Particularly, would watch the CPUTime column, to see if there is any suspect.

- I would try executing the following query to dig out some more clues:

SELECT *

FROM sys.dm_exec_requests a

OUTER APPLY sys.dm_exec_sql_text(a.sql_handle) b

WHERE session_id > 50

and session_id <> @@spid



- If nothing is currently running on the server, then Open the SQL Profiler, connect to the instance and trace the following events: (Be sure to select all columns in the output).

Profiling should help identify the bottleneck.
we need to look for rows which have a high CPU value.

* RPC: Completed (Under stored procedures)
* SQL: BatchCompleted (Under TSQL)

Q&A with Database Administrators

Q&A with Database Administrators

Questions & Answers

Q. What kind of Database Administrator do you think is the best Database Administrator?
A. Primary job of DBA is to secure the data and keep it safe as well as being able to reproduce data efficiently, when required. A Database Administrator, who can fulfill the requirements of Securing Data and Retrieving Data, is the best DBA as per my view.

questions about backup strategies and efficient restoring methodologies
.
Q. Can I restore the database if I do not have full backup but I have all the primary data file and secondary data files as well as logs?
A. You can not restore the database without having full database backup. However, if you have copy of all the data files (.mdf and .ndf) and logs (.ldf), when database was in working condition (or your desired state) you can attach that database using sp_attach_db.

Q. As per your opinion, what are the five top responsibilities of DBA?
A. I rate following five tasks as the most important responsibilities of DBA.

1. Securing the database from physical as well as logical integrity damage.
2. Restore the database from backup as part of disaster management plan.
3. Optimize the queries performance by proper indexing and optimizing joins, where conditions, select clause etc.
4. Design the new schema and support legacy schema as well legacy database systems.
5. Help developers to be better at writing SQL related code.

Q. One of the developers in my company moved one of the columns from one table to some other table in the same database. How can I find the name of the new table where the column has been moved?
A. This question can be answered by querying system views.
For SQL Server 2005 run the following code:

view sourceprint?

1.SELECT OBJECT_NAME(object_id) TableName

2.FROM sys.columns

3.WHERE name = 'YourColumnName'

The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but very handy script.

Q. What is the difference between SQL Server 2000 object owner and SQL Server 2005 schema?
A. Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.
SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
 
In SQL Server 2000, before dropping the user who owns database objects, all the objects belonging to that user need to be either dropped or their owner has to be changed. Every time a user has to be dropped or modified, system admin has to go through this inconvenient process.

In SQL Server 2005, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects.
Multiple users can be assigned to a single schema and they all automatically receive the same permissions and credentials as the schema to which they are assigned.

Due the same reason in SQL Server 2005 - when a user is dropped from database - there is no negative effect on the database itself.

Q. What is BI? I have heard this term before but I have no idea what is it?
A. BI is an acronym that stands for Business Intelligence. Microsoft has started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for long time.

The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting key enterprise data so that anyone in your company can quickly and easily ask questions based on accurate and timely data.

Effective BI allows end users to use data to understand why your business got the particular results that it did, to decide on courses of action based on past data, and to accurately forecast future results

Q. What is your recommendation if a query is running very slow?
A. Your question is very difficult to answer without looking at code, application and physical server. Few things should be looked at right away when similar situations arise.

• Restart Server
• Upgrade Hardware
• Check Indexes on Tables and Create Indexes if necessary
• Make sure SQL Server has priority over other operating system processes in SQL Server settings
• Update statistics on the database tables

Q. What should be the fill factor for Indexes created on tables?
A. Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I keep my servers default fill factor as 90.

Q. Which feature in SQL Server 2008 (to be released in February 2008) has surprised you? Name only one.
A. Plan Freezing is the new feature I never thought of. It is a very interesting feature and it is included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Q. How do you test your database?
This is a very generic question. I will be describing my generic database testing method as well as stored procedure testing methods.
Testing Databases:

• Table Column data type and data value validation.
• Index implementation and performance improvement.
• Constraints and Rules should be validated for data integrity.
• Application field length and type should match the corresponding database field.
• Database objects like stored procedures, triggers, functions should be tested using different kind of input values and checking the expected output variables.

Testing Stored Procedures:

• Understand the requirements in terms of Business Logic.
• Check that code follows all the coding standards.
• Comparing the fields' requirements of application to the fields retrieved by a stored procedure. They should match.
• Repeatedly run stored procedures many times with different input parameters and compare the output with expected results.
• Pass invalid input parameters and see if a stored procedure has good error handling.

What is .tuf file in Log Shipping?

What is .tuf file in Log Shipping?

Ø  Basically this .tuf file is the Transaction Undo File, which is created when performing log shipping to a server in Standby mode.

Ø  So if you ask why standby mode, database recovery is done when the log is restored and this mode also creates a file with the extension .TUF (which is the transaction Undo file on the destination server). 

Ø  In this mode we will be able to access the databases.

Ø  Undo file is needed in standby state because while restoring the log backup, uncommited transactions will be recoreded to the undo file and only commited transactions will be written to disk there by making users to read the database. 

Ø  When you restore next tlog backup SQL server will fetch the uncommited transactions from undo file and check with the new tlog backup whether the same is commited or not. 

Ø  If its commited the transactions will be written to disk else it will be stored in undo file until it gets commited or rolledback.

See the .tuf File Location

SELECT backup_destination_directory 
FROM dbo.log_shipping_secondary

SQL DBA Interview Questions Answers

SQL DBA Interview Questions Answers

Introductory

* Why do you like to be a DBA?
* Explain your skill set?
* Explain the environments you have worked in?
* What are your day to day tasks?
* What all tasks you have automated?
* How do you manage multiple SQL Servers?

Standards and Best practices

* Tell some standards and best practices you have implemented?
* How do you plan for the patching of SQL Server Service Packs and hotfixes?
* What is the difference between an index reorganization and an index rebuild?

Backup / Restore

* How will you validate if the backup is successful?
* How do you identify and rectify a performance issue?
* Name a few DBCC commands which you use for database administration?
* How do you backup the Analysis Services Databases?
* How you can take a Full backup without disturbing the LSNs?
* How do you plan for backup retention period?

Disaster Recovery

* How do you do a Point in Time recovery?
* What are the factors while designing the Disaster Recovery strategy?
* What is your database deployment strategy?
* What are the type of locks?
* How do you handle a deadlock issue?
* What is the difference between a fully-logged and minimally-logged operation?
* What is a query plan?

Monitoring

* Which all monitoring tools you have used including 3rd party softwares?
* Which third party tool is your favorite for Change Management, Backup Compression, Performance monitoring and Alerting?
* What may be the reasons for a huge MSDB database and how can you shrink it?
* Which all Windows performance monitor counters you use?
* Tell some DBCC commands and DMVs which you use on daily basis?
* How SQL Server does the memory management?
* How do you plan the memory distribution between the SQL Server and Operating system?
* What is the correlation between processors and number of temporary databases?
* What happens in the background when SQL Server service is restarted?
* What do you mean by database suspect mode and how do you repair a suspected database?

Difference between Checkpoint and LazyWriter

Difference between Checkpoint and LazyWriter

CheckPoint	Lazy Writer
1. Flush dirty pages to Disk	1. Flush dirty pages to disk.
2. Flush only Data pages to disk	2. Check for available memory and removed Buffer pool (execution plan/compile plan/ Data pages /Memory objects)
3. Default, Occurs approximately every 1 minute	3. Occurs depending upon memory pressure and resource availability
4. Can be managed with sp_confige -recovery interval option	4. It is lazy,  Sql server manages by its own.
5. Does not check the memory pressure	5. Monitor the memory pressure and try maintain the available free memory.
6. crash recovery process will be fast to read log as data file is updated.	6. No role in recovery
7. Occurs for any DDL statement	7. Occurs per requirement
8. Occurs before Backup/Detach command	8. Occurs per requirement
9. Depends upon the configuration setting, we can control.	9. Works on Least recent used pages and removed unused plans first, no user control.
10.  for simple recovery it flush the tlog file after 70% full.	10. No effect on recovery model.
11. can manually /Forcefully run command “Checkpoint”	11.No command for Lazy Writer
12. Very Less performance impact	12. No performance impact

Checkpoint:

Checkpoint occurs on database level.

To find when the checkpoint occur use undocumented function

select  * from ::fn_dblog(null,null) 

WHERE [Operation] like ‘%CKPT’

Also enabling trace flag will provide information on error log when checkpoint started at what database.

DBCC TRACEON(3502, -1)

Checkpoint impact the performance (very low IO) for heavy system, so we can even disable automatic checkpoint —-Never do this, using trace flag 3505

LazyWriter:

Lazy writer is on the server  to check when lazy writer occurs use

SQL Server Buffer Manager Lazy writes/sec