September 4, 2003 at 12:14 pm
I am trying to shrink my log file as the transaction log space is 62.38MB out of which only 0.18MB is used.
When I run the command:
DBCC SHRINKFILE (log_file, 20, truncateonly)
i get the error:
Cannot shrink log file 2 (log_file) because total number of logical log files cannot be fewer than 2.
Can somebody tell me what am i doing wrong?
Thanks
September 4, 2003 at 12:18 pm
Try running this first
backup log dbname with truncate_only
Then you should be able to run your statement, I beieve that error pertains to the used portion of the log and virtual log files.
HTH
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 4, 2003 at 12:36 pm
quote:
Try running this firstbackup log dbname with truncate_only
Then you should be able to run your statement, I beieve that error pertains to the used portion of the log and virtual log files.
HTH
Ray Higdon MCSE, MCDBA, CCNA
No luck. It still gives the exact same error and Transaction Log Space remains the same.
September 4, 2003 at 1:12 pm
If you are on SQL2K, run dbcc sqlperf('logspace') and post the results here, also, what recovery mode are you in for the database? If you don't know you can run sp_helpdb and it will say in the status field.
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 4, 2003 at 2:22 pm
quote:
If you are on SQL2K, run dbcc sqlperf('logspace') and post the results here, also, what recovery mode are you in for the database? If you don't know you can run sp_helpdb and it will say in the status field.Ray Higdon MCSE, MCDBA, CCNA
Here are the results development_db is the one for which I want to shrink the log:
master3.367187533.4686780
tempdb0.742187558.4210510
model0.992187584.3011780
msdb15.24218828.1362110
pubs0.742187552.631580
Northwind0.992187544.9803160
development_db62.3750.327216920
It was set to Simple. But now I changed it to Bulk-Logged, so that I can take a Transaction Log Backup.
Thanks
September 4, 2003 at 5:27 pm
OK, sounds like you solved it, when in simple you can not do a log backup
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 5, 2003 at 8:40 am
quote:
OK, sounds like you solved it, when in simple you can not do a log backupRay Higdon MCSE, MCDBA, CCNA
But my problem is not solved. I am unable to truncate the log file. Total allocated space for transaction log space is taking 62.38MB out of which only 0.18MB is used and 62.2MB is free. So i want recap this free space by making the total transaction log space as 3MB.
Thanks,
September 5, 2003 at 8:42 am
No one asks the version of your SQL Server, can you tell that?
September 5, 2003 at 1:00 pm
If he was able to change to bulk-logged he is on SQL 2000. Davinash, change to full mode then try to run
backup log dbname with truncate_only
Then run your shrinkfile command, then change back to simple or bulk-logged.
Ray Higdon MCSE, MCDBA, CCNA
------------
Ray Higdon MCSE, MCDBA, CCNA
September 5, 2003 at 1:54 pm
Do you have more than one log file? An sp_helpdb command will reveal what files are present. Look in the usage column.
For instance:
EXEC sp_helpdb 'Northwind'
will reveal two files:
Northwind (data only)
Northwind_log (log only)
If you have more than one log file, you can't use DBCC SHRINKFILE to shrink the log. See this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814574
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
September 5, 2003 at 2:15 pm
quote:
Do you have more than one log file? An sp_helpdb command will reveal what files are present. Look in the usage column.For instance:
EXEC sp_helpdb 'Northwind'
will reveal two files:
Northwind (data only)
Northwind_log (log only)
If you have more than one log file, you can't use DBCC SHRINKFILE to shrink the log. See this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;814574
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
No, I don't. I have only one log file. Here is the output of EXEC sp_helpdb <dbname>:
development_data 1C:\Development Database\development_data.mdf PRIMARY246336 KBUnlimited10%data only
development_log 2c:\Development Database\development_log.ldf NULL63880 KBUnlimited0%log only
Also ray_higdon, I tried again as you suggested. It gives the same error as soon as I run the SHRINKFILE command. Yes I am running SQL 2000 on Windows 2000 server. Just to let you know, this database was originally created in version 6.0 or 6.5 and its been upgraded since then. I wonder if any of these upgrades corrupted any system table and left behind some bad records in it. Because earlier I had one such security problem due to a bug in SQL upgrade to system tables.
Thanks.
September 5, 2003 at 2:27 pm
Taking a wild stab in the dark. Do you see anything other than these two files in the sysfiles and sysfiles1 tables for that particular database?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
September 5, 2003 at 2:32 pm
quote:
Taking a wild stab in the dark. Do you see anything other than these two files in the sysfiles and sysfiles1 tables for that particular database?K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
No. There are just 2 records, one for the db and one for the log in both tables.
September 7, 2003 at 4:35 pm
There are certain rules about how you can delete log files that I have never understood. The practical upshot seems to be you can never shrink them as much as you'd like!
However in SQL 7 and 2000 you can completely trash your log and start again.
BEFORE GOING ANY FURTHER DO A FULL BACKUP OF YOUR DATABASE!! I've only done limited testing of this script.
You could try this.
--Deletes the transaction log and replaces it with an 'empty' file
use master
set nocount on
declare
@dbname sysname,
@filename varchar(255),
@logname varchar(255),
@sql varchar(255),
@cmd varchar(255)
--***************************************************************
--Change database name here
select @dbname = 'xxx'
--***************************************************************
select @sql = 'select rtrim(filename) filename into ##file from ' + @dbname + '..sysfiles'
exec (@sql)
select @filename = filename from ##file where upper(filename) like upper('%.mdf')
select @logname = filename from ##file where upper(filename) like upper('%.ldf')
drop table ##file
if @filename is null or @logname is null
begin
select 'File error - aborted'
select @filename
select @logname
end
else
begin
exec sp_detach_db @dbname
select @cmd = 'del ' + @logname
exec xp_cmdshell @cmd
exec sp_attach_single_file_db @dbname, @filename
end
PS - how do you post and keep the format (indentations) of the code?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply