Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pvsramu
Starting Member
17 Posts |
 Posted - 2007-01-23 : 12:14:14
|
| Hi,Data file size is apprx 3 GB whereas log file size is 25 GB. I could not shrink the log file below 25 gb because it was set as min size in database properties. When I did DBCC LOGINFO('dbname'), it produced about 400 rows. It looks like log file has been incremented many times and grown upto 25 GB. I wanted to keep the log size as 25 GB instead of shrinking it. But I am little concerned about the number of rows LOGINFO command it produces. I am thinking to do this in order to reduce the VLFs in log file.step 1: Take complete database backupstep 2: Detach the databasestep 3: Delete the log filestep 4: Attach the database. While attaching it will ask to create a new log because original log will not exist.step 5: Increase the log size to 25 GB.Do you see any problems doing like this?Thanks,Ramu |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-23 : 13:19:51
|
| That will work. More importantly, are you regularly backing up? When you take a full backup, and more importantly, when you take log backups between full backups, the log will be truncated, which may keep it much smaller than the 25GB you are using now. |
 |
|
|
pvsramu
Starting Member
17 Posts |
Posted - 2007-01-23 : 14:07:39
|
| While defining the database, the size of data file is 4 GB and log file size is 25 GB. Yes, we are doing regular full backup and log back. Actual data in data file is 3 GB and used-space in log file is 1 GB (24 GB is free space). The thing is, initially we have created the log file size as 2 GB(I guess) and over period of time it has grown because autogrow has been set on database (I guess). Right now when I do DBCC LOGFILE, I get about 400 VLFs. My question is, is this Right way to reduce VLFs in the log file? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-23 : 16:06:20
|
| I don't know if I'd say it's the right way - it depends if you can afford to take the database offline. If you want to do it with the database online then you can do it with shrinkfile and a backup, seehttp://support.microsoft.com/?id=272318As you already said, you should grow it by a large amount after shrinking or detaching and attaching so that it doesn't autogrow in small increments again (the cause of the large number of VLFs). You probably don't need to make it 25GB though, try something smaller than that but larger than the autogrowth amount, which is clearly too small. |
|
|
|
|
|
|
|
|
shrink question