June 15, 2005 at 8:13 pm
Recently our transaction logs seem to have grown more quickly than normal (forgive the lack of previous details, I'm new to the company and still working on getting everything documented--there isn't a lot to go on).
This is on our production server (and my first live DB), so I'm hesitant to do anything rash or potentially fatal.
Our transaction logs are backed up daily, in the morning, and hourly throughout the day. I've read that you can look at the log size as well as space used in the Taskpad view, but I only saw 'space used' and disk space, not log space, so I'm not sure if shrinking is the way to go.
Specific questions:
How/where do I view space used vs. log space?
Should the size of transaction logs be equal to the DB? If not, is there a ratio I can apply?
Where can I view past growth rate?
Is it possible the transaction logs aren't being truncated? (DB Recovery is set to Full)
Do I need to truncate them before running DBCC_SHRINKFILE?
Should I use Enterprise Manager (Rt Click and choose Shrink Database)?
How long should I plan on this taking (880MB DB with a 604MB transaction log)? Currently, I'm scheduled to do this during lunchtime tomorrow.
Also, some of the other DB transaction logs seem out of proportion:
37MB DB with an 85MB DB
21MB DB with a 94MB transaction log (this is the tempdb)
In advance, thanks for helping out a newbie!
June 15, 2005 at 10:27 pm
How/where do I view space used vs. log space?
The DBCC SQLPERF (LOGSPACE) command will display the log space and the % used for all databases on the server.
Should the size of transaction logs be equal to the DB? If not, is there a ratio I can apply?
In most cases the logs will be smaller than the database, but there is no single formula for estimating log size in comparison to the database file(s). It depends on a combination of the activity type and level and the frequency of the log backups.
Where can I view past growth rate?
The msdb.backupfile table has a file_size column that indicates the size of the file that is backed up. So if you have some history in your msdb database you can query that table to derive growth trends.
Is it possible the transaction logs aren't being truncated? (DB Recovery is set to Full)
The log is truncated every time a log backup is performed. However, truncation in this sense does not mean that the file is actually shrunk. The log file is divided into multiple Virtual Log Files (VLF) which are written to in a circular fasion. When a backup is performed all VLF's which contain no active transactions are marked as being truncated, which means that they can be overwritten.
Do I need to truncate them before running DBCC_SHRINKFILE?
Possibly, since the shrink operation simply eliminates any truncated VLF's at the end of the physical file, it is possible the the log file may have a lot of free space, but the shrink operation won't actually shrink the file if the VLF at the end of the file contains active transactions.
Should I use Enterprise Manager (Rt Click and choose Shrink Database)?
You can, I prefer to use Query Analyzer for transparency and control.
How long should I plan on this taking (880MB DB with a 604MB transaction log)? Currently, I'm scheduled to do this during lunchtime tomorrow.
That depends on your server and IO subsystem performance and how much space is actually freed up. If I had to guess, I would count on about 20 minutes or so, but that is pretty much a WAG.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
June 16, 2005 at 1:28 am
there is free toll available for shrinking a database
http://www.codeproject.com/database/ShrinkingSQLServerTransLo.asp
My Blog:
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply