Shrink SQL Log file

elit2007
elit2007 used Ask the Experts™
on
How do you shrink a log file in SQL Management Studio Express?
Got a database with a huge log file (3 gb) after running for a long time without backup.
Normally the backup software will shrink the log file after backup, but this doesn't happens when I now run backup. Also tried to use Backup Exec and select "backup log file" ,but this doesn't either shrink the file. The backupfile is only 2MB, but when I try to restore, the log file gets the same size (3 gb).
So clearly there isn't 3 gb of data in the logfile. It has just reserved the amount of disk space.
I think I remember something about setting the "Recovery model" to "Simple" and than run backup?
The main thing is that I don't' loose any data if there still is something that hasn't been moved to the main database file.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
the backup does not shrink the file. however, if you run transaction log backups, however, you can then request the log file to shrink using DBCC SHRINKFILE.note: the transaction log backup only works with database in full recovery mode.

Commented:
This wil do your backup for you and also shrink down and truncate the empty space on the log and database file
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Commented:
Seems like the attachment didnt go thru
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- date for file name to be used
DECLARE @ShrinkDBFiles VARCHAR(8000) -- Variable to store compression scripts
DECLARE	@DataFile VARCHAR(200) -- Variable to store the main database file name
DECLARE	@LogFile VARCHAR(200) -- Variable to store the log file name
DECLARE @DataFileTable TABLE ([FileName] VARCHAR(1000)) -- Variable to store data filename scripts
DECLARE @LogFileTable TABLE ([FileName] VARCHAR(100)) -- Variable to store log filename scripts
DECLARE @DBCount INT -- Variable to store the total number of databases to be backed up
DECLARE @Completed INT -- Variable to store the amount of databases completed backup

SET @Completed = 0

-- Set the backup path to be used
SET @path = 'Path to backup to'
 
-- Set the date to be used in the file name (current system date)
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

-- Select all databases to be backed Up
DECLARE db_cursor CURSOR FOR  
SELECT [name] 
FROM [master].dbo.sysdatabases 
WHERE name NOT IN ('db to exclude')
ORDER BY [name]

-- Check the number of databases to be backed up
SELECT @DBCount = COUNT(*) 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('db to exclude')
		

-- Start the cursor to scroll thru the databases to backup and select the first database
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name

-- Display a message to the user informing the amount of databases to be backed up
PRINT 'Preparing to backup ' + CAST(@DBCount AS VARCHAR(50)) + ' databases.'

-- Start the backup process
WHILE @@FETCH_STATUS = 0   
	BEGIN
		-- Create Script for selecting the main database file name
		SET @DataFile = 
		'SELECT [name] FROM [sys].[database_files] WHERE [type_desc] = ''''ROWS'''''

		-- Create Script for selecting the log file name
		SET @LogFile = 
		'SELECT [name] FROM [sys].[database_files] WHERE [type_desc] = ''''LOG'''''

		-- Select the output file name to be used
		SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
		
		-- Add a use statement to the script to select the file name with the current database
		SET @DataFile = 'USE ' + @name + '; 
		EXEC sp_executesql N''' + @DataFile + '''';

		SET @LogFile = 'USE ' + @name + '; 
		EXEC sp_executesql N''' + @LogFile + '''';

		-- Select the main database file to be compressed by executing the previous step script
		INSERT INTO @DataFileTable EXEC (@DataFile)
		SELECT @DataFile = [FileName] FROM @DataFileTable
		
		-- Select the log file to be compressed by executing the previous step script
		INSERT INTO @LogFileTable EXEC (@LogFile)
		SELECT @LogFile = [FileName] FROM @LogFileTable
		
		BACKUP LOG @name WITH NO_LOG
		
		-- Set the scripts to be executed for compression
		SET @ShrinkDBFiles =
		'BACKUP LOG ' + @name + ' WITH NO_LOG
		 DBCC SHRINKFILE (' + @LogFile + ' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
		 DBCC SHRINKFILE (' + @DataFile + ' , 0, TRUNCATEONLY) WITH NO_INFOMSGS
		 DBCC SHRINKFILE (' + @LogFile + ' , 0, TRUNCATEONLY) WITH NO_INFOMSGS'
		
		-- ensure the scripts are executed within the current database
		SET @ShrinkDBFiles = 'USE ' + @name + '; 
		EXEC sp_executesql N''' + @ShrinkDBFiles + '''';
		
		-- Compress the database and log files
		EXEC (@ShrinkDBFiles)
		
		-- Delete the temporarily saved database file name
		DELETE FROM @DataFileTable
		
		-- Delete the temporarily saved log file name
		DELETE FROM @LogFileTable
		
		-- Shrink the current database size
		DBCC SHRINKDATABASE (@name)	WITH NO_INFOMSGS
		
		-- Backup the database tp the specified folder with the file standard name
		BACKUP DATABASE @name TO DISK = @fileName
		WITH FORMAT
		
		-- Increment the amount off database backups completed
		SET @Completed = @Completed + 1
		
		-- Select the next database to be backed up
		FETCH NEXT FROM db_cursor INTO @name
	END   

-- Release the resources used for the cursor
CLOSE db_cursor
DEALLOCATE db_cursor

-- Display the amount of databases backed up to the user
PRINT '' + CAST(@Completed AS VARCHAR(10)) + ' of ' + CAST(@DBCount AS VARCHAR(10)) + ' databases backed up successfully.'

Open in new window

Author

Commented:
Wow! your script 33946752 is almost as big as my log file :0)
Previously I have used simple command to shrink, but this doesn’t work in SQL 2008.
“BACKUP LOG "name" WITH TRUNCATE_ONLY DBCC SHRINKFILE ('"name"_Log',20)”
It’s something like this I’m looking for.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
the WITH TRUNCATE_ONLY has been removed from SQL 2008....
Commented:
In 2008 if your database is in simple recovery mode it should clear the transaction log even if you remove the WITH TRUNCATE_ONLY option.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial