Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Shrink log file Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 7:32 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:50 AM
Points: 743, Visits: 1,542
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?



Post #1443256
Posted Wednesday, April 17, 2013 8:09 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
shrinking the log - Bad option.

file will grow again to the size it required.

Is it that space crunch?


Regards
Durai Nagarajan
Post #1443282
Posted Wednesday, April 17, 2013 8:14 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:50 AM
Points: 743, Visits: 1,542
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.



Post #1443284
Posted Wednesday, April 17, 2013 8:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
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
Post #1443302
Posted Wednesday, April 17, 2013 8:40 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:50 AM
Points: 743, Visits: 1,542
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



Post #1443307
Posted Wednesday, April 17, 2013 8:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, May 30, 2014 8:15 AM
Points: 1,056, Visits: 2,687
yes but DBCC SHRINKFILE (1) will shrink the data file - avoid this


Regards
Durai Nagarajan
Post #1443315
Posted Wednesday, April 17, 2013 8:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:10 AM
Points: 1,155, Visits: 4,624
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
Post #1443316
Posted Wednesday, April 17, 2013 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 5,230, Visits: 9,457
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
Post #1443321
Posted Wednesday, April 17, 2013 8:58 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:50 AM
Points: 743, Visits: 1,542

exactly. I would like to reclaim some of the space and then schedule regular Tlog backups. That is the plan. thank you!!



Post #1443324
Posted Wednesday, April 17, 2013 9:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:10 AM
Points: 1,155, Visits: 4,624
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

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1443328
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse