January 23, 2007 at 10:23 am
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 backup
step 2: Detach the database
step 3: Delete the log file
step 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
January 23, 2007 at 2:55 pm
You don't need detach it..
What is the recovery model of the database?
What caused the db log file to grow 25 GB? Are you taking regular tlog backups of this db?
Why you want to be restrictive with shrink of database files
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
MohammedU
Microsoft SQL Server MVP
January 23, 2007 at 9:58 pm
I find a bit of contradiction here. What version of SQL server you are using? In SQL 2000 sp3 it shrinks below the initial size fine for both log and data file. If it doesn't just switch the DB into simple recovery mode shrink the log to 1MB, switch the DB back into full mode and increase the log file size to whatever size you want. After all this backup your database. This approach is less invasive and less dangerous IMHO than attaching a DB without a log file.
January 24, 2007 at 6:12 am
why and how do you get a 25gb log with a 2gb database, or are the figures a typo?
removing the log with detach is very dangerous and may leave you with a suspect database. Switching modes is a good way to reduce a log size, but remember to do a full or incremental backup after switching back to full recovery otherwise you will not be able to recover any logs backups.
If you don't need tran log backups put the database to simple recovery and that should help keep the log size in check.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 24, 2007 at 7:26 am
Are you sure your backing up the log daily. I came into a job where they had all databases in full recovery mode, yet were only taking full backups. The log files were 20 - 60 gig in the various databases. I backed up the transaction log with the backup log command and then did a shrink on the log file. That cleaned things up. I would run dbcc sqlperf (logspace) to see if the used percent in the log file, then backup the logs and do a shrink on the logfile. Post the results from the dbcc and we can advise you.
Tom
January 24, 2007 at 9:47 am
It sounds like your database is in FULL recovery mode. (Enterprise Manager, right click on the database and select Properties. Go to Options Tab. That's where you will see the recovery mode).
With FULL recovery mode, you MUST not only do full backups, but also transaction log backups.
With SIMPLE mode, you only need to do full backups.
You are doing backups aren't you?
If you don't need to recover up to a specific point-in-time and you don't mind losing some data, put the db into Simple mode. Do a full backup and then run the DBCC SHRINKFILE command against the log file. To find the file name, look in Enterprise Manager, go back to the Properties for the database, go to the Log tab and there you will see the actual file name.
If you need to be able to recover up to a specific point-in-time, put the db into FULL mode and do full backups AND transaction log backups.
-SQLBill
January 24, 2007 at 5:09 pm
Hi,
Thanks for all your responses. I understand that its best to change the recovery mode to simple, shrink the log file to reasonable size, change the recovery mode back to full.
We are using 2000 version. The thing is, I started a new job where they have setup the database properties as autogrow. They were not really worried about the performance issues. Over a period of about 2 yrs, the log has grown and went upto 25 GB. They are taking full backup and log backup every day. But there might have happened certain operations (like reindexing, heavy transactions, etc.,) happenedwhere the log might have caught upto that level. I did DBCC LOGINFO(), the status is 2 for most of the rows. Initially I thought of detaching and attaching the databases but thats not the right way of doing. Now probably I will work on changing the recovery mode and shrink the file and change the recovery mode back to full.
Thanks for all your inputs.
Regards,
Ramu
January 25, 2007 at 7:32 am
If you don't allow the log files to grow then instead of performance issues you have to worry about the application-crashing-because-the-log-file-is-full issues.
You have to get the log growth under control before you start limiting it. You say they do tlog backups every day, maybe they need to do them more frequently. Some systems do them hourly, some every 15 minutes or even every 5. It depends on the activity on your system.
January 25, 2007 at 11:28 am
Reindexing requires the log file to grow (I believe) 2.5 times the size of the database (it might be 1.5, but I think it's 2.5). It needs that amount of space in case the reindexing fails and it has to roll it back.
-SQLBill
January 25, 2007 at 5:25 pm
Almost 1.5 times of th table size not db size...
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspxThe
amount of free space needed varies and is dependent on the number of indexes being created in the transaction. For clustered indexes, a good guideline is: Required free space = 1.2 * (average rowsize) * (number of rows).
For nonclustered indexes, you can predict free space necessary by calculating the average row size of each row in the nonclustered index (length of the nonclustered key plus the length of clustering key or row ID). Then multiply that value by the number of rows. If you rebuild indexes for an entire table, you will need enough free space to build the clustered index and all nonclustered indexes. Similarly, if you rebuild a nonunique clustered index, you will also need free space for both the clustered and any nonclustered indexes. The nonclustered indexes are implicitly rebuilt because SQL Server must generate new unique identifiers for the rows. When you use DBCC DBREINDEX, it is good practice to specify the index you want to defragment. This
MohammedU
Microsoft SQL Server MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply