Search results matching tag '2012'

Dedicated Admin Connection (DAC) error in SQL Server 2012 RC0

SankarReddy13 — Sun, 22 Jan 2012 06:00:00 GMT

Dedicated Admin Connection (DAC) in SQL Server doesn’t need any introduction now that it is in the product for almost 6-7 years i.e from the days of SQL Server 2005. Starting from the early builds of SQL Server 2012, there is an error when connecting to DAC in SQL Server DENALI. This bug exists in SQL Server 2012 RC0 build as well but somehow MSFT can’t reproduce it and has been closed as won’t fix. To some extent I agree that this bug is NOT a big deal in the large scheme of things and am writing this to bring some awareness to the issue in case you happen to stumble upon it when using DAC in a real world scenario.

The bug is if you connect to a “New Query” window and try to use the same query window to change the connection to use DAC then it throws an error message that failed to connect to DAC. The end result is DAC connection succeeds and works fine but throws an error message which can be confusing. If you are using DAC to solve a real issue on a production box and at that time stumbling on this bug can be really frustrating and this can compound to stress you are already on. Knowing about this bug and the work around, may help to avoid getting into that situation.

Steps to reproduce:

Go to File | New | Database Engine Query | and connect to server with a regular connection.

Step 1 for DAC error in SQL Server 2012 RC0

Change Connection | use ADMIN: to connect to DAC

Step 2 DAC Error in SQL Server 2012 RC0
Shows error message below

DAC error in SQL Server 2012 RC0

Click ok and use DAC connection without any issues. How do I know that DAC connection succeeded? One can use the below query to find if DAC connection is being used or NOT. And also there is the information at the bottom of the screen as well.

Step 4 DAC Error in SQL Server 2012 RC0

SELECT
	c.session_id
	, s.login_name
	, c.connect_time
	, c.last_read
	, c.last_write
	, s.host_name
	, c.client_net_address
FROM sys.dm_exec_connections c
    JOIN sys.endpoints e
        ON c.endpoint_id = e.endpoint_id
    JOIN sys.dm_exec_sessions s
        ON s.session_id = c.session_id
WHERE e.is_admin_endpoint = 1

Proof that DAC connection succeeded

Workaround:

Until the bug is fixed, always use a new query window to connect to DAC directly. Don’t use an existing query window for a DAC connection.

If you can reproduce the issue, and if you think its important then vote and/or add comments for the bug below.

DAC Connection throws error

Republished with author's permission. See the original post here.

DIRTY_PAGE_POLL wait type in SQL Server 2012

SankarReddy13 — Sun, 15 Jan 2012 06:00:00 GMT

Looking at wait types in SQL Server is a great way to get some insight into how SQL Server is operating under the current load. Although wait types in SQL Server are around from the days of SQL Server 2000 (may be earlier), it has improved significantly from SQL Server 2005. New wait types are being added almost in every new SQL Server versions, providing us with great insight to how SQL Server is performing. This post is to understand the DIRTY_PAGE_POLL wait type in SQL Server 2012.

DIRTY_PAGE_POLL is a new wait type added in SQL Server 2012. If you see high values for this wait type on your server, should you be concerned? What is this wait type referring to? Let’s get into the details of what functionality of SQL Server does this refer to.

Before SQL Server 2012, the recovery time of the database(s) after an unexpected shutdown or crash used to depend on the ‘recovery interval’ sp_configure option which in turn was responsible for issuing “automatic checkpoints”. The timing of these automatic checkpoints used to vary a lot depending on the activity on the database and the algorithm had some issues as well to determine how frequently the checkpoint should be run to support the recovery time accurately. There is a myth that checkpoint will run every minute and that is NOT true and its a story for another post.

In SQL Server 2012, a new concept known as “Indirect checkpoints” is added which allows to control the recovery time much more accurately compared to previous versions. This is controlled at the database level unlike the old option in previous versions was at server level. The option is called “TARGET_RECOVERY_TIME” and is set using ALTER DATABASE command. For more details refer BOL at http://msdn.microsoft.com/en-us/library/hh403416(v=sql.110).aspx.

When you use “Indirect Checkpoint”, the background thread constantly looks for dirty pages to be written to disk at a much more aggressive rate (compared to automatic checkpoints) i.e to support the recovery time interval that is set using the “TARGET_RECOVERY_TIME”. Even if you are NOT using the “Indirect checkpoint”, the background thread may be waiting to poll for dirty pages. So, it is possible to see high values for this wait type especially when you aren’t using “Indirect checkpoint” feature. It is a benign wait type and shouldn’t be any concern if you happen to see high values for this wait type on your systems.

So the next question is should we change all databases to use “Indirect checkpoint” when upgraded to SQL Server 2012? While “Indirect checkpoint” has great promise, you should rely on extensive testing for your workload before using this feature as it changes the how much data is being written to the disk constantly.

Republished with author's permission. See the original post here.

SQL Server 2012 FileTable – Part 1

Geniiius — Tue, 03 Jan 2012 06:00:00 GMT

In a series of blog posts we will have a look at the new SQL Server 2012 table type called FileTable. This first blog post will be a simple getting started – how to create a FileTable and how to dump files into the folder and do some simple file manipulation.

A SQL Server FileTable is a special table where you can store directory and files – that’s not special I know, the special thing is that you can access these files and directories from windows applications as if they were stored in the file system.

The data stored in the FileTable is exposed to windows through a windows share, and via this share it is possible to gain non-transactional access to the data.

If you create or change a file through the windows share the command is intercepted by a SQL Server component and the changes are applied to the corresponding data in the FileTable.

Read more about FileTables in BOL

Let’s create a demo database with a FILESTREAM filegroup, I’ll be setting the NON_TRANSACTED_ACCESS to FULL this gives me full non-transactional access to the share, this option can be changed later if you want to do that. Here is the code:

CREATE DATABASE MyFileTableTest
ON  PRIMARY
(
    NAME = N'MyFileTableTest',
    FILENAME = N'C:\FileTable\MyFileTableTest.mdf'
),
FILEGROUP FilestreamFG CONTAINS FILESTREAM
(
    NAME = MyFileStreamData,
    FILENAME= 'C:\FileTable\Data'
)
LOG ON
(
    NAME = N'MyFileTableTest_Log',
    FILENAME = N'C:\FileTable\MyFileTableTest_log.ldf'
)
WITH FILESTREAM
(
    NON_TRANSACTED_ACCESS = FULL,
    DIRECTORY_NAME = N'FileTable'
)

That’s the database done, now let’s create the magic table – that is simple done with the “AS FileTable” keyword. When creating a FileTable there is only two options that you can change, everything else is out of the box. Let’s have a look at how I created the table:

USE MyFileTableTest
go

CREATE TABLE MyDocumentStore AS FileTable
WITH
(
    FileTable_Directory = 'MyDocumentStore',
    FileTable_Collate_Filename = database_default
);
GO

If we take a look at the GUI after creating the table, we can see that the table is created under the new folder called FileTables

that is not the only new thing, if we right click at the table that we just created we will see an option called “Explore FileTable Directory” – click that and a windows opens with the content of your FileTable. If you drop a file into this folder it will be accessible from SSMS, with a simple SELECT from the MyDocumentStore.

Here is how the folder looks:

And if you query the table you get the following: (This query only shows a few of the columns off the table)

Now you can do file manipulation with SSMS, something like renaming the files is just an update statement – easy peasy .

UPDATE MyDocumentStore
SET name = 'Geniiius.txt'
WHERE stream_id = '05A9F338-CF32-E111-BB43-080027F9EB26'

This is the end of the first blog post in this series – stay tuned new posts will be online very soon.

Republished with author's permission. See the original post here.

CONCAT() – SQL Server 2012

Geniiius — Tue, 29 Nov 2011 06:00:00 GMT

With SQL Server 2012 a new string function is born – CONCAT(). The output is a string, as the result of the concatenation of two or more strings – if the input is another type than string a conversion will be done implicit. The CONCAT() function takes between 2 and 254 parameters.

Here is how the syntax looks:

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Read the full BOL documentation here. Let’s have a look at code use cases.

/* The Simple one */

SELECT CONCAT('AGF','Vinder','Guld','I','2012') as GeniiiusOutput

Output: AGFVinderGuldI2012

/* Handling non varchar datatypes */

DECLARE @text1 varchar(10) = 'AGF'
DECLARE @text2 varchar(10) = 'Vinder'
DECLARE @text3 varchar(10) = 'Guld'
DECLARE @text4 varchar(10) = 'I'
DECLARE @Num1 int = 2012

SELECT CONCAT(@text1, @text2, @text3, @text4, @Num1) as GeniiiusOutput

Output: AGFVinderGuldI2012

/* Handling null values */

DECLARE @text1 varchar(10) = 'AGF'
DECLARE @text2 varchar(10) = 'Vinder'
DECLARE @text3 varchar(10) = null
DECLARE @text4 varchar(10) = 'I'
DECLARE @Num1 int = 2012

SELECT CONCAT(@text1, @text2, @text3, @text4, @Num1) as GeniiiusOutput

Output: AGFVinderI2012

This is for sure a feature that I have been missing, and already now it has become my new best friend.

@Geniiiuscom