Big transaction log file
in [Server Configuration]
|
Prev: SQL Server 2005 sp3 Query Execution Issue
Next: Difference between SQL 2008 Server Express and SQL 2008 R2 Express
From: fniles on 24 Apr 2010 11:21 I moved a SQL 2000 database to SQL 2005, changed some tables and re-indexed them. The Recovery Model is Simple. Data is about 13 gig in size, and the log file is about 16 gig in size. When I do "DBCC SQLPERF(LOGSPACE)" it shows that very little of the transaction log file is being used: Database Name Log Size (MB) Log Space Used (%) Status quotes 15618.68 0.3062864 0 I just did a full backup on the database. Don't the full backup on a Simple database usually truncate the transaction log file ? Shall I truncate the transaction log file manually ? Thank you
From: Dan Guzman on 24 Apr 2010 12:21 > I just did a full backup on the database. Don't the full backup on a > Simple database usually truncate the transaction log file ? Yes, SIMPLE recovery will remove committed transactions from the log up to the oldest uncommitted transaction. But be aware that a truncate does not release log file space back to the OS; truncate just marks the space is as available. > Shall I truncate the transaction log file manually ? You can reduce the file space with DBCC SHRINKFILE. For example, the following command will reduce the size to 1GB: DBCC SHRINKFILE('log_file_name', 1024); However, you should maintain sufficient log space for ongoing operation. Another reindex will only cause the log file to grow again so there is no value in shrinking the file in that case. -- Hope this helps. Dan Guzman SQL Server MVP http://weblogs.sqlteam.com/dang/ "fniles" <fniles(a)pfmail.com> wrote in message news:O4et3G84KHA.1424(a)TK2MSFTNGP04.phx.gbl... > I moved a SQL 2000 database to SQL 2005, changed some tables and > re-indexed them. > The Recovery Model is Simple. > Data is about 13 gig in size, and the log file is about 16 gig in size. > > When I do "DBCC SQLPERF(LOGSPACE)" it shows that very little of the > transaction log file is being used: > Database Name Log Size (MB) Log Space Used (%) Status > quotes 15618.68 0.3062864 0 > > I just did a full backup on the database. Don't the full backup on a > Simple database usually truncate the transaction log file ? > Shall I truncate the transaction log file manually ? > > Thank you >
From: Manpreet Singh on 27 Apr 2010 08:12
Hi, You need to reorganize pages in log file before shrinking it or otherwise SQL server wont shrink it .taking backup truncate all the committed transactions but some active transaction may remains at end of transaction log (data pages), making SQL server not to release the free space. USE [DatabaseName] GO DBCC SHRINKFILE (N'DatabaseName' , 10) GO The following command will reorganize the pages and then truncate the transaction log to 10 MB Manpreet Singh http://crazysql.wordpress.com/ MCITP (DBA, Developer) SQL Server 2005 MCTS (MOSS 2007), ITILv3F "fniles" wrote: > I moved a SQL 2000 database to SQL 2005, changed some tables and re-indexed > them. > The Recovery Model is Simple. > Data is about 13 gig in size, and the log file is about 16 gig in size. > > When I do "DBCC SQLPERF(LOGSPACE)" it shows that very little of the > transaction log file is being used: > Database Name Log Size (MB) Log Space Used (%) Status > quotes 15618.68 0.3062864 > 0 > > I just did a full backup on the database. Don't the full backup on a Simple > database usually truncate the transaction log file ? > Shall I truncate the transaction log file manually ? > > Thank you > > > . > |