Shrinking Log file

Shrinking a file depends on WHERE the free space is actual located. A log file tends to look like this: (code A = active I = inactive)
A I I A A A I I AA
in other words, a mixture of active and inactive portions. Shrinking the file only shrinks (removes) the Inactive portions from the END of the log file (in the example above, the end of the file is an active portion).
Backing up the log causes the Inactive portions to be moved to the end of the file:A A A A A A I I I I
Problem is that it doesn't happen immediately. How long does it take? Depends on your setup. Give it a hour or two then try to shrink the file. Or just set AUTOSHRINK on and the database will figure out when it can be shrunk.

-------------------------------------------------------------------------

DBCC LOGINFO('YourDatabaseName')--
1 -- The minimum recovery LSN (log sequence number) in the log has status = 2; this prevents the log file from reducing in size. The following steps will prompt a shrink process to proceed somewhat more promptly.

DBCC SHRINKFILE ('DBLogFile', TRUNCATEONLY)

BACKUP LOG 'DBName' WITH TRUNCATE_ONLY

2 -- Create a dummy table execute dummy inserts to move the active VLF

CREATE TABLE DummyTable
(DummyColumn VARCHAR(10),
PK INT )
INSERT DummyTable (PK) VALUES (1)
GO--

3 -- Insert into DummyTable to create transactions:
SET NOCOUNT ON
DECLARE @vi INT
SELECT @vi = 0
WHILE (@vi <>
BEGIN
UPDATE DummyTable
SET DummyColumn = DummyColumn
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @vi = @vi + 1
END
SET NOCOUNT OFF

-- 4-- Once the log has been truncated, portions near the front of the log may be cycled and reused.
The dummy transactions, allow SQL Server to reuse the "dead" space at the beginning of the log, instead of "growing" the log file.
The Min LSN with a staus = 2 will then shift to the unused portions of the log file. The VLFs should then be marked as unused and be deleted following a DBCC SHRINKFILE & BACKUP LOG.
VLF removal is what actually decreases the physical log file size.

Re-execute shrinkfile / truncate:
DBCC SHRINKFILE ('DBLogFile', TRUNCATEONLY)
BACKUP LOG 'DBName' WITH TRUNCATE_ONLY
--

5-- Check the size of the log file to determine if the file has shrunk.-- Re-run the script as may be necessary in order to reduce the size of the logfile.

SQL Mail

SQL Mail
Originally uploaded by SIVA Blog.

Lock time out

Lock time out
Originally uploaded by SIVA Blog.

SQL Server Setup Error

Setup Error
Originally uploaded by SIVA Blog.