New SQL Server 2016 Truncate Table With Partitions

I have a table with different partitions and would like to truncate rows in the particular partitions in a table rather than truncating whole table. How can we do this?

This task is fairly easy in sql server 2016. Microsoft introduced a new clause called “With Partitions” in truncate table command.

Let’s see how it works.

For the demonstration i have created a sample database “SamplePartition” with three Filegroups FG1, FG2 and FG3.

USE Master
GO

CREATE DATABASE SamplePartition
ON PRIMARY
(NAME='SamplePartition_1',
FILENAME=
'E:\PartitionDB\FG1\SamplePartition_1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'SamplePartition_2',
FILENAME =
'E:\PartitionDB\FG2\SamplePartition_2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'SamplePartition_3',
FILENAME =
'E:\PartitionDB\FG3\SamplePartition_3.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 )
GO

Now, we have a database with three data files on three different file groups.

 Use SamplePartition
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO
-- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO

1

Create partition function

Use SamplePartition
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2015-01-01', '2016-01-01')
GO

Create Partition Scheme:

Use SamplePartition
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO

Create Partition Table:

Use SamplePartition
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO

Inserting data to partition table:

Use SamplePartition
GO
INSERT INTO SalesArchival (SaleTime, ItemName)
SELECT '2013-03-25','Item1' UNION ALL
SELECT '2014-10-01','Item2' UNION ALL
SELECT '2015-01-01','Item1' UNION ALL
SELECT '2015-08-09','Item3' UNION ALL
SELECT '2015-12-30','Item2' UNION ALL
SELECT '2016-01-01','Item1' UNION ALL
SELECT '2016-05-24','Item3'
GO

Check the data in different partitions

Use SamplePartition
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO

2

With this we have data spread across different partitions

Let us truncate partition number 2

TRUNCATE TABLE SalesArchival
WITH (PARTITIONS(2));

I have truncated the rows in partition 2. Let’s see the result

3.png

There you go! We have truncated the rows in partition 2.

If you would like to truncate the range of partitions you can give the range like this

TRUNCATE TABLE SalesArchival
WITH (PARTITIONS(2 to 3));
Restrictions:

You cannot use TRUNCATE TABLE on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
  • Participate in an indexed view.
  • Are published by using transactional replication or merge replication.

References:

https://msdn.microsoft.com/en-us/library/ms177570.aspx 

How to create quick partition table with example

Hope you like the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

Fix – Tempdb path was wrongly updated

You have Moved the tempdb to other drive and forgot to add the name of the MDF and LDF at the end of the file path, hence the error Error: 5123, Severity: 16, State: 1. Also, unable to restart the SQL Server. Don’t be panic. We can fix this.

In this example I’m going to move the tempdb without providing the file names of data and log.

Find the location of the tempdb files

1

you can also use

 select name,physical_name From sys.master_files where database_id = 2 

Let’s move the tempdb to new location without providing the name of the files

ALTER DATABASE TEMPDB MODIFY FILE (NAME = 'TEMPDEV',
FILENAME = 'C:\Tempdb\')
ALTER DATABASE TEMPDB MODIFY FILE (NAME = 'TEMPLOG', FILENAME = 'C:\Tempdb\')

2

Check the file name paths

3

You can see that the tempdb files are modified in system catalog. Let’s restart the sql server service for change to be effected.  I have restarted the service and trying to connect to sql server but it fails.

4.PNG

Event viewer shows

Event 5123

FCB::Open failed: Could not open file C:\Tempdb\ for file number 1.  OS error: 3(The system cannot find the path specified.).

CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file ‘C:\Tempdb\’.

So now that we can’t get into SQL Server. How do we fix it?

Go to command prompt and change the directory to sql server instance Binn directory, then type sqlsrvr -c -f

In my machine i have used named instance so the command would be

C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Binn>sqlservr -sSQL2014 -c -f

5.PNG

Now the SQL Server instance is running in single user mode. You can connect to SQL Server.

6.PNG

There you go! you have connected to the instance. Check the path of the tempdb by running the below T-SQL

select name,physical_name From sys.master_files
where database_id = 2;go

7.PNG

You can see in the above screenshot that the physical name was not correctly given. It shows C:\Tempdb. Let us modify the physical path.

ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'c:\tempdb\tempdb.mdf')
ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'c:\tempdb\tempdb.ldf')

8.PNG

Here you go! Correct tempdb path.

Once tempdb has been correctly configured , you can close the SQL Server Instance running in the command prompts by pressing Ctrl+C with the window active. Then restart the SQL Service from the sql server configuration manager.

I have restarted the sql server and open the sql server instance normally. There you go! You have fixed the problem.

9.PNG

Note: I have used different parameters

-c : Start as a console application, not as a service.

-m: Start the SQL service in single user mode

-f : Start the SQL service in Minimal configuration mode.

-s : Connect to named instance of sql server

Cheers

Hope you enjoyed the post!

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

Database Mirroring = SQL Server 2016 AlwaysON Basic Availability Groups

Database Mirroring was one of the widely used feature since SQL Server 2005 until Microsoft announces Always On availability groups. Database Mirroring feature has been listed as deprecated ever since Availability groups have been introduced from SQL Server 2012.

SQL Server 2016 BOL states

Database Mirroring feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use Always On Availability Groups instead.

What is Basic Availability Groups in SQL Server 2016?

Basic availability groups works like database mirroring to support the database mirroring in SQL Server 2016. Customer’s who use database mirroring then can still setup basic availability groups. Basic availability groups only works for

  • One availability database
  • No read access on secondary replica
  • No backups on secondary replica
  • No availability group listener
  • No support in an existing availability group to add or remove a replica
  • No support for upgrading a basic availability group to an advanced availability group

Despite these limitations, with a basic availability group you get benefits similar to database mirroring in addition to other features. For each replica, you can choose either synchronous-commit or asynchronous-commit mode as similar to database mirroring.

One good thing about this you can see better performance as it uses new transaction log transfer mechanism.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

Availability Groups Monitoring Alerts

You have setup the availability groups. How would know that the availability groups are running fine?

All the good DBA’s always want to be notified via email when there are any issues related database servers not just only availability groups. In order to do this, you would need a alerts setup so that you can be notified.

What are the useful alerts for Availability Groups?

Here are few useful alerts you can setup on a fly.

  • Availability Groups – Suspended 
 USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'AG Data Movement - Suspended',
 @message_id=35264,
 @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=3,
 @database_name=N'',
 @notification_message=N'',
 @event_description_keyword=N'',
 @performance_condition=N'',
 @wmi_namespace=N'',
 @wmi_query=N'',
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'AG Data Movement - Suspended', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'AG Data Movement - Suspended', @operator_name=N'Robin', @notification_method = 7
GO
 

1

Error Number: 35264 indicates that the availability groups data movement suspended

  • Availability Groups Data Movement – Resumed
USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'AG Data Movement - Resumed',
 @message_id=35265,
 @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=3,
 @database_name=N'',
 @notification_message=N'',
 @event_description_keyword=N'',
 @performance_condition=N'',
 @wmi_namespace=N'',
 @wmi_query=N'',
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'AG Data Movement - Resumed', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'AG Data Movement - Resumed', @operator_name=N'Robin', @notification_method = 7
GO
 

2

Error Number: 35265 indicates the availability groups data movement resumed

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'AG Role change',
 @message_id=1480,
 @severity=0,
 @enabled=1,
 @delay_between_responses=0,
 @include_event_description_in=3,
 @database_name=N'',
 @notification_message=N'',
 @event_description_keyword=N'',
 @performance_condition=N'',
 @wmi_namespace=N'',
 @wmi_query=N'',
 @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'AG Role change', @operator_name=N'DBA', @notification_method = 7
GO
EXEC msdb.dbo.sp_update_notification @alert_name=N'AG Role change', @operator_name=N'Robin', @notification_method = 7
GO

 

 

3.PNG

Error Number 1480 indicates the Availability groups role change

There are many reasons availability groups fails. I have mentioned only few which are important to know.

For a long list of alerts you can configure the alerts based on severity level

Example of these alerts are as below:

Error 976, Severity 14 : The target database, ‘%.*ls’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other

Error 983, Severity 14: Unable to access database ‘%.*ls’ because its replica role is RESOLVING which does not allow connections. Try the operation again later.

Error 3402, Severity 10: The database ‘%ls’ is marked %ls and is in a state that does not allow recovery to be run.

Error 3633, Severity 16: The operating system returned the error ‘%ls’ while attempting ‘%ls’ on ‘%ls’ at ‘%hs'(%d).

Error 3752, Severity 16: The database ‘%.*ls’ is currently joined to an availability group. Before you can drop the database, you need to remove it from the availability group.

Error 19405, Severity 16: Failed to create, join or add replica to availability group ‘%.*ls’, because node ‘%.*ls’ is a possible owner for both replica ‘%.*ls’ and ‘%.*ls’. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try ag

Error 19406, Severity 10: The state of the local availability replica in availability group ‘%.*ls’ has changed from ‘%ls’ to ‘%ls’. The replica state changed because of either a startup, a failover, a communication issue, or a cluster error.

Error 19456, Severity 16: None of the IP addresses configured for the availability group listener can be hosted by the server ‘%.*ls’. Either configure a public cluster network on which one of the specified IP addresses can be hosted, or add another listener IP address

Error 19471, Severity 16: The WSFC cluster could not bring the Network Name resource with DNS name ‘%ls’ online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible.

Error 19476, Severity 16: The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WFSC cluster and validate the network name and IP address with the network administrator

Error 35206, Severity 10: A connection timeout has occurred on a previously established connection to availability replica ‘%ls’ with id [%ls]. Either a networking or a firewall issue exists or the availability replica has transitioned to the resolving role.

Error 35240, Severity 16: Database ‘%.*ls’ cannot be joined to or unjoined from availability group ‘%.*ls’. This operation is not supported on the primary replica of the availability group.

Error 35250, Severity 16: The connection to the primary replica is not active. The command cannot be processed.

Error 41009, Severity 16: The Windows Server Failover Clustering (WSFC) resource control API returned error code %d. The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid.

Error 41015, Severity 16: Failed to obtain the Windows Server Failover Clustering (WSFC) node handle (Error code %d). The WSFC service may not be running or may not be accessible in its current state, or the specified cluster node name is invalid.

Error 41042, Severity 16: The availability group ‘%.*ls’ already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group

Error 41050, Severity 10: AlwaysOn Availability Groups: Waiting for local Windows Server Failover Clustering service to start. This is an informational message only. No user action is required

Error 41052, Severity 10: AlwaysOn Availability Groups: Waiting for local Windows Server Failover Clustering node to start. This is an informational message only. No user action is required.

Error 41066, Severity 16: Cannot bring the Windows Server Failover Clustering (WSFC) resource (ID ‘%.*ls’) online (Error code %d). The WSFC service may not be running or may not be accessible in its current state

Error 41074, Severity 10: AlwaysOn: The local replica of availability group ‘%.*ls’ is preparing to transition to the primary role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required

Error 41091, Severity 10: AlwaysOn: The local replica of availability group ‘%.*ls’ is going offline because either the lease expired or lease renewal failed. This is an informational message only. No user action is required.

Error 41095, Severity 10: AlwaysOn: Explicitly transitioning the state of the Windows Server Failover Clustering (WSFC) resource that corresponds to availability group ‘%.*ls’ to Failed. The resource state is not consistent with the availability group state in the instance of SQL

Error 41131, Severity 10: Failed to bring availability group ‘%.*ls’ online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster

Error 41142, Severity 16: The availability replica for availability group ‘%.*ls’ on this instance of SQL Server cannot become the primary replica. One or more databases are not synchronized or have not joined the availability group, or the WSFC cluster was started in Force Quorum

Error 41152, Severity 16: Failed to create availability group ‘%.*ls’. The operation encountered SQL Server error %d and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command

Error 41158, Severity 16: Failed to join local availability replica to availability group ‘%.*ls’. The operation encountered SQL Server error %d and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved

Error 41160, Severity 16: Failed to designate the local availability replica of availability group ‘%.*ls’ as the primary replica. The operation encountered SQL Server error %d and has been terminated. Check the preceding error and the SQL Server error log for more details

Error 41172, Severity 16: An error occurred while dropping availability group ‘%.*ls’ from Windows Server Failover Clustering (WSFC) cluster and from the local metadata. The operation encountered SQL OS error %d, and has been terminated.

Error 41195, Severity 16: Availability group ‘%.*ls’ failed to process the WSFC lease-renewal command. The local availability replica lease is no longer valid to process the lease renewal command. Availability replica lease expired. This is an informational message only.

Error 41406, Severity 16: The availability group is not ready for automatic failover. The primary replica and a secondary replica are configured for automatic failover, however, the secondary replica is not ready for an automatic failover. Possibly the secondary replica is unavailable.

Error 41414, Severity 16: In this availability group, at least one secondary replica is not connected to the primary replica. The connected state is DISCONNECTED.

All you need is configure the alerts based on severity level. Severity level 16 to 25 is considered as critical.

References:

https://msdn.microsoft.com/en-us/library/dn135328(v=sql.110).aspx

Click to access Troubleshooting%20SQL%20Server%20AlwaysOn.pdf

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

Forgot SQL Server “SA” password? Don’t Worry

Hi Rama,

Need your help. I forgot the “SA” password and I am unable to access to SQL Server via windows/sql authentication.  Is there any way we can recover the “SA” account?

Hello Vamshi,

Yes, we can as long as you are able to RDP and you are a built in administrator to your windows box.

Thanks Rama, I am an admin to the windows box, I am able to RDP to the server. Could you guide me how to recover the “SA” account?

Yes Vamshi. Sure. Here is the way you can do that. Please follow the below steps to reset the “SA” account.

  • Log on to window box and make sure the built in administrator enabled

1.png

  • Go to SQL Server Configuration Manager and add the -m parameter to run the SQL Server in single user mode as show in below screenshot

2.png

3.png4.png

  • Re-start the SQL Server

5

  • Now logon to SQL Server with builtin administrator as below

6.png

  •  Here you go, you are able to connect to SQL Server. Now change the “SA” account password

7.png

  • After that remove the -m parameter from SQL Server configuration manager and restart the sql server

8.png

  • Connect SQL Server  with  new “SA” password. There you go!

910

 

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

 

Powershell Script to Add New Database to Existing Availability Groups

One of the common tasks for DBA to add the new database to existing availability groups. This can be done fairly easy via SSMS. The requirement is how to deploy the new database from Team Foundation Server (TFS) to SQL Server Alway on AG listener and automatically add this database to Availability groups. Here is the script written for this purpose. I have used powershell as this one of the tool which is very useful and easy way to automate from TFS.

 Write-host "Adding Database to existing Availability Groups"

$BackupLocation = "Provide the shared location where both nodes can access"
$dbName = "DatabaseName"
$ListenerName = "AGListener"

$ListReplicaNames = Invoke-Sqlcmd -Query "SELECT name, replica_server_name, role_desc FROM master.sys.availability_replicas as AR
INNER JOIN master.sys.dm_hadr_availability_replica_states as ARS ON AR.replica_id = ARS.replica_id
INNER JOIN master.sys.availability_groups as AG ON AR.group_id = AG.group_id" -ServerInstance $ListenerName
$replica1 = $ListReplicaNames | Where-Object {$_.role_desc -eq "PRIMARY"}
$replica2 = $ListReplicaNames | Where-Object {$_.role_desc -eq "SECONDARY"}
$server1 = $replica1.replica_server_name
$server2 = $replica2.replica_server_name
$AGName = $replica1.name
$dateStamp=get-date

try
{

#Create Backup of DB node 1
$bakPath = $BackupLocation + $dbName + "_" + $dateStamp + ".bak"
$trnPath = $BackupLocation + $dbName + "_" + $dateStamp + ".trn"

set-location "SQLSERVER:\SQL\$server1\Databases"
Write-Host "Backing up $server1 $dbName to $bakPath"
Backup-SqlDatabase -database $dbName -backupFile $bakPath
Write-Host " ... backup complete"

Write-Host "Backing up $server1 $dbName to $trnPath"
Backup-SqlDatabase -Database $dbName -BackupFile $trnPath -BackupAction Log
Write-Host " ... backup complete"

#Resotore Backup of DB in node 2
set-location "SQLSERVER:\SQL\$server2\Databases"
Write-Host "Restoring $server2 $dbName from $bakPath"
Restore-SqlDatabase -Database $dbName -BackupFile $bakPath -ServerInstance $server2 -NoRecovery
Write-Host " ... restoring complete"

Write-Host "Restoring $server2 $dbName from $trnPath"
Restore-SqlDatabase -Database $dbName -BackupFile $trnPath -ServerInstance $server2 -RestoreAction Log -NoRecovery
Write-Host " ... restoring complete"
#Add Database in AvailabilityGroups
Write-Host "Add database: $dbName to availability group: $AGName"
Add-SqlAvailabilityDatabase -Path SQLSERVER:\Sql\$server1\AvailabilityGroups\$AGName\AvailabilityDatabases -Database $dbName
Add-SqlAvailabilityDatabase -Path SQLSERVER:\Sql\$server2\AvailabilityGroups\$AGName\AvailabilityDatabases -Database $dbName
Write-Host " Database $dbname has been added"
}
catch
{
$ErrorMessage = $_.Exception.Message
Write-Host $ErrorMessage
}
finally
{
# This will write the execution time logged in the AddDBToAG.log file
$Time=Get-Date
"This script executed at $Time" | out-file D:\logs\AddDBToAG.log -append
}

 

 

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

SSIS Error – The Binary code for the script is not found

Application team reported me that one of the SSIS package is failing after recent changes. It was failing with message “Binary Code for the script is not found”

I have been asked to troubleshoot the issue. I have opened the SSIS package in SQL Server Data tools and try to run the package from SSDT. Here is the error i have received

1.PNG

The package was using Script Task and i have opened the script tasks to see any coding/syntax errors. I found there was missing semicolon and un compiled code. I have modified the logic in Script task and save the package.

After that i have ran the package and it is successful.

 

Conclusion:

Error “The Binary code for the script is not found” usually occurs when you use Script task in SSIS and save the task with errors. Edit the script task, correct the issues and then execute the package.

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

Query Store – Key feature in SQL Server 2016

As a DBA, how many times you hear about a user reporting that a query suddenly running more slowly than in the past?

SQL Agent job usually takes 3 hours , but this is running more than 12 hours, Could you please investigate?

What would you do in this case?

There are number of common reasons for slow running queries

  • Is the performance problem related to a component other than queries?
  • Are there any other components that might be causing or contributing to performance degradation?
  • If the performance issue is related to queries, which query or set of queries is involved?
  • How do I analyze the performance of a slow-running query?
  • Was the query optimized with useful statistics?
  • Are suitable indexes available?
  • Are there any data or index hot spots?
  • Is the query optimizer provided with the best opportunity to optimize a complex query?
  • If you have a large volume of data, do you need to partition it?

We can use Few of the free Tools like Plan Explorer, Sp_Whoisactive, Sp_BlitzCache to determine the root cause of this. But every method you do, you would require to collect data from various DMV’s, running traces analyze the performance. Most of the cases the root cause is Plan Regression, where the execution plan of a specific query has changed.

How does the query store new feature in SQL Server 2016 helps?

The new Query Store feature in SQL Server 2016 is used to simplify identification of performance issues and to simplify performance troubleshooting for queries caused by changes in execution plans (Plan Regression).

The Query store captures query run-time statistics and providing a dashboard to sort queries by resource consumption. This collection of data is very useful  for DBA to troubleshoot performance issues.

How to Enable Query Store?

Connect to SQL Server Database Engine –> Go to Object Explorer –> Right click on User Database –> Go to properties –> Click on Query Store –>Operation Mode

2.png

Once you enable the Query Store, you can see the query store folder create under database where you have enabled the query store. Expand the query store and check the options like “Regression Queries”, “Overall Resource Consumption” , “Top Resource Consuming Queries”, “Tracked Queries”

3.png

What is “Regressed Queries”?

This is used to review queries that might have regressed because of execution plan changes. The dashboard allows you to view the queries and their plans as well as to select queries based on statistics (total, average, minimum, maximum, and standard deviation) by query metric (duration, CPU time, memory consumption, logical reads, logical writes, and physical reads) for the top 25 regressed queries over the last hour.

8.png

What is Overall Resource Consumption?

This is used to visualize overall resource consumption during the last month in four charts: duration, execution count, CPU time, and logical reads. You have the option to toggle between a chart view and a grid view of the query store data.

5.png

What is Top Resource Consuming Queries?

This is used to review queries in the set of top 25 resource consumers during the last hour. You can filter the queries by using the same criteria available in the Regressed Queries dashboard.

9.png

What is Tracked Queries?

This is used  to monitor a specific query.

10.png

That’s really Cool!

References: 

https://msdn.microsoft.com/en-us/library/dn817826.aspx

https://blogs.msdn.microsoft.com/microsoft_press/2015/12/22/free-ebook-introducing-microsoft-sql-server-2016-mission-critical-applications-deeper-insights-hyperscale-cloud-preview-edition/

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

 

The report server cannot open a connection to the report server database

One of the student asked me while connecting to report server url, he was prompted the error as “The report server cannot open a connection to the report server database”

1.PNG

Looking at the error, it states that the report server database is not accessible by the report server.

Let us see the SQL Server report server configuration settings.

Go to SQL Server Configuration Manager –> Click on SQL Server Reporting Services Configuration Manager –> Click on Database as shown below

2.PNG

Figure 2: 

As you can see in the Figure 2, there were no report server database(s) configured on the instance. Let’s configure the report server database

Click on Change Database and follow the below screenshots

3.PNG

Figure 3:

4.PNG

Figure 4:

5.PNG

Figure 5:

6.PNG

Figure 6:

7.PNG

Figure 7:

8.PNG

Figure 8:

 

9.PNG

Figure 9:

Now, connect the report manager url , Here you go!

11.PNG

Figure 10:

Note: Sometimes you may face permission issue while connecting to report manager url as show in Figure 11

10.PNG

Figure 11:

When you get a permission error as above,

  • open the browser as administrator
  • Go to report manager url –> click on site setting –> Click on Security

12.png

Figure 12:

  • Click on New Role Assignment

13.png

Figure 13:

  • Add user to System Administrator role

14.PNG

  • Close the browser and reconnect the browser. There you go!. You are able to connect report server.

11

Hope you enjoyed the post!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter

 

Backup and Restore Database without non clustered indexes

In this post, I’m going to demonstrate how to take backup of database without considering non clustered indexes.

Before describing how to eliminate index data from backups, it’s first important to
point out that a non clustered index contains simply a copy of the rows from its
associated table, sorted differently by the index’s keys to allow efficient searching
during processing of queries. Eliminating this redundant index data from routine
database backups does not limit the recoverability of the database data from its
backup; indexes can always be re-created later if necessary.

Before we create any tables and indexes, first we will create a database “TestBackup”

 -- Create a testbackup to demonstrate the example
CREATE DATABASE TestBackup;

This will create a database called “TestBackup” on a “Primary Group”. By default, SQL Server creates databases on a primary group unless you specifically mention.

Let’s see on what file-group the database is in

 USE TestBackup;
SELECT name, data_space_id, is_default FROM sys.filegroups; 

1.PNG

Figure 1

By default, unless further filegroups are explicitly created, all the tables created within the database are stored in “Primary” Group.

Let’s continue by creating a simple table

 CREATE TABLE dbo.Employee (
Employee_No INT NOT NULL primary key
, Ename NVARCHAR(50) NOT NULL,
Sal MONEY NOT NULL
); 

To verify that the preceding table is created within the PRIMARY filegroup, execute the
following command:

 SELECT d.*
FROM sys.data_spaces d, sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Employee')
AND d.data_space_id = i.data_space_id
AND i.index_id < 2; 

2.PNG

Figure 2

Now, we will create non clustered index on Employee Table as below

 CREATE NONCLUSTERED INDEX ncix_Employee ON dbo.Employee (Employee_No); 

Verify  on which file group the non clustered index created

 SELECT I.NAME, I.INDEX_ID, I.TYPE_DESC, I.DATA_SPACE_ID
FROM SYS.DATA_SPACES D, SYS.INDEXES I
WHERE I.OBJECT_ID = OBJECT_ID('DBO.EMPLOYEE')
AND D.DATA_SPACE_ID = I.DATA_SPACE_ID
AND I.INDEX_ID > 1; 

3.PNG

Figure 3

You can see that both table and index is on default Primary Group. See the Figure 2 and 3 for the column data_Space_id (=1).  Next,we are going to add a new file group and secondary file for non clustered indexes as below and later move the non clustered index to newly added file group.

 ALTER DATABASE TestBackup
ADD FILEGROUP NCIX_FG;</pre>
ALTER DATABASE TESTBACKUP
ADD FILE (
NAME = N'TESTBACKUP_NCIX_FG1'
, FILENAME = N'C:\RAM\TESTBACKUP_NCIX_FG1.NDF')
TO FILEGROUP NCIX_FG; 

To move the non clustered index from primary group to the dedicated new file group, we would need to create non clustered index with DROP_EXISTING command as below

 CREATE NONCLUSTERED INDEX ncix_Employee ON dbo.Employee (Employee_No)
WITH DROP_EXISTING
ON NCIX_FG; 

Note: DROP_EXISTING option causes the newly created index to be created as the replacement of the existing index, without needing to explicitly drop the existing
index .

With this the new non clustered index with the same name created in new file group.

Next, I will take Backing up only the PRIMARY file group and restore the PRIMARY file group.

 BACKUP DATABASE TestBackup
FILEGROUP = 'PRIMARY'
TO DISK = 'C:\Ram\TestBackup_Primary.bak' 

Restoring the PRIMARY file group backup

 RESTORE DATABASE TestBackup_New
FILEGROUP = 'PRIMARY'
FROM DISK = 'C:\Ram\TestBackup_Primary.bak' WITH RECOVERY, MOVE N'TestBackup' TO N'C:\Ram\TestBackup.mdf',
MOVE N'TestBackup_log' TO N'C:\Ram\TestBackup_log.ldf', NOUNLOAD, STATS = 5 

Note: I have restored on the same instance with different name (TestBackup_New) and move the files to different folders. This will bring the database online and available for querying.

4

Figure 4

5.PNG

Figure 5

You cannot perform insert, update and delete commands because we have only restored the primary group, the underlying non clustered indexes associated with tables are offline. If you attempt to do, you would get the below error

6.PNG

Figure 6

7.PNG

Figure 7

Our main intention in this demonstration is to extract the data from the database without restoring non clustered indexes. This will be useful, if you are testing the data for analysis and you do not want to restore the non clustered indexes due to low disk space on the destination server.

Hope you enjoyed the post!

Happy Weekend!

Cheers

Ramasankar Molleti

LinkedIn: LinkedIn Profile

Twitter: Twitter