Shrinking the database...
-
You also can try to shrink the individual tempdb files
Go to the shrink database window
At the button, press files, the another window is opened where you can select one of the 2 files (tempdev, log) to shrink.
Select the radio button shrink to and fill in the quired size. do this for both files. I was be able to shrink the tempdb log file from 1000 MB to 250 MB
-
Newbie,I apreciate youtr willingness to think with me on solving the problem, but would ou read my initial mail, please?
Greetz,
Hans Brouwer -
Well, I did it. I stopped MSSQLSERVER and started it again. Tempdb has now shrunk to 8 Mb...
It doesnot explain why I could not shrink the db, it confirms the ultimate solution I had in mind.
I'm still wondering what can cause such behaviour. No, I can think of several reasons why, it's just I can't think of anything preventing me from shrinking given the activity at that moment.
I'll monitor this dbserver closely for a while and see if anything remarkable shows.
Tnx for respoinding all.
Greetz,
Hans Brouwer -
After the BACKUP LOG 'databasename' WITH NO_LOG statement, issue a SHRINK. This almost certainly works.
Truncating the transaction log:
BACKUP LOG { database_name | @database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
Explanation:
NO_LOG | TRUNCATE_ONLY
Removes the inactive part of the log without making a backup copy of it and truncates the log. This option frees space. Specifying a backup device is unnecessary because the log backup is not saved. NO_LOG and TRUNCATE_ONLY are synonyms.
After backing up the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE.
-
(I can take no credit for it, but I'm sure I found it on sqlservercentral)
try this...
if objectproperty (object_id('Sp_Shrinklog_Now'),'isProcedure') = 1 drop procedure Sp_Shrinklog_Now
GO
Create Procedure Sp_Shrinklog_Now @databaseName sysname, @duration Int = 60
As
Begin
Declare @Sql Varchar(8000)
Declare @IsBulkCopy Int, @IsTruncLog Int, @i int, @sec Varchar(10), @SecAvantFinale Varchar(10)
Set @sec = Str(@Duration)
Set @SecAvantFinale = Str(Case When @Duration > 10 Then @Duration - 10 Else 0 End)
Set @IsTruncLog = Databaseproperty (@databaseName, 'IsTruncLog')
If @IsTruncLog = 0 Execute sp_dboption @databaseName, 'trunc. log on chkpt.', true
Select @Sql =
'
Set nocount on
Declare @Fileid varchar (10), @db sysname, @i int, @d datetime
Set @db = db_name()
select @FileId = Str(FileId) from sysfiles where fileproperty(name, ''IsLogFile'')=1
checkpoint
Print ''Attemp to shrink the log of ''+ @db
Execute (''Dbcc shrinkfile (''+@FileId+'')'')
if objectproperty (object_id(''PseudoActivity''),''istable'') = 1 drop table dbo.PseudoActivity
Create table dbo.PseudoActivity (T char(1000), C char(1000))
set @i = 0
Set @d = getdate()
while (1=1)
Begin
If datediff (ss, @d, getdate()) < '+@SecAvantFinale+'
insert into dbo.PseudoActivity (t, c)
select top 1000 TABLE_NAME ,COLUMN_NAME
from Information_schema.columns
Else
insert into dbo.PseudoActivity (t, c)
select top 50 TABLE_NAME ,COLUMN_NAME
from Information_schema.columns
delete from PseudoActivity
Waitfor delay ''00:00:01'' -- wait a sec
set @i = @i + 1
If @i % 5 = 0
begin
Print ''Attemp to shrink the log of ''+ @db
Execute (''Dbcc shrinkfile (''+@FileId+'')'')
checkpoint
backup log @db with truncate_only
end
If datediff (ss, @d, getdate()) > '+@Sec+' Break -- If duration exceeded ?, stop
End
Print ''Last attemp to shrink the log of ''+ @db
Execute (''Dbcc shrinkfile (''+@FileId+'')'')
checkpoint
backup log @db with truncate_only
drop table dbo.PseudoActivity
'
Execute (@Sql)
If @@Error > 0 Print @Sql
If @IsTruncLog = 0 Execute sp_dboption @databaseName, 'trunc. log on chkpt.', False
Print 'Do a total backup on '+@databaseName+' !! Partials backups won''t be good until it is done '
End
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply