pae2
asked on
shrink log file sql server 2008
I have a log file that is ~8GB large. But it consistently has only 1% of data, as described by DBCC SQLPERF(LOGSPACE); this means, there's a lot of unused space. The db employs the full recovery model. How would I reduce the log to 1,024MB or 1GB via TSQL? I've seen code for this. But I want to see how others go about this.
Other than performing a full-backup of the db right before shrinking the file, are there any other steps to take? Should I do this during off-hours to reduce performance impact?
Are there any other things for me to keep in mind?
Thanks!
pae2
Other than performing a full-backup of the db right before shrinking the file, are there any other steps to take? Should I do this during off-hours to reduce performance impact?
Are there any other things for me to keep in mind?
Thanks!
pae2
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
When you switch a db from SIMPLE to FULL recover mode, SQL requires a full backup to create a valid recovery point for the db.
From Books Online (I added the bold, that parts' not in BOL :-) ):
"
If you must switch from the simple recovery model to the full recovery model, we recommend that you:
Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
Schedule regular log backups and update your restore plan accordingly.
Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
"
From Books Online (I added the bold, that parts' not in BOL :-) ):
"
If you must switch from the simple recovery model to the full recovery model, we recommend that you:
Immediately after you complete the switch to the full recovery model or bulk-logged recovery model, take a full or differential database backup to start the log chain.
The switch to the full or bulk-logged recovery model takes effect only after the first data backup.
Schedule regular log backups and update your restore plan accordingly.
Log backups are an integral and fundamental aspect of the full and bulk-logged recovery models. Log backups allow the transaction log to be truncated. If you do not back up the log frequently enough, the transaction log can expand until it runs out of disk space.
"
ASKER
Excellent - that's very good to know. Thank you Sir!
ASKER