SHRINK LOG File SQL

SQL Server Database Log File has grown exponentially over a period of time and now causing DISK space issue, how to reduce | Shrink Log file SQL Database (.ldf) size?
Steps to truncating log files and shrinking your database

1. Get logical / physical name of your database log file (LDF)
2. Truncate LOG File
3. Shrink LOG File

Let’s see how we SHRINK LOG File using TSQL
STEP 1 – Get logical / physical name of your database log file

use <[your database name]>
exec sp_helpfile

STEP 2 – Truncate LOG File

-- replace <[your database logical log filename]> with Logical file name, which we get in STEP 1
USE <[your database name]>
GO
BACKUP LOG <[your database logical log filename]> WITH TRUNCATE_ONLY
GO

STEP 3 – Shrink LOG File

-- replace <[your database logical log filename]> with Logical file name, which we get in STEP 1
USE <[your database name]>
GO
DBCC SHRINKFILE (<[your database logical log filename]>, 1) 

GO

What are the possible solutions, if my transaction log is FULL?

• Backing up the log
• Freeing disk space so that the log can automatically grow.
• Moving the log file to a disk drive with sufficient space.
• Increasing the size of a log file.
• Adding a log file on a different disk.
• Completing or killing a long-running transaction.

Why the size of LOG file is keep growing ?
Every SQL database has a transaction log that records all transactions and database modifications made by each transaction. If log record is never deleted from transaction log, the logical log would grow until it filled all the available space on the disks holding the physical log files.
BUT WHY SQL Server is keeping track of every transaction in transaction log file?
It’s a design of SQL Server, which uses a write-ahead log. A write-ahead log ensures that no data modifications are written to disk before the associated log record. Thus transaction Log supports

• Recovery of any individual transaction
• Recovery of all incomplete transactions when SQL Server is re started
• Rolling a restored database forward to the point of failure

Can we disable this Write ahead operation to avoid disk filling operation ?
NO, SQL Server always performs a write log operation but we can set a database recovery model to SIMPLE, which will automatically truncate the transaction log on CHECKPOINT.
Check my previous post, “difference between SIMPLE and FULL recovery Model” and “SQL Server Recovery Model” to know more about SQL Server recovery models.
When size of the log file is physically reduced?

There are three events, which forces SQL Server Database file to Shrink

• When a DBCC SHRINKDATABASE statement is executed.
• When a DBCC SHRINKFILE statement referencing a log file is executed.
• When an autoshrink operation occurs

Is Truncate LOG File and Shrink a log file is same thing ?
NO, both are altogether different operation. Shrinking means releasing free space to the Operating System whereas process of deleting log records to reduce the size of the logical log is called truncating log.
If a LOG file is 100% used and active then Shrink operation will reduce the size of the transaction log file.
Here are some facts about SHRINK and TRUNCATE

• Shrinking a log is dependent on first truncating the log
• Log truncation does not reduce the size of a physical log file
• Log truncation reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log.
• A log Shrink operation removes enough inactive virtual logs to reduce the log file size to the requested size, requested size can not be less than inactive size.

To make it more clear, lets take an example, if you have a 10 GB log file that has been divided into 10 1 GB virtual logs, then the size of the log file can only be reduced in 1 GB increments.
The file size can be reduced to 1 GB sizes such as 1 GB, 2 GB, 3 GB .. 9 GB , but it cannot be reduced to sizes such as 1150 MB or 2500 MB as Virtual logs that hold part of the logical log cannot be freed.
NOTE: So to reduce a log file size, we first need to truncate and then only we can shrink log file.
I am Truncating and Shrinking LOG file but size is not getting reduced, What is possible cause ?
If all the virtual logs in a log file hold parts of the logical log, the file cannot be shrink until a truncation marks one or more of the virtual logs at the end of the physical log as inactive. When any file is shrunk, the space freed must come from the end of the file.
How frequently I should SHRINK Database LOG File ?
Ideally, you should not be shrinking your LOG files, if you have followed all Best Practices, which I mentioned in the last of the article.
I can think of only two frequent scenarios where I go ahead and shrink log file

1. My database is full recovery model and transaction log is never being backed up, which made my log in GB’s TB’s. I have see a database where a data file is MB’s and transaction log was in GB’s. To correct this issue we need to regular backup transaction log.
2. On demand large BULK INSERT, which filled up transaction log.

Is it GOOD to shrink Database Log file ?
Without a valid business or technical reason, we should shrink database log. If you shrink your log and log file fills up then it would auto grow but that is as additional delay to your transactions.
If you are trying Just to keep a disk space free and available for others to use then definitely this is not a good thing to do.
What is the best practice to manage the SQL Log files ?
Database LOG File Best Practice

• Keep transaction number low I would prefer single log file, if possible, this help during recovery.
• Place the LOG file on the fastest available redundant disk volume
• Frequently backing up the log frequently, depends on business data recovery policy
• Initialize the database log file with required space to avoid auto grow during production hours.
• Place a log file on a larger DISK volume so that the log can automatically grow, incase that is required.
• Keep your VLF count low
• Defrag the disks on which your tranaction logs reside to get rid of disk file fragmentation.  (requires downtime)
• Back up the ‘tail of the log’ in a disaster scenario if possible

SQL Server Management Studio (SSMS) is slow

 My SQL Server Management Studio (SSMS) takes approx. 2-3 minutes to open, is there any way to speed that up?

YES, we can speed up the opening time for SQL Server Management studio by taking some preventive actions.
When ever we open a SQL Server Management Studio, if performs the following actions

1. Initiate a windows process by creating a new Process ID at Windows level, which is subject to be scanned by Antivirus software, in you have any.
2. Splash Screen displaying “Microsoft SQL Server”
3. Ask for Connection Information like
   • Server Type
   • SQL Server Name
   • Login Credentials like Authentication Mechanism, LOGIN ID and Password, in case this is SQL Server
4. Resolve the Server name and sent Authentication Request to Server.
5. SQL Server Management Studio also perform a ”checks for Server certificate revocation and publisher’s certificate revocation
6. Build SSMS environment by user specified / default settings
7. and then you finally use SQL Server Management Studio

Ohhh you never thought, it’s going to do so much by when you just open a SSMS. So now we need to simple and shorten these tasks to speed up the SSMS performance.

Steps to Boost up SSMS opening Time
Configure your antivirus to Exclude the scan of SSMS.exe if your enterprise policy doesn’t allow that than put SSMS at least in Low Risk processes. I am showing a screen shot of McAfee as I use that as antivirus

1. Instead of opening SQL Server Management studio by clicking Start >> All Programs >> Microsoft SQL Server >> SQL Server Management Studio. Instead of this either open from a RUN MENU by typing “SSMS.exe /nosplash”, this will skip the Splash Screen displaying “Microsoft SQL Server”
2. if we are going to connect mostly one identified SQL Server Instance and database, we can supple these settings too while opening SSMS

How to Restore only Corrupted pages from a database backup

How to Shorten the recovery time in case of database corruption? 
OW to FIX damaged page in SQL Server ?
HOW to FIX SQL Server ERROR : 8928 ?
HOW to FIX page level corruption in SQL Server Database ?
Error 8928 Details
Msg 8928, Level 16, State 1, Line 1
Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:223) could not be processed.  See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data), page (1:223). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data). Page (1:223) was not seen in the scan although its parent (1:923) and previous (1:222) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data). Page (1:224) is missing a reference from previous page (1:223). Possible chain linkage problem.

PAGE Level database restore is new feature which was introduced in SQL Server 2008 onwards.
This is a new feature in SQL Server 2008, where we can restore only some corrupted pages from a good database backup.
For example you have a 100 Gb database and only 1 page is corrupted than we can save recovery time by restoring a single page instead of a 100 GB database.

STEP 1 - Check Database For corruption
We can check database integrity by using DBCC CHECKDB command, to see weather there is corruption in database of not.

Looking at error message, we can clearly identify that there is corruption on page 223 as we can see this message. Object ID 2105058535, index ID 2, partition ID 72057594038910976, alloc unit ID 72057594039828480 (type In-row data): Page (1:223) could not be processed. 
STEP 2 – Restore faulty page from a GOOD Backup – PAGE Level Database Restore
Now we need to restore faulty pages from a SQL Server backup, that means restore only faulty pages. This is a new feature in SQL Server 2008, where we can restore only some corrupted pages from a good database backup.
For example you have a 100 Gb database and only 1 page is corrupted than we can save recovery time by restoring a single page instead of a 100 GB database.
SQL Command to perform a page level restore

use master
go
RESTORE DATABASE DBA PAGE = '1:223' FROM DISK = 'C:\temp\DBA_before_curruption.bak';
go

STEP 3 – Backup and Restore Current TRANSACTION LOG Backup
If you read the restore informational messages, which we received in last step states that there is difference between the LSN number.
Processed 1 pages for database ‘DBA’, file ‘DBA’ on file 1.

The roll forward start point is now at log sequence number (LSN) 43000000055600001. Additional roll forward past LSN 43000000058400001 is required to complete the restore sequence.
RESTORE DATABASE … FILE=<name> successfully processed 1 pages in 0.098 seconds (0.079 MB/sec).
To correct this LSN number, we need to backup the current log and restore in a current database, using the following syntax.

use DBA
BACKUP LOG DBA TO DISK = 'C:\DBA_log.bak' WITH INIT;
GO

use master
GO
RESTORE LOG DBA FROM DISK = 'C:\DBA_log.bak';

This is going to be pretty quick as only page level transactions will be rolled back or rolled forward, you can see that in message where backup log size was in MB’s but restore was kind of ZERO only.
Processed 5 pages for database ‘DBA’, file ‘DBA_log’ on file 1.

BACKUP LOG successfully processed 5 pages in 0.020 seconds (1.684 MB/sec).
Processed 0 pages for database ‘DBA’, file ‘DBA’ on file 1.
RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).

STEP 4 – Verify corruption has been resolved and data is consistent

Re-execute DBCC CHECKDB to ensure and verify that corruption has been removed and database is health now.

TAGS : access database corruption, client level logo page, database corruption, database corruption causes, database page restore, entourage database corruption, esent database corruption, exchange database corruption, page level, recover corrupted database, recover corrupted database, restore corrupted page, restore database page, restore database page in case of database corruption, sql database corruption, sql server page restore

 

How to backup SQL table ?

Backup SQL table, have you ever tried to backup a single SQL table inside a database? Let’s see How to backup SQL table | SQL Table Backup Restore
DOES SQL Server supports table level backups ?
Backup Types are dependent on SQL Server Recovery Model. Every recovery model lets you back up whole or partial SQL Server database or individual files or filegroups of the database. Table-level backup cannot be created, there is no such option. BUT there is a workaround  for this
Taking backup of SQL Server table possible in SQL Server. There are various alternative ways to backup a table in sql SQL Server

1. BCP (BULK COPY PROGRAM)
2. Generate Table Script with data
3. Make a copy of table using SELECT INTO
4. SAVE Table Data Directly in a Flat file
5. Export Data using SSIS to any destination

Let’s see how we can use these methods to take table backup in sql server
To make it more clear, let’s take example, we want to backup SQL table named "Person.Contact", which resides in SQL Server AdventureWorks sample database, which has 19972 records and table size is 6888 KB
Method 1 – Backup sql table using BCP (BULK COPY PROGRAM)
To backup a SQL table named "Person.Contact", which resides in SQL Server AdventureWorks, we need to execute following script, which

-- SQL Table Backup
-- Developed by DBATAG, www.DBATAG.com
DECLARE @table VARCHAR(128),
@file VARCHAR(255),
@cmd VARCHAR(512)
SET @table = 'AdventureWorks.Person.Contact' --  Table Name which you want to backup
SET @file = 'C:\MSSQL\Backup\' + @table + '_' + CONVERT(CHAR(8), GETDATE(), 112) --  Replace C:\MSSQL\Backup\ to destination dir where you want to place table data backup
+ '.dat'
SET @cmd = 'bcp ' + @table + ' out ' + @file + ' -n -T '
EXEC master..xp_cmdshell @cmd

Note -

1. You must have bulk import / export privileges
2. In above Script -n denotes native SQL data types, which is key during restore
3. -T denotes that you are connecting to SQL Server using Windows Authentication, in case you want to connect using SQL Server Authentication use -U<username> -P<passord>
4. This will also tell, you speed to data transfer, in my case this was 212468.08 rows per sec.
5. Once this commands completes, this will create a file named "AdventureWorks.Person.Contact_20120222" is a specified destination folder

Alternatively, you can run the BCP via command prompt and type the following command in command prompt, both operation performs the same activity, but I like the above mentioned method as that’s save type in opening a command prompt and type.
bcp AdventureWorks.Person.Contact out C:\MSSQL\Backup\AdventureWorks.Person.Contact_20

backup which can not restored is of no use, let’s perform a quick restore to verify that this table level backup do works….
Restore SQL table backup using BCP (BULK COPY PROGRAM)
The following script will help you to perform a table level restore, which we backed up in above steps

BULK INSERT AdventureWorks.Person.Contacts_Restore 
    FROM 'C:\MSSQL\Backup\Contact.Dat' 
    WITH (DATAFILETYPE='native'); 

backup which can not restored is of no use, let’s perform a quick restore to verify that this table level backup do works….
Restore SQL table backup using BCP (BULK COPY PROGRAM)
The following script will help you to perform a table level restore, which we backed up in above steps
BULK INSERT AdventureWorks.Person.Contacts_Restore FROM 'C:\MSSQL\Backup\Contact.Dat' WITH (DATAFILETYPE='native');

Method 3 – Backup sql table using SELECT INTO
SELECT INTO statement selects data from one table and inserts selected data into a different table. This is nothing just like making a copy table. This will make a copy of a table inside a database only.
I do personally use this statement, prior to make changes to production database if table if of few MB’s. Don’t use this for large tables, this might fill up entire space of your database / drive.
The following Script will create a table name Contacts_Copy_20120221, and copy all data from table Contact to this newly created table.
select * into AdventureWorks.Person.Contacts_Copy_20120221 from AdventureWorks.Person.Contact
Method 4 – Backup sql table using SAVE Table Data Directly in a Flat file
When you execute any Select statement, SQL Server by default shows you result in result area, but we can change that option and set
when we execute a statement, sent the output to a flat file, instead of showing that on SSMS screen.
This is how we Backup sql table using SAVE Table Data Directly in a Flat file

 

Indexes on SQL Server 2008

This topic, will let you know the very basic knowledge of Indexes in SQL server 2008. Lets begin.. and this small blog can be very useful to your SQL knowledge:
There are 8 types of SQL Server indexes in 2008:
a) Clustered
b) Non Clustered
c) Unique
d) indexes with included column
e) Full Text
f) Spatial

g) Filtered
h) XML
We have four different index structures on which above mentioned packages depend:
a) B- Tree -> Indexes depending on this structure are Clustered, Non-Clustered, Unique, non clustered with included, indexed views, spatial, filtered index
b) Token based functional Index -> Indexes depending on this structure are Full Text indexes
c) Internal Tables (node tables) -> Indexes depending on this structure are XML Primary Index
d) B+- Structure ->Indexes depending on this structure are XML Indexes

 

Database Mirroring Enhancements in SQL Server 2008 from 2005

Database mirroring is an alternative high-availability solution to failover clustering in SQL Server Enterprise. Database mirroring supports automatic failover, but does not require cluster-capable hardware, and can therefore provide a cost-effective alternative to failover clustering.
This article is not focused on to make you understand Database Mirroring Concept, rather this focuses on Enhancements, which are being done in SQL Server 2008 for Database Mirroring

Database Mirroring Enhancements in SQL Server 2008 from 2005

• Page-level mirroring:

• If a page on the principle or mirror server is corrupt, it is automatically replaced with corresponding copy on its partner

• Automatic Page Repair on Mirror Servers

• If a page on the principle or mirror server is corrupt, it is automatically replaced with the corresponding copy on its partner
• Some page types cannot be automatically repaired:
• File header pages
• Database boot page
• Allocation pages
• I/O errors on the principle server may be fixed during the mirroring session
• I/O errors on the mirror server require the mirroring session to be suspended

• Compressed Data flow

• Data Flow between the principle and mirror server is now compressed to improve performance

• Manual Failover

• Manual failover no longer require a database restart
• Log Performance
• Write-ahead on the incoming log stream on the mirror server
• Improved use of log-send buffers
• Page read-ahead during the undo phase after a failover

Database mirroring enables you to maintain two copies of a database. One copy is the principal server that client computers access. The other copy acts as a standby server. In the case of a failure of the principal server, the client computers can use the failover capability to connect to the standby computer with no loss of data. Mirroring can therefore increase the availability of a database and provide data protection. SQL Server 2008 offers several enhancements to the database-mirroring environment, including the following:

• Page-level mirroring. This replaces corrupt pages on one server with the same page on the partner server.
• Compressed data flow. This provides improved performance and reduces the network bandwidth that database mirroring uses.
• Manual failover. This no longer requires a restart of the database
• Log performance. This has the following improvements:
  • Write-ahead on the incoming log stream on the mirror server. This writes the incoming log records to disk asynchronously.
  • Improved use of log-send buffers. If the most recently used log cache contains enough free space for the current log records, they are appended to that log cache.
  • Page read-ahead during the undo phase after a failover. The new mirror server sends read-ahead hints to the principal server and the principal server puts those pages in its send buffer. This process improves the speed of the undo phase.

Before SQL Server 2008, data restore could occur at the file level only. A corrupt page may require a failover and then a restore of the file that contains the corrupted data. This procedure is expensive due to the resources and time that are used, and because more data is replaced than was actually corrupted.
SQL Server 2008 provides recovery at the page level. The database-mirroring environment automatically replaces corrupt pages on one server with the same pages from the partner server. The process does not require user intervention and does not interrupt the availability of the server. By using automatic page repair, the principal and mirror computers can recover from data page errors and from errors that prevent reading a data page.


If a mirror server finds a page with errors, it puts the mirroring session into the SUSPENDED state, logs the error, and then requests a copy of the page from the principal server. If the principal server can access the page, it sends a copy to the mirror server, which replaces the page and resumes the mirroring session. Otherwise, the mirroring session remains in a suspended state.
Automatic page repair is only available in SQL Server Enterprise. However, if you have one mirror running on Enterprise and one mirror on Standard, corrupt pages can be repaired on the Enterprise instance, but not on the Standard instance

 

SQL Script to change a mirror endpoint

 SQL Error

Database Mirroring login attempt failed with error: ‘Connection handshake failed. An OS call failed: (80090311) 0×80090311(No authority could be contacted for authentication.). State 67.’.

 

Solution in my specific scenario.

There was some issue with the port and I wanted to change the Mirroring EndPoint. Here is the script which can be use to changing the Mirroring end point.

-- /* ******************************************** */
-- /* Script to change a mirror endpoint            */
-- /* for mirroring issue with handshake failed    */
-- /* ******************************************** */
drop endpoint <endpointname>
go
CREATE ENDPOINT <endpointname>
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 5022 )
    FOR DATABASE_MIRRORING 
    (ENCRYPTION = DISABLED,ROLE=ALL)
GO