I've just found a blocking lock occuring in a SQL Server.
[color=blue]
>From the waitresource, I found that the blocker has the following[/color]
information:
wait_info: PAGEIOLATCH_EX
wait_resource: 14:1:564312
And the blockee has the following information:
wait_info: LCK_M_S
wait_resource: KEY: 14:405576483:2 (7501a5aa8355)
The problem is that I do not know which object is the blocker holding,
for I understand that for a PAGE lock, it is in the format of
database_id:file_id:page_id
I know the db_id, the file_id but don't know how to map the page_id
(564312) to a table/index in the database.
Can somebody shed some lights on this? thks a lot.
Hello,I not exactly sure how to determine the table when given the followinginformation:--Could not find the index for RID '999999' in index page('1:99999999'), index id ), database (whatever).--Can you someone tell how I use the system tables to determine whattable this index corresponds to? I'm assuming I used sysindexes andsysobjects somehow.Thanks in advance,JGB_DBA
We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.
When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.
Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.
Is there a way to convert an image pointer to a page ID that could beused in DBCC pagei.e.select TEXTPTR(document)FROM testdocs where id = 1resturns0xFEFF3601000000000800000003000000select convert(int,TEXTPTR(document)) FROM testdocs where id =1returns50331648dbcc page (9,3,8,1)dumps the first page of the imageI am trying to map 0xFEFF3601000000000800000003000000 - > pagenumber 8thanks
Why the index value is used in variable mapping while using for each loop container. In one of ssis package I saw the index value as 2. What 2 indicates in index?
I am trying to loop through 4 files in a folder and read the names of those files through the For each loop container task. I have 4 readme files (readme1.txt thru readme4.txt) in a folder called C:SSIS.
I have added a for each Loop container and a script task to my package. In the Variable Mapping page I have named the Variable and configured the index to be 0. The problem is when I execute the package the file name that is read is always readme1.txt. I want all the file names to be read under the folder ( in other words configure the index to be 0 thru 4). How do I configure the index so that it can read all the files?.
The following is the code I have in my script task:
I have a small problem in parameter mapping for Execute SQL Task. I am using a delete statement with 2 conditions. Followed by another Execute SQL Task which contains commit statement.
delete from tname where c1 = ? and c2 =?
where c1 is number(4) datatype and c2 is of varchar2(20) datatype in oracle.
The connection manager i am using is ORacle OLE DB provider. I am passing 2 global variables i.e g_v1 of Int32 and g_v2 of String Type.
In the parameter mapping of the Executing SQL task, i am mapping these 2 variables for c1 and c2 and changed the datatypes inside parameter mapping as Numeric for c1 and Varchar for c2.
I also set the property as ByPassPrepare = True.
When i am executing the package i getting INVALID NUMBER ERROR. i believe the SSIS is unable to perform the implict datatype converison.
For the next run, i changed the g_v1 varible datatype to Double and also i changed the parameter mapping for c1 as Doble datatype. This time it is working fine. I can see the Green signal for the 2 SQL Tasks.
But when i connected to Oracle check the count in the table, the data is not getting deleted.
Also, I set the property RetainSameConnection = TRUE for oracle connection manager. I am not able to trace this logical error.
The same is working fine in my local machine. But i am facing the problem when i deployed the same on the client machine.
Is there any problem with parameter mapping? What should be equialent Datatype for Oracle NUMBER datatype that should be used inside the SSIS package while declaring the global variable and inside the parameter mapping.
I have a table called "Jobs" on my SQL server. I have an access front end which connects to this as well as some access backend tables. We have 30+ users on the system. There are 1000s of records in this "jobs" table.
For some reason, at certain points of the day all users are stopped from updating a group of records. No-one can update values in a clump of records. It is usually 10-20 records that are numerically together. When i kick everyone off the system, and log back those particular records seem to be unlocked.
I can rule out permissions on the Jobs table. i am failry certain they have been set correctly.
To me it sounds like Page locking. Now i am new to SQL server. I am inheriting a recently created sql system.
But i notice there are multiple indexes formed on this JOBS table. I looked at one in particular. It is a "ClientName" field.. I noticed under this index > options...it says "Use Page locks when accessing the index".
Am i interpreting this right... But anyone searching under this field, will page lock on that table. And...if my memory serves me right, "page locking" locks a group of all the nearest records? Is this correct?
Does this sound like where my system is going wrong?
I can't find any explanation why is it string1 and string 2 of the footer section of my report displayed separately from the expression3 which is aligned on it and the rest of the object on the second page.
The expected design is that all Footer items should be displayed together of whether it is placed on the first page or on the last page.
As a workaround of this, I converted string 1 into an expression (Added = and enclosed the string with double quote).. As a result, all of the items in the Footer section are now placed together on the last page of the report.
I also remember one of the issue I encountered before where the Footer items where placed together on the first page and still have space at the bottom of the page, but then expression 6 is forced to display (alone) on the last page of my report.
I can't find any discussion related to this, I wish somebody could give me an idea why RS behaved like this.
Fit an intere table in same page without page break for save the excel export.
My table has a Group for order my dates.
I need to have the intere table in the same page, i don't care about blank space at the end of the page.
I can't use the page break beacuse i need an excel export in a unique sheet.. I have tested.. every page break..you'll have a different sheet in your excel export
How can I print a field that is in the dataset on each page? I added a textbox in the Page Header and use =Fields!ProjectName.value in the value property. I got an error "Fields cannot used in page header and footer."
How can I have the table header shows on each page? Currently if the data goes to the second page, there is no table header.
I know the shortcut to 'defragging' the database to eliminate and reclaim 'whitespace' in data and index pages is to setup a maintenance plan. This doesn't work for me for two reasons: 1. I like to know how things work. 2. I can not create maintenance plans with my hosted provider. I use the following T-SQL to rebuild all indexes on all tables:
But, does that not only address indexes? What about getting whitepace back from data in tables themselves? Am I over-thinking this? When you're paying for SQL by the MB and generate a lot of data (with a fair amount of churn) you want to keep it optimized and efficient, right?
hi friends.. i want to insert data in sql server2005 table through asp.net column names of this table comes from diffrent tables..... like Rollno--- in my new table....but in old table1 this is like RNO & from old table2 this is R_no so i want insert this two old diffrent table data into my new table...but i have problem coz of diffrent column names of old table... can you help me 4 that...
I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.
The New code is the new code to which the Code must be mapped to.Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.
Please help. The structure of the data is like this.
I have a page that has a drop down control that is used to control the items in a FormView. The drop down is bound to one SQLDatasource while the FormView is bound to another SQLDataSource. I've got everything working great. Selecting an item in the drop down reveals the details in the form view. the Form can be opened for editing and deleting. I even have the Insert working. But there lies my problem. Once the Insert is executed I'd like to take the Index value of the recently submitted item and set it to the SelectedIndex of my dropdown. Here is the code for each of the SQL events Sub FormViewUpdated(ByVal sender As Object, ByVal e As FormViewUpdatedEventArgs) Handles FormView1.ItemUpdated confirmation.Visible = True confirmation.Text = "<img src=""../img/smiley36.gif""> Your record has been updated."
Dim index As Integer = Me.DropDownList1.SelectedIndex DropDownList1.DataBind() Me.DropDownList1.SelectedIndex = index End Sub
Sub FormViewDeleted(ByVal sender As Object, ByVal e As FormViewDeletedEventArgs) Handles FormView1.ItemDeleted confirmation.Visible = True confirmation.Text = "<img src=""../img/smiley36.gif""> Your record has been deleted."
DropDownList1.DataBind() FormView1.ChangeMode(FormViewMode.ReadOnly) End Sub
Sub FormViewInserted(ByVal sender As Object, ByVal e As FormViewInsertedEventArgs) Handles FormView1.ItemInserted confirmation.Visible = True confirmation.Text = "<img src=""../img/smiley36.gif""> Your record has been inserted." DropDownList1.DataBind() End Sub I would like to do something similar to the Updated event in the Inserted sub. Notice I have a Me.DropDownList.SelectedIndex bit...I'd like to be able to set a variable to the new ID and then set the Me.DropDownList.SelectedIndex to that variable. My stored proc for the Insert reads like ALTER PROCEDURE dbo.usp_INSERTHelp_Faq ( @FAQQuestion varchar(500), @FAQText ntext, @FAQIndex varchar(500), @ModifiedBy int, @Help_FAQID int OUTPUT ) AS INSERT INTO Help_FAQ (FAQQuestion, FAQText, FAQIndex,ModifiedBy, Views) VALUES (@FAQQuestion,@FAQText,@FAQIndex,@ModifiedBy,0)
SELECT @Help_FaqID = @@Identity RETURN So, the Stored Proc is set up to return the latest ID. Also, my SQLDataSource for the FormView has an InsertParameter: <asp:Parameter Direction="Output" Name="Help_FAQID" Type="Int32" />
Any thoughts on how to close the loop here? How do I get my hands on the new Help_FAQID?
Table A 5.6GB 80 million rows Initial Page Space Used = 85.7% (sys.dm_db_index_physical_stats - avg_page_space_used_in_percent)
Index and data size are almost unchanged after an ONLINE rebuild of the clustered index. Page count actually goes up by 800. No fill factor or pad index settings. Page space used = 99.2%
During an OFFLINE rebuild the page count drops by ~20%. Page space used = 99.8%.
How can the pages be 99.2% used yet the number of pages hasn't gone done from the ONLINE rebuild?
I'm getting the feeling a shortcut was taken somewhere. Does an ONLINE rebuild only reorganize leaf level index pages but not any of the upper levels? Essentially it puts the pages in order but doesn't do any structure consolidation like OFFLINE?
It would seem that you're missing out on some of the benefits of defragmenting if your data is still spread across more pages and you don't get as many rows per read or it takes more reads to go down your tree to get to your data.
I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.
Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.
The structure of the data is like this. Code Name AA AA AL Aluminium ALM ALM ALT Aluminium in tonnes AR AR AUD Australian Dollars AUJPY AUJPY CAQ CAQ CC CC CCF CCF CER Carbon Emmission Reduction
The mapping table is like this: XAA AA XAL AL XMA ALM XAL ALT XAR AR
I want to map a table1 in one database to a table2 in another database. That way I can populate the table2 with the information that table1 has. How would you go about doing this. Very new at this and need help! Thanks alot .
i have one col in a nonclusted index which is bigint 8 bytes follwoing result shows min_record_size_in_bytes and max_record_size_in_bytes is 20 that is 12+8 page size is 8 kB. does that mean a record will use 20 bytes in a page or it will only use the space equalent to data stored in the col.what i have seen is page count is same weather i put some data in the col or null
I'm running a merge replication on a sql2k machine to 6 sql2k subscribers. Since a few day's only one of the merge agents fail's with the following error:
The merge process could not retrieve generation information at the 'Subscriber'. The index entry for row ID was not found in index ID 3, of table 357576312, in database 'PBB006'.
All DBCC CHECKDB command's return 0 errors :confused: I'm not sure if the table that's referred to in the message is on the distribution side or the subscribers side? A select * from sysobjects where id=357576312 gives different results on both sides . .
Hi everyone, When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ?? In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
I have a requirement to take xml file, in case the number of column changes, it should not fail the package, rather it should load the data in destination table. Destination table could be altered separately depending on xml schema by the DB team in production.
The only way to add a new column to an existing mapping that I know is to go to advanced editor and refresh. This however keeps only the default mapping (where the field names match), the rest is wiped out, so need to restore the mapping manually after that. Risky and annoying at the same time. Is there any alternative?
Hi, I use lookups to map surrogate of level 1 dimensions to my fact tables in SSIS. But how to handle a level 2 dimension with a ValidFrom and a ValidUntil date field? I do not use an IsCurrent column, because this could problem with late arriving facts.
- In dts I used an SQL statement like this:
update SA SET SA.DimProdRef = Dim.RecordID FROM SAWarenEingang SA, DimProd Dim where SA.ProduktNumber = Dim.ProduktNumber and SA.ArtikelkontoBewegungsdatum between Dim.ValidFrom and Dim.ValidUntil
Now in SSIS I want to handle the whole thing in the data flow without using a staging table: - Using Lookups: I would have to pass the date column for each inside the fact table into the lookup. That does not work. - Using Execute SQL in the data flow: would be very slow, because the statement will be executed for any line in the dataflow
My question here is as the dimension has SCD type 2 on it and every time when there is a change the persn_key gets a new key value but the fact table still points to oldest key.how to update the surrogate key on fact table to the current key value? As per the requirement fact surrogate key must be pointing to current active record on the dimension.
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
please explain the differences btween this logical & phisicall operations that we can see therir graphical icons in execution plan tab in Management Studio
I currently have an Ungrouped Dataset being displayed in a Table. One of the columns include AMOUNT. This is an example of what my report looks like atm: ================== **Report Name**
No Name Amount
Signed By XXXX ==================
I would like to display a SUBTOTAL of the Amounts for each page and, on the final page, a TOTAL of the Amounts. Yes the Last Page's SubTotal and Total will be the same. Looking like: ===============================
pg1 **Report Name**
No Name Amount Sub Total Amount Signed By XXXX
(lastpage) **Report Name**
No Name Amount
(<-----no huge spaces between Table/Subtotal) Sub Total Amount
Total Amount
Signed By XXXX
=================================
Issues: I've found a way to get a Page's SubTotal, but the way I found requires this to be put in a field on the Page Footer Section. This means that on the final page, the SubTotal will be at the top of the Page Footer and not neccessarily directly beneath the table.
So: 1. Is there a way to move the Page Footer so that its printed DIRECTLY under neath the Page Body instead of at the bottom of the Page? 2. If not, is there a way to have the SubTotal of each Page and a Final Page Total in the Page Body section. Cheers.
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop... below is my table variable and I need to create 3 indexes on this...
