SQL 2012 :: Constantly Growing Version Store
Jul 7, 2015
We have an issue with the Version Store growing constantly. According to sys.dm_os_performance_counters, "Version Generation rate (KB/s)" is growing, but "Version Cleanup rate (KB/s)" isn't. We use read-committed snapshot isolation
While dbcc opentran and sys.dm_exec_requests don't show any long running transactions, I wrote a query looking at sys.dm_tran_ active_snapshot_ database_transactions. This shows a number of long running transactions but, according to sys.dm_exec_sessions, they are all sleeping. The transactions that are running come and go very quickly, as I would expect.Could these sleeping transactions be responsible for preventing the version store from cleaning up?
View 2 Replies
ADVERTISEMENT
Jun 16, 2014
The TEMPDB transaction log file keeps growing.The database server is new and the transaction log was presized to 1 GB on installation. After installing a number of databases, the log file grew over a day to 38GB. Issuing a manual checkpoint was the only way to free some space to allow it to be shrunk back to a usable size. The usage of the file is still going up.
I am struggling to find what process is causing the log to be used so heavily. Looking at the log reuse wait desc for tempdb returns "Nothing" and tempdb itself isn't being used very much or growing in size.
View 9 Replies
View Related
Aug 22, 2014
I have a database with a table called RAW, this table receives msgs via XML, sms and various other formats. The data is written into this table at a rate of approximately 50-100 rows per minute.
I have a SP which takes the data written into the raw table and performs various actions looks for account information, writes to a log table, writes to incident table’s gets GPS information and so on.
The records written into the raw table need to be processed at almost instantly with a maximum of under a minute from when they arrive into the table.
At present there is a sql agent job which executes the SP, this consist of a step to execute the SP which on completion moves to the next step which is a loop waitfor delay and then back to step 1.
The trouble is it never actually finishes and runs 24/7 there is no break point for error handling and occasionally the records that arrive in the raw table do not get processed and the job has to be restarted for it to pick them up again.
I am looking for best way to handle this process, I thought about a trigger but the performance impact on using a trigger was too heavy to consider.
View 2 Replies
View Related
Jul 28, 2014
I have a Problem like the Following ..
On 24th my Mdf size was 10GB,when i checked now the Mdf size was increased suddenly to 30GB.
solution to decrease the Size and as well as where can i check the reasons behind that..
View 2 Replies
View Related
May 8, 2012
I have a C# app linked to a SQL db and I need to store it's version number in a table (could be something like 1.2.789) but I cannot find any datatype which allows me to do this.
I could create three fields in the table for each number but I don't want to.
View 3 Replies
View Related
Aug 22, 2001
I upgraded from SQL 6.5 to SQL 7 last month, and so far, everything's
been going fine.
However, I'm not using my old SQL 6.5 backup scripts, which, when the
backup was done, would dump the transaction log with TRUNCATE_ONLY, shrinking
the log size.
My SQL 7 server is set up with a Maintenance Plan which does everything,
including backup, but the log file seems to be growing and growing. I'm
up to 4.5 gigs now, for a database with a data file of 3.5 gigs.
How do I "dump transaction with TRUNCATE_ONLY" on a SQL 7 database?
Thanks,
Todd Wallace
View 3 Replies
View Related
Mar 1, 2004
I have merge replication setup up for 6 SQLCE Subscribers.
I have noticed that the MSmerge_tombstone table is growing
at a fast rate regardless of any changes to the data in
the database. It seems to be consistantly adding 50 rows
of data to the table every 2 minutes. As the table grows
it causes the SQLCE subscirbers to fail with the following
message:
ERROR: -2147467259
SQL Server Reconciler failed: Run
ERROR: -2147200925
: Failed to enumerate changes in the filtered
articles.
ERROR: 0
: {call sp_MSsetupbelongs
(?,?,?,?,?,0,?,?,1,?,?,?,?,?,?)}
ERROR: 0
: The merge process timed out while executing a
query. Reconfigure the QueryTimeout parameter and retry
the operation.
I'm sure that this is due to the size of the
MSmerge_tombstone.
Should the MSmerge_tombstone table grow at this rate?
36,000 rows every 24hrs!
I understand there is the sp_mergecleanupmetadata Stored
procedure but if i use this does that mean that because i
have to reinitialise all the subscribers, they are going
to have to pull down the whole subscription again.
I have since Changed a settings to make subscription
expiration date to 8 days instead of never expires but
we're still getting 50 rows added every 2 minutes
SQL SERVER 2000 SP3
Hope someone can shed some light on this for me.
Thanks.
.
View 5 Replies
View Related
Mar 18, 2014
I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index.
I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
View 4 Replies
View Related
Oct 26, 2015
Is it possible to find the version details from IP and Port.
For example I have IP and port number known to me ,but I don't have database credentials and other details.
I want to know which version of Sql server is running.
My purpose is to find the false vulnerabilities in security scanning.
View 1 Replies
View Related
Jul 19, 2007
The comapny i work for has a server running the following.
Opteron 246x2
2 Gig memory
320 Gig Sata 2 drives
Windows 2003 Standard Edition
SQL Server 2005 Express Edition. The Free one.
There are approx 10 users that connect to the server.
There are two programs which seem to use sql server. Act 7.0 and service ceo.
When the computer is rebooted its at 0% for sqlserver.exe
Than when all connect it maxes it 50% and its steady there. Seems Service ceo affects it the most.
Told by comapny need to buy full blown sql server to resolve problem. But i dont think this is the problem.
Questions: Is it common for server to be at 50% all the time with sql server running?
And if its not is there a way to reduce the sql cpu usage.
I am new to sql server and have done alot of research and fixes. Ive unsitalled and reinstalled all sql instances and done the tweaks suggested. Any fresh ideas would be great thanks.
John
View 3 Replies
View Related
Oct 26, 2006
I've got a little console app that basically pulls back a recordset from our SQL Server 2005, goes through each row in the dataset and may/may not insert a record into a different table in the database. We use sproc's for every transaction and I close every connection in the application. However, when the application ends, I still show connection pools open in the performance monitor. Same with websites that I know have no traffic or that have been stopped by me in IIS.Last night I showed a total of 6000+ "Current # pooled and nonpooled connections". Should I be worried about what seems to be unending growth in the connection pools? If so, how can I look to manage this better?
View 2 Replies
View Related
Jun 15, 2007
Hi, I've been trying to figure out this error message I'm getting. The exact error message is: " Procedure addservicerequest has no parameters and arguments were supplied." and this is applied to a SQL Command, but what confuses me is that I've got parameters already created and added. Is there any advice to help me out with this problem? Here's the code for reference: 1 Dim sqlparadate As New sqlparameter2 Dim sqlparacompany As New SqlParameter3 Dim sqlparalocation As New SqlParameter4 Dim sqlparacontact As New SqlParameter5 Dim sqlparaphone As New SqlParameter6 Dim sqlparadetails As New SqlParameter7 Dim sqlparaso As New SqlParameter8 Dim sqlparadrive As New SqlParameter9 Dim sqlparawork As New SqlParameter10 Dim sqlparaback As New SqlParameter11 Dim sqlparaproblem As New SqlParameter12
13 Dim sqlcon As New SqlConnection14 Dim sqlinsert As New SqlCommand("addservicerequest", sqlcon)15
16 'txttest.Text = caldateofservice.SelectedDate
17 If sqlcon.State = Data.ConnectionState.Closed Then
18 sqlcon.ConnectionString = "***"
19 sqlcon.Open()20 End If
21
22 sqlinsert.CommandType = Data.CommandType.StoredProcedure23 'sqlinsert.CommandText = "addservicerequest"24 'sqlinsert.Connection = sqlcon
25
26 With sqlparadate27 .SqlDbType = Data.SqlDbType.SmallDateTime28 .Direction = Data.ParameterDirection.Input29 .ParameterName = "@dateofservice"
30 .Value = caldateofservice.SelectedDate.ToString31 End With
32 sqlinsert.Parameters.Add(sqlparadate)33
34 With sqlparacompany35 .SqlDbType = Data.SqlDbType.NVarChar36 .Direction = Data.ParameterDirection.Input37 .ParameterName = "@company"
38 .Value = txtcompany.Text39 End With
40 sqlinsert.Parameters.Add(sqlparacompany)41
42 With sqlparalocation43 .SqlDbType = Data.SqlDbType.NVarChar44 .Direction = Data.ParameterDirection.Input45 .ParameterName = "@location"
46 .Value = txtlocation.Text47 End With
48 sqlinsert.Parameters.Add(sqlparalocation)49
50 With sqlparacontact51 .SqlDbType = Data.SqlDbType.NVarChar52 .Direction = Data.ParameterDirection.Input53 .ParameterName = "@contactname"
54 .Value = txtcontactname.Text55 End With
56 sqlinsert.Parameters.Add(sqlparacontact)57
58 With sqlparaphone59 .SqlDbType = Data.SqlDbType.NVarChar60 .Direction = Data.ParameterDirection.Input61 .ParameterName = "@contactphone"
62 .Value = txtcontactphone.Text63 End With
64 sqlinsert.Parameters.Add(sqlparaphone)65
66 With sqlparadetails67 .SqlDbType = Data.SqlDbType.NVarChar68 .Direction = Data.ParameterDirection.Input69 .ParameterName = "@details"
70 If ddldetails.SelectedValue = "Other" Then
71 .Value = txtreason.Text72 Else
73 .Value = ddldetails.SelectedValue74 End If75 End With
76 sqlinsert.Parameters.Add(sqlparadetails)77
78 With sqlparaso79 .SqlDbType = Data.SqlDbType.NVarChar80 .Direction = Data.ParameterDirection.Input81 .ParameterName = "@serviceorder"
82 .Value = txtserviceorder.Text83 End With
84 sqlinsert.Parameters.Add(sqlparaso)85
86 With sqlparadrive87 .SqlDbType = Data.SqlDbType.NVarChar88 .Direction = Data.ParameterDirection.Input89 .ParameterName = "@drivetime"
90 .Value = ddldrivetime.SelectedValue91 End With
92 sqlinsert.Parameters.Add(sqlparadrive)93
94 With sqlparawork95 .SqlDbType = Data.SqlDbType.NVarChar96 .Direction = Data.ParameterDirection.Input97 .ParameterName = "@worktime"
98 .Value = ddlworktime.SelectedValue99 End With
100 sqlinsert.Parameters.Add(sqlparawork)101
102 With sqlparaback103 .SqlDbType = Data.SqlDbType.NVarChar104 .Direction = Data.ParameterDirection.Input105 .ParameterName = "@backhome"
106 .Value = ddlbackhome.SelectedValue107 End With
108 sqlinsert.Parameters.Add(sqlparaback)109
110 With sqlparaproblem111 .SqlDbType = Data.SqlDbType.NVarChar112 .Direction = Data.ParameterDirection.Input113 .ParameterName = "@typeproblem"
114 .Value = ddlproblem.SelectedValue115 End With
116 sqlinsert.Parameters.Add(sqlparaproblem)117
118 sqlinsert.ExecuteNonQuery()119
120 sqlcon.Close() Specifically it occurs on line 118. Thanks
View 5 Replies
View Related
Jun 21, 2000
Hello:
In my production environment, ALL OF THE SUDDEN, my backups via EM schedule tasks, fail, yet when i examine the error, it says that the DUMP/LOAD was successful. I receive the following error:
'Could not insert a backup or restore history/detail record in msdb.dbo.sysbackuphistory or sysrestore history. This may indicate a problem with the msdb.. . . . . .
I expanded the MSDB database, and ran sp_purgehistory (no params) and it still FAILS. Is this related to the log? Please HELP asap. Thank you in advance.
View 2 Replies
View Related
May 21, 2007
Hello,
when is seemd that everything works some weird behaviours comes out.
I try to summarize the problem without to post the complete code.
Service Broker is set to have a dialog between two databases on the same SQL Server instance.
The Initiator queue has retention=on and there is an activation SP to handle errors and Target's end dialog message.
The Target queue has retention=off, MAX_READER =1 and there is an activation SP to receive the message (WAIT FOR (RECEIVE (1) ...), TIMEOUT 30000 and do something with this message (sample insert into a DB).
The conversation has a Timeout Dialog to end the dialog after a while.
The problem that the message is constantly processed. The Process doens't stop is I end the dialof after the processing either.
n.b.the Receive is within a Transation that I commit at the end.
some other informations that in the meanwhile I found out :
This was my complete WAIT FOR(RECEIVE :
WAITFOR ( RECEIVE top(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@messagetypename=message_type_name,
@message_body=message_body, -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM [TargetQueue]
), timeout 1000;
if (@@ROWCOUNT = 0)
BEGIN
COMMIT;
BREAK;
END
IF I delet TIMEOUT 1000, everything works as expected ...
Inside if (@@ROWCOUNT = 0)BEGIN..END I wrote also an Insert into a table to see wheter the end of the queue was reached but this insert never occurs (neither with not without timeout)
I'm happy that it works what if this is the solution, it make no sense to me!
Any ideas?
Thank you!
M.B.
Thank you very much
M.B.
View 9 Replies
View Related
Jun 30, 2007
Hello.
Let me describe first my replication setup:
- SQL Server 2005 SP1 (SP2 coming soon)
- Approximately 35 remote users (Salesrep laptop) using Pull Subscriptions
- Merge (Bi-Directional) (8 articles - tables only)
- Merge (Uni-Directional) (5 articles - tables only)
- Transactional (5 articles - tables only)
Users receive data based on their territory #, therefore they receive their customers sets of data. It happens that customer change from one territory to another but not frequently. When it happens, so far so good, the data is redirected to the new salesrep using the model we configured (Territory table with SUSER_NAME() to filter the data).
Ok, here's my problem. Since a while, I can see in the replication monitor that some users seems to log the same conflict again and again (Merge process). I mean, checking the history for many subscribers, there is always the same number in the "Conflict" colums.
As an example:
- Merge completed after processing 18 data change(s) (4 insert(s), 14 update(s), 0 delete(s), 31 conflict(s))
- Merge completed after processing 27 data change(s) (10 insert(s), 17 update(s), 0 delete(s), 31 conflict(s))
- Merge completed after processing 20 data change(s) (5 insert(s), 15 update(s), 0 delete(s), 31 conflict(s))
and so on...(Those are only 3 historical entries for a single subscriptions but there are many like that, always with the same count of conflict - vary per user). It appears to me that the same conflicts come over and over.
The thing is that if I decide to reinitialize a subscription, conflicts will disappear, therefore I know that it is not a process on the server that keeps changing the data; anyway, even if it was, changes would be applied on the subscription because the server always win in my setup.
Any idea what should I do with this? Any help would be greatly appreciated.
Thanks.
View 3 Replies
View Related
Dec 16, 2014
We have a table with 10 columns each column is of datatype int and can accept nulls, when I do a save is it better to have 0 inserted in to the column or just insert null?
View 9 Replies
View Related
Jun 5, 2014
We have a Silverlight based application which currently supports only one production version. Idea is to support three concurrent versions of the same application and user will switch to the newer versions based on their interest or they can still continue with the older version.
We still have to use the existing database for all these three versions.
What is the best way to architect this so that we can differentiate the code between the versions and still keep the data in sync and run all the versions in parallel.
View 7 Replies
View Related
Apr 16, 2014
Our company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies
View Related
Jun 26, 2014
I have been asked to look into using Filestream for centralising MS Office documents (mostly excel).I am worried about the "user interface" aspect.I read that there are "this and that" APIs to read/write data to the filestream but surely one would need to write a specific interface to Word/Excel... which feels like far too hard.I am a great admirer of SQL Server but is it the right tool for document management?
We use SQL Server 2012 and have offices round the world with various internet connection quality.Our main aim is to stop the current "spreadsheet nightmare" so common with Excel.
View 9 Replies
View Related
Jul 11, 2014
I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
[Code] ....
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
View 5 Replies
View Related
Jul 31, 2014
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
View 2 Replies
View Related
Aug 20, 2015
When we use Partition switch and load the data to a table, can we refresh the indexes for specific partition, so that we don't need to rebuild / refresh for complete is this possible ?
View 1 Replies
View Related
Nov 2, 2015
From my one app, a dynamic where clause will generate like below.
//where ordercity='london' and orderby='smith'
//where orderamount > 100 and shipcity='new york'
From server, I created a store procedure as below.
SELECT * FROM [Order] WHERE @whereSql
How to complete the store procedure so that I don't need to pass parameters one by one...
View 1 Replies
View Related
Nov 21, 2001
The log for our SQL 2000 database shows constant "starting up database dbname" entires. Is there a option that causes the databases to constantly be starting or is this a new feature of SQL 2000.
View 1 Replies
View Related
Feb 6, 2007
Our report server are constantly getting the below error.
What causes this - I know how to fix it, in fact, I've automated it but why does it constantly happen on some servers? I guess I'd like to know what causes it to try and fix it at those points instead of having to fix it here. Proactively.
Reporting Services Error
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled) (rsRPCError) Get Online Help
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content. Check the documentation for more information. (rsReportServerDisabled)
Bad Data. (Exception from HRESULT: 0x800900
Why
View 3 Replies
View Related
Aug 16, 2006
Trying to set up a tranform task between a mysql db using and ADO.NET connection and sql server.
My query to pull from the mysql db is something like "select x,y,z from table where last_updated" > @User::LastUpdated. This command is set up as an expression for the Data Flow Task and is the value for the [DataReader Source].[SqlCommand]
I have two questions.
Why does the package attempt a query against the mysql database all the time?
And Why is the query attempting to pull the entire table instead of having any regards for my where clause?
I've even added where last_updated > greatest('2006-08-15', '" + @User::LastUpdated to attempt to get it a where clause even when the parameter isn't set yet.
What is the trick? This is not feasible when pulling from multi-million row tables.
View 2 Replies
View Related
May 28, 2014
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
View 2 Replies
View Related
Oct 17, 2014
I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.
Also, I need to make sure the table structure will allow for new forms.
View 5 Replies
View Related
Nov 13, 2014
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
View 5 Replies
View Related
Jan 28, 2015
I have below code to send email in HTML table format with store procedure. from my
Database = "CreditControl"
Table = "Testing$"
and code as below
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
[code]....
View 6 Replies
View Related
May 29, 2015
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?
View 2 Replies
View Related
Sep 29, 2015
i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....
View 5 Replies
View Related
Jun 20, 2014
How can I input parameter for Store Procedure like In ('Value1','Value2')
Example :
Create procedure GetUsers
@Users nvarchar(200)
as
Select * from Users where UserID in (@Users)
View 6 Replies
View Related
