March 31, 2008 at 12:16 pm
Select into query that ran for several hours failed and table got truncated ... what might be the reasons? In the db I have two datafiles... with unrestricted growth and the space allocalted are
13834 MB and 12576 MB. What can be the problem?
one of the sql server logs were:
Autogrow of file 'filename2' in database 'dbname' cancelled or timed out after 4094 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size. Is this the problem?
March 31, 2008 at 12:56 pm
Change the autogrowth to a fixed size. With an increase of 10% every
resize needs more disk space and resources seeing your database size.
Also for 'select into' change recovery model of database to bulk_logged which will do minimal logging.
Suggest to take full backup and transaction log backup to free up space and recovery..
Also do you see any message in error log related to DBCC CHECKDB after this error.
NJ
March 31, 2008 at 1:09 pm
Where can I see the error log? I can see the activity log from which I pasted the log... The DTS has not logged the history anywhere for now. Thanks for your suggestions.
April 1, 2008 at 6:01 am
Management Studio -> Object Explorer -> YourInstance -> YourDatabase -> Management -> SQL Server Logs
Best practices for managing data file growth are:
- manually size your data files to eliminate the need for auto-growth
- leave auto-growth enabled so an unforeseen event (that needs lots of space) doesn't cause the database to stop
- enable instant file initialization so auto-growth doesn't wait while the file is zero-initialized (see 'Instant File Initialization' in the index in Books Online for details)
Be careful about changing to bulk-logged recovery model - any transaction log backup that contains minimally logged operations cannot be used for point-in-time recovery (i.e. you can't specify STOPAT to a time covered by that log backup).
No backup will *ever* affect the data file size. In fact no backup will *ever* directly affect the log file size either. A transaction log backup may allow some of the transaction log to be 'cleared', and then you can explicitly shrink the transaction log file size - but a backup never shrinks a file.
Not sure why you mention DBCC CHECKDB - looks like a simple timeout due to the auto-growth size being set too high - nothing to do with corruption.
Hope this helps
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
April 1, 2008 at 7:04 am
Paul, anything comparable to 'Instant File Initialization' in SQL2K? We're a ways away from SQL2K5 (probably going to SQL2K8) so I'm hoping there is something in our version that I can use. Thanks.
-- You can't be late until you show up.
April 1, 2008 at 7:08 am
No - unfortunately not.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply