Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 21 March 2017

Database Mail The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required in ASP.Net Application.

 The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required in ASP.Net Application.


Ref:

https://www.aspsnippets.com/Articles/GMAIL-Error-The-SMTP-server-requires-a-secure-connection-or-the-client-was-not-authenticated.aspx


when you configured database mail but still you are getting error 




solution:


you will get mail from your gmail account  and click on below link to activate it


Kindly click above link and turn on 


Now sending test mail 


success 

now we got mail from dbmail





























Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid namespace [0x8004100e]


https://support.microsoft.com/en-in/help/956013/error-message-when-you-open-sql-server-configuration-manager-in-sql-server-cannot-connect-to-wmi-provider.-you-do-not-have-permission-or-the-server-is-unreachable


I have installed SQL 2012 on my machine but when i tried to open configuration manager i got 
error as above




to work around see this page

https://support.microsoft.com/en-in/help/956013/error-message-when-you-open-sql-server-configuration-manager-in-sql-server-cannot-connect-to-wmi-provider.-you-do-not-have-permission-or-the-server-is-unreachable


This problem also occurs if you uninstall the 32-bit instance, and you then open SQL Server Configuration Manager.

Cause

This problem occurs because the WMI provider is removed when you uninstall an instance of SQL Server. The 32-bit instance and the 64-bit instance of SQL Server share the same WMI configuration file. This file is located in the %programfiles(x86)% folder.

Workaround

To work around this problem, open a command prompt, type the following command, and then press ENTER:
mofcomp "%programfiles(x86)%\Microsoft SQL Server\number\Shared\sqlmgmproviderxpsp2up.mof"
Note For this command to succeed, the Sqlmgmproviderxpsp2up.mof file must be present in the %programfiles(x86)%\Microsoft SQL Server\number\Shared folder.

The value of number depends on the version of SQL Server:nnn

Microsoft SQL Server 2012110
Microsoft SQL Server 2008 R2100
Microsoft SQL Server 2008100
Microsoft SQL Server 200590
After you run the Mofcomp tool, restart the WMI service for the changes to take effect. The service name is Windows management Instrumentation. 






after run this query in command prompt now am open configuration manager it opens with no issue








Thanks for reading

How many CPU cores is SQL Server using?

How many CPU cores is SQL Server using?

CPU count:

select cpu_count from sys.dm_os_sys_info


How many CPU cores is SQL Server using:

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'









Saturday, 18 February 2017

How to set Secondary File NDF file growth and restrict file size growth in MDF file in SQL Server

New File growth effect Primary MDF and Secondary NDF Added in SQL Server

s p_h e l p db db info

initially it was only 6 MB



Added some sample records to table on test db growth table


after added records you can see growth only on M D F file only


Now we added secondary file 

even if you decide to u n select on enable auto growth option on properties of db



it will give effect on auto growth as none


now we check s p_help db


now we insert sample data

it will give error as we disabled auto growth it will not allow data to insert as show error


we disabled auto growth so it will not allow data to insert see the error


So we should need to enable Auto growth on M D F then we are going to set auto growth option on
N D F then it will work as see below



s p_h e l p db of M D F and L D F info as see below


now we are going to alter database to new location as see below


take database offline then move the M D F file to new location as see below


Now move the database log file to new location as see below


after bring online database shows as new location F drive as see below


Now we insert some sample data as see below


Now we new insert data take effect on M D F  only but cant insert data on LDF see below



solution:

We should need to limit DB Growth in M D F file as see below



now we see help db info





we should need to change it secondary on clustered index /index on table otherwise table should increase on primary only


after change we insert 10000 Thousand records to table as see below


you can see NDF now growing but MDF is not growing itself





































Tuesday, 14 February 2017

How to MOVE database MDF and LDF file to New Drive location path step by step in SQL Server

Step 1

take data file path use sp_help db and save it to notepad and take screen shot and paste it to paint

Ex:

method 1

sp_helpdb 'databasename'

method 2

SELECT name, physical_name AS Current_Location
   FROM sys.master_files
          WHERE database_id = DB_ID(N'Testdbgrowth');

          GO

Step 2


Change file path through ALTER DATABASE MODIFY FILE query

USE master;
GO
ALTER DATABASE db_name
MODIFY FILE (NAME = dbMDFlogicalname, FILENAME = 'E:\new_location\Datafile name.mdf');
GO
ALTER DATABASE db_name
MODIFY FILE (NAME = dbLDFlogicalname, FILENAME = 'E:\new_location\Logfilename.ldf');

GO

Step 3:

verify any users or active connections still using the database or not using s p_who 2 query if any connection exists change single user and multi user mode immediately  to close connections as see 

below


USE master;
GO

-- Put database into single user mode closing open connections
ALTER DATABASE db_name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

-- Put it back into multi user mode
ALTER DATABASE db_name

SET MULTI_USER;



Step 4:

take offline for DB

Step 5:

copy physical file from source location to new location (don't rename if you should rename need to give same in alter query)

Step 6:

bring database online

Step 7:

verify s p_ h e l p d b command to new path available on M D F and L D F 


Demo:

Step 1:

we can see using 

s p _help db 'database name' to take the data file path 

or we can use  s y s.master_files to get database files info


or we can s p_help file to get this m d f and l d f info (note it will be work when we point db see below)



you can see the M D F available location see below


you can see the L D F available location see below



Kindly take it to notepad for future reference  or take it screenshot and save it on paint u r wish

Step 2

now we need to prepare query to take effect of new path location as see below

USE master;
GO
ALTER DATABASE Testdbgrowth
MODIFY FILE (NAME = Testdbgrowth, FILENAME = 'D:\SQL\Data file\Testdbgrowth.mdf');
GO
ALTER DATABASE Testdbgrowth
MODIFY FILE (NAME = Testdbgrowth_log, FILENAME = 'D:\SQL\Log\Testdbgrowth_log.ldf');

GO


now we need to execute to take effect

it clearly says it will effect when next db went online/offline


Step 3:

now we need check whether any db is any connection using or not to take db offline without any issue


Step 4

now we are going to take db offline


now it shows db offline status


Step 5:

now the time to move M D F  cut and past it to new path location


now the time to move L D F  cut and past it to new path location



Step 6:

now time to bring database online



after success message kindly refresh on server as see below



you can see Test db Growth db comes online as see below

Step 7:

also kindly verify the M D F and L D F path comes to new location as see below




Note:

1)If you rename  M D F or L D F but not give correct name in alter query 

2) if you alter query with new path location but not move M D F and L D F then you will get below error


if we any data file or log file not place correctly we could see following error

path not available to come online 



still show db in recovery pending cant come online due to path not available


you can see take online is disabled you should only take to offline mode only 



solution

if db was in recovery pending then check the data file path m d f and l d f available  then 

once again alter query with correct path and bring db offline and bring online






that is it keep blogging and reading ☺☺☺☺☺☺☺☺