Monday, December 1, 2014

Truncate_Only Vs NUL

How to truncate and shrink the transaction log in SQL Server

 In SQL Server 2005

Backup log with truncate_only    -- It will truncate the log without taking backup of transaction log. This command can be run against full or bulk-recovery model, SQL throws out inactive log records for that database and puts database in simple recovery model and if you run the backup log against this database it will throw the below error:

Msg 4214, Level 16, State 1, Line 1
Backup LOG cannot be performed because there is no current database backup.

The above error message indicates the log chain has been broken and the log has been truncated.

In SQL Server 2008
Backup log with truncate_only   

It throws the error 'TRUNCATE_ONLY' is not a recognized backup option.

As per the books online we should switch the database to simple recovery model, shrink the file and switch it back to full recovery model. It is the replacement for truncate_only option in SQL Server 2008 onwards









About NUL

NUL is a device, It is similar to \dev\nul in Unix environment, whatever you writes to nul it will get erased. We should not use this option frequently, if our transaction log grown out of range and we don’t have any option to stop it in such scenarios you can use this option.

When you issue a command like BACKUP DATABASE TEST_DB TO DISK='NUL' then SQL Server forms inactive log records into a transaction log file and writes into a nul device which means the operating system will erase whatever written to nul device and you will see a message showing backup log successfully processed, and it wont switch your database to SIMPLE recovery model, that’s a main difference between truncate_only and NUL

But you should be very careful before writing data into NUL device, because though it doesn't switch the database in SIMPLE recovery model it will break the log chain and resets LSN number

Query to find LSN Number

select database_name, type, first_lsn, last_lsn ,checkpoint_lsn ,database_backup_lsn
from msdb..backupset where database_name = 'test_db'

Thursday, September 25, 2014

The WSFC cluster could not bring the Network Name resource with DNS name 'ServerName' online. (Microsoft SQL Server, Error: 19471)

While configuring SQL Server 2012 Always on Listener name you may encountered the below issue :

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057The WSFC cluster could not bring the Network Name resource with DNS name 'ServerName' online. The DNS name may have been taken or have a conflict with existing name services, or the WSFC cluster service may not be running or may be inaccessible. Use a different DNS name to resolve name conflicts, or check the WSFC cluster log for more information.
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

1. Verify the listener AD object has proper permissions or not, If not ask your admins to give proper permissions to that object

2. Later do nslookup of that objet and get the IP address of it and configure the listener with static port of the IP which you got in the nslookup.

3. If that doesn't work then remove the listener from the SQL AG and ask your admins to add DNS to the object first and then add listener at the AG it will work.


Friday, September 5, 2014

SQL Server Virtual Service Accounts

SQL Server Virtual Service Accounts
From SQL Server 2012 onwards if you observe the SQL Server configuration manager you will notice SQL Server Engine and SQL Server Agent will be assigned with two different accounts like NT Service\MSSQLServer or NT Service\MSSQL$InstanceName and NT Service\SQLServerAgent or NT Service\SQLAgent$Instance, these two are known as virtual service accounts and were introduced in SQL Server 2012. For earlier versions like SQL Server 2005, 2008 if you observe you will see NT Authority\System or Local System but there is no NT Service\MSSQLServer.

Virtual Accounts was introduced in windows 2008 R2 which is managed local account with auto-password. These virtual accounts are unique instances of NT Service account and we cant find this accounts in Local Users and Groups and they cannot be deleted and you cannot change password or you cannot get password of the virtual account that will be auto password maintained by OS. When you install SQL Server 2012 on top of windows 2008 R2 you will see these virtual accounts in the SQL Server configuration manager, DBA’s generally runs SQL Service with Local system account or Domain Account with limited privileges, tough it is tough periodically they will change the password of domain accounts for security reasons. If we use virtual accounts then we can avoid this situations like changing passwords periodically.

For some reason if you change the virtual account to Local System for SQL Server Engine. Later you want to assign virtual account back to SQL Service, now you cant browse this account because it is not found in local users and groups, So you need to type the account as NT Service\MSSQLServer, For password you don’t know the password if you give any password system will say it’s an incorrect password, So you need to leave the password box blank and then click apply to get the auto generated password automatically. Like this you can change the service accounts to assign virtual accounts.

Wednesday, August 13, 2014

How to view collation of server, database and columns

Below are the commands to view server collation, database collation and collations of columns

To view server level collation setting

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));     

To view database level collation

SELECT name, collation_name FROM sys.databases;

To view column level collation

SELECT name, collation_name FROM sys.columns
where object_id in (select object_id from sys.objects where TYPE='U' and name = 'Client')


Tuesday, July 22, 2014

Hide all databases from users / login in SQL Server

Hide all databases from users / login in SQL Server


1. Create SQL login with some name kalyan_login, here dont specify any user mapping

2. From SSMS

USE master;



3. Granting database access to kalyan_login

USE master;





Now the login is able to view only one database that he has access

You can use the below command to hide databases from all logins


Tuesday, July 8, 2014

Identity Column value jumps to 1001 in SQL Server 2012

Recently we upgraded database from SQL Server 2008 to SQL Server 2012 version, We identified an issue with Auto increment identity columns.

If we restart SQL Server 2012 instance Identity column (auto-increment) value suddenly jumps to 1001 if the datatype is int and jumps to 10000 if the datatype is bigint.

Dont use auto-increment identity columns and use sequence feature in SQL Server 2012.

Wednesday, June 18, 2014

VI Editor Commands in Linux

Vi editor commands

Input commands with (esc or ESCape)

Appends after cursor
Inserts before cursor
Opens a new line below the cursor
Opens a new line above the cursor
Deletes single word
Deletes entire line
Undo last change
Undo all changes of single line
-yy- or -Y-
Copies single line
Copies 5 lines
Deletes 5 lines
Paste the line after the cursor
Paste the line before the cursor
Deletes a character
Saves file with the given name
Save and exit from vi
Quit without save
Same as wq
Goto last line
Goto specified line number
Displays current line number and file information
Searches for a string in the file
Repeats the search towards down (Find Next)
Repeats the search towards up (Find Previous)
:set list
Show invisible characters
:set nolist
Don’t show invisible characters
:set number
Displays line numbers
:set nonumber
Line numbers off
:set ignorecase
Ignore the case (helps while searching for a string in the file)
:set noignorecase
Switch off the ignore case option
:%s/Historical/historical_new /g   
Replaces string Historical to historical_new
% -  All lines
/g  - Replace all occurrences in all lines
:5,20s/kalyan/kalyan_new /g
Replaces string from 5 to 20 lines only
:%s/SearchString/Replacewith /gi
-gi - Indicates replace string globally ignores case
Replace only in current line, If you include %infront of s it replaces all lines


Tuesday, June 17, 2014

Filestream and FileTables in SQL Server

Managing Unstructured Data in SQL Server
Prior to SQL Server 2008, storing and managing unstructured data is a bit difficult one,  for that we have only two options varbinary and image fields to store unstructured information. Though it provides transactional consistency it badly hurts the performance. The other approach many people follow is storing the path of unstructured data in the table, it gives good performance but it has its own side effects like it doesn’t ensure transactional consistency and it is difficult while doing backup and restore we need to make sure the physical paths should available in the target server.
FILESTREAM -- A new feature introduced in SQL 2008 which allows you to store BLOBS (word documents, images, pdf files) in the NTFS file system instead of database. As it is NTFS SQL Server takes an advantage of streaming capabilities and maintains transactional consistency of the data. While accessing FILESTREAM it uses streaming API and it will not use SQL Sever buffer pool, hence it will not SQL Server memory for processing. FILESTREAM is not a datatype and it is a attribute assigned to varbinary(max) column, then it becomes filestream enabled column.
If you store any image or pdf on the filestream enabled column then only pointer to the file will be stored in the column and the actual file will get stored in the NTFS.
By default installation this feature will be in disable state, In order to use this feature it has to be enabled.
To enable FILESTREAM and It can be enabled in three levels :
  1. Configuration Manager - SQL Server - Properties - On FileStream Tab - Check the below items
    1. Enable FileStream for Transact-SQL Access
    2. Enable FileStream for file I/O Access
    3. Windows Share Name
      Specify Share Name SQL2012_FileTables
    4. Allow remote clients access to filestream data
Enable FileStream for File I/O Access                  -- If you are interested in enabling read and write FILESTREAM data from windows then we need to check this check box.
Allow remote client access to filestream data   -- If remote clients need to access the file share using their stored procedure or application this feature must be enabled.
Using T-SQL
Level: Level of FILESTREAM functionality to enable, a integer between 0 and 3. If not specified, level 3 is default.
0 - FILESTREAM functionality disabled.
1 - Enable FILESTREAM for T-SQL access.
2 - Enable FILESTREAM for T-SQL and file I/O streaming access but do not allow remote clients file I/O streaming access.
3 - Enable FILESTREAM for T-SQL and file I/O streaming access and allow remote client streaming access.
Share: Name of windows share that is created for file I/O streaming access to FILESTREAM data. If not specified, instance name is default.
The major difference between FILESTREAM enabled and a normal database is
For a normal database we will be having only MDF and LDF files
For a FileStream enabled database will be having MDF, LDF and FileStream Container
FILESTREAM Container is a special folder in the NTFS where SQL Server will store all the FILESTREAM data as files in the disk.
PRIMARY ( NAME = FSTreamData, FILENAME = 'C:\Temp\FStreamData.mdf'),
FILEGROUP FStreamFolder CONTAINS FILESTREAM( NAME = FStreamFolder, FILENAME = 'C:\Temp\FStreamFolder')
LOG ON ( NAME = FStreamLOG, FILENAME = 'C:\Temp\NP\FStreamLOG.ldf')
In the above statement 3rd line on CREATE DATABASE Statement shows FILSTREAM Container, which creates a folder in C:\Temp with name FStreamFolder, Under which there are two folders $FLOG and filestream.hdr, The $FLOG is similar to transaction log file in the database and filestream.hdr consists metadata information used by SQL Server, (Please don’t make any changes to that file).
Now Creating a table with FileStream Enabled column
Use FileStreamTest
CARIMAGE varbinary(max) FILESTREAM Null)
For every FILESTREAM enabled table you should create a column with UNIQUEIDENTIFIER ROWGUIDCOL.
After executing the above command a new table will get created on the FileStreamTest Database and if you observe the c:\temp\Fstreamfolder a new folder will be created with GUID as its name, SQL Server will creates one folder per each table when there is FILESTREAM enabled column exists.
To Store and retrieve information from a FILESTREAM Enabled column
-- Variable to Store ImageData
select * from CARS
Select carid, CARIMAGE.PathName() from CARS
From SQL Server 2012
In SQL Server 2012 FileTable concept was introduced and it will get more simplified than FILESTREAMs, File table is a feature newly introduced in SQL Server 2012 is an enhancement of FILESTREAM which was introduced in SQL Server 2008.
FileTable is a special where you need not to specify column definition while creating a filetable, the schema for the filetable is fixed.
Below is the syntax for creating FILETABLE in SQL Server 2012
PRIMARY ( NAME = filetable_data,FILENAME = 'C:\temp\filetable.mdf'),
LOG ON ( NAME = filetable_log,FILENAME = 'C:\temp\filetable.ldf')
SELECT * FROM sys.database_filestream_options WHERE database_id=DB_ID('FileTeableDemo')
USE FileTeableDemo
CREATE TABLE MyDocuments AS FileTable
CREATE TABLE MyImages AS FileTable
To Insert any records into FILETable that can be done in two ways: 1. Using T-SQL Like we did for Filestream and 2. Right Click on FileTable on which you want to insert records - Explore file table - Copy and paste images into it.
select * from mydocuments
select * from myimages

Thursday, June 12, 2014

Basic Linux commands for DBA's Part IV

Compression Related

Compress the files, Each single file will be compressed as one file and a new file will get created with filename.gz
-c -- If you specified -c it displays standard out to console and creates a new gz file without delete the original file
$gzip *.err    -- Compress all .err files and creates new file per .err with .err.gz
$ gzip errorlog
$ ls - lrt
$ gzip -c errorlog > errorlog1.gz
$ ls -lrt
$ gzip *.err
Uncompress the file that was compressed using gzip or compress commands
-c -- displays standard output to console and without unzip the file
$  gunzip *.err.gz -- Uncompress all .err.gz files into .err files
$ gunzip errolog.gz
$ ls -lrt
$  gunzip *.err.gz
Tar stands for Tape Archive in Linux. This command is used to create a archive for collection of files into highly compressed files in unix / linux systems
Tar Examples
-c -- Creates a new .tar archive file
-v -- Verbose mode, shows the progress of .tar file
-f -- filename
[root@Kalyan workspace]# tar -cvf mysqlbinaries.tar /local/mnt/workspace/mysqlbinaries/
tar: Removing leading `/' from member names
[root@Kalyan workspace]# ls -lrt
total 374356
drwxr-x--- 2 kalyan users      4096 May 27 04:07 mysqlbinaries
-rw-r--r-- 1 root   bin   382955520 Jun  2 06:50 mysqlbinaries.tar
If you observe the above command tar -cvf, It just creates a .tar files with the collection files in the mysql binaries folder, but it wont compress the collection size.
[root@Kalyan mysqlbinaries]# du -h .
366M    .
[root@Kalyanmysqlbinaries]# cd ..
[root@Kalyanworkspace]# du -h mysqlbinaries.tar
366M    mysqlbinaries.tar
The size of whole mysqlbinaries folder and size of .tar file is same.
-z -- This option helps to create a compressed gzip archive file.
$ tar -cvzf mysqlbinaries.tar.gz /loca/mnt/workspace/mysqlbinaries
$ tar -cvzf mysqlbinaries.tgz /loca/mnt/workspace/mysqlbinaries
[root@kalyan workspace]# du -h mysqlbinaries.tgz
358M    mysqlbinaries.tgz
[root@kalyan workspace]# du -h mysqlbinaries.tar
366M    mysqlbinaries.tar
-j -- This option is used to create bz2 Archive file, it compress and creates archive file less than the size of gzip but it will take more time to compress an decompress the file. It is used to create highly compressed file (.tar.bz2 or tbz or tb2)
-x -- This option is used to untar or extract the contents of an archive file
-C -- Usually if you untar any file using -x option then it will extract the contents to the current folder, if you want to change the extract location to some other folder you need to specify -C option
$ tar -xvf abc.tar      -- Extracts the contents to current folder
$ tar -xvf abc.tar -C /usr/kalyan/tmp  -- Extracts the contents to specified folder
-t -- This option helps to list the contents of any tar file without extracting it
$ tar -tvf abc.tar            -- Lists the contents of the given tar file
--extract -- This option helps to extract only particular file from the .tar file
$ tar --extract --file=<.tarfile> 
$ tar --extract --file=mysqlbinaries.tar local/mnt/workspace/mysqlbinaries/MySQL-client-advanced.x86_64.rpm
-r -- This option helps to add / append a file to existing tar file
$ tar -rvf
$ tar -rvf mysqlbinaries.tar local      --- It adds local folder to mysqlbinaries.tar file

Wednesday, June 11, 2014

Basic Linux commands for DBAs - Part III

Search Related

Description And Example
 The grep command is used to search for a text in the file or from a list
 $ grep "linux" file1.txt           -- Search for lines containing "linux"
 $ grep kalyan/etc/passwd   -- Search for lines containing "kalyan"
-i- case insensitive serach
$ grep -i "kalyan"  file1.txt     --Search for lines containing "kalyan" and ignore the cas
-n displays line number
$ grep -i -n "linux" t1.txt     -- Search for lines containing "linux" and ignore the case and displays line number on which line it find the word linux
Search string in multiple files
$ grep -i "linux" *.txt           -- Search for lines containing "linux" in all *.txt files
-c Displays Occurrences counts from
$ grep -ic "linux" *.txt          -- Search for lines containing "linux" in all .txt files and displays only occurrences count
-v Inverts the search to display lines that doesn't match
$ grep -vic "linux" *.txt           -- Displays lines that doesn't has "linux" word on it and displays only occurrences count
Regular Expressions with Grep
^d        -- Displays lines start with d
$ grep "^t" -in t1.txt               -- Displays lines start with T and -i- is used to ignore the case
$ grep "^[TL]" -in t1.txt           --Displays lines start with either T or L
$ grep "[^0-9]" -in t1.txt           --Displays lines contains atleast one-numeric value, the meaning of ^ caret will change if you                
                                                        enclosed with square braces.
$ ps -ef | grep "mysql"            --Displays processes which has mysql in it.
$ ls - l | grep "^d"                     --Displays only directories
$ grep "x$" t2.txt                      --Displays lines ends with "x"
$ grep "5\.." t1.txt                    --Suppose we have data in a file with version numbers like 5.1, 5.x and 5.5 like that, It will search those lines and give you output
$ grep "[A-Z][A-Z]"  t1.txt         --Displays lines containing two capital letters (Ex : EA, SA etc.,)
Find command is used to locate files in the linux / unix server
Syntax  : fine where-to-look criteria what-to-do
All arguments to find command are optional.
$ find                              -- Displays paths of all files in the current directory and sub-directories.
$ find . -print                 -- Displays exact same result as above command
$ find / -name "test.txt" -- Search for the file test.txt on all the current file system
$ find . -name "Kalyan*" -- List filenames starts with Kalyan, If you wan to ignore case then use -i.
$ find . -name "*.txt"      -- Lists  only .txt files
We can also specify multiple locations as below to search for any particular file
$ find /var/lib /usr/tmp $HOME -name "*.txt"           -- File locations must be separated by space.
-type option is a parameter to separate the files and directories which can be used along with find command, This can be used only if you want to make your search specific to one particular type
$ find . -type f -name "t1.txt"                --Search for a file with name t1.txt in the current location
$ find . -type d -name "binaries"          -- Search for a directory name binaries in the current location
$ find . -type f -name "*.java"
$ find . -type f -perm 0555 -print           -- Displays files which has permissions 555 (read and execute for all )
$ find . -type f -perm 0777 -print -exec chmod 644 {} \; -- Search for files which has 777 and changes permissions to 644
$ find . -type f -name "t1.txt" -exec rm -f {} \;     -- find and remove t1.txt file in the current folder
$ find . -type f -empty            -- Find all empty files
$ find . -type d-empty            -- Find all empty directories
-user  -- This option helps to find files based on the owner type
$ find . -user root       -- Displays files whose owner is root in the current directory
$ find . -group bin         -- Displays files whose group is bin in the current directory
$ find . -group bin -name "t2.txt"       -- Search files by group
-size and -mtime  -- Size option is helps to search files by size and mtime helps to search files by modification time
$ find . -size +10M          --Find files bigger than 10 MB size in current directory
$ find . -size -10M           --Find files lesser than 10 MB size in current directory
$ find . -mtime 1             --Search files and folders that are modified in last 24 hours
$ find . -mtime 10           --Search for files and folders that are modified in last 10 days
$ find . -mtime 10 -type f    -- Search for only files that are modified in last 10 days
$ find . -mtime 10 -type d    -- Search for only directories that are modified in last 10 days
$ find . -type f \( -name "*.txt" -o -name "*.t" \)   -- Search files with multiple extentions
Find and Copy
find . -type f \( -name "*.txt" -o -name "*.t" \) -exec cp {} txtfiles
 find . -type f -name "*.txt" -exec grep -i "linux" {} \;


Friday, June 6, 2014

Basic Linux commands for DBAs - Part II

Permissions Related

Description And Example
With help of this we can change the file permissions of a file, which can be assigned permissions to user, group and others
4 - (read), 2 - (write) and 1 - (execute).
chmod 777 filename -- read, write and execute for all
chmod 755 filename --rwx for owner, rw for group and others.
First "-" indicates it’s a normal file if first character is "d" then it is a directory, first set or rwx (read, write and execute) belongs to owner, and second set of rwx belongs to group and third set of rwx belongs to others.
Helps to change the owner and group of the file
$ chown filename
$ chown kalyan file1 -- It changes the owner of file to kalyan login
How to change the owner and group
$ chown :[groupname] filename
$ chown kalyan:users file1 -- It changes the owner as "kalyan" and group as "users" for file1
How to change owner to all files in any folder
$ chown -R
$ chown -R Kalyan:Users /usr2/kalyan/jpgs
Helps to change the group of the file, The difference between chown and chgrp is, using chown you can change the owner and group of a file, but using chgrp you can change on the group of the file not the owner
$ chgrp filename
$ chgrp bin /usr2/kalyan/myfile -- It changes the group of myfile to bin
$ chgrp -R bin /usr2/kalyan/executables/ - It changes the group to bin for all files in the given path

Disk Space Related

Description And Example
The df command stands for "disk filesystem", It displays how much space is occupied and how much space is available on file systems not directories and files
$ df -- Displays file systems usage statistics when you run without any argument
$ df -h -- Displays in human readable format
$ df -h test -- It displays usage information of the filesystem where test resides, it wont give you size of test directory.
-i- -- Displays inode information instead of block usage
$ df -i. -- Displays inode information instead of block usage
-a -- Shows all file system disk usage
-k -- Displays output in Kilobytes
-i- --Displays inode information
-t --Displays disk space usage for each of the specified type of file system
-T --Displays file system type
The du command stands for "disk usage", this command will display usage information of a directory and sub-directory and displays the blocks that are occupied.
-a -- Write counts for all files, not just directories
-h -- Displays output in human readable format
-c -- To get the total usage in the output
-s -- To get only total size in form of summary
$ du -h -- Displays usage information of the given directory
$ du -sh --Displays only size of the directory (nothing else will be printed)
$ du -ahc --Displays all files human readable format including total size of the folder
$ du -sh --exclude="*.txt" --Exclude the .txt files and provides the size of remaining all files

Thursday, June 5, 2014

Basic Linux Commands for DBAs

General Linux Commands for DBAs

Displays current working directory
$ pwd
List the current directory contents
$ ls
 ls -l
List the current directory contents with extra details
$ ls -l
Displays files for the specified path
$ ls
 cd ~ / cd
User home directory
$ cd  / $ cd ~
 cd -
Previous current directory
$ cd -
Clear screen
$ clear
Creates an empty file
$ touch abc
Removes specified file
$ rm abc
 rm -rf
-f force -r recursive, Removes complete directory with its contents
$ rm -rf
 cp file1 file2
Creates a copy of file1 to file2, if destination is file it creates a duplicate copy, if destination is directory it moves the file into that directory.
$ cp file1 file2
 cp -r dir1 dir2
Creates a duplicate copy of whole directory
$ cp -r dir1 dir2
 mv file2 file3
Renames given file to new file
$ mv file2 file3
Displays contents of the file
$ cat file1.txt
Displays contents of the file page by page, for next page {Space}  to quit {q}
$ more file1.txt
Displays first 10 lines from the file
$ head file1.txt
$ head -20 file1.txt (first 20 lines)
Displays last 10 lines from the file
$ tail file1.txt
$ tail -20 file1.txt (last 20 lines)
 tail -f
Displays contents of the file as it grows, starting with last 10 lines, to quit {ctrl+c}
$ tail -f Kalyan2.err
Displays current date and time
$ date
Display help for the given command
$ man cat
 command > filename
Redirects output to specified filename
$ date > date.txt
 command >> filename
Appends the redirected output to existing file
$ date >> date.txt
 sort file1
Sort the contents of the file
$ sort file1
 | (Pipe)
Pipes the output to other command
$ cat file1 | sort
Displays all current processes
$ ps
Displays all running processes
$ top
Kills the process Id
$ kill
Displays current months calendar
$ cal
Displays current uptime
$ uptime
Displays who is online (All the users who are connected to this host)
$ w
Displays the current logged in user name
$ whoami
Displays information about the user
$ finger kalyan
Displays possible locations of the given application
$ whereis mysql
$ whereis perl
Displays which app runs
$ which mysql
$ which perl
Prints the system information
$ uname
 uname -a
Prints Kernel information
$ uname -a
 uname -o
Displays operating system
-o Operating System
-v Kernel version
-n Node Name
-m Machine
-p Processor
-a All
$ uname -o
Displays number of newlines, words and bytes in the file
$ ls -lrt | wc - l
Displays count for ls -lrt
Displays the name and value of each shell vairable
$ set
To view value in any environment variable
Displays all environment variable
$ printenv