Cannot Shrink Log File 2 (ABC_Log) Because All Logical Log Files Are In Use....

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
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?


View Replies


Cannot Shrink Log File 2 Because Files Are In Use.

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?


View Replies View Related

Shrink Log Files

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.

View Replies View Related

Why Truncate / Shrink Log Files Work The Way They Do???

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 Replies View Related

What Is A Logical File?

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 Replies View Related

Same Logical File Name For Two DB.

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,


View Replies View Related

Change Logical Name Of Db And Log File

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 Replies View Related

Logical File Names

Does anyone know how to change the logical file name of a database?

Appreciate any help.


View Replies View Related

Renaming Logical File Name

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?



View Replies View Related

How To Change Logical File Name

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 Replies View Related

Databases- Same Logical File Name

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
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

View Replies View Related

Shrink File Tool From EM Took All The Empty Space Allocated To Data File

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 Replies View Related

Renaming Logical File Names

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 Replies View Related

SQL Logical File Name Changes During Backup/restore

I cannot restore a backup of a db "A" into a db "B" without getting rid of old logical file names.
SQL7 sample database is Northwind. Its logical file names and locations are (as seen in Properties):

File Name Location
---------------------------------------- -------------------------------------------------------------
northwind ...dataorthwnd.mdf
northwind_log ...dataorthwnd.ldf

I successfully backed it up to ...BACKUPw_bk

I then restored <w_bk> into a new database SOUTHCALM.

Checking its properties, I read:

File Name Location
---------------------------------------- -------------------------------------------------------------
northwind ...datasouthcalm.mdf
northwind_log ...datasouthcalm.ldf

How can I change the logical file name? It must be obvious, but I am feeling dumb today.

Thanks to all,
Boris Feldblyum

View Replies View Related

Renaming A Logical Database File

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....?



View Replies View Related

Indexes On Seperate Logical File...

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?



View Replies View Related

Restore SQL DB With Correct Logical File Names

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 Replies View Related

Difference Between Backing Up To A File And A Logical Backup Device?

What's is thebetween backing up a databaseto 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?


View Replies View Related

Shrink Log File

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 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 ***Don't just participate in USENET...get rewarded for it!

View Replies View Related

How To Shrink 38 Gig .ldf File

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 Replies View Related

My SQL 7 Log File Won&#39;t Shrink


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,


View Replies View Related

Shrink File

I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution

View Replies View Related

How To Shrink A Log File?

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?


View Replies View Related

Log File Shrink

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 Replies View Related

Log File Can't Shrink

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??

View Replies View Related

How To Shrink MDF File

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 Replies View Related

Trying To Shrink The '.mdf' File


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


View Replies View Related

Copyrights 2005-15, All rights reserved