Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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
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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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?

-D

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.
Thanks
LS
:confused:

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,

Dindu.

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
Hi,
Does anyone know how to change the logical file name of a database?

Appreciate any help.

Steve

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?

Thanks,

Al

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

Thanks!

Dean

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?

Thanks!

Rich

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

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?

Thanks,
Stef

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

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
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 Replies !   View Related
Data File Will Not Shrink
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 Replies !   View Related
Msdbdata.mdf File Is Over 5 GB, Cannot Shrink It
I have  problem with my sql server developer edition.

 

I began reciving alerts regarding disk space, and found out that the msdb files are 5 GB!!! the data file is full, so I cannot shrink it, but the table usage shows only a few MB used by tables.

View Replies !   View Related
Log File Auto Shrink Question
Hi, I am trying to automate shrinking the transaction log file on SQL server.
Every so often we get errors with our application using SQL server, in which I resolve by running the backup log and shrink log commands. However, recently I got the error: Could not allocate space for object 'table_name' in database 'database_name' because the 'Primary' file group is full. To resolve this I had to create another transaction log and then run the backup log and shrink log commands.

I know need to automate the process of shrinking the log file. I have checked and the Auto Shrink checkbox is ticked but these errors still occur.
How can I delete the additional log file I created and automate this task of shrinking the log file within SQL server? Any help would be appreciated...Thanks

View Replies !   View Related
Shrink The Transaction Log File In SQL7
I have a database (sql server 7.0) that has some big unused space in the transaction log. I tried to shrink the transaction log, but it seems it is not working. I used the same procedures to successfully shrink the log file for database of SQL Server 2000.

Here are what I tried:

shrink database from the Enterprise Manager.
or
dbcc shrinkfile (Spoper_Log, 40)
backup log Spoper with truncate_only

Does anybody know how to shrink the transaction log in SQL Server 7? This is a production database and I can't afford to lose any data.

View Replies !   View Related
Shrink Primary Data File
I have tried using DBCC SHRINKFILE and SHRINKDATABASE to shrink the Primary Data File, but neither of them works. I can shrink the other database files. Please help me. Provide me any suggestions.

(I have already tried shrinking the transaction log, then creating a table, inserting records into the table, shrinking the database and file, then truncating the log and backing up the database)

View Replies !   View Related
Log File 80gigs &#043; Wont Shrink
i have a log file for a 200 meg database that is now over 80gigs. It is way out of control but DBCC shrink file just reports... the file is not part of a replication set.

Cannot shrink log file 2 (Database_log) because all logical log files are in use.

(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


I have read around and seen the suggestion to make a new database file and copy all the data to it. How is that done? I am using SQL 2005 Express with microsoft SQL server management Studio express.

View Replies !   View Related
Shrink Log File Database In Sql2005
hi
i have shrink log file of database use dbcc command

Message
Executed as user: sa. Cannot shrink log file 2 (abc.ldf) because all logical log files are in use. [SQLSTATE 01000] (Message 9008) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded.

View Replies !   View Related
MANUAL SHRINK DATABASE OR FILE
 

Hi guys

when we do manual Database shrink at normal office hours does it make any issues

or best way to control it

1. Does it lock database  and runs the shrink statement.

2. at time of shrink users allowed or not.

3. transactions can be committed.

View Replies !   View Related
Trunc And Shrink Log (.ldf) File -&&> Trans Log
hi,

how can I trunc an shring a db transaction log?



thanks for reply....

View Replies !   View Related
Log File Automaticaly Shrink During Backup Log Without Particular Option
Hi,I am using a SQLServer 7 SP4 version.I have scheduled one job with the SQLServer agent which backup the logof the databases every day.The command is :"backup log base to [backup device] with noinit"At the end of the command, the logfile shrink from 20Mo to 1Mo.I don't understand because I though that only 'backup log withtruncate...' or 'dbcc shrink datafile' could do that.Any idea ?FYARJP

View Replies !   View Related
Can&#39;t Shrink DATABASE File With Free Space
I have a SQL 7 database which has grown from it's original size of 400 megabytes to over 1.2 gig. It currently shows over 850 meg free space, but using DBCC or EM to shrink it changes nothing. (Please note this is the database file not the log file I am shrinking). I have even tried dumping the DB and recreating it and loading from the dump file, an no change. has anyone seen this problem before.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved