Best way to shrink a log file?
-
I took a vacation and when I returned, a rookie DBA created a database initialized with a massive log file. He almost filled up the server. I'd like to know the best way to shrink this monster. I like to avoid doing a backup and restore.
Thanks.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
dbcc shrinkfile ('logical_log_file_name', size-in-MB )
* Noel -
OK, this will be interesting, dbcc shrinkfile is definitely the right answer but how far you will be able to shrink the log file will vary according to the number of virtual log files (http://msdn2.microsoft.com/en-us/library/ms179355.aspx) SQL server created when the log was created, apparently the size/number of the virtual log files is not fixed...
Be interested to see the initial size of your log file and what you can get it to shrink to?
Joe
-
If the database is in full recovery mode and you haven't backed up the transaction log, shrinking won't be able to do anything because SQL Server will not discard any piece of the transaction log.
If there are unused virtual log sections at the end of the transaction log (because it was defined too large), that extra space can be released to the operating system.
This script will truncate the transaction log, shrink the database (including the transaction log) and release any extra space to the operating system. You can switch to dbcc shrinkfile to only do a single file (i.e. the transaction log) as well as specify a target size if you want to reserve space. Refer to SQL Books Online to syntax and options.
IMPORTANT: If you truncate the transaction log on a production database, you should immediately backup the full database. Otherwise, you will loose the ability to recover from a disaster.
declare @SQLString nvarchar(512)
declare @DatabaseName nvarchar(128)
set @DatabaseName = 'PutDatabaseNameHere'
--------------------------------------
--Step 1: Truncate the transaction log
--------------------------------------
--notify user
print N''
print N'Truncating the transaction log...'
--construct SQL string
set @SQLString = N'backup log [' + @DatabaseName + N'] with no_log'
--execute the SQL string
exec master.dbo.sp_executesql @SQLString
--------------------------------------------------
--Step 2: Shrink the database (data and log files)
--------------------------------------------------
--notify user
print N''
print N'Shrinking the database...'
--construct SQL string
set @SQLString = N'dbcc shrinkdatabase([' + @DatabaseName + N'], notruncate)'
--execute the SQL string
exec master.dbo.sp_executesql @SQLString
---------------------------------------------------------
--Step 3: Release any extra space to the operating system
---------------------------------------------------------
--notify user
print N''
print N'Releasing any extra space to the operating system...'
--construct SQL string
set @SQLString = N'dbcc shrinkdatabase([' + @DatabaseName + N'], truncateonly)'
--execute the SQL string
exec master.dbo.sp_executesql @SQLString
-
Joe is right about the truncation being useless if you do not back up the transaction log and are running in full or bulk recovery mode. Before running shrink file, back up your transaction log.
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Great Advice! I plan to work on this next Monday. I will post the results.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
Thanks too I plan to do this next week too let see.
-
first truncate the log file and then perform the shrinking as it will free the space to operating system once you truncate the log file,
1.) backup log database name with truncate_only
2.) Dbcc shrinkfile('logfilename',sizeinmb)
[font="Verdana"]- Deepak[/font]
-
Or:
Backup
LOG tempdb WITH NO_LOG
GO
DBCC
SHRINKFILE ('templog',TRUNCATEONLY)
GO
-
You ARE going to look over the rookie DBA's shoulder while he/she cleans up their own mess, right?
-
I ran these commands to Shrink the log file:
backup
log Linkages WITH TRUNCATE_ONLY
DBCC
SHRINKFILE('Linkages_log',50)
The original size of the log was 143,011,584KB.
After the ShrinkFile, the file was 52,224KB.
Now I am running a defrag on the drive.
The server is much happier.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
TRUNCATE_ONLY should *always* be your "LAST RESOURCE". Create an appropriate maintenace schedule to deal with the log space. That is the right way
Cheers,
* Noel -
We do full backups on a daily basis. That is sufficient for our needs. Truncating the log was acceptable.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
-
Thanks to Joe. That worked a treat for me.
-
fizzleme (8/20/2007)
We do full backups on a daily basis. That is sufficient for our needs. Truncating the log was acceptable.Then set the DB into simple recovery mode so that the log will auto-truncate and you won't have to worry about it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply