Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1296
  • Last Modified:

ERRORLOG file on WSUS is getting very large and continues to grow.

I'm receiving the following error when looking at the event viewer for application;
Error: 9002, Severity: 17, State 6
The log filr for database 'OnePoint' is full. Back up the transaction lof for the database to free up some log space.

I'm new to SQL and in the past I have just been going in and deleting some BAK files that have date's older then a month to help free up space. I would like to figure out what is causing this file to increasingly get larger and larger.  Using SQL 8.0 and WSUS 3.1.6.
  • 2
1 Solution
Mark WillsTopic AdvisorCommented:
Nah, it isn't the external BAK files, it is within the database itself.

Database is made up of LOG and DATA and held as independant files on disk. That have an allocated space on disk and it adds data into that space as needed - ie it has a capacity to carry a quanitified amount of data. You message is saying it is full - ie it is at 100% capacity. Normally the DATA and LOG files are set to auto grow, but might not be enough physical disk to grow, or, might not be set.

So, you will need to go into SQL Server and "fix" the OnePoint database. Right click on the database, and do a full backup before anything else. Then right click again (on the database name), go into tasks, then shrink files. A pop-up will appear and you can see the data and the log file capacities (use the drop down to chnge from data to log). Then toward the bottom you can select to shrink the log file. It should show you the capacity and free space, do not shrink it all the way down, it does need some nominal space. The log will shrink very quickly - the data no where near as quickly. Moving forward, it would be best to change the recovery mode to simple (right click on the database, go into properties). It is a minimally logged option and keeps the log file clean.

The set up a maintenance plan to do full backups and it will stay nice and clean for you...  However, if not comfortale, or not exposed to the SQL Server space, then can use Wsusdbmaintenance scripts - have a look at : http://go.microsoft.com/fwlink/?LinkId=87027

Also worth having a look at : http://technet.microsoft.com/en-us/library/cc708594.aspx  and click on a few of those links, and look on the left hand side for other WSUS commentary.
Mark WillsTopic AdvisorCommented:
Apologies, posted the wrong link - not so much that it is wrong, just not the one I wanted to share : http://technet.microsoft.com/en-us/library/cc708471.aspx
Your database is set to FULL recovery mode. What that means is that it will never remove from the transaction log the data that has become obsolete, that is the transactions are finished and closed and rendered the data modified in the database file (mdf). The transaction log is a file that has at leas two purposes one is to ensure that the transactions are either committed or rolled back if a problem occurred, data integrity, and second for recovery in case of failure.

If the database is a production one then FULL recovery model is OK but in that case you need to do FULL backups the database at times, maybe differential backups and also transaction backups. This is what is called a maintenance plan. Making backup to the transaction log will also truncate it and leave space to grow again.

If the database is only for development or QA purpose then switching to SIMPLE recovery mode will just take care of the transaction log.

The difference is that the FULL mode offers you the possibility to recover data in case of failure until the point in time of failure while the SIMPLE mode no. You will only be able to recover data ssaved with the last full backup.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now