SQLTeam.com | Weblogs | Forums

Large Log File


#1

Hi,

I have a database with the following mdf & log files

mdf: 410 GB (RESJunMaster.mdf)
log: 354 GB (RESJunMaster_log.ldf)

I would like to bring to your attention that the above database was build as a result of large insert statements. Millions of records at one time.

This database is static and is not expected to grow again in future. I have a large log file that is holding considerable amount of space.

Is there any way I can regain this space safely without causing any problem to the database. Considering this database will not have any other data inserted to the tables i.e., static.

Thank you for your response..


TempDB
#2

are you performing regular log backups?


#3

If the data is static and not changing make sure your recovery model is set to SIMPLE mode. For backups you don't need incremental backups for static data. You can shrink the log file then but make sure you have a safe working backup at hand


#4

I set the database to SIMPLE mode - but as the large insert statements executed that took hours , the log file kept growing and growing..

I also do not perform regular log files back up.

So how do u advise me to go ahead please..

Thank you


#5

Have you just set it to SIMPLE?

If the db is static and acting as an archive db and you don't need point in time recovery shrink the log file to 1MB.

Make sure you have a full working db backup before proceeding.

-- make sure you connect to correct db
USE YOUR DB
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (yourlog_Log, 1);
GO

You can also shrink the db to pull back space from the end of the file to usable space at the beginning of the file, although I would not recommend anything like that at this point.


#6

dr223, even with the database set to SIMPLE mode, transactions are logged. After the insert (and database backup) if the log file does not reduce then you could do the shrink.

Also if you will be adding data to the table periodically, you will need to keep an eye on it.


#7

Will this database have index rebuilds or index reorgs on it? If so, those require log space. So you need to keep this in mind when shrinking the log file. For a SIMPLE recovery model database, I would recommend the log file size to be the size of the largest index at a minimum.