May 5, 2010 at 11:18 am
I have database in express SQL 2008 in th simple recovery mode, can logs grow in this db? If yes, how can I protected?
May 5, 2010 at 11:42 am
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;
May 5, 2010 at 12:47 pm
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?
May 5, 2010 at 12:57 pm
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).
May 5, 2010 at 3:01 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply