April 28, 2008 at 5:32 am
This might be due to maintenance plan that runs every Monday.
The following is one of the error that is coming up
"Executed as user: M01\SQL_service. Cannot shrink file '2' in database 'Siebel' to 12800 pages as it only contains 10888 pages. [SQL 1000] (Message 7993) DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528). The step succeeded."
the log is "dbcc shrinkfile(siebel_Log,100,TRUNCATEONLY)". Here the command is trying to shrink the file to 100 MB. As the shrink is not able to make it to 100 MB, the step is maintenance plan is failing.
Can you please look in to is and give any suggestion how we can fix this now and not to repeat in future.
April 28, 2008 at 5:57 am
Cannot shrink file '2' in database 'Siebel' to 12800 pages as it only contains 10888 pages. i.e. you are trying to shrink 10888 pages to 12800 pages which is not possible.
Suggestion: Keep as it is 100 MB is not a big size.
Still if you would like to perform the task; Increase the filesize to around 20000 from 10888 and than shrink to 12800. :hehe:
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 28, 2008 at 8:51 am
DO NOT SHRINK your databases as part of a maintenance plan. This can cause fragmentation and ruin your reindex work.
Remove this as part of your maintenance plan.
April 29, 2008 at 3:12 am
I completly agree with Steve. My policy is never shrink a data or log file if you expect it to grow within the next 3 months. Even then, have a good reason for doing the shrink.
Repeated shrinking and growth of database files will result in NTFS file fragmentation, which can only be cured by a NTFS defrag. Excessive NTFS file fragmentation will harm performance, regardles of how well organised the data is within the file.
Shrinking of data files will also give internal database fragmentation of indexes, which is bad for performance. This can only be cured by rebuilding the indexes, which in turn will cause the database file size to grow.
You need to work out how big your data and log files need to be to cope with your workload, and plan to have that amount of disk space available. You should also monitor growth trends and request enough disk space is provided to cope with the next few months growth.
The size of log files will depend on the rate of change to your data, the duration of your transactions, your configuration of replication, and the frequency of log backups. Increasing the frequency of log backups can reduce the maximum size your log neds to be (but you will still have to adjust the size manually). However, unless you need the log data for log shipping or other uses, there is no point in doing log backups more frequently than the duration of your longest transactions. For many sites, a log backup every hour is a good compromise if log shipping is not in use.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 8, 2015 at 8:25 am
Yes, I totally agree that you should not shrink your files as part of a maintenance plan. However I have run across the same message and I have a valid reason to shrink my data files.
"Cannot shrink file '1' in database 'TNT' to 32768000 pages as it only contains 32243712 pages."
Odd that SQL Server cannot shrink it to more pages that the file has.
The reason why I need to shrink the file is that we are creating a new file group and plan to move a bunch of archived data out of these data files. I need to remove a bunch of empty space from the existing files to make room for the new data files that will store the archived information.
As we move the data in chunks, I will need to perform addition shrinking on the current data files and grow the new archived data files.
So aside from "you should never shrink your data files" advice, why is this error happening and why can't I shrink my files even though there is plenty of room to shrink the files. One other thing to note, I am working with 8 data files that are 250 MB each and have between 39 and 43 percent of free space. I would like to reduce that to 10-15 percent of free space.
Thank you in advance for any help you can provide.
Pat B
Pat Buskey
April 8, 2015 at 9:41 am
Looks like I might have fat fingered the size and typed a size larger than the current file size which would explain the error message.
Pat Buskey
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply