July 11, 2011 at 1:55 am
I get the error: "The transaction log for database 'xxx' is full" while the index rebuild (maintenance plan) was 1h20min running.
But this is strange, because I did a last log backup 10min before the rebuild starts + log file can grow unrestricted + there's enough diskspace.
The database is in full recovery and isn't that big, I even get this error on smaller databases 🙁
There's a full backup at 0:15 and log backups each hour from 5:00 till 22:00 and then I started the index rebuild (22:10)...
After this error, the update stats, full backup and integrity check plans fails too -> same error.
On a weekly day, there aren't problems with integrity check and backups.
I only start the index rebuild and update stats maintenance plans on saturday night + sunday morning.
How can I get those errors solved?
And why do I get this kind of error, the log is cleared 10min before?
July 11, 2011 at 2:08 am
In full recovery index rebuilds are fully logged and require easily 1.2x the size of the index being rebuilt in log space.
If you can accept the minor risks associated, switch to bulk-logged recovery for the duration. Otherwise grow the log and rebuild only the indexes that need rebuilding.
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
July 11, 2011 at 2:21 am
GilaMonster (7/11/2011)
In full recovery index rebuilds are fully logged and require easily 1.2x the size of the index being rebuilt in log space.If you can accept the minor risks associated, switch to bulk-logged recovery for the duration. Otherwise grow the log and rebuild only the indexes that need rebuilding.
The last option is under investigation, I've implemented a daily job to maintenance the indexes (organize/rebuild depending fragmentation) on another datase and this is working well.
So when my collegua is back from vacation, I want to implement it on all important db's...
But in this case, it's strange: there has to be enough space to grow... there's still 30Gb free on the log disk and file growth is unrestricted with 500MB grow... and still this error?
July 11, 2011 at 2:24 am
It can still happen. The autogrow can timeout (check the log) or it can take too long and the transaction still fail.
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
July 11, 2011 at 2:28 am
GilaMonster (7/11/2011)
It can still happen. The autogrow can timeout (check the log) or it can take too long and the transaction still fail.
Where can I see that the log growth was time out?
I don't see messages in SQL server log about this.
July 11, 2011 at 2:28 am
Whats the size of the DB data files now ?
Check the values for the below underlined text
/****** Object: Database [AdventureWorksDW] Script Date: 07/11/2011 13:56:20 ******/
CREATE DATABASE [AdventureWorksDW] ON PRIMARY
( NAME = N'AdventureWorksDW_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\MSSQL.1\MSSQL\AdventureWorksDW_Data.mdf' , SIZE = 84608KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
LOG ON
( NAME = N'AdventureWorksDW_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data\MSSQL.1\MSSQL\AdventureWorksDW_Log.LDF' , SIZE = 18432KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO
Are you rebuilding all indexes or do you have a check to identify particular indexes and only rebuild them?
Can you run perfom and check the growth of the tran log files during the index rebuild.
July 11, 2011 at 2:34 am
Rhox (7/11/2011)
GilaMonster (7/11/2011)
It can still happen. The autogrow can timeout (check the log) or it can take too long and the transaction still fail.Where can I see that the log growth was time out?
I don't see messages in SQL server log about this.
It would be in the SQL error log. I said it can happen, not that it did happen.
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
July 11, 2011 at 2:50 am
/****** Object: Database [A105_A] Script Date: 07/11/2011 10:48:31 ******/
CREATE DATABASE [A105_A] ON PRIMARY
( NAME = N'VUMNIEUWS_Data', FILENAME = N'D:\Microsoft SQL Server\Data\A105_A.mdf' , SIZE = 9040640KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
FILEGROUP [FG_VUMNIEUWS]
( NAME = N'VUMNIEUWS_Data1', FILENAME = N'D:\Microsoft SQL Server\Data\A105_A_1.mdf' , SIZE = 8879680KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
( NAME = N'VUMNIEUWS_Data2', FILENAME = N'D:\Microsoft SQL Server\Data\A105_A_2.mdf' , SIZE = 6911104KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
( NAME = N'VUMNIEUWS_Data3', FILENAME = N'D:\Microsoft SQL Server\Data\A105_A_3.mdf' , SIZE = 8037504KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
FILEGROUP [FG_VUMNIEUWS_NTM]
( NAME = N'VUMNIEUWS_NTM', FILENAME = N'D:\Microsoft SQL Server\Data\A105_A_4.mdf' , SIZE = 15995008KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB ),
FILEGROUP [ftfg_FS_Debate]
( NAME = N'ftrow_FS_Debate', FILENAME = N'D:\Microsoft SQL Server\Data\ftrow_FS_Debate.ndf' , SIZE = 893120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [ftfg_FS_NewsTableMain]
( NAME = N'ftrow_FS_NewsTableMain', FILENAME = N'D:\Microsoft SQL Server\Data\ftrow_FS_NewsTableMain.ndf' , SIZE = 3243776KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [ftfg_FS_NewsTableMain_MetaData]
( NAME = N'ftrow_FS_NewsTableMain_MetaData', FILENAME = N'D:\Microsoft SQL Server\Data\ftrow_FS_NewsTableMain_MetaData.ndf' , SIZE = 42048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'VUMNIEUWS_Log1', FILENAME = N'D:\Microsoft SQL Server\Log\A105_A_1.ldf' , SIZE = 127880KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB ),
( NAME = N'VUMNIEUWS_Log2', FILENAME = N'D:\Microsoft SQL Server\Log\A105_A_2.ldf' , SIZE = 127944KB , MAXSIZE = UNLIMITED, FILEGROWTH = 512000KB )
GO
/****** Object: Database [A105.001_A] Script Date: 07/11/2011 10:50:19 ******/
CREATE DATABASE [A105.001_A] ON PRIMARY
( NAME = N'Actu24_Data', FILENAME = N'D:\Microsoft SQL Server\Data\A105.001_A.mdf' , SIZE = 1188288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [FG_ACTU24]
( NAME = N'Actu24_Data1', FILENAME = N'D:\Microsoft SQL Server\Data\A105.001_A_1.NDF' , SIZE = 416384KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Actu24_Data2', FILENAME = N'D:\Microsoft SQL Server\Data\A105.001_A_2.NDF' , SIZE = 234944KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Actu24_Data3', FILENAME = N'D:\Microsoft SQL Server\Data\A105.001_A_3.NDF' , SIZE = 312768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [FG_ACTU24_NTM]
( NAME = N'Actu24_NTM', FILENAME = N'D:\Microsoft SQL Server\Data\A105.001_A_4.NDF' , SIZE = 3082560KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [ftfg_FS_Debate]
( NAME = N'ftrow_FS_Debate', FILENAME = N'D:\Microsoft SQL Server\Data\ftrow_FS_Debate{364B6819-4538-4970-93B2-11017771F667}.ndf' , SIZE = 90432KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
FILEGROUP [ftfg_NewTableMain_PROD_Search]
( NAME = N'ftrow_NewTableMain_PROD_Search', FILENAME = N'D:\Microsoft SQL Server\Data\ftrow_NewTableMain_PROD_Search.ndf' , SIZE = 1188288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'Actu24_Log', FILENAME = N'D:\Microsoft SQL Server\Log\A105.001_A_1.ldf' , SIZE = 1536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%),
( NAME = N'Actu24_Log2', FILENAME = N'D:\Microsoft SQL Server\Log\A105.001_A_2.ldf' , SIZE = 1830400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
July 11, 2011 at 2:53 am
Error is on both db's, but especialy the A105_A is important here...
I know the data and log are on the same disk, but there's still enough diskspace on (30Gb)
July 11, 2011 at 3:13 am
As I said, it could simply have been that the autogrow wasn't fast enough. Grow the logs, see if that helps (and make sure you don't have autoshrink or manual shrink anywhere)
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
July 11, 2011 at 3:16 am
GilaMonster (7/11/2011)
As I said, it could simply have been that the autogrow wasn't fast enough. Grow the logs, see if that helps (and make sure you don't have autoshrink or manual shrink anywhere)
Ok I will do, thanks for the advise!
July 11, 2011 at 3:23 am
July 11, 2011 at 3:28 am
btw, why do both databases have 2 log files?
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
July 11, 2011 at 3:52 am
I don't know why they have 2 log files... I'm just here for 1 week (as consultant) and i received this errors in my checks, so I'm trying to solve them... my colleagua here is now in vacation, so I will ask her why there are 2 log files used...
I don't have a VPN connection to monitor the diskspace live.
I'm installing a diskalert-job for this problem 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply