June 26, 2008 at 10:03 pm
I am concerned about the size of an SQL server database that's to go into production right about now. The mdf file is 2 GB and the log file is over 6 GB.
What am I to do here? I have no experience in administration tasks! What are the implications of this large file size?
June 26, 2008 at 10:06 pm
If the database is in Full recovery mode, you need to take scheduled log backups. If you do not need log backups, change the recorery to Simple.
LOG truncates occurs automatically
· for a simple recovery model database after a checkpoint occurs
· for the FULL and Bulk-logged model after taking the Transaction Log backup, if a checkpoint occurs after the previous backup.
The only way to recover the space after truncation is to run the DBCC Shrinkfile command.
June 26, 2008 at 10:50 pm
Ok.. so first I backup the log file.. then i use dbcc shrinkfile.. but what size do i shrink it to? how much can it come down from 6GB?
EDIT:
Can I directly do a task>Backup>BackupType-Transaction Log?
Or do i need to take a full backup before this?
June 26, 2008 at 11:46 pm
Better take
1)full backup then
2)issue checkpoint
3)shrink datafile using dbcc shrinkfile(mdf_file_name,truncateonly) or use dbcc shrinkdatabase
3)take a transaction log backup
4)issue dbcc shrinkfile(ldf_file_name,10)
you specify how much you want to free up ...give 1Gb first and if it come to 1Gb then try 10 Mb [BigGrin]
June 26, 2008 at 11:58 pm
Nisha (6/26/2008)
I am concerned about the size of an SQL server database that's to go into production right about now. The mdf file is 2 GB and the log file is over 6 GB.What am I to do here? I have no experience in administration tasks! What are the implications of this large file size?
Those aren't large files for SQL. One of my systems has a 600GB mdf file and a 250GB log file. SQL can handle really large files
You do need to investigate why the log is so large compared with the data. Once youve shrunk it, make sure that you have regular log backups running and don't shrink on a regular basis.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply