Resource locks

hi,
I have a T-sql as part of ETL. The code selects all data
from table x (with 3 million rows) and populates table y
(which already has 1.5 million rows). There are no where
conditions no calculations - simple insert into table x
(col1,col2,...coln) select col1,col2...coln from table y.
When I run sp_lock I see a lot locks of type EXT and mode
X. Can I used hint TABLOCKX.
I noticed that if I used hint TABLOCKX for the table
inserted into then number of locks reduces drastically. If
I use hint TABLOCKX in the table selected from there is no
impact.
Anybody encountered similar issues? Any input will be
useful..
Thx,
DeepaThat means you are getting extent locks which are OK for this type operation
but if you don't have other users accessing it would be best to use a table
level lock but don't need to use TABLOCKX. Instead try TABLOCK but if you
have users in the table you are selecting from they may prevent this from
happeing.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:01cc01c3b51f$f2276eb0$a301280a@.phx.gbl...
> hi,
> I have a T-sql as part of ETL. The code selects all data
> from table x (with 3 million rows) and populates table y
> (which already has 1.5 million rows). There are no where
> conditions no calculations - simple insert into table x
> (col1,col2,...coln) select col1,col2...coln from table y.
> When I run sp_lock I see a lot locks of type EXT and mode
> X. Can I used hint TABLOCKX.
> I noticed that if I used hint TABLOCKX for the table
> inserted into then number of locks reduces drastically. If
> I use hint TABLOCKX in the table selected from there is no
> impact.
> Anybody encountered similar issues? Any input will be
> useful..
> Thx,
> Deepa
>|||Thank you Andrew. I am new to sqlserver and your help is
appreciated.
Now, this table has six indexes and dropped them before
the insert and recreate them after.
What I notice is for a brief moment the number of locks
jumps to 30,000+ before reducing to around 200 incase of
tablock and 4000 when used without tablock. Why is there a
spike initially.
Also I read that each lock resource uses 96k. My box has
2GB memory which means I can have upto (2*1024*1024*1024)/
(96*1024) which comes to about 21800 then how can the lock
resource grow to 30,000?
i encounter resource lock issues only at the time that the
o.s backup is happening. My systems people are not able to
advice me. Would you know if an o.s. backup would be heavy
on memory. I'm not a windows person either...
Many thanks,
Deepa|||Its 96 bytes Deepa and not 96KB :-)
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Yes as Hassan pointsout it is 96 bytes and not KB so you ae not as short on
memory as you think. The reason you see it spike initialy is that sql sever
starts out with row or page locks and will escalate to a table lock if it
can after a while. You say it is an OS backup, are you sure they aren't
using a sql plug-in to do sql backups as well? If you are doing a sql
backup it will take locks when it reads the data. If it is strictly an OS
backup they should eliminate the sql erver files from the backup as they are
useless from a sql server point of view and only can cause issues when
accessing the sql files.
--
Andrew J. Kelly SQL MVP
"Deepa" <anonymous@.discussions.microsoft.com> wrote in message
news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
> Thank you Andrew. I am new to sqlserver and your help is
> appreciated.
> Now, this table has six indexes and dropped them before
> the insert and recreate them after.
> What I notice is for a brief moment the number of locks
> jumps to 30,000+ before reducing to around 200 incase of
> tablock and 4000 when used without tablock. Why is there a
> spike initially.
> Also I read that each lock resource uses 96k. My box has
> 2GB memory which means I can have upto (2*1024*1024*1024)/
> (96*1024) which comes to about 21800 then how can the lock
> resource grow to 30,000?
> i encounter resource lock issues only at the time that the
> o.s backup is happening. My systems people are not able to
> advice me. Would you know if an o.s. backup would be heavy
> on memory. I'm not a windows person either...
> Many thanks,
> Deepa
>
>|||Thank you both. Yes it is 96 bytes and not 96kb.. my bad!
I have been doing tests with drop index / populate /
recreate index and testing is in progress but this is
likely to resolve the error.
Its a standard o.s backup. The files being backed up are
the backup files created by my maintenance plans - no the
datafiles.
many thanks for the response.
>--Original Message--
>Yes as Hassan pointsout it is 96 bytes and not KB so you
ae not as short on
>memory as you think. The reason you see it spike
initialy is that sql sever
>starts out with row or page locks and will escalate to a
table lock if it
>can after a while. You say it is an OS backup, are you
sure they aren't
>using a sql plug-in to do sql backups as well? If you
are doing a sql
>backup it will take locks when it reads the data. If it
is strictly an OS
>backup they should eliminate the sql erver files from the
backup as they are
>useless from a sql server point of view and only can
cause issues when
>accessing the sql files.
>--
>Andrew J. Kelly SQL MVP
>
>"Deepa" <anonymous@.discussions.microsoft.com> wrote in
message
>news:002b01c3b5dd$f18aef40$a501280a@.phx.gbl...
>> Thank you Andrew. I am new to sqlserver and your help is
>> appreciated.
>> Now, this table has six indexes and dropped them before
>> the insert and recreate them after.
>> What I notice is for a brief moment the number of locks
>> jumps to 30,000+ before reducing to around 200 incase of
>> tablock and 4000 when used without tablock. Why is
there a
>> spike initially.
>> Also I read that each lock resource uses 96k. My box has
>> 2GB memory which means I can have upto
(2*1024*1024*1024)/
>> (96*1024) which comes to about 21800 then how can the
lock
>> resource grow to 30,000?
>> i encounter resource lock issues only at the time that
the
>> o.s backup is happening. My systems people are not able
to
>> advice me. Would you know if an o.s. backup would be
heavy
>> on memory. I'm not a windows person either...
>> Many thanks,
>> Deepa
>>
>
>.
>

resizing a subreport

Hello:
I have a subreport displaying a title (actually a table with some rows of
information) that is common to many different reports.
I'll like to implement this title in a subreport and link it from the main
reports, but some of them are in portrait format while others are landscape.
Is there any way of resizing a subreport?
Thanks,
Daniel Bello Urizarri.Daniel Bello wrote:
> Hello:
> I have a subreport displaying a title (actually a table with some rows of
> information) that is common to many different reports.
> I'll like to implement this title in a subreport and link it from the main
> reports, but some of them are in portrait format while others are landscape.
> Is there any way of resizing a subreport?
> Thanks,
> Daniel Bello Urizarri.
As far as I know, subreports can be minimally formatted size-wise;
however, it is very limited and portrait versus layout formatting is
not feasible. The subreport could be put inside a list, which could
control the layout a little better. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

Resize rectangle

I am putting a rectangle around my table. Table's height can grow alone with
a number of rows.
Can I resize a height of the rectangle accordingly?
ThanksOn Apr 4, 12:12 pm, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am putting a rectangle around my table. Table's height can grow alone with
> a number of rows.
> Can I resize a height of the rectangle accordingly?
> Thanks
If I'm understanding you correctly, the rectangle will automatically
grow w/the size of the table.
Regards,
Enrique Martinez
Sr. Software Consultant

Resetting rows count

I have a table in Database where I have added and removed rows, during the
period of developing applications. Now I have removed all row, but when new
are added, their IDs don't start from 1.
Is there a way to reset some counter or something?
hi Nikolay,
"Nikolay Petrov" <johntup2@.mail.bg> ha scritto nel messaggio
news:%23AvGpW5gEHA.1184@.TK2MSFTNGP12.phx.gbl...
> I have a table in Database where I have added and removed rows, during the
> period of developing applications. Now I have removed all row, but when
new
> are added, their IDs don't start from 1.
> Is there a way to reset some counter or something?
>
you are probably referring to a table colum's property known as IDENTITY...
in order to reset it's internal value, you can have a look at the DBCC
CHECKIDENT
(..)http://msdn.microsoft.com/library/de.../en-us/tsqlref
/ts_dbcc_5lv8.asp action...
if you want to delete all rows from a user table and reset the IDENTITY
property on the same time, you can issue a TRUNCATE TABLE statement instead
of DELETE FROM...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Reset IDENTITY seed

Hello,
Can I reset the IDENTITY seed of a Table column without delete/drop the table?
I want to delete all the table rows, restore de seed, and restore abackup made on a XML (using SET IDENTITY_INSERT Table ON)
I cant drop the table due to acount restricctions.
regards,
Edu
You could try
TRUNCATE TABLE MyTable|||Check outDBCC CHECKIDENT.|||Thanks,
DBCC CHECKIDENT. works fine!
Regards,
Edu

Reset Identity DataType

How can I reset an identity Int column back to start with 1 if I remove all rows from the table?

Thanks,TRUNCATE TABLE tablename

Reset Excel Destination for Error Logging

I am using sheets in an Excel spreadsheet to receive redirected error rows. The trouble is that it keeps appending the rows to the bottom of the sheet - even if I delete the rows in the spreadsheet. If I remove the spreadsheet all together then I need to set-up the Excel Desitnation Sheets again.

Each time I run the Control Flow I would like it to be removing old log entries and writing in the new ones.

There must be an easy way?

Unfortunately the Excel driver does not support anything like TRUNCATE TABLE. Even if you opened Excel and cleared the contents of those rows, the driver would still see them as "used" ... the only solution is to delete the rows themselves from within Excel. And you would want to check the range definition to make sure that you were in fact deleting all the rows in the range.

Of course the cleaner solution would be to drop and recreate the table. You could use an Execute SQL task for this purpose, and probably get one of the SSIS components to write the SQL for you to copy and paste in.

-Doug

Require Solution for this SQL problem

Hi,

I have two tables TableA and TableB. I have a stored procedure which
runs every morning and based on some logic dumps rows from TableA to
TableB. In Table B there are two additional colums ID and RunID. ID is
a normal sequence applied for all rows. But the RunId should be
constant for a run of stored proc.

So for e.g. say structure of Table A and Table B

Table A Table B

col1 ID RunID col1

Now when I run stored proc I want rows copied as below

TableA TableB

col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2

The next day when stored prc runs I want data as

TableA TableB

col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
row1 3 2 row1
row2 4 2 row2

So for every run of stored proc each day I want the Run ID incremented
only by one and ID is normal sequence which increments for allrows
inserted.

Please help.

Nick"Nick" <nachiket.shirwalkar@.gmail.comwrote in message
news:1189853588.129281.216870@.n39g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Hi,
>
I have two tables TableA and TableB. I have a stored procedure which
runs every morning and based on some logic dumps rows from TableA to
TableB. In Table B there are two additional colums ID and RunID. ID is
a normal sequence applied for all rows. But the RunId should be
constant for a run of stored proc.
>
So for e.g. say structure of Table A and Table B
>
Table A Table B
>
col1 ID RunID col1
>
Now when I run stored proc I want rows copied as below
>
TableA TableB
>
col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
>
The next day when stored prc runs I want data as
>
TableA TableB
>
col1 ID RunID col1
row1 1 1 row1
row2 2 1 row2
row1 3 2 row1
row2 4 2 row2
>
So for every run of stored proc each day I want the Run ID incremented
only by one and ID is normal sequence which increments for allrows
inserted.
>
Please help.
>
Nick
>

Lookup the ROW_NUMBER() function.

--
David Portas

Request timeout problem

Hi everybody.

Got a nice little problem here. I have a accessdatabase containing 100 000 rows. Im fetching these rows to a dataset and then inserting them, row by row, to a MSSQL dB. The dB and IIS is running on the same server. I also have full control over this webserver, so I pushed theServer.ScriptTimeout valueup to 3600 sec (both in IIS and in the c# code) but when executing this query (witch aprox. take me 8 minutes) I recieve aError Code: 408. The operation timed out. The remote server did not respond within the set time allowederror.

Someone got a clue for me? :)

-Thomas

Perhaps retrieving 100,000 rows and inserting them one at a time into another server, from a front end is not a good idea. Try looking into other options like (1) creating a text file from access and doing a BULK INSERT into SQL server or (2) creating a DTS package or (3) check if OPENROWSET works. Read up books on line for each of these options and see which works best for you.

|||

Ok, I will do :) Thanks for the quick reply!

Got a tip on how to generate custom reports on this as well? Have a select statement based on a BETWEEN two DATETIME stamps, it take some time, but seems to work ok, but I'm always looking for a way to improve this :)

Appreciate it!

Representing rows as columns

Hi
I have a table in SQL Server 2000 that has following data:

PunchTime PunchType
11:45:00 In
12:45:00 Out
1:45:00 In
3:15:00 Out

Is there a way in SQL to represent this in the following format:
In Out In Out
11:45:00 12:45:00 1:45:00 3:15:00

ThanksYou specification is unclear in several respects. What is the datatype of
the PunchTime column (DATETIME or CHAR maybe)? What is the primary key of
this table? Do we know whether the times are AM or PM? Why is 1:45 shown
after 12:45 in your required result?

The best answer will be to do it in your client application. What you want
is purely presentational and presentational functionality belongs
client-side.

--
David Portas
SQL Server MVP
--|||[posted and mailed, please reply in news]

Rajeev (navvyus@.yahoo.com) writes:
> I have a table in SQL Server 2000 that has following data:
> PunchTime PunchType
> 11:45:00 In
> 12:45:00 Out
> 1:45:00 In
> 3:15:00 Out
> Is there a way in SQL to represent this in the following format:
> In Out In Out
> 11:45:00 12:45:00 1:45:00 3:15:00

There is no built-in construct, but there are a couple of possibilities
to depending on your requirements.

For this particular case, you could to this, under the assumption that
you have at most four rows per day:

SELECT In = in1.PunchTime, Out = out1.PunchTime,
In = in2.PunchTime, Out = out2.PunchTime
FROM tbl in1
JOIN tbl out1 ON in1.PunchDate = out1.PunchDate
LEFT JOIN tbl in2 ON in1.Punchdate = in2.PunchDate
AND in1.PunchType = in2.PunchType
AND in1.PunchType < in2.PunchType
LEFT JOIN tbl out2 ON out1.Punchdate = out2.PunchDate
AND out1.PunchType = out2.PunchType
AND out1.PunchType < out2.PunchType
WHERE in1.PunchType = 'In'
AND out1.PunchType = 'Out'

Here I have assumed there is a date column in the table, since that
would make sense. I have also been lazy and assumed that there is
always one In and one Out each day.

In a more general columns where you want dynamic column names etc,
you have to build dynamic SQL. But before you do that, check out
the third-party tool RAC, http://www.rac4sql.net/ which aspires to
be the ultimate tool for crosstab queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ReportViewer: Show toggle for specific item in a list??

Hello,
Wondering if anyone has any ideas about this:
I have ReportViewer on an ASP page with a report that has a list. The
list shows me rows and I want to be able to toggle just one of the
items in the list based on the type of item it is. Currently I can
only show the toggle icon for all the list items, like this:
+Item1
+Item2
+Item3
But I want the report to show only this:
Item1
+Item2
Item3
I've tried all manner of IIF formulas but none work. Is this even
possible?
ThanksPlease, does anyone have any experience with this?
On Mar 13, 6:11 pm, phrankbo...@.hotmail.com wrote:
> Hello,
> Wondering if anyone has any ideas about this:
> I have ReportViewer on an ASP page with a report that has a list. The
> list shows me rows and I want to be able to toggle just one of the
> items in the list based on the type of item it is. Currently I can
> only show the toggle icon for all the list items, like this:
> +Item1
> +Item2
> +Item3
> But I want the report to show only this:
> Item1
> +Item2
> Item3
> I've tried all manner of IIF formulas but none work. Is this even
> possible?
> Thanks