Query to get table statistics details

-> Below query can be used to get the details of statistics on a table.

select distinct object_name(a.object_id) as Table_name,a.name StatsName,a.stats_id,a.auto_created,a.user_created,c.name as Stats_Column from sys.stats a
INNER JOIN sys.stats_columns b on a.object_id=b.object_id and a.stats_id=b.stats_id
INNER JOIN sys.columns c on b.object_id=c.object_id and b.column_id=c.column_id
where a.object_id=object_id('SomeTableName')

-> Removing the where clause will display all statistics from all tables on the database.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

Advertisements

Optimizing queries using XML in SQL Server

-> I recently worked on tuning a query that scans through XML data.
-> Below is the query,


set statistics io on
SELECT t.COL1 FROM Table1 t WHERE COL3=N'1' or COL3=N'10' or COL3=N'102'
or COL3=N'103' or COL3=N'104' or COL3=N'105' or COL3=N'106' or COL3=N'11'
or COL3=N'12' or COL3=N'120' or COL3=N'121' or COL3=N'122' or COL3=N'123'
or COL3=N'124' or COL3=N'125' or COL3=N'126' or COL3=N'127' or COL3=N'129'
or COL3=N'13' or COL3=N'130' or COL3=N'131' or COL3=N'132' or COL3=N'133'
or COL3=N'134' or COL3=N'135' or COL3=N'136' or COL3=N'137' or COL3=N'138'
or COL3=N'139' or COL3=N'14' or COL3=N'141' or COL3=N'143' or COL3=N'144'
or COL3=N'145' or COL3=N'146' or COL3=N'147' or COL3=N'148' or COL3=N'149'
or COL3=N'15' or COL3=N'150' or COL3=N'151' or COL3=N'152' or COL3=N'153'
or COL3=N'154' or COL3=N'155' or COL3=N'156' or COL3=N'157' or COL3=N'158'
or COL3=N'159' or COL3=N'16' or COL3=N'160' or COL3=N'161' or COL3=N'162'
or COL3=N'163' or COL3=N'164' or COL3=N'166' or COL3=N'167' or COL3=N'168'
or COL3=N'169' or COL3=N'17' or COL3=N'170' or COL3=N'171' or COL3=N'173'
or COL3=N'174' or COL3=N'175' or COL3=N'176' or COL3=N'177' or COL3=N'178'
or COL3=N'179' or COL3=N'18' or COL3=N'184' or COL3=N'185' or COL3=N'186'
or COL3=N'187' or COL3=N'188' or COL3=N'189' or COL3=N'19' or COL3=N'190'
or COL3=N'191' or COL3=N'192' or COL3=N'193' or COL3=N'194' or COL3=N'195'
or COL3=N'196' or COL3=N'197' or COL3=N'198' or COL3=N'199' or COL3=N'2'
or COL3=N'20' or COL3=N'200' or COL3=N'201' or COL3=N'202' or COL3=N'203'
or COL3=N'204' or COL3=N'205' or COL3=N'206' or COL3=N'207' or COL3=N'208'
or COL3=N'209' or COL3=N'21' or COL3=N'210' or COL3=N'211' or COL3=N'212'
or COL3=N'213' or COL3=N'214' or COL3=N'215' or COL3=N'216' or COL3=N'217'
or COL3=N'218' or COL3=N'22' or COL3=N'227' or COL3=N'228' or COL3=N'229'
or COL3=N'23' or COL3=N'230' or COL3=N'233' or COL3=N'234' or COL3=N'235'
or COL3=N'236' or COL3=N'237' or COL3=N'238' or COL3=N'239' or COL3=N'24'
or COL3=N'240' or COL3=N'241' or COL3=N'242' or COL3=N'25' or COL3=N'26'
or COL3=N'27' or COL3=N'28' or COL3=N'29' or COL3=N'3' or COL3=N'30'
or COL3=N'309' or COL3=N'31' or COL3=N'32' or COL3=N'33' or COL3=N'34'
or COL3=N'35' or COL3=N'36' or COL3=N'37' or COL3=N'38' or COL3=N'39'
or COL3=N'4' or COL3=N'40' or COL3=N'41' or COL3=N'42' or COL3=N'43'
or COL3=N'44' or COL3=N'45' or COL3=N'46' or COL3=N'47' or COL3=N'48'
or COL3=N'49' or COL3=N'5' or COL3=N'51' or COL3=N'52' or COL3=N'53'
or COL3=N'54' or COL3=N'55' or COL3=N'56' or COL3=N'57' or COL3=N'58'
or COL3=N'59' or COL3=N'6' or COL3=N'62' or COL3=N'64' or COL3=N'66'
or COL3=N'67' or COL3=N'68' or COL3=N'69' or COL3=N'7' or COL3=N'70'
or COL3=N'71' or COL3=N'72' or COL3=N'73' or COL3=N'74' or COL3=N'76'
or COL3=N'77' or COL3=N'78' or COL3=N'79' or COL3=N'8' or COL3=N'80'
or COL3=N'81' or COL3=N'82' or COL3=N'83' or COL3=N'84' or COL3=N'85'
or COL3=N'86' or COL3=N'87' or COL3=N'88' or COL3=N'89' or COL3=N'9'
or COL3=N'90' or COL3=N'91' or COL3=N'92' or COL3=N'93' or COL3=N'94'
or COL3=N'95' or COL3=N'96' or COL3=N'97' or COL3=N'98'
ORDER BY COL6 ,COL5
,ISNULL(COL2.value('(/row/c9)[1]',  'VARCHAR(256)'), CHAR(1)) ,COL1
set statistics io off

-> Object Table1 definition,


CREATE TABLE [dbo].[Table1](
            [COL1] [varchar](255) NOT NULL,
            [COL2] [xml] NULL,
            [COL3]  AS ([dbo].[udTable1_C4]([COL2])) PERSISTED,
            [COL4]  AS ([dbo].[udTable1_C2]([COL2])) PERSISTED,
            [COL5]  AS ([dbo].[udTable1_C8]([COL2])) PERSISTED,
            [COL6]  AS ([dbo].[udTable1_C1]([COL2])) PERSISTED,
            [COL7]  AS ([dbo].[udTable1_C3]([COL2])) PERSISTED,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
            [COL1] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-> Execution plan for the above query,
Blog46_1

-> Checking the “Table Valued Function” operator from the execution plan,
Blog46_2

-> We are spending most of our time reading the XML data from Table Table1.

(3227544 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Table1’. Scan count 1, logical reads 2097889, physical reads 0, read-ahead reads 0, lob logical reads 424529, lob physical reads 0, lob read-ahead reads 36.

-> Lets introduce a new computed column using below code to avoid the costly read on XML column,


CREATE FUNCTION [dbo].[udTable1_C9](@COL2 XML)
RETURNS nvarchar(20)
WITH SCHEMABINDING
BEGIN
RETURN ISNULL(@COL2.value('(/row/c9)[1]',  'VARCHAR(256)'), CHAR(1))
END
GO

ALTER TABLE dbo.Table1 ADD COL8  AS ([dbo].[udTable1_C9]([COL2])) PERSISTED
GO

CREATE INDEX IX_TABLE1_C3_C6_C5_C8_C1 ON Table1 (COL3) INCLUDE (COL6,COL5,COL8,COL1)
GO

-> Executing below query. The only change to this query is that we are using the computed column in order by.


set statistics io on
SELECT t.COL1 FROM Table1 t WHERE COL3=N'1' or COL3=N'10' or COL3=N'102'
or COL3=N'103' or COL3=N'104' or COL3=N'105' or COL3=N'106' or COL3=N'11'
or COL3=N'12' or COL3=N'120' or COL3=N'121' or COL3=N'122' or COL3=N'123'
or COL3=N'124' or COL3=N'125' or COL3=N'126' or COL3=N'127' or COL3=N'129'
or COL3=N'13' or COL3=N'130' or COL3=N'131' or COL3=N'132' or COL3=N'133'
or COL3=N'134' or COL3=N'135' or COL3=N'136' or COL3=N'137' or COL3=N'138'
or COL3=N'139' or COL3=N'14' or COL3=N'141' or COL3=N'143' or COL3=N'144'
or COL3=N'145' or COL3=N'146' or COL3=N'147' or COL3=N'148' or COL3=N'149'
or COL3=N'15' or COL3=N'150' or COL3=N'151' or COL3=N'152' or COL3=N'153'
or COL3=N'154' or COL3=N'155' or COL3=N'156' or COL3=N'157' or COL3=N'158'
or COL3=N'159' or COL3=N'16' or COL3=N'160' or COL3=N'161' or COL3=N'162'
or COL3=N'163' or COL3=N'164' or COL3=N'166' or COL3=N'167' or COL3=N'168'
or COL3=N'169' or COL3=N'17' or COL3=N'170' or COL3=N'171' or COL3=N'173'
or COL3=N'174' or COL3=N'175' or COL3=N'176' or COL3=N'177' or COL3=N'178'
or COL3=N'179' or COL3=N'18' or COL3=N'184' or COL3=N'185' or COL3=N'186'
or COL3=N'187' or COL3=N'188' or COL3=N'189' or COL3=N'19' or COL3=N'190'
or COL3=N'191' or COL3=N'192' or COL3=N'193' or COL3=N'194' or COL3=N'195'
or COL3=N'196' or COL3=N'197' or COL3=N'198' or COL3=N'199' or COL3=N'2'
or COL3=N'20' or COL3=N'200' or COL3=N'201' or COL3=N'202' or COL3=N'203'
or COL3=N'204' or COL3=N'205' or COL3=N'206' or COL3=N'207' or COL3=N'208'
or COL3=N'209' or COL3=N'21' or COL3=N'210' or COL3=N'211' or COL3=N'212'
or COL3=N'213' or COL3=N'214' or COL3=N'215' or COL3=N'216' or COL3=N'217'
or COL3=N'218' or COL3=N'22' or COL3=N'227' or COL3=N'228' or COL3=N'229'
or COL3=N'23' or COL3=N'230' or COL3=N'233' or COL3=N'234' or COL3=N'235'
or COL3=N'236' or COL3=N'237' or COL3=N'238' or COL3=N'239' or COL3=N'24'
or COL3=N'240' or COL3=N'241' or COL3=N'242' or COL3=N'25' or COL3=N'26'
or COL3=N'27' or COL3=N'28' or COL3=N'29' or COL3=N'3' or COL3=N'30'
or COL3=N'309' or COL3=N'31' or COL3=N'32' or COL3=N'33' or COL3=N'34'
or COL3=N'35' or COL3=N'36' or COL3=N'37' or COL3=N'38' or COL3=N'39'
or COL3=N'4' or COL3=N'40' or COL3=N'41' or COL3=N'42' or COL3=N'43'
or COL3=N'44' or COL3=N'45' or COL3=N'46' or COL3=N'47' or COL3=N'48'
or COL3=N'49' or COL3=N'5' or COL3=N'51' or COL3=N'52' or COL3=N'53'
or COL3=N'54' or COL3=N'55' or COL3=N'56' or COL3=N'57' or COL3=N'58'
or COL3=N'59' or COL3=N'6' or COL3=N'62' or COL3=N'64' or COL3=N'66'
or COL3=N'67' or COL3=N'68' or COL3=N'69' or COL3=N'7' or COL3=N'70'
or COL3=N'71' or COL3=N'72' or COL3=N'73' or COL3=N'74' or COL3=N'76'
or COL3=N'77' or COL3=N'78' or COL3=N'79' or COL3=N'8' or COL3=N'80'
or COL3=N'81' or COL3=N'82' or COL3=N'83' or COL3=N'84' or COL3=N'85'
or COL3=N'86' or COL3=N'87' or COL3=N'88' or COL3=N'89' or COL3=N'9'
or COL3=N'90' or COL3=N'91' or COL3=N'92' or COL3=N'93' or COL3=N'94'
or COL3=N'95' or COL3=N'96' or COL3=N'97' or COL3=N'98'
ORDER BY COL6 ,COL5 ,ISNULL(COL8, CHAR(1)) ,COL1
set statistics io off

-> Below execution plan uses the index created. It no longer performs a read on the XML column,
Blog46_3

-> Execution stats below for the query,

(3227544 row(s) affected)
Table ‘Table1’. Scan count 202, logical reads 20644, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-> Reads are greatly reduced and the query performs comparably good. All good till now as far as scalar promotion is concerned. Lets look at issues that this computed column can cause,

1) All queries using Table1 will be serial after computed column with scalar function is defined on Table1.
2) Parallel rebuild on this table will not be possible if SQL Server Enterprise edition is used.
3) More space used by the computed column and indexes.
4) Many more….

-> It is very important to test with important workloads before using computed columns in production.

-> In my case the script cannot be rewritten and this query is very important for our business. Further Table1 had computed columns with scalar function before I created this, so went with this choice.

-> I would have definitely thought twice if this was the first computed column for Table1.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights

Finding Undistributed commands in Transactional replication

-> Below query can be used to find undistributed command count in transactional replication.


set nocount on
declare @publisher varchar(255)
declare @publisher_db varchar(255)
declare @Subscriber varchar(255)
declare @Subscriber_db varchar(255)
declare @subscription_type int
declare @subscriber_id int
set @publisher = ''
set @publisher_db = ''
set @Subscriber = ''
set @subscriber_db = ''
set @subscriber_id = (select distinct b.subscriber_id from master.dbo.sysservers a INNER JOIN MSsubscriptions b on a.srvid = b.subscriber_id where a.srvname=@Subscriber)
Declare @PublicationName VARCHAR(1000)
DECLARE CheckUndistributedcmdinbadway_cursor CURSOR FOR
select distinct b.publication,a.subscription_type from MSsubscriptions a inner join MSpublications b on a.publication_id = b.publication_id where a.subscriber_id = @subscriber_id
OPEN CheckUndistributedcmdinbadway_cursor
FETCH NEXT FROM CheckUndistributedcmdinbadway_cursor INTO @PublicationName,@subscription_type
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Publication: '+@PublicationName
EXECUTE sp_replmonitorsubscriptionpendingcmds
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication =@PublicationName,
@subscriber =@Subscriber,
@subscriber_db =@subscriber_db,
@subscription_type =@subscription_type
print '------------------------------------------------------------------------'
print ' '
FETCH NEXT FROM CheckUndistributedcmdinbadway_cursor INTO @PublicationName,@subscription_type
END
CLOSE CheckUndistributedcmdinbadway_cursor
DEALLOCATE CheckUndistributedcmdinbadway_cursor

 

-> Before running this query, select “Results to Text (CTRL + T)” from SQL Server management studio and execute it.

-> The query is not the best way to get the undistributed command count in transactional replication. But helped me solve my purpose. Can be useful for someone else.

-> The query will be blocked if distribution cleanup is running.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Logshipping metadata table log_shipping_monitor_secondary is not getting updated

-> I worked on a datacentre migration recently. One of the server involved in the migration was a Logshipping Primary and Monitor server. Logshipping primary and Monitor server were in new datacentre, but the logshipping secondary was at old datacentre.

-> After the migration, all of the logshipping jobs like Backup, copy and restore were working as expected. But the LS_Alert job was failing with below error,

Executed as user: <LOGIN>. The log shipping primary database <ServerName>.<DatabaseName> has backup threshold of 45 minutes and has not performed a backup log operation for 320 minutes. Check agent log and logshipping monitor information. [SQLSTATE 42000] (Error 14420). The step failed.

-> I checked the metadata tables msdb..log_shipping_monitor_primary and msdb..log_shipping_monitor_secondary to get more details.

-> Metadata table msdb..log_shipping_monitor_primary was updated recently. But msdb..log_shipping_monitor_secondary table was not updated recently. The time it was last updated was the time when we moved the Logshipping Primary and monitor server to new datacentre.

-> I started a profiler trace on the primary and monitor server without much luck.

-> I then started a profiler trace on the secondary server and found below messages,

OLE DB provider “SQLNCLI10” for linked server “LOGSHIPLINK_<Monitorserver>_-1140148506” returned message “Login timeout expired”.
OLE DB provider “SQLNCLI10” for linked server “LOGSHIPLINK_<Monitorserver>_-1140148506” returned message “A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.”.

-> I tried connecting the monitor server using SQL Server management studio from secondary server without much luck. I tried pinging the monitor server from secondary server and got the below,

C:\Users>ping <MonitorServer>
Pinging <MonitorServer> [192.152.0.3] with 32 bytes of data:
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.
Reply from 10.0.0.9: TTL expired in transit.

Ping statistics for 192.152.0.3:
Packets: Sent = 4, Received = 4, Lost = 0 (0% loss),

-> Updated my network team and they corrected it. Tried connecting the monitor server using SQL Server management studio from secondary server and this time it worked.

-> Metadata table msdb..log_shipping_monitor_secondary started getting updated normally.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

 

Restore a database backup to an Azure Managed Instance

1) Creating Azure storage account and upload backup file

-> Click “Create a resource”. Select “Storage” and click “Storage Account”.

Blog45_1.PNG

-> Complete “Create storage account” and click “Create”.

Blog45_2.PNG

-> Once the Storage Account deployment completes. Click on Resource group JB_RG, select jbmistorage. Under settings, click “Shared Access Signature”. Check the details and modify accordingly to your needs. I am leaving the default and clicking on”Generate SAS and connection string”.

Blog45_3.PNG

-> Copy the SAS Token and BLOB Service SAS URL and place it in carefully,

Blog45_4.PNG

-> Under “BLOB SERVICE”, click containers and Click “+ Container”.

Blog45_5.PNG

-> Provide the required details and click ok.

Blog45_6.PNG

-> Click on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on properties.

Blog45_7.PNG

-> Copy the URL.

Blog45_8.PNG

-> Once the URL is copied, go back to the container page by clicking  on Resource group JB_RG, select jbmistorage. Click Conatiners under “BLOB SERVICE”. Click on “jbmibackupcontainer”. Click on “Upload”. Select the backup file you want to upload and click “Upload”. Wait for the upload to complete.

Blog45_9.PNG

-> Upload of backup file in progress.

Blog45_10.PNG

-> Upload completed.

Blog45_11.PNG

2) Restore the database JB_AQ on the Managed instance.

-> Create a SAS Credential using below query,

CREATE CREDENTIAL [https://<storage_account_name>.blob.core.windows.net/<container>]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘<shared_access_signature_key_with_removed_first_?_symbol>’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

-> When you click on the container “jbmibackupcontainer”. You will be able to find the backup file uploaded.

WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’ – Will remain as it is.

<shared_access_signature_key_with_removed_first_?_symbol> – Replace this with the SAS Token that was stored earlier. Please note that you should remove the leading ? from the SAS Token.

Blog45_18.PNG

-> My command is as below,

CREATE CREDENTIAL [https://jbmistorage.blob.core.windows.net/jbmibackupcontainer]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘sv=2017-11-09&ss=b&srt=sco&sp=rwdlac&se=2018-06-13T09:33:37Z&st=2018-06-13T01:33:37Z&spr=https&sig=AWlMTj6MZ0M4ictY2nBJ4%2BfVr0kx0RWfpFU1xlJ76FU%3D’

-> Execute the query,

Blog45_16.PNG

-> Below query checks the SAS Credential and backup validity,

RESTORE FILELISTONLY FROM URL =
https://<storage_account_name&gt;.blob.core.windows.net/<container>/<Backup_File>.bak’

https://<storage_account_name&gt;.blob.core.windows.net/ – Click on “Resource group” JB_RG and select “jbmistorage”. Copy the “Blob Service Endpoint” as indicated below and replace “https://<storage_account_name&gt;.blob.core.windows.net/” with the copied value.

Blog45_12.PNG

<container>- Click on “Resource group” JB_RG and select “jbmistorage”. Select “Container” under “BLOB SERVICE”. Copy the container name on the Right side. The container name in my case is “jbmibackupcontainer”.

Blog45_13.PNG

<Backup_File>.bak – Replace it with the backup file we uploaded. When you click on the container “jbmibackupcontainer” . You will be able to find the backup file uploaded.

Blog45_15.PNG

-> My command is as below,

RESTORE FILELISTONLY FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

-> Execute the query,

Blog45_17.PNG

-> Use the below query to restore the database JB_AQ in Managed Instance.

RESTORE DATABASE [JB_AQ] FROM URL =
https://jbmistorage.blob.core.windows.net/jbmibackupcontainer/JB_AQP_MI.bak&#8217;

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Physical_database_name column in sys.databases

-> I was trying to rename a database on the Azure managed Instance and got the below expected error,

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to rename JB_MI_1. (ObjectExplorer)
——————————
ADDITIONAL INFORMATION:
Rename failed for Database ‘JB_MI’. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=14.0.17254.0+((SSMS_Rel_17_4).180502-0908)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Rename+Database&LinkId=20476
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Database ‘8b680fc7-b6ab-4d70-be7a-dff62547bf51‘ is enabled for database mirroring or has joined an availability group. The name of the database cannot be changed. (Microsoft SQL Server, Error: 957)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.2000&EvtSrc=MSSQLServer&EvtID=957&LinkId=20476
——————————
BUTTONS:
OK
——————————

Blog44_1.PNG

-> It was unusual to see a GUID as a database name in the message.

-> I checked this further to see if I can understand what this GUID is and tried querying the sys.databases to see if I have some details.

-> The sys.databases has a column called physical_database_name that holds this value.

Blog44_2.PNG

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Sp_configure on Managed instance

-> We use Sp_configure almost everyday on our Database Server in On-Premise.

-> We can access Sp_configure in Managed Instance as well. But not all setting can be tweaked.

-> We will check most of the used configuration settings and see if it can be changed or not,

Name Can be changed?
Show Advanced Options Yes
Ad Hoc Distributed Queries Yes
allow updates No
automatic soft-NUMA disabled  No
backup checksum default  Yes
backup compression default Yes
blocked process threshold (s)  Yes
clr enabled  Yes
clr strict security  Yes
contained database authentication  Yes
cost threshold for parallelism  Yes
cross db ownership chaining  Yes
Database Mail XPs  Yes
filestream access level  No
fill factor (%) No
index create memory (KB)  Yes
lightweight pooling  No
locks  No
max degree of parallelism  Yes
max server memory (MB)  No
max text repl size (B)  No
max worker threads  Yes
min memory per query (KB)  Yes
min server memory (MB)  No
nested triggers  Yes
network packet size (B)  Yes
Ole Automation Procedures  Yes
optimize for ad hoc workloads Yes
priority boost  No
recovery interval (min)  Yes
remote admin connections  Yes
Replication XPs  Yes
scan for startup procs  No
xp_cmdshell  Yes, But when you execute xp_cmdshell in query window. You will get an error “‘xp_cmdshell’ is not supported in this version of SQL Server.”

 

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.