DBCC ShrinkFile Error
Feb 6, 2008
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
View 3 Replies
ADVERTISEMENT
May 12, 2007
Any 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
View 1 Replies
View Related
Nov 21, 2006
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.
View 21 Replies
View Related
Oct 14, 2007
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?
View 1 Replies
View Related
Apr 5, 2001
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.
View 1 Replies
View Related
Jul 5, 2001
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
View 2 Replies
View Related
Oct 18, 1999
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
View 1 Replies
View Related
Nov 28, 2001
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.
View 2 Replies
View Related
Oct 19, 2006
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.
View 5 Replies
View Related
Dec 29, 2005
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 Related
Oct 19, 2006
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.
View 5 Replies
View Related
Aug 29, 2006
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?
View 5 Replies
View Related
Aug 1, 2007
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
View 1 Replies
View Related
May 14, 2001
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
View 1 Replies
View Related
Jan 25, 2001
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
View 3 Replies
View Related
Dec 30, 2005
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.
View 14 Replies
View Related
Feb 18, 2004
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
View 1 Replies
View Related
Jul 19, 2013
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 ?
View 1 Replies
View Related
Feb 13, 2006
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 Related
Feb 16, 2008
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
View 6 Replies
View Related
Jun 19, 2007
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
View 5 Replies
View Related
Mar 5, 2002
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
View 1 Replies
View Related
Aug 19, 2007
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.
View 6 Replies
View Related
Feb 13, 2006
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?
View 4 Replies
View Related
Jan 31, 2002
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 Related
Mar 10, 2006
Hi,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 Related
Oct 30, 2000
What 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??
View 3 Replies
View Related
Mar 23, 2003
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.
View 3 Replies
View Related
Jul 20, 2007
Hi,
I issued this command on Tempdb but it doesnot shrink the file.
dbcc shrinkfile (tempdev_3,1)
go
Messages:
-----------------------------------------------------------------------------------------------------------
DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.
-----------------------------------------------------------------------------------------------------------------------------------------------
I have checked that there are no tables associated with any user in tempdb. Any help is appreciated.
Regards,
Razi
View 15 Replies
View Related
Apr 29, 2014
Why shrinkfile empty file does not redistribute data evenly in the primary file group with multiple files:
Please run the script attached to see what the end result is.
This is what I set up last night on my test machine.
1) Create database [FGTest] size 200MB
2) Create table called TEST on primary
3) Insert 40MB of data into test
4) Create another file group called temp in primary size 200MB
5) Shrinkfile('FGTest',emptyfile) so that all data is transfered from FGTest into temp file group.
6) Add another 2 files called DATA2 and DATA3. Both are 200MB.
7) We now have 3 empty files that I want data distributed evenly on. FGTest, DATA2 & DATA3
8) Shrinkfile('temp',emptyfile) to move all the data from temp over the 3 file groups evenly
I would expect at this stage to have the following:
FGTest = 13MB,
DATA2 = 13MB,
DATA3 = 13MB
(40MB of data over 3 files should be about 13 MBish in each file)
What I actually end up with is this:
FGTest = 20MB
DATA1 = 10MB
DATA2 = 10MB
It looks as though SQL Server is allocating 50% of all data to the original file and then 50% evenly over
the remaining files in PRIMARY.
View 3 Replies
View Related
Mar 26, 2007
Hi All,
I am playing with DBCC command to check the contsrainst on a perticular table (DBCC CHECKCONSTRAINTS ('myTable') WITH ALL_CONSTRAINTS), it always gives the following result:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
nothing more than that, anyone can help please?
Cheers,
Riaz
View 3 Replies
View Related
Sep 6, 2005
I got a error message with SQL 2000 SP3. I do not know this is a bug or table is real corrupted. need help with this one.
(1) DBCC CHKDB output:
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'TAMVSQLNYUNYU' as 'NYUMC amnyuclustadmin' (trusted)
Starting maintenance of database 'PowerPath_NYU' on 9/6/2005 4:20:03 AM
[1] Database PowerPath_NYU: Check Data and Index Linkage...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8964: [Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 405628538. The text, ntext, or image node at page (1:1001056), slot 10, text ID 1595707686912 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'audit_record' (object ID 405628538).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'PowerPath_NYU'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PowerPath_NYU ).
The following errors were found:
[Microsoft][ODBC SQL Server Driver][SQL Server]Table error: Object ID 405628538. The text, ntext, or image node at page (1:1001056), slot 10, text ID 1595707686912 is not referenced.
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in table 'audit_record' (object ID 405628538).
[Microsoft][ODBC SQL Server Driver][SQL Server]CHECKDB found 0 allocation errors and 1 consistency errors in database 'PowerPath_NYU'.
[Microsoft][ODBC SQL Server Driver][SQL Server]repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PowerPath_NYU ).
** Execution Time: 0 hrs, 2 mins, 20 secs **
[2] Database PowerPath_NYU: Check Data and Index Allocation...
WARNING: Database PowerPath_NYU is currently being used by 36 user(s).
This may cause spurious problems to be reported.
** Execution Time: 0 hrs, 0 mins, 1 secs **
[3] Database PowerPath_NYU: Check System Data...
** Execution Time: 0 hrs, 0 mins, 3 secs **
Deleting old text reports... 1 file(s) deleted.
End of maintenance for database 'PowerPath_NYU' on 9/6/2005 4:22:27 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
I also run DBCC CHKTABLE and follwoing is the output.
Server: Msg 8964, Level 16, State 1, Line 1
Table error: Object ID 405628538. The text, ntext, or image node at page (1:1001056), slot 10, text ID 1595707686912 is not referenced.
DBCC results for 'audit_record'.
There are 1958345 rows in 42549 pages for object 'audit_record'.
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'audit_record' (object ID 405628538).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (PowerPath_NYU.dbo.audit_record ).
Thanks,
KT
View 3 Replies
View Related
May 21, 2001
I am new in this profession and have been hearing DBCC error. Could somebody please explain to me how to handle it in case I some day get it on my application. I do not want to wait until I face it before I start trouble-shooting what I do not know how to go about it. Thanks for your kind help.
View 2 Replies
View Related