Can logs get very big in the simple recovery database?

Question

Can logs get very big in the simple recovery database?

Krasavita

SSC-Insane

Points: 23138
More actions
May 5, 2010 at 11:18 am

#221285

I have database in express SQL 2008 in th simple recovery mode, can logs grow in this db? If yes, how can I protected?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jack_bab-959705 Grasshopper Points: 12 More actions · Answer 1

Krasavita (5/5/2010)
I have database in express SQL 2008 in th simple recovery mode, can logs grow in this db? If yes, how can I protected?

hi,

How to Shrink

dbcc sqlperf(logspace)

go

sp_helpdb

-- I

BACKUP LOG AdActivity_prod WITH NO_LOG

go

-- II

DBCC SHRINKDATABASE (AdActivity_prod, 10 )

go

if still you face higher usage try using

-- backup log tempdb WITH NO_LOG

-- dbcc shrinkfile (templog,0)

-- dbcc shrinkfile (tempdev,0)

The above code(backup log tempdb WITH NO_LOG) will work for Sql 2008

How to Protect

Move the tempdb database to a different disk location.

Make use of the script attached below :

-- Determine the logical file names of the tempdb database and their current location on the disk.

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

GO

--Change the location of each file by using ALTER DATABASE.

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = 'F:\MSSQL\tempdb.mdf');

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, FILENAME = 'F:\MSSQL\templog.ldf');

GO

--Stop and restart the instance of SQL Server.

--Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N'tempdb');

--Delete the tempdb.mdf and templog.ldf files from the original location

Hope this helps;

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

I am littele confused,but at this time I have 5474.63 MB for temp if I re start sql server instanse, I thought temp db re creates again and size would be down, right?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

It would be nice if you were more descriptive in asking your question.

Yes, a database using the SIMPLE recovery model can still have its log file grow. it is dependent on how much data is being inserted.updated/deleted in a single transaction and how many concurrent transactions are running.

If the database is using the SIMPLE recovery model, BACKUP LOG will not work, even if you are doing TRUNCATE_ONLY. Also, realize that that is being depreciated and may not be available in future versions of SQL Server (talking about BACKUP LOG WITH TRUNCATE_ONLY, not BACKUP LOG itself).

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 4

Lynn Pettis (5/5/2010)
If the database is using the SIMPLE recovery model, BACKUP LOG will not work, even if you are doing TRUNCATE_ONLY. Also, realize that that is being depreciated and may not be available in future versions of SQL Server (talking about BACKUP LOG WITH TRUNCATE_ONLY, not BACKUP LOG itself).

Since this is SQL Server 2008 forum - you should know that the above command will not work. It is not being deprecated - it has been deprecated in this version 😉

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs