SQL SERVER – Server Configuration Options

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;

or

sp_configure 'show advanced options',1

GO

reconfigure

Go

sp_configure

go

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)

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

GO

USE DB_TestLSN

GO

--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.

1. Create DB
2. Backup Full Database (BkpDBFULL1)
3. Backup Log (BkpLOG1)
4. Backup Full Database (BkpDBFULL2)
5. Restore BkpDBFULL2
6. 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): ?

A:

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)

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

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>

Go

sp_changedbowner ''

Go

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

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'