Category: Databases

SQL Server 2016 SP1 Edition Limit Changes

width=724
Microsoft made the following changes in their documentation (see screenshot above) to accurately reflect the memory limits on lower editions of SQL Server.

Key Points

  • The limits for In-Memory OLTP data is per database.
  • The limits for Columnstore segment cache is per SQL Server instance across all the databases in the instance.

Example Scenario

A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by “max server memory” or total memory on the server if max server memory is uncapped.

Change WordPress Password using MySQL

If the Forgot password option doesn’t work for you (which it really should), then you can change the password of your self-hosted WordPress login.

Check the contents of wp-config.php for the database login. Use that login information for MySQL login.

define('DB_NAME', 'wpdatabase');
define('DB_USER', 'wpdbuser');
define('DB_PASSWORD', 'wpdbpassword');

Replacing DB_USER and DB_NAME from that command with the values from wp-config.php, enter the following command to connect to the database on your server.

mysql -uDB_USER -p DB_NAME

Get a list of users in the WordPress database.

select user_login from wp_users;

This might return multiple entries, but it was just one in my case: admin
To change the password, issue the following. Be sure to change ‘admin’ to the value of your user_login.

update wp_users set user_pass=md5('NEW_PASSWORD_HERE') where user_login='admin';

All done. Now you can log into your WordPress administration panel with the new password you specified.

Calculate Date Differences in MySQL

There is a function in MySQL that will calculate the number of days between two given dates:  datediff

Example

mysql> select datediff('2014-06-09','2014-07-07');
+----------------------------------------------+
| datediff('2014-06-09','2014-07-07') |
+----------------------------------------------+
| -28 |
+----------------------------------------------+
1 row in set (0.00 sec)

Calculate Days Between Now and Specific Date

mysql> select datediff(now(),'2014-07-01');
+------------------------------+
| datediff(now(),'2014-07-01') |
+------------------------------+
|                            6 |
+------------------------------+
1 row in set (0.00 sec)

MSSQL Find Last Date & Time Database Table Accessed

This is a T-SQL that will show all the last access date and time for Select, tables in a database.

select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()

Source: SQL Authority
Additional Information/Links:  SQL Blog
 

MSSQL Server Uptime Using T-SQL

A sample T-SQL for determining uptime of a Microsoft SQL Server.

USE master
SET NOCOUNT ON
DECLARE @crdate DATETIME, @hr VARCHAR(50), @min VARCHAR(5)
SELECT @crdate=crdate FROM sysdatabases WHERE NAME='tempdb'
SELECT @hr=(DATEDIFF ( mi, @crdate,GETDATE()))/60
IF ((DATEDIFF ( mi, @crdate,GETDATE()))/60)=0
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))
ELSE
SELECT @min=(DATEDIFF ( mi, @crdate,GETDATE()))-((DATEDIFF( mi, @crdate,GETDATE()))/60)*60
PRINT 'SQL Server "' + CONVERT(VARCHAR(20),SERVERPROPERTY('SERVERNAME'))+'" is Online for the past '+@hr+' hours & '+@min+' minutes'
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher')
BEGIN
PRINT 'SQL Server is running but SQL Server Agent < > running'
END
ELSE BEGIN
PRINT 'SQL Server and SQL Server Agent both are running'
END

Example Output:

SQL Server "MSSQL01" is Online for the past 1438 hours & 7 minutes
SQL Server and SQL Server Agent both are running

Source: MSDN Social