SQL Server Post

SQL SERVER – SQL Daily Tips – Space Unused Vs Space Unallocated

2009-07-29T02:35:00.003-05:00

Space Unused:

Are pages allocated on extends, however this pages are not used yet by any kind of objects. It means, SQL Server has 8 pages by each extent. If an object reside in a page of a certain extend, this space is allocated and used, but the rest of the space of the extend is allocated but unused because there are not objects.

Space Unallocated:

Space (extends) that is not in use, it means extends that are not allocated by SQL Server. Any kind of grow will produce a space unallocated.

SQL SERVER – SQL Error – MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{8BDD006D-7863-4D25-9806-08527E285EA4}'.

2009-07-29T00:12:00.002-05:00

Problem.

Overall summary:

Final result: Failed: see details below

Exit code (Decimal): 2064843076

Exit facility code: 787

Exit error code: 324

Exit message: MsiGetProductInfo failed to retrieve ProductVersion for package with Product Code = '{8BDD006D-7863-4D25-9806-08527E285EA4}'. Error code: 1605.

Start time: 2009-07-28 18:30:43

End time: 2009-07-28 18:31:14

Requested action: ComponentUpdate

Log with failure: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20090728_182952\Detail_ComponentUpdate.txt

Exception help link: http%3a%2f%2fgo.microsoft.com%2ffwlink%3fLinkId%3d20476%26ProdName%3dMicrosoft%2bSQL%2bServer%26EvtSrc%3dsetup.rll%26EvtID%3d50000%26ProdVer%3d10.0.1600.22%26EvtType%3d0x4E099C65%400x7B130144

The error happened during the installation of a SQL Server 2008.

Solution / Fix / Workaround

The above remark line show us the product code related with the error. First, is necessary to know that SQL Server 2008 has around of 30 products registered on the Registry. Each product has a Product code and an installation code as well (GUID).

For this case the Product Code is the following:

{8BDD006D-7863-4D25-9806-08527E285EA4}

The Installation Code is build with the product code in reverse form:

D600DD8368752D460894AE582E72580

One time that you got the Installation Code look into the Windows Registry and delete all the entries found.

Then try again the installation and the result must to be SUCCESSFULLY.

SQL SERVER – SQL Daily Tips – Backup your database without affect the normal backup sequence.

2009-07-20T16:40:00.001-05:00

Sometimes is necessary take a full backup or log backup for a database without affect the backup normal sequence for the database and log as well. This backup can be with different purpose as testing, development, etc. For this cases SQL Server provides an option for the backup statement, this is COPY_ONLY.

BACKUP DATABASE db_name TO disk='C:\db_name.bkp' WITH COPY_ONLY

This option COPY_ONLY, can be performed for any backup type. COPY_ONLY has certains behaviors depending of the backup type:

* A log backup taken using copy_only, does not truncate the transaction log.

* A data backup taken using copy_only cannot be used as base backup for differential or incremental backups.

* A differential backup has not effect with copy_only option.

SQL SERVER – Server Configurations Options – Allow updates

2009-07-19T01:56:00.001-05:00

This option is present when sp_configure is executed or when sys.configuration is queried, but this functionality is unavailable since SQL Server 2005, it is obsolete. Direct updates to system objects are not allowed. If sp_configure is used to enable the option, this configuration has not effect. In previous versions as SQL Server 2000 this functionality is available.

Testing the option

The following code enable the option “allow updates”, then a table is created and finally an update to system table is executed:

sp_configure 'allow updates', 1

RECONFIGURE with override

CREATE TABLE TBLTEST (TESTFIELD INT )

update sys.objects

set name = 'TBLTEST_1'

where name = 'TBLTEST'

When the update to system table is triggered the following error is shown:

Msg 259, Level 16, State 1, Line 1

Ad hoc updates to system catalogs are not allowed.

This option is not advanced option and is not necessary enable the “show advanced options”. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER – Server Configurations Options – Catalog View – sys.configurations

SQL SERVER – SQL Daily Tips – Monitoring progress for tasks.

2009-07-19T01:29:00.001-05:00

SQL Server 2005 introduces Dynamic Management View, one of this objects allows us monitoring the progress for different tasks and process in SQL Server.

select percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, *

from sys.dm_exec_requests

this query is very helpful in the daily work to monitoring different tasks.

SQL SERVER – Server Configurations Options – Agent XPs Option

2009-07-18T05:14:00.005-05:00

The Server configuration option called Agent XPs is used to enabled the SQL Server Agent extended stored procedures on the server. When this option is disabled, the SQL Server Agent node is not enabled in SQL Server Management Studio and you can not use the SQL Server Agent Service.

When the SQL Server agent is restarted, the extended stored procedures are enabled automatically.

To enabled this option:

sp_configure 'show advanced options', 1

RECONFIGURE with override

sp_configure 'Agent XPs', 1

RECONFIGURE with override

sp_configure 'show advanced options', 0

RECONFIGURE with override

This option is an advanced option and is necessary enable first the “show advanced options”. To understand in a better way if a certain option is or is not advanced, if it is dynamic or is necessary restart, please refer SQL SERVER – Server Configurations Options – Catalog View – sys.configurations

SQL SERVER – Server Configurations Options – Catalog View – sys.configurations

2009-07-18T05:10:00.001-05:00

The sys.configuration catalog view allow us to see the same values than sp_configure with “show advanced options” enabled. Even this Catalog view show us extra information because is possible to know if an option is or is not an advanced option, or if is or is not dynamic option.

Important Columns,

Value: Configured Value for the option

Minimum: Minimum Value for the option

Maximum: Maximum Value for the option

Value_in_use: current running value for the option.

Is_dynamic: it column allow us to know if the option is dynamic or not. 1 = the value takes effect when the RECONFIGURE statement is executed. 0 = the value takes effect when the SQL Server is restarted.

Is_advanced: it column allow us to know if the option is advanced. 1 = advanced option, it means that the option is displayed and can be changed just when “show advanced options” is set through sp_configure.

Now we are going to see statements using sys.configurations that are equivalent than sp_configure.

Normal View:

Using sys.configurations

select * from sys.configurations where is_advanced = 0

Using sp_configure:

sp_configure 'show advanced options', 0

RECONFIGURE with override

sp_configure

Advanced Option View:

Using sys.configurations

select * from sys.configurations where is_advanced = 1

Using sp_configure:

sp_configure 'show advanced options', 1

RECONFIGURE with override

sp_configure

Dynamic Option View

Using sys.configurations

To see all the dynamic options:

select * from sys.configurations where is_dynamic = 1

To see all the non dynamic options:

select * from sys.configurations where is_dynamic = 0

Using sp_configure is impossible to know if an option is dynamic.

Conclusion.

Using sys.configurations to see the server configuration is better than sp_configure because with a single select statement is possible to view all the server options with extra information as is_dynamic and is_advanced values, this extra information is impossible to obtain with sp_configure. Any changed necessary on the server configuration has to be performed with sp_configure.

SQL SERVER – SQL Daily Tip – Force Database Offline.

2009-07-18T03:40:00.002-05:00

To force a database to bring OFFLINE you can use the next statement:

alter database DBNAME set offline with rollback immediate

this is a quickly way to force a database to set it OFFLINE, when you use this statement a rollback occurs for all the transactions running.

SQL SERVER – SQL Error – Msg 7399, Level 16, State 1, Line 1. The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authenticati

2009-07-16T05:35:00.001-05:00

Problem.

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification".

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "(null)".

The error happened when the OPENROWSET statement was used.

The syntax used for the error is the following.

select * from openrowset('SQLOLEDB.1','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=db_test2;Data Source=.\bkp','select * from dbo.table2')

Solution / Fix / Workaround

The right syntax for my query was the next one:

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

SQL SERVER – Server Configurations Options – Adhoc distributed Queries Option

2009-07-16T04:32:00.001-05:00

When Adhoc Distributed Queries Options is enable (value = 1), SQL Server allow use Ad hoc queries through OPENROWSET and OPENDATASOURCE functions using OLEDB provider.

OPENROWSET and OPENDATASOURCE use OLE DB providers, this functions must be used only for data that is infrequently accessed. For data that is frequently accessed, is better use linked servers.

Example:

I have two SQL Server instances:

The first one is the localhost (.), I am going to called Server 1, and the second one is a SQL Server named instance localhost\BKP (.\BKP), I am going to called Server 2.

Server 2.

create database db_test2

use db_test2

create table table2

(

field1 int,

field2 varchar(10)

)

insert into table2 values (1,'test')

Server 1.

On the server 1, I check and the Ad Hoc Distributed Queries is disabled. ( run_value = 0 )

sp_configure 'Ad Hoc Distributed Queries'

Result

name minimum maximum config_value run_value

----------------------------------- ----------- ----------- ------------ -----------

Ad Hoc Distributed Queries 0 1 0 0

Then, I test my Ad hoc query using OPENROWSET.

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

I got the following error because the Ad Hoc Distributed Queries option is not enabled.

Msg 15281, Level 16, State 1, Line 1

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

To execute the Ad hoc query is necessary enable the option and try again the query.

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure with override

Result.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.

select * from

openrowset('SQLNCLI','Server=.\bkp;Uid=test_user;Pwd=test;Database=db_test2;','select * from dbo.table2')

Result.

field1 field2

----------- ----------

1 test

(1 row(s) affected)

SQL SERVER – SQL Real Cases – Issue with auto_close option (Root cause of SUSPECT database).

2009-07-11T03:43:00.004-05:00

Days ago I was working to find out the root cause of a SUSPECT state in a productive database. When I started to work in the database, it was already online. Somebody restart the MSSQL service and it bring online the database, but nobody knows what was the cause of the issue, it task is my job. :-)

Please be aware that the database name, data file or log file names are not the real values.

1. First I saw that the size of Transaction Log was several times the size of the entire db. It is bad, but this is not the cause of the suspect database.

2. I started to checking the ERRORLOG and I saw the system was configured to archive almost 60 ERROLOG. It is strange.

3. I started to read all the ERROLOG files, one by one. How to check it?, please check SQL SERVER – SQL TIPS – SELECT the ERROLOG. or SQL SERVER – Working with ERRORLOG.

4. When I was checking the ERROLOG.10, I saw the following error:

2009-07-03 22:40:02.78 spid51 udopen: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\Productive_DB_LOG_FILE.ldf. 0

2009-07-03 22:40:02.78 spid51 FCB::Open failed: Could not open device c:\ Productive_DB_LOG_FILE.ldf for virtual device number (VDN) 2.

In the first error I can see that the log file can not be blocked by SQL Server. We knows that SQL Server block the data files and log files when it try to start the database. The issue here is, the database was not offline at any moment. The system was working during long period of time and I did not see any message where the database was offline. So, if the database was ONLINE, what was the cause of the log file was not blocked by SQL Server?

At this moment I knew that the cause of the database was in SUSPECT state, was because a log file for the database was blocked by other process and can not be blocked by SQL Server when it try start the database.

5. When I was checking the ERRORLOG files, I saw several messages (not errors) as the following.

2009-07-03 22:33:08.85 spid51 Analysis of database 'PRODUCTIVE_DB' (9) is 100% complete (approximately 0 more seconds)

2009-07-03 22:40:02.74 spid51 Starting up database 'PRODUCTIVE_DB'.

Mmmmmm Starting up database several times?,

