[MS SQL Server] Choosing replication type and preparing for it

---

Choosing replication type and preparing for it Posted: 28 Sep 2013 08:53 PM PDT Hi all,We have 15 servers (in different regions) and each need to work with subset of data and exchange modifications with central the server.I have chosen merge replication with dynamic filter to partition data, therefore central server acts as publisher and 15 servers as subscriber.The problem is that data is mainly generated at subscribers and when I want to initialize snapshot, the central server has nothing to send to subscriber. Besides, if I sync them manually for startup, it will be difficult if in the future we require to reinitialize particular subscriber because the changes at the subscriber happens a lot. Another idea is to use central subscriber model but I'm not sure if this is the right choice.Please advise me to choose right approach.Many thanks in advance,Leila

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL 2012] Output based on Mapping Table and Orders Table

---

Output based on Mapping Table and Orders Table Posted: 29 Sep 2013 03:26 AM PDT I have two tables,1st Table is the Order Table which has order no,old items, price and their quantity.2nd Table is Mapping Table which has mapping for each unique combinations of the old order items.3rd Table is the desired output.Some notes:1)Whenever in an order there is a combination of item1,item2,item3 of the mapping table it should retrieve output1 and output 2 for that, when in order there is a combination of item1 and item 2 of the mapping table it should retrieve output1 and output 2 for that, when in order there is only item 1 present it should retrieve output1 and output 2 for that.2)Also, price of item2 and item3 will always be 0. The output table should take the price and quantity of Item1 always.3) Its not necessary that Order No will be sequential. It can be like 456,789,989 etc.DDL and DML for my question:declare @orders table ( OrderNo int, OrderItem varchar(10), Quantity int, Price money )declare @mapping table ( Item1 varchar(10), Item2 varchar(10), Item3 varchar(10), Output1 varchar(10), Output2 varchar(10) )insert into @ordersvalues (1, 'A', 3, 960), (1, 'B', 1, 0), (1, 'C', 1, 0), (1, 'D', 2, 200), (2, 'E', 5, 100), (2, 'B', 1, 0), (2, 'C', 1, 0), (3, 'Q', 6, 1000), (4, 'B', 1, 0), (4, 'A', 3, 300), (5, 'A', 7, 4000)insert into @mappingvalues ('A', 'B', 'C', 'X', 'S'), ('A', 'B', '', 'P', 'R'), ('A', '', '', 'O', ''), ('D', '', '', 'Z', ''), ('E', 'B', 'C', 'Y', ''), ('Q', '', '', 'M', ''), ('J', 'B', 'C', 'N', '')-- Output Expecteddeclare @output table ( OrderNo int, NewItem varchar(10),Quantity int,Price money)insert into @outputvalues (1, 'X', 3, 960), (1, 'S', 3, 960), (1, 'Z', 2, 200), (2, 'Y', 5, 100), (3, 'M', 6, 1000), (4, 'P', 3, 300), (4, 'R', 3, 300), (5, 'O', 7, 4000)select * from @ordersselect * from @mappingselect * from @output-- Solution that I tried giving wrong outputDECLARE @OutputTable TABLE (orderNo int, newItem varchar(1), quantity int, price money);INSERT INTO @OutputTable(orderNo, newItem, quantity, price) SELECT o.orderNo, m.output1, o.quantity, o.price FROM @mapping as m INNER JOIN @orders as o ON m.item1 = O.orderItem AND o.price != 0 AND m.output1!=''INSERT INTO @OutputTable(orderNo, newItem, quantity, price) SELECT o.orderNo, m.output2, o.quantity, o.price FROM @mapping as m INNER JOIN @orders as o ON m.item1 = O.orderItem AND o.price != 0 AND m.output2!=''SELECT * FROM @OutputTable ORDER BY orderNoI have also attached the solution I tried from my end.Thanks in advance.[url=http://s10.This image host is not supported, please use another/93j6lzb3d/Query_Image.png][/url]

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] INSERT INTO MULTIPLE TABLES AT ONCE

---

INSERT INTO MULTIPLE TABLES AT ONCE Posted: 08 Mar 2012 09:14 AM PST Hello All,Is it possible to insert records from one table into two separate tables? If not, based on the info below, what would you recommend?Here is the scenario:I have a table with approximately 15 columns and 60k rowsI want to insert the contents of that table into two existing empty tables. The important element of the insert is in the two tables, the first column in both tables is an identity auto increment column. I want to keep the rows consistent for that identity number between the two tables.I know this code doesn't work, but if it did, this is what it would look like:Table: MAIN_TABLE m1Columns: C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12Table: TABLE1 t1Columns: ID, E1, E2, E3, E4, E5, E6Table: TABLE2 t2Columns: ID, E7, E8, E9, E10, E11, E12 INSERT INTO TABLE1, TABLE2(t1.E1, t1.E2, t1.E3, t1.E4, t1.E5, t1.E6,t2.E7, t2.E8, t2.E9, t2.E10, t2.E11, t2.E12)SELECT m1.C1, m1.C2, m1.C3, m1.C4, m1.C5, m1.C6m1.C7, m1.C8, m1.C9, m1.C10, m1.C11, m1.C12FROM MAIN_TABLE m1JOIN TABLE1 t1 ON m1.C1 = t1.E1JOIN TABLE2 t2 ON m1.C1 = t2.E7So, an entire row from MAIN_TABLE will be split between TABLE1 and TABLE2 and the ID column in TABLE1 and TABLE2 will increment by 1 and ID 1 in both tables should reflect the complete record taken from MAIN_TABLE row 1.Hopefully that makes sense. I appreciate your help!Thanks in advance!!Ronnie

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] synch two tables

---

synch two tables Posted: 28 Sep 2013 05:04 PM PDT Guys,I have two tables one is users and the other is frap_users ,Both contains some common attributes which are required to be same means if there is any change in the users table the same column has to updated in the frap_users and vice versa and this has to done through triggers only.When i have written triggers it is getting into infinite loop.I will appreciate any thoughts and suggestions on this.Am using Sql server 2008Regards,Papis

Need Help on Fastest Search Logic Posted: 27 Sep 2013 11:19 PM PDT Hi,I have two tables named "Table1" and "Table2".Table1 Details:id bigint, product_name nvarchar(1000),quantity intrecords count on Table1 : 25000( may increase in future)Table2 Details:id bigint, product_name nvarchar(max),details nvarchar(1000), description nvarchar(1000)Note: Table2 productName column will have comma separated valuesrecords count on Table2 : 186289( may increase in future)sample data: Table1:1 canola 1202 bread 1303 sauce 1404 corn 120Table2:1 canola,tea,muffin,cheese jellyproducts null2 vinegar,canola,sunflower oliproducts null3. cornil,vegoil,canola,sesameoil oilproducts nullmy requirement is i will take each prodcutname from Table1 and will search it in Table2's prodcutname column. if matches found then get the row and insert into temp table.i tried with while loop/cursor with "like" condition but it takes 15+ hours to process the records. I know searching in comma separate data with "like" will take time, but it take 15+ hours and still running. if am not wrong that looping will be bad on this larger records. so i have tried with joins as well. but nothing seems reducing the processing time.[code="sql"]Try1 : select T2.* from dbo.Table1 t1inner join dbo.Table2 t2 on t2.product_name like '%' + t1.product_name + '%' Try2: select T2.* from dbo.Table2 T2inner join dbo.Table1 T1 on CHARINDEX(T1.product_name, T2.product_name) > 0 [/code]Is there any fastest way to achieve this requirement? please help me on this and share with me sample code if there is any.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server] Is this Correct Use of Dynamic SQL???

---

Is this Correct Use of Dynamic SQL??? Posted: 27 Sep 2013 04:19 PM PDT [code="sql"]set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[SearchBiography] @firstname nvarchar(50), @middlename nvarchar(50), @lastname nvarchar(50), @sexID int, @statusID int ASBEGIN SET NOCOUNT ON; DECLARE @SqlQuery varchar(max) , @SqlQueryFirstName varchar(max),@SqlQueryMiddleName varchar(max), @SqlQueryLastName varchar(max), @SqlQuerySex varchar(max), @SqlQueryStatus varchar(max) SET @SqlQuery = '' SET @SqlQueryStatus = '' SET @SqlQueryFirstname = '' SET @SqlQueryMiddlename = '' SET @SqlQueryLastName = '' SET @SqlQuerySex = '' SET @SqlQueryStatus = '' IF @sexID <> 0 SET @SqlQuerySex = ' WHERE sexID = ' + convert(varchar(20), @sexID) IF @statusID <> 0 BEGIN IF LEN(@SqlQuerySex) > 0 SET @SqlQueryStatus = ' AND statusID = ' + convert(varchar(20), @statusID) ELSE SET @SqlQueryStatus = ' WHERE statusID = ' + convert(varchar(20), @statusID) END IF LEN(@firstname) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 SET @SqlQueryFirstname = ' AND firstname like ''%' + @firstname + '%''' ELSE SET @SqlQueryFirstname = ' WHERE firstname like ''%' + @firstname + '%''' END IF LEN(@middlename) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 SET @SqlQueryMiddlename = ' AND middlename like ''%' + @middlename + '%''' ELSE SET @SqlQueryMiddlename = ' WHERE middlename like ''%' + @middlename + '%''' END IF LEN(@lastname) > 0 BEGIN IF LEN(@SqlQuerySex) > 0 or LEN(@SqlQueryStatus) > 0 or LEN(@SqlQueryFirstname) > 0 or LEN(@SqlQueryMiddlename) > 0 SET @SqlQueryLastname = ' AND lastname like ''%' + @lastname + '%''' ELSE SET @SqlQueryLastname = ' WHERE lastname like ''%' + @lastname + '%''' END SELECT @SqlParam = ' @xfirstname nvarchar(50), @xmiddlename nvarchar(50), @xlastname nvarchar(50), @xsexID int, @xstatusID int ' EXEC sp_executesql, @SqlParam, @firstname,@middlename,@lastname,@sexID,@statusID END[/code]

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

---

• Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
• How to flush SQL Server database from RAM to hard disk?
• How to create properly tree-hierarchy
• What does this SQLCMD statement do?
• Snapshot for transactional replication taking too long for large database
• Display executed commands and number of executions
• Can the 'returning' clause return source columns that are not inserted?
• Error on changing ownership of database from Files Page in database properties dialog box
• Entity with >150 attributes: One table or many tables with relationships
• How do I write this script?
• How do I find out how far along my PostgreSQL query is?
• Oracle query is slower the second time
• How to measure a perfomance of SQL Server database? [closed]
• Access denied when disabling agent job, despite SqlAgentOperator membership
• Minimizing Page Fetches
• Query to get reposts from people the user is following
• Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop
• Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
• USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view)
• How can I generate a usage log?
• Bitmask Flags with Lookup Tables Clarification
• SQL Server 2012 catalog.executions to sysjobhistory - any way to join them?
• SQLite writing a query where you select only rows nearest to the hour
• Mongo replication lag slowly increasing
• Unable to start Oracle Database Server : Get an error as I try to start it
• SQL Server replication subscriptions marked as inactive
• What's the difference between a temp table and table variable in SQL Server?
• Is nested view a good database design?

Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys? Posted: 28 Sep 2013 07:54 PM PDT I'm a fan of surrogate keys. There is a risk my findings are confirmation biased. Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based on IDENTITY() values. My background in computer systems tells me performing any comparative operation on an integer will be faster than comparing strings. This comment made me question my beliefs, so I thought I would create a system to investigate my thesis that integers are faster than strings for use as keys in SQL Server. Since there is likely to be very little discernible difference in small datasets, I immediately thought of a two table setup where the primary table has 1,000,000 rows and the secondary table has 10 rows for each row in the primary table for a total of 10,000,000 rows in the secondary table. The premise of my test is to create two sets of tables like this, one using natural keys and one using integer keys, and run timing tests on a simple query like: SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Key = Table2.Key; The following is the code I created as a test bed: USE Master; IF (SELECT COUNT(database_id) FROM sys.databases d WHERE d.name = 'NaturalKeyTest') = 1 BEGIN ALTER DATABASE NaturalKeyTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE NaturalKeyTest; END GO CREATE DATABASE NaturalKeyTest ON (NAME = 'NaturalKeyTest', FILENAME = 'C:\SQLServer\Data\NaturalKeyTest.mdf', SIZE=8GB, FILEGROWTH=1GB) LOG ON (NAME='NaturalKeyTestLog', FILENAME = 'C:\SQLServer\Logs\NaturalKeyTest.mdf', SIZE=256MB, FILEGROWTH=128MB); GO ALTER DATABASE NaturalKeyTest SET RECOVERY SIMPLE; GO USE NaturalKeyTest; GO CREATE VIEW GetRand AS SELECT RAND() AS RandomNumber; GO CREATE FUNCTION RandomString ( @StringLength INT ) RETURNS NVARCHAR(max) AS BEGIN DECLARE @cnt INT = 0 DECLARE @str NVARCHAR(MAX) = ''; DECLARE @RandomNum FLOAT = 0; WHILE @cnt < @StringLength BEGIN SELECT @RandomNum = RandomNumber FROM GetRand; SET @str = @str + CAST(CHAR((@RandomNum * 64.) + 32) AS NVARCHAR(MAX)); SET @cnt = @cnt + 1; END RETURN @str; END; GO CREATE TABLE NaturalTable1 ( NaturalTable1Key NVARCHAR(255) NOT NULL CONSTRAINT PK_NaturalTable1 PRIMARY KEY CLUSTERED , Table1TestData NVARCHAR(255) NOT NULL ); CREATE TABLE NaturalTable2 ( NaturalTable2Key NVARCHAR(255) NOT NULL CONSTRAINT PK_NaturalTable2 PRIMARY KEY CLUSTERED , NaturalTable1Key NVARCHAR(255) NOT NULL CONSTRAINT FK_NaturalTable2_NaturalTable1Key FOREIGN KEY REFERENCES dbo.NaturalTable1 (NaturalTable1Key) ON DELETE CASCADE ON UPDATE CASCADE , Table2TestData NVARCHAR(255) NOT NULL ); GO /* insert 1,000,000 rows into NaturalTable1 */ INSERT INTO NaturalTable1 (NaturalTable1Key, Table1TestData) VALUES (dbo.RandomString(25), dbo.RandomString(100)); GO 1000000 /* insert 10,000,000 rows into NaturalTable2 */ INSERT INTO NaturalTable2 (NaturalTable2Key, NaturalTable1Key, Table2TestData) SELECT dbo.RandomString(25), T1.NaturalTable1Key, dbo.RandomString(100) FROM NaturalTable1 T1 GO 10 CREATE TABLE IDTable1 ( IDTable1Key INT NOT NULL CONSTRAINT PK_IDTable1 PRIMARY KEY CLUSTERED IDENTITY(1,1) , Table1TestData NVARCHAR(255) NOT NULL CONSTRAINT DF_IDTable1_TestData DEFAULT dbo.RandomString(100) ); CREATE TABLE IDTable2 ( IDTable2Key INT NOT NULL CONSTRAINT PK_IDTable2 PRIMARY KEY CLUSTERED IDENTITY(1,1) , IDTable1Key INT NOT NULL CONSTRAINT FK_IDTable2_IDTable1Key FOREIGN KEY REFERENCES dbo.IDTable1 (IDTable1Key) ON DELETE CASCADE ON UPDATE CASCADE , Table2TestData NVARCHAR(255) NOT NULL CONSTRAINT DF_IDTable2_TestData DEFAULT dbo.RandomString(100) ); GO INSERT INTO IDTable1 DEFAULT VALUES; GO 1000000 INSERT INTO IDTable2 (IDTable1Key) SELECT T1.IDTable1Key FROM IDTable1 T1 GO 10 The code above creates a database and 4 tables, and fills the tables with data, ready to test. The test code I ran is: USE NaturalKeyTest; GO DECLARE @loops INT = 0; DECLARE @MaxLoops INT = 10; DECLARE @Results TABLE ( FinishedAt DATETIME DEFAULT (GETDATE()) , KeyType NVARCHAR(255) , ElapsedTime FLOAT ); WHILE @loops < @MaxLoops BEGIN DBCC FREEPROCCACHE; DBCC FREESESSIONCACHE; DBCC FREESYSTEMCACHE ('ALL'); DBCC DROPCLEANBUFFERS; WAITFOR DELAY '00:00:05'; DECLARE @start DATETIME = GETDATE(); DECLARE @end DATETIME; DECLARE @count INT; SELECT @count = COUNT(*) FROM dbo.NaturalTable1 T1 INNER JOIN dbo.NaturalTable2 T2 ON T1.NaturalTable1Key = T2.NaturalTable1Key; SET @end = GETDATE(); INSERT INTO @Results (KeyType, ElapsedTime) SELECT 'Natural PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime; DBCC FREEPROCCACHE; DBCC FREESESSIONCACHE; DBCC FREESYSTEMCACHE ('ALL'); DBCC DROPCLEANBUFFERS; WAITFOR DELAY '00:00:05'; SET @start = GETDATE(); SELECT @count = COUNT(*) FROM dbo.IDTable1 T1 INNER JOIN dbo.IDTable2 T2 ON T1.IDTable1Key = T2.IDTable1Key; SET @end = GETDATE(); INSERT INTO @Results (KeyType, ElapsedTime) SELECT 'IDENTITY() PK' AS KeyType, CAST((@end - @start) AS FLOAT) AS ElapsedTime; SET @loops = @loops + 1; END SELECT KeyType, FORMAT(CAST(AVG(ElapsedTime) AS DATETIME), 'HH:mm:ss.fff') AS AvgTime FROM @Results GROUP BY KeyType; These are the results: Am I doing something wrong here, or are INT keys 3 times faster than 25 character natural keys?

How to flush SQL Server database from RAM to hard disk? Posted: 28 Sep 2013 04:57 PM PDT As part of the nightly maintenance plan, my working MS SQL Server 2012 databases are backup-ed with options of simple recovery and full backup. Transaction log is not backup-ed. Also, at nights the MSSQLSERVER service or SQL Server is restarted Checking the the properties of corresponding files in operating system (Windows Server 2008), I observe that "Modified" time of .mdf files correspond to the time of maintenance plan (making backup) run but .log files modification time is one-two weeks earlier. Why is such difference and why the operating system fails to update modification time? So, what is the time and reason of .mdf file modification time? Is it restart of SQL Server or the time of backup? How can I estimate when a database was last written to a physical disk, that is how recent is .mdf file of a corresponding database? How can I force SQL Server to flush the database to hard disk in order to have most actualized .mdf file in case of seeing problems in SQL Server functioning?

How to create properly tree-hierarchy Posted: 28 Sep 2013 05:01 PM PDT I'm using web2py to create an application I've got 2 different roles: client and referents. Every client can have multiple referents. Both client and referent can insert row in a table, called object. The user table, where client and referent are registered, has a column called "client_id". Every referent in the column_id has the id of the client to whom he reference to. The problem is: which value should a client have in client_id? A possible answer could be NULL or the id of the client itself. Which one is better? Or there's something better?

What does this SQLCMD statement do? Posted: 28 Sep 2013 11:41 AM PDT I am new to SqlCmd. Can anyone give a short explanation of the following command? SQLCMD -S VMLIVE-DATA\SQLSERVER -Q "RESTORE DATABASE GIS_ENG FROM DISK = N'\\10.143.251.101\ClientData\Backup\DatabaseBackups\DB_ENG.bak' WITH FILE = 1, MOVE N'DB_ENG' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.mdf', MOVE N'DB_ENG_log' TO N'\\10.153.201.11\E$\SQLSERVER\Data\DB_ENG.ldf', NOUNLOAD, REPLACE, STATS = 10"

Snapshot for transactional replication taking too long for large database Posted: 28 Sep 2013 12:56 PM PDT I want to establish a transaction replication of a large database (200GB). But when I create the publisher and the snapshot agent starts, it takes very very long time and it sticks on the following message The process is running and is waiting for a response from the server but the problem is when I see the size of the snapshot folder, I can see it is growing up! But too slowly. Do you have any Idea about this? or any different way of doing the replication? how can I find the more detailed warning or errors messages? P.S. In my database there are two tables for Images that they make 98% of the size of my database. when I don't include them in my publication, everything goes fine. but when I Include them, I will face the problem.

Display executed commands and number of executions Posted: 28 Sep 2013 08:53 AM PDT How would I go about displaying the SQL statements (say..the first 15 characters) and the number of executions for all that have been executed?

Can the 'returning' clause return source columns that are not inserted? Posted: 28 Sep 2013 06:24 AM PDT Here's a minimal example of my real-world problem: create table t(id serial primary key, rnd double precision); of course you can return the inserted columns with a returning clause: with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *; /* | ID | RND | |----|----------------| | 9 | 0.203221440315 | */ you can also return a literal: with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, 1.0 dummy; /* | ID | RND | DUMMY | |----|----------------|-------| | 11 | 0.594980469905 | 1 | */ but you can't return the source columns: with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd; /* ERROR: missing FROM-clause entry for table "w": with w as (insert into t(rnd) values(random()) returning *) insert into t(rnd) select random() from w returning *, w.rnd */ Is there any way I can get w.rnd out of the final returning clause? SQLFiddle

Error on changing ownership of database from Files Page in database properties dialog box Posted: 28 Sep 2013 08:42 AM PDT I've restored a database and created a new user(same user from which I've logged in) as db_owner. Now when I wanted to see database diagram got an error Database Diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use files page of Database Properties dialog box or the Alter Authorization statement to set the database owner to a valid login, then add the database support objects Now when I tried to change ownership from Files page of Database Properties I am getting error as Set Owner failed for Database 'ABC' . (Microsoft.SqlServer.SMO) Additional Info: an exception occurred in SMO The proposed new database owner James-PC\James is mapped as user James in this database. Parameter name: James-PC\James" Please help me fix the issue so that I may be able to see database diagrams on this computer after restoring the database backup from another system.

Entity with >150 attributes: One table or many tables with relationships Posted: 28 Sep 2013 05:44 AM PDT I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY operations. I could create one table for all entity: CREATE TABLE rows { terminal_id long reference terminals(id), time timestamp, -- description 170 attributes PRIMARY KEY(terminal_id, time) } or I could create many tables with relationships: CREATE TABLE rows { row_id long PRIMARY KEY, terminal_id long reference terminals(id), time timestamp -- need create index for group by } CREATE TABLE gps { row_id long references rows(row_id), -- description gps attributes } CREATE TABLE fuel { row_id long references rows(row_id), -- description fuel attributes } -- etc. Please advise optimal structure for the database of this type.

How do I write this script? Posted: 28 Sep 2013 11:43 AM PDT I have a very large database and I want to select certain records and then set a column value such as: SELECT name, email FROM table WHERE zipcode = 12345 INTO outfile /tmp/file.csv; Then I want to set the mail_flag equal to today's date such as SET mail_flag = 09012013 I'm just not sure how to string this together.

How do I find out how far along my PostgreSQL query is? Posted: 28 Sep 2013 07:14 AM PDT I have a pretty decent idea of how many rows my SELECT...INTO query will actually process (e.g. I know how many will materialize). I understand Postgres won't tell me percentage completeness, is there a way (buried deep in logs, system tables, or otherwise) that I can find out how many rows have been pumped into the destination table or have been read by the SELECT query?

Oracle query is slower the second time Posted: 28 Sep 2013 11:16 AM PDT I have a general question about something I'm seeing in performance tuning in Oracle. I have a query that I'm testing. It's too big and complex for me to provide a simple example, sorry! (I tried lowering it down, but lost the symptom) The first time I run the query, it's 2 seconds. Subsequent times it's 3, 4, or even 5 seconds. This happens consistently - if I change the spacing, I get 2 seconds again, then it goes back up. This is the opposite of what I would expect - after the first time, the database doesn't need to parse the query, and blocks should be in read into the buffer cache. The only thing I can think of is that it is writing temp data somewhere, in such a way that it is faster to write and read it than it is to just read it. But that of course makes no sense. Does anyone know why this is happening? Or any red flags in the execution plan that would cause something like this? FYI, this is a SELECT, with multiple joins, aggregation, and views. No internal PL/SQL. Thank you @Colin It's unlikely another process could be affecting this Dev machine, at least so consistently. Not sure what dynamic sampling is. Same thing as cardinality feedback? @druzin No bind variables, but no predicate changes either - same hash. @mustaccio Good idea, although I suspect it's something to do with implicit temp tables or caching. I'll give it a try.

How to measure a perfomance of SQL Server database? [closed] Posted: 28 Sep 2013 03:36 PM PDT I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics. So, I need to measure "performance" of the RDBMS database before performance tuning and optimization and after. Which metrics are better suited for this? Trying to answer the obvious questions ahead ... I/O (hardware) bottlenecks are absent since the SQL Server runs on a virtual rack having plenty of physical resources under it. The database is used by approx. 60 users (mostly 8 hours a day) with widely varying load (per sec). This is a company management task, so the results of this task should be easy to grasp. UPDATE: the corresponding my question was answered in StackOverflow.com by @Martin von Wittich and, then, deleted by moderator/community: Run different kinds of queries (INSERT, UPDATE, DELETE) in a loop (e.g. 1000 times), then divide the runtime by the amount of loop iterations. Now you now how long a single query of that type takes. Then do whatever you want to do to improve the performance, and compare the results. Quite good for me. Also I do not see why it was deleted in SO with the reason: "This question does not appear to be about programming" and closed in this site with: "There are either too many possible answers, or good answers would be too long for this format."

Access denied when disabling agent job, despite SqlAgentOperator membership Posted: 28 Sep 2013 12:59 PM PDT I am attempting to disable a SQL agent job from an account which a member of the SqlAgentOperator role (but not sysadmin) The doco says this should be possible 3 SQLAgentOperatorRole members can enable or disable local jobs they do not own by using the stored procedure sp_update_job and specifying values for the @enabled and the @job_id (or @job_name) parameters. If a member of this role specifies any other parameters for this stored procedure, execution of the procedure will fail. ...and indeed the code for msdb.dbo.sp_update_job appears to support this - it has explicit handling for exactly this scenario. Yet if I run: use [MSDB] go select suser_sname() if(is_member('SqlAgentOperatorRole')<>1) raiserror('Not in required role',16,1) go exec dbo.sp_update_job @job_name='a job', @enabled=0 ... I get the following error: Msg 229, Level 14, State 5, Procedure sp_update_job, Line 1 The EXECUTE permission was denied on the object 'sp_update_job', database 'msdb', schema 'dbo'. The error message appears to indicate the proc's not even being run, but even granting that user explicit EXECUTE on that stored proc doesn't seem to fix it. Does anyone know how to grant a user the ability to disable/enable SQL agent jobs that they don't own, or has successfully used this functionality. Maybe it's just broken in SQL 2012 SP1 CU4 (which is what I am using)

Minimizing Page Fetches Posted: 28 Sep 2013 02:24 PM PDT I have a complicated database structure and am trying to use it to retrieve Records based on multiple selection criteria from several tables. As a general rule, is it better to attempt to use correlated sub-queries to check the state of flags in other tables to determine Eligibility, or am I better of creating Views that utilize Joins that represent the valid Records? As a more concrete example, is this: Select Col1, Col2, Col3 From Table1 Where (Select RefCol From Table2 Where Table2.PK = Table1.FK) = "Condition" Superior or Inferior to something like this: Select T1.Col1, T1.Col2, T1.Col3 From T1 Inner Join T2 On T1.FK = T2.PK Where T2.RefCol = "Condition" -- Edited -- As a corollary question: Is it productive to create Views which contain Intermediary validations? IE, if I repeatedly need to check if T1.Col1 = 1 and T1.Col2 = 0 is it worthwhile to create the following view: Create View T1Validated As Select Col1, Col2 From T1 Where Col1 = 1 And Col2 = 0 And then later use the existence of a record in T1Validated in subsequent checks, or is that likely to produce additional database page retrievals and/or table scans?

Query to get reposts from people the user is following Posted: 28 Sep 2013 01:24 PM PDT I have a table posts where all the posts by users are stored, the structure of this table is as follows | post_id | post_user | post_content | post_date | the users table is as follows | user_id | username | user_joined | user relationship table is as follows | follower | following | rel_date | this is the query I am using to get the posts from people that user is following to show them. SELECT * FROM posts WHERE post_user in(SELECT follower from user_follow where following=$loggedin_user) Now I want users to share posts, for which I created a table repost_user as follows | repost_user | original_post_user | post_id | repost_date | I want to get posts from people that user following, which includes reposts too.. How do I do this? EDIT : How my resultset should look post_id | post_content | post_user | post_date | is_repost | repost_user | repost_date for eg if its normal post the row should look like 23 | <some content> | 3 | <post date> | false | null | null | if its a repost the row would be 23 | <some content> | 3 | <post date> | true | 2 | <repost_date> |

Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop Posted: 28 Sep 2013 07:24 PM PDT I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials. I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform. Is there a version of Oracle XE 11g R2 available for Windows 7? And if so, could you please point me to it? Thanks...

Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour! Posted: 28 Sep 2013 06:24 PM PDT I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour. I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar. Currently the fastest way i've found to get the information into my DB until now was: Process raw data into CSV files. Load Data In File to rawData Table. Insert rawData table into Table1. (on dupe key do my function) Truncate rawData Repeat. Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour. I got 16G of ram so I set my Key_Buffer_Pool to 6G. I have my query cache pool to 16M I have my query cache limit to 10M I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not. Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow. Maybe allowing more table cache? Or even Query Cache? mysql sql mysqli innodb myisam Merge Code: b.3_InMarket = (b.3_InMarket OR r.3_InMarket), To compare my 2 bool columns. Update Ok I set Raid0 Changed my query to Lock Write on tables when inserting When importing csv im disabling keys then re-enabling them before upsert. Changed concurrent_insert to 2

USER_ID field in alert logs (also in V$DIAG_ALERT_EXT view) Posted: 28 Sep 2013 12:24 PM PDT Does anyone know what triggers the USER_ID field in the log.xml to be populated? The value also exists in the V$DIAG_ALERT_EXT view. I've found by observing the logs that if a temp tablespace fills up, it will log the USER_ID of the problematic SQL statement causing the issue. But other than that, it appears that value is always NULL.

How can I generate a usage log? Posted: 28 Sep 2013 05:48 PM PDT I'm trying to automatically generate an events log report in SQL Server that records events including successful login, Create/Delete Account, An Account was Unlocked, Account locked due to maximum login attempts being reached. This query will also be customized for After Hours Login. I already know that some of data requested can be generated by using SQL Profiler or C2 audit, Event logs and the sys.login_token, sp_who, etc. However, I cannot get to the SQL script that would allow me to run the reports at any given time.

Bitmask Flags with Lookup Tables Clarification Posted: 28 Sep 2013 08:24 PM PDT I've received a dataset from an outside source which contains several bitmask fields as varchars. They come in length as low as 3 and as long as 21 values long. I need to be able to run SELECT queries based on these fields using AND or OR logic. Using a calculated field, where I just convert the bits into an integer value, I can easily find rows that match an AND query, by using a simple WHERE rowvalue = requestvalue, but the OR logic would require using bitwise & in order to find matching records. Given that I would need to work with several of these columns and select from hundreds of millions of records, I feel that there would be a huge performance hit when doing bitwise & operations to filter my SELECT results. I came across this answer from searching and it looked like it may fit my needs, but I need some clarification on how it is implemented. Is this as simple as creating a lookup table that has all possible search conditions? Example for 3 bits using (a & b) (Edit: Wrong bitwise op) 001,001 001,011 001,101 001,111 010,010 010,011 010,110 011,011 011,111 etc The author mentions that it's counter-intuitive initially, but I can't help but feel I'm interpreting the solution incorrectly, as this would give me a single lookup table with likely billions of rows. Any clarifications on the answer I linked above or other suggestions that would preserve the existing database are appreciated. Edit: A more concrete example using small data. Four flags, HasHouse,HasCar,HasCat,HasDog, 0000 is has none, 1111 is has all. Any number of flags, from all to none, can be flipped, and results must be filtered where selection matches all (Using exact value comparison) or at least 1 (Using bitwise &). Adding a single calculated column for each bitmask is ok, but adding a column for each bit for more than 100 bits, coupled with how to insert/update the data is why I'm trying to find alternative solutions.

SQL Server 2012 catalog.executions to sysjobhistory - any way to join them? Posted: 28 Sep 2013 03:24 PM PDT I have exhausted my resources and can't find a foolproof way to join the ssisdb.catalog tables to the jobs that run them. Trying to write some custom sprocs to monitor my execution times and rows written from the catalog tables, and it would be greatly beneficial to be able to tie them together with the calling job.

SQLite writing a query where you select only rows nearest to the hour Posted: 28 Sep 2013 05:24 PM PDT I've got a set of data where data has been taken approximately every minute for about three month and the time has been stored as a unix timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing. What I need is the row nearest to the hour, with the timestep rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour. Where a matching hour could not be found it would be helpful if the query could include a time but no value. I realise I'm asking a lot, but this would be incredibly helpful Thanks for taking the time to read this. James BTW, The table is just PK (autoincrement),timestamp,value, sensor id(FK). I've tried this to get the data out: SELECT strftime('%S',time, 'unixepoch'),strftime('%M',time, 'unixepoch'),strftime('%H',time, 'unixepoch'), strftime('%d',time, 'unixepoch'), strftime('%m',time, 'unixepoch'), strftime('%Y',time, 'unixepoch'), value from Timestream where idSensor=359;

