Microsoft SQL Server Version Details

Quick summary:

	RTM (no SP)	SP1	SP2	SP3	SP4
SQL Server 2016	13.0.1601.5	13.0.4001.0 or 13.1.4001.0
SQL Server 2014	12.0.2000.8	12.0.4100.1 or 12.1.4100.1	12.0.5000.0 or 12.2.5000.0
SQL Server 2012      codename Denali	11.0.2100.60	11.0.3000.0 or 11.1.3000.0	11.0.5058.0 or 11.2.5058.0	11.0.6020.0 or 11.3.6020.0
SQL Server 2008 R2      codename Kilimanjaro	10.50.1600.1	10.50.2500.0 or 10.51.2500.0	10.50.4000.0 or 10.52.4000.0	10.50.6000.34 or 10.53.6000.34
SQL Server 2008      codename Katmai	10.0.1600.22	10.0.2531.0 or 10.1.2531.0	10.0.4000.0 or 10.2.4000.0	10.0.5500.0 or 10.3.5500.0	10.0.6000.29 or 10.4.6000.29
SQL Server 2005      codename Yukon	9.0.1399.06	9.0.2047	9.0.3042	9.0.4035	9.0.5000
SQL Server 2000      codename Shiloh	8.0.194	8.0.384	8.0.532	8.0.760	8.0.2039
SQL Server 7.0      codename Sphinx	7.0.623	7.0.699	7.0.842	7.0.961	7.0.1063

Ref : https://sqlserverbuilds.blogspot.in/

Resend an email sent from SQL Server

Dear All,

Sometimes the email goes missing in the SQL server.

Find the email you want to resend using one of the following views in MSDB:

sysmail_allitems

sysmail_faileditems

Use following Query:


DECLARE @mailitemID INT = -- MailItem_Id here e.g 98765

DECLARE @profilename NVARCHAR(125);
DECLARE @recipients VARCHAR(2000);
DECLARE @copy_recipients VARCHAR(2000);
DECLARE @blind_copy_recipients VARCHAR(2000);
DECLARE @subject NVARCHAR(510);
DECLARE @body VARCHAR(2000);
DECLARE @body_format VARCHAR(20);
DECLARE @importance VARCHAR(6);
DECLARE @sensitivity VARCHAR(12);
DECLARE @query BIT;
DECLARE @attachment BIT;

--Get the information for the email
SELECT
@profilename = [profiles].[name],
@recipients = [mail].[recipients],
@copy_recipients = [mail].[copy_recipients],
@blind_copy_recipients = [mail].[blind_copy_recipients],
@subject = [mail].[subject],
@body = [mail].[body],
@body_format = [mail].[body_format],
@importance = [mail].[importance] ,
@sensitivity = [mail].[sensitivity],
@query = CASE WHEN [mail].[query] IS NULL THEN 0 ELSE 1 END,
@attachment = CASE WHEN [mail].[file_attachments] IS NULL THEN 0 ELSE 1 END
FROM msdb.dbo.sysmail_allitems mail
INNER JOIN msdb.dbo.sysmail_profile profiles ON mail.profile_id = profiles.profile_id
WHERE mailitem_id = @mailitemID;

IF @query = 0 AND @attachment = 0
BEGIN

--Re send the email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@copy_recipients = @copy_recipients,
@blind_copy_recipients = @blind_copy_recipients,
@subject = @subject,
@body = @body,
@body_format = @body_format,
@importance = @importance,
@sensitivity = @sensitivity

END
ELSE
BEGIN
RAISERROR('unfortunately this script cannot send',11,0,@mailitemID)
END

I've verified its working fine.

mysql update query

Mysql update query for



SELECT * FROM wordpress99.wp_users;

update wordpress99.wp_users set user_pass=MD5('!@#$%^@2019') where ID=1

DHL web service - timeout

Hello team,

Las few days faced issue with DHL side, following error message received,

DHShipment() The request channel timed out while waiting for a reply after 00:00:59.9990010. Increase the timeout value passed to the call to Request or increase the SendTimeout value on the Binding. The time allotted to this operation may have been a portion of a longer timeout.

windows activation

Windows activation Steps, 1} Search command prompt 2} right click and run as an administrator in command prompt 3}  slmgr.vbs -ipk XXXXX-XXXXX-XXXXX-XXXXX-XXXXX (Enter the product key) 4} Enter & 30 Sec wait

Note:- Any get Error Restart the PC & check

purging cloudflare cache with an api call in wep api and asp.net C#

Hi All,

Cloudflare's cache

To remove one or more files from Cloudflare's cache either by specifying URLs, faced challenges to integrated the cloud-flare API call. We've tried VS 2015 and framework 4.5.2. Exception through 'upgrade the version' and tried higher version VS 2017 and framework 4.6.1 and its works fine.

Cloudflare Request:


curl -X POST "https://api.cloudflare.com/client/v4/zones/023e105f4ecef8ad9ca31a8372d0c353/purge_cache" \
     -H "X-Auth-Email: user@example.com" \
     -H "X-Auth-Key: c2547eb745079dac9320b638f5e225cf483cc5cfdda41" \
     -H "Content-Type: application/json" \
     --data '{"files":["http://www.example.com/css/styles.css",{"url":"http://www.example.com/cat_picture.jpg","headers":{"Origin":"cloudflare.com","CF-IPCountry":"US","CF-Device-Type":"desktop"}}]}'


Cloudflare Response

{
  "success": true,
  "errors": [],
  "messages": [],
  "result": {
    "id": "9a7806061c88ada191ed06f989cc3dac"
  }
}

Here, Existing application running .net framework 4.5 (immediate need to convert 4.6.1 ), if we change to 4.6.1 version. Required to verify full application and takes 3 to 4 months. Meanstime we think and created web api and call the url in existing application. 

4.5(traditional asp.net)--->4.6.1(web api)---> Cloudflare Purge (Restful API)

Happy coding:)

IIS,Memory leak,output cache,set the memory limit, recycling

Recenly faced iis related memory leakage, To investigate its happen application level or system level.

Application level

i. Check DB - costly query and DB performance

ii. outputcache

iii. third-party dll and excel macro relate application used


System level

i. To reduce number application pool (each application pool allocating memory, so allocate static site are to defaultapppool)

ii. check system configuration (RAM and C drive space)

I referred below links,


IIS - Output cache: https://serverfault.com/questions/557920/iis-memory-management-thresholds-with-potential-leaks


Memory: https://forums.iis.net/t/1230103.aspx?Windows+Server+2012+IIS+8+0+IIS+Worker+Process+Consumes+more+memory


Recycling: http://www.bursky.net/index.php/2013/03/managing-iis-server-memory-usage/


https://stackoverflow.com/questions/16162524/how-to-increase-memory-and-cache-size-for-application-pool-in-iis-7-efficiently

https://serverfault.com/questions/598672/app-pool-doesnt-respect-memory-limits

Slow performance Wordpress,MySQL on Windows Server with IIS fastcgi

Hi,

Faced performance problem with an internal WordPress site.

I changed the settings below and my speed improvement went from +1650ms to 450ms.

The default is set to localhost.

/** MySQL hostname */
define('DB_HOST', 'localhost');
Change this to 127.0.0.1

/** MySQL hostname */
define('DB_HOST', '127.0.0.1');

full stack developer

My interesting topic , daily leaning new things.

What is a Full-Stack Developer?

Developer specialized in everything from front-end to back-end; to a developer who has a general knowledge in all steps from concept to finished product.

Full-stack developer to have specialized knowledge in all stages of software development.

Stages,

1.Server, network, and hosting environment
2.Relational and non-relational databases
3.How to interact with APIs and the external world
4.User interface and user experience
5.Quality assurance
6.Security concerns throughout the program
7.Understanding customer and business needs

ssrs, reports session time out

Hi, I've faced ssrs report - Session timeout issue. Searched and fixed the issue.

Go to administrator account (most important) and

i.choose site setting-->general

ii. choose do not timeout (radio button)

iii. click ok

iv. restart SQL server reporting services

mysql grant privileges

mysql> CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'localhost' WITH GRANT OPTION;
mysql> CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'%' WITH GRANT OPTION;

Site traffic source and medium

Hi,

Today learn google analytic, traffic source and medium.

Source / Medium. 

Source: the origin of your traffic, such as a search engine (like, google,bing,yahoo) or a domain (irctc.co.in). 

Medium: the general category of the source, (for example, organic search (organic), cost-per-click paid search (cpc), web referral (referral), email campaign). 

Solved Mix match content issue

Hi,

Daily I've received new experience, today fixed mix and match content in blog.

Searched and found WordPress plugin.

Installed activities Really secure SSL plugin and resolved the issue.

Note. If anyone using cloudflare SSL certificate and use cloudflare plugin.

Have a good day!

Rewrite rule in iis

N numbers of  4xx series and 5xx series rewrite rul map in iis and web.config.

WordPress in IIS

Hi,

Yes, Possible WordPress, PHP, MySQL in IIS and windows environment.

I've difference experience to the implementation of the blog in IIS.

Please follow points,

Devops role

Devops role,

1.work collaboratively with software engineering to deploy and operate our systems.
2.Help automate and streamline our operations and processes.
3.Build and maintain tools for deployment, monitoring and operations. 4.And troubleshoot and resolve issues in our dev, test and production environments.

Sql Server - Find the Size of Database File and Find the Size of Log File

use below script to find the size of the DB

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = '<<your database name>>'
GO

fix: sql server 2008 r2 cpu usage 100%

CPU usage can be caused by a lot of reasons like:

• Bad execution plans caused by bad statistics
• Locks and blocks
• DBCC CHECKDB
• Index rebuild
• etc

I have faced cpu usage touch 90-100%  around 10 hour.  follow action taken but not able to fix it.

1. select * from sys.sysprocesses where blocked>=1 (kill unwanted spid) 

2. restarted sql services

3. Sp_who2 'active'

Referred following articles : 

http://www.sqlservercentral.com/Forums/Topic1411982-391-1.aspx

http://raaviblog.com/how-to-investigate-100-cpu-usage-problem-in-sql-server-2008/


 "after 8 hour search found link and offline dba support to arrive the lack of index. in maintenance activity sp_updatestats missing."

====================================================================

Query execution causing CPU spike:

Query execution  takes long times and spikes CPU commonly because of in-correct carnality estimates caused by outdated statistics, Lack of Index, Server configuration, Distributed queries, etc.

When the server is experiencing this problem run the query in below link to list all the queries which are executing in the server order by CPU time desc along with plan.

{

Get SQL Text and Query Plan for statements which are executing now

}

 It could be one query which is driving the majority CPU time or Multiple queries each driving the CPU. Look at the CPU time of the above query output.

If it is single query/Store procedure which is driving the majority of CPU.

1.        Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will  be approximately

same in execution plan .If there is huge difference stats are out dated and requires update

=======.=====================================================

Thanks god found link and read first step and stick-out run the 

Solution: "sp_updatestats"

1) did you update ALL statistics with a FULL SCAN?

2) I would simply use profiler to capture rpc batch completed and tsql batch completed events and find the big hitters. Tune these. I would also consider running a trace to disk and using Qure from DBSophic to do aggregate trace analysis. The thing(s) you need to tune most are not necessarily the ones that run the longest or use the most resources individually...

3) it is possible that someone altered the schema in any way - i.e. dropped some indexes?

4) I also see this routinely at clients these days: did you perchance upgrade to a much better IO subsystem? Getting data into the CPUs faster can cause them to actually start earning their keep.

 

performance: msdb.dbo.sp_readrequest;1 – long running process

When monitoring the new installed version of SQL 2008 R2 SP1, I have encountered that Database mail leaves a hung process few minutes. This doesn’t do any blockages or other trouble to your SQL installation, either that it is anoying to see over and over again this long running process. In order to get rid off it, just check the system parameter value for “DatabaseMailExeMinimumLifeTime” running the following query:

Use msdb
GO
exec sysmail_help_configure_sp 'DatabaseMailExeMinimumLifeTime'
GO

The default setting will be 600. You need to change it to a lower period.

Use msdb
GO
exec sysmail_configure_sp 'DatabaseMailExeMinimumLifeTime', 30
GO

=================================================================

Anyone get a solution to this one?  I'm getting the same hung process today after an upgrade to 2005 SP3

Had a look at the mail logs - the process runs for 10minutes.  Which is coincidentally the same as the setting for 'Database Mail Executable Minimum Lifetime (seconds)'

When I adjusted 'Database Mail Executable Minimum Lifetime (seconds)' down from the 10 minutes default to 10 seconds everything went back to normal.  Much happiness for me. 

I am troubleshooting a similar issue, whereby the Performance Dashboard Reports (the Query Duration Component) show that a stored procedure used by Database Mail is the most costly query. It appears to be a service broker wait request, i.e. the database mail queue is waiting for further requests to process.

Based on your comments this seem like a plausable solutiuon. I have adjusted the very same Database Mail property to see if it clears this issue.

Error: Cannot drop the database because it is being used for replication

Hi,

Try using sp_removedbreplication ‘dbname’