December 13, 2011 at 10:57 am
I have so interface that leave iterations of data in my database as tables. They are supposed to clear after 10, but the process that clears them stopped working, and I had months worth of them, and my .MDF file had grown to about 40 gig. I cleared out the extract tables, and went to the shrink page, and it told me the files were about 60% empty. I kicked of a shrink (the server had run out of room), but almost an hour and a half later it is still spinning. How long should this process take normally? Thanks.
December 13, 2011 at 11:04 am
it all depends on which other options you selected in the GUI, for instance did you elect to "reorganise pages before releasing unused space"?
Remember to leave enough free space for data growth and maintenance operations
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 13, 2011 at 11:05 am
Does this return anything?
SELECT r.[session_id]
, c.[client_net_address]
, s.[host_name]
, c.[connect_time]
, [request_start_time] = s.[last_request_start_time]
, [current_time] = CURRENT_TIMESTAMP
, r.[percent_complete]
, [estimated_finish_time] = DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP)
, [estimated_seconds_left] = DATEDIFF(s, CURRENT_TIMESTAMP, DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP))
, current_command = SUBSTRING ( t.[text], r.[statement_start_offset]/2, COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647) )
, module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_connections AS c
ON r.[session_id] = c.[session_id]
INNER JOIN sys.dm_exec_sessions AS s
ON r.[session_id] = s.[session_id]
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.[percent_complete] <> 0;
December 13, 2011 at 11:49 am
Sean Grebey-262535 (12/13/2011)
How long should this process take normally? Thanks.
Depends. Got heaps or LOB columns?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 12:10 pm
Sean Grebey-262535 (12/13/2011)
I kicked of a shrink (the server had run out of room), but almost an hour and a half later it is still spinning. How long should this process take normally? Thanks.
To add to Gail's question, this database IS considered under maintenance and no users are currently attempting access, right?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 13, 2011 at 1:59 pm
Evil Kraig F (12/13/2011)
Sean Grebey-262535 (12/13/2011)
I kicked of a shrink (the server had run out of room), but almost an hour and a half later it is still spinning. How long should this process take normally? Thanks.To add to Gail's question, this database IS considered under maintenance and no users are currently attempting access, right?
Yeah Database is only used at night when the interfaces are run. Shrink finished after about 2 hours.
December 13, 2011 at 2:17 pm
Cool. Now go rebuild all of your indexes to fix the fragmentation that the shrink caused.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 2:36 pm
Sean Grebey-262535 (12/13/2011)
Shrink finished after about 2 hours.
How much free space did you leave?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply