When a doctor is in front of a patient, he first attempts to diagnose the disease before prescribing drugs! Now, before I provide you the first pill; I have to give you some details about a SQL Server database.

A database is made up of two files: dbname.mdf and dbname.ldf, the first contains your data while the second contains the transaction log. In fact, SQL Server is able to manage several log files for one database, and even several data files, but that’s another story. This log file memorizes every change in your data, to be able to restore them up to the minute in case of a crash.

Imagine a website with a few thousand news article records; you might think that the transaction log will be small, because when you write an article normally you do not rewrite the text several times a day… and you will be wrong! As a sample, to be able to provide you with statistics, the software must increase some counters whenever a visitor reads an article! In reality, many operations can be performed in the background and thus make the size of the log file grow.

Now let me give you another example, imagine you have tested a new module and imported a lot of records. Later, you change your mind and delete this module. If you think that you retrieved the space used on the drive by this module and the data imported... again you are wrong! Just like a hard disk does not really delete files but marks them as deleted, SQL Server will not retrieve the space previously used until you run the right command. The bigger the size of these files, the more time it takes access the information in the database. This is the reason your website can become increasingly slow.

Doctor’s advice: To treat a sick database, you must connect as a 'host' and go to Host > SQL.

Know the name of your patient

Several SQL commands require the db name and sometimes it can be tricky to find it because the db has been created by an automated procedure. Enter the following SQL command into the text box and click on ‘Execute’:

SELECT DB_NAME()

Know the size and weight of your patient

To verify if your patient needs a cure, you have to know his size (in pages) and his weight (in MB). Enter the following SQL command into the text box and click on ‘Execute’:

SELECT name, FILEPROPERTY(name, 'IsPrimaryFile') AS IsDataFile, FILEPROPERTY(name, 'IsLogFile') AS IsLogFile, size, FILEPROPERTY(name, 'SpaceUsed') AS spaceused, CAST(((size*8.0)/1024.0) AS decimal(18,2)) AS [CurrentSizeMB], CAST(((FILEPROPERTY(name, 'SpaceUsed')*8.0)/1024.0) AS decimal(18,2)) AS [SafeSizeMB]  FROM sys.sysfiles

This command returns an array of two records (at least). In general, the file name contains ‘Data’ and ‘Log’ to identify them easily. Whatever the names, the columns ‘IsDataFile’ and  ‘IsLogFile’ allow you to know the type of each of them (1=true, 0=false). The column ‘size’ contains the number of pages of 8KB currently allocated for the file. The column ‘spaceused’ contains the number of pages really used. The column ‘CurrentSizeMB’ is self-explanatory, while the column ‘SafeSizeMB’ is the size your file should have if you use the right pill! Of course, the difference between both of the last 2 values is the space you could save.

name    IsDataFile            IsLogFile              size        spaceused          CurrentSizeMB SafeSizeMB

DotNetNuke_2_Data    1             0             7984      7280      62.38     56.88

DotNetNuke_2_Log      0             1             128         61           1.00       0.48

Prescribe drugs to your patient

Now that you have been able to diagnose if your patient need a cure, you just have to prescribe the right pill. Enter the following SQL command into the text box and click on ‘Execute’:

DBCC SHRINKDATABASE(databasename)

Or

DBCC SHRINKDATABASE(0)

The second version using zero as parameter simply means ‘shrink the current database’. You can check if your treatment was effective by using the 'size & weight' pill. The data file should have regained its normal weight, while the lower weight for the transaction log file is 1MB. If the weight of both files has not changed, it’s probably because your patient has too high of a fever (busy server); in this case let them rest a little and re-apply the treatment later (at night).

Why your patient cannot be cured?

Sometimes patients are not receptive to the treatment; this is due to their immune system. SQL Server has a very special ‘immune system’; it can be configured in three different ways called ‘Recovery Models’: FULL, BULK_LOGGED and SIMPLE. As explained previously, the transaction log is used to restore your db. Depending on your needs, you can restore your db exactly as it was just prior to the accident (also called ‘point-in-time’ restore), or to the state where it was when the last full or differential backup occurred.

The full recovery model is the most secure, but it’s also the one needing the more maintenance. If your website is a ‘mission critical’ business application, then you should choose this recovery model (in this case, please don’t tell me you want a hosting plan for $2.00!). To make a long story short, this kind of real critical application requires several dedicated servers to be really fault tolerant, period! To prevent the log file from becoming huge, you must backup and shrink it regularly. Because the log file contains every transaction from the last full database backup, the size of the log file is never reduced by a DBCC SHRINKDATABASE command. You have to perform a separate backup of the log file then shrink it using a dedicated command.

The bulk logged recovery model is close to the full model. It differs on the way bulk data operations are managed. You should switch to this model only before a bulk operation then switch back to the full model just after.

The simple recovery model is the one I use for all my websites, even for heavy loaded ones! You can find articles on the Internet explaining that this model should not be used in production, that’s not true. SQL Server still uses the transaction log file with this model, but the log is automatically truncated when a checkpoint is reached. If you perform an update on a large amount of data, the log file will grow to memorize the state of the full transaction. When the transaction is complete and the data has been written to the data file, the log is truncated and the space can be re-used by the next transaction. On the other hand, the file size is not restored, because for the SQL Server, it’s just a waste of time. This is the reason why you must monitor the size of your database files from time to time.

If you can deal with a small loss of data and you have an automatic backup performed each night, you can use the simple recovery model. In the worst case, you will lose only the changes of the current day. It’s simple like that! Otherwise, you should rent a dedicated server and use a commercial version of SQL Server (not Express) to be able to create serious a maintenance plan.

Discover the kind of immune system of your patient

Before being able to make a decision, you have to know which ‘immune system’ is currently used. Enter the following SQL command into the text box and click on ‘Execute’:

SELECT recovery_model_desc FROM sys.databases WHERE name='dbname'

Change the immune system of your patient

Because you know differences between each recovery model, you could decide to switch to the simple recovery model. Don’t do it if your data is critical or if you don’t truly understand the risks! Enter the following SQL command into the text box and click on ‘Execute’:

ALTER DATABASE dbname SET RECOVERY SIMPLE

Surgery is sometimes the only choice!

If your website uses the full recovery model and your hard disk is full, you can try the ‘last chance’ surgery. You have to understand that after this ‘surgery’ you will not be able to perform a ‘point-in-time’ restore of your database! Because your transaction log will be totally erased, you should perform a full backup of your database just after.

People who are Dinosaurs know what a ‘system device’ is (CON, COM1, PRN and so on). If you are old like me, you remember your operating system has a NUL device. When your write something to the NUL device, you write… nowhere! We will use this device to deceive SQL Server and make it believe that the log was saved.

Don’t forget to replace ‘dbname’ and ‘logfilename’ before performing the commands, dbname was provided by the first pill while logfilename was provided by the second one. Enter the following SQL commands into the text box and click on ‘Execute’:

BACKUPLOG dbname TO DISK='NUL'

CHECKPOINT

BACKUPLOG dbname TO DISK='NUL'

DBCC SHRINKFILE (logfilename)

Be a ‘Good Doctor’!

I can’t teach you how to compete with Dr House in just a few paragraphs, but you will find in the references several really good articles. Especially if you use SQL Server Express, you should have a look at the last article. Ola Hallengren is a talented db admin who provides, for free, several useful procedures to maintain your SQL Server.

I hope you have learned some good practices and discovered some tips and tricks with this blog post! It was just a few tips, now it’s up to you to learn more about SQL Server and become a ‘Good Doctor’.

References:

MSDN – DB_NAME: http://msdn.microsoft.com/en-us/library/ms189753.aspx

MSDN – FILEPROPERTY: http://msdn.microsoft.com/en-us/library/ms188401.aspx

MSDN – SHRINKDATABASE: http://msdn.microsoft.com/en-us/library/ms190488.aspx

MSDN – ALTER DATABASE SET Options: http://msdn.microsoft.com/en-us/library/bb522682.aspx

MSDN – DBCC SHRINKFILE: http://msdn.microsoft.com/en-us/library/ms189493.aspx

About.com – SQL Server Recovery Models: http://databases.about.com/od/sqlserver/a/recoverymodels.htm

MSSQLTips - SQL Server Simple Recovery Model: http://www.mssqltips.com/sqlservertutorial/4/sql-server-simple-recovery-model/

TechRepublic – Help! My SQL Server Log File is too big!!!: http://www.techrepublic.com/blog/datacenter/help-my-sql-server-log-file-is-too-big/448

SQLServerPedia - Backup Log with Truncate_Only: Like a Bear Trap: http://sqlserverpedia.com/blog/sql-server-backup-and-restore/backup-log-with-truncate_only-like-a-bear-trap/

SQLskills.com – BACKUP LOG WITH NO_LOG - use, abuse, and undocumented trace flags to stop it: http://www.sqlskills.com/blogs/paul/post/backup-log-with-no_log-use-abuse-and-undocumented-trace-flags-to-stop-it.aspx

TechRepublic – Create database maintenance plans in SQL Server 2005 using SSIS: http://www.techrepublic.com/blog/datacenter/create-database-maintenance-plans-in-sql-server-2005-using-ssis/248

TechNet – Microsoft SQL Server 2000 Index Defragmentation Best Practices: http://technet.microsoft.com/library/Cc966523

Ola Hallengren – SQL Server Maintenance Solution: http://ola.hallengren.com/