Issue while package build (Transaction Log file is Full)

We recently upgraded out toolset from 8.96 D1 to 8.98.2.0 and also upgraded from MSDE to SSE on the Deployment Server.

Recently we have started receving errors while doing package build as "Transaction Log file is Full for the planner data files". We see the size close to 2 GB.

Also we are unable to register the sql server SSE db using our old sql server.

Any pointers?

Thanks,
CNC Guy
E1 8.11 SP1
Solaris

There is a SQL SSE DB Manager client that can be installed on the deployment server to alow administration of the database

Can you post your build log file?

[ QUOTE ]
We recently upgraded out toolset from 8.96 D1 to 8.98.2.0 and also upgraded from MSDE to SSE on the Deployment Server.

Recently we have started receving errors while doing package build as "Transaction Log file is Full for the planner data files". We see the size close to 2 GB.

Also we are unable to register the sql server SSE db using our old sql server.

Any pointers?

Thanks,
CNC Guy
E1 8.11 SP1
Solaris

[/ QUOTE ]



Hmmm, using SQL Server Management Studio while logged on as a user that has domain admin rights, connect to the local SSE database instance (DEPLOYMENTSERVERNAME/JDESSELOCAL and run this script:

<font class="small">Code:</font><hr /><pre>

SELECT name, compatibility_level, recovery_model_desc, is_auto_shrink_on, is_auto_create_stats_on, is_auto_close_on, is_auto_update_stats_on,
state_desc
from sys.databases

</pre><hr />

Make sure that none of the databases have a recovery model other than SIMPLE, unless you are doing regular transaction log backups of these databases.

Also, run this script:

<font class="small">Code:</font><hr /><pre>


select sys.databases.name as 'Database Name',
cast (sys.master_files.name as char(20)) as 'Logical File Name',
sys.master_files.physical_name as 'Physical File Name',
sys.master_files.size * 8.0 / 1024.0 as 'Size',
'MB' as 'Unit',
case
when sys.master_files.max_size = -1 then sys.master_files.max_size
when sys.master_files.max_size = 268435456 then sys.master_files.max_size
else (select sys.master_files.max_size * 8.0 / 1024.0) end as 'Max Size',
case
when sys.master_files.max_size = -1 then 'Unlimited'
when sys.master_files.max_size = 268435456 then 'Unlimited'
else 'MB' end as 'Unit',
case when sys.master_files.is_percent_growth = 1
then sys.master_files.growth
else (select sys.master_files.growth * 8.0 / 1024.0) end as 'Growth',
case when sys.master_files.is_percent_growth = 1
then 'Percent'
else 'MB' end AS 'Unit'
from sys.databases INNER JOIN sys.master_files on sys.databases.database_id = sys.master_files.database_id
order by 'Database Name'

</pre><hr />

and make sure that the 'Unit' column value for each database says 'Unlimited'. Address the first issue of recovery model first, then the second issue of the possibility of the physical files being growth limited second. Neither should be the case but the transaction log should be set to SIMPLE and, if the package build is causing the log to grow even in SIMPLE recovery model, you need to make sure that 1- the file is allowed to grow and 2- the filesystem has enough space for it to do so.




You can also install SQL Server Management Studio Express (free download) on the Deployment Server to be able to see the databases and run the scripts.

Thanks for your suggestions Jeff. We did download and installed the SQL Server Management Studio Express 2005 on our Deployment Server and then checked the SSELOCAL DB and the recovery model for our JDE DB were FULL. The system DB were SIMPLE though. In fact what I could see is since only the JDE DB were upgraded from MSDE to SSE the System DB were still being backed up i.e. trasactional log backup which might be scheduled by our SQL server DBA's and hence the model for system DB is simple (also some of custom JDE DB like OWBAK_JJ17394_PY811 was also SIMPLE since that prob was not upgraded to SSE).

Also the 2nd thing i.e. Unit for all DB was MB and not Unlimited which meant the DB weren't set to grow unlimited. We checked and the value was 2 GB and hence our problems since the transactional log was close to 2 GB. For the time being we have increased the size for transaction log i.e. ldf files to 4 GB but should we reallty set them i.e. all ldf and mdf files to Unlimited? It there a concern with that since it looks like prior to SSE upgrade we did not have that Unlimited but may be the trasancation log backups were taking place?

Can we set this i.e. transactional log backup ourselves through the SQL Server Management Studio Express or do we need to contact our SQL Server DBA's? Also do we need to schedule the backup for only transactional logs (ldf files) or we need for data files (mdf files) as well?

Thanks again for your help Jeff. Alteast we could proceed further. But suprisingly Oracle did not tell this when we upgraded to SSE from MSDE. They should have. Isn't it?


Also we are attaching our build file for reference as well.

Thanks,
CNY Guy

I am guessing that your dba's had a backup plan in place that involved point-in-time recovery for your Planner databases and the upgrade from MSDE to SSE broke that. If they are using a centralized backup solution (perhaps CommVault or Tivoli or ArcServe) and do full, differential and transaction log backups for *all* SQL databases that may explain why your databases are in FULL recovery mode and transaction log backups *were* being done. I cannot imagine that the upgrade changed the recovery model from SIMPLE to FULL.

I do not personally see the need for point-in-time recovery for Planner databases. I would talk to your dba's to determine why the databases were set to FULL (which strongly suggests that a transaction log backup plan was in place), decide if it is truly necessary and re-implement the log backups as soon as possible if you decide to. If you decide not to do log backups you will have to change the recovery model to SIMPLE.

As far as the unlimited filegrowth question: I set all my databases (all of them) to unlimited growth based on the philosophy that filesystems are generally monitored for available size and limiting the growth there will give one an indication of impending failure. Limiting maximum size at the database level means that you will never know that failure is coming and only become aware that you have run out of space in a manner similar to what happened with your situation. I would rather know ahead of time. Some might say that running the filesystem out of space would cause a more catastrophic failure but I would argue that running out of space at the database level causes just as much trouble. So, given that actually running out of space has the same effect regardless of where the limit is, I choose the method that is more likely to give me some sort of advance notice (via an alert) or is more likely to be noticed (Hey, the G: drive is running low on space).

Feel free to limit filegrowth as your organization sees fit and the truth is that if you are in SIMPLE model or FULL with log backups the files should not grow that much.


[ QUOTE ]
Thanks for your suggestions Jeff. We did download and installed the SQL Server Management Studio Express 2005 on our Deployment Server and then checked the SSELOCAL DB and the recovery model for our JDE DB were FULL. The system DB were SIMPLE though. In fact what I could see is since only the JDE DB were upgraded from MSDE to SSE the System DB were still being backed up i.e. trasactional log backup which might be scheduled by our SQL server DBA's and hence the model for system DB is simple (also some of custom JDE DB like OWBAK_JJ17394_PY811 was also SIMPLE since that prob was not upgraded to SSE).

Also the 2nd thing i.e. Unit for all DB was MB and not Unlimited which meant the DB weren't set to grow unlimited. We checked and the value was 2 GB and hence our problems since the transactional log was close to 2 GB. For the time being we have increased the size for transaction log i.e. ldf files to 4 GB but should we reallty set them i.e. all ldf and mdf files to Unlimited? It there a concern with that since it looks like prior to SSE upgrade we did not have that Unlimited but may be the trasancation log backups were taking place?

Can we set this i.e. transactional log backup ourselves through the SQL Server Management Studio Express or do we need to contact our SQL Server DBA's? Also do we need to schedule the backup for only transactional logs (ldf files) or we need for data files (mdf files) as well?

Thanks again for your help Jeff. Alteast we could proceed further. But suprisingly Oracle did not tell this when we upgraded to SSE from MSDE. They should have. Isn't it?


Also we are attaching our build file for reference as well.

Thanks,
CNY Guy

[/ QUOTE ]

Thanks again Jeff for your explaination. Pardon me for this but I am not a SQL guy so as I understand correctly changing the recovery model from FULL to Simple would mean that the restoration cannot be point in time. Is it? But the DB can still be restored?

Also changing it to FULL means the log file increases is size more as compared to SIMPLE? and hence a transactional log backup strategy needs to be in place if the recovery model is FULL?

Finally as I said does this need to be only for the transactional log files i.e. ldf files or also for the data file i.e. mdf files?

Also I too agree to your point of having unlimited growth but I wasn't too sure if there are internal limits to that. I read somewhere MSDE had a 2 GB upper limit whereas SSE has 4 GB.

Thanks,
CNC Guy

You are certainly welcome.

Here is how recovery model and backups work:

If you are in SIMPLE recovery model the transaction log gets truncated occasionally (when is complicated and not germane to this discussion) and thus does not grow. You do not get point-in-time recovery with the SIMPLE model.

If you are in FULL recovery model the transaction log will get truncated during the log backup if a full backup has been done. FULL recovery model, when combined with transaction log backups will provide point-in-time recovery. FULL recovery model, when *not* combined with log backups will provide an ever growing transaction log file.

I really do not see the need for point-in-time recovery for PLANNER databases. Honestly, I don't really see much need for any recovery for them. They are really nothing more than applied ESU's and easily rebuilt by re-applying the ESU's. Again, your choice though as you weigh the cost/benefit.


Some random related notes:

-Truncation does not reduce the size of a physical log file, it reduces the size of the logical
log file.

-If one does a full backup, a transaction log backup, and a DBCC Shrinkfile on the transaction logs, the physical files will be shrunk.

-Shrinking files (database or transaction log) is not recommended for files that will re-grow

-Full database backups alone do not truncate transaction logs.

-Using full recovery model without also doing transaction log backups or specifying that the transaction log is truncated during a full backup (SQL EM or NetBackup) will result in the transaction logs never being truncated.


About the MDF and LDF files, the MDF (or NDF for secondary) files are data files and the LDF files are transaction log files. MDF (NDF) and LDF are standard extensions but you could actually specify any name for the data and transaction log files. We are not really doing filesystem backups so the full (and/or differential) and transaction log backups do not directly relate to backing up those files.


Regarding file sizes, you are absolutely correct on the max file sizes for MSDE and SSE. I apologize for my misguided answers about those.


[ QUOTE ]
Thanks again Jeff for your explaination. Pardon me for this but I am not a SQL guy so as I understand correctly changing the recovery model from FULL to Simple would mean that the restoration cannot be point in time. Is it? But the DB can still be restored?

Also changing it to FULL means the log file increases is size more as compared to SIMPLE? and hence a transactional log backup strategy needs to be in place if the recovery model is FULL?

Finally as I said does this need to be only for the transactional log files i.e. ldf files or also for the data file i.e. mdf files?

Also I too agree to your point of having unlimited growth but I wasn't too sure if there are internal limits to that. I read somewhere MSDE had a 2 GB upper limit whereas SSE has 4 GB.

Thanks,
CNC Guy

[/ QUOTE ]

Thanks Jeff for the insight into the SIMPLE and FULL. Basically I agree the recovery model can be SIMPLE for planner databases but our DBA's are third party and they decide for themselves.

Basically our question now is as you said we are having FULL recovery model without backups and our files (ldf and mdf) are increasing.. the Transactional log files are around 2.8 GB now. What should we do now? Have the model changed to SIMPLE from FULL? But that will not reduce the size? or do a transactiona log backup?

Our SQL server DBA says that SSE is a limited software and it is cumbersome to scheule backups manually and there is a size constraint of 4 GB anyway. He is asking whether we can install the SQL Server 2005 Standard edition i.e. upgrade the SSE to SQL Server 2005 Standard?

Is that possible?

Thanks,
CNC Guy

[ QUOTE ]
Thanks Jeff for the insight into the SIMPLE and FULL. Basically I agree the recovery model can be SIMPLE for planner databases but our DBA's are third party and they decide for themselves.

Basically our question now is as you said we are having FULL recovery model without backups and our files (ldf and mdf) are increasing.. the Transactional log files are around 2.8 GB now. What should we do now? Have the model changed to SIMPLE from FULL? But that will not reduce the size? or do a transactiona log backup?

Our SQL server DBA says that SSE is a limited software and it is cumbersome to scheule backups manually and there is a size constraint of 4 GB anyway. He is asking whether we can install the SQL Server 2005 Standard edition i.e. upgrade the SSE to SQL Server 2005 Standard?

Is that possible?

Thanks,
CNC Guy

[/ QUOTE ]

At this point your DBA's have gone beyond being unreasonable to what could be called incompetent. If you are in the full recovery model you MUST truncate the transaction log either manually or as a side-effect of a transaction log backup. The latter is usually the choice of competent DBA's since you get the added bonus of point-in-time recovery.

Let me state that again: ff you are in the full recovery model you MUST truncate the transaction log.

I find it difficult to believe that a knowledgeable DBA would suggest you spend untold thousands of licensing dollars on implementing SQL Server Standard instead of simply putting the stinking databases in the SIMPLE recovery model. As to SSE being "limited software " - of course it is. That is exactly what it is designed to be and exactly why Denver is using it - because it is limited software. However, it fits perfectly within the limits of the intended use...and it is free, as in beer.

Here, tell your DBA to run this while connected (if he can manage to figure out how to connect, since we had to tell him how to do that here earlier) to the planner instance:

<font class="small">Code:</font><hr /><pre>
EXEC sp_MSforeachdb @command1='if ''?'' not in
(''master'',''model'',''tempdb'',''msdb'')
ALTER DATABASE ? SET RECOVERY SIMPLE'
</pre><hr />

Note: there are no double-quotes above, just single-quotes, sometimes paired
Note: may get error on tepmdb. Other databases process though


Unless you really need the disk space there is no need to shrink the transaction log physical files after switching back to SIMPLE recovery model. All shrinking and growth of files do is cause filesystem fragmentation. If you really want to do so, send me an email and I'll give you the script.



Does you organization want someone who actually knows what they are doing to run this thing for you?