Everyone’s favorite topic of dinnertime conversation! Or maybe that’s just me? With that being said, there are a number of reason for a database to grow quickly in size; however the majority of the time, this can be tied to one specific issue, which fortunately can be remedied easily.
To begin the diagnosis it is best to check the database file size. The location of the files are generally based on the application which had created the particular SQL instance. To discover the path simply open SQL Server Management Studio and expand the database list. Right click the database in question and choose the “properties” selection. Once on the properties window choose the “files” selection from the selection group in the top left pane. This will indicate the path of the files which can then be pasted into the windows explorer, taking you directly to their location.
The database is comprised of two files, one with an .MDF extension and the other with an .LDF extension. The .MDF file is the primary data file of a SQL database. The .LDF file is the transaction log file of a SQL database. In this scenario we are concerned with the size of the .LDF (log file) in relation to the .MDF file. If the transaction log file (LDF) is multiple times bigger than the data (MDF) file then this is the source of the space issue on your hard drive. Normally the log file (LDF) is quite smaller in size than the data (MDF) file.
In order to correct this issue you would need to open SQL Server Management Studio and expand the database list. Right click the database in question and choose the “properties” selection. Once on the properties window choose the “options” selection from the selection group in the top left pane. You will most likely see that the “recovery model” is set to FULL. Now, assuming that you have not set up a transaction log backup process*, you can change the recovery model to SIMPLE. In addition you can change the “auto shrink “ option (under other options) to TRUE and click OK. The log file will begin to shrink of the next couple of days and will remain small than the data file.
* What is the main difference between Full and Simple?
Simple: No transaction log backups. Changes since the most recent backup are unprotected. In the event of a disaster, those changes are lost and need to be redone. You can only recover to the end of a backup.
Full: Requires transaction log backups. No work is lost due to a lost or damaged data file. Can recover to a specific point in time, assuming that your backups are complete up to that point in time. For example, prior to application or user error.
Need SQL help? Click here to schedule a free consultation with us or give us a call at (212) 869-9300.
