April 17, 2013 at 7:32 am
I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)
The database had been set to Full recovery mode but there were no regularly scheduled TLOG backups running.
I'd like to reclaim some of the space back to the OS from the log file. I ran a couple of TLOG backups and a full backup. Now I'd like to shrink the logs. How would I go about doing that?
April 17, 2013 at 8:09 am
shrinking the log - Bad option.
file will grow again to the size it required.
Is it that space crunch?
Regards
Durai Nagarajan
April 17, 2013 at 8:14 am
yes, there is a space crunch. The other reason I wanted to shrink was because there were no Tlog backups running, there is a ton of free space in the log. when I run DBCC SQLPERF(logspace) - it is showing that I am only actually using 5% of the 17GB in these log files.
I wanted to shrink and then schedule regular log backups.
April 17, 2013 at 8:32 am
Truncate log will break the backup chain.
in order to create a new chain you have to start with a full backup and then proceeded by log backups.
do remember once the file grown to some size it wont release the space automatically to OS.
for shrinking the log
USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
post this take a full backup and then schedule log backup.
try to avoid if you can get more space.
Regards
Durai Nagarajan
April 17, 2013 at 8:40 am
would this also work?
Use [my database]
alter database [my database] set recovery simple
--Backup LOG with NO_LOG
Use [my database]
BACKUP LOG [my database] WITH NO_LOG
--Shrink log files
Use [my database]
DBCC SHRINKFILE (1)
DBCC SHRINKFILE (2)
--Change recovery mode back to FULL
Use [my database]
alter database [my database] set recovery FULL
-- FULL database backup when completed
April 17, 2013 at 8:49 am
yes but DBCC SHRINKFILE (1) will shrink the data file - avoid this
Regards
Durai Nagarajan
April 17, 2013 at 8:49 am
Jpotucek (4/17/2013)
I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)The database had been set to Full recovery mode but there were no regularly scheduled TLOG backups running.
I'd like to reclaim some of the space back to the OS from the log file. I ran a couple of TLOG backups and a full backup. Now I'd like to shrink the logs. How would I go about doing that?
You are not taking a t-log backup then is there any reason to place a DB in full recovery.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 17, 2013 at 8:55 am
It's OK to shrink files if the reason they've grown isn't likely to be repeated. This is indeed the case in your situation - somebody set the database to full recovery but didn't schedule any log backups.
There's no need to change the recovery mode to shrink the logs. Just take a full backup, shrink the logs to the maximum size you think they'ill need, take another full backup, and check that you have transaction log backups scheduled at a suitable frequency.
John
April 17, 2013 at 8:58 am
exactly. I would like to reclaim some of the space and then schedule regular Tlog backups. That is the plan. thank you!!
April 17, 2013 at 9:05 am
Jpotucek (4/17/2013)
exactly. I would like to reclaim some of the space and then schedule regular Tlog backups. That is the plan. thank you!!
Glad to hear this 😀
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 17, 2013 at 12:01 pm
John Mitchell-245523 (4/17/2013)
It's OK to shrink files if the reason they've grown isn't likely to be repeated. This is indeed the case in your situation - somebody set the database to full recovery but didn't schedule any log backups.There's no need to change the recovery mode to shrink the logs. Just take a full backup, shrink the logs to the maximum size you think they'ill need, take another full backup, and check that you have transaction log backups scheduled at a suitable frequency.
John
A full backup will not do anything with the log files. To mark the space usable in the transaction log files - you have to perform a transaction log backup when the database is in full or bulk-logged recovery model.
To get this space back, perform a transaction log backup and then run: DBCC LOGINFO in that database. Look for a 2 in the status column to determine which VLF is still active. That is as far as the log file can be shrunk...
Shrink the file, then do another transaction log backup - repeat until you have shrunk the file to the desired size. Ideally, you want to get rid of the secondary log file as it is not needed.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 17, 2013 at 9:20 pm
Jpotucek (4/17/2013)
I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)
Just a note - a SQL Server database cannot have 2 log files, only one.
I would:
1. change the DB recovery mode to simple;
2. shrink the log file;
3. change the DB recovery mode back to full;
4. run the full backup for it;
5. run the regular tlog backups (every hour, for example) after that;
April 18, 2013 at 1:36 am
A full backup will not do anything with the log files.
I know. The full backup is in case shrinking the logs causes any problem that you might want to back out of. Unlikely, yes, but I'd rather have a backup and not use it than need a backup and not have one.
Just a note - a SQL Server database cannot have 2 log files, only one.
It can have as many as you like. That's not to say it should, though.
change the DB recovery mode to simple
You're the second person that's suggested that. I can't think why you'd want to. Please will you explain?
John
April 18, 2013 at 1:41 am
simon.murin (4/17/2013)
Jpotucek (4/17/2013)
I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)Just a note - a SQL Server database cannot have 2 log files, only one.
Good Catch
I would:
1. change the DB recovery mode to simple;
2. shrink the log file;
3. change the DB recovery mode back to full;
4. run the full backup for it;
5. run the regular tlog backups (every hour, for example) after that;
why do you want to suggest changing recovery model when shrink command works in full recovery?
Regards
Durai Nagarajan
April 18, 2013 at 4:00 am
simon.murin (4/17/2013)
Jpotucek (4/17/2013)
I have a Database (about 1GB) with 2 log files (one at about 4GB and one at about 13 GB)Just a note - a SQL Server database cannot have 2 log files, only one.
You'd think that were true, but it's not. SQL Server databases can have multiple log files, however there's not usually a good reason for doing so. It writes to them sequentially, not striping, so there are no performance gains. The only reason I can think when it might be handy is if you have several small drives and need the space but can't reconfigure the drives.
The first thing to do therefore might be to get rid of one of the log files.
After that, the thing to bear in mind is VLFs. A log file that is shrunk and then allowed to grow by itself can result in far too many VLFs, which can affect performance. See this article by Kimberly Tripp[/url] for some excellent advice.
Duncan
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply