Online database info

Steps to Rename Physical Database Files in SQL Server

The users can manually rename the physical files according to choice using the Transact-SQL commands in the following manner:

Firstly, we will create a database “new_db” using SQL query. Naturally, the physical files will be named as “new_db.mdf”. Our main aim is to rename SQL Server physical files from “new_db.mdf” to “old_db.mdf”. Follow the below mentioned steps to manually rename the files:

Firstly, run the following command to create a new database with name ”new_db”.

CREATE DATABASE [new_db] 
 CONTAINMENT = NONE 
 ON  PRIMARY 
( NAME = 'name', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\new_db.mdf',SIZE = 5MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10MB )

Thus, a new database is created with physical file “new_db”. The following steps can be used to rename SQL Server physical files name.

Now, locate the physical location of file on your system using the following command:

USE new_db 
GO 
SELECT file_id, name as [logical_file_name], physical_name 
FROM sys.database_files

Also, the database should be brought to OFFLINE state because the files cannot be renamed in the online mode. Run the following command to bring database to offline state:

USE [master]; 
GO 
--Disconnect all existing session. 
ALTER DATABASE new_db SET SINGLE_USER WITH ROLLBACK IMMEDIATE 
GO 
--Change database in to OFFLINE mode. 
ALTER DATABASE new_db SET OFFLINE

Since the database is offline now, so browse to the location of the file as found from Step(1). Rename the file to desired name e.g., old_db.mdf.
Now, update the system catalog for updating the new name of the physical file.

ALTER DATABASE new_db MODIFY FILE (Name='new_db', FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\old_db.mdf') GO

This updates the physical file name in system catalog file.

Run the following command to bring the database in ONLINE mode:

ALTER DATABASE new_db SET ONLINE 
Go 
ALTER DATABASE new_db SET MULTI_USER 
Go

So, the database physical file name has been renamed to “old_db.mdf” and the database is also in the Online mode. The users can now work normally on the database to perform any operation.

Conclusion

The user may feel it necessary to rename physical file name in SQL Server due to any reason. However, to avoid confusion between the physical files and database, the users are always advised to rename SQL Server physical database files using T-SQL. All the detailed steps have been mentioned in the above section.

Queries regarding Mysql Proceduers

To find out all the procedures of a database:-

SHOW PROCEDURE STATUS WHERE Db = 'thirstydb';

To set a new definer:-
mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@localhost' AND db='devthirstydb';
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0 Changed: 0 Warnings: 0

mysql> UPDATE `mysql`.`proc` p SET definer = 'thirstydev@%' WHERE definer='thirstylive@%' AND db='devthirstydb';
Query OK, 85 rows affected (0.04 sec)
Rows matched: 85 Changed: 85 Warnings: 0

To select all the procdure of a particular db:-
select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

select name FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';

To delete all the procdure of a particular db:-
DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'PROCEDURE';

DELETE FROM mysql.proc WHERE db = 'devthirstydb' AND type = 'FUNCTION';

Mysql procedures:-

To find out all the procedures:-

mysql> show procedure status;
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+--------------+---------------------------------------+-----------+---------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| thirstydb | advanced_vendor_filtering | PROCEDURE | thirstylive@% | 2016-08-12 05:13:27 | 2016-08-12 05:13:27 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | advanced_vendor_filtering_my_bookings | PROCEDURE | thirstylive@% | 2016-08-12 05:22:29 | 2016-08-12 05:22:29 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | advanced_vendor_filtering_temp | PROCEDURE | thirstylive@% | 2016-08-08 08:29:11 | 2016-08-08 08:29:11 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | all_food_categories | PROCEDURE | thirstylive@% | 2016-08-08 08:29:20 | 2016-08-08 08:29:20 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | apikeyValidity | PROCEDURE | thirstylive@% | 2016-08-08 08:29:29 | 2016-08-08 08:29:29 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | apikeyValidity_vendor | PROCEDURE | thirstylive@% | 2016-08-08 08:29:41 | 2016-08-08 08:29:41 | DEFINER | | utf8 | utf8_general_ci | latin1_swedish_ci |
| thirstydb | directory | PROCEDURE | thirstylive@% | 2016-08-19 03:00:51 | 2016-08-19 03:00:51 | DEFINER | | utf8

To find out a procedure by a certain definer name and dbname:-
mysq> use mysql
mysql> select name from proc where definer = 'thirstydev@%' AND db='devthirstydb';

+---------------------------------------+
| name |
+---------------------------------------+
| advanced_vendor_filtering |
| advanced_vendor_filtering_my_bookings |
| advanced_vendor_filtering_temp |
| all_food_categories |
| apikeyValidity |
| apikeyValidity_vendor |
| base_url |
| directory |
| facebook_login

To call a procedure:-
mysql> call search_result(1);
Empty set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

To view a procedure:-

mysql> show create procedure user_login;
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| user_login | | CREATE DEFINER=`thirstylive`@`%` PROCEDURE `user_login`(email_in VARCHAR(255), password_in VARCHAR(255),
api_key_in VARCHAR(50), base_url_in TEXT)
BEGIN

DECLARE email_verified_declared INT(2);
DECLARE user_id_declared BIGINT;

SELECT email_verified INTO email_verified_declared FROM user_profile WHERE email = email_in AND `password` = password_in;
SELECT user_id INTO user_id_declared FROM user_profile WHERE email = email_in AND `password` = password_in;

IF email_verified_declared = 0 THEN

SELECT 'unverified_email' AS col_name;

ELSEIF user_id_declared IS NULL THEN

SELECT 'invalid_credentials' AS col_name;

ELSE

UPDATE user_profile SET api_key = api_key_in WHERE user_id = user_id_declared;
CALL user_profile(user_id_declared, base_url_in);

END IF;

END | utf8 | utf8_general_ci | latin1_swedish_ci |
+------------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+-------------------

How to find out highest table size in sql server 2014 ?

SELECT
t.NAME AS TableName,
i.name as indexName,
sum(p.rows) as RowCounts,
sum(a.total_pages) as TotalPages,
sum(a.used_pages) as UsedPages,
sum(a.data_pages) as DataPages,
(sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
(sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
(sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
t.NAME NOT LIKE 'dt%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
t.NAME, i.object_id, i.index_id, i.name
ORDER BY
TotalSpaceMB desc

Mysql error-log monitoring using Shell script with mail alert

#!/bin/bash
SERNAME=xyz.kmi.com
SERPUBIP=192.168.72.50
SERVER=`hostname`
WEEKDAY=`date '+%w%H%M'`
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/var/log
egrep 'Error' $Error_log_loc/mysqld.log |sort -u > $Error_log_loc/mysqld_ALERTLOG.txt
cat $Error_log_loc/mysqld.log >> $Error_log_loc/mysqld_archived.log

cat /dev/null > $Error_log_loc/mysqld.log

if [ -s "$Error_log_loc/mysqld.log" ] ; then
cat $Error_log_loc/mysqld_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

sysdate=`date | awk '{ print $3}'`
if [[ $sysdate -eq 4 || $sysdate -eq 11 || $sysdate -eq 18 || $sysdate -eq 25 ]] ; then
mv $Error_log_loc/mysqld_archived.log $Error_log_loc/mysqld_archived_ALERTLOG_${DATE_VAR}.log
gzip $Error_log_loc/mysqld_archived_ALERTLOG_${DATE_VAR}.log
fi
exit 0

Batch script to take database users backup in sql server

SQL Server Version:- SQL 2014

Create a batch file which will be scheduled on task scheduler.

set backuplogfilename=%date:~-7,2%-%date:~-10,2%-%date:~-4,4%-0%time:~1,1%%time:~3,2%%time:~6,2%
SQLCMD.EXE -S localhost -U sa -P "sa@123" -i "E:\users.sql" >> "E:\users_%backuplogfilename%.log"
-- save and exit

Now schedule the above .bat file in task scheduler

Create a sql file lets say users.sql
Content of users.sql

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR

SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

exec dbo.sp_help_revlogin
go

Monitoring percona Error-log using shell script

vi /backups/alertlog_monotor.sh

#!/bin/bash
SERNAME=srv1.xyz.com
SERPUBIP=192.168.72.50
SERVER=`hostname` #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'` #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/data/mysql
egrep 'Error' $Error_log_loc/$SERNAME.err |sort -u > $Error_log_loc/$SERNAME_ALERTLOG.txt
cat $Error_log_loc/$SERNAME.err >> $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt

cat /dev/null > $Error_log_loc/$SERNAME.err

if [ -s "$Error_log_loc/$SERNAME_ALERTLOG.txt" ] ; then
cat $Error_log_loc/$SERNAME_ALERTLOG.txt | mail -s "URGENT -ERROR in Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@24x7-itsupport.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
mv $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
gzip $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

Oracle Alert log monitoring using shell script

Vi /backups/alertlog_monotor.sh

#!/bin/bash
export ORACLE_SID=kmioradb
export ORAENV_ASK=NO
. /home/oracle/.bash_profile
SERNAME=oradb.kminfosystems.com
SERPUBIP=192.168.72.50
SERVER=`hostname` #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'` #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Alert_log_loc=/u01/app/oracle/diag/rdbms/kmioradb/kmioradb/trace/
# Check for the existence of ORA- in the alert log and email/page on error
egrep 'ORA-|error|TNS' $Alert_log_loc/alert_$ORACLE_SID.log |sort -u > $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt #### Output file with ORA- errors
cat $Alert_log_loc/alert_$ORACLE_SID.log >> $Alert_log_loc/archived_alert_$ORACLE_SID.log
cat /dev/null > $Alert_log_loc/alert_$ORACLE_SID.log

if [ -s "$Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt" ] ; then
cat $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " soumya@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
mv $Alert_log_loc/archived_alert_${ORACLE_SID}.log $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
gzip $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh