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.
dcosta25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
ZberteocCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.