How to Add Secondary files to the Logshipping database

One of my friend asked me how to add a file to a log shipped database without reconfiguring log shipping. I tested the following in SQL 2008 Enterprise edition server to find out the information and it worked fine :

1. I configured log shipping between the databases info system
2. Stopped and disabled all the backup, copy and restore jobs.
3. Added a secondary file named Infi_data1.ndf to the log shipped info system database in primary server
4. Manually took a transaction log backup for the database after adding the file
5. Manually copied the transaction log backup to secondary server
6. Manually restored that particular transaction log backup using the WITH MOVE option and WITH NO RECOVERY clause in secondary server

Basically while using the WITH MOVE option I would mention the newly created secondary file i had recently added to the log shipped database.

The following is the script to add a new file to the info system database and restoring the log backup in secondary server using no recovery or using standby option
ALTER DATABASE info system
ADD FILE
(
NAME = Infi_data1,
FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Infi_data1.ndf',
SIZE = 5MB,
MAXSIZE = 15,
FILEGROWTH = 10%
)

GO

RESTORE filelistonly from disk='D:\Database\SQLDBA\Infisys.trn'

GO

RESTORE log Info system FROM Disk='D:\Database\SQLDBA\Infisys.trn'
WITH MOVE 'Infi_data1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\Infi_data1.ndf',
norecovery

When Tempdb is full and occupied disk space?

Issue
SQL Server Services is not starting because temp database can’t grow further as it reached to its maximum size (All Disk Space is utilized). We can’t delete the temp database file from the disk because of disk issues.
What can be done to resolve the issue?
Resolution
To resolve the issue DAC should be enabled.
What basically we will do is moving the temp database on to some other drive.
Steps to do
Step1
Start SQL Server Service with minimal configuration

Start the command prompt with Administrative Priviliges

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -sInstancename -f

In case instancename is default  then u don’t provide the instance name

D:\Program Files\Microsoft SQL Server\MSSQL10.DELL\MSSQL\Binn>sqlservr.exe -f

Start another instance of command prompt with administrative priviliges.

Step2

sqlcmd -A -dmaster -E -SNL03-DF271\instance name

In case instancename is default  then u don’t provide the instance name

 sqlcmd -A -dmaster -E -SNL03-DF271\instance name

Move the temp database file to some other location
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\SQLLog\templog.ldf’);
GO
Once file get moved;Restart the SQL Server Services
Press Ctrl+C on the command Prompt in the first step
Restart the SQL Server Service.

How to get information which jobs are executing currently through T-SQL

I got to know this when I have to monitor more than 100+ jobs. I really have to put great effort to know what all jobs are running at a time and much more.

USE msdb 

GO

exec sp_get_composite_job_info @execution_status=1

GO

Other parameter for @execution_status
@execution_status  =  0 = Not idle or suspended,
1 = Executing,
2 = Waiting For Thread,
3 = Between Retries,
4 = Idle,
5 = Suspended,
6 = WaitingForStepToFinish,
7 = PerformingCompletionActions

Fragmentation

What is Fragmentation?

It is a general term used to describe various effects that can occur in indexes because of data modifications. There are two general types of fragmentation Internal and External.

Internal Fragmentation:

Internal Fragmentation often called physical fragmentation or page density.
Internal fragmentation is wasted space on index pages, both at leaf and non leaf levels.
 It occurs because of

•  Page splits leaving empty space on the page that was split and newly allocated page
• Delete operations that leave pages less than full

Internal Fragmentation means the index is taking more space than necessary leading to increased disk space
usage, more pages to read to process the data and more memory used to hold the pages in buffer pool.

External Fragmentation : It is where the pages or extents comprising the leaf level of a clustered or non clustered index are not in an efficient order.
Efficient order means where the logical order of the pages and extents is the same as the physical order of the pages and extents with in the data files.
It occurs:
Due to page splits and reduces the efficiency of ordered scans of part of a clustered or non clustered index

Difference Between Delete & Truncate Commands

• TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command
• You can use WHERE clause(conditions) with DELETE but you can't use WHERE clause with TRUNCATE
• A trigger doesn’t get fired in case of TRUNCATE whereas Triggers get fired in DELETE command
• TRUNCATE resets the Identity counter if there is any identity column present in the table where delete not resets the identity counter
• Delete and Truncate both are logged operation.But DELETE is a logged operation on a per row basis and TRUNCATE logs the de-allocation of the data pages in which the data exists. 
• TRUNCATE is faster than DELETE 
• Reason:When you type DELETE.all the data get copied into the Rollback Table space first.then delete operation get performed.That's why when you type ROLLBACK after deleting a table you can get back the data(The system get it for you from the Rollback Table space).All this process take time.But when you type TRUNCATE it removes data directly without copying it into the Rollback Table space.That's why TRUNCATE is faster.Once you Truncate you can't get back the data.
  
  
  Misconception : You Cannot Rollback  data when Using TRUNCATE Command.
  DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.
  
  

  USE tempdb
  GO-- Create Test Table
  CREATE TABLE TruncateTest (ID INT)
  INSERT INTO TruncateTest (ID)
  SELECT 1
  UNION ALL
  SELECT 2
  UNION ALL
  SELECT 3
  GO-- Check the data before truncate
  SELECT * FROM TruncateTest
  GO-- Begin Transaction
  BEGIN TRAN
  -- Truncate Table
  TRUNCATE TABLE TruncateTest
  GO-- Check the data after truncate
  SELECT * FROM TruncateTest
  GO-- Rollback Transaction
  ROLLBACK TRAN
  GO-- Check the data after Rollback
  SELECT * FROM TruncateTest
  GO-- Clean up
  DROP TABLE TruncateTest
  GO

  Misconception :It is a common mistake to think that TRUNCATE is not logged 
  This is wrong.Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast.
  The deallocation of the data pages is recorded in the log file,but the extents have been marked as empty for reuse. Therefore, BOL refers to TRUNCATE operations as "minimally logged" operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.