Docstoc

Shrink Large SQL Log files

Document Sample
Shrink Large SQL Log files Powered By Docstoc
					How to Shrink Large SQL Log Files for ANCS+ 8.0
09/15/2006. 3 pages.

This white paper explains how to shrink the size of larger SQL log files created while using ANCS+ 8.0.
Microsoft SQL Server 2005 may create large “transaction log” files as it works with the data, especially
when converting data from the previous version of ANCS+, or when “Sweeping” large databases. These
log files can be made smaller to free additional hard drive space.

Please contact Re:discovery technical support for assistance using this white paper. Working with
Microsoft SQL Server may require you to have Administrator rights on your computer.

Note: The log file is a component of the Microsoft SQL Server database system. It records information
about “transactions” in your database, for potential use recovering from data loss. You should make a
backup of your data before shrinking the log file. The log file does not contain actual data used by
ANCS+ 8.0.

1. In Windows Explorer, make a note of the large log file(s) you want to shrink. Log files are named with
an LDF file extension. For example: “RediscoveryNPS_CR_PARK_Log.LDF”, where “PARK” is your park
acronym, and “CR” indicates a Cultural Resources data directory. NH indicates a Natural History
directory, AR indicates an Archives directory, and IT indicates an Item level archives directory. The
illustration below shows several large log files.




Make a note of the name of the specific log file(s) you want to shrink. You will need the file name(s) in
step 3 below.




        Re:discovery Software Inc. • 3040 Berkmar Drive Suite B1 • Charlottesville, VA 22901                1
               Phone: 434.975.3256 • Fax: 434.975.3935 • Email: support@rediscov.com
                                     Web: RediscoverySoftware.com
2. Start Query Express: Open the QueryExpress folder, and double-click the file “QueryExpress.exe”.
The Query Express “Connect…” window opens:

Enter the SQL server name. For stand-alone
and small network installations of ANCS+,
use the example shown, but replace
(localhost) with your computer name. For
large network installations, use the computer
name and the name of the SQL instance.

Use SQL Server Authentication.

Enter the login name. The default for ANCS+
is “red”.

Enter the password. For stand-alone and
small network installations of ANCS+, contact
Re:discovery for the password. For large
network installations, use the SQL password
you assigned.

Click the Connect button.


The Query Express window opens:

The Query Express window
may or may not show the
database browser column on
the left. To toggle the
browser on or off, click the
Show/Hide Browser button on
the button bar, or choose
Hide Object Browser from the
Window menu.

In the large white space, you
will enter command lines as
shown in the examples on the
next page.




       Re:discovery Software Inc. • 3040 Berkmar Drive Suite B1 • Charlottesville, VA 22901           2
              Phone: 434.975.3256 • Fax: 434.975.3935 • Email: support@rediscov.com
                                    Web: RediscoverySoftware.com
3. In the large white space of the Query Express window, enter command lines as shown in the examples
below. The command will be specific to the file name for the log file you want to shrink. In the examples
below, replace “TEST” with the acronym in the log file name. Leave all other text exactly as shown
below.

For a file named RediscoveryNPS_CR_TEST_Log.LDF:
use RediscoveryNPS_CR_TEST
alter database RediscoveryNPS_CR_TEST set recovery simple
dbcc shrinkfile (RediscoveryNPS_FOBU_Log)
alter database RediscoveryNPS_CR_TEST set recovery full

For a file named RediscoveryNPS_NH_TEST_Log.LDF:
use RediscoveryNPS_NH_TEST
alter database RediscoveryNPS_NH_TEST set recovery simple
dbcc shrinkfile (RediscoveryNPS_NH_PARK_Log)
alter database RediscoveryNPS_NH_TEST set recovery full

For file named RediscoveryNPS_AR_TEST_Log.LDF:
use RediscoveryNPS_AR_TEST
alter database RediscoveryNPS_AR_TEST set recovery simple
dbcc shrinkfile (RediscoveryNPS_AR_PARK_Log)
alter database RediscoveryNPS_AR_TEST set recovery full

For a file named RediscoveryNPS_IT_TEST_Log.LDF:
use RediscoveryNPS_IT_TEST
alter database RediscoveryNPS_IT_TEST set recovery simple
dbcc shrinkfile (RediscoveryNPS_IT_PARK_Log)
alter database RediscoveryNPS_IT_TEST set recovery full

When the command lines are ready, click the “Execute Query” button (the green arrow), or choose
Execute Query from the Query menu.

The lower half of the window
will indicate “The command(s)
completed successfully”, and
may show the grid of numbers
as shown here. Repeat the
command and execute for
additional log files if needed.
Close Query Express when
finished. If the system asks
you ”Save changes to
untitled1.sql?”, click No.

The log file(s) will be reduced
to the minimum file size.




        Re:discovery Software Inc. • 3040 Berkmar Drive Suite B1 • Charlottesville, VA 22901            3
               Phone: 434.975.3256 • Fax: 434.975.3935 • Email: support@rediscov.com
                                     Web: RediscoverySoftware.com

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:11
posted:9/21/2011
language:English
pages:3