Microsoft Contoso BI Demo Dataset for Retail Industry

    Today, I was working on demo where I wanted to have test dataset for Retail db and after some googing I found MS has it’s own database and here are details –
    The Contoso BI Demo dataset is used to demonstrate DW/BI functionalities across the entire Microsoft Office product family. This dataset includes C-level, sales/marketing, IT, and common finance scenarios for the retail industry and support map integration. In addition, this dataset offers large volumes of transactions from OLTP and well-structured aggregations from OLAP, along with reference and dimension data.
    Steps to restore Contoso BI Demo Database
    You can download from below location –
    2.  After download please run the .exe files and it will ask you to unzip
    3. It will generate ContosoRetailDW.bak file along with Data Dictionary of Contoso BI demo dataset for Retail Industry.xls file
    4. Restore database with below script – you can modify your file

Script

USE [master]
RESTORE DATABASE [ContosoRetailDW] FROM DISK = N’C:\ContosoBIDB\ContosoRetailDW.bak’ WITH FILE = 1, MOVE N’ContosoRetailDW2.0′ TO N’D:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ContosoRetailDW.mdf’, MOVE N’ContosoRetailDW2.0_log’ TO N’D:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\ContosoRetailDW.ldf’, NOUNLOAD, STATS = 5

GO

 

Advertisements

The transaction log for database ‘XXX’ is full due to ‘LOG_BACKUP’

Yesterday, one of my client send me email stating she is not able to run any query against database.

she was getting below error message –

The transaction log for database ‘xxx’ is full due to ‘LOG_BACKUP’

From error message it’s look like transaction log full issue hence I looked for db property and found database is in Full recovery model and growth is restricted.

Hence I thought, let’s change restricted growth to unlimited but it’s not allowing me and was getting same error message.

I thought to perform Full backup and same error message.

I tried to shrink the log file but same error message.

Finally, I tried to perform t-log backup and it went successfully and there after I am able to shrink the file and change db growth to unlimited

Still, I did not get chance to look reason behind this wired error message but if any of you know please feel free to share.

 

 

 

Performance Dash board and performance Baseline

These two articles were published my Microsoft SQL team and it will help you to create Performance dashboard and performance baseline report.

These reports are really cool and will eliminate your performance monitoring need.

https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-dashboard-reports-unleashed-for-enterprise-monitoring/

 https://blogs.msdn.microsoft.com/sql_server_team/sql-server-performance-baselining-reports-unleashed-for-enterprise-monitoring/#comment-2705

Server TCP provider failed to listen on [ ‘any’ 1433]. Tcp port is already in use.

Issue – Today I faced very weird issue; on multi instance SQL Server machine, couple of SQL Services was not starting after Server reboot ( In my case it was SQL 2014 and SQL 2016 ) and every time I was trying to restart my SQL Services, I was getting below error message –

 

1

Solutions – On error message it’s look like some other processes was trying to use SQL default port 1433.

Hence it was not allowing to restart SQL Services with Port 1433.

On my server, we have multiple SQL Services installed ( SQL 2008 to SQL vNext ) hence I started checking if any of these SQL Server have assigned static port 1433.

I start checking assigned port for all SQL Server and I found SQL Server 2014 and SQL Server 2016 was having assigned static port 1433.

In order to resolve the issue, I removed static port entry and restart my SQL Services and it started successfully.

2

34

Alter database set multi_user not working

I got call from one of my client when I was about to leave office and they said their missing critical databases is not coming into multi_user mode.

I was like it’s going to be very easy to resolve.

I tried to run

Alter database <dbname> with multi_user and got below error.

Msg 1205, Level 13, State 68, Server XXXXXXXXXXX, Line 1
Transaction (Process ID 108) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.
Msg 5069, Level 16, State 1, Server XXXXXXXXXXX, Line 1
ALTER DATABASE statement failed.

I tried to run –

Alter database <dbname> with multi_user with rollback immediate but again same error.

I looked to extended event for deadlock and it was showing nothing.

I looked into processes running on server and found deadlock is happening on system processes, so I can’t even KILL those processes.

Meanwhile client said they have bring all the application down but again no luck.

Finally, After doing some research I am able to find the solution and the solution is set the priority of your session high by below t-sql and it will allow you to run your query.

SET DEADLOCK_PRIORITY HIGH

go

Alter database <dbname> set multi_user

You can find more information here –

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-deadlock-priority-transact-sql

Note – This work  around also help you to resolve any deadlock issue in short run.

 

Cannot be configured for publishing and distribution. Publishing and distribution are supported only in SQL Server version 7.0 or later.

I got really wired issue when i was trying to configure Replication on my Enterprise edition 2016 server.

I checked couple of time and everything was looking okay until I realize I was running SSMS 2014.

1

I started SSMS 2016 and it’s fixed my issue.

I know its not very big issue but I thought to share with you all, just to save couple of min.

2

 

The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context

My Development team contacted me and said they are not able to access database  “The server principal “XYZ\USER” is not able to access the database “YourDBName” under the current security context”

when I looked into server server I found –

  1. Login was there and access was granted.
  2. When I looked into database I found user was disable ( Red down arrow mark )

2

Here are two solutions –

  1. Most of the people recommended to recreate use by deleting and reading into database.

But main reason why it’s disabled because user is not having connect permission to database.

  1. if you grant connect permission it will resolve your issue.

use [YourDBName]

go

grant connect to [xyz\user]

1

NetFx3 error while installing SQL Server 2012

Issue – While Installing SQL Server 2012 on windows 2012 server I have received below error, which indicate .Net 3.5 is not installed on this machine.

In windows server 2012 .Net 3.5 do not install by default as it’s come with .Net 4.5.

1

Let’s learn how to install .Net 3.5 on windows 2012 box.

Go to control Panel –> Select Turn windows  features on or off

2

or go to Server Manager –> Add roles and features

3

It launch  new window –> Next

4

select –> “Role-based or feature based installation” –> Next

6

7

Go –> Next –> Next –> Select “.Net Framework 3.5 Feature” –> Next

8

Select –> Specify an alternate source path

9

Find  windows media directory and go to “Source\sxs” path Mine is installed in D:\ so for me it’s “D:\Source\sxs”

11

Provide directory path and click OK.

12

Click on Install –>

13

It will take few minute and then your installation done.

14

Or you can use below command –

dism /online /enable-feature /featurename:NetFx3 /source:D:\sources\sxs

15

Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files.

Problem – I have found VLF size of my database abc is very huge and when i have tried to shrink the file i have received below error –

Could not locate file ‘xy_abc_Log’ for database ‘abc’ in sys.database_files.

Resolution –

While my investigation I have found logical fine name under sys.master_files is different as compare to sys.database_files.

select name from sys.master_files where database_id = db_id(‘abc’)

xy_abc_Data
xy_abc_STAGE_Log

select name from sys.database_files

xy_abc_Data
xy_abc_Log

You will find logical name is different hence you need to update logical file name in sys.master_files table.

use [abc]

go

ALTER DATABASE [abc] MODIFY FILE (NAME=N’xy_abc_Log’, NEWNAME=N’xy_abc_Log’)
GO

How to bypass restart computer error message while installing SQL Patch

If you ever encounter restart computer error message while performing SQL Server patching and you don’t have opportunity\permission to restart your machine.

You can bypass this situation by deleting value from PendingFileRenameOperations key.

Here are the steps which you need to follow –>

  1. To Open Registry Editor. Click Start  ( win + R ) –> Run, input “regedit” and press Enter.
  2. Go to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager, and delete any value in “PendingFileRenameOperations” key.

restart

regedit

rerun.PNG

Replication – Snapshot is not getting generated

Replication issues and resolutions

Today I have encountered very strange issue –

Scenario – Snapshot is not getting generated for newly added articles

or

Error when setting up Transactional Replication via SQL scripts.

Msg  14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Step performed to resolved the issue-

  1. Re snap with reinitialize all subscription option no luck.
  2. Removed the newly added articles and add back and ran the snapshot but NO Luck.
  3. Drop the subscriber and add back and generate the snapshot but no luck ( Most of time it resolve the issue )
  4. Script out publication and drop the publication and when I try to add

While adding Publication i am getting error message –

Error Message –

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533

Specify all articles when subscribing to a publication using concurrent snapshot processing.

Resolution —

There are 2 unofficial workarounds:

(a) You can circumvent the check by specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article

(b) You could change the immediate_sync property in syspublications to 0 (see sp_changepublication).

Other more official workarounds —

Including changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only) and ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.

Before —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’concurrent’,

@retention = 0,

@allow_push = N’true’,

 

After —

use [Publisher db]

exec sp_addpublication @publication = N’Publicatoin name’,

@description = N’Transactional publication of database ”dirtrbuter name” from Publisher ”publisher name”.’,

@sync_method = N’native’,

@retention = 0,

@allow_push = N’true’,

 

  1. I have change the @sync_method = N’concurrent’ to @sync_method = N’native’ and ran the script to create publication.
  1. Ran the snapshot but snapshot not getting generated for newly added articles

7. Remove all article (from console) and added again … and ran the snapshot resolve my issue

