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.
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?
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?
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?
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?
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?
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.
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:
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
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
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.
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?
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.
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.
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)
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.
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
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??
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.
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?
I followed the advice of Paul Randal, but Im stumped as I am not able to determin what the corruption issues are. This is SQL 2000 and the database is a Solomon database that was recently upgraded to 6.5. the error I get when running the DBCC checkdb is as follows:
Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:18645) with latch type SH. sysindexes failed. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, the bad news. I am a bit of a novice and have picked this up from someone who left my company. It appears the latch error has been around for some time and only reared up when I instituted a new back up system that runs a dbcc check befor backing up. I don't think I have any clean backups.
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.
DBCC results for 'DocCollections'. DBCC results for 'sysobjects'. There are 116 rows in 4 pages for object 'sysobjects'. DBCC results for 'sysindexes'. There are 148 rows in 7 pages for object 'sysindexes'. DBCC results for 'syscolumns'. There are 666 rows in 12 pages for object 'syscolumns'. DBCC results for 'systypes'. There are 26 rows in 1 pages for object 'systypes'. DBCC results for 'syscomments'. There are 157 rows in 15 pages for object 'syscomments'. DBCC results for 'sysfiles1'. There are 2 rows in 1 pages for object 'sysfiles1'. DBCC results for 'syspermissions'. There are 104 rows in 1 pages for object 'syspermissions'. DBCC results for 'sysusers'. There are 108 rows in 2 pages for object 'sysusers'. DBCC results for 'sysproperties'. There are 1702 rows in 26 pages for object 'sysproperties'. DBCC results for 'sysdepends'. There are 474 rows in 3 pages for object 'sysdepends'. DBCC results for 'sysreferences'. There are 2 rows in 1 pages for object 'sysreferences'. DBCC results for 'sysfulltextcatalogs'. There are 0 rows in 0 pages for object 'sysfulltextcatalogs'. DBCC results for 'sysfulltextnotify'. There are 0 rows in 0 pages for object 'sysfulltextnotify'. DBCC results for 'sysfilegroups'. There are 1 rows in 1 pages for object 'sysfilegroups'. DBCC results for 'tblCommentsArchive'. There are 64678 rows in 395 pages for object 'tblCommentsArchive'. DBCC results for 'tblLoanConditionsDeleted'. There are 0 rows in 0 pages for object 'tblLoanConditionsDeleted'. DBCC results for 'dtproperties'. There are 7 rows in 1 pages for object 'dtproperties'. DBCC results for 'LoanInfoFix'. There are 52421 rows in 166 pages for object 'LoanInfoFix'. DBCC results for 'tblLoanConditionsFIX'. There are 392167 rows in 2099 pages for object 'tblLoanConditionsFIX'. DBCC results for 'tblLoanConditions'. There are 743281 rows in 6004 pages for object 'tblLoanConditions'. DBCC results for 'Conditions'. There are 144 rows in 2 pages for object 'Conditions'. DBCC results for 'REGIONS'. There are 51 rows in 1 pages for object 'REGIONS'. DBCC results for 'RegionsPhil'. There are 53 rows in 1 pages for object 'RegionsPhil'. DBCC results for 'DLQ'. There are 13569 rows in 34 pages for object 'DLQ'. DBCC results for 'CountyList'. Server: Msg 2511, Level 16, State 2, Line 1 Table error: Object ID 2073058421, Index ID 0. Keys out of order on page (1:10386), slots 17 and 18. Server: Msg 8978, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. Page (1:759) is missing a reference from previous page (1:10386). Possible chain linkage problem. Server: Msg 8935, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. The previous link (1:10386) on page (1:10387) does not match the previous page (1:759) that the parent (1:10326), slot 81 expects for this page. Server: Msg 8936, Level 16, State 1, Line 1 Table error: Object ID 2073058421, index ID 1. B-tree chain linkage mismatch. (1:759)->next = (1:10387), but (1:10387)->Prev = (1:10386). There are 3022 rows in 13 pages for object 'CountyList'. DBCC results for 'ContactPreference'. There are 4 rows in 1 pages for object 'ContactPreference'. DBCC results for 'Broker'. There are 21614 rows in 334 pages for object 'Broker'. DBCC results for 'Conversion Errors'. There are 2 rows in 1 pages for object 'Conversion Errors'. DBCC results for 'LoanInfo'. There are 271755 rows in 8970 pages for object 'LoanInfo'. CHECKDB found 0 allocation errors and 4 consistency errors in table 'LoanInfo' (object ID 2073058421). DBCC results for 'SettlementAgent'. There are 285013 rows in 4143 pages for object 'SettlementAgent'. DBCC results for 'statelist'. There are 51 rows in 1 pages for object 'statelist'. CHECKDB found 0 allocation errors and 4 consistency errors in database 'DocCollections'. repair_rebuild is the minimum repair level for the errors found by DBCC CHECKDB (DocCollections ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I'm running MS SQL 2000 sp3 under 2000 server sp4. I lost a hard drive in a RAID5 and afterwords my weekly maintenance (rebuild and check links) errored five days later. I'm on a weekly backup cycle and running Great Pains Dynamics 6.0. Can't go back now. Here's what dbcc checkdb found on my 5 gig db.
DBCC results for 'UPR41400'. There are 5 rows in 1 pages for object 'UPR41400'. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 171147655, index ID 0: Page (1:192881) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 171147655, index ID 0, page (1:192881), row 56. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 4203 and 111.
I'm keeping a warm backup server up to date so I can run whatever you suggest on it without harming my production db further. I'm monitoring the db daily and after seeing some other posts, I realize i'm very lucky this is the only error. At the end of the messages it informs me that 'repair_with_allow_data_loss' is my first option. The strange thing is I can return all rows on the table without error. It's a tax table. Do I have any other options? Thanks
