How to accurately track how much memory was granted to a DBCC checkdb during it's execution?
I'd like to do this without continuously querying dm_exec_query_memory_grants, if at all possible - but if that's the only option.
Our last CheckDB failed on the 02/09. We were having problems with our SAN then and that got sorted out. The databases that it failed still has the snapshot showing in SQL Management Studio, screenshot exemple below (server name blanked out)
I reran CheckDB on the 04/09 on all the databases and the same databases that failed on the 02/09 failed again. I noticed in the log file, it tried to use the same snapshot file created on 02/09 to run CheckBD
I ran CheckDB manually on the databases that failed "DBCC CheckDB (WSS_Content_ce) with all_errormsgs" and did not get any errors back.
How do I remove the snapshot databases? We had a problem on 02/09 so the snapshot copy obvisouly has problems. I'd like to remove these snapshots and rerun a CheckDB on all the databases to ensure all is ok.
I'm trying to grant a read-only permission in sql 2014 after a restore and I wanted to find out what it is in SQL 20114?
View 4 Replies View Related
I have a 2GHZ cpu with 1GB of RAM. I occassionally see very slow (long) queries against a local SQL Server 2005 Express (SP2) database. The issue occurs against different SQL Queries, but all queries are rather basic select statements Perfmon shows that the SQL Server counter for the "MEMORY GRANT QUEUE WAIT Avg MS" gets extremely high (25000+ ms). Perfmon also also shows that PAGING is not occuring, and the system is not under unsual stress. The problem is not reproducible with MSDE.
Has anyone seen this issue, or have any recommendations for a next course of action?
Is there any easy way to detect SQL Server memory is getting paged out? Any DMV query or tips to confirm it is paging out.
My SQL Server Version is: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Is it available by default or it has to be Configured.
If yes then how to configure Tempdb in Memory for SQL 2012 SP2 CU1?
Is there has any SQL query to check running out of "min memory per query SQL Server"(default value is 1024KB)?
View 2 Replies View RelatedHow to set maximum server memory by using sql scripts in sql server 2014?
View 5 Replies View RelatedWe have Windows Server 2008R2 installed on VM Server.On that we have three SQL instances running. From few past months we are observing physical memory is going high. Earlier we observed it was at 86-88%, now it is 96-97%.We have 16RAM & 8 CPU cores on VM. what is the best and ideal configuration so that we can rectify the high physical memory issue.
View 13 Replies View RelatedWe have a new set up on VM to run an application running 24*7 (migrated from SQL server 2008R2) with below configs:
1. OS- Windows server 2012 Standard 64 bit hosted on Virtual Machine
2. Memory 16 GB and Cores =4 with 2.4GhZ processor
3. SQL server 2012 SP2 , 64 bit Standard edition.
4. Total size of databases as of now 15 GB with biggest being 5 GB.
How should i go around in setting the MAX and MIN server memory settings. I have this set up for many of SQL 2005 and 2008R2 servers, but for 2012 i heard that things has slightly changed.
How should i start analyzing and setting the right value of this MAX and MIN?
Eager to know to set the Max Value of Maxtransfersize and Buffercount adjustment but also anxious to know Physical memory impact.URL...The max size that can be used is the (Buffer Pool’s To Physical Memory / 16). As returned from the GlobalMemoryStatusEx (ullTotalPhys ) API call. HereBob dorr saysBuffer Pool’s To Physical Memory/16 what it means? (ie. Buffer Pool/ Physical memory/16.0 or (Physical Memory - buffer pool)/16
View 9 Replies View RelatedOne server, a part of a 2 node always on cluster, had problems, I had to restart.
I'm getting this error for example now:
Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
+
SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes.
Now, on the instance I have activated maximum servere memory, my question; Is it possible to adjust this setting without starting the service?
Some parameter for example?
The server itself have enough memory.
GRANT SELECT ON [dbo].[TblAreaCatmap] TO [admin] prevent grant from being automaticly add to each column?
Is there a way when you issue a grant select to a table or a view to not also grant select for each column.
The problem is when you use the grant command it automaticly adds the grant command to each column. I want to grant the permission at the table level so when the table is scripted it only has a single grant command instead of a grant for the table and a grant for each column which is not needed.
The sql managemnt studion interface will allow you to do this but onlt by using the interface. If you issue the above command from a query window it also creates A GRANT FOR EVERY COLUMN. How can I stop this behavior.
If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
On first Node A: The server has 16 GB of physical RAM.
On second Node B: The server has 10 GB of physical RAM.
Now, this being Active Active, Node A can be clustered on failure onto Node B..Now reporting server is configured under these two nodes, with defined MAX and MIN server memory of 12 as MAX and 0 is min IN GB.Now with this setting on SQL whenever the cluster moves, such config make OS goes low on node for 10 GB.I am only left with option of switching this MAX and MIN to a default setting or is there any other alternative such as script which can change this setting accordingly when cluster moves to respective server.
I am searching for query to find total memory allocated to sqlserver, and how much being used utilized as well cpu utilization percentage .
View 10 Replies View RelatedAfter I have set the maximum sql server memory in sql server 2014 using sp_configure, do I need to reboot whole server or restart sql service?
View 4 Replies View Related1. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index. do I need and code changes for application and how to store tables /indexes in OLTP memory
Assume table index may have primary key index as well.
2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.
3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. This would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@UserID int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @UserID
execute SQL:
EXEC schemaname.SetUserCulture @UserID = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
In SQL Server 2005 SP2 I want to grant the ability to create views to a user but in order to do this it requires that the users has the ability to grant alter on a schema.
Is there any way to grant this privilage without granting alter on schema also?
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
My Integrity job started failing recently with the following error. Msg 701, Level 17, State 123, Line 1 There is insufficient system memory in resource pool 'default' to run this query. Process Exit Code 1. The step failed.
select @@ version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
System is having 4GB RAM and SQL is using most of it. It has 2 processors.
In SQL Server 2014, how big for the block size is better for performance? 64 KB? 4 KB?
For normal database files, best practise is 64 KB disk block size. Not sure if it is same for memory-optimized filegroup.
I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets()
at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
I have a Windows sever 2012 with sql server 2012 enterprise. Ram size is 22GB. Sometimes SQL sever takes 95% memory.My question, How to reduce memory size without killing any process because it's production server.So there are many background process is running. And,Is there any guides to learn why Memory is raise d so high and how to reduce it.
View 10 Replies View RelatedHello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
I am running Visual Studio 2005. I have an SSIS Package which is consuming a huge amount of memory. During the execution of the package the memory keeps increasing. Until finally i get an Out of Memory exception. I have run this package using dtexec, and in the BIDS. No difference. I do have some script components and have added some code to get the assemblies in the current appdomain. I do see that one particular assembly is increasing on every loop. VBAssembly every time it hits the script component is increasing by 6, and along with it the memory is climbing. What is this VBAssembly being used for is there an update to SQL Server Integration Services that I need?
Thanks! Aaron B.
sql server 2000 is running on windows server 2003 ... 4gb of memory on server .... 2003 was allocated 2.3gb nd sql server was allocated (and using all of it) 1.6gb for total of approx 4gb based on idera monitor software ... all memory allocated betweeen the OS and sql server .... then 4 more gb of memory added for total now of 8g ... now idera monitor shows 1.7gb for OS and 1.0 gb for sql server ..... 'system' info shows 8gb memory with PAE ... so I assume that the full 8gb can now be addressed .... why are less resources being used now with more total memory .... especially sql server ..... i thought about specifying a minimum memmry for sql server but i amnot convinced that would even work since it seems that this 1gb limit is artificial .... it it used 1.6 gb before why would it not use at least that much now ??
thank you
i want to create a lot of index for my database for performance.but i need find memory usage by indexes.
How to find memory usage by index in sql server?
I've a database with a memory optimized filegroup on it. How can I remove it?I have removed the memory optimized table I had on it, but when I try to remove the filegroup I receive an error.
View 12 Replies View RelatedHi,
I am going to install SQL Server 2000 (then SQL 2K5) on a Win Server 2K3 with 8 GB of ram, but it will be 16 GB in the near future.
I would like to reserve a fixed memory (for momemt less than 3-4 GB) for SQL Server and the rest for application (virtualization).
Without AWE enabled, max memory for SQL Server 2K5 is 4GB as for SQL Server 2000?
How can I manage and optimize memory keeping in mind AWE. (any doc, website available?)
Thank