SQL2005, Managing Memory
Jan 24, 2008
Hello~
I operate MsSql server with Maximum Server Memory.
The server has 2GB main memory and Maximum Server Memory is 1GB.
If sqlserv.exe use 1GB main memory, new query is not cached.
I think, sql server release pages in main memory to cache new query. but no release.
By above reason, performance of new query is not good.
How can sql server cache new query?
-- Memory Setting --
MIN : 0, MAX : 1GB
Because my English is poor, I am sorry.
View 5 Replies
ADVERTISEMENT
Apr 17, 2007
Hi,
I've been running SQL2000 for the past few years with 2gb of RAM and just recently upgraded to SQL2005 with 4 gigs.
My old system would always have the sqlservr.exe process using as much RAM as it could, this number would never really drop, maybe if it did it was in the single digit percentages.
I was just setting up new maintenance plans on my server, and I noticed from the time I started to the time I finished the SQLSERVR.EXE process dropped from using 3.5gb of RAM to just 1.5GB of RAM right now. It's pretty steadily going up too.
Does sql2005 have some new advanced memory management features? Did creating a sql maintenance plan release some memory? Did sqlserver crash ? I've never seen this before.
Thanks very much! :)
mike123
View 2 Replies
View Related
Feb 20, 2007
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.).Net SqlClient Data Provider at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
at System.Data.SqlClient.TdsParserStateObject.ReadUInt32()
at System.Data.SqlClient.TdsParser.ReadSqlValueInternal(SqlBuffer value, Byte tdsType, Int32 typeId, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumnHeader(Int32 i)
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetInt32(Int32 i)
Ive just started getting this on a stable application thats used a datareader on millions of records.
Not sure where to got from here and I can't find anyone else whos getting the failure during the processing.
I could disable shared memory protocol but that seems extreme. I'm on Sql Enterprise 9.00.2047. Maybe the process is hammering the server very hard? Personally I've rarely ever seen SQL be the cause of an error, only user config, bad disks or power issues.
I'm running the app again with SQL Profiler capturing "standard" events.
Just need it to blow up again.
I can run the app on another machine of course and I wouldn't get Shared Memory Provider being used. Maybe I ought to do that as well. At least if the error is not really in the Shared Memory I'd have another avenue to explore.
Anthony
View 5 Replies
View Related
Mar 28, 2007
Currently, we have the following setup...
SQL 2005 Std, 8gb ram, boot ini switches for /PAE and /3GB, AWE enabled and the min/max memory settings for a single instance respectively set to 1024 and 7168. Performance-wise the system is a bit slower than before (with SQL2000) and there seems to be alot of paging. The SQL service is also running under the "LocalSystem". In this case, do I need to specify the local "SYSTEM" to have rights to "Lock pages in memory"? I have a feeling there is a misconfiguration somewhere. Can someone point me in the right direction? Thanks.
View 4 Replies
View Related
Dec 15, 2005
hi
i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
pls some one can help me ???
thanks
chaus
View 62 Replies
View Related
Oct 12, 2007
Can you open/use a database created in SQL2005 in SQL2005 Express?
Thanks for the help!
Max
View 4 Replies
View Related
Dec 27, 2005
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View 3 Replies
View Related
Jan 4, 2007
Hi
We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,
currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.
is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.
This has to be done for our production and very critical, please help
Thanks
Samuel I
View 4 Replies
View Related
Sep 19, 2007
I am unable to install 32-bit SQL Server Integration Services on the server due to something that was left behind by the 64-bit version.
I've uninstalled SQL Server 2005 64-bit and when I try to install the 32-bit version of Integration Services, I get this error: "Failed to install and configure assemblies C:Program Files (x86)Microsoft SQL Server90DTSTasksMicrosoft.SqlServer.MSMQTask.dll in the COM+ catalog. Error: -2146233087 Error message: Unknown error 0x80131501 Error descrition: FATAL: Could not find component 'Microsoft.SqlServer.Dts.Task.MessageQueueTask.ServCompMQTask' we just installed."
I can't seem to figure out how to resolve this problem with the COM+ and I can't remember if Integration Services is required.
Can anybody please advise?
View 1 Replies
View Related
Jul 8, 2007
Hello,
I have a vb program that include a dts package that has been saved to vb with sql2000 dts wizard and works very good.
Now that I upgrade my website to sql2005, this vb dts package doesn't work.
The error I get is:
Microsoft Data Transformation Services (DTS) Package
Invalid STDGMEDIUM structure
(Microsoft Data Transformation Services (DTS) Package (80040066): Invalid STDGMEDIUM structure
) (Microsoft SQL-DMO (ODBC SQLState: 42000) (80004005): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ')'.)
I searched in the internet how to make dts package in sql2005 and save it to vb and found no information about it.
What Can I do to get the vb code of the dts package I create in sql2005 or how do i migrage the sql2000 vb dts package code to sql2005?
Thanks,
Kubyustus
View 4 Replies
View Related
Jul 13, 2015
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?
View 4 Replies
View Related
Sep 28, 2007
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)
View 4 Replies
View Related
Oct 11, 2007
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.
View 3 Replies
View Related
Jul 5, 2004
Hi,
I have a page that shows some problem indexes (cards ? I don't know the word :/ ) and I want to show only those that are from the current day.
I'm using SQL server 2000. I have a date field inside my table (datetime type). So I tried to put another condition in my WHERE clause. This is:
WHERE something = something else AND mydate = DATEPART('dd', getdate())
or
WHERE something = something else AND mydate = DAY(getdate())
Both don't work..
I wonder if I can really use this in a WHERE clause...of if I'm using them correctly.
View 5 Replies
View Related
Feb 7, 2001
Does anyone know if it's possible to grant a user the ability to manage jobs in server agent besides giving them SA rights? I none of the server roles beside SA seem to be able to work.
Thanks
View 1 Replies
View Related
Aug 16, 2004
Hello,
I am currently working on a large project that will be developed using MS SQL. I have been considering two options when it comes the creation of the database. The first option is to create a sepereate table for each of my clients. The tables would be labeled by their Id number then the tables reqular name. Fore example : 781_Users.
The other option is to create a different database for each of the clients.
I just need the data to be seperated between clients, it cant mix. Please just give me your opinion on which option you would consider if you were operating a large project.
Thanks in advance!
View 2 Replies
View Related
Sep 27, 2004
Has anyone experienced any limitaions or concerns for managing SQL 2K remotely using remote desktop, pleased advise.
Thanks
View 1 Replies
View Related
Jan 9, 2008
Hello everyone. Have a little situation here. I am not normally the DBA at my work, but i've been poking around a little with a SQL Server 2005 implementation. I'm pretty new to this, so please pardon my lack of knowledge.
This particular server is running a lightly used SQL server, or so im told. As part of my Sys Admin duties, I logged into the box today for some routine checks, only to be notified that I was running out of disk space (which is another story).
After poking around awhile, I see that my tempdb is almost 15gb in size.
I don't have a lot of experience with SQL Server 2005. I've worked mostly with 2000. I did read a few things where tempdb has changed a bit in 2005? I wasn't sure.
So I have about 10gigs of space free on my HD (I'm adding more space tomorrow) and I need to figure out what to do.
I came across this thread here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64914
is that something I should do? I'm a little concerned I might run out of space overnight and not make it in time.
Anyone have some suggestions?
Thanks.
TCG
View 10 Replies
View Related
Jan 3, 2006
Hello,Has anyone ever come across a reason why someone would manually create auser table incl. permission flags and not use the inbuilt user/rolesprovided by that database? The only reason that stands out for me is tomake the database that bit more portable?Thanks,Craig.
View 2 Replies
View Related
Sep 12, 2006
Hi everyone,I'm looking into moving my desktop system at work to Linux from WindowsXP, and one tool I've yet to find a suitable replacement for is MS SQLEnterprise Manager. I mainly need to query MS SQL databases fromLinux, and management tasks (monitor backups, services, etc) can bedone on the MS SQL servers themselves. THere was an application I usedto use on OSX which would connect to a variety of databases, includingMS SQL, and they had a Linux version of the client .. but can'tremember the name. But really any Sybase client that can connect to MSSQL with some ease will work.Thanks for any suggestions or ideas ...Alex
View 5 Replies
View Related
May 15, 2007
I want to centralize my previous standalone application. Previous application was using VB.NET and Access XP. Now I want to keep a centralized database (SQL Server 2005) and VB.NET 2005. At this point of time thousands of concurrent users will connect to the database at the same time.
In my application, when a ticket is being issued to a tourist, an SQL query finds the max(ticketno) for the current month from the main table and reserves this number for the current ticket. When the operator has finished entering information and clicks SAVE button, the record is saved to the main table with this ticket no. Previously there was no issue since the database was standalone.
I want to know how to block the new ticket no so that other concurrent users are not using the same number for saving a record. How to better tune the database for thousands of concurrent users at the same time? I also want that the other user must not get an error message when he attempts to save a record. It should be automatically handled by the database.
View 8 Replies
View Related
Sep 5, 2007
Hello,I have an ASP.NET website which uses SQLServer 2000 as database. I am into tracking the customers who visit to the website. We are getting around 10000 users per day. When each page gives request, request details will be inserted into SQL tables.Table's I am using is indexed. I am in confusion that when these index will be regenerated ? Will it regenerated automatically when new row's are inserted ? or do we need to regenerate it manually ? And what is the difference on Clustured Indexing and Full text indexing ? Which one I should use for better performance ?Thanking youNavaneeth
View 6 Replies
View Related
Jul 4, 2006
Hi everyone,
When an error occured in a transaction and we can also create a message for this error ourself too by using @@error in order to print a message . However, I wonder that is it possible to really catch and handle the error ??
For example, when error occured it makes the transaction terminated. So how can we prevent transaction from terminating ??
Meanwhile, I also examine RAISERROR statement but it also does not prevent a transaction from terminating instead ÅŸt only provide an additional message for the error.
View 6 Replies
View Related
Aug 28, 2015
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 Related
Jun 26, 2007
I've combed through SQL Help to find the answer to my question but I think it's telling me it can't be done. I work both from an office with my servers and from home. When I'm at home I would like to access my SQL server remotely using a tool such as MS SQL Server Management Studio. But it appears there is no way to access my SQL Server for management purposes using Management Studio over a remote internet connection. I can access the server using Management Studio while I'm on the internal office network but not from home.
Has anyone been able to do this or might recommend a third party tool as robust as Management Studio?
Thanks
View 6 Replies
View Related
Nov 25, 2005
HiI'm trying to access the properties of my datasource programatically so I can change the stored procedure it connects to depending on the value of a querystringIn my page load I have
Dim myMode = Request.Params("mode")Select Case myMode Case "Category" sqldatasource1.SelectCommand = "productsbycategory" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add querystring parameter Case "Wishlist" sqldatasource1.SelectCommand = "productsbywishlist" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedure ******'Need to add Session variable parameter Case Else sqldatasource1.SelectCommand = "allProducts" sqldatasource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureEnd SelectThe problem that I have is that I need to pass a parameter to the first 2 procedures above, a querystring to the first and a session variable to the second.How do I add parameters programaticallyMany thanks
View 2 Replies
View Related
Jan 2, 2006
Hi, I just start using MSSQL server 2005, I already install vss studio
2005 so SQL server 2005 is already installed by default (express
edition).
But unlike SQL server 2000, I coultn't find any tools similiar to "sql
entreprise manager". I was able to create a new database only from
server explorer tools in VSS. But pretty strange that this database act
like a microsoft access database (creating a database file on my
app_data folder). (I am sorry I am new in SQL server 2005
ExpressEdition ).
My question :
1. is that (above) really how mssql server 2005 works?
2. how to create a new user for this database? (example user: sa password: "admin")
thanks
View 2 Replies
View Related
Apr 25, 2001
Does anyone have any pointers or suggestions in explaining this topic?
The topics are what's including: Managing Transactions, SQL Server Locking and Managing Locks.
View 3 Replies
View Related
Jun 11, 2001
Hi all
Does any one know abt the web interface for managing SQL SERVER
I would like to create database,create tables create users, modify, insert data etc.. over the web
i belive there are interfaces are available on the net
can any body tell me from where i can download it
Thanks in advance
Prasad
View 2 Replies
View Related
Oct 14, 2004
We have a convoluted DTS package. The package is stored and scheduled to run on database server1. Within the package we:
• Make a connection to database server2
• Execute a ‘Process Task’ to execute EXCEL.EXE that is installed on server3
• Pass parameters through the ‘Process Task’ direct EXCEL.EXE to open a .xls file on server4
• When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.
The questions are:
1.Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?
2.And how do we control the security context that executes Excel through this use of automation?
3.Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?
View 3 Replies
View Related
Feb 2, 2007
I'm fairly new to SQL server, and I'm helping someone who has a fairly high employee turnover rate.
I would like to create an interface that would allow her to add/edit/delete users and their permissions without having to get into management studio. I'll build a form in the front end application that will presumably pass parameters to a SProc that will do the work.
Her users fall into two categories - full and read only. The tasks she'll need to perform are very simple.
1) Add new user with full access to all tables
2) Add new user with read only access to all tables
3) Change user access from read only to full
4) Change user access from full to read only
3) Delete user
Can someone point me in the right direction on this? Some sample code would be nice, but at the very least I'd like a better term to search by..
Searching for "stored procedure user permissions" brings up tons of hits that mostly deal with who can and can't run a sproc..
View 14 Replies
View Related
Jul 10, 2007
I currently use Enterprise Manager 8.0 with several SQL 2000 servers. I'm ready to work with my first SQL Server v 2005. Can I use EM 8.0 along side the new Management Console until I'm through the learning curve? I understand you cannot register a 2005 server with EM 8.0, but I assume you can register a 2000 server from the new Management Console without any problem.
View 1 Replies
View Related
Nov 5, 2007
Hello,
I have a solution with a lot of packages, and each of them has configurations set on an SQL Server table. Right now the information in the table is outdated and I'd like to overwrite it with the new values on the properties of each package (and its elements). Is there another (faster) way to do this without having to disable/enable or recreate all configurations manually?
Thanks in advance
View 7 Replies
View Related
