Prev: Missing Object xp_cmdshell
Next: Batch File ???
From: Bernd Maierhofer on 5 Aug 2010 03:43
> Does not seem to be related to indexing, but if there are fulltext
> queries running simultaneous, the reindexing could be the victim.
Not that I know of. But I will have a look at this.

> No, this is very unlikely. Your database is 30 GB which is fairly modest.
30 GB is not one of the largest DBs, but "fairly modest" is very dependent
of your point of view. AFAIK 30 GB ranges in the top 20% of MSSQL database
sizes.

tx Bernd


From: Erland Sommarskog on 5 Aug 2010 14:51
Bernd Maierhofer (bernd.maierhofer(a)dato.at) writes:
> 30 GB is not one of the largest DBs, but "fairly modest" is very dependent
> of your point of view. AFAIK 30 GB ranges in the top 20% of MSSQL database
> sizes.

Depends on what it is 20% of. Maybe 80% of all SQL Server databases
are less than 30GB. However, I don't think that 80% of the data that
lives in SQL Server databases lives in databases less than 30 GB.

I believe most of the production databases of the system I work with are
> 30 GB, the biggest is some 700-800 GB by now.

In any case, the point is that SQL Server does not choke because you
reindex a 30GB database. So I suspect that there is something else going
on at your server.

There are a couple of DMVs to investigate memory usage, but it is not
entirely easy to grasp. And if the memory eater is a black hole like an
extended stored procedure, it is even more difficult to find.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Bernd Maierhofer on 6 Aug 2010 02:23
> Depends on what it is 20% of. Maybe 80% of all SQL Server databases
> are less than 30GB. However, I don't think that 80% of the data that
> lives in SQL Server databases lives in databases less than 30 GB.
:-) Well said!


> In any case, the point is that SQL Server does not choke because you
> reindex a 30GB database. So I suspect that there is something else going
> on at your server.
This is, what I believin , too. However, this is a plain new server, and I
did nothing but assembling a maintainance job by clicking the options.
Scheduled for sunday. And on sunday - peng.

There are no SP running.

I disabled the jobs and will see what happens.

Thanks for your input!

Bernd


From: Erland Sommarskog on 6 Aug 2010 17:07
Bernd Maierhofer (bernd.maierhofer(a)dato.at) writes:
> This is, what I believin , too. However, this is a plain new server, and I
> did nothing but assembling a maintainance job by clicking the options.
> Scheduled for sunday. And on sunday - peng.

That's an interesting piece of information. That makes it less
likely there is something within SQL Server stealing the memory.

Have you checked the SQL Server error log, if there are any interesting
messages, like about part of the memory being swapped out? Or anything
else that explains why SQL Server stops responding.

Do you know if the service account has the permission Lock Pages in
Memory? And exactly which edition and version (from @@version) do
you have?

Also, it could be interesting to schedule this query to run every 10
minuutes or so, and insert the data into a table:

SELECT physical_memory_in_use_kb,locked_page_allocations_kb,
page_fault_count, memory_utilization_percentage,
available_commit_limit_kb, process_physical_memory_low,
process_virtual_memory_low
FROM sys.dm_os_process_memory;



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

First  |  Prev  | 
Pages: 1 2
Prev: Missing Object xp_cmdshell
Next: Batch File ???