I have a 5 gig database that usually uses about 4 gig for the data and up to 1 gig for the log in SQL 7. I have a backup of a database that I need to reload on a backup system to run some reports off of it since it was from a particular point in time. I set a new database up with size of 4 gig for the data and 1 gig for the log and then load this backup file. The resulting size is 4 gig for the data and 8.5 gig for the log file. sp_spaceall tells me that the log should be no more than 1 gig in size and that I have almost 8 gig free for the db. When I try to shrink the database it leaves it at about 12 gigabytes, but still tells me I have 8 gig free. How can I shrink the size of my log file down (as disk space is a problem on the backup server) to the actual space it uses?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Massive Transaction Log 1
- Thread starter mdr227
- Start date
The problem may be that the active portion of the transaction log is near the end of the log file. SQLS cannot truncate a log lower than this active portion.
This from the FAQ in BOL:
I execute DBCC SHRINKDATABASE to shrink my transaction log files. However, they don't seem to be shrinking. What's going on?
DBCC SHRINKDATABASE shrinks data files on a per-file basis, but shrinks log files as if all the log files existed in one contiguous log pool.
The shrinking of log files is not immediate. The shrinking of log files does not occur until the active portion of the log moves. As updates are performed on the database, the shrink operation occurs at checkpoints or transaction log backups. Each log file is marked with the target_percent for the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file to bring its size as close to the target_percent as possible. Because a log file can be shrunk only to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file even if it is not being used.
Robert Bradley
This from the FAQ in BOL:
I execute DBCC SHRINKDATABASE to shrink my transaction log files. However, they don't seem to be shrinking. What's going on?
DBCC SHRINKDATABASE shrinks data files on a per-file basis, but shrinks log files as if all the log files existed in one contiguous log pool.
The shrinking of log files is not immediate. The shrinking of log files does not occur until the active portion of the log moves. As updates are performed on the database, the shrink operation occurs at checkpoints or transaction log backups. Each log file is marked with the target_percent for the shrink operation. Each subsequent log backup or log truncation attempts to shrink the file to bring its size as close to the target_percent as possible. Because a log file can be shrunk only to a virtual log file boundary, it may not be possible to shrink a log file to a size smaller than the size of a virtual log file even if it is not being used.
Robert Bradley
- Thread starter
- #3
In a normal production environment, the log will get naturally truncated after you've backed it up, and after additional transactions (inserts, updates, deletes) force the active portion to "wrap around" to the beginning of the log file.
Use the command DBCC LOGINFO to show the log entries for the desired database and look for a status of 2 (active); if its at or near the end, that indicates that the active portion is at the end.
Robert Bradley
Use the command DBCC LOGINFO to show the log entries for the desired database and look for a status of 2 (active); if its at or near the end, that indicates that the active portion is at the end.
Robert Bradley
If you really, really need to shrink it now, and don't have a production system, here are steps on how to do it:
Robert Bradley
Robert Bradley
Since we get this question frequently, I've FAQ'd it, though I don't consider myself an expert on the subject. Let me know if you find any flaws in it.
Robert Bradley
Robert Bradley
-
1
- #7
JeremyCionca
MIS
The following is a script that I use to shrink the transaction logs of my databases. I have written the script for each database and put the scripts in a DTS package to run each day. Hope this helps...
/*************************************************************************************
* Name: ShrinkTransLog.sql
* Date: 6/12/00
* Purpose: To reduce a transaction log to 1 MB
* Source: MSDN Newsgroups from 6/9/00
*
* Note: Before running this script, replace "DATABASE" with the correct database name
* of the transaction log that you want to shrink. Also (very important), be
* aware of the actual .mdf and .ldf names within the database. Some of the
* .mdf files may have a "_data" in their name. Others may not.
* Some of the .ldf files may have a "_log" in their name. Others may not.
* Also realize that, within STEPS 2 and 3, replace drive 'e' with the proper
* drive housing the "mssql7" directory
*
**************************************************************************************/
use master
go
-- Step 1: Detach database
exec sp_detach_db 'DATABASE','true'
go
-- Step 2: Rename current log file
exec xp_cmdshell 'del e:\mssql7\data\*.ldf_sql',no_output
go
exec xp_cmdshell 'ren e:\mssql7\data\"DATABASE_log.ldf" "DATABASE_log.ldf_sql"'
go
print "
print '*****************************************************************'
print '* Log file has been renamed with a *.ldf_sql extension. *'
print '*****************************************************************'
print "
-- Step 3: Reattach database and create new log file with size of 1 MB
exec sp_attach_db @dbname = N'DATABASE',
@filename1 = N'e:\mssql7\data\DATABASE_Data.mdf'
go
-- Step 4: Display transaction log info
DBCC SQLPERF (LOGSPACE)
/*
* Note: If all goes well, you way want to delete the *ldf_sql file to free up space.
* Also, restrict the file growth of the transaction log to 2 MB (or whatever you want).
* Do this in the Enterprise Manager, right-click the database, select "Properties",
* and click on the "Transaction Log" tab.
*/
/*************************************************************************************
* Name: ShrinkTransLog.sql
* Date: 6/12/00
* Purpose: To reduce a transaction log to 1 MB
* Source: MSDN Newsgroups from 6/9/00
*
* Note: Before running this script, replace "DATABASE" with the correct database name
* of the transaction log that you want to shrink. Also (very important), be
* aware of the actual .mdf and .ldf names within the database. Some of the
* .mdf files may have a "_data" in their name. Others may not.
* Some of the .ldf files may have a "_log" in their name. Others may not.
* Also realize that, within STEPS 2 and 3, replace drive 'e' with the proper
* drive housing the "mssql7" directory
*
**************************************************************************************/
use master
go
-- Step 1: Detach database
exec sp_detach_db 'DATABASE','true'
go
-- Step 2: Rename current log file
exec xp_cmdshell 'del e:\mssql7\data\*.ldf_sql',no_output
go
exec xp_cmdshell 'ren e:\mssql7\data\"DATABASE_log.ldf" "DATABASE_log.ldf_sql"'
go
print "
print '*****************************************************************'
print '* Log file has been renamed with a *.ldf_sql extension. *'
print '*****************************************************************'
print "
-- Step 3: Reattach database and create new log file with size of 1 MB
exec sp_attach_db @dbname = N'DATABASE',
@filename1 = N'e:\mssql7\data\DATABASE_Data.mdf'
go
-- Step 4: Display transaction log info
DBCC SQLPERF (LOGSPACE)
/*
* Note: If all goes well, you way want to delete the *ldf_sql file to free up space.
* Also, restrict the file growth of the transaction log to 2 MB (or whatever you want).
* Do this in the Enterprise Manager, right-click the database, select "Properties",
* and click on the "Transaction Log" tab.
*/
FYI... Microsoft recognized this as an issue and now has minimized this in SQL Server 2000. With 2000, you no longer have to force the minlsn in the virtual log around to the beginning of the physical file, as Robert's link illustrates.
Jeremy also has a solution that will work in cases where there is no activity occurring at night, assuming you can do away with your transaction log. If you choose this method, make a full database backup first.
If you are operating in a 24x7 environment, you will need most likely want to implement Robert's solution.
Before you attempt either of these solutions, make sure you make a full database backup.
Hope this helps... Tom Davis
tdavis@sark.com
Jeremy also has a solution that will work in cases where there is no activity occurring at night, assuming you can do away with your transaction log. If you choose this method, make a full database backup first.
If you are operating in a 24x7 environment, you will need most likely want to implement Robert's solution.
Before you attempt either of these solutions, make sure you make a full database backup.
Hope this helps... Tom Davis
tdavis@sark.com
Tom's cautions are important: the log files are there for a reason, and they are the size that they are for a reason; circumventing the built-in handling of the sizes in a production OLTP database could yield disastrous consequences if you don't understand the consequences.
Robert Bradley
Robert Bradley
- Status
Similar threads
- Locked
- Question