New answers tagged

0

Although the question Why Does the Transaction Log Keep Growing or Run Out of Space? provides lots of information on transaction log management, it does not mention the main performance problem of Virtual Log Files, which I will address below. Since your question is about managing size and performance of the transaction log, I'll write about each subject ...


0

In my experience, Sql Server sometimes wants to scan small tables (even when they have indexes that you'd think would be used) which can lead to deadlocks on a hot table. It might be a kludge solution, but I've had tables like this and was able to solve my problem by actually adding lots of 'bogus' rows to 'widen' the table and force Sql Server (optimizer)...


0

The syntax of you query is not often called "correlated subquery". A correlated subquery would be like this: SELECT ndo.sku AS ParentSKU, ( SELECT SUM(g.sessions) FROM gasessiondata AS g WHERE g.date >= ndo.FromDate AND g.date <= ndo.ToDate ) AS s FROM Temp.NumberOfDaysOnline AS ndo ; Your query ...


0

To address this problem, I did the following (I'm using PostgreSQL for this, but SQL Server has CTEs and RECURSIVE CTEs and window/analytic functions. I doubt if this can be done in MySQL - maybe using variables - not sure). PostgreSQL is as powerful as the big boys - MySQL is really a toy! Created a table: CREATE TABLE stock (sku int, datein date, dateout ...


0

Try this: SELECT eventsink.RedEvent.DeviceId, MAX(eventsink.RedEvent.Created), BranchNumber, CountryCode FROM eventsink.RedEvent JOIN md.Elevator ON md.Elevator.DeviceId = eventsink.RedEvent.DeviceId JOIN eventsink.RedMasterDataEvent ON eventsink.RedMasterDataEvent.DeviceId = eventsink.RedEvent.DeviceId ...


1

After you change the recovery model to SIMPLE, perform a full backup, that should truncate your tlog. From MSDN: After Switching to the Simple Recovery Model If you switch from the full or bulk-logged recovery model to the simple recovery model, you break the backup log chain. Therefore, we strongly recommend that you back up the log immediately before ...


0

You could use INNER LOOP JOIN to cause a nested loop, but it may not improve the performance. Or use an OPTION (LOOP JOIN) hint to still allow the optimiser to decide join order (which is forced by join hints).


2

The best way is not to shrink it at all. You are not losing any data when you shrink the log or when switching recovery models. You are losing recovery points though. But there's a reason the file was sized that way. Backup your transaction logs frequently, such as every minute, to minimize its size. If the size is still bigger than what you'd think, ...


1

How about you use a view, and change the definition of the view as the months change, on a scheduled job? You would just do all queries against this view, to make sure you're always getting the latest data. Example: CREATE VIEW dbo.Sessions_month AS (SELECT Columns FROM Sessions_month_1) GO Then your job would do ...


-5

To repair a corrupt .mdb database of MS Access, one can try Compact and Repair feature of MS Access. It repairs corrupt database and makes it accessible for database users. Other than this one can also use import feature of MS Access that helps database users import their corrupt database to a new database file.


0

Adapted from here; see that for rationale. For separated tables, each should look similar to this: CREATE TABLE XtoY ( # No surrogate id for this table x_id MEDIUMINT UNSIGNED NOT NULL, -- For JOINing to one table y_id MEDIUMINT UNSIGNED NOT NULL, -- For JOINing to the other table # Include other fields specific to the 'relation' ...


1

I am not here to judge whether it is a good idea to use dynamic sql, but just for your question, you may use the following code to reach your goal DECLARE @month_number AS VARCHAR(2); DECLARE @table_name AS VARCHAR(15); DECLARE @table AS VARCHAR(MAX); declare @qry nvarchar(max); SET @month_number = month(getdate()); SET @table_name = 'Sessions_month_' SET @...


0

This is a bad idea. You will need to use Dynamic SQL to do this. Be aware of the performance and security implications of it. Be sure to read through the Dynamic SQL Bible to understand the pros and cons of it: http://www.sommarskog.se/dynamic_sql.html.


0

I have done it myself. select userID from UserCities where CityID IN (select CityID from school sc, student st where sc.SchoolID = st.SchoolID and StudentID = 382) and userID IN (select userID from users where tagNumber = 4521) ;


0

You are doing a form of "pivoting"? Two JOINs gives you two columns. GROUP_CONCAT gives you one column with some delimiter between the items. If you have a business reason for one versus the other, that will be the deciding factor. If either "format" of the output is acceptable (which is assume you are assuming), then the next question is whether you ...


0

How about using windowing functions? WITH Numbered AS ( SELECT *, ROW_NUMBER() OVER (PARTITON BY Event ORDER BY DateTime DESC) AS rownum FROM VehicleData WHERE Registration = @Registration AND DateTime < @DateTime ) SELECT V.Registration, V.DateTime, V.Event, V.Data FROM Numbered V WHERE rownum = 1; Make sure you have an index on: (Registration, Event,...


3

The hierarchy_table has 5 columns that all reference the name_table, so you need 5 joins. It may be better to use LEFT joins instead of INNER, in case some of these columns are nullable and you still want the rows returned: SELECT o.name AS object, p1.name AS parent_1, p2.name AS parent_2, c1.name AS child_1, c2.name AS child_2 ...


2

As you mentioned the contactNo is INT NOT NULL, in your CASE statement currently you are handling only for the value 8022 and not for the remaining values. If you add the ELSE 0 in the case statement it will solve the issue. It will select 0 for the entries other than contactNo = 8022 So the working code will be: SELECT id, queueid, CAST((CASE contactNo ...


3

CREATE TABLE IF NOT EXISTS `tblproduct` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `code` varchar(255) NOT NULL, `image` text NOT NULL, `price` double(10,2) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `product_code` (`code`) ) <<<<------- put semi-colon here. Your problem here is with the last line of your ...


3

You can use alias name for tables involved in query. select b.name object, c.name parent_1, d.name parent_2 from hierarchy_table a, name_table b, name_table c, name_table d where a.object_id = b.name_id and a.parent_id_1 = c.name_id and a.parent_id_2 = d.name_id


0

This would be better accomplished through a series of inserts. Create a temporary table that contains all of the columns from table Appointments and table Events. Insert into the temp table using each of your join predicates in the order of importance that you have listed above. For each insert after the first one (the one joined on appointment_id), use the ...


2

Lennart's first query produces a result like this (i.e. side-by-side): id|cola|id|cola 1|A1|2|A2 8|A1|9|A2 If you want a result like the one below (i.e. in a more "tabular" format), which might be better for you? id|cola 1|A1 2|A2 8|A1 9|A2 then use this query: SELECT t1.id, t1.cola FROM tab1 t1 JOIN tab1 t2 ON t1.id + 1 = t2.id WHERE t1.cola = 'A1' ...


3

Since you don't mention any specific DBMS I'll assume first that there are no window functions: select A.Id, A.ColA, B.Id, B.ColA from T as A join T as B on A.Id + 1 = B.Id where A.ColA = 'A1' and B.ColA = 'A2' Assuming that window functions do exists: select Id, ColA, Lead_id, LeadColA from ( select Id, ColA , Lead(id) over ( order by ...


0

Much simpler, but does not give you exactly the same output. Try this: SELECT productID, GROUP_CONCAT(DISTINCT imageURL SEPARATOR ' ') FROM images GROUP BY productID (DISTINCT, SEPARATOR, and ORDER BY are optional; see the docs.)


0

Subqueries are the easiest solution for the problem, I think. You can so a simple select or create a new table with CREATE TABLE ... FROM SELECT ...: select product_id, (select image_url from images as images_1 where images_1.product_id = images_o.product_id limit 0, 1) as image_url_1, (select image_url from images as images_2 where images_2....


0

create table #tbl2 (f1 int identity , f2 int) insert into #tbl2 select ProductId from dbo.Table_1 group by ProductId declare @index int = 1, @count int = (select MAX(f1) from #tbl2), @id int while @index <= @count begin create table #tbl (f1 int identity, f2 nvarchar(50)) set @id = (select f2 from #tbl2 where f1 = @index) insert into #tbl ...


0

The approach I usually use for this type of "get the last record per group" query looks something like the following. WITH V AS ( SELECT VD.Registration, VD.DateTime, VD.Event, VD.Data FROM VehicleData VD WHERE VD.Registration = @Registration AND VD.DateTime < @DateTime AND VD.Event in (SELECT Name FROM EventList) ) SELECT V....


1

Try Something like if you wish to use joins : select name from table1 t1 inner join table2 t1 on t1.id=t2.id where t2.status = 'closed'; or select name from table1 t1, table2 t2 where t1.id=t2.id and t2.status = 'closed';


1

Make your where clause operate conditionally based on your input value like this: SELECT URYRR_FORWARDS_REC_H.CB_LINE as "CB_LINE", URYRR_FORWARDS_REC_H.CB_LINE_DESCRIPTION as "DESCRIPCION", URYRR_FORWARDS_REC_H.UYU_BALANCE_FW as "MONEDA LOCAL", URYRR_FORWARDS_REC_H.USD_BALANCE_FW as "MONEDA EXTRANJERA", URYRR_FORWARDS_REC_H.UYU_BALANCE_COA as "MONEDA LOCAL"...


1

What is probably happening is that SQL Server wants to download the whole remote table to your tempdb before attempting to join to the local table, including the XML column. Try something like this: SELECT TA2.ID FROM [DB].[dbo].[table1] TA2 INNER JOIN ( SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol FROM [linkedsrv].[DB].[dbo].[...


0

To solve this problem, I did the following (+1 for the question BTW - it was an interesting challenge and I learnt a lot by going through it). I used PostgreSQL to do this - don't have a SQL server instance running, but the principles should be the same. Also, it might be nice for your next question (in a decade's time?) to have DDL and DML :-) Created a ...


0

You can do this for all databases (even MySQL - which doesn't have Window functions or CTEs - i.e. WITH clauses). To solve this, I did the following: Created a table CREATE TABLE table1 (Investor_ID int, my_date DATE, type VARCHAR(20), amount int); Note, never have a column named "date" (or any other SQL keyword). Populated the table: INSERT INTO ...


1

You could do both a union of the tables and an intersection of the tables and then get the difference between these. create table table1 as (select 1 id, 'Aron' name, 23 age from dual); insert into table1 values (2,'Bary',30); create table table2 as (select 1 id, 'Aron' name, 21 age from dual); insert into table2 values (2,'Bary',30); ( select * from ...


0

Sounds like you don't care about the records unless they are of type "Balance Update." Also looks like you're just trying to return the highest of the "balance Update" records, and not a Sum of them. In that case, I think this will do the trick. This query shows what the second one is doing, which is using the ROW_NUMBER functionality to order the records ...


0

Have you tried adding "TOP 1" to the selects? SELECT TOP 1 MAX(f3.History_date).... SELECT TOP 1 MAX(encounter_date).... TOP 1 should guarantee only one record is returned, even if you have two records with the same MAX value (which seems like your case). Also, have you tried running your SELECT statements on their own to confirm that you're only ...


5

Both queries are exactly the same, should return exactly the same result set and should produce exactly the same Execution plan. BUT the second query is using ANSI-92 SQL syntax . The first one is using the older (very old) version of SQL Joins. I can think of a few reasons why you should use the ANSI-92 SQL Syntax. It separates the Join Conditions ...


0

I don't think this is the appropriate medium to be asking that question. What you can do is either visit YouTube and search for tutorials where they design attendance systems or you can contact a freelancer. For now you can take records in Microsoft Excel since it simpler to use and then migrate the data once you have a mysql database setup.


1

It would appear so. I have not tried these, but the following is the list of items that can be added to an SSDT SQL Server Database Project: Broker Priority Contract Endpoint (ServiceBroker over TCP) Endpoint (SOAP over HTTP) Event Notification Message Type Queue Remote Service Binding Route Service


0

1) Yes. It's a maintenance/documentation nightmare but technically there's no reason it wouldn't work. 2) In general each null will be one bit of storage. So 80 null fields might be 10 bytes per row. The full answer is that it varies depending on data type but with varchar for the most part it's a good rule of thumb. Some alternatives where you expect ...


0

question is not clear to me but this might be what you are looking for select l.license_num, cr.course_topic_code, cr.required_minutes from Course_Requirements cr join Licensees l on cr.license_level = l.license_level and cr.residential = isnull(l.residential,1);


2

The query posted by @mrdenny didn't seem to return reliable data for me. I had more luck this way. select b.TABLE_TYPE, a.* from INFORMATION_SCHEMA.COLUMNS a LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME


0

Having been pointed in the right direction by @Phil, it looks like the following merge will work: MERGE userabilitytest USING (VALUES (1, 'EDITACCOUNT'), (1, 'EDITORDER'), (1, 'BOOHOO'), (1, 'FooBar') ) AS foo(account_id, ability) ON userabilitytest.account_id = foo.account_id and userabilitytest.ability ...


-3

Option Name Description NLS_LANG The current language, territory, and database character set, which are determined by session-wide globalization parameters. NLS_LANGUAGE The current language for the session. NLS_SORT The sequence of character values used when sorting or comparing text. To check the current NLS settings, type: select * from v$...


5

@Lennart's answer can be simplified to a single Group By: SELECT LogDate, StartOfDay, EndOfDay, DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds FROM ( SELECT -- instead of FORMAT better use a Standard SQL CAST: CAST(LogDateTime AS DATE) AS LogDate, MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay, ...


1

You can modify your query to: SELECT LogDate, MAX(StartOfDay), MAX(EndOfDay), DATEDIFF(s, MAX(StartOfDay), MAX(EndOfDay)) FROM ( Select LogDate, StartOfDay = MIN(case when EventCode = 1 THEN LogDateTime END), EndOfDay = MAX(case when EventCode = 2 THEN LogDateTime END), EventCode FROM ( Select ...


2

It's almost certainly going to be full backups or indexing. The queries won't show up as a bunch of memory allocated but rather as intensive disk IO. If you are tracking those counters then have a look. Some other easy ways to confirm that these are the cause of that disk IO: Looking at agent job schedules. If it wasn't checkdb, or indexing, then apps ...


0

Before you say PLE is too low, you should know threshold calculation for PLE. You can visit the URL to know that http://sqlperformance.com/2014/10/sql-performance/knee-jerk-page-life-expectancy. PLE alone don't impact performance of SQL server. There are various performance counter we should consider like Buffer cache hit ratio, disk queue length, ...


0

Unfortunately PLE alone isn't going to tell you much. What's your baseline PLE? Does it dip then steadily rise again, or does it stay low for an extended amount of time? Are you getting any performance hits? My advice would be to build up a bigger picture of the issue by looking at other memory related performance measures. Page Life Expectancy is just ...


4

Based on your image, something like the following will be fine (using a RDBMS solution): create table articles ( article_id integer PRIMARY KEY, title varchar(256) NOT NULL, url varchar(256) NOT NULL, summary varchar(256) NOT NULL, body varchar(4000) NOT NULL ); create table article_comments ( comment_id ...


0

If your database is set to full recovery model you can investigate your transaction log backups. See fn_dump_dblog for more informations.



Top 50 recent answers are included