Sage 100

The title says it all - is it ok to delete the log files for SQL server as they are related to Sage 100? 

And, if not, what purpose do they serve? 
Is there a way to make the logs smaller? If so, how? 

TIA

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------

They are part of the backup and recovery strategy. 

• If you run a "full" backup within SQL, the log file space will be freed and can be recycled (right-click and shrink files, log file, and set a reasonable size if they grew too large). The idea is that you can combine a full backup with the LDF to catch up to the latest transaction, if the MDF is lost.  The architecture was mainly designed for physical drive failures (two drives... one with backup & LDF, one with the MDF), before VM's and RAID arrays.  You can also do a full backup, then smaller (more frequent) log backups, again to protect the data in case of disaster.  It's mainly for advanced environments, with mission critical minute to minute data... which is not Sage 100 (usually).
• You can also change the backup and recovery model to "Simple" (from "Full"), to basically disable the log files... and shrink to a reasonable working size (500MB is usually what I set).  This is what we normally do now, unless the client has sophisticated IT who want to do it their way (i.e. log backups during the day to minimize data loss in the case of a disaster).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Note: never shrink the main database files.  Only shrink database log files (which will auto-grow if more space is needed).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 01-09-2023 13:31
From: Kevin Moyes
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

They are part of the backup and recovery strategy.

• If you run a "full" backup within SQL, the log file space will be freed and can be recycled (right-click and shrink files, log file, and set a reasonable size if they grew too large). The idea is that you can combine a full backup with the LDF to catch up to the latest transaction, if the MDF is lost.  The architecture was mainly designed for physical drive failures (two drives... one with backup & LDF, one with the MDF), before VM's and RAID arrays.  You can also do a full backup, then smaller (more frequent) log backups, again to protect the data in case of disaster.  It's mainly for advanced environments, with mission critical minute to minute data... which is not Sage 100 (usually).
• You can also change the backup and recovery model to "Simple" (from "Full"), to basically disable the log files... and shrink to a reasonable working size (500MB is usually what I set).  This is what we normally do now, unless the client has sophisticated IT who want to do it their way (i.e. log backups during the day to minimize data loss in the case of a disaster).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Thanks, Kevin. This is excellent info! 

Do you manage the SQL backups for the customers you have? 
I never have been asked to manage any backups however for one particular customer their IT seems unwilling to take on the backup task in SQL.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------

Original Message:
Sent: 01-09-2023 13:31
From: Kevin Moyes
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

They are part of the backup and recovery strategy.

• If you run a "full" backup within SQL, the log file space will be freed and can be recycled (right-click and shrink files, log file, and set a reasonable size if they grew too large). The idea is that you can combine a full backup with the LDF to catch up to the latest transaction, if the MDF is lost.  The architecture was mainly designed for physical drive failures (two drives... one with backup & LDF, one with the MDF), before VM's and RAID arrays.  You can also do a full backup, then smaller (more frequent) log backups, again to protect the data in case of disaster.  It's mainly for advanced environments, with mission critical minute to minute data... which is not Sage 100 (usually).
• You can also change the backup and recovery model to "Simple" (from "Full"), to basically disable the log files... and shrink to a reasonable working size (500MB is usually what I set).  This is what we normally do now, unless the client has sophisticated IT who want to do it their way (i.e. log backups during the day to minimize data loss in the case of a disaster).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

One thing to add here  if you do go the Full backup route, make sure three is a maintenance plan in place to periodically shrink the log files, otherwise they will keep growing until the disk gets full and processing comes to a screeching halt.

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
------------------------------

Original Message:
Sent: 01-09-2023 13:41
From: Wayne Schulz
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

Thanks, Kevin. This is excellent info!

Do you manage the SQL backups for the customers you have?
I never have been asked to manage any backups however for one particular customer their IT seems unwilling to take on the backup task in SQL.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------


Original Message:
Sent: 01-09-2023 13:31
From: Kevin Moyes
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

They are part of the backup and recovery strategy.

• If you run a "full" backup within SQL, the log file space will be freed and can be recycled (right-click and shrink files, log file, and set a reasonable size if they grew too large). The idea is that you can combine a full backup with the LDF to catch up to the latest transaction, if the MDF is lost.  The architecture was mainly designed for physical drive failures (two drives... one with backup & LDF, one with the MDF), before VM's and RAID arrays.  You can also do a full backup, then smaller (more frequent) log backups, again to protect the data in case of disaster.  It's mainly for advanced environments, with mission critical minute to minute data... which is not Sage 100 (usually).
• You can also change the backup and recovery model to "Simple" (from "Full"), to basically disable the log files... and shrink to a reasonable working size (500MB is usually what I set).  This is what we normally do now, unless the client has sophisticated IT who want to do it their way (i.e. log backups during the day to minimize data loss in the case of a disaster).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

If you do regularly scheduled Full backups, the log files should not grow unbounded.  If the recovery model is Full, but you don't do backups (from within SQL), that is where the log file sizes become a problem.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 01-09-2023 14:17
From: Phil McIntosh
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

One thing to add here  if you do go the Full backup route, make sure three is a maintenance plan in place to periodically shrink the log files, otherwise they will keep growing until the disk gets full and processing comes to a screeching halt.

------------------------------
Phil McIntosh
President
Friendly Systems, Inc.
------------------------------


Original Message:
Sent: 01-09-2023 13:41
From: Wayne Schulz
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

Thanks, Kevin. This is excellent info!

Do you manage the SQL backups for the customers you have?
I never have been asked to manage any backups however for one particular customer their IT seems unwilling to take on the backup task in SQL.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT


Original Message:
Sent: 01-09-2023 13:31
From: Kevin Moyes
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

They are part of the backup and recovery strategy.

• If you run a "full" backup within SQL, the log file space will be freed and can be recycled (right-click and shrink files, log file, and set a reasonable size if they grew too large). The idea is that you can combine a full backup with the LDF to catch up to the latest transaction, if the MDF is lost.  The architecture was mainly designed for physical drive failures (two drives... one with backup & LDF, one with the MDF), before VM's and RAID arrays.  You can also do a full backup, then smaller (more frequent) log backups, again to protect the data in case of disaster.  It's mainly for advanced environments, with mission critical minute to minute data... which is not Sage 100 (usually).
• You can also change the backup and recovery model to "Simple" (from "Full"), to basically disable the log files... and shrink to a reasonable working size (500MB is usually what I set).  This is what we normally do now, unless the client has sophisticated IT who want to do it their way (i.e. log backups during the day to minimize data loss in the case of a disaster).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

No, we don't manage backups at all and leave that to customer IT.  After far too many questions about SQL log files, I'm in the habit of just switching to Simple, and mentioning it in upgrade notes, saying their IT can change it if they prefer.  Most seem to do server snapshots as backups, which works fine with Simple recovery model.  (The change to Simple can be done on the fly, with no need to take the server offline... users should be unaffected).

Back in the early 2000's I worked in a data center (auto sector) where we set up SQL backups on the mission critical production servers, which we validated weekly by using the backups to refresh extra utility servers (where changes were tested before being approved for production).  Two hard-drives on the production servers... text book setup at the time... so I know very well how it worked back then, and the general architecture of MS SQL databases haven't changed.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 01-09-2023 13:41
From: Wayne Schulz
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

Thanks, Kevin. This is excellent info!

Do you manage the SQL backups for the customers you have?
I never have been asked to manage any backups however for one particular customer their IT seems unwilling to take on the backup task in SQL.

------------------------------
Wayne Schulz
wayne@s-consult.com
Schulz Consulting
(860) 516-8990
Moodus, CT
------------------------------


Original Message:
Sent: 01-09-2023 13:31
From: Kevin Moyes
Subject: Can SQL Server Logs Be Deleted Or Do They Serve A Purpose with Sage 100 Premium?

They are part of the backup and recovery strategy.

• If you run a "full" backup within SQL, the log file space will be freed and can be recycled (right-click and shrink files, log file, and set a reasonable size if they grew too large). The idea is that you can combine a full backup with the LDF to catch up to the latest transaction, if the MDF is lost.  The architecture was mainly designed for physical drive failures (two drives... one with backup & LDF, one with the MDF), before VM's and RAID arrays.  You can also do a full backup, then smaller (more frequent) log backups, again to protect the data in case of disaster.  It's mainly for advanced environments, with mission critical minute to minute data... which is not Sage 100 (usually).
• You can also change the backup and recovery model to "Simple" (from "Full"), to basically disable the log files... and shrink to a reasonable working size (500MB is usually what I set).  This is what we normally do now, unless the client has sophisticated IT who want to do it their way (i.e. log backups during the day to minimize data loss in the case of a disaster).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.