Applies To
	Product(s):	SELECTserver
	Version(s):	09.00.00.99
	Environment:	N/A
	Area:	Installation / Configuration
	Subarea:	SQL Management
	Original Author:	Michael.Dougherty, Bentley Technical Support Group

 

 

 

 

 

 

 

Error or Warning Message

There is an error message in the SELECTserver log files that the SQL transaction logs are full.

Explanation

As time goes by, the disk space requirements for the SELECTserver database may fluctuate greatly. This is due to how SQL server functions and will require the database to be maintained. You can set SQL Server to automatically expand a database when it requires more space; and you can also set it to "shrink" so it can reclaim allocated but unused space. Sometimes it makes sense to enable autogrow and autoshrink events, and sometimes it doesn't.
(See KB #315512 from Microsoft for information about enabling each of these options.)

Shrinking
Data Files (*.MDF / *.NDF)

If you haven't enabled the auto-shrink setting, there may be times when your database has been cleared of significant amounts of data, and you'd like to reclaim the space on disk that the partially empty MDF file is taking up. To do this, you can use the DBCC SHRINKDATABASE command. See this MSDN entry for the technical documentation on the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands:

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
[SELECTserver]

GO

ALTER
DATABASE [SELECTserver] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC
SHRINKFILE(SELECTserver _Log, 1)

ALTER
DATABASE [SELECTserver] SET RECOVERY FULL WITH NO_WAIT

GO

In this case, SELECTserver is the name of the database as seen in sql management studio

SELECTserver_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
[SELECTserver]

GO

DBCC
SHRINKFILE(SELECTserver_Log, 1)

GO

 

See Also

Increase SQL Server Performance for the SELECTServer

 

Technical Requirements
http://communities.bentley.com/products/licensing/w/Licensing__Wiki/selectserver-technical-requirements.aspx

SELECTserver FAQ:
http://communities.bentley.com/products/licensing/w/Licensing__Wiki/selectserver-general-faq.aspx

Deployed SELECTserver Knowledge Requirements

http://communities.bentley.com/products/licensing/w/licensing__wiki/deployed-selectserver-knowledge-requirements-tn.aspx

 

REFERENCE

http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/

http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html