What Is The Usage Of RowGUID Under SQL Server 2005?
Apr 16, 2008Hi All
What is the usage of Row Guid under SQL Server 2005? It seems not much information state the main purpose of it?
Regards
Alex
ADVERTISEMENT
Usage Of ROWGUID
Feb 27, 2004Is it a good Practice to use Rowguid for creating unique identifiers within a table
View 1 Replies View RelatedInvalid Column Name 'rowguid'. Error When Generating Snapshot For Merge Replication SQL Server 2005
May 15, 2007Hi there,
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
How Do I Measure Bandwith Usage For Merge Replication SQL 2005 To Sql Server Mobile 2005?
Jan 24, 2007Hi,
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
thnx,
pdns.
SQL Server 2005 DTS/Memory Usage
Sep 18, 2007We have an application that we currently run on SQL Server 2000 that works by creating a DTS package that it then executes.
Due to performance reasons, we have been considering switching to 2005, for a few reasons. Can anyone confirm clarify the following?
1) SQL Server 2000 caps RAM usage at 2GB, whereas SQL Server 2005 is only limited by the OS - RAM usage is a big current issue for us, so if upgrading to 2005 would solve this it would help a lot. Can anyone confirm my understanding of this?
2) Would using the legacy DTS in SQL Server 2005 take advantage of this RAM difference, or is it running on the old 2000 engine and only able to use the 2GB?
Thanks for any help.
SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?
Jan 30, 2014providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?
Basic Steps As How To Web Usage Mine In Sql Server 2005
Jan 26, 2007
Hi there,
I am doing a project on web usage mining of my universities server logs and im just wondering how i go about mining them in sql server 2005?
Do i mine them in one table? do i normalise the web log data? what algorithms will i use on them as im trying to get usage patterns from the users and also where most of the users come from.
Thanks in advance
Gary
SQL Server 2005 Agent Service High CPU Usage
Jan 30, 2008I have six stored procedures that I have to run hourly during the day. When I schedule them into separate SQL jobs (they must run concurrently), the percent CPU of Sqlagent90.exe process goes way up, eventually pegging out the overal CPU usage on the server. When I run the six stored procedures concurrently from query windows in SQL Server Management Sudio, the overall CPU usage stays very low.
Is there something about the SQL Server Agent that inherently adds CPU overheard? Thanks in advance ....
Memory Usage Of SQL Server 2005 Mobile Edition
Jan 2, 2007Hi forum readers,
we are working on a release 2.0 mobile solution right now. In our version 1.0 we did not have to worry about memory issues as our application was the only application running on our target devices (e.g. T-Mobile MDA Compact II Pocket PCs, WM2005).
Now we need to share the available memory with others. As our application relies on its SQL Server 2005 Mobile Edition database we are wondering about memory usage of that server.
We know that a Pocket PC divides its memory into Storage and Program. If our application uses a 5 MB database and 1.5 MB for DLLs and it's exe-file. These files reside in the storage space when not loaded. When the application starts up it is loaded in the program memory. What happens to the 5 MB database file? Is is loaded into Program memory as well? Are only portions of that file loaded? Or is nothing loaded at all?
Does anyone have a deeper insight into that server an can answer my questions.
Best regards,
Tobias
Usage Of Dtutil And Dtexec In SQL Server 2005 SSIS
Nov 22, 20061) We are migrating data from SQL Server 2000 to 2005 DB.
2) We are developing SSIS packages in client, build, deploy and install and copying to SQL Server 2005 server to execute for migration.
3) For this we encrypt all packages with password, then decrypt all while executing in a batch file using dtexec. This works fine.
4) What does not work is executing a master execute task in which are individual execute tasks. This is so since the other packages are still encrypted.
5) We decrypted them separately first using dtutil using Decrypt and Copy/Move option but while executing error is thrown that these other packages are still protected by password.
6) Do we need to decrypt, then encrypt using default package protection level and then decrypt again.
7) How exactly to just execute a master package when packages are developed on client.
Constant CPU Usage On Server When Using SQL Server 2005 Management Studio
Jun 7, 2006
I have recently installed SQL Server 2005 (Developer Ed) + SP1 onto a VMWare based Windows 2003 + SP1 server.
SQL Server works fine when connecting to it using Mangement Studio on Windows XP.
However, I have noticed strange CPU usage on the server which seems to be caused by Management Studio (either directly or indirectly).
When no-one is connecting to the server using Management Studio, the server happily ticks along with CPU usage around 1-5% range. However, as soon as someone connects to the SQL Server instance using Management Studio the CPU usage begin to go up and down constantly.
The CPU usage ranges from 5-50% and it goes up and down (fairly regularly) every few seconds. It does this even when nothing is actually being done in Management Studio. The moment Management Studio is closed, the CPU usage goes back to normal.
The processes on the server that appear to be causing the CPU spikes are services.exe and wmiprvse.exe.
On a possibly connected note (though possibly not), the Security log in the server's Event Viewer shows that there are logins occuring every minute or so (most of the logins are from my account).
Any ideas?
Extreme Slow SQL Server 2005 And High CPU Usage - Casting Of Values
Feb 20, 2006Hello sql and .net gurus :-)I have a problem with my website www.eventguide.it. It's completly developed under .NET 2 and SQL Server 2005 Express. My problem is the folowing:The server is a Intel 3Ghz HT processor with 1GB Ram. No other page on the running system is a CPU consuming site. We optimized the SQL statements, the code, the caching and many other parts of the website (pooling on SQL access), but the SQL Server uses about 50% to100% of the CPU and about 400MB RAM all the time. The whole site seems to be very, very slow. In fact there are many of SQL operations on every page request, but we cache a lot of them in different ways (page output caching, application caching). So I don't understand we have so much performance problems. Any suggestions for optimised code in general? I read nearly all of the MS .NET performance papers - but real world experience is the missing part :-) It is better to cast the values of a SQL reader like thisDim String1 as String = Ctype(DataReader.item(0), String)Dim Integer1 as Intger = Ctype(DataReader.item(1), Integer)or like thisDim String1 as String = DataReader.item(0)
Dim Integer1 as Intger = DataReader.item(1)Thanks a lot for your help!FOX
Computing The CPU Usage ,memory Usage For An Inserted Record
Nov 2, 2007
I have a client program that writes to sql server database 10 records per second . i want to compute the CPU usage and the memory usage for the whole program or CPU usage,memory usage for the insert statement in the program .
Can anybody help me with this?
CPU Usage(%), Logical IO Performed (%) Usage For Adhoc Queries Is 90%
Sep 7, 2007
Hello, When I am seeing SQL Server 2005 Management studio Server Dashboard> I am seeing my(USERS) databases and msdb database usage is very small % of in CPU Usage(%), Logical IO Performed (%) Usage pie chart.
90% of Total cpu usage is showing for Adhoc Queries. what excatly this means in Dashboard? if application uses more than it would have shown in Database level or not?
sicerely this dashboard is good, if any one is watching daily, please advice their experiences here.
Thanks in advance. Hail SQL Server!
Rowguid/ Uniqueidentifier
Jun 7, 2007hi. im new in using rowguid, i want to know how long can this datatype can hold. and is there a chance that value can be duplicate or the sql is checking it already before it create a new one.
and what will happen if i select yes in isrowguid.
thnx
MS SQL 2005 && Memory Usage Question
Jan 25, 2008OVERVIEW
I run a MS SQL DB and have 7GB of RAM allocated to it to use. My DB size is around 30GB. I have about 30-40 users at any given time accessing this DB, and on any given moment we may have 2-3 queries being processed at the exact same time, but not really a high rate of usage.
The SQL is running on a 2 - Dual Core Xeon 2.8Ghz processor server in RAID 1+0. The average CPU usage is around 8-15% at most times, spikes to 60-70% sometimes then drops back down.
QUESTION
Why or what would cause SQL to show in my Task Manager as using 7GB of RAM constantly? When I restart SQL, system RAM drops to 1.7GB and holds. As traffic increases, as queries begin to be processed in SQL the RAM rises till it reaches the 7GB limit I set. Before it reaches this limit, the ASPX pages run smooth as silk. Once it reaches this 7GB limit they begin to crawl. A process that would take 2-6 seconds, now takes 30-60 seconds.
I know a couple years back I ran into this and it was because processes were not being closed and SQL was holding them in RAM, but I have been assured this is not the case now. I was told that because my DB is 30GB total (MANY tables, most are hardly ever accessed, and the bulk of the data is hardly accessed) that it is common for SQL to use and hold on to 7GB of data - is this true?
What would cause SQL to hold 7GB and slow way down? IS there a KB article that could help me understand this?
Thanks for your help.
SQL 2005 Memory Usage Just Keeps Rising
Oct 10, 2007Hi,
I have a three-tier app written in C#, which takes information from a third party source (typically an array of double precision floats) and commits it to a SQL 2005 Db.
The Server then notifies the client that the information is available and the client queries the Db (through the server) in order to display it in "real-time". It all seems to work fine except that the memory usage by SQL 2005 just keeps rising. I have run a memory profiler on the server and client apps and they do not have a leak.
The test I am running has all three apps on the same machine and is reading in about 250k of data a second. It typically runs for just under two days on a machine with 2Gb RAM before falling over with System.OutOfMemoryException.
I have tried setting max memory usage but it seems to make no difference... anyone seen anything similar or know what my problem might be?
Thanks,
Paul G
SQL 2005 (32-bit) Cluster Memory Usage
Mar 6, 2008We recently did an in-place upgrade of our cluster from SQL 2000 Enterprise to 2005 Enterprise. We are seeing memory utilization on the server that is not expected and wanted to get an idea from others if this is normal. Here is our setup:
32-bit Windows Server 2003 SP1
SQL 2005 SP2, 2-node cluster
16 GB memory on each node
/3GB /PAE switches in the boot.ini file
AWE Enabled, min memory=0, max memory=14000
The strange bit is that in the Task Manager the SQL service is showing as only using 205 MB of memory and the pagefile usage is at 13 GB. This is troublesome since it looks like SQL is using virtual memory instead of physical memory. We recently upgraded the memory from 4 GB to 16 GB, the sql cluster service account was added to the Lock Pages in Memory policy, the boot.ini switches were set and the AWE enabled/max_server_memory script was run before upgrading to 2005. Can anyone confirm either 1) this is normal or 2) how to correct the memory usage?
I have also questioned using the /3GB switch; would it be better to remove that and set the max memory for SQL to 12000?
Any help would be greatly appreciated. If I left off any information that would be helpful, please let me know.
Chris
T-SQL Create Table Is Rowguid
May 2, 2006I have the following to create a table in sql server......
CREATE TABLE [dbo].[myTable] (
Id VARCHAR(50) NOT NULL,
fldDate SMALLDATETIME,
guid uniqueidentifier NOT NULL PRIMARY KEY
)
GO
I can't seem to find the right syntax to make the uniqueidentifier a rowguid.
Thanks,
Zath
@@identity Equivilant With ROWGUID
Mar 17, 1999How can I convert this to use GUIDs in SQL 7
Create Procedure sp_sfAddressNameAdd
@AddressName varchar(100)
As
DECLARE @AddressNameID int
INSERT INTO sfAddressName (AddressName)
VALUES (@AddressName)
SELECT @AddressNameID = @@IDENTITY
return @AddressNameID
AddressNameID is set to uniqueidentifier with newid() as default
Thank you,
Craig
Invalid Column Name 'rowguid'
Nov 29, 2006Hi all and I appreciate any help
I recently been assigned to a project that has an ms-access front end and sql server 2000 sp4 as the database engine.
The database was replicated (merge replication) but for some reason they drop replication and when they tried to put it back on they got allot of problems and so they paused it.
My task now is to make it work again. After allot of reading I desided to clean the database and start from the beginning. Using some scripts and instructions on a copy of the database I cleaned all the tables related to merge replication but when I went back to ms-access to update a record I got an error
"invalid column name 'rowguid'" . I checked all my tables in my database but I find not such column as it was dropped using the scripts.
Any ideas that I can use?
Thanks allot,
George
Prob With Rowguid Using Newid() Fn
Apr 23, 2006hi guys
cant find the prob, please help
ALTER TABLE [dbo].[tblUserDetails]
ALTER COLUMN [UserDetailRowGUID] [uniqueidentifier] set default newid()
go
error:Incorrect syntax near the keyword 'default'.
cm
Merge Replication Without Rowguid
Jul 20, 2005hi,I have a database that has Primary Key constraints on every table. Yetwhen I run the replication wizard and try to configure mergereplication the wizard notifies me that it will create a rowguid onthe tables even though they already have a primary key.anybody know how to avoid the rowguid column from being created?regards,Doron
View 1 Replies View RelatedInvalid Column Name 'rowguid'
Nov 29, 2006Hi all,
I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.
I was given a copy of a database with merge replication on it and doing allot of reading and using some scripts I cleaned replication (or so Im thinking) and tryed to use it again on the ms-access front end. When I try to update some data I get "invalid column name 'rowguid'" , but no such column exist in my database as it was dropped.
Any help on this?
thanks George.
Invalid Column Name 'rowguid'
Feb 23, 2007I'm trying to set up a merge publication in MSSQL 2005 SP1.
I have a database which I have successfully made into a merge publication. When I run the snapshot agent, several articles are processed successfully, then I encounter an error with the following: Invalid column name 'rowguid'
Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.
The replication monitor details the following error:
Error messages:
· Message: Invalid column name 'rowguid'.
Command Text: sp_MSpublicationview
Parameters: @publication = RelMS Publication
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615
· Server [XXXXXXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615
Can anyone shed further light on this?
Thanks.
Undesired ROWGUID Column
Sep 30, 2005Hello Everyone,
View 4 Replies View RelatedReturn Autoassigned Rowguid
Jan 16, 2008
Hello,
I've a table with one column specified as rowguid.
An insert into this table is done in a stored procedure, which should return the new rowguid by an output parameter.
How can I obtain the autoassigned rowguid of the inserted record?
is there something like ident_current which is used for identity-columns?
Thanks in advance
Raimund
SQL 2005 Express - Sqlserv.exe Memory Usage Always Increasing.
Nov 1, 2006Hi,
I'm hoping that some might have an answer for me after much net searching.
I have a server (200GB disk space, Dual 3.8 GHZ processors, 4GB memory) that hosts 6, very small, SharePoint sites (WSS 2.0) and SQL 2005 express handling 1 config DB and 7 content DB's.
The biggest DB at the moment is only 300 MB and the sites are not actively being used yet, they are only open to a select number of users (+ - 25 in total) who are using them as reference "areas" at the moment. Each site is running in it's own application pool as well.
I find that the sqlserv.exe process increases in memory usage and does not seem to decrease. It gets to the point of 960 MB usage and then databases cannot be used (SQL 2005 Express max memory is 1GB). SQL seems to "close" them down and site errors being received are "site is not in configuration database". When the SQL service is restarted the memory usage idles around 70 - 80 MB, the sites are 100% again but after an hour or 2 the memory usage is sitting at 560 MB again and doesn't seem to decrease.
Is there a way I can bring this memory usage down?
Any feedback would be greatly appreciated.
Preserve ROWGUID Column During Publication Delete
Apr 30, 2007Hello,
I am looking to preserve the ROWGUID column that is created automatically when a table is added to a publication as I have noticed that the column is removed and readded (with new values) each time the publication is deleted and recreated. We are planning on using the values in the rowguid column for a non-replication purpose, and don't wish the column to be removed or the values to be reset in the event the 'owning' publication is dropped for whatever reason. I've done some research and have identified the preserve_rowguidcol column of the sysmergearticles table as a candidate for achieving the desired goal. Before moving forward, I wanted to ask for input regarding this. Are there any best practices I've overlooked on this topic? Neither Google nor this forum's search results yield any real discussion on this topic.
Thanks!
DanD
Excessive Network Usage With SQL 2000 To SQL 2005 Transactional Replication
Aug 3, 2007We have a SQL2000 database (Publisher) replicating inserts and updates across a 10Mb link to a SQL 2005 database (Subscriber). The Publisher has two tables we are interested in, 1 with 50 columns and 1 with 15. Both tables have 6 insert/update triggers that fire when a change is made to update columns on the publisher database.
We have set up a pull transactional replication from the Subscriber to occur against the Publisher every minute. We have limited the subscription/replication configuration to Publsih 6 columns from table 1 and 4 from table 2. Any change occuring on any other columns in the Publisher are of no interest. The SQL 2005 database has a trigger on table 1 and table 2 to insert values into a third table. There are around 7,000 insert/updates on table 1 and 28,000 on table 2 per day. All fields in the tables are text.
We are seeing "excessive" network traffic occuring of approximately 1MB per minute (approx 2GB per 24 hrs). We also see that the Distributor databases are getting very large -- upto around 30GB and growing until they get culled. We have reduced the culling intrval from 72 hrs to 24 hours to reduce the size.
Does anyone have any suggestions as to how this "excessive" network traffic can be minimised and how the distributor database size can be minimised. I think that maybe they are both related?
Thanks,
Geoff
WA POLICE
After Merge Replication - Wanted To Remove Rowguid Column
Jul 28, 2001Hi all,
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
Server Mem Usage
Sep 25, 2006Hello.
I am using a SQL Server 2005 devenv and each time I open my ssis
package, the server mem usage jumps up to almost 400mb ram. On opening,
tries to validate each data flow task and that takes forever, I have
delayed validation however still takes time to load. I am currently
using terminal services to access visual studio and the project has two
ssis packages that myself and another developer are working on, could
this have something to do with this?
In saying this, everything worked perfectly a week ago, now its
extremely frustrating.
Anyone with feedback would be greatly appreciated.
Cheers
SQL SERVER MEM USAGE PILEUP
Jun 10, 2005hey guys,could anyone help me out ..?i have a serious problem with my SQL server DB ..it uses up all my memory (i see it in task manager) after a whole day of asp's triggering the db .. it should go down when there are no triggers anymore, right?please help :)Stallema
View 4 Replies View Related