Feeds:
Posts
Comments

Archive for the ‘New Features’ Category

Quick one today:

Sequence is nice new feature in Sql Server, it allows a single location to manage IDENTITY-ies. In the past, we looked at one aspect of Sequence, today we’ll cover a bit more ground.

Once the Sequence definition is set, you may need to alter the definition. Below sample scripts show a way to modify some aspects of Sequence definition.

--
--   Create SEQUENCE and play with its DEFINITION
--
CREATE SEQUENCE SampleSequence
	AS BIGINT
	START WITH 0
	INCREMENT BY 1
	MINVALUE -10
    MAXVALUE 200
GO

SELECT name, start_value, maximum_value, current_value
FROM sys.sequences
WHERE name = 'SampleSequence' ;
GO

ALTER SEQUENCE SampleSequence RESTART WITH 2
GO

ALTER SEQUENCE SampleSequence MAXVALUE 300;
GO

SELECT name, start_value, maximum_value, current_value
FROM sys.sequences
WHERE name = 'SampleSequence' ;
GO

 

Hope this helps,
_Sqltimes
Advertisements

Read Full Post »

Quick one today:

Frequently, we run into situations, where we need to put place holders, in a SQL script, that take different values based on some conditions or  settings. Essentially having variables about the script itself (and not the code)

We are taking about variables at the meta-level (script file level, and not the variables in the code itself)

SQLCMD mode within SSMS allows us to set placeholders / variables that will take new values (as you provide) right before executing the script.

First enable SQLCMD mode in SSMS:

SSMS_SQLCMD

In the query window, write some code as below:

--
--  Set variables in a script
--
:SETVAR DatabaseName "master"
:SETVAR TableName "MSreplication_options"

USE $(DatabaseName)
SELECT TOP 10 *
FROM syscolumns

USE $(DatabaseName)
SELECT *
FROM sys.objects
WHERE name LIKE '$(TableName)'
GO

SSMS_SQLCMD_Result

Observations:

Variable replacement occurs both in

USE $(DatabaseName)

and

WHERE name LIKE ‘$(TableName)

  • Having single quotes does not affect variable replacement, as this replacement occurs at the script file level (and node code level — if that makes sense).
  • Since this is a universal replacement capability, it allows you to use variable replacement anywhere in the code without need for special escape characters.
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Sequence numbers is a valuable feature in Sql Server. Ir replaces the use of IDENTITY column property in some scenarios.

In the past we covered similar topic on how to query the current value of IDENTITY column. With SEQUENCEs it is slightly different:

--
--  Query SEQUENCE number details
--
SELECT name, start_value, maximum_value, current_value
FROM sys.sequences
WHERE name = 'TransactionIdSequence' ;
GO

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Interesting one today:

On a production box, the backup jobs have been failing with an interesting and perplexing error. Its says “Not enough disk space“; As you can guess, this is one of those confusing or misleading error messages that’s not what it seems on the surface — Making it worthwhile for a post of its own.

Detailed error message is below:

BACKUP DATABASE DummyDB
TO        DISK = N''
	, DISK = N''
	, DISK = N''
	, DISK = ''
WITH STATS = 1
GO
...
...
...
68 percent processed. 
69 percent processed. 
70 percent processed. 
Msg 3202, Level 16, State 1, Line 1 

Write on "F:\MSSQL\Backup\DummyDB.BAK" failed: 
112(There is not enough space on the disk.) 

Msg 3013, Level 16, State 1, Line 1 
BACKUP DATABASE is terminating abnormally.

This error occurs in both backups with & without compression; And in FULL & Differential backups.

This is a fairly large database, ranging up to 18 TB. So, backups are an ordeal to perform. So, when DIFF backups started failing, it was a bit concerning too.

After attempting several backups on local  & remote storage with plenty of space, a pattern still did not emerge. The only constant is that it fails around 70% completion progress.

At that point, one of  my colleagues (Thanks Michael) pointed out that, as part of backup operation, Sql Server will first run some algorithm that calculates the amount of space needed for the backup file. If the backup drive has enough free space well  and good, if not, it throws this error.

But, as you can guess, we had plenty of free space i.e. peta bytes of free space.

Occasionally, manual backups are successful. So, I’m not sure what is going on, but here is my theory:

At different points, Sql  Server  runs the algorithm (“pre-allocation algorithm”) to determine if there is enough space. Initially it comes back saying “yes” — and the backup proceeds with writing to the backup file; Again a little later, it checks, and it comes back with “Yes”; But at someone on subsequent checks (in our case between 70% to 72% complete), the algorithm decides there is  not enough disk space.

So, turns out there is a TRACE FLAG called 3042 that could disable this algorithm from making any assessments — that way backups could progress to completion.

From  MSDN:

Bypasses the default backup compression pre-allocation algorithm to allow the backup file to grow only as needed to reach its final size. This trace flag is useful if you need to save on space by allocating only the actual size required for the compressed backup. Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Using this trace flag might cause a slight performance penalty (a possible increase in the duration of the backup operation).

Caution: Manually make sure there is plenty of space for backup to complete — since we are disabling the algorithm.

--
-- Disable pre-allocation algorithm
--
DBCC TRACEON (3042)
GO

BACKUP DATABASE DummyDB
TO        DISK = N''
    , DISK = N''
    , DISK = N''
    , DISK = ''
WITH STATS = 1
GO
DBCC TRACEOFF (3042)
GO

Make sure you test this in a non-production environment, before enabling it in production.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

Last time we walked through the T-SQL steps to insert a tracer token to measure latency in replication topology. Today, we’ll look at an alternate method to query tracer token details; a.k.a. MStracer_tokens &  MStracer_history meta tables.

Along with the T-SQL procedures (sys.sp_helptracertokenhistory), Sql Server also provides a way to query the tracer tokens using metadata tables i.e. MStracer_tokens &  MStracer_history.  They keep track of details for each token. Querying them will provide us necessary information.

--
-- Query tracer token tables
--
SELECT	  publication_id
	, agent_id
	, t.publisher_commit
	, t.distributor_commit
	, h.subscriber_commit

FROM MStracer_tokens t
JOIN MStracer_history h
	ON t.tracer_id = h.parent_tracer_id

ORDER BY t.publisher_commit DESC
GO
Query Tracer Token Details

Query Tracer Token Details

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

As part of the series on Replication, we’ll cover Tracer Tokens topic today. Tracer Tokens is one of the techniques to measure the latency in replication topology; It is unique and a powerful way to measure the health and latency of replication set up.

Concept:

In replication, we have a Publisher, Distributor & Subscriber. Publisher has a Publication as the source of data to be replicated to Subscriber(s). Distributor helps in getting data from Publisher to Subscriber. In this topology, data is constantly flowing from Publisher to Distributor and eventually to all the Subscribers. At every step, as data flows through the topology, there is latency. Tracer Tokens helps in measuring this latency at each step.

Tracer tokens are dummy replication traffic inserted at the Publisher; As it flows through the topology, it captures the time it takes to arrive at each step (Distributor) and eventually to the destination (Subscriber). This BoL article has more details on this concept.

T-SQL to Insert Tracer Tokens

There are 4 main T-SQL procedures to managing Tracker Tokens:

  1. Insert tracer token at the Publisher
  2. Get a list of all tracer tokens
  3. Gather details on a given tracer token
  4. Delete tracer token history

Insert Tracer Token at Publisher

Connect to Publisher and point to the publisher database. Then run the ‘sp_posttracertoken‘ procedure with appropriate parameters to insert token into this particular publication. See the example below and the attached result.

@tokenID is the OUTPUT variable, that returns the ID of the token after successfully inserting at the Publisher.

--
-- Insert token at publisher
--
DECLARE @tokenID AS INT

EXEC sp_posttracertoken   @publication		= 'SamplePublication'
						, @tracer_token_id	= @tokenID OUTPUT

SELECT @tokenID AS [TokenID]
GO
Insert Tracer Token

Insert Tracer Token

Get a list of all tracer tokens

In situations, where we do not have the token Id readily available, we could query and get a list of all the tokens inserted with their IDs.

In SSMS, go to Distributor instance and point to the Distributor database and run ‘sys.sp_helptracertokens’ procedure with relevant parameters. See below:

--
-- Get the list of tokens already inserted
--
EXEC sys.sp_helptracertokens @publication	= 'SamplePublication'
							, @publisher	= 'ABC_Instance'
							, @publisher_db = 'SampleDatabase'

GO
List of Tracer Tokens

List of Tracer Tokens

 

Gather details on a given tracer token

Now this is the important procedure that shows us the latency numbers at each step of the replication topology. Open SSMS and point to Distribution instance and point to Distribution database and run ‘sys.sp_helptracertokenhistory’ procedure with pertinent parameters. Se below:

--
-- Query latency number gathered by a particular tracer token
--
EXEC sys.sp_helptracertokenhistory	  @publication = 'SamplePublication'
									, @publisher = 'ABC_Instance'
									, @publisher_db = 'SampleDatabase'
									, @tracer_id = -2147483574
GO
Latencies gathered from Tracer Token

Latencies gathered from Tracer Token

 

Delete tracer token history

Finally, removing the tokens from the metadata. Sql Server provides ‘sp_deletetracertokenhistory’ procedure to delete a given token from a publication. See below:

--
-- Delete a particular token
--
EXEC sp_deletetracertokenhistory  @publication = 'SamplePublication'
								, @publisher = 'ABC_Instance'
								, @publisher_db = 'SampleDatabase'
								, @tracer_id = -2147483573
GO
Delete Tracer Token

Delete Tracer Token

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »