sql server misc: Hi

My transaction log has reached an unwanted size, 2GB for a 5 MB database. I
am using SQL 2000. How do i shrink it?

Martin,

The following information may assist you in achieving your goal. It was
obtained from a sample chapter of

"Microsoft® SQL ServerT 2000 Administrator's Pocket Consultant " by William
R. Stanek.
The ISBN is 0-7356-1129-7.

==

To manually compact or shrink database files (both data and log files) in
Enterprise Manager, complete the following steps:



1.. Start Enterprise Manager and then, using the entries in the left pane,
work your way down to the Databases folder.

2.. Right-click the database you want to work with and from the shortcut
menu, choose All Tasks. From the All Tasks menu, choose the Shrink Database
option.

3.. You should see the Shrink Database dialog box shown in Figure 4-5. The
Database Size area shows the total amount of space allocated to all database
files and the amount of free space. Use this information to help you decide
whether you really want to shrink the database.

4.. Use Maximum Free Space In Files After Shrinking to set the percentage
of free space in the database. To squeeze all extra space out of the
database, use a value of 0 percent, but be aware that the next write
operation may cause the database to grow automatically.

5.. To reorganize data pages and move them to the beginning of the data
files, select Move Pages To Beginning Of File Before Shrinking. This
compacts the data pages but doesn't remove empty data pages.


----------------------------------------------------------------------------
--
NOTE:
Log files aren't reduced in size immediately. Instead, the size is reduced
when the transaction log is backed up or the log is truncated, whichever
occurs first. Also, you normally can't shrink a database smaller than the
model database (which is the database template).
----------------------------------------------------------------------------
--



Click to view graphic

Figure 4-5. Shrinking a database is easy with Enterprise Manager's
Shrink Database dialog box. Just make your selections and click OK, or
schedule the task on a recurring basis.



6.. Click OK to begin or continue on to step 7 for scheduling. SQL Server
locks the database while shrinking it, which blocks access.

7.. The property settings you make in this dialog box are saved and unique
to the current database. If you want to use these properties to shrink the
database on a recurring basis, select Shrink The Database Based On This
Schedule and then click Change. You can now schedule this task as explained
in Chapter 12, "Database Automation and Maintenance."
To manually compact or shrink individual database files in Enterprise
Manager, complete the following steps:




1.. Start Enterprise Manager and then, using the entries in the left pane,
work your way down to the Databases folder.

2.. Right-click the database you want to work with and from the shortcut
menu, choose All Tasks. From the All Tasks menu, choose the Shrink Database
option.

3.. You should see the Shrink Database dialog box shown previously in
Figure 4-5. The Database Size area shows the total amount of space allocated
to all database files and the amount of free space. Use this information to
help you decide whether you really want to shrink the database.

4.. Click Files. This displays the Shrink Database Files dialog box.

5.. Use the Database File selection menu to choose the data or log file
that you want to shrink.

6.. Choose a shrink action:


a.. Compress pages and then truncate free space from the file

b.. Truncate free space from the end of the file

c.. Empty the file (data will migrate to other files in the filegroup)

d.. Shrink file to __ MB


7.. If you want to use these properties to shrink the data or log file at
a later date and time, select Shrink The File Later and then select a date
and time.

8.. Click OK.
Another way to shrink a database is to use Transact-SQL. Two commands are
provided:


DBCC SHRINKDATABASE ( database_name [, target_percent] [, {NOTRUNCATE |
TRUNCATEONLY}] )DBCC SHRINKFILE ( {file_name | file_id } { [, target_size] |
[, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}] }) You use DBCC SHRINKDATABASE
to shrink all data files in the database and DBCC SHRINKFILE to shrink a
specific data file. By default, these commands also compact the database.
You can override this option with TRUNCATEONLY or specify that you only want
to compact the database with NOTRUNCATE.
The following command compacts and then shrinks the Customer database to 30
percent free space:


DBCC SHRINKDATABASE ( Customer, 30 ) The following commands compact and then
shrink an individual file in the Customer database to 5 MB free space:

USE CustomerDBCC SHRINKFILE ( Customer_Data, 5 )
----------------------------------------------------------------------------
----
NOTE:
The DBCC SHRINKFILE command is the only technique you can use to shrink
individual data and log files to be smaller than their original size. With
DBCC SHRINKFILE, you must shrink each file individually, rather than trying
to shrink the entire database. Additionally, the truncation options for DBCC
SHRINKDATABASE and DBCC SHRINKFILE only apply to data files and are ignored
for log files. You don't truncate transaction logs with these commands.

--
Keith Wilson

This posting is provided "AS IS" without express or implied warranty,
guarantee, or rights.


[quoted text, click to view]


begin 666 imagelink.gif
M1TE&.#EA) `9`*+_`````/___\W-S>CHZ#-F___,`(J*BF;,,R'_"T%$3T)%
M.DE2,2XP`M[M`"'Y! ``````+ `````D`!D```.O&+K<_A""2:N]-2I0NO]@
MV ':P(FH2$8"8+QP+,_O^@RMIF]L'N\.6P/G.QP,`8*2H%@FE<^ D$$D&8R$
M`9 Q70B^5B-R"24_G=W %QRX'K?<Q[IX8#K-SB1O. \?M7!2#G-T!@-EB'90
M>PM$:WY(@8(,A'2*=V5GC &.CVUB@'!"&%8&!(:2HR$D`ZVAHG$-I+.D<0=Z
;4 "5NZX:)+>RNP*M@6E27[V2"[3*S<X,"0`[
`
end