SQL Files Grown Out of Control?

SQL Files Grown Out of Control?

For merchants running AspDotNetStorefront who aren’t regularly running the Monthly Maintenance routines, you might find yourself in a situation similar to one of our clients where the size of the transaction log for their SQL database had grown large enough that their site was shut down because it couldn’t grow any more.

In this case, since we couldn’t access the admin section of the site to run the monthly maintenance, I was able to run the following commands in order to clear the transaction log to the point of the last backup AND then shrink the physical size of the SQL database files. Please note that before running these commands GET A FULL BACKUP OF THE DATABASE. If you can’t do this directly from SQL Management Studio (or similar tool) you should be able to initiate an ad-hoc backup from your hosting company’s control panel.

Where:

  • database_name is the name of the database
  • logfile_name is the name of the logfile (not the filename of the physical file) as shown in the sp_helpfile results
  • datafile_name is the name of the datafile (not the filename of the physical file) as shown in the sp_helpfile results

The assumption in the latter two are that the datafile_name is the name of the database and the the logfile_name is the name of the database with _log appended to it (like exhibita, exhibita_log for the database named exhibita) but this is not always the case. If you can’t access the results of exec sp_helpfile command from SQL Management Studio (or equivalent), you should be able to get this via your hosting control panel or from the host directly.

Here is an example of that command on one of my databases:
image

As you’ll see from the above, the rule of thumb that I mentioned in the previous paragraph doesn’t apply here as the filename for the Data file is different than the “standard”.

DON’T FORGET TO BACKUP YOUR DATABASES before trying this though.

Do you have other techniques that you use to accomplish this task? If so, please let us know using the comments section below.

Stalk Me...

Michael Gibbs

I'm a technologist at heart, assisting companies best utilize the Internet to market themselves and sell their products and services online. This includes organizations as large as Google and as small as one or two individuals.
At Over The Top and Exhibit A Communications, I've programmed solutions for Google as well as at least one other company that was later acquired by Google.

I've been CTO of an Internet SaaS company and spent my time pretty evenly between guiding the future technical strategy of the company, architecting software solutions for my dev teams, designing and running a data center to service our clients world-wide as well as being a technical evangalist/sales engineer to our media clients large and small.

I've also been Chief Photographer of the Daily Sun/Post newspaper back when it was a 5-day a week daily newspaper.

I also spent a great many years as a beach lifeguardfor the City of San Clemente as well as Jr. Lifeguard instructor and then as it's program coordinator.
Bragging rights
Private Pilot with Instrument rating and proud husband of soon to be 25 years (and counting).
Stalk Me...

Latest posts by Michael Gibbs (see all)

Leave a Reply