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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
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