Browse DevX
Sign up for e-mail newsletters from DevX


Control Log File Growth to Avoid Out-of-Space Errors-4 : Page 4




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Recreating Error 1105
In order to gain an understanding of how Error 1105 occurred in the first place, I recreated the error. I "broke" my configuration by causing it to fail and generate the error.

Figure 1. Recreating Error 1105: Unchecking the Automatically Grow File option disallows the DB from asking the OS for more space.
After setting the recovery model to simple and disabling AutoGrow, I forced the error by filling up the files. Unchecking the Automatically Grow File option disallows the DB from asking the OS for more space. I unchecked the checkboxes under the Log and Data File tabs (see Figure 1).

The next steps were to create a table in the dev database called stuffit and then create a routine to make the file run out of space. The condition While 1 = 1 makes the insert statement run forever. This routine starts a process that keeps going until the error is generated and the database stops operating. After opening Query Analyzer and executing the insert statement (see Figure 2), I succeeded in generating the error, leaving the database dead in the water. The database was still available for querying, but nothing else. Its data file was too full to accept any more inserts.

I used the following resources to recreate the error:

  • SQL Server log files
  • Performance monitor logs
  • Alerts engine and job steps

You can use them to debug and document the out-of-space errors you encounter. Let's step through each one and examine the helpful information it can offer.

Log Files
The server creates a rolling set of log files that is available inside Enterprise Manager and in the SQL default directory. These files are the first line of defense in debugging most errors. Here is a log report for the activity that generated Error 1105:

2002-03-23 10:48:44.43 spid54 Error: 1105,
Severity: 17, State: 2 2002-03-23 10:48:44.43 spid54
Could not allocate space for object 'stuffit'
in database 'dev' because the 'PRIMARY' file
group is full.

This is a straightforward report of what's gone wrong, but it's light on details about how to correct it. One solution is to enlarge the file by performing an Alter Database command and specifying how much to increase the file size.

Performance Monitor

Figure 2. Query Analyzer: Setting up Query Analyzer and hitting the Execute button generated the desired error.
Performance Monitor is a tool that comes with the operating system, which gives it a somewhat more inclusive perspective than the SQL error logs. When you install SQL Server, Performance Monitor receives several extra sets of counters specifically for the SQL Server DBMS. The extra counters log disk access, memory used, threads opened, and processor time expended. They give you additional depth in evaluating bottlenecks in your installation, and guide you about what resources need to be expanded to eliminate the bottlenecks.

Performance Monitor's biggest advantage is that it is integrated with SQL Server's Alerts and Jobs Objects, part of the Distributed Management Object (DMO) (see Figure 3). An administrator can make a counter's threshold be the trigger for an alert, which can email or page the administrator or whomever is assigned to maintenance.

Figure 3. Performance Monitor: Performance Monitor is integrated with SQL Server's Alerts and Jobs Objects.
Database Alerts
The Alerts engine is a SQL Server tool that integrates the output of Performance Monitor with the SQL DMO Job object. It not only can find out about trouble, it can actually do something about it. It can be set to monitor for a specific error number (such as a showstopper like 1105) or watch for a particular Performance Monitor threshold to be reached. Since the Alert engine is integrated with the Job engine, once the error or event is detected it can initiate a SQL DMO Job that executes a workflow you specify.

The workflow has the capability to take different actions based on the success or failure of its preceding steps, so the action you specified can branch. The specified actions are in the form of T-SQL commands you type into the Job steps held by the Alert. In Figure 3, I just set an alert for the size of the log file so I would get a warning. The warning can come as a dialog, a page to an administrator, or an e-mail informing the recipient of the execution of the job.

Plan Ahead
The solution for out-of-space errors is to plan ahead for them with appropriate setup, sizing, and maintenance to keep logs from out-of-control growth. You can eliminate surprises by setting up alerts and jobs that will catch and potentially fix the errors for you. As a last resort, you can make existing files larger or create additional files with the Alter Database command. Should you choose this solution, you may have to move the file to a different drive that is visible to the server and create new files on the new file group so the database can use them.

Drew Georgopulos is the Database Architect for GoAmerica Communications. He moonlights as an Adjunct Professor of Computer Science at Marymount College of Fordham University in Tarrytown, NY teaching systems analysis and relational database design. He holds a certification in systems analysis and relational design from Columbia University, has presented at SQL Connections on SQLDMO, and is a member of the Worldwide Institute of Software Architects.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date