SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shirnk Tlog(LDF) size?


Shirnk Tlog(LDF) size?

Author
Message
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6959 Visits: 3501
Hi,

Database High availability - Mirror setup configured, Principal server Tlog (.LDF) file size 25 GB, also warning messages recording at error log file like VLF file more than 1000.

TLog backup configured at every 15 min.

I want reduce Tlog size, How to do that with out using DBCC SHIRNKFILE (db_LOG, TRUNCATE_ONLY)

Because if using TRUNCATE_ONLY command LSN chain will be broken and not able to restore point in time recovery.

Thanks
ananda
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99595 Visits: 33014
The only way to shrink the size of the file is to shrink the size of the file.

If your file is growing too large, you have a couple of choices. First, take more frequently log backups. If you're mirroring, increase the frequency of the log transmission. Second, reduce the size or number of transactions by adjusting your code. That one is frequently extremely difficult, but it is sometimes possible.

That's it. We really have very few options in managing log file size.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6959 Visits: 3501
Yes.. this is OLTP database, it is very huge tansaction across 8 manufactring sites.

configured sperate Tlog backups ran at every 15 min, each Tlog backup file size is maximum 8 MB, but it would be increased upto 4 to 5 GB during Index maintanence, update stats, Integrity jobs etc.. on weekly basis.

Pls. confirm, if execute DBCC SHIRNKFILE (db_log, TRUNCATE_ONLY) what will be impact on mirror database? does it keep on restore log files at mirror database?

I am planing for execute that command. For reduce the log file size.

Thanks
ananda
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228507 Visits: 46342
truncate only is not a valid option when shrinking a log. If you don't specify a size, it will shrink as far as possible, probably requiring you to grow it again.
Don't run that command.
If you choose to shrink, shrink to a sensible size which is sufficient for your day to day operation.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99595 Visits: 33014
Absolutely what Gail says. But, the real issue is going to be, what happens next time you run index maintenance, etc. Isn't the log just going to grow again? Then do you shrink it again? Then won't it grow again?

See where this is going?

Unless you've experienced a one time issue with the log, backups were failing or something like that, I wouldn't suggest you shrink it. It's probably the size it needs to be. If it's too big for a given drive, get a bigger drive.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQL Galaxy
SQL Galaxy
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6959 Visits: 3501
Ok, Many thanks form experties suggestions.

server side 800 GB freespace available at log file partition, so I am not executed that command let it be gorw that file.

Pls. confirm,
Does it any performance issues happend if it is huge VLF created in LDF file?

Thanks
ananda
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99595 Visits: 33014
ananda.murugesan (7/22/2014)

Ok, Many thanks form experties suggestions.

server side 800 GB freespace available at log file partition, so I am not executed that command let it be gorw that file.

Pls. confirm,
Does it any performance issues happend if it is huge VLF created in LDF file?

Thanks
ananda


Yes. You've either been shrinking and growing it over and over, and/or, your growth increment is really tiny (probably the default 1mb) and you've grown it thousands of times. That scatters things across the disk and can cause performance issues primarily in startup and backup and recovery.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Tac11
Tac11
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 Visits: 1159
Source: Glenn Berry

-- Switch to your database
USE ngmetadata;
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO DISK = N'N:\SQLBackupsgmetadataLogBackup.bak' WITH NOFORMAT, INIT,
NAME = N'ngmetadata- Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO

-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Step 3: Grow the log file back to the desired size,
-- which depends on the amount of write activity
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO

-- Switch back to your database
USE ngmetadata;
GO

-- Check VLF Count for current database after growing log file
DBCC LogInfo;
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55087 Visits: 17707
smtzac (7/23/2014)
Source: Glenn Berry

-- Switch to your database
USE ngmetadata;
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Check individual File Sizes and space available for current database
SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB], [file_id]
FROM sys.database_files;

-- Step 1: Compressed backup of the transaction log (backup compression requires Enterprise Edition in SQL Server 2008)
BACKUP LOG [ngmetadata] TO DISK = N'N:\SQLBackupsgmetadataLogBackup.bak' WITH NOFORMAT, INIT,
NAME = N'ngmetadata- Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 1;
GO

-- Step 2: Shrink the log file
DBCC SHRINKFILE (N'ngmetadata_log' , 0, TRUNCATEONLY);
GO

-- Check VLF Count for current database
DBCC LogInfo;

-- Step 3: Grow the log file back to the desired size,
-- which depends on the amount of write activity
-- and how often you do log backups
USE [master];
GO
ALTER DATABASE ngmetadata MODIFY FILE (NAME = N'ngmetadata_log', SIZE = 8GB);
GO

-- Switch back to your database
USE ngmetadata;
GO

-- Check VLF Count for current database after growing log file
DBCC LogInfo;

As already pointed out by Gail, TRUNCATEONLY is an invalid option for DBCC SHRINKFILE when shrinking logs.
The script from Glenn shows you how to resize your log if you have an excessive number of VLFs, it does not advocate you shrink the log regularly or long term ;-)

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Tac11
Tac11
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1541 Visits: 1159
You're right Perry Whittle!!!! That is just for more VLFs.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search