shrink Database files - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

shrink Database files

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2008-09-26 : 05:23:40

Hi friends,
I need to shrink Database log files in my scheduler.Can any one help me in doing this,

Create Procedure Test
as
begin
set nocount on

use fin_ods
dbcc shrinkfile(2,640)
use fin_wh
dbcc shrinkfile(2,640)
use fin_ods

set nocount of
end

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-26 : 08:46:51

What recovery model your database has? If you are taking log backup frequently ,you shouldn't worry about it.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-27 : 09:11:26

Not sure why you need to shrink your log file becuase as SODEEP has already said normally the regular backup of log file
(FULL recover module) will truncate the inactive portion of the log file.

However sometimes when a backup is performed the log file is not truncated because each virtual log file that make up the physical log file must be completly inactive for truncation to work or maybe someone as specified the WITH NOTRUNCATE on the backup.

This is why regular backups are required.

However if there is a good reason to shrink the log file by need or design then do the following

USE databasename;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE databasename
SET RECOVERY SIMPLE;
GO

-- Shrink the truncated log file to 1 MB.
-- 1 mb is obviously small - just an example
-- therefore insert your own desired size

DBCC SHRINKFILE (databasename_logname, 1);
GO

-- Reset the database recovery model.

ALTER DATABASE databasename
SET RECOVERY FULL;
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources