SQL server freezes my machine.l'm running sql2000 and sp2.Every now and then it freezes my machine as if its running a process in the background. How can l sort this out its slowing my machine down.
l've got 512mb of ram and l don't see why l should have problems with memory .Please help
I am running SQL 2005 Standard under Windows 2003 Standard with 8 GB of RAM in one of our 64 bit servers; I noticed min server memory was set for some reason to 128 MB and max server memory to 6114 MB by the previous DBA. We plan to upgrade it to 32 GB. What is the best approach when changing those values ? Should I change those values to the defaults (0 - 2147483647) or create min and max boundaries? . This box is only utilzed by SQL.
Also I know that those changes are reconfigured dynamically so no need to reboot, but would this place any performance hit in the server and should I use Management studio or sp_configure ? (for some reason I don't trust Management studio for these type of changes :))
Thanks,
Carlos
Ok,
My SQL Server production DB freezes for 20 minute periods about 3 times a day, during
this time no-one can do anything. I checked the perf. monitor counters (all
the ones there at default), the scary part is that they ALL fall to 0 during
the freeze, then rise to normal. Yes, that's zero %!
I checked the ID's connecting, there are no common ones that connect just
before a freeze, and there are no hardware issues. It's only a 400Mb DB,
with a trans log of 120Mb. Between 200-400 users are connected during
business hours, both from the LAN and off. Some users are running some
fairly heavy reports, but there's no correlation between these and the
freezes. The biggest table only has 1.5 million rows, and the average query
time is 232 milliseconds.
This whole mess has been going on since the 15th of Jan, we searched high
and low for any alterations made, but the client swears blind that no
changes were made. Any ideas anyone?
Thanks in advance all,
Jaishel.
Hi,
the SQL DB freezes and no one can access the DB for 5 or 10 minutes. Even select queries don t execute, nothing is displayed.
Until we kill the process id that s blocking in the sql activity monitor, only then the DB is released and people can work again.
What does it mean that no query executes until we kill the processes ID? what could it be?
Also, recently we created indexes and ran tuning adviser, is it possible that the creation of indexes cause the freeze of a DB? is that possible?
Thanks a lot for your help
Hi all !
Hi have a package with 3 steps :
1 - import data from flat file into a temp table
2 - copy selected data from the temp table to permanent tables
3 - truncate temp table
The task #2 is a SQL statement
INSERT INTO MODELS (MODEL_ID)
SELECT DISTINCT MATNR
FROM STANDARD
WHERE MATNR NOT IN (SELECT MODEL_ID FROM MODELS)
where STANDARD is the temp table and MODELS a permanent one. My problem is this simple task freezes all the time. It stalls on 'Starting' and stays in this state forever when I execute the package.
Any idea why ?
I am installtion MSDE on a Windows XP Pro. The install gets through to 5 seconds and freezes. THis sits for about an hour and nothing happens. The Microsoft SQL Server folder is created, but the services are not created. We tried to reinstall many times and removed the registry key before each reinstall (after uninstalling and rebooting). Any ideas about what could be wrong?
View 9 Replies View RelatedWe're running the Microsoft product SMS 2003 SP1 for software deployment, patching, hardware inventory, etc. The back-end is SQL 2000 Enterprise SP4 which is installed on the same box as the SMS 2003 SP1 product, and the DB is 145GB's.
We started noticing that the server would freeze every minute or so for 30 seconds. We started logging stats via perfmon and saw that the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 for 30 seconds at the same time the freezing occurred. I have determined that this is occurring during the checkpoint. The recovery interval option is set to the default of 0 on SQL, when I changed the setting to every 5 minutes, the average disk queue length for the physical drive of F: would skyrocket between 400 - 500 every 5 minutes and would subside after 2 minutes. I understand the need for the checkpoint / recovery interval option, but don't believe this high average disk queue length should be occurring.
Does anyone know why this is happening and how to fix this ? The freezing of the box while checkpointing is killing me.
Thanks
Hi all
I have a master table, with ID Column int, and Description column Varchar
And another details table, with MasterID Int, and Detail id INT
If i use this query:
SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col=b.id_col
Never returns data, and get freeze, and i have to kill process on Managment
But if i use this:
SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col=b.id_col+0
It returns data i Want, is this a Bug? Or i have an error?
The 2 columns is the same data.
But if I run this,
SELECT * FROM table1 a INNER JOIN table2 b ON a.id_col+0=b.id_col
Nothing happens,
What could it be?
Hi
We are repeatedly having the trouble of our publisher database becoming unresponsive during network problems.
We have publisher in carribean, distribution agent in canada and subscribers all over. The problem comes when publisher cant deliver to distributor due to network outages.
We are planning on moving the distributor to the carribean ultimately but that will take a while, and our application repeatedly needs to be restarted due to these freezes.
Does anyone know of this problem & possible ways to fix it in the short term (short of changing our replication topology).
is this a bug in replication?
ps.database is merge & transactional pubs. (some tables merge/some trans)
help much apprec,
des
Hi, I'm using SQL2005 v9.00.3042.00 (SP2) on Windows 2003. I have a situation, when I turn on and begin to trace a database using the "SQL server Profiler", that particular VB application that uses the databases freezes (hourglass) when user click on anywhere that require a data query (download list, etc.) until I either pause or stop the trace, what gives? Thanks in advance.
View 2 Replies View RelatedSet up - windows server 2003 - SQL server 2000 + tools installed
Not sure why its happening but recently when running some scripts onto one of the databases held on the server windows crashes.
I say crashes - it is non responsive but the mouse cursor can be moved.
these scripts are not being run from the server but from one of many machines on our companies network.
Any suggestions would be great
We have SQL 2000 running on Server 2003. The server has 4 GB RAM, RAID 1 and has the /3GB switch in Boot.ini. We have 6 databases, the largest is about 14 GB. Until recently I could use Enterprise Manager to perform database backups to a folder on the same server but now the operation fails. After selecting the location for the backup and clicking OK, the hard drive light comes on but goes out after 15 - 30 seconds. The progress bar does not move. I've waited and waited but nothing further happens and the server doesn't respond. The only solution is to push the power button and let the server re-boot. Very occasionally the backup will run for a minute or two and the progress bar might get to about one third of the way across, (in which case the destination file will show up with more than 0 bytes), but it never completes. I can find nothing in the event log relating to this problem. As far as I am aware, nothing has changed since the last successful backup. Backups to tape using Retrospect appear to work fine but I haven't done a test restore. Does anyone have any ideas as to what I can try to get the backups working again?
View 14 Replies View RelatedI am trying to move data from a transactional database to a data warehouse using a slowly changing dimension. The transactional data comes from a view in SQL server that takes <60 seconds to run and returns about 60k rows. The warehouse table is currently 80k rows long (and growing), and contains 7 historical (type 2) dimensions. When I execute the package in BIDS the DataFlow Task begins to execute, and shows that between 20k and 30k rows have been pulled from the data source into the SCD Transform in the first hour before it simply stops doing anything. This is not to say execution stops; it continues. There is no error thrown. No warning given. System resources are 98% free. The database is not being hit at all. And yet, I have let the package sit 'still' as it were for over 8 hours, and nothing ever happens.
Here is a copy of one log:
starttime endtime message
4/8/2008 9:36 4/8/2008 9:36 Execute phase is beginning.
4/8/2008 9:36 4/8/2008 9:36 PrimeOutput will be called on a component. : 1715 : Union All
4/8/2008 9:36 4/8/2008 9:36 A component has returned from its PrimeOutput call. : 1715 : Union All
4/8/2008 9:36 4/8/2008 9:36 PrimeOutput will be called on a component. : 2912 : Staged Queues
4/8/2008 9:36 4/8/2008 9:36 Rows were provided to a data flow component as input. : : 2970 : DataReader Output : 70 : Slowly Changing Dimension : 81 : Slowly Changing Dimension Input : 9947
4/8/2008 9:37 4/8/2008 9:37 A component has returned from its PrimeOutput call. : 2912 : Staged Queues
4/8/2008 9:37 4/8/2008 9:37 A component has returned from its PrimeOutput call. : 2912 : Staged Queues
4/8/2008 9:59 4/8/2008 9:59 Rows were provided to a data flow component as input. : : 1718 : New Output : 1715 : Union All : 1716 : Union All Input 1 : 3825
4/8/2008 9:59 4/8/2008 9:59 Rows were provided to a data flow component as input. : : 1688 : Historical Attribute Inserts Output : 1682 : Get End Date : 1683 : Derived Column Input : 645
4/8/2008 9:59 4/8/2008 9:59 Rows were provided to a data flow component as input. : : 1702 : Derived Column Output : 1692 : Update End Date : 1697 : OLE DB Command Input : 645
4/8/2008 10:01 4/8/2008 10:01 Rows were provided to a data flow component as input. : : 1759 : OLE DB Command Output : 1715 : Union All : 1758 : Union All Input 2 : 645
4/8/2008 10:01 4/8/2008 10:01 Rows were provided to a data flow component as input. : : 2970 : DataReader Output : 70 : Slowly Changing Dimension : 81 : Slowly Changing Dimension Input : 9947
4/8/2008 10:24 4/8/2008 10:24 Rows were provided to a data flow component as input. : : 1718 : New Output : 1715 : Union All : 1716 : Union All Input 1 : 3859
4/8/2008 10:24 4/8/2008 10:24 Rows were provided to a data flow component as input. : : 1688 : Historical Attribute Inserts Output : 1682 : Get End Date : 1683 : Derived Column Input : 641
4/8/2008 10:24 4/8/2008 10:24 Rows were provided to a data flow component as input. : : 1702 : Derived Column Output : 1692 : Update End Date : 1697 : OLE DB Command Input : 641
4/8/2008 10:26 4/8/2008 10:26 Rows were provided to a data flow component as input. : : 1759 : OLE DB Command Output : 1715 : Union All : 1758 : Union All Input 2 : 641
4/8/2008 10:26 4/8/2008 10:26 Rows were provided to a data flow component as input. : : 2970 : DataReader Output : 70 : Slowly Changing Dimension : 81 : Slowly Changing Dimension Input : 9947
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1718 : New Output : 1715 : Union All : 1716 : Union All Input 1 : 3969
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1688 : Historical Attribute Inserts Output : 1682 : Get End Date : 1683 : Derived Column Input : 662
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1702 : Derived Column Output : 1692 : Update End Date : 1697 : OLE DB Command Input : 662
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1793 : Union All Output 1 : 1787 : Get Start Date : 1788 : Derived Column Input : 9947
4/8/2008 10:49 4/8/2008 10:49 Rows were provided to a data flow component as input. : : 1814 : Derived Column Output : 1797 : Insert Destination : 1810 : OLE DB Destination Input : 9947
4/8/2008 15:34 4/8/2008 15:34 The pipeline received a request to cancel and is shutting down.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown or an error in another thread is causing the pipeline to shutdown.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown or an error in another thread is causing the pipeline to shutdown.
4/8/2008 15:34 4/8/2008 15:34 The pipeline received a request to cancel and is shutting down.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" has exited with error code 0xC0047039.
4/8/2008 15:34 4/8/2008 15:34 Thread "WorkThread1" has exited with error code 0xC0047039.
Notice the time difference between the last OnPipelineRowsSent event and the first OnError event (when I clicked the stop button): 5 hours! In all that time, SSIS did not log a single event, or use more than 2% of my processor or exceed 1GB page file or hit the database even once! I am assuming this means it is simply not doing anything. It is not failing, nor is it executing, it is just sitting there.
Has anyone experienced a similar problem? Does anyone know how I might troubleshoot this? Thanks in advance for any help, and let me know if I need to clarify. Also, I am new to SSIS, so if I am missing something obvious, go easy on me! Thanks.
Mitch Connors
Hi Everybody,
I am having a very weird problem, and I was wondering if you have any idea about what I could do to fix this.
In a nutshell:
* I have this application that uses ADODB and ODBC to connect to an SQL database.
* We have two installations: one running SQL 2000 sp 3, and the other SQL 2005 sp 1.
* The one that runs on SQL 2000, my app performs 8,500 transactions a day with no problem.
* The one that runs on SQL 2005, my app performs 200 transactions a day, and goes down at least once a day.
Trying to figure this out, I noticed that, from time to time, when working with SQL 2005, the application kind of freezes when it tries to read or write to the database, and after a while (say 20 seconds or more), it continues as if nothing had happened. Trying to debug this issue, I noticed that this wait time period is ended if I close one of the other open connections that I have (say, I close the query analyzer)
As people may say, ADODB and ODBC should no longer be used... however, I have noticed this same kind of behavior with other tools, like the query analyzer from the SQL 2000 client tools when connecting to the SQL 2005 server.
I suspected a Licensing issue... however, I haven't been able to replicate this problem by opening several query analyzer instances.
Any help is greatly appreciated!!!!!
Best Regards,
Manuel
After I change a fairly complex stored procedure and I run a reportagainst it, crystal hangs at "assesing database". I have verified thedatabase. When I run a trace on SQL is shows repeated cachemiss overand over. I let it run for 30 minutes and nothing.Anyone?
View 5 Replies View Related
To the Moderator:
I am not sure if this thread needs to go into this forum or there is one for C# stored procedures/CLR Database Objects.
To All:
Is this a known issue that when debugging CLR stored procedures the VS 2005 freezes even before reaching into the stored procedure? I can restart my computer and debugging works for a while and then it starts to take forever when I step into (F10) during debugging. I am running only one instance of sql server, and even that of my local machine as I have never been able to debug on a remote server.
This random and unstable behavior of the debugger is proving very unproductive and costly. Is there a microsoft update/fix/patch to make debugging CLR stored procedures a smooth process?
Thanks.
I'm trying to use SSIS in MSSQL 2012 to extract data from MAS90 database.The connection string is tested to be working, because I can extract successfully using the same one in Excel.I follow the wizard of import data in management studio, but after selecting the tables and mapping all those things, when clicking finish, the management studio always freezes.
View 6 Replies View RelatedHelp!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
Hi All,
I have enterprise version software CD1 and CD2 of SQL server 2005
when i try to run setup.exe..the following error pops -up
The image file Setup.exe is valid, but is for a machine type other than the current machine
MY OS version is windows 2003 32bit Operating system...
I am suspecting the binarie may be of 64bit.. How do we check the binaries are 32 bit and 64 bit software
Thanks for your Help
Mohan
Hi to everyone,
I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.- Thanks to everyone for any help.
Hullo all
I have two machines,
One is a standalone machine and the other is on a domain network. How can I run a stored procedure/job on the standalone machine from the domain machine ?
running the procedure as a Domain user results in a failed job/stored proc.
also creating an sql login and attempting to run it as that user also fails. How can I solve this problem ?
please e-mail me at wayde@sunnygrp.com if you have any thoughts...
Hi All;We are going to change our application server. We will copy all of ourDatabase from Mic 2000 Server OS to Mic 2003 Server OS. I found anarticle that how to move all the folders from same OS. My question isthat Would the 2003 OS be a problem when we copy all of the MC SQL 2000folders to New OS ? Has anybody done this before? Could you gimme yoursuggestions please?ThanksASA
View 1 Replies View RelatedHi,
The SQL Server Database hangs overnight and also consumes high disk space on one of our servers. This has been recurring for quite a few weeks and occurs daily.
Can somebody assist me in trouble-shooting the same
Thanks in Advance
Deepak
Hello again!
Once again I got stuck with a weird problem and I´m dependent on you.
I built a Data Flow task in which I did an OLE-DB Source Adapter with a SQL-Query as Source.
The Query goes on a Linked Server which connects to a Informix Table via ODBC. When I enter the Query and klick "OK" or try to go on the "Columns"-Tab, the BIDS freezes completely and the "Delay Notification Message" appears in the System Tray. When I check the Activity Monitor of my SQL Server, there´s a process marked as "Runnable" (green) which contains the Query
"select collationname(0x0904D00034)"
This one is oviously run by Visual Studio and doesen´t execute properly. I waited for an hour or so, but nothing changes. I´m running SQL Server 2005 SP2 (9.00.3042)
Any help would be greatly appreciated!!!
Thanks in advance!
Hi,
here goes the question:
i have 1 sqlserver 2k5 machine (machine A) and sqlserver 2k (machine b)
how do i build a query to select data from 2k machine in a query of 2k5?
i mean something like
select *
from login:passw@machineb.dabatase.dbo.customers
thanks!
Hi,
here goes the question:
i have 1 sqlserver 2k5 machine (machine A) and sqlserver 2k (machine b)
how do i build a query to select data from 2k machine in a query of 2k5?
i mean something like
select *
from loginassw@machineb.dabatase.dbo.customers
thanks!
I have a fuzzy lookup in a data flow but this data flow never gets pass pre-execute phase. What is the problem?
Thanks
I have a package that hangs in the designer after I change the sql statement in a DataReader Source from a 'select' to a 'call stored procedure'. The stored procedure takes 2 date parameters. I use an expression to build the 'call stored proc' statement and the 2 date strings. The data reader source uses an ADO.Net connection manager. The ADO.Net connection manager uses the provider for MySQL (Connector/.Net 5.1) which I installed from MySQL.com (http://dev.mysql.com/downloads/connector/net/5.1.html). Before creating the stored procedure I had been using an expression to build a 'select' statement with two date variables as follows:
select ...
where ads.last_seen >= "" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] )
+ "" and ads.first_seen <= "" + (DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] )+ "" group by sm.service_provider_id,lm.location_id,lm.web_sublocation_id;"
The sql for the data reader source is set via the sql command property of the data flow component.
After testing the sql, I created a stored proc from this sql and then changed the expression (using the sql command property of the the data flow component) to build the 'call stored proc' statement, like this.
"call usp_SEL_Rollup ("" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "","" +(DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] ) +"");"
then when I tried to switch to the data flow tab, the editor froze, with the status bar saying "validating datareader source". The data flow tab says "Loading...". I don't know how to troubleshoot this. Each time I have tried I have had to kill the application. Any ideas/suggestions?
Thanks,
Al
Hi,
If I use GetDate() function from client machine (using "\ServerMachineSQLExpress" instance from client machine "ClientMachine"), then from where I will get the DateTime value ?
From "ServerMachine" or from "ClientMachine"
Thanks
Hi Guys,
I was wondering if there was any white papers on the migration part from microsoft?
Apparently the situation is like this
Currently installed MOSS 2007 in a testing pc as application server. This application server will refer another testing database server which running SQL Server 2005.
Now, wanted to move this 2 temporary server into another 2 new servers.
Correct me if I'm wrong, it cannot be done by using image like Norton Ghost due to difference hardware spec.
So, if I install another fresh copy of MOSS2007 & SQL server 2005 in that 2 new servers, how can I migrate the contents, documents, etc from the testing database server?
It would be great if someone can show me the links to kb article. Thanks
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.