How can i resize log file in FULL Recovery Mode

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

How can i resize log file in FULL Recovery Mode

Author

Topic

lsglavo
Starting Member

2 Posts

Posted - 2011-05-19 : 09:31:31

Hi Guys,
i have a database in SqlServer2005 settled in FULL Recovery Mode.
My database is active in Local Publications (Replication Section) and a datafile size 300m, but it's log file increase and actually is more of 20GB of size.
If i run the following step

USE TEST
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TEST
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (TEST_Log, 300);
GO
BACKUP LOG TEST WITH TRUNCATE_ONLY;
GO
-- Shrink the truncated log file to 300 MB.
DBCC SHRINKFILE (TEST_Log, 300);
GO
-- Reset the database recovery model.
ALTER DATABASE TEST SET RECOVERY FULL;
GO

nothing happen, my log file increase daily and i am not able to truncate/resize it.

Can you help me about this issue?

BR - Luigi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 09:39:54

Are you making regular transaction log backups? If not, you'll never be able to shrink the log file. Changing the recovery model is the wrong way to accomplish what you're trying to do.

lsglavo
Starting Member

2 Posts

Posted - 2011-05-19 : 10:11:51

Ja, in the maintenance plan, i have scheduled a regular backup, and every day i have a big file .bak ( that include also the transaction log backup). This is another reason because my free space on disk increase considerably.

Many thanks - Luigi

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-05-19 : 11:07:10

Rob didn't ask about full backups. He asked about log backups. They are different things. If you are running in full recovery model, you must be running regular log backups. The whole point of full recovery is to be able to take log backups for point-in-time recovery.

Please read through this: [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources