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.
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
I am having DB with 4 ndf and 1 Mdf, In properties
1.MDF- Intial Size 5GB and Autogrowth 5GB
2. 5 NDF- Intial Size 20GB and Autogrowth 5GB.
So we are using the template and creating DB's.S o we have more than 30DB on same size. But Sometime actual data was very low not even 15 GB.
is it good if i shrink the DB?
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.
I have a 2.8G SQL 7 database with 1.6G used space. I want to shrink it to 1.8G, but after I issued the 'DBCC srhinkfile(datafile, 1800)', it didn't shrink the file, instead it expanded to 2.7G used space? I tried on a test server, the command worked every time although the time to complete the task tended to increase every time.
I did the DBCC DBREINDEX (fill factor 90) and updated usage, the size didn't change. DBCC CHECKDB, CATELOG AND SHOWCONTIG were all performed and there wasn't any error or fragmentation.
Now I'm wondering what's the actual size of my database, 1.6G or 2.7G? If there's a way that I can get back close to my original used sized, then I can try to shink the file again. Any comment is welcome. Thanks advance.
I wonder how do I shrink log file in a specific database which in Mirror/Synchronized/Restoring state..So that database is in Mirror server ( High availability ).
View 4 Replies View RelatedDear all I create this html file on the fly in my asp.net application abd what i would like to do is to store it inside my sql2005 database. What would be the best way?The html file itself is not really big. Probably not more then 600 - 800 characters most. I was thinking the text type fields of the database and then when retreiving it dump it inside in a file and save the file with html extention. Are there any better sugestions? Thank you for your time
View 3 Replies View RelatedI'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
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
I am looking for the correct procedure for shrinking a database file ,
please reply back if anybody knows the solution
Thanks
Ajay
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
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
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 RelatedMy 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 RelatedI 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
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
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
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 RelatedHello,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 Relatedafter 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
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
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.
I am trying to shrink the log file as it is eating my disk space. increased to 100GB..message after running the query"Cannot shrink log file 2 (mydatabase_log) because the logical log file located at the end of the file is in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
"
(1)use mydatabase alter database mydatabase set recovery simple with no_wait dbcc shrinkfile(mydatabase_log,1) database [mydatabase] set recovery full with no_wait
(2)USE mydatabase;
GO
Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE mydatabase
SET RECOVERY SIMPLE;
GO
Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (mydatabase_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE mydatabase
SET RECOVERY FULL;
GO
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.
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
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.
hi,
how can I trunc an shring a db transaction log?
thanks for reply....
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
Is Auto shrink is good option where database is very critical and there is no down time?
View 4 Replies View RelatedHi all
My Transactional log size increased to 39GB, it is in full recovery mode,
To regain the space, i have done the following
BACKUP LOG DB_NAME WITH TRUNCATE_ONLY
DBCC SHRINK_FILE (LOG_FILE_NAME,500)
But not able to regain the space in the hard disk.
No Transactional backups to truncate the log file were setup.
Can you please tell me why the space was released and what should i do further to clean up the sapce
Output of below query is
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage where internal_objects_alloc_page_count >10 and session_id> 50
GROUP BY session_id;
[Code] ....
Database MDF is 27806 MB and I tried to shrink but unable to shrink. It is production server.I do not want Restart sql server.There is no open transaction.
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+. The data (.mdf) file is only 3GB.
Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."
I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
Greetings, I have just arrived back into the country (NZ) and back into ASP.NET.
I am having trouble with the following:An attempt to attach an auto-named database for file (file location).../Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
It has only begun since i decided i wanted to use IIS, I realise VWD comes with its own localhost, but since it is only temporary, i wanted a permanent shortcut on my desktop to link to my intranet page.
Anyone have any ideas why i am getting the above error? have searched many places on the internet and not getting any closer.
Cheers ~ J