Digital Indian Gov: Sql

Showing posts with label Sql. Show all posts

WordPress – All the IP Addresses of Comments are Same and of Local Server

Recently 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.

SQL SERVER – Invalid Object Name ‘master.dbo.spt_values’ in Management Studio

SoAs.in July 20, 2017 Server , Sql

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.

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

SQL SERVER – Can I Delete Always On Availability Groups Initial Sync Folder?

SoAs.in July 19, 2017 Server , Sql

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.

SQL SERVER – Interesting Observation – Count of Temporary Table Re-generations

SoAs.in July 18, 2017 Server , Sql , Tech

The 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.

-- 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.

Digital Indian Gov

Labels

Layout

Post Style

Technology

Breaking News

Articles by "Sql"

WordPress – All the IP Addresses of Comments are Same and of Local Server

SQL SERVER – Invalid Object Name ‘master.dbo.spt_values’ in Management Studio

SQL SERVER – Can I Delete Always On Availability Groups Initial Sync Folder?

SQL SERVER – Interesting Observation – Count of Temporary Table Re-generations

Digitalindiagov.com

Contact Form