When I run dbcc memorystatus on my SQL 2005 Server under Memory Manager I get:
VM Reserved 33610144
VM Committed 1478012
Doesn't the VM Reserved seem really high? Is there a way to force SQL to release the VM Reserved memory?
We are dealing with occasional problems running out of virtual address space. I have implemented a job that records VAS every two minutes using the dm_os_virtual_address_dump function. When I notice a big drop in VAS I look at the SQL log and see event like:
Common language runtime (CLR) functionality initialized using CLR version v2.0.50727 from C:WINDOWSMicrosoft.NETFrameworkv2.0.50727
AppDomain 2 (32767.dbo[runtime].1) created.
In this case VAS dropped from 53MB down to 7MB. Then just 5 minute later I see these events
AppDomain 2 (32767.dbo[runtime].1) is marked for unload due to memory pressure
AppDomain 2 (32767.dbo[runtime].1) unloaded.
But we don't seem to recover any VAS and eventually have to reboot. We are using replication and I sometimes see this when replication restarts after we have an out of sync data issue. We do not have SQL CLR enabled.
Does anyone have any insight on what is going on? What triggers these AppDomains to be created and why does it eat up all the VAS? How could replication have anything to do with it?
We are running...
SQL Server 2005 Enterprise sp2 (with hotfix 3175)
Windows Server Enterprise 2003 SP1 32GB RAM
Max Server Memory in sp_configure = 29000MB
THANKS
I am new to SQL and might be missing something very easy. I have a situation where the space allocated to the transaction log of a database is extremly large (5 Gig). I can not manually reduce it. This gives me a "Error 21335: [SQL-DMO]The new DBfile size must be larger than its current size." This is a problem because the increase in size has taken all available space on the server.
Mike
hello everyone,
I have a question about the under what circumstances will a large database with mostly free space not shrink?
thanks
In an Intranet Application using Win NT, Apache, Tomcat and SQL Server, the memory space used by SQL Server is drastically increasing and finally the system crashes. Nearly 40 people are accessing the system. The hardware configuration is P2 processor with 393 MB RAM and 2GB Virtual Memory. SQL Server,Web server and Servlet Engine are running on same machine.
Within three hours, SQL Server occupies 200M memory and the system perfomance comes down and finally the system stopes the tomcat servlet engine.
Anybody have any idea on this? We have nearly 1500 JSP pages,200 Bean files and 300 tables in SQL Server.
In my environment, there is maintenance plan configured on one of the server and while running DBCC checkdb on a database of size around 200GB, log file usage of tempdb is increasing and causing the maintenance job to fail.
What can I do to make the maintenance job run successfully, size of the tempdb database is only 50GB and recovery model is set to simple. It cannot be increased as the mount point on which it is residing is 50GB.
Hi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedHello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.
How do we suppress multiple spaces to a single space in T-SQL
E.G.
Field: FullName
e.g.
WOMENS HEALTH RIVER VALLEY
JOHN FAMILY MED GROUP
HERSH STWEART P.
PARK HEIGHTS MEDICAL CENTER
KOPP WHITEFIELD E
The o/p wanted is
HERSH STWEART P.
Thank you.
Is virtual server the same as creating more than 1 instance of SQL Server?
Thanks!
I have a SSIS package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.
I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.
Getting Error:
[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 4 buffers were considered and 4 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked
Any help would be greatly appreciated.
Hi Guys,
Windows XP, IIS 5.1
I have written an installer that creates an MS SQL Database, creates the
necessary data tables and then imports the data into the data tables. All
this works fine.
I now need to copy the web pages to a directory on the users machine and
then make the chosen directory a virtual directory so the user can call the
pages in the usual way (http://localhost/mytestsite/index.html)
Would anyone know how to create a virtual directory using dos command line parameters. I have seen something using "IIsVdir.vbs" but I do not have this script file.
Any help would be really appreciated.
Best Regards,
Steve.
Steve
What is a virtual server?In my new job,my boss mentioned about moving the websites from a physical server to a virtual server.Why is this done or needed?
Also he insists that I buy and install SQL server 2000 enterprise edition in my laptop and use the enterprise manager to access their server.Can't I just download the client tools from their SQL package and be able to access their server?Do I need a CAL or is it just enough to have a permission in their network for my laptop?
Can anybody throw some light here please?Thanks!
I have one package that is constantly running out of virtual memory, right now I am on a development server, running only this package. The package is moving data from one table into another on the same server in the same database. The server has 3 Gb of memory and is only running SS2005 and SSIS. I am a local admin on the server and running the package through BIDS, once again for our initial testing. I tried setting the property BufferTempStoragePath to our E drive so it can utilize the 100 Gb of free space we have but that doesn't seem to work either. I have also tried setting the MaxRowSize to many different values to no avail. I am constantly getting an error, see below for exact error, when it gets through roughly half the load. Moreover it reports this error about 500 times in the progress report if I let the package run to completion. Finally, when all is said and done the package has moved the data successfully but the package always shows as failing.
I have googled continuously on this problem but have not found a resolution. I did see on a post here where it was recommended to run the package out of process, however I don't see the benefit at this point when this is the only package I am running. I also don't understand why it would report the error so many times and fail the package when it is completing successfully? Source and Destination have the same number of records at the end of the task. Could someone please try to make sense of this.
Getting Error:
[DTS.Pipeline]
Information: The buffer manager detected that the system was low on
virtual memory, but was unable to swap out any buffers. 4 buffers were
considered and 4 were locked. Either not enough memory is available to
the pipeline because not enough is installed, other processes are using
it, or too many buffers are locked
Thank you for your assistance!
Hello
I design package Merge 2 tables
Table one contains almost 25 million
Table 2 contains almost 30 million
using lookup component to split record if exist start update else insert as new record every time I start execute get error on lookup component low virtual memory
Kindly if anyone have suggest I will be appreciated
Thanks in advance
Note the 2 tables have PK & IX
I have a sbs 2003 sp2 server running exchange 2003 sp2 it was running fine until one day I got this low virtual memory error, I checked the memory usage in the task manager and it is using 7 gigs of virtual memory, I increased the max size from 6 gigs to 8 gigs just to see what happens and now it is using 9 gigs of virtual memory. I don't have any clue as to what the problem is! Please Help!!!
View 1 Replies View RelatedI made some copy of table and I have this error but on my hard disk i have 4 gig of empty space.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object 'Backup_Date_11_24_00_Time_9_08_34_AM' in database 'LogActiviteIntramedia' because the 'PRIMARY' filegroup is full.
/Intranet_API/Forms/videTableLog.asp, line 16
My question is how can I increase the space of primary filegroup?
Thanks and have a good friday
I really need some help. This is what I'm trying to do: first of all, i have a PC running Vista. Now on that PC, i have a virtual machine using VMWare Workstation 6, which has Win XP with SP2 installed on it. On the virtual machine, i have Visual Studio 2005 and SQL Server 2005 Express Edition with SP2; now, the thing is, i have a program in C#, which is supposed to connect to a network, but since I'm running on just one PC which is not connected to a network, i instead connect to 127.0.0.1 via port 1433. Now here's where my problems start. At first, i could not connect because i kept receiving an error which said "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provedier: TCP provider, error: 0 - No connection could be made because the target machine actively refused it.)". A friend of my mine helped, so i did the following: i went into SQL Server Surface Area Configuration (configuration for localhost) , changed the remote connections to allow for both local and remote, using both TCP/IP and named pipes, restarted the database engine, started the SQL Server Browser; then i went to administrator tools, Data Sources (ODBC), and from System DSN tried to add an SQL Server. I gave it a name, and on the server textbox i tried typing "localhost", but doing so, i could not get past the next screen, (i did specify the port as 1433) where i have to chose between win nt and sql server authentication, and neither would work (even though i know the sa password and can log on to SQL Management Studio using that password). Then i tried copying the server name from the management studio, as the server when trying to add the system DSN. That seems to have worked, and i finished ading the SQL Server to the System DSN, using the database which i wanted. But after all these steps, i still get the exact same error as before - "An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provedier: TCP provider, error: 0 - No connection could
be made because the target machine actively refused it.)".If someone can please help - i've tried everything i can think of - i can access the internet from the virtual machine, i even tried disabling the firewall, but no luck. Is it because of the virtual machine? If so, can anyone suggest a solution? I actually doubt it, because i've even tried to install it on my pc, not the virtual machine, so on vista - and also installed SP1 for VS 2005 and SP1 for Vista, but still the same thing. Sorry for making this post so long, but if anyone can help, i'd really appreciate it. Thanks!
I am trying to undertstand how does sql 2005 decide how to use the memory.
Does it use RAM first than the virtual memory or does it decide what to use conditionaly?
Anybody have an idea? Like if the table is really big does it put half
of the table to RAM and the other half to virtual memory ?
Say I have 2 servers clustered for SQL. xxxP1 and xxxP2.
Can my SQL virtual server name be xxxP1 or does it have to be something other than the names I called the 2 W2K boxes.
Thanks..
One of our clients controls data for about 150 companies. Each company has the same schema and is running SQL Server 7. The maintenence task is horrible. He would like to put all the data in one large database, but needs to control the access, so a user at one company selecting data in a table cannot have access to another company's data in the same table. I thought about using views for each company but that is 150 companies times 100 tables. With Oracle8i you can make virtual private databases. Can this be done in SQL Server 7? Thanks in advance
View 1 Replies View RelatedWe have a Server that is set to use 80mb of a 128mb machine. The machine is also set to use up to 300mb of Virtual memory as needed.
After running for 10 days, we got the message "Low on Virtual memory". We did some looking and found SQLServer was using 300mb between real and virtual memory.
So the question: Is there a manual method to get SQLServer to release extra memory with out stopping and restarting the service?
We are using sql server 2008 r2 standard with sp2. I have a 12 GB database in production server, log file was set to 150MB with increment of 10 percent, in last 4 years database size has gone from 2gb to 12 gb. I ran following command
DBCC LOGINFO
And found I have 150 plus rows(which means 150 plus virtual log files)
I increased the size of log file to 25 percent of data file which comes to be approx 3gb and also set auto growth to 20 percent ...
Additional info: we have a log shipping environment in production, i am taking log backups every 15 mins.. still number of virtual log files is same , why is that , how to bring them between number of 25-50 as thats the recommended.
Hi,
I have the following requirement :
I need to have a way to intercept the SQL queries from an application written in VB and using a ODBC driver and modify the SQL queries before it goes through the ODBC driver and then to the database. This I need to do without modifying the original application.
The solution I have in mind is to write a ODBC virtual driver and configure my application to use my virtual ODBC driver. The ODBC virtual driver in turn will use the actual ODBC driver to the database. The virtual driver will basically intercept the SQL queries, modify it and then give it to the real ODBC driver.
My question is
1. Is this a feasible solution?
2. What should I do in order to implement the vitrtual ODBC driver.
3. Any pointer will be appreciated.
Thanks
Jake.
:confused: Please forgive this elementary question. I have database which has a view that produces the desired records and fields from multiple tables. As I understand it a view is a Virtual Table. My problem is I need to export these results periodically to deliver to a customer. I am running this database on SQL Server 2000 if I right click on a table I have the option to dts the data to a text file but when I right click on the view I do not have this option. How can I make this virtual table an actual table. PLEASE Help Thank You, Ed
View 1 Replies View RelatedI am using internet through a dongle connection and every time I connect to internet, the ip gets changed.
1. How to create a database when the ip is not constant?
2. Can I use DNS server ip as database engine?
3. Is there any way to get the system ip?
Good morning,
I am looking for some first-hand experiences from fellow DBA's where they had SQL Server running on a Win2K3 VM. What sort of issues (or successes) did you find re: resource sharing, swap files, etc? Are there any experiences where using a VM negatively affected your environment?
Thanks so much. What a great forum!
I have two instances of SQL Server running on my Development machine.I am having some performance problems and while investigating theproblem I saw with the Process Explorer form Sysinternals that bothinstances consume each 800 Mbytes of memory!I experimented with sp_configure and by giving both instances a fixedmemory size. Both methods do not seem to have any effect.Can anybody explain me why SQL Server is using so much memory?Thanks for any information.Evert WiesenekkerPSBesides the northwind database I only have one simple extra database(70 Mb in size) installed.
View 2 Replies View RelatedI've been discussing here a SQL 7 view which scrolls slowly when linked toan Access 2000 MDB. After trying various things, I've distilled it down tothe following: when the linked view has a virtual primary key, it is slow;when there is no virtual primary key, it scrolls quickly.Looking at Profiler, I see that when there is no virtual primary key, Accesssends a simple select command to SQL Server. However, when there is avirtual primary key, Access gets a list of primary key values, and then usesthose values to get records from SQL Server in groups of 10.This may account for why the scrolling is slow when there is a virtualprimary key. However, I wonder if there's any way to improve performance orotherwise work around this apparent bottleneck.The MS literature says that creating a virtual primary key will not affectperformance. However, since different methodologies for obtaining data areused with and without the virtual primary key, it seems that it does affectperformance, and certainly does in this situation.Thanks,Neil
View 9 Replies View Related