Cannot Shrink Log File 2 (ABC_Log) Because All Logical Log Files Are In Use....
Nov 6, 2003
A small database ABC with data only 5 mb but its log is growing everyday around 20 mb. I want to shrink its size like for other databases on daily bases.
1. backup log ABC with truncate_only
2. DBCC SHRINKDATABASE (ABC, 10)
got following error:
<<Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.>>
with no_log also tried but have the same error when dbcc shrinkdatabase..
any idea?
thanks
-D
View 8 Replies
ADVERTISEMENT
May 24, 2004
after backup the log with truncate_only,
dbcc shrinkdatabase('ABC', 10)
failed with following error:
Cannot shrink log file 2 (ABC_Log) because all logical log files are in use.
I have put above statements with a job running every night, just got the same error on shrinkdatabase.
Is there a way to shrink the log without stop-restart the sql server?
-D
View 4 Replies
View Related
Jul 31, 2006
Hi All,
I have a question that keeps my mind buys and I wasn't able to find answer on that.
I have a full backup recovery plan and I am backing up my Log files in every 15 minutes. At the same time My log files are shrunk in every 3 hours so Are shrinking the log files correct approach?
If somebody enlighten me on this issue I appreciate it.
Thanks
LS
:confused:
View 4 Replies
View Related
Nov 6, 2015
I re-started the SQL Service.
I did numerous commands with no luck.
Shrink DB, Shrink files and shrink DB.
I tried the GUI but it bombs out.
View 2 Replies
View Related
Nov 13, 2007
I've been having some trouble keeping my log file size under control due to some off-hours data loads. Currently, while updating some certain tables, the data base is still in "Full" mode, which of course makes the log file grow accordingly.
Today I figured out that, given the current scenario, in order to shrink the log file back down (after the table update), I have to (1) backup the log file, (2) shrink the log file, (3) backup the log file again, and (4) shrink the log file again. My question is: why do I have to do two iterations of backup / shrink? Why does it not work with one iteration? I just want to better understand the architecture and functionality.
Also, would there be a better way for keeping the log file size under control? If I switched the database to "Simple" mode to load the data, would that work any better?
If so, what might be the best methodology?
Thanks in advance for any responses.
Kent Rothrock
Highland Mapping, Inc.
View 4 Replies
View Related
Oct 12, 2015
how to shrink log files in SQL 2014 alwaysOn ?
View 1 Replies
View Related
Nov 7, 2000
In the database properties screen, there are four tabs: General, Transaction Log, Options, Permissions. In the General Tab, it lists four columns: File name, Location, Space Allocated, File Group.
The string in the File name column has a value such as MY_DATABASE_DAT, whereas the Location column has a value like D:mssql7dataMY_DATABASE.MDF.
The Location value is clearly the Windows file name. The "File name" (which I have seen called the "logical file") is a mystery to me. What is it used for? How can it be changed? Is there a problem if two different databases have the same "logical file" name?
(For example, if you do sp_detach_db, copy the file to another place and then sp_attach_db the new file to another database name, you have two different databases with the same "logical file" name.)
Much thanks to anyone who can shed light on this.
View 1 Replies
View Related
Jul 5, 2000
Hi everyone,
I have a database (xyz) one Machine A. On the same machine I want to create a copy of the (xyz)database with different name(xyz_1). When I restore with move option it will restore but the thing is logical file names of xyz and xyz_1 are same. I know I can change these file names by manupulating the system table.
My question is If I didn't change the logical file names of database xyz_1(new). Is there any problems or issues may arise.
Thank you,
Dindu.
View 1 Replies
View Related
Jul 20, 2005
I've production sql server 7 sp3 on windows NT. I had a 8GB data file ofwhich 5GB were used and 3GB were unused. I wanted to take back the unused3GB.So I did the following with EM GUI:1. I tried to "truncate fre space from end of the file". Didn't truncatethe file. I believe there was no empty space at the end of the file.2. Next I chose the option to "shrink file to 5GB". And to my horror thedata file instead of taking just 5GB took the empty spaces also and the sizeof the used data file went to 8GB.Any idea what's going on?TIA,SP
View 2 Replies
View Related
Jan 10, 2012
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
View 9 Replies
View Related
Jan 18, 2000
I cannot find the answer to this: how do you update the logical file name for a database? Restore database "Alpha" over "Beta" specifying WITH MOVE parms allows "Alpha"'s physical files to overlay "Beta"'s, but now "Beta"'s logical file names are "Alpha_Data" and "Alpha_Log".
Updating sysfiles directly gives back a 270; "Get outta here!"
Can this not be done?
View 2 Replies
View Related
Apr 24, 2000
Hi
Every one
One thing i found out that my 2 databases in server A
Has same logical file name .
I have try to change the name but it is not allowing me to change.
I had refer BOL it says that we should have unique logical file name in a server for each database.
Question i have is does it going harm me i don't know this
if any one u know please suggest me.
Problem I already started getting is I do backup 5 small databases of 10 to 20 mb in one tape only. The 2 database which has same logical file name out
of which one is getting copied & another is not ,All other database it backup
perfecly.
But at the same time in a hard disk if i take backup of this database in a same device it works perfecly so i don't understand where the problem is
If any has any idea please suggest me
Thanks
Nirmal.
View 1 Replies
View Related
Mar 14, 2003
I'm moving a database (XYZtest) from the test server to the production server via sp_detach/sp_attach. I want the logical file names to be XYZ_data, rather than XYZtest_data, etc. I can easily rename the disk files, but how do I rename the logical file names?
Thanks,
Al
View 1 Replies
View Related
Apr 24, 2002
Hi,
Does anyone know how to change the logical file name of a database?
Appreciate any help.
Steve
View 1 Replies
View Related
Jul 20, 2005
Hi,Is there an option (stored procedure) or whatever to change to logical nameof the databasefile and the database log file ?Arno de Jong, The Netherlands.
View 1 Replies
View Related
Mar 9, 2000
Is there a way to rename the logical file for a database. For example, if I am moving a development database into production, I can use backup - but the backup takes the logical file names of the database and puts it into my production server. Now I have a production database with "dev_data1" for a logical file.....Can I change that name....?
Thanks!
Dean
View 3 Replies
View Related
Sep 22, 2000
Hi all -
Quick question... I want to move all my non-clustered indexes to their own seperate drive array. How would I accomplish this?
Do I just add the filename at the end of the statement like you do in Oracle? (EXAMPLE: CREATE INDEX IDX_Cls_Code on dbo.Class(Code) on secondary.ndf)
If this isn't correct, can someone please post the correct syntax?
Thanks!
Rich
View 1 Replies
View Related
Jul 23, 2005
Is there a way to rename the logical file names? I'm not talkingspecifically about the physical files, because those can be changedduring a restore, but the values immediately to the left of those inEnterprise Manager such as DBName_Data and DBName_log. EnterpriseManager lets me change them during a restore, but when I do it gives anerror. Any ideas?
View 1 Replies
View Related
Feb 20, 2007
Hi,I am planning to automate a nighty restore of a DB on another servercan someone point me in the right direction with the SQL script tomodify the logical file names to the correct path and not the onescarried over with the DB??i.e the database is to be renamed on the new serverany help much appreciatedMany thanks in advance
View 14 Replies
View Related
Apr 30, 2015
Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
For instance:
database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf
Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].
View 3 Replies
View Related
Jun 16, 2007
What's is the between backing up a database to a file and a logical backup device?
If I point the logical backup device to a file on the filesystem, it's same as backing up to a file? isn't?
Thanks
View 1 Replies
View Related
Apr 3, 2011
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies
View Related
Mar 27, 2000
I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay
View 1 Replies
View Related
Oct 20, 1999
On a database with a log file that has an unrestricted file growth, the file size exceeds 1 GB. Since this excessive was caused by a badly written update statement, I want to reduce the size to about 200MB.
After reading the BOL I was convinced that I only need to take two actions: truncate the log file (to create some free space in the log file)and shrink it.
These are the statements I executed:
backup log ODS with truncate_only
dbcc shrinkfile (ODS_Log, truncateonly)
After I executed these statements - BTW, there were no errors - the file size was still the same. Can somebody tell me why?
Thanks,
Stef
View 1 Replies
View Related
Aug 27, 1999
Hi,
I have created a new database in SQL Server 7 with the auto grow options set to on. I then added a whole load of new data to the table which made the transaction log file grow to 20Mb.
I then truncated the transaction log to remove all the completed transactions. The Enterprise Manager now shows the Log to only have 3MB of data in it but the file is still 20MB.
I have tried setting the truncate log on checkpoint option, and tried running DBCC SHRINK DATABASE and DBCC SHRINK FILE commands but these seem to have no affect on the file size.
Does anyone have any idea what I might have missed/done wrong?
Yours well and truly stuck,
Martin
View 2 Replies
View Related
Oct 25, 2004
I have Disk Xtender 2000 which was made by OTG Software , Legato and now EMC. I have an NT 4.0 PC with Microsoft SQL 2000. I have a drive space problem and need to shrink a 38 gig .ldf file called OTG03.ldf I also have a 2 gig .mdf file called OTG03.mdf How can I shrink this .ldf file. I'm not a DBA so being specific is greatly appreciated.
View 7 Replies
View Related
Dec 13, 2012
My DB's recover model is SIMPLE. Is it OK to schedule a SHRINK FILE only on the log files regularly? Any GOOD vs BAD about my plan? I want to do this because the log files keeps on increasing. Right now, the log file s on ENABLE AUTOGROWTH, FILE GROWTH = 10%, RESTICTED FILE GROWTH = 2,097,152.
View 8 Replies
View Related
Apr 7, 2008
I am geting growth alerts and need to shrink a log file that is 99% full, but it won't let me. Here is the message I get.
The transaction log for database 'SOM_System' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
What can I do in order to shrink this log file??
Thanks
View 2 Replies
View Related
Apr 30, 2008
I have noticed many times, even after log backup, shrink file (SSMS) is not reducing the file size. Even though there is free space shown in SSMS.
Subsequently I change the recovery type to SIMPLE and shrink – that does the trick for me.
Does anyone know why does this happen? Is there a better solution?
------------------------
I think, therefore I am - Rene Descartes
View 2 Replies
View Related
Jun 4, 2007
Hello,
I have a database that the '.mdf' file is just huge. The physical size on disk is approximately 110 gig. I run weekly maintenance plans to rebuild the indexes on it.
I ran the 'sp_spaceused' command and got the following results:
database size: 106996.25 MB
unallocated space: 9614.35 MB
reserved: 99716504 KB
data: 13968056 KB
index_size 4002184 KB
unused: 81746264 KB
I was trying to clear the unused space, the numbers are telling me that I have 81 gig of space unused, but no matter what I do the '.mdf' file will not shrink.
I ran the following command: DBCC SHRINKDATABASE ({dbname}, 10,TRUNCATEONLY)
Any thoughts?
Thanks for the help
Richard
View 3 Replies
View Related
Sep 6, 2007
My database MDF file growth is high and due to space limitation i have to shrink the MDF file so how do i do that
View 5 Replies
View Related
Jul 20, 2005
Hello,Database log file on MSsql2000sp3 is 27gb when database itsself 305mb.I attempted to shrink the log file with Enterprise manager,but it wantsto use a minimum of 26.xxx MB,approximatley 27gb of disk space.when running the dbcc shrinkfile (file_name) message returned is "allvirtual logs are in use'Any ideas how to reduce the log file?Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Jan 7, 2008
First off, I'm normally not one to shrink the hell out of data files (for obvious reasons), but this is a special case. We are setting up a mini test environment in preparation for migrating one of our systems to SQL Server 2005 (among other things).
The problem I have is that the test SQL Server I have to use has only about 50GB of disk space that I can allocate to databases, when the databases that I need to fit in there sit at around 130GB total. I've got 13 total databases that I have to fit in there. There are several logging tables in each, and lots of binary data that's really not needed for our testing. The first thing I did on my testing copies was to change the recovery model to simple, which chopped the log file.
So I've been truncating the logging tables and have been stripping out the unneeded data. I then have been running a script that I wrote to reindex, then shrink the individual files, then the database as a whole. Probably overkill, but it has worked on all of the DBs with the exception of one.
This particular DB is approx 21GB in total size (it's already come down from about 55GB), but when you look at the free space, it's showing 75% free inside the mdf file. I don't really care much about performance at this point, I just need to get the file size down and can't figure out how.
Any ideas?
BTW - this is the script that I wrote:
declare @tablename varchar(255)
declare @logfilename nvarchar(200)
declare @datafilename nvarchar(200)
declare @dbname nvarchar(200)
declare @sql nvarchar(1000)
set @dbname = ltrim(rtrim(db_name()))
set @logfilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.ldf%')))
set @datafilename = ltrim(rtrim((select name from sysfiles where lower(filename) like '%.mdf%')))
/* Reindex Tables */
declare tablecursor cursor for
select '[' + table_schema + '].[' + table_name + ']' from information_schema.tables
where table_type = 'base table'
open tablecursor
fetch next from tablecursor into @tablename
while @@fetch_status = 0
begin
print 'Reindexing ' + @tablename
dbcc dbreindex(@tablename,' ',90)
fetch next from tablecursor into @tablename
end
close tablecursor
deallocate tablecursor
/*Shrink the crap out of the DB*/
set @sql = 'BACKUP LOG [' + @dbname + '] WITH TRUNCATE_ONLY'
print @sql
exec sp_executesql @sql
set @sql = 'DBCC SHRINKFILE([' + @logfilename + '], 1)'
print @sql
exec sp_executesql @sql
set @sql = 'DBCC SHRINKFILE([' + @datafilename + '], 1)'
print @sql
exec sp_executesql @sql
set @sql = 'DBCC SHRINKDATABASE([' + @dbname + '], 1)'
print @sql
exec sp_executesql @sql
View 4 Replies
View Related
