Shrinking File Log – SQL Server with SQL Script

When you monitoring your physical database (SQL Server) you will find file .LDF on big size . It because that file contain your transaction log on your daily operation on database.

And sometime if your harddisk capacity is become out of space, and sometime it make your operational transaction become fail to execute. So you need to execute some script to handle it.

-- 1.to know database information
sp_helpdb YourDBName
-- after that you will find your log file
-- 2.to do shrinkfile action
DBCC SHRINKFILE(YourDBName_log)

Sample best practice on our environment :


 
PS : when i do this script, i use SQL Server 2008











Have a nice try, hope it will help you also.

Running SQLCmd – SQL Server

We can use sql query with interface or we can use it with command prompt, on this time i prefer to discuss from command prompt.

With interface, we can run it from :

With command prompt we can run it from


The script is like this :
For Window Authentication Login
sqlcmd -S server1\SQLExpress

For Account Authentication Login
sqlcmd -S server1\SQLExpress -U SqlUserAccount -P SqlPassword

Refer to SqlCmd utility – Case Sensitive format
 
And we can try for this useful scripts :
SqlCmd Help
sqlcmd /?

Execute query from file
sqlcmd -S myServer\instanceName -i C:\myScript.sql

Save query result on file
sqlcmd -S myServer\instanceName -i C:\myScript.sql -o C:\EmpAdds.txt

Reference Link
http://msdn.microsoft.com/en-us/library/ms165702.aspx
http://msdn.microsoft.com/en-us/library/ms170572.aspx

How to use MonYog for MYSQL

To analysis your database, you can use MonYog application.

Go to this application:


This application will display several information:
Disk information – will display file capacity that already use for database

Process graphic, connection, cache hit and statement that use:

and it also handle monitor/advisor:

it handle statement also – with graphic support.

hope this tool can improve your skill on MySql.

How to use SQLYog for MySQL

When i ask to my friend – Fendy, is there anyway to execute SQL syntax on MySQL.

MySQL without command console (dos), and he answered “you can use SqlFront or SqlYog”, so i prefer SqlYog to to this:

ps: I use SQLYog Enterprise version 7.14 and MySQL 5.0.77
this application will help you to use MySQL GUI more friendly

Go to SqlYog application


And the application will show like this
 

use can write your sql syntax on right template, and result will appear on the bottom template


when you want to know the history of your sql syntax, you can use function history – like sql profiler:
 
when i want to create sp, i can do it, it because i use mysql version 4.12, to create sp or function you must install MySql database >= 5 ( MySQL version)

this is example to create sp:
 

and to call sp:


when i try to alter sp, on SqlYog, it will generate script to drop and create that sp again, the basic flow to alter sp.

The important point is to backup your database, where you can save data and structure on current database:

to Restore database, you only click Restore from SQL Dump and choose your backup file.

ok, have a nice Sql Script on MySql.

How to use MYSQL Database – command prompt

Today i want to test MySQL database

I use mysql-4.1.22-win32, setup and after install it,  i go to 
 

and it will display this window (need password) if you use it setting :
 
default window will appear like this:
 
after that we learn about command syntax on mysql

to know mysql version we can use this syntax:
mysql> select version();



to display database that exist:
mysql> show databases;


to create new database:
mysql> create database practise01;


to change to database that you want to choose:
mysql> use practise01;


to create new table:
mysql> create table tblTest01(id int);


to show table structures:
mysql> describe tbltest01;
or
mysql> show columns from tbltest01;


to show tables that exist:
mysql> show tables;


to alter table structure:
mysql> alter table tbltest01 add nama varchar(20);

to fill your data table:
mysql> insert into tbltest01(id,nama) values(1,"jimon");
point : insert into tablenm(col1,col2,…) values(datacol1,datacol2,…)
 

to display data from current table:
mysql> select * from tbltest01;
 

to update data from current table:
mysql> update tbltest01 set id=3,nama="cyntia" where nama="cintia";

to close window mysql command prompt:
mysql> exit;

ok have a nice try, hope this will help you to know mysql better.