试试下面的存储过程。 我可是成功地解决了问题呀。祝好运。 CREATE PROCEDURE sp_ShrinkLogFileSize AS begin ----- SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT --USE smsc_sm_db -- 要操作的数据库名 SELECT @LogicalFileName = 'smsc_sm_db_log', -- 日志文件名 @MaxMinutes = 10, -- Limit on time allowed to wrap log. @NewSize = 20 -- 你想设定的日志文件的大小(M)-- Setup / initialize DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName SELECT 'Original Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName CREATE TABLE DummyTrans (DummyColumn char (8000) not null) DECLARE @Counter INT, @StartTime DATETIME, @TruncLog VARCHAR(255) SELECT @StartTime = GETDATE(), @TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'DBCC SHRINKFILE (@LogicalFileName, @NewSize) EXEC (@TruncLog) -- Wrap the log if necessary. WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) AND (@OriginalSize * 8 /1024) > @NewSize BEGIN -- Outer loop. SELECT @Counter = 0 WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) BEGIN -- update INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans SELECT @Counter = @Counter + 1 END EXEC (@TruncLog) END SELECT 'Final Size of ' + db_name() + ' LOG is ' + CONVERT(VARCHAR(30),size) + ' 8K pages or ' + CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' FROM sysfiles WHERE name = @LogicalFileName DROP TABLE DummyTrans SET NOCOUNT OFF end GO
The target size for data and log files as calculated by DBCC SHRINKDATABASE can never be smaller than the minimum size of a file. The minimum size of a file is the size specified when the file was originally created, or the last explicit size set with a file size changing operation such as ALTER DATABASE with the MODIFY FILE option or DBCC SHRINKFILE. For example, if all the data and log files of mydb were specified to be 10 MB at the time CREATE DATABASE was executed, the minimum size of each file is 10 MB. DBCC SHRINKDATABASE cannot shrink any of the files smaller than 10 MB. If one of the files is explicitly grown to a size of 20 MB by using ALTER DATABASE with the MODIFY FILE option, the new minimum size of the file is 20 MB. To shrink a file to a size smaller than its minimum size, use DBCC SHRINKFILE and specify the new size. Executing DBCC SHRINKFILE changes the minimum file size to the new size specified.Use DBCC SHRINKFILE to reduce the size of a file to smaller than its originally created size. The minimum file size for the file is then reset to the newly specified size. IronPromises(铁诺) ,这是我在msdn上找到的资料。SHRINKDATABASE不能把数据库缩小到比初始时还小,SHRINKFILE却可以。 认错,认错。 CRYSTAL:就用 DBCC SHRINKFILE("数据库名称",大小),大小不小于当前数据大小。
怎么又写错了,应该是 DBCC SHRINKFILE("数据库文件名(带路径)",大小)
很简单,将日志设置成auto shrink,truncate log on checkpoint,在数据库属性中. 使用dbcc设置checkpoint即可.
我刚刚用了管理器里面的shunk database,它显示我的库可用空间是2560M,实际用的是235M,可是点确定之后,并没有缩小库文件的大小。
该怎么办?是不是非要删库呢?
USE DBNAME
GO
DBCC SHRINKFILE (filename,10)
GO
我已经成功缩减了各个数据文件的大小,但是这个语句似乎对日志文件没用作用。
select name,filename from sysfiles找到物理文件在数据库中的name然后根据这个name执行DBCC SHRINKFILE (name, 20) -- 压缩成20MB对日志文件也是有效的.
backup log yourDBName with no_log;再執行dbcc SHRINKFILE (name, 指定大小)
竹版主说的是对的.你说错了.不信可以自己试一下.而且压缩后的大小不能小于目前数据的大小.
我可是成功地解决了问题呀。祝好运。
CREATE PROCEDURE sp_ShrinkLogFileSize AS begin
-----
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
--USE smsc_sm_db -- 要操作的数据库名
SELECT @LogicalFileName = 'smsc_sm_db_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 20 -- 你想设定的日志文件的大小(M)-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF end
GO
exec sp_detach_db yourDBName,true;2. 将.LDF文件从data目录中删掉或者移走.3.以单文件形式挂上DB,SQL Server会自动给你一个504KB的日志文件
exec sp_attach_single_file_db
yourDBName,
’d:\mssql7\data\yourDBName_data.MDF’
做这些操作前最好先备份以防不测.
IronPromises(铁诺) ,这是我在msdn上找到的资料。SHRINKDATABASE不能把数据库缩小到比初始时还小,SHRINKFILE却可以。
认错,认错。
CRYSTAL:就用 DBCC SHRINKFILE("数据库名称",大小),大小不小于当前数据大小。
DBCC SHRINKFILE("数据库文件名(带路径)",大小)
使用dbcc设置checkpoint即可.