backing up database

Here is the situation, I have a web application (asp.net/vb.net) with ms sql 2000 as the database. I have two database server. I would like to backup database 1 with database 2. I would like to do it at intervals and through code. If anyone can give me some ideas on how to do this or any research material, please do.

ThanksAnd the copy the stript|||You will need to set up a linked server. You can then run:

EXEC SERVER.master.dbo.xp_executesql @.backup_command

Backend generate and save the report to local drive?

Hi,
Any ideas on how to automatically generate and save the report to local
server drive backend when user click on certain link on the application?
The user do not have to render and display the report in frontend or click
on save button when prompted? The system should not prompt user for any
action instead.
Thanks in advance for any suggestion.Hi,
you can call the WebService of reporting Services (with optional
additional parameters, get the byte[] from the render method and save
this in a file on your network share of choice. That can all happen
behind the scenes and in addition if you want to asynchronously.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

AWE question (Geoff N. Hiten)

Hello
We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
application installed on it.
We also have \3gb parameter in the boot.ini
It is a cluster Active/Passive
What would you recommenf to insttall a hotfix to Passive first and then
Active or it does not matter?
Is it any problems installing a hotfix? What is about lock pages in memory?
And running AWE enabling on Passive first also?
Geoff , how much memory to give to SQL SERVER if small application is using
about 50MB AND SQL SERVER 2.7gb?
ThanksAlex,
There is no SQL 2004. I am guessing since you mention SP4, that is SQL
2000, since SQL 2005 is only at SP2 for now.
Hotfixes install to all nodes on a cluster for a particular instance. You
need to start the install from the node that is currently hosting the
instance. You must grant lock pages in memory to the SQL service account in
order to get AWE to work. AWE is a setting for the SQL instance, so it will
take effect whichever node hosts that instance.
For an 8GB system, I would start by setting SQL at 6GB and see how free
memory looks. You may be able to inch it up a bit, but it is better to
leave a little extra free memory than to run out. The only down side is
with SQL 2000 you have to stop and restart the service to enact an AWE
memory change.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alex" <test@.hotmail.com> wrote in message
news:ehk91SwkHHA.4248@.TK2MSFTNGP06.phx.gbl...
> Hello
> We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> application installed on it.
> We also have \3gb parameter in the boot.ini
>
> It is a cluster Active/Passive
> What would you recommenf to insttall a hotfix to Passive first and then
> Active or it does not matter?
> Is it any problems installing a hotfix? What is about lock pages in
> memory?
> And running AWE enabling on Passive first also?
>
> Geoff , how much memory to give to SQL SERVER if small application is
> using about 50MB AND SQL SERVER 2.7gb?
> Thanks
>
>|||Thank you Geoff N. Hiten
Yes , we are using SQL Server 2000(SP4).
Please take alook at the follwing list and tell me whether it looks good to
you.
a) SQL node1 --(Active) has 8GB
b) SQL node1 --(Passive) has 8GB
1) Install hotfix from(http://support.microsoft.com/kb/899761) on SQL1 node
(Active)
2) Install hotfix from(http://support.microsoft.com/kb/899761) on SQL2 node
(Passive)
3) Grant 'lock pages in memory' on on SQL1 node (Active)
4) Grant 'lock pages in memory' on on SQL1 node (Passive)
5) Enabling AWE on SQL1 node (Active)--set to 6GB
6) Enabling AWE on SQL1 node (Passive)--set to 6GB
Thanks you again
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:OZ4pcewkHHA.4904@.TK2MSFTNGP05.phx.gbl...
> Alex,
> There is no SQL 2004. I am guessing since you mention SP4, that is SQL
> 2000, since SQL 2005 is only at SP2 for now.
> Hotfixes install to all nodes on a cluster for a particular instance. You
> need to start the install from the node that is currently hosting the
> instance. You must grant lock pages in memory to the SQL service account
> in order to get AWE to work. AWE is a setting for the SQL instance, so it
> will take effect whichever node hosts that instance.
> For an 8GB system, I would start by setting SQL at 6GB and see how free
> memory looks. You may be able to inch it up a bit, but it is better to
> leave a little extra free memory than to run out. The only down side is
> with SQL 2000 you have to stop and restart the service to enact an AWE
> memory change.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Alex" <test@.hotmail.com> wrote in message
> news:ehk91SwkHHA.4248@.TK2MSFTNGP06.phx.gbl...
>> Hello
>> We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
>> application installed on it.
>> We also have \3gb parameter in the boot.ini
>>
>> It is a cluster Active/Passive
>> What would you recommenf to insttall a hotfix to Passive first and then
>> Active or it does not matter?
>> Is it any problems installing a hotfix? What is about lock pages in
>> memory?
>> And running AWE enabling on Passive first also?
>>
>> Geoff , how much memory to give to SQL SERVER if small application is
>> using about 50MB AND SQL SERVER 2.7gb?
>> Thanks
>>
>>
>

AWE Memory on SQL Server 2000(SP4)

Hello everybody
We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
application installed on it.
We also have \3gb parameter in the boot.ini
SQL Server is using 2,7 GB.
Application is using three processes , that each one is about 50 MB .
I took a Page Life expectancy counter which was under 300 for about 10-15
minuets and after grown up till 320 ... and then droped again...and up
again..
Do you think I need to enable AWE? If you do , how much memory to give to
SQL Server?
I have heard that there is a problem with SP4 and AWE, am I right?
Thank you.Definitely enable AWE. Also make sure that /PAE is in the boot.ini file.
You will need to apply at least one hotfix after SP4 to get AWE to woork
correctly.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alex" <test@.hotmail.com> wrote in message
news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hello everybody
> We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> application installed on it.
> We also have \3gb parameter in the boot.ini
> SQL Server is using 2,7 GB.
> Application is using three processes , that each one is about 50 MB .
> I took a Page Life expectancy counter which was under 300 for about 10-15
> minuets and after grown up till 320 ... and then droped again...and up
> again..
> Do you think I need to enable AWE? If you do , how much memory to give to
> SQL Server?
> I have heard that there is a problem with SP4 and AWE, am I right?
> Thank you.
>
>
>
>|||"Alex" <test@.hotmail.com> wrote in message
news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
> Hello everybody
> We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> application installed on it.
> We also have \3gb parameter in the boot.ini
> SQL Server is using 2,7 GB.
> Application is using three processes , that each one is about 50 MB .
> I took a Page Life expectancy counter which was under 300 for about 10-15
> minuets and after grown up till 320 ... and then droped again...and up
> again..
> Do you think I need to enable AWE? If you do , how much memory to give to
> SQL Server?
> I have heard that there is a problem with SP4 and AWE, am I right?
> Thank you.
I believe you need Enterprise versions of SQL Server and your OS to use AWE.
If you have these I would add /PAE and /3GB to boot.ini, install the SP4
hotfix pertaining to AWE and then enable AWE in SQL Server limiting SQL
Server to 6GB
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
When you restart SQL Server you should see Address Windowing Extensions
Enabled in the log.
You can also use the SQL Performance counter-Total Server Memory to confirm
that SQL is using 6GB.
Read these:
http://support.microsoft.com/kb/274750
http://www.sql-server-performance.com/awe_memory.asp|||Hi,
What is the hotfix after SP4 installed ?
Do I need to apply the hotfix if I am running SP3a on SQL2000 ?
JT
"Terri" wrote:
> "Alex" <test@.hotmail.com> wrote in message
> news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
> > Hello everybody
> > We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
> > application installed on it.
> >
> > We also have \3gb parameter in the boot.ini
> >
> > SQL Server is using 2,7 GB.
> > Application is using three processes , that each one is about 50 MB .
> >
> > I took a Page Life expectancy counter which was under 300 for about 10-15
> > minuets and after grown up till 320 ... and then droped again...and up
> > again..
> >
> > Do you think I need to enable AWE? If you do , how much memory to give to
> > SQL Server?
> > I have heard that there is a problem with SP4 and AWE, am I right?
> >
> > Thank you.
> I believe you need Enterprise versions of SQL Server and your OS to use AWE.
> If you have these I would add /PAE and /3GB to boot.ini, install the SP4
> hotfix pertaining to AWE and then enable AWE in SQL Server limiting SQL
> Server to 6GB
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
>
> When you restart SQL Server you should see Address Windowing Extensions
> Enabled in the log.
>
> You can also use the SQL Performance counter-Total Server Memory to confirm
> that SQL is using 6GB.
>
> Read these:
> http://support.microsoft.com/kb/274750
> http://www.sql-server-performance.com/awe_memory.asp
>
>
>|||The hotfix (build 2040) fixes a bug introduced in SP4 related to AWE on
32-bit host computers. The hotfix is only necessary if you have installed
SP4 and yout host computer has more than 4GB of physicl memory.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Johnny" <Johnny@.discussions.microsoft.com> wrote in message
news:D1A4714E-D506-4D9F-912E-C44AB8595E92@.microsoft.com...
> Hi,
> What is the hotfix after SP4 installed ?
> Do I need to apply the hotfix if I am running SP3a on SQL2000 ?
> JT
> "Terri" wrote:
>> "Alex" <test@.hotmail.com> wrote in message
>> news:uzB1UnkkHHA.1240@.TK2MSFTNGP04.phx.gbl...
>> > Hello everybody
>> > We have windows 2003 server (8GB) with SQL Server 2004(sp4) +one small
>> > application installed on it.
>> >
>> > We also have \3gb parameter in the boot.ini
>> >
>> > SQL Server is using 2,7 GB.
>> > Application is using three processes , that each one is about 50 MB .
>> >
>> > I took a Page Life expectancy counter which was under 300 for about
>> > 10-15
>> > minuets and after grown up till 320 ... and then droped again...and
>> > up
>> > again..
>> >
>> > Do you think I need to enable AWE? If you do , how much memory to give
>> > to
>> > SQL Server?
>> > I have heard that there is a problem with SP4 and AWE, am I right?
>> >
>> > Thank you.
>> I believe you need Enterprise versions of SQL Server and your OS to use
>> AWE.
>> If you have these I would add /PAE and /3GB to boot.ini, install the SP4
>> hotfix pertaining to AWE and then enable AWE in SQL Server limiting SQL
>> Server to 6GB
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>>
>> When you restart SQL Server you should see Address Windowing Extensions
>> Enabled in the log.
>>
>> You can also use the SQL Performance counter-Total Server Memory to
>> confirm
>> that SQL is using 6GB.
>>
>> Read these:
>> http://support.microsoft.com/kb/274750
>> http://www.sql-server-performance.com/awe_memory.asp
>>
>>

Avoiding transaction logging - at least reducing it

I have an application that uses work tables within an SP to generate a recordset output to the caller as the result of a search operation. The SP does not update the main database, and the results, although sizeable, are not saved.

Clearly I do not need to log the updates to the work tables. They start out empty and the contents could be discarded.

Does SQL Server recognise this and not log the updates to work tables? Is there any way I can indicate that a table is not significant and need not be logged at all?

Would it help to put the work tables in a separate database?

Maybe it would not make much difference anyway?

Thanks in advance for any helpFortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !|||Originally posted by fadace
Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !

Huh? Well I'm glad you didn't become very specific about what you're talking about.

Veritant:

You say Search to return result set, then you say update to work table

SELECTS are not logged

Creating a result set on the fly, like

SELECT * INTO #temp FROM...is not logged

TRUNCATE TABLE or DROP TABLE is not logged..

If you have some examples of what you're doing and need help, post it and we'll look at it...

Avoiding time-outs

The C++ application calls the database to look up property data. One
troublesome query is a function that returns a table, finding data which
is assembled from four or five tables through a view that has a join,
and then updating the resulting @.table from some other tables. There
are several queries inside the function, which are selected according
to which parameters are supplied (house #, street, zip, or perhaps parcel
number, or house #, street, town, city,...etc.). If a lot of parameters
are provided, and the property is not in the database, then several queries
may be attempted -- it keeps going until it runs out of queries or finds
something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
some failure cases, depending on the distribution of data. (~100 mil
properties in the DB) Some queires operate on the assumption the input data
is slightly faulty, and take relatively a long time, e.g., if WHERE
ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
all this is going on the application may decide the DB is never going to
return, and time out; it also seems more likely to throw an exception the
longer it has to wait. Is there a way to cause the DB function to fail if
it takes more than a certain amount of time? I could also recast it as
a procedure, and check the time consumed after every query, and abandon
the search if a certain amount of time has elapsed.

Thanks in advance,
Jim Geissmanjim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

You don't give any information about your version of MSSQL, and the
client library you're using, but you may be able to set a suitable
timeout period on the client side. Alternatively, look at the "query
governor cost limit Option" in Books Online - this terminates queries
that run for more than a given number of seconds.

Simon|||jim_geissman@.countrywide.com (Jim Geissman) wrote in message news:<b84bf9dc.0403031505.2838a043@.posting.google.com>...
> The C++ application calls the database to look up property data. One
> troublesome query is a function that returns a table, finding data which
> is assembled from four or five tables through a view that has a join,
> and then updating the resulting @.table from some other tables. There
> are several queries inside the function, which are selected according
> to which parameters are supplied (house #, street, zip, or perhaps parcel
> number, or house #, street, town, city,...etc.). If a lot of parameters
> are provided, and the property is not in the database, then several queries
> may be attempted -- it keeps going until it runs out of queries or finds
> something. Usually it takes ~1-2 sec for a hit, but maybe a minute in
> some failure cases, depending on the distribution of data. (~100 mil
> properties in the DB) Some queires operate on the assumption the input data
> is slightly faulty, and take relatively a long time, e.g., if WHERE
> ZIP=@.Zip fails, we try WHERE ZIP LIKE substring(@.Zip,1,3)+'%'. While
> all this is going on the application may decide the DB is never going to
> return, and time out; it also seems more likely to throw an exception the
> longer it has to wait. Is there a way to cause the DB function to fail if
> it takes more than a certain amount of time? I could also recast it as
> a procedure, and check the time consumed after every query, and abandon
> the search if a certain amount of time has elapsed.
> Thanks in advance,
> Jim Geissman

See "remote query timeout Option" in the help text. However, relying
on this may cause inconsistent bahaviour.

This design pattern can also lead to heavy load on your database.

As a suggestion, have two separate sets of queries, one that assumes
good data (should be much quicker which you want to use most times?)
and one that may have incorrect data (will be slower, but not used
very often). In you screen have a checkbox to indicate what search
option to use. Alternatively perform better validation on the data
before submitting the form.|||That sounds interesting. I'll look into it.

> You don't give any information about your version of MSSQL, and the
> client library you're using, but you may be able to set a suitable
> timeout period on the client side. Alternatively, look at the "query
> governor cost limit Option" in Books Online - this terminates queries
> that run for more than a given number of seconds.
> Simon

Avoiding deadlock

I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.

So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.

Instead I'm now getting deadlock errors.

If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?

Thanks.

Nate Hekman

You can change your code to the following:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)

WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT

The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.

Another approach is to do the following:

BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0

BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

COMMIT

|||

This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)

BEGIN TRANSACTION

IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...

Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.

--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs

create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)

then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.

|||

I didn't think that:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)

Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.

(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)

Thanks!

|||You are right. This should be XLOCK instead.|||

Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.

Thanks again.

Nate

|||

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

|||

This problem's interesting and I'm sure lots of people have encountered before.

There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?

Question 2>
How does it solve the deadlock problem?

|||

Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.

Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.

Avoiding deadlock

I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.

So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.

Instead I'm now getting deadlock errors.

If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?

Thanks.

Nate Hekman

You can change your code to the following:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)

WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT

The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.

Another approach is to do the following:

BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0

BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

COMMIT

|||

This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)

BEGIN TRANSACTION

IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...

Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.

--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs

create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)

then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.

|||

I didn't think that:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)

Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.

(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)

Thanks!

|||You are right. This should be XLOCK instead.|||

Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.

Thanks again.

Nate

|||

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

|||

This problem's interesting and I'm sure lots of people have encountered before.

There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?

Question 2>
How does it solve the deadlock problem?

|||

Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.

Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.

Avoiding ASPNETDB SQL database?

Hello.

I have been developing a small site that has two backend SQL Server databases. One for my application data and one for the ASPNETDB database that is created by the ASP .NET Configuration utility.

Is it possible to configure the ASP .NET Configuration tool to use my custom database instead of creating a second database called ASPNETDB?

Thanks in advance.

Kev

You first step would be to create the required ASP.NET required tables and objects. You would need to run the aspnet_regsql.exe tool located in the C:\<WindowsDir>\Framework\<frameworkversion> folder.

After that you need to change the connectionstrings section of you Web.Config.

|||

Thanks for the swift response jguadagno - much appreciated.

I ran the aspnet_regsql.exe utility and updated the connection string in the web.config file and that works perfectly.

Thanks again.

Avoid windows login prompt while accessing report server.

Hi,

We are using Microsoft Reporting Service 2005 to develop reports and we are accessing these reports through a J2EE application.
The front end is implemented using Tapestry and we using JBoss as our applicaiton server.
We are using Shared Data Sources for the reports and we set its data source type to SQL Server Analysis Services.
In the credential tab, by default "Use Windows authentication" is selected. All other options are disabled.

When I access my reportserver through my web application, I am always prompted for a windows login and password.
How can I avoid being shown the windows login prompt, since our web application will be used by several users and we do not want the users to type in a username/password everytime they want to access our reports.

Please suggest me solution for this scenario.

Thanks in advance!

Hi,

i think the core problem is, that you use windows integrated authentication. In this case the current login user on the client is used to authenticat the access to the database, if this user dont have access rights to the database, you get the login prompt.

Perhapse it is possible for you to set fixed credentials in the shared datasource?

Tibor Csizmadia

|||

How do I give the users access rights to the database? and there may be so many users invoking the reports through our web application.

As I mentioned in my earlier post when I create a new shared data source in my report project, in the Credentials tab "Use Windows Authentication (Integrated Security)" is selected by default. All other options are disabled so I cannot set any other credentials.

Why is it that I can select credential only as "Windows Authentication" is it because the data source type is selected as SQL Server Analysis Services?

Also one thing I forgot to mention is that we are using SQL Server 2005 Standard Edition

|||

Hi,

if you want to use the integrated security, i would create a security-group the the users who have access to the database. Then asign in the SQL-Server-Management Studio this security group to the database (Security/Logins, Add new User, choose the security-group from the AD, assign the Database to access throu this group).

I doent know why you can only use the windows Authentication for "Microsoft SQL-Server Analyse Services" in VS.NET, but if you define the share datasource in the reporting-managment website (http://localhost/Reports) you can set outher authentications...Perhaps MS have for this an explanation.

I hope this help you.

Tibor Csizmadia

Auto-updating Display

Hi,

I would like to create an application that displays the status of my machines almost real-time (Run, Down, or idle etc). And I'm wondering what is the common method to accomplish this? Do you tell the application to keep going out and getting the status of the machines at a specific interval? or Do you have a way for the application to just 'listen' if a status update is available for the specific machines? If so, how? I only have a 'Read' access to the database. Also, other applications update the database (and I don't have access to those applications.) My application will be in VB.NET. Thanks for any leads.Checking if a machine is up or down or it's status is not related to your database in any way.

Most of the people I know that do this utilize ADSI or WMI to accomplish the desired effect with timers to continue to call upon the servers as necessary.

auto-shrink on a write-once read-many daily recreated db?

Hi,
I have an application that feeds a mssql2000sp3 server
from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
and then re-populates (using bulk inserts) a 3 GB db (with just one
main table occupying 98% of it).
Then, during the days that db is accessed at 99%
only with a lot of read request (in most cases for this kind of
usage I use mysql, but this time I were forced to use
mssql server). For many reasons I cannot have the db dropped
and re-created from scratch. My problem is
that, in just 3 days, the transaction log wastes more than 6 GB
(reaching the disk space limit, but "effectively using" just 50
MB of it). I read in many posts that setting the auto-shrink flag
may reduce performance and this'll be an issue for me.
Does auto-shrink option imply very bad
performance even for such a "quite read-only" db?
Or may I set the auto-shrink flag for this db (and maybe
changing autoshrink frequency to something like 4 hours - I don't
know if it's possible and how to do it)?
In case I can change the db usage and prevent ANY db writing during
the working hours, may I expect better performance enabling the
read-only flag?
Thanks in advance for your help!
bye,
PiErre
This behavior of kill and fill is common in reporting or data warehouse
databases. Check your recovery mode on the database. Change it from full
to either simple or bulk logged. Full records every change in the
transaction log and while it can be recovered to the last transaction log
backup it takes a toll on the transaction log. If that doesn't save you
enough space, run a SQL script once per day after the load that truncates
the transaction log and shrinks the file.
<siggy2@.supereva.it> wrote in message
news:1130237889.329371.259120@.f14g2000cwb.googlegr oups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>
|||Yes, autoshrink is bad for that scenario. You don't know when the shrink will kick in, and it might
do some shuffling of data even if it is futile. In short, no control.
1. Run in simple recovery, and make sure that your imports are minimally logged. Drop the indexes,
add data, add the indexes.
2. If you *really* need to shrink after this, do it after the import (in between it is no meaning as
you don't modify data). Use DBCC SHRINKFILE and target only the log file.
Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<siggy2@.supereva.it> wrote in message news:1130237889.329371.259120@.f14g2000cwb.googlegr oups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>
|||First of all: thanks to all for the quick and helpful answers
Tibor Karaszi ha scritto:

> Yes, autoshrink is bad for that scenario. You don't know when
> the shrink will kick in, and it might
> do some shuffling of data even if it is futile.
> In short, no control.
ok - good to know it!

> 1. Run in simple recovery, and make sure that your
> imports are minimally logged. Drop the indexes,
> add data, add the indexes.
forget to mention I already did it that way (gh!)

> 2. If you *really* need to shrink after this, do it after
> the import (in between it is no meaning as
> you don't modify data). Use DBCC SHRINKFILE and target
> only the log file.
ok - I'm testing this solution...

> Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
already read it... but thank you anyway...
bye,
PiErre

auto-shrink on a write-once read-many daily recreated db?

Hi,
I have an application that feeds a mssql2000sp3 server
from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
and then re-populates (using bulk inserts) a 3 GB db (with just one
main table occupying 98% of it).
Then, during the days that db is accessed at 99%
only with a lot of read request (in most cases for this kind of
usage I use mysql, but this time I were forced to use
mssql server). For many reasons I cannot have the db dropped
and re-created from scratch. My problem is
that, in just 3 days, the transaction log wastes more than 6 GB
(reaching the disk space limit, but "effectively using" just 50
MB of it). I read in many posts that setting the auto-shrink flag
may reduce performance and this'll be an issue for me.
Does auto-shrink option imply very bad
performance even for such a "quite read-only" db?
Or may I set the auto-shrink flag for this db (and maybe
changing autoshrink frequency to something like 4 hours - I don't
know if it's possible and how to do it)?
In case I can change the db usage and prevent ANY db writing during
the working hours, may I expect better performance enabling the
read-only flag?
Thanks in advance for your help!
bye,
PiErreThis behavior of kill and fill is common in reporting or data warehouse
databases. Check your recovery mode on the database. Change it from full
to either simple or bulk logged. Full records every change in the
transaction log and while it can be recovered to the last transaction log
backup it takes a toll on the transaction log. If that doesn't save you
enough space, run a SQL script once per day after the load that truncates
the transaction log and shrinks the file.
<siggy2@.supereva.it> wrote in message
news:1130237889.329371.259120@.f14g2000cwb.googlegroups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>|||Yes, autoshrink is bad for that scenario. You don't know when the shrink will kick in, and it might
do some shuffling of data even if it is futile. In short, no control.
1. Run in simple recovery, and make sure that your imports are minimally logged. Drop the indexes,
add data, add the indexes.
2. If you *really* need to shrink after this, do it after the import (in between it is no meaning as
you don't modify data). Use DBCC SHRINKFILE and target only the log file.
Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<siggy2@.supereva.it> wrote in message news:1130237889.329371.259120@.f14g2000cwb.googlegroups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>|||First of all: thanks to all for the quick and helpful answers
Tibor Karaszi ha scritto:
> Yes, autoshrink is bad for that scenario. You don't know when
> the shrink will kick in, and it might
> do some shuffling of data even if it is futile.
> In short, no control.
ok - good to know it!
> 1. Run in simple recovery, and make sure that your
> imports are minimally logged. Drop the indexes,
> add data, add the indexes.
forget to mention I already did it that way (gh!)
> 2. If you *really* need to shrink after this, do it after
> the import (in between it is no meaning as
> you don't modify data). Use DBCC SHRINKFILE and target
> only the log file.
ok - I'm testing this solution...
> Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
already read it... but thank you anyway...
bye,
PiErre

auto-shrink on a write-once read-many daily recreated db?

Hi,
I have an application that feeds a mssql2000sp3 server
from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
and then re-populates (using bulk inserts) a 3 GB db (with just one
main table occupying 98% of it).
Then, during the days that db is accessed at 99%
only with a lot of read request (in most cases for this kind of
usage I use mysql, but this time I were forced to use
mssql server). For many reasons I cannot have the db dropped
and re-created from scratch. My problem is
that, in just 3 days, the transaction log wastes more than 6 GB
(reaching the disk space limit, but "effectively using" just 50
MB of it). I read in many posts that setting the auto-shrink flag
may reduce performance and this'll be an issue for me.
Does auto-shrink option imply very bad
performance even for such a "quite read-only" db?
Or may I set the auto-shrink flag for this db (and maybe
changing autoshrink frequency to something like 4 hours - I don't
know if it's possible and how to do it)?
In case I can change the db usage and prevent ANY db writing during
the working hours, may I expect better performance enabling the
read-only flag?
Thanks in advance for your help!
bye,
PiErreThis behavior of kill and fill is common in reporting or data warehouse
databases. Check your recovery mode on the database. Change it from full
to either simple or bulk logged. Full records every change in the
transaction log and while it can be recovered to the last transaction log
backup it takes a toll on the transaction log. If that doesn't save you
enough space, run a SQL script once per day after the load that truncates
the transaction log and shrinks the file.
<siggy2@.supereva.it> wrote in message
news:1130237889.329371.259120@.f14g2000cwb.googlegroups.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>|||Yes, autoshrink is bad for that scenario. You don't know when the shrink wil
l kick in, and it might
do some shuffling of data even if it is futile. In short, no control.
1. Run in simple recovery, and make sure that your imports are minimally log
ged. Drop the indexes,
add data, add the indexes.
2. If you *really* need to shrink after this, do it after the import (in bet
ween it is no meaning as
you don't modify data). Use DBCC SHRINKFILE and target only the log file.
Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<siggy2@.supereva.it> wrote in message news:1130237889.329371.259120@.f14g2000cwb.googlegroups
.com...
> Hi,
> I have an application that feeds a mssql2000sp3 server
> from 1 a.m. to 4 a.m. from monday to friday. It totally emptyies
> and then re-populates (using bulk inserts) a 3 GB db (with just one
> main table occupying 98% of it).
> Then, during the days that db is accessed at 99%
> only with a lot of read request (in most cases for this kind of
> usage I use mysql, but this time I were forced to use
> mssql server). For many reasons I cannot have the db dropped
> and re-created from scratch. My problem is
> that, in just 3 days, the transaction log wastes more than 6 GB
> (reaching the disk space limit, but "effectively using" just 50
> MB of it). I read in many posts that setting the auto-shrink flag
> may reduce performance and this'll be an issue for me.
> Does auto-shrink option imply very bad
> performance even for such a "quite read-only" db?
> Or may I set the auto-shrink flag for this db (and maybe
> changing autoshrink frequency to something like 4 hours - I don't
> know if it's possible and how to do it)?
> In case I can change the db usage and prevent ANY db writing during
> the working hours, may I expect better performance enabling the
> read-only flag?
> Thanks in advance for your help!
> bye,
> PiErre
>|||First of all: thanks to all for the quick and helpful answers
Tibor Karaszi ha scritto:

> Yes, autoshrink is bad for that scenario. You don't know when
> the shrink will kick in, and it might
> do some shuffling of data even if it is futile.
> In short, no control.
ok - good to know it!

> 1. Run in simple recovery, and make sure that your
> imports are minimally logged. Drop the indexes,
> add data, add the indexes.
forget to mention I already did it that way (gh!)

> 2. If you *really* need to shrink after this, do it after
> the import (in between it is no meaning as
> you don't modify data). Use DBCC SHRINKFILE and target
> only the log file.
ok - I'm testing this solution...
[vbcol=seagreen]
> Additional info here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp[/vbcol
]
already read it... but thank you anyway...
bye,
PiErre

Auto-Refresh for SQL Grids displaying SQL Server Data

Functionallity of the Application--

"We have a large legacy application using SQL Server 2000. We're gradually adding .NET WinForms controls to it, using the IdeaBlade DevForce package as the data access layer, and primarily Infragistics Grids and other controls for display. We're using Visual Studio 2005, and plan to migrate to SQL Server 2005 pending validation, but I need a solution that works for 2000."

Problem description--

"I would like a mechanism to allow grid displays to automatically update when any change is made to one of the tables that contributes data to it. I'd like the mechanism to be event driven using some kind of publish/subscribe model. "

Challenges-

"Instead of using notification services ,any triggers or extended stored procedures if suffice this issues ,help me out in this regard."

Of course, Query Notifications in SS2005 will be of great interest to you. But that doesn't help in the meantime.

ASP.NET 2.0 has some cache notifications for SQL Server 2000 that you may want to check out as being something you can tweak to fit your needs. Here are a couple of articles that may help.

http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc

http://www.15seconds.com/issue/040518.htm

You can use SSNS for this, but it'll require that you develop a custom delivery protocol to notify the client app.

HTH...

Joe

Auto-Refresh for SQL Grids displaying SQL Server Data

Functionallity of the Application--

"We have a large legacy application using SQL Server 2000. We're gradually adding .NET WinForms controls to it, using the IdeaBlade DevForce package as the data access layer, and primarily Infragistics Grids and other controls for display. We're using Visual Studio 2005, and plan to migrate to SQL Server 2005 pending validation, but I need a solution that works for 2000."

Problem description--

"I would like a mechanism to allow grid displays to automatically update when any change is made to one of the tables that contributes data to it. I'd like the mechanism to be event driven using some kind of publish/subscribe model. "

Challenges-

"Instead of using notification services ,any triggers or extended stored procedures if suffice this issues ,help me out in this regard."

Of course, Query Notifications in SS2005 will be of great interest to you. But that doesn't help in the meantime.

ASP.NET 2.0 has some cache notifications for SQL Server 2000 that you may want to check out as being something you can tweak to fit your needs. Here are a couple of articles that may help.

http://www.c-sharpcorner.com/UploadFile/mosessaur/sqlcachedependency01292006135138PM/sqlcachedependency.aspx?ArticleID=3caa7d32-dce0-44dc-8769-77f8448e76bc

http://www.15seconds.com/issue/040518.htm

You can use SSNS for this, but it'll require that you develop a custom delivery protocol to notify the client app.

HTH...

Joe

auto-number/Identity column

I am migrating a web application I wrote from ASP to ASP.Net, and from
Access to MS SQL server.

In the Access version, I did not use the auto number for creating
invoices and other documents, because I heard somewhere (perhaps
incorrectly) that if the db was ever compacted or otherwise changed,
it could change the values of the auto-numbers. Not a good thing.
So I wrote a routine that, just before creating a new record, would
look for the highest value in the table and create the new record with
the next number.

So my question is, am I safe in assuming that in MS SQL that I can set
a starting number for the next, let's say, invoice and that new
numbers will be issued in sequence, and that these numbers will never
change? What happens if an invoice is deleted? is the number gone
forever? Just wondering how others deal with these issues...thanks.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."Larry Rekow (larry@.netgeexdotcom) writes:
> So my question is, am I safe in assuming that in MS SQL that I can set
> a starting number for the next, let's say, invoice and that new
> numbers will be issued in sequence, and that these numbers will never
> change? What happens if an invoice is deleted? is the number gone
> forever? Just wondering how others deal with these issues...thanks.

If you need sequential numbers, and cannot accept gaps, you should not
use the IDENITY property. If you attempt to insert a row, and the
insert fails, that consumes a number. The whole point is that the number
is not transactional, so that it scales better. If you need a contiguous
series of numbers, roll your own.

What does not happen is that once the number has been given to a row,
the number will not change at whim.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 31 Aug 2004 21:06:39 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Larry Rekow (larry@.netgeexdotcom) writes:
>> So my question is, am I safe in assuming that in MS SQL that I can set
>> a starting number for the next, let's say, invoice and that new
>> numbers will be issued in sequence, and that these numbers will never
>> change? What happens if an invoice is deleted? is the number gone
>> forever? Just wondering how others deal with these issues...thanks.
>If you need sequential numbers, and cannot accept gaps, you should not
>use the IDENITY property. If you attempt to insert a row, and the
>insert fails, that consumes a number. The whole point is that the number
>is not transactional, so that it scales better. If you need a contiguous
>series of numbers, roll your own.
>What does not happen is that once the number has been given to a row,
>the number will not change at whim.
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
thanks; glad I asked.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."

AutoNumber in SQL 2005

Hello friends,

I am using Sql Server 2005 in my web application. In one scenario I had made one field of a table as Autonumber by keeping its datatype as int and then setting its Identity flag to true. Now is there any inbuild function that can give us the next number tat will be generated if a new record will be inserted. This is needed in the case if last 1 or 2 records are deleted for e.g.

last record has value 7 and if the record is deleted still for the new record the number generated will be 8 i.e. after 6 there is 8. So by any means can we get the next number, or can we keep that autonumber to move serially even in case some record is deleted it should go for the max + 1 number.

Please let me know.

Thanks & Regards
Girish Nehte

sql server doesn't guarantee sequential order numbers, though there are some tricks to force it (reseeding the table via DBCC checkident). I'd recommend that you not rely on a function that assumes the max+1, but instead use scope_identity to return the actual value -http://www.sqlteam.com/article/understanding-identity-columns andhttp://www.sqlteam.com/article/creating-a-sequential-record-number-field are worth reading to detail you some options.

|||

Why should you care what the next number will be? If this is important then there is something conceptually wrong with your design. Identity columns are inherently dataless, they have no intrinsic meaning. What are you trying to accomplish?

|||

Hi dbland,

Actually I have to save two records in two different tables and after one records save I have to take fetch the identity of that record and set that identity aas the fireign key for the second records but as both the records we are entering are in the same transaction i.e. after both the queries executed we commit the transaction due to which before commiting we are unable to get the identity key of first record which we will be needing in entering the second record.

I dont know whether I was able to make clear my requirements or not but if you please got it let me know any better way to accomplish it.

Thanks & Regards
Girish Nehte

|||

i think if you are still going to implement this then use max in the query to retrive the highest figure in the column.like select max(id) as maximum from ur table.

|||

Thanks, Girish, for that clarification. What you want to do is very common and easily accomplished using the SCOPE_IDENTITY() function (seehttp://msdn2.microsoft.com/en-us/library/ms190315(SQL.90).aspx). Here's an example

declare @.LastId int
begin tran
insert ... into table1
select @.LastId = SCOPE_IDENTITY()
insert ... @.LastId,... into table2 (...FK_field,...)
commit tran

In my example, I save SCOPE_IDENTITY() to a variable and use the variable, this is not necessary, I just consider it good programming practice to always save @.@. fields and the like into temp variables so they don't get overwritten.

If you rollback the identity counters will still have been incremented, but it doesn't matter.

BTW, note in the MSDN article how SCOPE_IDENTITY() differs from @.@.identity