Microsoft Best Practice Analyzer

Microsoft Best Practice Analyzer –

Best Practices Analyzer (BPA) is a server management tool that is available in Windows Server® 2008 R2. BPA can help administrators reduce best practice violations by scanning one or more roles that are installed on Windows Server 2008 R2, and reporting best practice violations to the administrator. Administrators can filter or exclude results from BPA reports that they do not have to see. Administrators can also perform BPA tasks by using either the Server Manager GUI, or Windows PowerShell cmdlets.

You will find more details in –
https://technet.microsoft.com/en-us/library/dd759260(v=ws.11).aspx

Installation of BPA – SQL 2012

Prerequisite
• PowerShell V2.0
• Microsoft Baseline Configuration Analyzer V2.0
• User should have admin access on server and SQL Server
o Note – You need to add your individual id or else you will get login error after running BCA.

Steps by Step Installation –
Step 1 – Download and Install the Microsoft Baseline Configuration Analyzer v2 (BCA) (follow default download instructions) – This will install to C:\Program Files\Microsoft Baseline Configuration Analyzer
Download link – https://www.microsoft.com/en-us/download/details.aspx?id=16475
Step 2- Install the Microsoft SQL Server 2012 Best Practices Analyzer (SQL BPA) (follow default download instructions) – This will install to C:\Windows\System32\BestPractices\v1.0\Models\SQL2012BPA\
https://www.microsoft.com/en-us/download/details.aspx?id=29302
Note – You will not able to see any icon for (SQL BPA) as it’s only install executables.

p1

Working –
Step 1 – Open Microsoft Baseline Configuration

p2

Note – If you wanted to connect remote computer, click on Connect to Another Computer.

p3

Step 2 – Select “SQL Server 2012 BPA” from drop down.
Step 3 – Go to Enter Parameters and select parameters you wants to select.

p4

Note – If it’s remote server provide server name in Alternate_Server_to_scan (Only server name not instance name), provide instance name in SQL_Server_Instanace_name fields.
Step 4 – Click on “Start Scan”
Step 5 – Analyze your result.

p6

Issues –
I was configuring BPA 2012 in windows 2012, after installing both BCA and SQL BPA, I was not able to see “SQL Server 2012 BPA” in drop down and to resolve the issue I have performed below steps –

p7

1. Navigate to the directory C:\Windows\System32\BestPractices\v1.0\Models and right-click the SQL2012BPA folder and make a copy.
2. Navigate to C:\ProgramData\Microsoft\Microsoft Baseline Configuration Analyzer 2\Models\ and paste the SQL2012BPA folder in this new location.
(Note – ProgramData is hidden folder which you need to see after checking “Hidden Items” in view of your file explorer)
3. Start the BCA with Run As Administrator and you will now see the SQL Server 2012 BPA listed in the “Select a product” dropdown.

Configuring deadlock and Storing Deadlock events in a table

Configuring deadlock and Storing Deadlock events in a table

SQL Server 2012 and above.

Verification –

Ensure Extended Event “system_health” is running.

SSMS, checking Management -> Extended Events -> Sessions. These should be green arrow which indicate its running.

p1

 

The system_health session writes to two targets, both an event file and the ring buffer:

p2

 

Right click on package0.event_file and click on View Target Data…

p3

p4

Configuring Deadlock Event Id and logging.

Step 1 –

Enable deadlock logging

p5_1

–TO CHECK IF LOGGING IS ENABLED (is_event_logged)
SELECT * FROM sys.messages WHERE language_id = 1033 AND message_id = 1205

–TO ALTER THE LOGGING PARAMETER
EXEC sp_altermessage @message_id = 1205, @parameter = ‘WITH_LOG’ ,@parameter_value = ‘true’
GO

Step 2 –

Create SQL Server Job called “Deadlock Capture Job” and add step with script called “Deadlock_Capture_Script”

SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON

DECLARE @LastDeadLock DATETIME2, @XE_EVENT_FILE_TARGET XML, @XE_FILE_PATH NVARCHAR(260)

SELECT @XE_EVENT_FILE_TARGET = CONVERT(XML, xst.target_data)
FROM sys.dm_xe_sessions xs
JOIN sys.dm_xe_session_targets xst
ON xs.address = xst.event_session_address
WHERE xs.name = ‘system_health’
AND xst.target_name = ‘event_file’

SELECT @XE_FILE_PATH = t.c.value(‘File[1]/@name’, ‘varchar(260)’) FROM @XE_EVENT_FILE_TARGET.nodes(‘/EventFileTarget’) AS t(c)

SELECT CAST(event_data AS XML) AS event_data
INTO #DEADLOCKS_DATA
FROM sys.fn_xe_file_target_read_file(@XE_FILE_PATH, null, null, null)
WHERE object_name = ‘xml_deadlock_report’

IF (SELECT OBJECT_ID(‘DEADLOCKS_Details’)) IS NULL BEGIN
CREATE TABLE DEADLOCKS_Details
(
DeadlockTimestamp DATETIME2 NOT NULL,
DeadlockEvent NVARCHAR(250) NOT NULL,
VictimProcessId NVARCHAR(250) NOT NULL,
Processid_1 NVARCHAR(250) NOT NULL,
WaitResource_1 NVARCHAR(250) NULL,
WaitTime_1 INT NULL,
OwnerId_1 BIGINT NULL,
CurrentDb_1 INT NULL,
LastTranStarted_1 DATETIME2 NULL,
LastBatchStarted_1 DATETIME2 NOT NULL,
Statement_1 NVARCHAR(MAX) NULL,
Client_1 NVARCHAR(250) NULL,
Hostname_1 NVARCHAR(250) NULL,
Loginname_1 NVARCHAR(250) NULL,
Processid_2 NVARCHAR(250) NOT NULL,
WaitResource_2 NVARCHAR(250) NULL,
WaitTime_2 INT NULL,
OwnerId_2 BIGINT NULL,
CurrentDb_2 INT NULL,
TranStarted_2 DATETIME2 NULL,
LastBatchStarted_2 DATETIME2 NOT NULL,
Statement_2 NVARCHAR(MAX) NULL,
Client_2 NVARCHAR(250) NULL,
Hostname_2 NVARCHAR(250) NULL,
Loginname_2 NVARCHAR(250) NULL
)
END

SELECT @LastDeadLock = ISNULL(MAX(DeadlockTimestamp), ‘1900-01-01’) FROM DEADLOCKS_Details

;WITH CTE_EVENTS AS
(
SELECT
c.value(‘@timestamp’, ‘DATETIME2’) AS DeadlockTimestamp,
c.value(‘(@name)[1]’, ‘nvarchar(250)’) AS DeadlockEvent,
c.value(‘(data/value/deadlock/victim-list/victimProcess)[1]/@id’, ‘nvarchar(250)’) AS VictimProcessId,
c.value(‘(data/value/deadlock/process-list/process)[1]/@id’, ‘nvarchar(250)’) AS Processid_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@waitresource’, ‘nvarchar(250)’) AS WaitResource_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@waittime’, ‘int’) AS WaitTime_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@ownerId’, ‘bigint’) AS OwnerId_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@currentdb’, ‘int’) AS CurrentDb_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@lasttranstarted’, ‘datetime2’) AS LastTranStarted_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@lastbatchstarted’, ‘datetime2’) AS LastBatchStarted_1,
c.value(‘(data/value/deadlock/process-list/process/inputbuf)[1]’, ‘nvarchar(max)’) AS Statement_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@clientapp’, ‘nvarchar(250)’) AS Client_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@hostname’, ‘nvarchar(250)’) AS Hostname_1,
c.value(‘(data/value/deadlock/process-list/process)[1]/@loginname’, ‘nvarchar(250)’) AS Loginname_1,
c.value(‘(data/value/deadlock/process-list/process)[2]/@id’, ‘nvarchar(250)’) AS Processid_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@waitresource’, ‘nvarchar(250)’) AS WaitResource_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@waittime’, ‘int’) AS WaitTime_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@ownerId’, ‘bigint’) AS OwnerId_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@currentdb’, ‘int’) AS CurrentDb_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@lasttranstarted’, ‘datetime2’) AS LastTranStarted_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@lastbatchstarted’, ‘datetime2’) AS LastBatchStarted_2,
c.value(‘(data/value/deadlock/process-list/process/inputbuf)[2]’, ‘nvarchar(max)’) AS Statement_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@clientapp’, ‘nvarchar(250)’) AS Client_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@hostname’, ‘nvarchar(250)’) AS Hostname_2,
c.value(‘(data/value/deadlock/process-list/process)[2]/@loginname’, ‘nvarchar(250)’) AS Loginname_2
FROM #DEADLOCKS_DATA
CROSS APPLY event_data.nodes(‘//event’) AS t (c)
)
INSERT DEADLOCKS_Details
SELECT *
FROM CTE_EVENTS
WHERE DeadlockTimestamp > @LastDeadLock

DROP TABLE #DEADLOCKS_DATA

Select the database from Dropdown

p5

Step 3 –

Create deadlock Alerts in SQL Server Agent-

p7

p8