Solved

BES SQL Server growing and shrinking at large intervals

Posted on 2009-03-30
7
1,049 Views
Last Modified: 2012-06-27
Hi,
The amount of space taken up by our bes database on our bes server has been fluctuating a lot in a small amounts of time.  3 days ago there was around 14GB of free space on the drive, then, this morning I was working and noticed it down to 1.6GB of space on the drive.  I deleted some logs and got it to 4GB, but then this evening it was back down just below 1GB.  The BESMgmt.ldf file has been changing size, but I don't know why.  What gets stored in the database there?  Can I do anything about how large the .ldf file is?  Please help, this thing's going to crash.

BlackBerry Enterprise Server Version: 4.1.6.11
SQL Server 2008
0
Comment
Question by:RobocopGodzilla
  • 3
  • 2
7 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 168 total points
ID: 24026184
It sounds very much like transaction log backups aren't running on your SQL Server database. Check your logs to see if the backups are completing successfully - both DATABASE and LOG backups.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 336 total points
ID: 24032146
Database is made up of two (can be more) files - the data file is the mdf and the transaction log is the ldf. The backup is a vital part of SQL server health. And we are talking about the backup facility within SQL Server itself.

The transaction log serves a very important part of a recovery process in some situations and depends on the recovery mode of your database. If running "SIMPLE" recovery mode then transactions are minimally logged and the transaction log file is kept pretty clean and small all by itself. If running "FULL" recovery mode then individual transaction are logged in the transaction log and then you need to manage the log file.

The idea behind FULL recovery mode (and is the default for a database in 2005 and 2008) is that you can recover up to a point in time by applying transaction log backups to the last full backup. Whereas SIMPLE recovery is a matter of going to the last full backup.

FULL recovery mode does need management, and the best way is to use maintenance plans. Run a full backup every night, and then run transaction log backups during the day at regular and frequent intervals - most common are multiples of 15 minutes (ie every quarter hour, half hour, hourly etc). That helps keep the transaction log fairly small and clean.

The maintenance plans can be scheduled to run automatically (not in the Express edition). So, can be a case of set and forget (well set and monitor really).

If you are seeing growth in the transaction log, then it sounds like you are not running frequent enough transaction log backups.

You could change the database to simple recovery, but then in the event of a database failure, your only protection is the last full backup, and is ill-advised for a normal OLTP type operation. For BES which is more a data repository rather than data processor, then it is your call - if your business depends on the information, then you will want high availability and high recoverability (meaning FULL recovery mode).

So, if you do not already have them, and it is not the Express edition, then create three new maintenance plans.
1) Full nightly backup - is a full backup run each day / night - could even run multiple times during the day if the database is smallish.
2) Transaction log backup - if running FULL recovery mode (otherwise do not worry about it).
3) Database Maintenance Tasks - run periodically to update statistics, rebuild indexes, shrink / resize databases etc - normally run at a period where there is high volume turnovers such as an end of month purge / archive or similar - keeps database running efficiently - can run weekly over the weekend if you have some downtime available and regardless of activity.

In the meantime, run a FULL backup and set up those maintenance plans. Maybe and only momentarily, change to SIMPLE recovery mode until you have your plans in place. Thouhgt should not take long to set up and is done in SSMS (SQL Server Management Studio)

You can also inspect and shrink your files by right clicking on the database, tasks, shrink, files  and a window will popup. There is a drop down selection for looking at either the data file or the log file. The down the bottom, you can select some actions - best to use middle radio button to reorganise and also nominate some space. You do need to allow enough space for the data base to grow (physical allocation of disk, and then starts to use that space with data activity).

The logs will shrink very quickly. The data files will take considerably longer.

0
 

Author Comment

by:RobocopGodzilla
ID: 24077132
Yes, our BES was set to full recovery mode because it is set up to mirror its database on another server.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 336 total points
ID: 24081373
Mirror - in the SQL definition ?  Very important part of the puzzle. Can have big impact. Outrageous transaction logs can indicate that the mirror is broken...

When your database is a part of Database Mirroring, you must be FULL recovery mode, and optionally / additionally can use log shipping. You must not truncate the log even if you back up the transaction log.

Some other factors come into play - specifically how the mirrored server is set up. E.g. Is your network bandwith fast enough to handle the workload between your principal and mirror servers? Is there a problem or systems (such as virus checking) on the principal or mirror that might affect IO throughputs ?

Basically, Database Mirroring (even if the mirror server is broken), keeps the transactions in the transaction log file to send them to the mirror database when it's available. So clearly there is evidence of some kind of lag between the mirror and the live site - which could be using additional log shipping.

Have a look at : http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

specifically the areas around : "automatically assumes the role of the mirror and starts synchronizing with the principal. For as long as the mirroring state stays DISCONNECTED, the transaction log space on the principal cannot be reused, even if you back up the transaction log. If the log file grows and reaches its maximum size limit or runs out of disk space, the complete database comes to a halt. To prevent this you have two options
 1) either plan for enough disk space for the transaction log to grow and bring back the mirror database before the space fills up, or
 2) break the database mirroring session."

0
 

Author Comment

by:RobocopGodzilla
ID: 24150284
So it's been a week or so.  We ended up breaking the mirror, truncating the transaction log and we got all of the hard drive space back.  

After that crisis was averted, I did a full backup of the principle db, then full restore on the mirror.  Then did a transaction log backup on the principle and a transaction log restore on the mirror.  I reenabled the mirroring, but I'm positive we'll have the same issue in a few months.  Can I schedule any maintenance that will prevent this from happening?  Or do I have to break the mirror every now and then, truncate the log and then re-enable the mirror?

Is there a chance the mirror wasn't set up correctly by the previous guy or by me just now?  I didn't totally understand the last paragraph of the previous post.  Someone out there has to have a MS SQL Server mirror that they don't have to constantly think about.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24291955
There was a bug sometime ago with Maintenance Plans and Mirrors but was fixed as part of CU5 in SP2. So, best to make sure you have all service packs up to date.

You only have to stop or suspend mirroring for planned outages. Like updating all the patches. You might also like to stop it if you are doing some structural, like generating a new clustered primary key on a large table - it can cause the mirror tempdb log to go a bit ballistic. So important point here is to plan ahead.

When restoring those file on the mirrored site, use the norecovery option.

Might like to have a quick read of : http://technet.microsoft.com/en-us/magazine/2007.03.insidemscom.aspx good high level overview of getting ready.

You should read through that previous link as well  : http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

And remember an uncontrollable log file is probably the best indicator (apart from various logs and messages), that there is something wrong with the mirror and may need you to recreate.

0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now