What is the cause that the database is starting in several times? I thought in the option AUTO_CLOSE at db level. SQL Server has an option that close the database automatically when the last user close his connection to database. It option is AUTO_CLOSE.

6. Then I checked if the AUTO_CLOSE is enabled with the next statement:

SELECT DATABASEPROPERTYEX('PRODUCTIVE_DB','ISAUTOCLOSE')

Result.

If IsAutoClose is 1, the option is enabled.

As per SQL Server BOL.

IsAutoClose: Database shuts down cleanly and frees resources after the last user exits.

7. Conclusion.

At this moment I know that AUTO_CLOSE option is enable. So, if this option is enable, it cause that SQL Server frees the resources of the database after the last user finish his connection and when a new user is connected to the database, it is started and it generate a message of “Starting Database” in the ERROLOG files. Hence, I am ready to conclude: AUTO_CLOSE option is enabled, this option caused that all resources are frees and caused that the data files and log files of the database are not blocked anymore, then this is cause that another process was able to block the log file and it happened, finally, when SQL Server try to start the database and block the log file again, it was not possible and SQL Server marks in SUSPECT state the PRODUCTIVE_DB.

SQL SERVER – SQL TIPS – SELECT the ERROLOG.

2009-07-11T03:20:00.006-05:00

A useful tip for look into the ERRORLOG, is use a SELECT statement with ERROLOG as table.

First is necessary to create a table with the structure of the ERROLOG outcome.

CREATE TABLE TBLERRORLOG

(

LOGDATE DATETIME,

PROCESSINFO VARCHAR(30),

DETAIL VARCHAR(8000)

)

Then, full the table with the results of extended stored procedure xp_readerrolog:

INSERT INTO TBLERRORLOG exec master..xp_readerrorlog

Now is possible SELECT the table TBLERRORLOG with a certain filter:

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

If you have several error logs, you can use the following code:

truncate table TBLERRORLOG

INSERT INTO TBLERRORLOG exec sp_readerrorlog 2

SELECT * FROM TBLERRORLOG where DETAIL like '%master%'

First, the table TBLERRORLOG is truncated, then the table is filled using the undocumented stored procedure sp_readerrolog, there is possible to define the ERROLOG number, in this case I use 2, then a SELECT statement is used. This code can be used several times depending of the ERROLOG allowing us a quickly way to find some issue.

SQL SERVER – TSQL – Handling Errors

2009-07-09T02:48:00.006-05:00

For SQL Server 2000 and earlier version, the most common way to working error as using @@ERROR function. SQL Server 2005 introduces the construct TRY…CATCH, it improves handling errors. The construct TRY…CATCH is the principal tool for handling error on SQL Server, specifically with exceptions errors. This capability allows you to manage exception errors in similar way as .NET programming languages.

A TRY…CATCH consist in two parts: these are a TRY block followed immediately by a CATCH block. All the TSQL Code must to be on TRY block and the handling error must to be on CATCH block. When in the TRY block does not exist error, the CATCH block is simply skipped. If the TRY block has an error, then the control is transferred to the next CATCH block.

Both, TRY and CATCH block starts with BEGIN TRY and BEGIN CATCH respectively, and ends with END TRY and END CATCH respectively.

You can test the following code in order to see a case without error on TRY block.

-- TSQL WORKING FINE

BEGIN TRY

PRINT 100 / 10

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The code on TRY block was executed successfully; that’s way the CATCH block was not executed. The result is the following:

Working on BLOCK TRY. WITHOUT ERRORS!!!!!!

Now, execute the following code with error on the TRY block:

-- TSQL WITH ERROR - DIVIDE BY ZERO

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'

END CATCH

The divide by zero causes an ERROR on the TRY block, it cause that the control was passed immediately to CATCH BLOCK and also cause that the second PRINT on TRY block was not executed. The result is the following.

Working on BLOCK CATCH WITH ERRORS!!!!!!

To obtain information about the error that cause the CATCH block to be executed, SQL Server provides the following system functions:

ERROR_NUMBER() function returns an integer with the number of error

ERROR_SEVERITY() function returns the severity number.

ERROR_STATE() function returns the error state number.

ERROR_LINE() function returns the line number inside the routine that caused the error.

ERROR_MESSAGE() function returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

ERROR_PROCEDURE() function returns the name of the stored procedure or trigger where the error occurred.

You can execute the following code in order to create a Stored Procedure that encapsulates the system functions mentioned above. Also you can customize the error messages. The following code is customized when the error number 208 (Invalid Object Name) happens, this customized message error will be used later. :

create procedure dbo.sp_err_msg

if ERROR_NUMBER() = 208

print 'Oops, the table queried does not exists. '

else

select

ERROR_NUMBER() as ErrorNumber,

ERROR_SEVERITY() as Severity,

ERROR_STATE() as State,

ERROR_LINE() as ErrorLine,

ERROR_MESSAGE() as ErrorMessage,

ERROR_PROCEDURE() as ErrorOnProcedure

You can modify the second code used in this article in order to see how works the error system functions.

BEGIN TRY

PRINT 100 / 0

PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The divide by zero cause an error on TRY block, then the control was passed to CATCH block and the stored procedure is executed. The result is the following.

Some errors are not handled by construct TRY…CATCH :

Compilation errors. Ex syntax error.
Recompilation errors. Ex object name resolution error.

If an compilation or recompilation errors occurs when they are at TRY … CATCH level, those errors are not handled by construct TRY …CATCH. On the other hand, if the error occurs at a lower execution level (ex. Sp_executesql or a stored procedure) the errors are handled by the construct TRY…CATCH.

Run the following code to demonstrate an event that is not handle by TRY…CATCH. The code, execute a SELECT statement to a non existing table, it cause an error:

BEGIN TRY

SELECT * FROM TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

Result:

Msg 208, Level 16, State 1, Line 2

Invalid object name 'TABLENOTDEFINED'.

However, if the SELECT statement is executed by a stored procedure the error will occur at a level lower than TRY block. Then, the error will be handled by the construct TRY … CATCH.

create procedure dbo.sp_exec_TABLENOTDEFINED

SELECT * FROM TABLENOTDEFINED

BEGIN TRY

exec dbo.sp_exec_TABLENOTDEFINED

END TRY

BEGIN CATCH

exec dbo.sp_err_msg

END CATCH

The table non defined on the Stored procedure cause an error 208, it is handled by TRY ... CATCH, and a customized error message is shown.

Result.

Oops, the table queried does not exists.

Conclusion

TRY..CATCH is the main tool provided by SQL Server to handling errors. To obtain more information about the errors is possible to use a system functions as: ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE().

SQL SERVER – TIPS – Reclaim space for a table after drop column.

2009-07-02T02:34:00.003-05:00

In a table, after a column is dropped using alter table drop column statement, is necessary to reclaim the space, just when a fixed-length column or text column were dropped.

To see the behavior you can create 3 tables with 3 fields, this 3 fields must to be different datatypes

-- create a db test

create database db_test

use db_test

-- create 3 table with the same fields

create table test1 (

a char(20), --fixed-lenght column

b varchar(20), --variable-lenght column

c text --text column

)

create table test2 (

a char(20), --fixed-lenght column

b varchar(20), --variable-lenght column

c text --text column

)

create table test3 (

a char(20), --fixed-lenght column

b varchar(20), --variable-lenght column

c text --text column

)

Now, you can add some information and see the space of each table.

-- insert some information into the 3 tables.

insert into test1 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ')

insert into test2 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ')

insert into test3 values ('aaaaaaaaaa','bbbbbbbbb','text text text text text text text text text text text text text ')

--checking the space

sp_spaceused test1

sp_spaceused test2

sp_spaceused test3

--Result

test1 1 32 KB 24 KB 8 KB 0 KB

test2 1 32 KB 24 KB 8 KB 0 KB

test3 1 32 KB 24 KB 8 KB 0 KB

Then, drop a column for each table, after drop a column the space for each table is the same than before. To reclaim the space is necessary to execute the dbcc cleantable on each table. This dbcc utility must to be executed when a variable-length column or text column were dropped.

-- drop one column in each table in order to

-- see the behavior after drop a column

alter table test1 drop column a

alter table test2 drop column b

alter table test3 drop column c

--reclaim space

dbcc cleantable ('db_test','test1')

dbcc cleantable ('db_test','test2')

dbcc cleantable ('db_test','test3')

MSDN says “DBCC CLEANTABLE reclaims space after a variable-length column or a text column is dropped by using the ALTER TABLE DROP COLUMN statement. The command does not reclaim space after a fixed-length column is dropped.”

After execute DBCC CLEANTABLE, you can see that some space was reclaimed for the table test3 where the TEXT column was dropped, for the other two tables the space is the same, even for the table 2 where a variable-length column was dropped, in this case some space must to be reclaimed, perhaps does not happening anything because the amount of data is not relevant.

sp_spaceused test1

sp_spaceused test2

sp_spaceused test3

--Result

test1 1 32 KB 24 KB 8 KB 0 KB

test2 1 32 KB 24 KB 8 KB 0 KB

test3 1 24 KB 16 KB 8 KB 0 KB

SQL SERVER – TIPS – Transfer Objects between Schemas

2009-07-02T01:30:00.001-05:00

In this tip, I show you, how you can transfer an object between schemas using ALTER SCHEMA statement.

-- create a db test

create database db_test

use db_test

--Create a schema original

create schema original

-- create a schema final

create schema final

-- create a table with the schema original

create table original.test1 (a int)

-- checking the schema for the test1 table

select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1'

-- Result

schema_name name

------------ ---------------

original test1

-- transfering object test1 from schema original to final

-- ORIGINAL -> FINAL

alter schema final transfer original.test1

-- Result

Command(s) completed successfully.

-- checking the schema for the test1 table after the changes

select schema_name(schema_id) as schema_name, name from sys.objects where name = 'test1'

-- Result

schema_name name

------------ ---------------

final test1

SQL SERVER – Working with ERRORLOG.

2009-07-02T00:27:00.002-05:00

In this article we are going to see how we can work with ERRORLOG. What is the ERRORLOG? The ERRORLOG, is the SQL Server Log, it means, the place where we can check the errors for SQL Server.

SQL Server has several ERRORLOG files, by default each instance contains 6 ERROLOG files, it value can be changed and we are going to see how to do it, later. Every time that SQL Server starts a new ERRORLOG file is generated and the current ERROLOG is renamed to ERROLOG.1; and the ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

How to see ERRORLOG content?

To see the ERRORLOG we can use the Microsoft SQL Server Management Studio (SSMS) or using TSQL.

Using TSQL:

We can use a extended stored procedure called xp_readerrorlog, it display the errors existing on the last ERRORLOG file.

xp_readerrorlog

Results:

LogDate ProcessInfo Text

----------------------- ------------ -----------------------------------------------------

2009-07-01 18:35:02.150 spid53 Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2009-07-01 18:35:02.150 spid53 Server process ID is 1208.

2009-07-01 18:35:02.150 spid53 Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

Using SSMS:

We can check one file or all the files mixed in the following path: SSMS->Management->SQL Server Logs.

How to see ERRORLOG files list?

To know the file name, creation date and file size for the errorlog files in my SQL Server instance, we can use the stored procedure called xp_enumrrorlogs, it does not need any input parameter.

exec xp_enumerrorlogs

Results:

Archive # Date Log File Size (Byte)

----------- --------------------- --------------------

0 07/01/2009 18:35 1348

1 07/01/2009 18:35 1616

2 07/01/2009 17:42 1616

3 07/01/2009 17:40 1616

4 07/01/2009 17:27 14222

5 06/29/2009 07:19 13004

6 06/28/2009 00:06 2588

(7 row(s) affected)

Obviously, we can use Windows Explorer J as well. In the results of xp_readerrorlog we can see where the errolog files are located.

How ERRORLOG can be Cycling/Purge/Reset?

As is mentioned on the beginning of this article, every time that SQL Server is started the ERRORLOG is purge and a new ERRORLOG file is generated.

When a SQL Server instance is up and running for long periods of time, the errorlog file grow up considerably and it cause hard to read the entire file. SQL Server offers the capability to cycle the ERRORLOG in a manual way, to perform this task we can use the system stored procedure called sp_cycle_errorlog.

exec sp_cycle_errorlog

Result.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When sp_cycle_errorlog is executed successfully, the current ERRORLOG file is renamed to ERRORLOG.1 and the same behavior happens with the rest of ERRORLOG files. The last ERRORLOG file is deleted.

Another way to purge the SQL Server log is using the DBCC ERROLOG. This DBCC is used when the stored procedure sp_cycle_errorlog is executed.

DBCC ERRORLOG

How many old ERRORLOG are needed?

If the plan is to cycle/purge/reset the errorlog file in a manual way, is necessary keep in mind the timeframe to generate a new log file. Also is necessary to know the frequency to reset the log and the amount of information necessary to hold.

If the plan is to hold more files than the default. We can change using the SSMS->Management -> SQL Server Logs, then right click and choose the Configure option. The values allowed are from the default (6) until 99, it value is the maximum value allowed by SQL Server.

Conclusion

Having an ERRORLOG file in a reasonable size, it makes easier to browse and analyze the log error. This article show us how we can work with the errolog file: cycling the file with sp_cycle_errorlog, reading the file with xp_readerrorlog, listing the files with xp_enumerrologs and changing the total of errorlog files.

SQL SERVER – Server Configuration Options

2009-06-28T15:49:00.008-05:00

The server configuration options can be managed using sp_configure system stored procedure and SQL Server Management Studio. Some options needs to be configured with SQL Server Surface Area Configuration Tool. The most frequent options can be configured through SQL Server Management Studio. The entire configuration options can be managed and modified using sp_configure.

Is essential for DBA to know the meaning and understand each server configuration options. We know that is hard to accomplish this task, but we can do it, if we find out one by one each of this options. Hence, we are going to try to understand one by one (day by day) these options on this blog.

First we need to know what are the all the server configuration options. To know what are the entire server configurations, we can look into sys.configuration system view or use sp_configure system stored procedure.

select * from sys.configurations;

sp_configure 'show advanced options',1

reconfigure

sp_configure

In the following table we can the see all the server configuration options, the minimum / maximum value for each option, default value. Each value has a letter A ( advanced option, to change this options the setting show advanced options to 1 is required), RR ( this options require restart the db engine) and SC ( automatic configuration, restart is not required).

Configuration option	Minimum value	Maximum value	Default
access check cache bucket count (A)	0	16384	0
access check cache quota (A)	0	2147483647	0
ad hoc distributed queries (A)	0	1	0
affinity I/O mask (A, RR)	-2147483648	2147483647	0
affinity64 I/O mask (A, only available on 64-bit version of SQL Server)	-2147483648	2147483647	0
affinity mask (A)	-2147483648	2147483647	0
affinity64 mask (A, RR), only available on 64-bit version of SQL Server	-2147483648	2147483647	0
Agent XPs (A)	0	1	0 (Changes to 1 when SQL Server Agent is started. Will be 1 if SQL Server Agent is set to automatic start during setup.)
allow updates (Obsolete. Do not use. Will cause an error during reconfigure.)	0	1	0
awe enabled (A, RR)	0	1	0
backup compression default	0	1	0
blocked process threshold (A)	0	86400	0
c2 audit mode (A, RR)	0	1	0
clr enabled	0	1	0
common criteria compliance enabled (A, RR)	0	1	0
cost threshold for parallelism (A)	0	32767	5
cross db ownership chaining	0	1	0
cursor threshold (A)	-1	2147483647	-1
Database Mail XPs (A)	0	1	0
default full-text language (A)	0	2147483647	1033
default language	0	9999	0
default trace enabled (A)	0	1	1
disallow results from triggers (A)	0	1	0
EKM provider enabled	0	1	0
filestream_access_level	0	2	0
fill factor (A, RR)	0	100	0
ft crawl bandwidth (max) , see ft crawl bandwidth(A)	0	32767	100
ft crawl bandwidth (min) , see ft crawl bandwidth(A)	0	32767	0
ft notify bandwidth (max) , see ft notify bandwidth(A)	0	32767	100
ft notify bandwidth (min) , see ft notify bandwidth(A)	0	32767	0
index create memory (A, SC)	704	2147483647	0
in-doubt xact resolution (A)	0	2	0
lightweight pooling (A, RR)	0	1	0
locks (A, RR, SC)	5000	2147483647	0
max degree of parallelism (A)	0	64	0
max full-text crawl range (A)	0	256	4
max server memory (A, SC)	16	2147483647	2147483647
max text repl size	0	2147483647	65536
max worker threads (A, RR)	128	32767 (1024 is the maximum recommended for 32-bit SQL Server, 2048 for 64-bit SQL Server.)	0 Zero auto-configures the number of max worker threads depending on the number of processors, using the formula (256+( -4) * 8) for 32-bit SQL Server and twice that for 64-bit SQL Server.
media retention (A, RR)	0	365	0
min memory per query (A)	512	2147483647	1024
min server memory (A, SC)	0	2147483647	0
nested triggers	0	1	1
network packet size (A)	512	32767	4096
Ole Automation Procedures (A)	0	1	0
open objects (A, RR, obsolete)	0	2147483647	0
optimize for ad hoc workloads (A)	0	1	0
PH_timeout (A)	1	3600	60
precompute rank (A)	0	1	0
priority boost (A, RR)	0	1	0
query governor cost limit (A)	0	2147483647	0
query wait (A)	-1	2147483647	-1
recovery interval (A, SC)	0	32767	0
remote access (RR)	0	1	1
remote admin connections	0	1	0
remote login timeout	0	2147483647	20
remote proc trans	0	1	0
remote query timeout	0	2147483647	600
Replication XPs Option (A)	0	1	0
scan for startup procs (A, RR)	0	1	0
server trigger recursion	0	1	1
set working set size (A, RR, obsolete)	0	1	0
show advanced options	0	1	0
SMO and DMO XPs (A)	0	1	1
SQL Mail XPs (A)	0	1	0
transform noise words (A)	0	1	0
two digit year cutoff (A)	1753	9999	2049
user connections (A, RR, SC)	0	32767	0
User Instance Timeout (A, only appears in SQL Server 2008 Express.)	5	65535	60
user instances enabled (A, only appears in SQL Server 2008 Express.)	0	1	0
user options	0	32767	0
xp_cmdshell (A)	0	1	0

