Common problems with SQL SERVER --- SQL Server transaction logs are full.

Hello All,
In the past 2 years, i have noticed an enormous amount of ISV's are using MS SQL Server (Express, Standard) as their application back end, common applications such as Commvault, Backupexec, Vmware Virtual Center, and many others have adopted SQL Server's express (free edition) as their back end database and this perhaps brings in new challenges for IT Admins who are not very familiar with SQL Server.


One of the most common problems i have noticed is SQL Server Transaction logs causing grief right left and center, below are a few steps and tips which can help resolve similar issues.


Thanks
Huzeifa Hakimuddin Bhai

Problem: SQL Server transaction logs are full

When the transaction log becomes full, SQL Server Database Engine issues a 9002 error. The log can fill when the database is online or in recovery. If the log fills while the database is online, the database remains online but can only be read, not updated. If the log fills during recovery, the Database Engine marks the database as RESOURCE PENDING

Solution:

Step 1:- Truncating transaction logs

1)      First take a full backup of your database, then run the following commands,

2)      BACKUP LOG <Database name> WITH TRUNCATE_ONLY

The above command will truncate your transaction log files but will not reduce the size of the transaction logs. Just truncating the transaction log file should be enough to get the database back to operational, however If the transaction log file has outgrown what the customer originally anticipated, we can shrink the log files to an acceptable size.

Step 2:- Shrinking transaction logs

1)      Before shrinking the log file take a full backup of your database just to be sage, then verify the current size of your database transaction logs as well as the percent currently in use. Use the below command,

2)      DBCC SQLPERF(logspace) 

3)      Considering the information above run the following command

DBCC SHRINKFILE(pubs_log, 2)

The first parameter in brackets is the logical name of the transaction log file and the second parameter is the size you want this log file to get reduced to, sizes are in MB.

In the above example, I am reducing the pubs_log transaction log to 2 MB

4)      How to check the logical name of the transaction log file?

a)      select a.name, b.name as 'Logical filename', b.filename from sys.sysdatabases a

inner join sys.sysaltfiles b

on a.dbid = b.dbid where fileid = 2

OR

b)      Open management studio àRight click on the Database àClick Properties àclick Files

You should see the logical name of the log file.

5)      Verify log file sizes by running the command again DBCC SQLPERF(logspace) 

Hope this helps

Thanks

Regards

Huzeifa