Blog » 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.

  USE database_name 
  EXEC sp_helpfile

--Truncate Log
  BACKUP LOG database_name WITH TRUNCATE_ONLY
  GO

--Shrink Log file
  DBCC SHRINKFILE (logfile_name, 1)
  GO

--Shrink Data File
  DBCC SHRINKFILE (datafile_name, 1)
  GO

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.

Michael Gibbs
Stalk Me...
Latest posts by Michael Gibbs (see all)