Hi,
I have some space available in the database, I tried dbcc shrink database and srrink file. I am not getting the disk space. But the amount of free space on the database sometime get increased.
Any help please
Thanks
John Jayaseelan
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
Hi All,
I am having a problem with "dbcc shrinkfile (datafile, emptyfile)". It does not totally empty the whole data file, any idea? It seems like it always leaves 0.06MB behind.
Hi
Any info on dbcc shrinkfile would be useful.
I have tlogs that are over 3G.
What are the adverse affects of running dbcc shrinkfile.
Regards
S
Hello,
I am wondering if there will be any performance issues concerning executing the DBCC SHRINKFILE action against a production database? So far all I have read, and it would make sense, would be to disable the scheduled Transaction Log Task to ensure that no backup is running against the database. Does anyone else have any more or other information about this subject?
Thanks in Advance, Daimon
Hi!
We made a mistake to run dbcc shrinkfile at the same time with database backup. We stoped and restarted services and run dbcc shrinkfile again. No success.
System doesn't shrink file at all.
What we can do?
Thank you,
Elena.
Hi All,
I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?
Thanks.
I used dbcc shrinkfile to shrink transaction log, but it worked for only one day. When I checked the properties, transaction log was back to the size I started with. TL was 1586 MB and I set the target size to 1 MB. Any idea why it happened?
View 1 Replies View RelatedHi All,
I have a job that has multiple steps. Step 1 rebuilds the indexes, step 2 truncates the transaction log, and step 3 shrinks the transaction log via dbcc shrinkfile command. The job has been running for quite a while without any problems until this past weekend. The job ran successfully but when I looked at the size of the transaction log, it was the same as before the job ran. I have read on BOL that if part of the logical log is in the virtual logs beyond the target_size mark, SQL Server 2000 frees as much space as possible and issues an informational message. My questions is where is this message stored? How can I read it?
Thanks.
Hello,
I am attempting to shrink the disk size of my db log files, using the following command;
dbcc shrinkfile (filename,truncateonly)
The command completes successfully, but the log file size remains unchanged. Am I missing something here? Should the file size be reduced?
How long does it take to execute DBCC Shrinkfile(DB_FILE, emptyfile) on a 10GB datafiles? If you put your datafiles together with the tempdb datafiles on the same logical drive do we have a performance issue?
Thanks
Hi,
Are there any effects(negative) of running dbcc shrinkdatabase/file on a production box at low/high usage time or high/low activity period of db?
TIA
I have a log that has grown unchecked for a long time. I truncated it, used DBCC SHRINKFILE on it, and backed it up. It has not shrunken. I still have a database with an allocated size of 506 MB with 435 MB unused.
I have seen messages that others have posted where they have used DBCC SHRINKFILE without success. It was recommended that they use the sp_force_shrink_log script that is available on the www.sqlpass.org web page.
Has anyone used this script that can tell me how and where to run it? I'm new to this. I tried creating a script using Jobs in the Enterprise Manager and got an error that the command was too long.
Need help!
Thanks,
Veronica
When I execute a DBCC SHRINKFILE or try shrinking database files through enterprise manager it works fine, except when I reboot the server the files return to the original size. Here is the statement I used:
DBCC SHRINKFILE (filename, filesize)
I have also tried using the TRUNCATEONLY option.
When i excute this command
DBCC SHRINKFILE(pubs_log, 2)
the message "Could not locate file 'TEST' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator." raiseed.
please what is it?
thank a lot
How to use this query against any database ? what is the correct way of using this command ?
I have read this: [URL] ..... but it doesn't work.
How to use this command ?
hi i got problem when i want to shrink file into one of my database
it always cause error
"A severe error occurred on the current command. The results, if any, should be discarded."
it happend in DBCC ShrinkFile (@name, 0)
i dont know why it occurs anybody can help?
thanks
-- shrink all files within the database
Declare @curFiles Cursor
Declare @Name sysname
Set @curFiles = Cursor Local Fast_Forward Read_Only For
Select RTrim(LTrim(name)) from sysfiles
Open @curFiles
Fetch Next From @curFiles Into @Name
While @@Fetch_Status = 0
Begin
-- Cause problem because transaction log backup had run at the same time
--that the shrink was occurring which is what caused this latch problem
DBCC ShrinkFile (@name, 0)
Fetch Next From @curFiles Into @Name
End
Close @curFiles;
deallocate @curFiles;
go
arifliminto86
[edit by tkizer]: moved thread out of Data Corruption forum
Hi,When I use dbcc shrinkfile to shrink LOG file, following error occurs:DBCC SHRINKFILE(2)---------------------------------------------------------------------------------------Cannot shrink log file 2 (myDB_log) because all logical log files are inuse.(1 row(s) affected)I have only one transaction log file in my Database, who can tell me what'tthe matter?If my current log file is in use, how can I find who is using it and stopusing then do the shrink operation?Thanks.Scarab
View 5 Replies View RelatedAny body got any ideas how we might get around the following error.
command used:
dbcc shrinkfile('DB_Data',EMPTYFILE)
Result:
DBCC SHRINKFILE: Page 3:9224674 could not be moved because the partition to which it belonged was dropped.
Msg 2555, Level 16, State 2, Line 1
Cannot move all contents of file "DB_Data" to other places to complete the emptyfile operation.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
the file needs to be split from 1 x 200G file into multiple data files in the same filegroup. works for a couple of hours the gives this error, file is still 100G, but has 99% empty space.
Cheers
Hi!
I have filegroup that has to few files.
I added new files (corresponding to number of cores).
Now I'm trying to move data to these new files:
dbcc shrinkfile ('oldfile', EMPTYFILE)
But now there are gone nearly one day and dbcc is not finished yet.
It is stressing not to see some progress.
Is it possible to see in some sys table how much data are moved?
Alternative suggestions?
Thanks
Hello,
I'm running the following command on the machine with MSDE on it:
osql -Uuserid -Ppassword -dlocalmsdedatabasename -Q"DBCCSHRINKFILE('logicalnameofdatafile')"
The command has been sitting with no visible movement (neither the size of the file is changing nor the CPU/Memory changes in Task Manager) for 3 hours already.
Is there anything wrong?
Thanks,
Lana
I'm running full recovery mode and doing log shipping so changing to simple mode is not an option.
I'm running BACKUP LOG right before and when I check it says my log is 99% free (on a 180GB log).
When I do DBCC LOGINFO('dbname') right before and after I see a dozen entries and they are all over the file and not just at the starting offset areas. The BACKUP LOG doesn't clean out the file completely.
Is there any explanation for this? Even though I'm doing this at off hours, is it possible that someone on the site in that split second is putting new entries in the log? Why are they spread out though? If they just put entries at the beginning I could shrink the file to a normal size still.
SQL2000 Server, SP4, a database with a 17Gb log file. It has been backed up so all transactions should be validated, now the real file size needs to be shrunk because I need the diskspace plus I want to speed up the backup process.
http://support.microsoft.com/kb/272318/ Tells me what to do but not where to do it.
So I need to run this code : DBCC SHRINKFILE(pubs_log, 2)
but from what console do I run it?
I'm trying to run
dbcc shrinkfile(4)
/*
File ID 4 of database ID 13 cannot be shrunk as it is either being shrunk by another process or is empty.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
*/
The commented lines is what I get in return. There's nothing being executed on this file. I dropped a few indexes. I need the space back from the file. Backups and everything are run on it normally. Is there something I'm missing or is there something wrong with it? I don't do 'AutoShrink'. Also, the file is not empty. Checkdb is working fine. Dbcc ShrinkDatabase also works fine but doesn't even recognize these files. It doesn't even show this files in the results pane when executing the command. Thank you for your help.
On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.
View 1 Replies View RelatedHi,I need to shrink a database file and was wondering whether it isrequired to run a full backup after the shrink operation.In SQL Server 7.0 shrinkfile was a non-logged operation so wouldinvalidate your transaction logs. Is the same true for 2000?Obviously as a matter of course I would backup before and after theoperation but going forward I may want to implement this on a regularbasis.CheersDee
View 7 Replies View RelatedWhat is the best way to control Transaction log sizes?? The logs keep growing and when I manually truncate
them and use the dbcc shrinkfile command, it doesn't want to shrink it to the specified size. In some cases,
our data file is smaller than the log file. It'll have a Gig of space allocation but only contain 40 megs of data.
Any suggestions on how I can shrink the log file??
Hi Folks:
We´ve migrated from SQL Server 7.0 EE to SQL Server 2000 in a 8processors, 8 GB RAM server, using W2000.
All seems go ok, but after reorganizing indexes, when we want to recover free space in the differents files using DBCC SHRINKFILE, that recovers are taking the triple of time than with previous SQL Server 7.0.
Shrinking big files (6GB to recover 1.5 GB), previously taking 3 hours now are needing 9 hours.
Any suggestion or help will be appreciated.
Best Regards.
I am using SQL Server SP 2 on Windows 2003 Server Standard edition:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I have a datbase that's rather large. The log file is 94656 pages, and the data file itself is 94197200 pages. There's only one data file and one log file. The database passes DBCC CHEKCDATABASE with no errors.
When I run DBCC SHRINKDATABASE against the database, the command runs for about twenty seconds then produces this error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I can't find anything interesting in the ERRORLOG around the time that I run this command. The error appears if I use the TRUNCATEONLY option or not.
How do I fix this problem?
And in general, why are the engine errors in SQL Server so confusing and not directly actionable?
Hi there
I'm having a problem free'ing a extended stored procedure DLL from memory, so I can delete the file/replace it and reload a new one into the DB.
Quote: Originally Posted by http://www.windowsitlibrary.com/Content/77/20/1.html
DBCC <DLL Name>(free)
This statement must be run before a DLL will be replaced and the operating system will let the file be overwritten.
I'm running the command in Query Analyzer and it seems to be "working" fine, but when I try delete the dll, it tells me another program is using it.
The only way I seem to be able to free the dll is to restart the DB.
Am I doing something wrong?
Thanks
SynAck
xp_fixeddrives gives the free space on the server drives. Is there a xp which gives the actual size of the drive?
I need to calculate the %space used!
------------------------
I think, therefore I am - Rene Descartes
Hi there.
When I try to free previously registered DLL, server is starting to execute query and it takes a number of minutes and nothing happens.
Here is an example:
sp_addextendedproc 'xp_test','test.dll' //works fine
exec master..xp_test '999' //works fine
sp_dropextendedproc 'xp_test' //works fine
dbcc test(free) //do not work
I've tested it on:
SQL MSDE SP3 //i was getting an acces violation first: spid 51 Exception 0xc0000005 EXCEPTION_ACCESS_VIOLATION at 0x00000000
SQL MSDE SP4 //but on this SP I have the same behaviour as on server 2005 / infinite query execution time
SQL Server 2005 standard //infinite query execution time
SQL Server 2005 standard with SP2 //infinite query execution time
I can't understand this because this started after chaning my machine.
It worked without any problems on the previous one and I've used this metod very often on the same dll which hasn't changed.
Is there any way to solve this?
Thanks for your time.
In chapter 2 of Microsoft Press' "Inside Microsoft SQL Server 2005 - T-SQL Querying", an example showing how to use dynamic management view sys.dm_exec_query_optimizer_info begins with the following code:
SET NOCOUNT ON;
USE Northwind; -- use your database name here
DBCC FREE PROCCACHE; -- empty the procedure cache
GO
When I copy, paste and execute this query, I get the message:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'PROCCACHE'
How can I correct the syntax?
PS. The rest of the example seems to run properly even without the DBCC FREE PROCCACHE line.
Thanks.
Bill