MS SQL DATABASE/LOGS Shrinking ( Tested with MsSQL - 2005, 2008 )

Reference By Mr.Varun Dhavan (Database Expert Microsoft)

Q:- What does database shrinking means ?
Ans:- In a SQL Server database, each file within a database can be reduced to remove unused pages.
Although the Database Engine will reuse space effectively, there are times when a file no longer needs to be as
large as it once was; shrinking the file may then become necessary. Both data and transaction log files can be reduced, or shrunk.
The database files can be shrunk manually, either as a group or individually, or the database can be set to shrink automatically at specified intervals.


Q:-What are best practices and implications of shrinking ?
Ans:- Consider the following information when you plan to shrink a database or file:

1) A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
2) Most databases require some free space for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
3) A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. For example, you should not shrink a database or data file after rebuilding indexes. This is another reason not to repeatedly shrink the database.
4) Unless you have a specific requirement, do not set the AUTO_SHRINK database option to ON.


Q:- How does shrinking of a log file happen ?
Ans:- A log file is shrunk when you issue the following command to the SQL Server:
DBCC SHRINKFILE ('logical file name', targetsize)


Q:- How do I know if a log file can be shrunk ?
Ans:- To understand whether the log file can be shrunk, you will need to fire the following commands and understand their outputs.

The first command that needs to be fired is:
DBCC SQLPERF(logspace)
This will let us know what percentage of the log file is actually in use. The lower the percentage, the more the file can be shrunk.


SHRINKING DATABASE T-LOG FILES ROOT-CAUSE

Step we followed to shrink the T-log file of the database ?

Step 1. Back up the transaction log file to make most of the active virtual log files inactive.
Therefore, the inactive virtual log files can be removed in a later step.
To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.

BACKUP LOG TO DISK = ''

Step 2. Shrink the transaction log file. To do this, run a Transact-SQL statement that is similar to the following Transact-SQL statement.
DBCC SHRINKFILE (, ) WITH NO_INFOMSGS


Root cause: Why the Transaction-log files grown so huge ?

1. Databases while running in FULL recovery model and When the transaction logs grow to an unacceptable limit,
you must immediately back up your transaction log file. While the backup of your transaction log files is created,
SQL Server automatically truncates the inactive part of the transaction log.
The inactive part of the transaction log file contains the completed transactions, and therefore, the transaction log file is no
longer used by SQL Server during the recovery process. SQL Server reuses this truncated,
inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space.

2. If Replication on any databases is in active mode and SQL Server Agent is down then due to this, huge pile of transaction that
were pending to replicated, however could not be replicated as the Replication Agent jobs were not running