SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Shrink log file


Shrink log file

Author
Message
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4486 Visits: 1680
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?



durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3587 Visits: 2784
shrinking the log - Bad option.

file will grow again to the size it required.

Is it that space crunch?

Regards
Durai Nagarajan
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4486 Visits: 1680
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.



durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3587 Visits: 2784
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
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4486 Visits: 1680
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



durai nagarajan
durai nagarajan
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3587 Visits: 2784
yes but DBCC SHRINKFILE (1) will shrink the data file - avoid this

Regards
Durai Nagarajan
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4667 Visits: 4969
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

Helping SQL DBAs and Developers >>>SqlserverBlogForum

John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35622 Visits: 16695
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
Jpotucek
Jpotucek
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4486 Visits: 1680
exactly. I would like to reclaim some of the space and then schedule regular Tlog backups. That is the plan. thank you!!



muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4667 Visits: 4969
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 :-D

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search