Mongo replication lag slowly increasing Posted: 28 Sep 2013 09:24 AM PDT I am running a replica-set in production with slaveOk = false using mongo 2.0.7 in AWS The replication lag on one of the servers is close to 58 hours. This replication lag is sometimes decreasing ( at very slow rate ) and sometimes increasing, but overall its lag is increasing 1-2 hours per day. I restarted the server, but seeing no benefit The read rate is 10 times of the read rate, compared to the other secondaries. I checked the logs, but nothing weird The lock % is very high ( close to 100% ) only on that server( and there is no such issues with the capped collection ). Other secondaries hardly have 10-20 lock% One more weird thing that I noted about this server ( secondary ) is that mms is showing the version to be 2.2.1 and type as 'standalone' server, which is not the case ( cross checked using db.version() and rs.status() command )

Unable to start Oracle Database Server : Get an error as I try to start it Posted: 28 Sep 2013 10:24 AM PDT I just installed Oracle Database Express Edition 11g Release 2 for windows.It created a short cut icon on the desktop : but as I click this icon I see this dialog box : What is it ? How do I start my Oracle Database Server ?

SQL Server replication subscriptions marked as inactive Posted: 28 Sep 2013 11:24 AM PDT Is there any way to force SQL Server NOT to mark subscriptions as inactive, ever? It happens sporadically when there are connection issues and I don't want to have to reinitialize the subscription every time. Note, I'm not talking about the subscriptions being marked as expired...just as inactive. Thank you.

What's the difference between a temp table and table variable in SQL Server? Posted: 28 Sep 2013 09:18 AM PDT This seems to be an area with quite a few myths and conflicting views. So what is the difference between a table variable and a local temporary table in SQL Server?

Is nested view a good database design? Posted: 28 Sep 2013 10:51 AM PDT I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect statement. Students SELECT studentID, first_name, last_name, SchoolID, ... FROM students CREATE VIEW vw_eligible_student AS SELECT * FROM students WHERE enroll_this_year = 1 Teachers SELECT TeacherID, first_name, last_name, SchoolID, ... FROM teachers CREATE VIEW vw_eligible_teacher AS SELECT * FROM teachers WHERE HasCert = 1 AND enroll_this_year = 1 Schools CREATE VIEW vw_eligible_school AS SELECT TOP 100 PERCENT SchoolID, school_name FROM schools sh JOIN vw_eligible_student s ON s.SchoolID = sh.SchoolID JOIN vw_eligible_teacher t ON s.SchoolID = t.SchoolID At my workplace, I have investigated one of our in-house database application. I checked through the objects found out that there are two or three layers of the view stack each other. So that was remind me about what I read in the past. Can any one help explaining it? If it is not OK to do so, I want to know that it is limited to just SQL Server or it is for database design in general. Additional Info: I updated an example from my company. I change a bit to be more general without too many technical (too many columns in this example). Mostly the nested view we used is based on abstract or aggregated view. For example, we have a large student table with hundred of columns. Say, Eligible Student View is based on students who enrolls this year. And student eligible view could be use other places such as in stored-procedure.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[MS SQL Server] CACHESTORE_SQLCP flush automatically?

---

• CACHESTORE_SQLCP flush automatically?
• Log not available error 9001
• Calculating Memory usage

CACHESTORE_SQLCP flush automatically? Posted: 27 Sep 2013 11:38 PM PDT Hi.I have checked my production database for top memory consumer components Database enabled - AdHoc workloads and force parameterzation (for the reason too much single plans created and wasted memory)1. CACHESTORE_SQLCP - using Constantly 2.5GB due to database do not have SP and all.. Application request Ad Hoc query only..2. TokenAndPermUserStore - using 800 MBI cleared the TokenAndPermUserStore by using command (dbcc freesystemcache('TokenAndPermUserStore') and automatically reduce the CACHESTORE_SQLCP memory usage, what could be reason? Thanksananda

Log not available error 9001 Posted: 19 Dec 2010 11:31 PM PST Came in to work this morning to face a bunch of alerts for severity 21 errors."DESCRIPTION: The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database."Hmm.. The drive the log is on was available and logs for other DB were on it. Plenty of space left. Window Application event log showed no errors other than the one listed above. Windows System log showed no errors. I ran dbcc checkdb on the database and the only error reported was that the log was not available.I took the database offline, then brought it online again and all seems good now. DBCC Checkdb gives no errors. DBCC loginfo(0) gives info, so I'm assuming the log is available. Now just trying to figure out what happened.Took a closer look at the SQL error log and I see this:12/20/2010 02:30:01,spid20s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:30:01,spid20s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:20:29,spid18s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:20:29,spid18s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:00:14,spid217,Unknown,Recovery completed for database NetPerfMon (database ID 18) in 6 second(s) (analysis 1 ms<c/> redo 1212 ms<c/> undo 4437 ms.) This is an informational message only. No user action is required.12/20/2010 02:00:13,spid217,Unknown,1 transactions rolled back in database 'NetPerfMon' (18). This is an informational message only. No user action is required.12/20/2010 02:00:09,spid217,Unknown,21 transactions rolled forward in database 'NetPerfMon' (18). This is an informational message only. No user action is required.12/20/2010 02:00:03,spid19s,Unknown,The log for database 'SpotlightManagementFramework' is not available. Check the event log for related error messages. Resolve any errors and restart the database.12/20/2010 02:00:03,spid19s,Unknown,Error: 9001<c/> Severity: 21<c/> State: 5.12/20/2010 02:00:03,spid212,Unknown,FILESTREAM: effective level = 0<c/> configured level = 0<c/> file system access share name = 'MSSQLSERVER'.12/20/2010 02:00:03,spid212,Unknown,Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install.12/20/2010 02:00:03,spid15s,Unknown,Transaction (Process ID 15) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.12/20/2010 02:00:03,spid15s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.12/20/2010 02:00:03,spid15s,Unknown,Transaction (Process ID 15) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.12/20/2010 02:00:03,spid15s,Unknown,Error: 1205<c/> Severity: 13<c/> State: 51.12/20/2010 02:00:02,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 02:00:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 01:30:01,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 01:30:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 01:00:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 01:00:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 00:30:01,spid206,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 00:30:01,spid206,Unknown,Starting up database 'SpotlightManagementFramework'.12/20/2010 00:00:23,spid18s,Unknown,This instance of SQL Server has been using a process ID of 1788 since 12/7/2010 9:47:54 PM (local) 12/8/2010 3:47:54 AM (UTC). This is an informational message only; no user action is required.12/20/2010 00:00:01,spid295,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/20/2010 00:00:01,spid295,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 23:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 23:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 23:00:01,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 23:00:01,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 22:30:02,spid210,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 22:30:01,spid210,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 22:00:01,spid223,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 22:00:01,spid223,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 21:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 21:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 21:00:01,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 21:00:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 20:30:02,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 20:30:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 20:00:01,spid222,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 20:00:01,spid222,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 19:30:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 19:30:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 19:00:03,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 19:00:02,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 18:30:01,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 18:30:01,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 18:00:01,spid218,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 18:00:01,spid218,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 17:30:01,spid196,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 17:30:01,spid196,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 17:00:02,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 17:00:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 16:30:01,spid214,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 16:30:01,spid214,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 16:00:01,spid229,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 16:00:01,spid229,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 15:30:02,spid205,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 15:30:02,spid205,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 15:00:02,spid219,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 15:00:01,spid219,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 14:30:01,spid213,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 14:30:01,spid213,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 14:00:01,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 14:00:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 13:30:01,spid208,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 13:30:01,spid208,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 13:00:02,spid223,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 13:00:01,spid223,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 12:30:01,spid222,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 12:30:01,spid222,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 12:00:02,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 12:00:01,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 11:30:01,spid203,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 11:30:01,spid203,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 11:00:01,spid220,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 11:00:01,spid220,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 10:30:01,spid206,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 10:30:01,spid206,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 10:00:02,spid212,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 10:00:02,spid212,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 09:30:02,spid214,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 09:30:01,spid214,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 09:00:03,spid225,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 09:00:01,spid225,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 08:30:01,spid199,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 08:30:01,spid199,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 08:00:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 08:00:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 07:30:02,spid204,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 07:30:01,spid204,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 07:00:01,spid216,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 07:00:01,spid216,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 06:30:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 06:30:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 06:00:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 06:00:00,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 05:30:01,spid201,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 05:30:01,spid201,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 05:00:01,spid217,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 05:00:01,spid217,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:30:01,spid207,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:30:01,spid207,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:00:04,spid215,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:00:04,spid215,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 04:00:00,spid207,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 04:00:00,spid207,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 03:30:01,spid198,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 03:30:01,spid198,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 03:00:05,spid234,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 03:00:04,spid234,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 02:52:32,spid78,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 02:52:32,spid78,Unknown,Starting up database 'SpotlightManagementFramework'.12/19/2010 02:52:29,spid71,Unknown,CHECKDB for database 'SpotlightManagementFramework' finished without errors on 2010-12-17 02:50:42.070 (local time). This is an informational message only; no user action is required.12/19/2010 02:52:29,spid71,Unknown,Starting up database 'SpotlightManagementFramework'.2 AM is when my maintenance jobs run. It looks like, for some reason, checkdb got stuck in a loop on this database and had tons of spids working. The first "log uinavailable" error occurs at 12/20/10 2:00:03 with a state value of 5. The subsequent ones all have a state value of 1. I haven't been able to find out what the state value represents. I also noticed that the DBCC CHECKDB messages are appearing at times when DBCC is not scheduled to be run - at roughly half hour intervals through out the day. My transaction log backups run then, but I have verified that job does not perform dbcc.It looks like the clusters of checkdb messages started happening on 12/7/10 at 10 PM, which is just after I restarted the server after applying SQL 2008 SP2.Anyone have any ideas?

Calculating Memory usage Posted: 27 Sep 2013 03:15 PM PDT Hi, I wanted to calculate the Total SQL Server Memory usage using DMVs.From sql server 2008, I have below direct query. What I am trying is to get the same value using sys.dm_os_memory_clerks. For memory troubleshooting we normally use DBCC MEMORYSTATUS and sys.dm_os_memory_clerks.I am personally comfortable with tsql and wanted to use sys.dm_os_memory_clerks.Infact, I use sys.dm_os_memory_clerks to see where sql server memory has gone or in other wordswhich component is taking up more memory within sql server. But here, i am interested in only calculating total sql server memory usage which i am not able to get using below query. Notes: I am working on SQL 2008 Developer Edition , 10.0.5500 x64, OS 64bit i.e. win 7 sp1 and LPM is enabled as service account is Local System.-- sql 2008 direct query to find out sql server overall memory usage select virtual_address_space_committed_kb/(1024) as [Total Overall SQL Server Memory usage(MB)]--RAM+pagefile. Does this include AWE and MTL memory as well when we say commit;from sys.dm_os_process_memorygo--output --249 mb-- Reference Link:http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspxI am trying get the same value using sys.dm_os_memory_clerks. Why am I not getting the same value. Am I missing anything to sum up? Basically, I wanted to use this query for sql 2005 instances. select SUM(single_pages_kb+multi_pages_kb+awe_allocated_kb+shared_memory_committed_kb+virtual_memory_committed_kb)/1024 from sys.dm_os_memory_clerks --output --144 mb I am getting 144 as my output, above sql 2008 query returns 249 value. Why there is a difference? I calculated using perfmon counters i.e Process:Private Bytes(Sqlservr) + SQL Buffer Manager: Total Pages which I am getting value close to 245 which is acceptable.Reference Link :http://blogs.msdn.com/b/joesack/archive/2009/01/08/find-non-buffer-pool-memory-memtoleave-in-private-bytes.aspxBut why using sys.dm_os_memory_clerks I am getting a low value ?Please help.Thank you.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server 2008 Administration To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610