so, we are going to try day by day see some details for each of the server configuration options.

References:

SQL Server BOL - Server configuration options.

SQL SERVER – Working with LSN for Restore and Backup Databases. (Error: Msg 4305, Level 16, State 1, Line 1)

2009-06-25T03:03:00.002-05:00

Today in my job, some colleague asks me about the next error:

Msg 4305, Level 16, State 1, Line 1

The log in this backup set terminates at LSN 9386000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 9417000002731000001 can be restored.

So, my explanation was:

You are trying to apply a Log file that finish with LSN 9386000024284900001 into a DB restored with LSN 9417000002731000001, so, the LSN of the Full Backup (restored) is older than the log file that is trying to apply. So, the log file is not possible to apply it.

Now, we are going to replicate that error and we are going to see how to know the LSN of each backup, in order to clarify and understand the error mentioned above.

First a DB will be created, some backups will be taken, then the DB will be restored and some log will be applied in the wrong way, then we are be able to identify the right way and the we will apply the logs.

First, we create a database and a table for testing purpose.

--Create DB for Testing

create database DB_TestLSN

USE DB_TestLSN

--create a testing table.

create table test(

a int

)

Now, we are going to backup the database and log:

backup database DB_TestLSN

to disk='c:\DB_TestLSN.bak'

A table2 and table3 are generated and two log backups are taken:

create table test2(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG1'

create table test3(

a int

)

-- backup log file

backup log DB_TestLSN

to disk='c:\DB_TestLSN_LOG2'

Restoring database….

First restore the full backup

restore database DB_TestLSN_REST

from disk='c:\DB_TestLSN.bak'

with norecovery

then we restore intentionally the wrong log file

restore log DB_TestLSN_REST

from disk='c:\DB_TestLSN_LOG2'

Msg 4305, Level 16, State 1, Line 1

The log in this backup set begins at LSN 18000000015300001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000014000001 can be restored.

Msg 3013, Level 16, State 1, Line 1

RESTORE LOG is terminating abnormally.

… is necessary to identify what is the correct way to restore the logfiles. For this, we can mention two manners:

1. If we have access to the server where the backup was taken.

We can check the system table msdb..backupset. To identify the chronologic order of the data backup and log backup, we can put special attention on the following columns

backup_start_date, backup_finish_date, first_lsn, last_lsn

With that columns we can view the LSN of each of the backups taken, so our error shows that 18000000015300001 (this is the first lsn for the second log backup) is too early to apply and and earlier log backup that includes LSN 18000000014000001 must be exists, and that is correct, the first log backup includes the LSN 18000000014000001 and also this logs begins with the log_chain.

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn

from msdb..backupset where database_name = 'DB_TestLSN'

database_name type first_lsn last_lsn begins_log_chain

DB_TestLSN D 18000000007400155 18000000014000001 0

DB_TestLSN L 18000000007400155 18000000015300001 1

DB_TestLSN L 18000000015300001 18000000015900001 0

2. If we have just the backup files.

To know the LSN of the backups we can use

RESTORE HEADERONLY from disk='c:\DB_TestLSN.bak'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG1'

RESTORE HEADERONLY from disk='c:\DB_TestLSN_LOG2'

In this case also is necessary to put special attention on the columns first_lsn, last_lsn in each of the backup files.

To reproduce exactly the error mentioned on the top of this article,

Msg 4326, Level 16, State 1, Line 2

The log in this backup set terminates at LSN 18000000015400001, which is too early to apply to the database. A more recent log backup that includes LSN 18000000022000001 can be restored.

Msg 3013, Level 16, State 1, Line 2

RESTORE LOG is terminating abnormally.

follow up the next steps.

Create DB
Backup Full Database (BkpDBFULL1)
Backup Log (BkpLOG1)
Backup Full Database (BkpDBFULL2)
Restore BkpDBFULL2
Try to apply BkpLOG1

Instead of use backup log, you can use backup incremental and the behavior is the same than we show in this examples.

[Questions & Answers]

Q: What is a LSN (Log Secuence Number): ?

Every record in the Microsoft SQL Server transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

SQL SERVER – DBA Types (Logical DBA Vs Physical DBA)

2009-06-24T22:16:00.005-05:00

Days ago I was talking with some colleagues about the existing DBA types, so we were discussing about Logical DBA and Physical DBA. Hence I am going to show some DBA types according two authors.

According to Craig S. Mullins (Book: Database Administration: The Complete Guide to Practices and Procedures) (http://www.CraigSMullins.com), (http://media.techtarget.com/digitalguide/images/Misc/DBA_types.pdf)

There are DBAs who focus on logical design and DBAs who focus on physical design;
DBAs who specialize in building systems and DBAs who specialize in maintaining and
tuning systems; specialty DBAs and general-purpose DBAs.

System DBA: A system DBA focuses on technical rather than business issues, primarily in the system administration area. Typical tasks center on the physical installation and performance of the DBMS software. System DBAs are rarely involved with actual implementation of databases and applications. They might get involved in application tuning when operating system parameters or complex DBMS parameters need to be altered.

Database Architect: The database architect is involved in new design and development work only; he is not involved in maintenance, administration, or tuning of established databases and applications. The database architect designs new databases for new or existing applications. The rationale for creating a separate position is that the skills required for designing new databases are different from the skills required to keep an existing database implementation up and running. A database architect is more likely than a general purpose DBA to have data administration and modeling expertise.

Database Analyst: Sometimes junior DBAs are referred to as database analysts. Sometimes a database analyst performs a role similar to that of the database architect. Sometimes the data administrator is referred to as the database analyst or perhaps as the data analyst. And sometimes a database analyst is just another term used by some companies instead of database administrator.

Data Modeler: A data modeler is usually responsible for a subset of the DA’s responsibilities.

Application DBA: In direct contrast to the system DBA is the application DBA. The application DBA focuses on database design and the ongoing support and administration of databases for a specific application or applications. The application DBA is likely to be an expert at writing and debugging complex SQL and understands the best ways to incorporate database requests into application programs. The application DBA must also be capable of performing database change management, performance tuning, and most of the other roles of the DBA.

Performance Analyst: Performance analysts are a specific type of task-oriented DBA. The performance analyst, more common than other task-oriented DBAs, focuses solely on the performance of database applications.

Data Warehouse Administrator: Organizations that implement data warehouses for performing in-depth data analysis often staff DBAs specifically to monitor and support the data warehouse environment. Data warehouse administrators must be capable DBAs, but with a thorough understanding of the differences between a database that supports OLTP and a data warehouse.

According to Brad McGhee. He defines the following classification for DBA:

DBA System Administrator
DBA Database Designer
DBA Developer
DBA High Availability Specialist
DBA Business Intelligence Specialist
DBA Report Writer

For details of each type, please check the ebook. (Book: How to become an exceptional DBA)

As we can see, both authors have the same idea, in the following table I put the DBA classification for each author and the equivalent between each other. From my point of view I did a classification of DBA Logical or Physical as well.

Conclusion.

The Logical DBA is the person in charge of developing, design, debugging, etc, the Logical stuff for the database and the Physical DBA is the person in charge of the configuration, maintenance, physical design, etc. From my perspective exist some DBA types that is necessary to perform both activities Logical and Physical.

SQL SERVER – Error: Property Owner is not available for Database '[DB_NAME]'. This property may not exist for this object, or may not be retrievable

2009-06-23T17:28:00.001-05:00

Problem.

Error: Property Owner is not available for Database '[DB_NAME]'.

This property may not exist for this object, or may not be retrievable

due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This a common error that happens when we try to access the database options and the database doesn’t have owner. The database can not be an owner because somebody clean the owner property, or the user (owner) was deleted, etc.

Solution / Fix / Workaround.

Is necessary to assign a proper owner for the database. To know what are the valid owners for the database, we can execute the following query:

select databases.name,server_principals.name

from sys.[databases]

inner join sys.[server_principals]

on [databases].owner_sid = [server_principals].sid

To assign the owner for the database:

USE <SID>

sp_changedbowner ''

SQL SERVER – Error: The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is runn

2009-06-23T15:04:00.004-05:00

Problem.

Sometimes when we are trying to truncate the transaction log, we can got the following error message:

Statement:

BACKUP LOG <SID> WITH TRUNCATE ONLY

Error:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

This is because some part of the transaction log is pending to replicate (publisher side), hence is not possible to truncate it from the transaction log. In this case is necessary to mark these transactions as replicated in order to be able to truncate them.

Solution / Fix / Workaround.

To mark the transactions as distributed (or replicated) we can use the following statemtent:

USE <SID>

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Use sp_repldone just for troubleshooting purpose and in emergency situations. If this SP is not used in the proper way you can invalidate pending information to be replicated.

Sometimes when sp_repldone is used, is possible that we get the following error.

Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1 The database is not published

It means that our Database is not published. A workaround to this error, is to publish our database with the below statement, then try again the sp_repldone and finally execute again the sp_dboption with publish = false.

sp_dboption '', 'Publish', 'true'

Ex.

sp_dboption '', 'Publish', 'true'

EXEC sp_repldone ....

sp_dboption '', 'Publish', 'false'

SQL SERVER – Checkpoint

2009-06-22T02:13:00.002-05:00

Checkpoint is the process that writes all dirty pages to disk for the current database. Checkpoint process help to minimize time during a later recovery by creating a point where all dirty pages have been written to disk.

Checkpoints can be performed concurrently on the databases
If a checkpoint fails or is interrupted and a recover is required, the database engine can not recover from the “failure” checkpoint, is necessary to recover from the last successful checkpoint.
The database engine performs any modification to database pages in memory (for performance reasons) it cause dirty pages, those dirty pages are not written to disk on each modification performed, those are written to disk just when a checkpoint occur.
When checkpoints occur?

Before a backup, the database engine performs a checkpoint, in order that all the changes to database pages (dirty pages) are contained in the backup.
Stopping the server using any of the following methods, they it cause a checkpoint.

Using Shutdown statement,
Stopping SQL Server through SQL Server configuration, SSMS, net stop mssqlserver and ControlPanel-> Services -> SQL Server Service.
When the “SHUTDOWN WITH NOWAIT” is used, it does not execute checkpoint on the database.

When the recovery internal server configuration is accomplished. This is when the active portion of logs exceeds the size that the server could recover in amount of time defined on the server configuration (recovery internal).
When the transaction log is 70% full and the database is in truncation mode.

The database is in truncation mode, when is in simple recovery model and after a backup statement has been executed.

The time required to perform a checkpoint depends directly of the amount of dirty pages that the checkpoint must write.

[Questions & Answers]

Q: What is Dirty Page?.

A: Dirty pages are the data pages that exists on the buffer cache and have been modified, but not yet written to disk.

Q: What is the checkpoint syntax?

Checkpoint [duration_time]

Duration_time:

Time desired to perform the checkpoint process.
Is specified in seconds.
Must be greater than 0.
When is omitted, the database engine adjust automatically the duration time to minimize the performance impact.
Depending of the duration defined, is the amount of resources that SQL Servers assign to checkpoint process. Ex. If the duration_time is defined in 10 seconds but the checkpoints normally is going to take 20 seconds, SQL Server assigns more resources with performance impact in order to accomplish the 10 seconds defined. In the other hand, if the checkpoint is going to take 5 seconds, SQL Server assigns fewer resources than would be assigned by default. Checkpoint process usually takes more/less time than the specified.

SQL SERVER – Factors that can delay log truncation.

2009-06-18T06:00:00.004-05:00

The process of log truncation frees space in the log files for reuse by transaction log. Exists factors that can delay lo truncation of the active portion by shrinking or truncated the log.

To check what are the factors that delay the log truncation we can check the sys.databases view, look into the columns: log_reuse_wait and log_reuse_wait_desc.

select log_reuse_wait, log_reuse_wait_desc from sys.databases

The following table shows a brief description of log_reuse_wait and log_reuse_wait_desc columns of the sys.databases catalog view.

log_reuse_wait value	log_reuse_wait_desc value	Description
0	NOTHING	Currently there are one or more reusable virtual log files.
1	CHECKPOINT	No checkpoint has occurred since the last log truncation, or the head of the log has not yet moved beyond a virtual log file (all recovery models).
2	LOG_BACKUP	A log backup is required to move the head of the log forward (full or bulk-logged recovery models only). Note: Log backups do not prevent truncation. When the log backup is completed, the head of the log is moved forward, and some log space might become reusable. Is necessary to take a log frequently
3	ACTIVE_BACKUP_OR_RESTORE	A data backup or a restore is in progress (all recovery models). A data backup works like an active transaction, and, when running, the backup prevents truncation.
4	ACTIVE_TRANSACTION	A transaction is active (all recovery models). A long-running transaction might exist at the start of the log backup. In this case, freeing the space might require another log backup. A transaction is deferred (SQL Server 2005 Enterprise Edition and later versions only). A deferred transaction is effectively an active transaction whose rollback is blocked because of some unavailable resource.
5	DATABASE_MIRRORING	Database mirroring is paused, or under high-performance mode, the mirror database is significantly behind the principal database (full recovery model only). .
6	REPLICATION	During transactional replications, transactions relevant to the publications are still undelivered to the distribution database (full recovery model only).
7	DATABASE_SNAPSHOT_CREATION	A database snapshot is being created (all recovery models). This is a routine, and typically brief, cause of delayed log truncation.
8	LOG_SCAN	A log scan is occurring (all recovery models). This is a routine, and typically brief, cause of delayed log truncation.
9	OTHER_TRANSIENT	This value is currently not used.

References.

MSDN. http://msdn.microsoft.com/en-us/library/ms345414.aspx

SQL SERVER – Database Mirroring. Error: 1475, Error: 1478, Error: 1412.

2009-06-18T05:55:00.003-05:00

Problem.

When a database mirroring is configured, some error messages are not completely clear when we got it. The following errors are typical while a database mirroring configuration is performed:

Database Mirroring cannot be enabled because the database may have bulk logged changes that have not been backed up. The last log backup on the principal must be restored on the mirror. (Microsoft SQL Server, Error 1475)

The mirror database has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been restored on the mirror database. (Microsoft SQL Server, Error 1478)

The remote copy of database has not been rolled forwarded to a point in time that is encompassed in the local copy of the transactional log. . (Microsoft SQL Server, Error 1412)

I got the error mentioned above when I try to start the synchronization for database mirroring.

Solution / Fix / Workaround.

To start the synchronization successfully follow the next steps:

1. Principal Instance - Take a full backup and a log backup as well

2. Copy the full/log backups from Principal Instance to Mirror instance

3. Mirror Instance - Restore with NORECOVERY option the full backup

4. Mirror Instance – Apply the log backup

5. Principal Instance - Start synchronization

Enjoy!!

SQL SERVER - Error: 1418. The server network address can not be reached or does not exist. Check the network address name and that the ports for the l

2009-06-18T05:49:00.003-05:00

Problem.

During the past days I was working in a Database Mirroring Configuration and one of the errors I got was the following:

Msg 1418, Level 16, State 1, Line 1

The server network address "%.*ls" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

It was presented when I run the command:

ALTER DATABASE TestMirror SET PARTNER = 'TCP://hostname:5050'

The server network endpoint did not respond because the specified server network address cannot be reached or does not exist.

Solution.

To solution my problem, I accomplished the following points:

1. The ENDPOINTS should have the status started.

select state_desc from sys.database_mirroring_endpoints

2. The system should not be behind a firewall.

3. Make sure that the principal server instance and mirror instance are listening on the right ports and whether are available.

4. Name pipes protocol enabled.

5. The same username and password were used to run SQL Server services on both machines.

6. The usernames to run SQL Server services have NT Auth Access between both machines.

7. Make sure that the username for the SQL Server services has rights on the SQL Server instance.