Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 05-06-2009
Member
 
Join Date: May 2008
Posts: 859
How to shrink SQL server db log file

I am running a website build for my office internal purpose. I am running SQL server on its background. Now a days the database log files are growing larger and larger. It is creating problems for us while taking data backup. I want to know how to shrink this files on the server?
Reply With Quote
  #2  
Old 05-06-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: How to shrink SQL server db log file

There are some points to that I would like to discuss:

1. Take the complete backup of your database
2. Generally you should take t-log backup for a huge database every 15-30 minutes in order to restrict log files from growing larger
3. However, if you still want then shrink

But according to me, it is better to avoid shrinking.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 05-06-2009
Member
 
Join Date: May 2008
Posts: 685
Re: How to shrink SQL server db log file

Any system admin who know MS SQL server can shrink database if a large number of transactions are taking place. It?s simple and the script to do so is here:

Code:
CREATE TABLE #TDatabases(
DBName nvarchar(128),
DBLogicalName nvarchar(128)
)
INSERT INTO #TDatabases
SELECT db.name DBName, mf.name DBLogicalName
FROM sys.databases db join sys.master_files mf
on db.database_id = mf.database_id
WHERE db.name not in ('master', 'tempdb', 'model', 'msdb', 'distribution') AND type_desc LIKE 'log'

SET NOCOUNT ON
DECLARE @VarDBLogicalName nvarchar(128)
DECLARE @VarDBName nvarchar(128)
DECLARE @VarRowCount int

SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@rowcount
WHILE @VarRowCount <> 0
BEGIN
EXEC('use' + @VarDBName + 'backup log'+ @VarDBName + 'with no_log
dbcc shrinkfile(''' + @VarDBLogicalName + ''', TRUNCATEONLY) WITH NO_INFOMSGS')
DELETE
FROM #TDatabases
WHERE DBName = @VarDBName
SELECT top 1 @VarDBName = DBName, @VarDBLogicalName = DBLogicalName
FROM #TDatabases
SET @VarRowCount = @@ROWCOUNT
END
DROP TABLE #TDatabases
SET NOCOUNT OFF
Reply With Quote
  #4  
Old 05-06-2009
Member
 
Join Date: May 2008
Posts: 2,293
Re: How to shrink SQL server db log file

In previous versions of SQL, it was very difficult to (almost impossible) to shrink log file, howeer it is quiet possible in the later verions as follows:

Basically first import or export the data from the database and then you can shrink it. To import or export data use: "highlight the database->Tasks->Import data" or "highlight the database->Tasks->Export data" command respectively.

Now you can shrink the log file to the desired size lets say 5MB using: "highlight the database->Tasks->Shrink->Files" and then set the file size. Finally click OK to complete.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to shrink SQL server db log file"
Thread Thread Starter Forum Replies Last Post
Setup Apache Web Server and File Server on Windows Rudra.J Guides & Tutorials 1 11-05-2011 09:09 AM
How to shrink .avi file Maranello Windows Software 3 08-08-2009 02:03 PM
How create iso file with Dvd shrink Damodar Windows Software 3 31-07-2009 03:08 PM
Compact or Shrink down excel file Visala Windows Software 3 28-07-2009 05:21 PM
Cannot shrink transaction log file in in sql Deepest BLUE Software Development 3 06-06-2009 01:00 PM


All times are GMT +5.5. The time now is 05:23 AM.