April 23, 2003 at 8:50 am
I have taken on the administration and support of a sql server DB using sql server 7.0. The DB was not properly sized by the looks of things and the initial space allocated is shown below:
data file - 5500MB (unrestricted growth)
Trans log - 6267MB (unrestricted growth)
As you can see the trans log was given way too much allocated space to begin with.
I want to be able to reduce the space allocated to the log. I have truncated it and it is using 13MB of the approx 6GB allocated to it. The server the DB is running on is running low on space and i would like to reclaim this space by reducing the allocated size of the log. Is there a way of doing this and maybe also reducing the allocated data file size. Currently the data file is using approx 2GB of the 5.5GB allocated.
When i go into DB properties and try to do this it gives me the error saying that the new allocated size must be greater than the current allocation.
Any help is appreciated
April 23, 2003 at 8:56 am
First, I would recommend you take a backup of the Database and the Transaction Log. Then, you should be able to run dbcc shrinkdatabase ('YourDB', Target%) and shrink the database successfully. For fastest results, start with the target % at 50 and work your way down until it is where you think it should be.
With all that said, depending on the processing you are doing, the database may grow to that size again. It got there now and it will probably get there again.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 23, 2003 at 8:59 am
One note, if the log was this big initially, then you can't shrink below the initial size.
That being said, shrink it and see, you may need help and there's a script in this site that helps. The log may not grow this big, I've often seen bad backup plans causing the log to grow unnecessarily.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 23, 2003 at 11:29 am
If the initial file size is so big, you can't shrink it to smaller. If the log file grows so big, you can do the follows:
1, Back up the database,
2, Run dbcc shrinkdatabase(dbname, truncateonly)
3, Change the db option to truncate log on checkpoint for sql7, recovery model to simple
for sql2k.
4, Run shrinkdatabase(sbname) for shrinking
both data and log files, Run shrinkfile(filename) for shrinking the file you want to shrink.
5, Change db option back.
Hope this will help.
Robert
April 23, 2003 at 11:33 am
Forgot to mention. If the initial log file is so big, you don't want it so big, then you can use detach and attach the log file
to reduce it.
Robert
April 23, 2003 at 1:10 pm
try to use ap_attch_single_file_db to get a new log file.
April 23, 2003 at 1:17 pm
try to use sp_attach_single_file_db to get a new log file...
pardon my terrible typing errors(previous reply)
April 24, 2003 at 1:44 am
Cheers for all this info guys i am going to try the suggestions today so i will get back to you with what worked.
April 24, 2003 at 2:35 am
As it didnt matter about the transaction data being lost in this case. I dettached the Database using sp_dettach_db, deleted the log file from within explorer and then ran sp_attach_single_file_db. This created a new log file with an allocated size of 1MB instead of 6GB which it was before. Thanks for all your ideas guys.
Cheers,
Mike
April 24, 2003 at 8:10 am
Ok, here is a Stored Procedure I wrote based on the things in this Post. If someone would analyze this to see if I missed anything I'd appreciate it. Thanks!
Please excuse the formatting, I noticed that this forum does not keep the Indenting I usually code with.
-- Shrink a Database and Log File
CREATE Procedure spShrinkDatabase
(@Database NVarChar(128))
As
Set NoCount On
Declare @DBLog NVarChar(128), @BackUpName NVarChar(128), @BackUpFile NVarChar(255)
If (@Database Is Not Null) And (Exists(Select * From Master.dbo.SysDatabases Where Name = @Database))
BEGIN
--Set @DBLog = @Database + N'_Log'
Set @BackUpName = @Database + N'_BackUp'
-- Determine the BackUp File
Select @BackUpFile = FileName From Master.dbo.SysDatabases Where Name = @Database
Set @BackUpFile = Reverse(@BackUpFile)
Set @BackUpFile = SubString(@BackUpFile, CharIndex('\', @BackUpFile, 0), Len(@BackUpFile))
Set @BackUpFile = Reverse(@BackUpFile) + @BackUpName + N'.bak' --Set the FileName
Print @BackUpFile
Print @BackUpName
-- BackUp the Database
BackUp Database @Database To Disk = @BackUpFile With Init, NoUnload, Name = @BackUpName, NoSkip, Stats = 10, NoFormat
DBCC ShrinkDatabase (@Database, TruncateOnly) -- Shrink the Database
-- Truncate the Log Files
Declare @LogCursor Cursor
Set @LogCursor = Cursor Fast_Forward For Select Name From SysFiles Where FileName Like '%.ldf%'
Open @LogCursor
Fetch Next From @LogCursor Into @DBLog
While (@@Fetch_Status <> -1)
BEGIN
If (@@Fetch_Status <> -2)
BEGIN
DBCC ShrinkFile (@DBLog, 2, TruncateOnly) -- Shrink the Log File
END
END
Close @LogCursor
DeAllocate @LogCursor
--Shrink Database last time
DBCC ShrinkDatabase (@Database)
END
Set NoCount Off
GO
April 24, 2003 at 8:10 am
I normally take a full backup before and after I do this. I am using SQL Server 7 and have a standby read-only DB that I am shipping logs to once an hr from our main Prod DB and applying it. After this process I would need to restore the latest BAK and then set up the application of the trans logs.
Another note: If your DB has lot of activity, best to backup your trans logs once an hr if you want to avoid having it grow to big.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply