SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 shrink Database files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sent_sara
Constraint Violating Yak Guru

India
370 Posts

Posted - 09/26/2008 :  05:23:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 09/26/2008 :  08:46:51  Show Profile  Reply with Quote
What recovery model your database has? If you are taking log backup frequently ,you shouldn't worry about it.
Go to Top of Page

james_wells
Yak Posting Veteran

55 Posts

Posted - 09/27/2008 :  09:11:26  Show Profile  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000