Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Shirnk Tlog(LDF) size? Expand / Collapse
Author
Message
Posted Tuesday, July 22, 2014 4:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:41 PM
Points: 1,076, Visits: 3,050
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
Post #1595006
Posted Tuesday, July 22, 2014 5:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1595035
Posted Tuesday, July 22, 2014 6:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:41 PM
Points: 1,076, Visits: 3,050

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
Post #1595049
Posted Tuesday, July 22, 2014 6:31 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 40,385, Visits: 36,829
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 2008, MVP
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

Post #1595056
Posted Tuesday, July 22, 2014 6:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1595058
Posted Tuesday, July 22, 2014 6:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:41 PM
Points: 1,076, Visits: 3,050

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
Post #1595064
Posted Tuesday, July 22, 2014 8:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:31 PM
Points: 14,000, Visits: 28,381
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
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1595105
Posted Wednesday, July 23, 2014 11:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:06 AM
Points: 124, Visits: 451
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;
Post #1595628
Posted Thursday, July 24, 2014 4:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:46 AM
Points: 6,619, Visits: 14,185
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"
Post #1595843
Posted Thursday, July 24, 2014 6:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 9:06 AM
Points: 124, Visits: 451
You're right Perry Whittle!!!! That is just for more VLFs.
Post #1595869
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse