June 22, 2014 at 1:49 pm
While we were trying to purge the data in our SQL Standard database files ,the rows are not getting dropped or deleted from the NDF files and the transaction log became full.
1. we have history.ndf (userdata001) which is huge and this is causing disk space issues . Is there a way to shrink the NDF files , without corruption . our data files - .MDF is relatively small.
2. Also how to first clear the transaction log error which says that the transaction log has become full?
thanks
June 22, 2014 at 2:18 pm
Shrinking the file - DBCC ShrinkFile. Requires that there's actually free space in the file first, if there isn't you'll have to delete data.
Full transaction log - http://www.sqlservercentral.com/articles/Administration/64582/
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
June 22, 2014 at 9:22 pm
while we tru to delete data on the tables, but the sql server transaction logs became full.
What are the steps that a DBA or sql admin needs to do, to resolve the transaction log full issue and run the sql purge on the tables to reduce the .NDF file size.
we think some steps in this order
;
1. increase disk space
2. run full back up on all tables ?
Is this correct in order or should we some qyuick steps?
June 22, 2014 at 10:42 pm
Hi,
If your DB is in full recovery model or if the transaction which your are running is IMPLICIT_TRANSACTIONS ON mode then you should have sufficient space for transaction log file/drive,there is no other go.
regards,
K7
June 23, 2014 at 2:33 am
rquest7 (6/22/2014)
What are the steps that a DBA or sql admin needs to do, to resolve the transaction log full issue
Did you bother to read the article I referenced?
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
June 23, 2014 at 4:01 am
GilaMonster (6/23/2014)
rquest7 (6/22/2014)
What are the steps that a DBA or sql admin needs to do, to resolve the transaction log full issueDid you bother to read the article I referenced?
Probably not 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2014 at 4:18 am
rquest7 (6/22/2014)
While we were trying to purge the data in our SQL Standard database files ,the rows are not getting dropped or deleted from the NDF files and the transaction log became full.1. we have history.ndf (userdata001) which is huge and this is causing disk space issues . Is there a way to shrink the NDF files , without corruption . our data files - .MDF is relatively small.
2. Also how to first clear the transaction log error which says that the transaction log has become full?
thanks
Gail's given you pretty much all you need. On top of this, first find out how free space you have in the files using this query
USE [yourdb]
GO
SET NOCOUNT ON
SELECTdf.name AS LogicalFileName
, ISNULL(fg.name, 'Log') AS FilegroupName
, physical_name AS PhysicalOSName
, (df.size / 128) AS SizeMBs
, (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS SpaceUsedMBs
, CASE df.growth
WHEN 0 THEN 'No growth'
ELSE 'Growth allowed'
END AS GrowthAllowed
, CASE
WHEN df.max_size / 128 = 0 THEN CAST(df.size / 128 as varchar(50)) + ' MBs'--'Unlimited'
WHEN df.max_size / 128 = 2097152 and df.growth = 0 THEN 'No growth'
WHEN df.max_size = 268435456 THEN '2TB'
ELSE CAST(df.max_size / 128 AS VARCHAR(10)) + ' MBs'
END AS MaxGrowthSize
, CASE df.is_percent_growth
WHEN 0 THEN CAST(df.growth / 128 AS VARCHAR(10)) + ' MBs'
ELSE CAST(df.growth AS VARCHAR(10)) + ' %'
END AS Growth
, (df.size / 128) - (FILEPROPERTY(df.name, 'SpaceUsed') / 128) AS FreeMBs
FROM sys.database_files df LEFT OUTER JOIN sys.filegroups fg
ON df.data_space_id = fg.data_space_id
ORDER BY df.type
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 23, 2014 at 6:02 am
Why is my transaction log full?[/url]
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2014 at 12:41 pm
rquest7 (6/22/2014)
While we were trying to purge the data in our SQL Standard database files ,the rows are not getting dropped or deleted from the NDF files and the transaction log became full.1. we have history.ndf (userdata001) which is huge and this is causing disk space issues . Is there a way to shrink the NDF files , without corruption . our data files - .MDF is relatively small.
2. Also how to first clear the transaction log error which says that the transaction log has become full?
thanks
Quite right, you must first free up space in the trans log, or your db will stay stalled/dead.
One clean method is to temporarily add another log file on a drive with space available. Then that db can log again so it can "unstall".
One quick-and-dirty method is to put the db in simple recovery mode, take a checkpoint and then shrink the log file.
DBCC SHRINKFILE ( 2 )
Which you choose depends on the criticality of the data, your risk tolerance and your backup statuses. For example, if you have a (fairly) recent full backup of your db, you could take a differential backup now, a tail-log backup (if you prefer), then use the q-and-d method above. Worse case, you can get back to your current point by using the full and diff backups (and tail-log backup).
Once that is done, you remove data from the .NDF in batches, for example, 25,000 rows at a time rather than all-at-once. If at all possible, delete using a clus key range/setting. Once you get the log going, let me know and we can work on a delete process if needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply