Reducing Log File size below initial size
-
I made the mistake of creating a 120Gb database without specifying a log file size, not realising that SQL Server would create the log file at 25% of the total database size. This has resulted in a log file of 28Gb of which only 2Mb has been used! The data loaded into this database is static and will not be updated. Therefore the log file does not need to be more than 5 or 10Mb.
I understand truncating the log will only reduce it back to its initial size. I've read some stuff (here and on MSDN) about the Shrink command, but I'm not 100% sure if this will reduce the log file size below its initial size. Please can someone clarify this for me?
-
I would
Backup log xxx with truncate_only
Then use DBCC SHRINKFILE and specify the size that you want. This will shrink the physical log file.
Then go into enterprise manager and change your settings on % increas, max etc.
------------------------------
The Users are always right - when I'm not wrong! -
Thanks for the reply. I set up another database to test this out on. The database is approx 20Gb and I left SQL Server to create a log file which is 650Mb. I haven't loaded any data into this database.
I've tried various commands in both Enterprise Manager and Query Analyzer without success. Enterprise Manger tells me the database has been shrunk but the logfile is still 650Mb.
I ran the BACKUP and SHRINKFILE commands as you suggested. After QA had thought about it for a while, it came back with a message saying "Cannot shrink log file 2 (logtest_log) because total number of logical log files cannot be fewer than 2. (1 row(s) affected)". Any ideas?
-
I am as surprised as you are. I would suggest reading the chapter "Shrinking the Transaction Log" in BOL. I think you need to investigate the virtual log concept. Sorry I cannot be any more of a help. DBA skills not really my area.
------------------------------
The Users are always right - when I'm not wrong! -
DBA skills aren't my area either as you can probably tell by the mistake I made with the log files in the first place!
I'll check that chapter out in more detail. I'll post back here if I get anywhere (or not!).
-
What you can try (may not be the BEST idea) is:
DETACH the database, RENAME the log file, ATTACH the database and then try and shrink the LOG file (or try it during the "cant find log file")
Good Hunting!AJ Ahrens
webmaster@kritter.net -
AJ may have a good idea. Detach a database.
Then in Query Analyzer run EXEC sp_attach_single_file_db with parameters like here below. You will specify ONLY a database file name and it will create a log for you (it would be nice to know what size
)It may work if the database originally had only 1 DB file and one log file
Please, see the article sp_attach_single_file_db in Books Online. It has examples.
sp_attach_single_file_db [ @dbname = ] 'dbname'
, [ @physname = ] 'physical_name'
Arguments
[@dbname =] 'dbname'
Is the name of the database to be attached to the server. dbname is sysname, with a default of NULL.
[@physname =] 'phsyical_name'
Is the physical name, including path, of the database file. physical_name is nvarchar(260), with a default of NULL.
Regards,Yelena Varsha
-
The above suggesstion is probably the best solution. below is a small explaination.
The transaction log is divided into virtual log files. When you truncate a log the virtual log files are marked as inactive. A subsequent log shrink operation will truncate the virtual log extents. Hope this explains that when you shrink the log, the virtual log extents are not shrinked but will only truncate the ones which are empty. Hence you cannot shrink the log file below the virtual log boundary.
-
You can see the virtual log files within your log by running
dbcc loginfo
A log must have at least 2 virtual log files, and when SQL creates a log, it creates it with a minimum number of virtual files. So, in your example of a 650MB log file, its most likely made up of two virtual logs, 325MB each. This is where you're going to run into a problem with the sp_attach_single_file_db procedure. Since you can't specify a log size, SQL's probably going to create one that is approximately the same size as before, and with 2 virtual log files.
Based on your description, I'm assuming the database has about 120GB of data in it already? If not, you could shrink the data file first, then detach and run sp_attach_single_file_db to create a smaller log file. Otherwise, if you could copy out a large portion of the data, shrink, sp_attach..., then expand, and finally copy the data back in.
Steve
-
AJ definitely has the right idea
I did exactly this the other day to reduce a 10gig logfile down to 18megs
Use SP_detach_db and then reattach just the mdf file using sp_attach_single_file_db
Backup (full), checkpoint and truncate the log first..
Cheers
James
-
Thanks for all the responses.
I found the loginfo command but I didn't really understand the info it returned. I worked out there were two virtual logs because it returned two records, and the FileSize and StartOffset are fairly obvious, but I don't know what the rest are (FSeqNo, Status, Parity, CreateLSN).
I'll try the detach and re-attach route with my test database and see what happens. The live database is using about 102Gb with 9Gb free. The data is a single aerial photography layer so it probably won't be possible to export part of it. I guess shrinking 9Gb off the database size won't make that much difference to the log file.
We're using SQL Server as a GIS (Geographical Information System) data server with ESRI's ArcSDE running on top of it (http://www.esri.com/arcsde). The databases and filegroups are still created using standard SQL thought which is why I didn't mention it before.
-
I can confirm that the detach/re-attach method works. I detached the database in Enterprise Manager (EM), renamed the logfile, then re-attached the just the MDF using the SP in Query Analyzer(QA). SQL Server moaned about the logfile and created a new one 504Kb on disk (although EM said it was 1Mb). I used EM to set the growth factor for the new log file.
I had several filegroups on the database and it's picked them up OK (even though I only specified the MDF when re-attaching it). I proved this by loading data into a table created on a particular filegroup. The NDF grew when the initial limit was reached. The logfile also grew to 10Mb. DBCC LogInfo showed 6 virtual logs. I ran DBCC ShrinkFile and it dropped to 3Mb and only 3 virtual logs.
On to the live DB now...!
-
I have another method, because on a certain moment I had not enough diskspace to make a backup and used
ALTER DATABASE [name of the database] SET RESTRICTED_USER With ROLLBACK IMMEDIATE
DUMP TRANSACTION [name of the database] WITH NO_LOG
DBCC SHRINKFILE (2,200)
ALTER DATABASE KLASSEMENT SET MULTI_USER
Maybe it isn't the right way but it worked fine
Normally I use the code
use [name of the database]
BACKUP LOG [name of the database] WITH TRUNCATE_ONLY
DBCC SHRINKFILE(2,200)
but only when I have enough space
I run this code in Query analyser.
But I am not at all a specialist
Francis
-
Definately worked for me. The logic is for truncating log , you need to have transaction. Try this.
dbcc shrinkfile (2, notruncate)
dbcc shrinkfile (2, truncateonly)
create table t1 (char1 char(4000))
go
declare @i int
select @i=0
while(@i < 100)
begin
insert into t1 values ('a')
select @i = @i +1
end
truncate table t1
backup log <DBname> with truncate_only
drop table t1
Go
-
But does SQL Server change the size of the virtual logs? If I have a 650Mb log file consisting of 2 325Mb virtual logs, and the log grows beyond 650Mb, will SQL Server just add another 325Mb virtual log file, or adjust the size of the virtual logs to fit the new logfile size?
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply