| Applies To | |||
| Product(s): | SELECTserver | ||
| Version(s): | 09.00.00.95 | ||
| Environment: | N\A | ||
| Area: | Installation / Configuration | ||
| Subarea: | SQL Management | ||
| Original Author: | Philip Conard, Bentley Technical Support Group | ||
Problem
There is an error message in the SELECTserver log files that the SQL
transaction logs are full
Solution
To shrink the SQL Transaction logs, first figure out the database
recovery mode. To find out if you database recover mode is set to full,
bulk-logging, or simple right click on database, properties, click on
Options in the window, 2nd drop down is the mode. Here is bit more info
on DB recovery modes:
http://msdn.microsoft.com/en-us/library/aa173531%28v=SQL.80%29.aspx)
If your database recovery mode is set to FULL or bulk-logging, use the
SQL statement below.
The example script looks like this
USE [master]
GO
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
When you use the statement above you have to change some of the values
to match your database for a test db. This is what it looks like with a
database name of hdr_oma_2-15-11
USE [hdr_oma_2-15-11]
GO
ALTER DATABASE [hdr_oma_2-15-11] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(PWOMA_HUB_Log, 1)
ALTER DATABASE [hdr_oma_2-15-11] SET RECOVERY FULL WITH NO_WAIT
GO
In this case, hdr_oma_2-15-11 is the name of the database as seen in sql
management studio
PWOMA_HUB_Log is the logical name of the transaction log. You can find
this by right clicking on the database and selecting properties, click
on 'files' and look in the 'logical name' column.
If your database recovery mode is set to Simple the script is a bit
simpler,
USE [master]
GO
DBCC SHRINKFILE(TestDbLog, 1)
GO
So in my case
USE [hdr_oma_2-15-11]
GO
DBCC SHRINKFILE(PWOMA_HUB_Log, 1)
GO