April 24, 2007 at 3:57 am
Hi All,
since the last upgrade SQL 2000 --> 2005, the biggest database I am working on (1.4 GB, with loads of binary data) keeps freezin at least one time per week. When I say 'freezin' I mean that you cannot query some of the tables as it gives a timeout error when trying.
I am not a DBA and I don't have any idea of what to do, the only thing which comes to my mind is that this database is too big, as if I go to the preperties window it says Space Available: 38 MB (what's that? there's really only 38 MB on this DB?).
I tried re-writing all mainteinment plans (full backup every night, log backup every hour), but I realized that the database keeps freezing (and the backup jobs keep running untill you stop them).
anyone could give any hint?
Thanks in advance,
Giovanni Idili
April 24, 2007 at 5:15 am
- run sql-profiler to figure out what's going on
- find out what's blocking the server :
http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/default.mspx?mfr=true
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 24, 2007 at 5:34 am
Thanks for your answer,
but I've never ran SQL Profiler before, how can I run it?
I am looking at the link you posted but I don't really fell home as I am not familiar with the concepts.
Anyway thanks for putting me in the right trail!
Cheers,
Giovanni
April 24, 2007 at 5:38 am
SQL Profiler :
start \ programs \ microsoft sqlserver 2005 \ performance tools \ sql profiler
I'd start with the default presented trace.
Keep in mind this may have impact on your server !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 24, 2007 at 5:40 am
Ok.
2 simple ones abouti it:
1) Should I start it when I am experiencing the issue or anytime?
2) Which kind of impact could it have on the server?
Thanks!
Giovanni
April 24, 2007 at 5:45 am
When you are experiencing the issue, run the query mentioned at the hyperlink. It will show you who is blocking who, this may get you on track.
You can run profiler anytime, the trick is to capture the execution tree.
The impace profiler may have depends on the type and amount of info you want to capture.
The default trace, hasn't got that much an impact, but if you capture e.g. all events, the overhead may be huge?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 24, 2007 at 6:00 am
OK, thanks.
Do you think there's something I can do about this before the issue presents itself again?
Cheers,
G
April 24, 2007 at 6:12 am
By default, we add startuptraces for deadlockdetection at all our servers.
you can use this script to add the startup parameters.
If you also want to start the traces now, without restarting sqlserver, uncomment the last line and execute the batch.
declare @MaxSQLArg varchar(50)
declare @NewSQLArg varchar(50)
declare @NewSeq int
set @NewSeq = 0
Declare @TraceFlag Nvarchar(50)
create table #tmpRegValues (Value varchar(50), Data varchar(1000))
create table #tmpNewRegValues (Value varchar(50), Data varchar(1000))
insert into #tmpRegValues
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
-- next SQLArg.
select @MaxSQLArg = max(Value) from #tmpRegValues
set @NewSeq = convert(integer, substring(@MaxSQLArg,7,len(@MaxSQLArg)))
if @@version like '%SQL Server 2005%'
begin
set @TraceFlag = '-T1222' -- More detailed deadlock info
if not exists (select * from #tmpRegValues where Data =@TraceFlag)
begin
print 'adding traceflag ' + @TraceFlag
--Bepalen volgende SQLArg.
select @MaxSQLArg = max(Value) from #tmpRegValues
-- set @NewSeq = convert(integer, substring(@MaxSQLArg,7,len(@MaxSQLArg)))
set @NewSeq = @NewSeq + 1
set @NewSQLArg = substring(@MaxSQLArg,1,6) + convert(varchar(5),@NewSeq)
print @NewSQLArg
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', @NewSQLArg, N'REG_SZ', @TraceFlag
end
else
begin
print @TraceFlag + ' allreaddy installed'
end
end
else
begin
Set @TraceFlag = '-T1204' -- print deadlock info
if not exists (select * from #tmpRegValues where Data =@TraceFlag)
begin
print 'adding traceflag ' + @TraceFlag
set @NewSeq = @NewSeq + 1
set @NewSQLArg = substring(@MaxSQLArg,1,6) + convert(varchar(5),@NewSeq)
print @NewSQLArg
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', @NewSQLArg, N'REG_SZ', @TraceFlag
-- voorbeeldje exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SQLArg3', N'REG_SZ', N'-T1204'
end
else
begin
print @TraceFlag + ' alreaddy installed'
end
end
set @TraceFlag = '-T3605' -- write deadlock info to SQLServerSystemLog
if not exists (select * from #tmpRegValues where Data =@TraceFlag )
begin
print 'adding traceflag ' + @TraceFlag
set @NewSeq = @NewSeq + 1
set @NewSQLArg = substring(@MaxSQLArg,1,6) + convert(varchar(5),@NewSeq)
print @NewSQLArg
exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', @NewSQLArg, N'REG_SZ', @TraceFlag
-- voorbeeldje exec xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters', N'SQLArg4', N'REG_SZ', N'-T3605'
end
else
begin
print @TraceFlag + ' alreaddy installed'
end
if @NewSeq > 0
begin
Print 'old values'
select * from #tmpRegValues
insert into #tmpNewRegValues
exec master..xp_instance_regenumvalues N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
Print 'New values'
select * from #tmpNewRegValues
end
drop table #tmpRegValues
drop table #tmpNewRegValues
--dynamic activation
-- dbcc traceon(1204, 1222, 3605, -1)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 24, 2007 at 6:26 am
OK,
many thanks once again for your help!
Giovanni
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply