January 11, 2008 at 1:02 am
Hi,
My Transcaction log is increasing manifold to upto 20GB which is almost twice the size of whole DB. This is threatening to be a possible DISK SPACE CRUNCH scenario on my system in another few months.
I wish to reclaim the precious disk space also without taking my DB offline. I tried with transaction log backup but while it does take the backup doesn't let me reclaim the disk space.
I want to know what are my options w.r.t Disk Space Reclaim.
Please help.
Ankit
January 11, 2008 at 9:18 am
You have to shrink the log file
DBCC SHRINKFILE (N'LogFileLogicalName', 0, TRUNCATEONLY)
GO
January 11, 2008 at 9:20 am
You can also do this via SSMS by right-clicking the database and going to tasks --> shrink --> files
January 11, 2008 at 9:28 am
Note the above is only needed if you want to truncate the log and reclaim space. If not use
DBCC SHRINKFILE (N'LogFileLogicalName', 0) instead, to just shrink the log file.
January 14, 2008 at 2:28 am
Well I wish to reclaim disk space. So I believe I'll go for Enterprise Manager Option.
Can you pinpoint me to any important thing I ned to watchout for in its settings or, apprise me about any side-effects it may cause ?
Thanks for your time.
Ankit
January 14, 2008 at 7:20 am
Well one thing you should watch out for is the use of the truncate command, as this will break your backup chain.
There is really no penalty for shrinking the log becuause you are just returning the log to the original file size.
January 15, 2008 at 7:30 am
Also, don't forget to take a full backup after you shrink the log file.
January 15, 2008 at 8:42 am
Keep in mind that the log can be bigger than the data size. It depends on transaction volume. I could have a 1MB database with 1 table, 1 row, 1 column, and if I update this value every second, my log will be larger than the data.
I'm guessing that you don't have log backups running. You should be running transaction log backups periodically, more often than database backups. I see many people running hourly if not more often.
January 15, 2008 at 9:18 pm
This is a reporting server of mine whereby in a day we feed in a lot of records & rest of the time it is used to fetch data ranging from days, weeks & months.
But considering the solutions provided to me I have recreated the log file for once & scheduled the Transaction Log Backups to 4 times a day.
I believe these measures should keep the log space in control.
Thanks everybody for your inputs. Would revert back if I continue to face problems in this respect.
Ankit
January 16, 2008 at 3:30 am
why do you need the transactional log backup of it is just a reporting server? You can just take a full backup just after you complete the data feed. Change the recovery model to simple.
January 16, 2008 at 6:24 am
Hi
You can change the database mode into simple and then use Back log command or use enterprise manager to shrink the files, if you are using log shipping, then you can change the mode into full once you have truncated hope this will help
January 16, 2008 at 6:50 am
Ankit,
I agree with the other recommendations to put the database in Simple mode and make a backup job that will backup your database when your feeds are completed. The backup in simple recovery mode will truncate your transaction log to the point of the backup of data from the last backup taken.
--
SQL Server 2005 Books Online (September 2007)
http://msdn2.microsoft.com/en-us/library/ms191164.aspx
Backup Under the Simple Recovery Model
The simple recovery model provides the simplest form of backup and restore. Backup is easy to manage because the transaction log is never backed up. However, if there are no log backups, a database can be restored only to the end of the most recent backup of the data. If a failure were to occur, updates that are made after the most recent backup of the data are lost.
Note: Under the simple recovery model, the transaction log is automatically truncated to remove any inactive virtual log files. Truncation usually occurs after each checkpoint but can be delayed under some conditions. For more information, see Transaction Log Truncation.
--
Feel free to use the following script to keep an eye on the space usage, syntax will run against SQL Server 2000 and SQL Server 2005.
-- Author: Damon T. Wilson
-- Creation Date: 13-DEC-2006
--
-- Usage:
-- Report on a single database for Physical File Name, Total Size in MB,
-- Space Used in MB, Space Available in MB.
--
-- Runs on SQL 2000.
-- Runs on SQL 2005.
SELECT name AS NameOfFile,
size/128.0 as TotalSizeInMB,
CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS SpacesUsedInMB,
size/128.0 -CAST(FILEPROPERTY(name, 'SpaceUsed' )AS int)/128.0 AS AvailableSpaceInMB
FROM dbo.SYSFILES
go
--
Hope This Helps
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 16, 2008 at 10:19 pm
With more & more votes coming in for Simple Recovery Mode I'm considering to put it on a trial basis on my test server.
As I'm trying out this mode for the first time I'd appreciate if someone could put forth anything I need to be careful about while setting my recovery model to Simple.
The steps I'm following to put my DB in simple mode is via Enterprise Manager->MyDB->Properties->Options & choosing Simple from drop-down menu with all the default options intact.
I hope going by the number of responses for the simple model it turns out to be exactly serving my purpose.
Thanks for all the inputs
Ankit
January 17, 2008 at 6:30 am
Ankit,
The only thing you need to be careful of when having a database in Simple Recovery is that you are restricted to restoring to your latest database backup whether it be a Full or Incremental database backup.
For example, you have decided on taking a Full database backup at Midnight, and taking an Incremental backup every hour on the hour. If you had a database crash at 5:15:43 AM, you would have to restore your Full backup at Midnight, and apply your Incremental backup taken at 5 AM. Any database activity that occurred between 05:00:01 AM and 05:15:43 AM would be lost.
Hope This Helps
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 17, 2008 at 6:55 am
Ankit,
The only thing you need to be careful of when having a database in Simple Recovery is that you are restricted to restoring to your latest database backup whether it be a Full or Incremental database backup.
For example, you have decided on taking a Full database backup at Midnight, and taking an Incremental backup every hour on the hour. If you had a database crash at 5:15:43 AM, you would have to restore your Full backup at Midnight, and apply your Incremental backups at 1 AM, 2 AM, 3 AM, 4 AM, and 5 AM. Any database activity that occurred between 05:00:01 AM and 05:15:43 AM would be lost.
You would only need to restore the last full backup and the newest differential backup. The newest differential backup contains ALL the changes since the last full backup.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply