December 4, 2003 at 5:32 pm
Hi all,
How to shrink log file in sql 6.5?
Please advise. Thanks!
Robert
December 5, 2003 at 1:12 pm
Hello Robert,
Here is some doc about that subject (found in BOL):
DBCC {
...
SHRINKDB (database_name [, new_size[, 'MASTEROVERRIDE']])
...
}
---------------------------------------------
SHRINKDB (database_name [, new_size [, 'MASTEROVERRIDE']])
When specified with only the database_name parameter, DBCC SHRINKDB returns the minimum size to which this database can shrink, and it lists all objects and indexes that are preventing you from shrinking it further. You cannot shrink the database to a size smaller than the one returned; use caution and drop or move the database objects until the returned size is acceptable.
When new_size is specified, DBCC SHRINKDB shrinks the size of the specified database to the value, as specified in 2K pages by the new_size parameter. DBCC SHRINKDB may shrink both the data and log portions of the database. To change the size of only the data or the log, shrink the entire database first and then use the ALTER DATABASE statement to increase the size of the data or log portion of the database.
To shrink a user database, the database must be set to single user mode. Use the sp_dboption system stored procedure to set this database option. After setting the database to single-user mode, it is recommended that you dump both the master database and the database you are shrinking, prior to using DBCC SHRINKDB.
To shrink the master or tempdb databases you must start the server in single-user mode (use the sqlservr command-line executable with the -m parameter).
Important The database cannot be shrunk beyond either the size of the model database or to a size that is not a valid increment of allocation units. That is, new_size must be equal to or greater than the minimum size as returned by DBCC SHRINKDB plus any number of 512-byte increments (each of which is 256 2K pages). For example, if DBCC SHRINKDB returned a message that the 'Database can be shrunk to 5376 pages', the database can be shrunk to 5376 (10.5 MB), 5632 (11 MB), 5888 (11.5 MB), and so on. For more information, see the "Shrink a Database" example, later in this section.
After successfully shrinking a database to the desired size, back up both the master database and the database that has been shrunk.
DBCC SHRINKDB is fully logged and recoverable except when used on the master database. The MASTEROVERRIDE clause is required when decreasing the size of the master database. However, use caution if you need to shrink the size of the master database, because recovery could fail if the system fails while shrinking the master database. Back up the master database prior to shrinking it. You must be the system administrator or database owner to execute this statement.
---------------------------------------------
Hope it will help
Best regards,
Carl
December 5, 2003 at 1:34 pm
Wow, is that from v6.5? It's been awhile, but I thought you couldn't shrink those files.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
December 5, 2003 at 3:01 pm
Thank you very much Carl and Steve.
I want to make it clear:
1, When dbcc shrinkdb shows minimun size, it is in MB. Whem specifying new_size, it is in 2k pages.
2, When I specifying a new_size, the server will distribute the allocation for data file and log file automatically.
Thanks again.
Robert
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply