Articles by "Sql"

2019 4G LTE 4G VoLTE 5G 7th Pay Commission Aadhaar Actor Wallpapers Actress Wallpaper Adriana Lima AdSense Ahoi Ashtami Airtel Akshay Kumar Alcatel Alexa Rank Amazon Android Android Pie Android Q Anna university Antivirus Anushka Sharma Apple Apps Army Army App Asus Athletics Auto Auto Insurance Avengers Axis Bank Bajaj Bang Bang Reloaded Bank Battery Bhai Dooj Katha Bharti Bhumi Pednekar Big Bazaar Bing BlackBerry Blogger BlogSpot Bluetooth BoB Bollywood Boot Boxing Browser Bsnl Budget Budhvar Business buy Camera Car Car Loan Cash Celebrity CEO Chandra Grahan Channels Chhath chrome Comparisons Computer Coolpad Credit Cricket Crime Deepika Padukone Defence Detel Dhanteras Diamond Crypto Diwali DNS setting Domain Donate Doogee DTH DTH Activation DTH Installation DTH Plans in India Dusshera Earn Money Education Electronics Email Entertinment Ex-serviceman Extensions Facebook Festivals Flipkart Foldable Smartphone Food Funny Gadgets Galaxy Galaxy S8 Game Ganesh ganesh chaturthi Gionee Gmail God Google Google + Google Assistant Google Drive Google Duo Google Pixel Google Tez Google Voice Govardhan Puja GST GTA Guide Guruvar Hamraaz hamraaz app hamraaz app download hamraaz army hamraaz army app hamraaz army app download Hamraaz Army App version 6 Apk Happy New Year Hariyali Teej Hartalika Teej Harvard University HDFC Bank Headphones Health Heena Sidhu Hello App Help Hindi History Hockey Holi Katha Hollywood Home Loan Honor HostGator Hosting Hrithik Roshan HTC Huawei humraaz app iBall IBM ICICI Bank Idea India indian army app Infinix InFocus Information Infosys Instagram Insurance Intel Internet Intex Mobile iPad iPhone iPhone 8 IPL IRCTC iVoomi Janmashtami Javascript JBL Jio Jio GigaFiber JioRail JioSaavn Jokes Karbonn Kareena Kapoor Kartik Purnima Karva Chauth Karwa Chauth Kasam Tere Pyaar Ki Katrina Kaif Kendall Jenner Kimbho Kodak Kumkum Bhagya Landline Laptop Lava Lenovo LET LG Library of Congress Lifestyle Linkedin Lisa Haydon Loans Macbook Maha Shivratri Map Market Mary Kom Massachusetts Institute of Technology Meizu Messages Mi Micromax Microsoft Mobile Mokshada Ekadashi Money Motorcycles Motorola Movie Music Narendra Modi Narsingh Jayanti Nature Naukri Navratri Netflix Network News Nexus Nia Sharma Nokia Notifications OBC Ocean Office Offrs OMG OnePlus Online Opera Oppo Oreo Android Orkut OS OxygenOS Padmavati PagalWorld PAN Panasonic Passwords Patanjali Pay Payment Paypal Paytm PC PDF Pendrive Personal Loan Phone Photo PHP Pixel Plan PNB Bank PNR Poco Poster PPC Pradosh Pragya Jaiswal Prepaid Princeton University Printer Priyanka Chopra PUBG Qualcomm Quora Quotes Race 3 Railway Rambha Tritiya Vrat Realme Recruitment Redmi Relationship Religious Restore Results Review Rule Sai Dharam Tej Saina Nehwal Salman Khan Samsung Sanusha Sawan Somvar Vrat SBI Bank Script Sell SEO Serial Server Shahid Kapoor Shanivar Sharad Poornima Sharp Shiv Shreyasi Singh Shruti Haasan Sim Smart Android TV Smartphones Snapchat Social Software Somvar Sonakshi Sinha Sonam Kapoor Soney Songs Sony Xperia Space Speakers Specifications Sports Sql Stanford University State Bank of India Stickers Story Sunny Leone Surabhi Sushant Singh Rajput Swadeshi Tax Tech Technology Tecno Telugu Tiger Shroff Tiger Zinda Hai Tips Tollywood Tool Top Trending People Trading Trai TRAI Rules for cable TV Trailer Trends Truecaller Tubelight Tulsi Vivah Tv Twitter Typing Uber University of Oxford UP Board Update USB Vacancies Valentines Day Verizon Vertu Video Vijayadashami Virat Kohli Virgin Visas Vivo Vodafone VPN Vrat Katha Vrat Vidhi Wallpaper War Wayback Machine WhatsApp Wi-Fi Windows Windows 10 Wipro Wireless WordPress workstation WWE Xiaomi Xiaomi Mi 6 Yeh Hai Mohabbatein Yo Yo Honey Singh Yoga YotaPhone YouTube ZTE
Showing posts with label Sql. Show all posts

July 22, 2017 , ,
WordPress – All the IP Addresses of Comments are Same and of Local ServerRecently I walked into a very interesting situation with this blog. As many of you may know that this blog is hosted on Linux server and runs with MySQL and PHP. Self-hosted WordPress is the platform for this blog.
Recently I started to notice that after some updates on platform, all the comments on the blog posts have exactly the same IP Address. This started to create a major problem for me. As most of you know malicious attacks and spamming is a common problem everywhere. This blog also receives lots of spam messages and it is very important that spams are filtered out so I can respond to all the valid comments.
However, now all the comments contain the same IP address and that also of the my server’s IP address, it was very difficult for the spam catcher plugin to catch all the spams. After reading through quite a many spam comments, I decided to fix this problem for good.

Here is the solution I applied.

I went to php.config which is in the root folder of public_html and added following lines in the code.

1
2
if($_SERVER['HTTP_X_REAL_IP']){
$_SERVER['REMOTE_ADDR'] = $_SERVER['HTTP_X_REAL_IP'];}

Once I applied to above mentioned line, everything was back to normal. All the comments in the WordPress, started to show the IP address of the original individuals.

Well, that’s it. A very simple solution to a complicated problem.

July 21, 2017 ,
One of my clients contacted me for On Demand (55 minutes) as they believed that this was a simple issue. John was honest enough to confess the mistake which he has done, which lead to error related to invalid object name.

John was trying to troubleshoot a deadlock issue, and he found that this specific server doesn’t have a system_health session in extended events.
SQL SERVER – Invalid Object Name ‘master.dbo.spt_values’ in Management Studio

So, he found that the definition of the session is defined in U_tables.sql file from “Install” folder.

C:\Program Files\Microsoft SQL Server\MSSQL13.SQLSERVER2014\MSSQL\Install

He executed the script, but it failed with below the messages.

This file creates all the system tables in master.
drop view spt_values ….
Creating view ‘spt_values’.
Msg 208, Level 16, State 1, Procedure spt_values, Line 56
Invalid object name ‘sys.spt_values’.
sp_MS_marksystemobject: Invalid object name ‘spt_values’
Msg 15151, Level 16, State 1, Line 61
Cannot find the object ‘spt_values’, because it does not exist or you do not have permission.
drop table spt_monitor ….
Creating ‘spt_monitor’.
Grant Select on spt_monitor
Insert into spt_monitor ….

Now, there was a bigger problem. A lot of places in SSMS, he started seeing below errors.

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Invalid object name ‘master.dbo.spt_values’. (Microsoft SQL Server, Error: 208)

John knew that he should create the view ‘master.dbo.spt_values’ using below but was unable to.

WORKAROUND/SOLUTION

To create master.dbo.spt_values the reference is needed to sys.spt_values. This can’t be accessed by normal connection. There are two ways

Start SQL Server in single user mode, which would need downtime.
Connect using Dedicate Administrator Connection (DAC). You can read more about DAC over here. Diagnostic Connection for Database Administrators
After making connection run below script.

1
2
3
4
5
6
7
8
9
10
11
create view spt_values as
select name collate database_default as name,
    number,
    type collate database_default as type,
    low, high, status
from sys.spt_values
go
EXEC sp_MS_marksystemobject 'spt_values'
go
grant select on spt_values to public
go

July 20, 2017 ,
This was one of an interesting question which I heard from one of my clients who deployed Always On availability groups. I was not able to find much documentation and clarity so did some research and found an answer. This can also be an interview question as well.

Question:
I have configured Always On availability group using the Wizard. While configuration, I have selected a shared folder in below screen.
SQL SERVER – Can I Delete Always On Availability Groups Initial Sync Folder?

Due to some unavoidable reasons, I must remove that share.  If I remove that share, will availability group continues to function? Is it needed like the way we have shared location in log-shipping?

Answer:
Yes, we can remove that share and it won’t have any impact on the existing availability group. The purpose of the share is to do an initial sync via backup and restore method done by Wizard interface when we use Initialize now option. One backup and restore is complete and databases are synchronized, we can remove the files or even the share itself. Log Shipping needs a share to propagate transaction log backups in a shared location which is not the case with availability.

Have you faced a similar situation before? Let me know your thoughts about this blog post via comments.

July 19, 2017 , ,
SQL SERVER – Interesting Observation – Count of Temporary Table Re-generationsThe best part of my SQL Server Performance Tuning Practical Workshop is that at the end of the workshop we spend quite a good amount of time to solve the issues users have in their production system. During one of the recent workshop, I was asked a very interesting question  about temporary tables.
“How do we know the count of how many times a single temporary table has been regenerated?”

I particularly found this very interesting as so far in my career, I have been asked this only 3 times. The best part is that I knew the answer.
However, before we start answering this question, I must refer you to another amazing blog post written by my friend over here SQL SERVER – Maximum Allowable Length of Characters for Temp Objects is 116. This blog post contains very interesting information about the max size of the temporary table name.
Now let us read the answer of the question – how do we know how many times any particular temporary table has been regenerated.

When we create any temporary table, it is internally created with a 128 characters in its name. The last 12 digits of this long name contain unique magic bits or a hexadecimal number which indicates how many times the table has been created so far since the last SQL Server restart.

This number resets when SQL Server services are reset.

You can recreate this example by running the following query multiple times in the SQL Server Management Studio. Every single time when you re-run this query, it will increment one in the number which is at the end of the temporary table’s name.

1
2
3
4
5
6
7
8
9
-- Create TempTable
CREATE TABLE #TempTable (Col INT);
-- Check TempTable Name
SELECT [name]
FROM tempdb.sys.tables
WHERE [name] LIKE N'#TempTable%';
-- Drop TempTable
DROP TABLE #TempTable;
GO

Here is the example when I ran above script multiple times in the SSMS and you will notice the last digit increasing by 1 every time I run the script.

Please leave a comment let me know if you knew about this part of SQL Server.

MsnTarGet.com

Satish Kumar

Contact Form

Name

Email *

Message *

Powered by Blogger.