April 20, 2011 at 4:21 am
Hello all,
I have the script below which should gather information about database space but when I try to run it it hangs.
The server is 2005 sp3. I believe that something is getting blocked(sys.allocation_units) and I don't know how to find that.
declare @db_name sysname,
@version varchar(4)
/*
***********************************************************************
* Get server version and set cursor up for databases (only non-
* suspect).
***********************************************************************
*/
SET NOCOUNT ON
select @version = substring(@@version,23,4)
if @version = '7.00'
declare db_cursor cursor for
select name
from master..sysdatabases
where databaseproperty(name,N'IsShutdown') <> 1 and
databaseproperty(name,N'IsInRecovery') <> 1 and
databaseproperty(name,N'IsNotRecovered') <> 1 and
databaseproperty(name,N'IsOffline') <> 1 and
databaseproperty(name,N'IsSuspect') <> 1 and
has_dbaccess(name) = 1
else
exec ('declare db_cursor cursor for select name from master..sysdatabases where databasepropertyex(name,''status'') not in (''SUSPECT'', ''OFFLINE'', ''RESTORING'', ''RECOVERING'') and has_dbaccess(name) = 1')
/*
***********************************************************************
* Create temporary tables needed to hold space information
***********************************************************************
*/
create table #dbspace (database_name sysname,
total_space decimal(35,2),
used_db_space decimal(35,2) NULL,
total_log_space decimal(35,2))
/*
**********************************************************************
* Open database cursor and loop through all databases on server to
* collect their database space information
***********************************************************************
*/
open db_cursor
fetch db_cursor into @db_name
while @@fetch_status = 0
begin
insert into #dbspace (database_name,total_space,used_db_space,total_log_space)
EXEC ('use [' + @db_name +
'] select db_name = db_name(),
1,
total_db_used = (
SELECT (SUM(CONVERT(real, case type
when 2 then used_pages
else data_pages end)) * (select low
from master.dbo.spt_values
where number = 1
and type = ''E''))/1024/1024
FROM sys.allocation_units),
1' )
fetch db_cursor into @db_name
end
select * from #dbspace
deallocate db_cursor
drop table #dbspace
go
Any ideas?
Thank you
April 20, 2011 at 4:54 am
Take a look at sys.dm_exec_requests and sys.dm_os_waiting_tasks and you can tell exactly what the process is waiting on and if it's blocked.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 20, 2011 at 11:20 am
Thanks.
We did that and it seems like there is a Citrix server connection who seems to be the blocker. We will try to stop that connection when possible an give it a try.
Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply