Autonumber field: box

Showing posts with label box. Show all posts

Tuesday, March 27, 2012

Backing Up SQL DBs With Retrospecct

I have SBS2003 and am using Dantz Retrospect to back up the SQL databases to
a NAS box outside of our building. I have never been sucessful doing this
because Retrospect throws an error saying that it cant read the database due
to "an unknown Windows error". I have tried talking to the folks at Dantz
and they said that they have done all they can and that I should call the SQL
tech support.
Any ideas? TIA
Scott
I don't know what Retrospect is but it is most likely trying to just backup
the sql files directly. That is never a good way to back up a database.
You need to either use the native SQL Server BACKUP command to dump the
backup file to disk (or tape if directly attached) and then you can back
that file up with your backup software. There are some 3rd party backup
tools that have a Plug-In for Sql Server that does the equivalent of a
native backup but I doubt that Retrospect has that.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>I have SBS2003 and am using Dantz Retrospect to back up the SQL databases
>to
> a NAS box outside of our building. I have never been sucessful doing this
> because Retrospect throws an error saying that it cant read the database
> due
> to "an unknown Windows error". I have tried talking to the folks at Dantz
> and they said that they have done all they can and that I should call the
> SQL
> tech support.
> Any ideas? TIA
> Scott
|||Retrospect does (or is supposed to do) the same thing as the Backup Database
option in EM, however, it doesnt work and I have to manually do the backup
every day.
"Andrew J. Kelly" wrote:

> I don't know what Retrospect is but it is most likely trying to just backup
> the sql files directly. That is never a good way to back up a database.
> You need to either use the native SQL Server BACKUP command to dump the
> backup file to disk (or tape if directly attached) and then you can back
> that file up with your backup software. There are some 3rd party backup
> tools that have a Plug-In for Sql Server that does the equivalent of a
> native backup but I doubt that Retrospect has that.
> --
> Andrew J. Kelly SQL MVP
>
> "scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
> news:5D4CDEFB-3390-4AC4-B554-BB3427CE3A21@.microsoft.com...
>
>
|||Well it's hard to say what this is doing. If you can do a native backup
then it must be their code. It's pretty lame that they can't help you.
Andrew J. Kelly SQL MVP
"scottdog129" <scottdog129@.discussions.microsoft.com> wrote in message
news:0A5D2BBA-12DD-4819-B4E2-6737CC59C5F7@.microsoft.com...[vbcol=seagreen]
> Retrospect does (or is supposed to do) the same thing as the Backup
> Database
> option in EM, however, it doesnt work and I have to manually do the
> backup
> every day.
> "Andrew J. Kelly" wrote:
sql

Thursday, March 22, 2012

Backing up and restoring 50 databases to a new server

I would like to replace my SQL 2000 server with a faster box. I currently
use a combination of full and transactional backups to back up the server
every day. I know that I could build a new box and restore each database
from backup file one at a time but was wondering if there was a quicker way
to store all the databases
Hi,
Look in to the below web sites for various strategies to move all the
databases to new server.
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Thanks
Hari
MCDBA
"Richard Zellmer" <tdmailbox@.yahoo.com> wrote in message
news:#$TGJe7IEHA.1412@.TK2MSFTNGP12.phx.gbl...
> I would like to replace my SQL 2000 server with a faster box. I currently
> use a combination of full and transactional backups to back up the server
> every day. I know that I could build a new box and restore each database
> from backup file one at a time but was wondering if there was a quicker
way
> to store all the databases
>
>
|||If you have installed SQL on the new server with the same paths... I have stopped the SQL services on both servers and copied all DB's including system to the new server. I have done this on many occasions. I always double check @.@.servername on the ne
w server when I am done.
On the new server just rename the SQL Directories before you copy the old ones over. This will give you something to fall back on just in case of failure. Copy all the mdf's and ldf's to the same location on the new box.
Jeff
MCDBA, MCSE+I

Backing up and restoring 50 databases to a new server

I would like to replace my SQL 2000 server with a faster box. I currently
use a combination of full and transactional backups to back up the server
every day. I know that I could build a new box and restore each database
from backup file one at a time but was wondering if there was a quicker way
to store all the databasesHi,
Look in to the below web sites for various strategies to move all the
databases to new server.
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Thanks
Hari
MCDBA
"Richard Zellmer" <tdmailbox@.yahoo.com> wrote in message
news:#$TGJe7IEHA.1412@.TK2MSFTNGP12.phx.gbl...
> I would like to replace my SQL 2000 server with a faster box. I currently
> use a combination of full and transactional backups to back up the server
> every day. I know that I could build a new box and restore each database
> from backup file one at a time but was wondering if there was a quicker
way
> to store all the databases
>
>|||If you have installed SQL on the new server with the same paths... I have
stopped the SQL services on both servers and copied all DB's including syste
m to the new server. I have done this on many occasions. I always double c
heck @.@.servername on the ne
w server when I am done.
On the new server just rename the SQL Directories before you copy the old on
es over. This will give you something to fall back on just in case of failu
re. Copy all the mdf's and ldf's to the same location on the new box.
Jeff
MCDBA, MCSE+I

Sunday, March 11, 2012

AWE SQL Performance Problem

We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB R
AM. The setting seems to have made the difference in RAM usage, but we are e
xperiencing extremely high CPU utilization and overall the system does not s
eem to be any faster. If an
ything, it seems to have slowed down some. There are a lot of variables, but
I was wondering of anyone has had adverse effects with the AWE setting.From BOL.
<<
To enable AWE, set awe enabled to 1. SQL Server will reserve almost all
available memory, leaving 128 megabytes (MB) or less, unless a value has
been specified for max server memory.
What is your max memory setting? Leaving 128M for the OS probably isn't a
good idea.
BTW... Profiler may be helpful in understanding the CPU usage.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Phil Appel" <phil@.chileinc.com> wrote in message
news:774F1405-0770-4A4F-B551-9CC330AE9CE4@.microsoft.com...
> We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB
RAM. The setting seems to have made the difference in RAM usage, but we are
experiencing extremely high CPU utilization and overall the system does not
seem to be any faster. If anything, it seems to have slowed down some. There
are a lot of variables, but I was wondering of anyone has had adverse
effects with the AWE setting.|||We had some CPU spin and server instability issues that were instantaneously
and permenantly solved by turning off the /3GB switch and reducing max
server mem to 1.5GB less than physical. PSS said there was potential kernel
memory starvation with /3GB and max server memory set to near physical. Not
sure if this applies to your scenario.
"Phil Appel" <phil@.chileinc.com> wrote in message
news:774F1405-0770-4A4F-B551-9CC330AE9CE4@.microsoft.com...
> We recently enabled AWE on a SQL Server cluster. Each box has 8CPU and 8GB
RAM. The setting seems to have made the difference in RAM usage, but we are
experiencing extremely high CPU utilization and overall the system does not
seem to be any faster. If anything, it seems to have slowed down some. There
are a lot of variables, but I was wondering of anyone has had adverse
effects with the AWE setting.|||Hi Phil,
I am viewing your post and since we have not heard from you for some time
and I wonder how things go on your side. To disable the AWE to check if the
performance issue is related to the AWE is the method to narrow down
performance problems to AWE. Also, you could refer to the link of the
followings for reference:
How To Gather Information for Effective Troubleshooting of Performance
Issues
http://support.microsoft.com/defaul...microsoft.com:
80/support/kb/articles/Q175/6/58.ASP&NoWebContent=1
http://support.microsoft.com/defaul...kb;en-us;298475
Also, if the case is critical and it should be troubleshot by some advanced
tools which are not supported in the newsgroup. Therefore, we probably will
not be able to resolve the issue through the newsgroups. I'd recommend
opening a Support incident with Microsoft Support Services, so that a
dedicated Support Professional can assist with your advisory case. Please
be advised that contacting phone support will be a charged call.
To obtain the phone numbers for specific technology request please take a
look at the web site listed below.
http://support.microsoft.com/defaul...US;PHONENUMBERS
If you are outside the US please see http://support.microsoft.com for
regional support phone numbers.
Thanks.
Hope this helps.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Thursday, March 8, 2012

awe question

We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory bu
t
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS a
nd
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can n
ot
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB
in the machine.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.
gbl...
> Andrew J. Kelly wrote:
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.

awe question

We are going to turn on AWE on our production environment currently
its 4 processor box with 12GB of RAM and windows advanced server 2000
loaded on it. Now my question is do i need to use /PAE and /3GB
switches in our boot.ini in order to enable 6gigs to our sql server
or /PAE will okay?kulkarni.ninad@.gmail.com wrote:
> We are going to turn on AWE on our production environment currently
> its 4 processor box with 12GB of RAM and windows advanced server 2000
> loaded on it. Now my question is do i need to use /PAE and /3GB
> switches in our boot.ini in order to enable 6gigs to our sql server
> or /PAE will okay?
>
With 6Gb of RAM, I would use the /3GB switch as well. That would
effectively allow SQL to use as much as 5GB. Without the /3Gb switch
you are pretty well limited to 4Gb.|||That is not really true. /3GB allows SQL Server (or any properly enabled
application) to use up to 3GB of the 4GB of directly addressable memory but
limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
the App to see memory above 4GB and is totally independent of the /3GB
switch. The decision to use /3GB or not is dependent on how much of that
directly addressable memory you need to use. The only part of SQL Server
that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
cache. I would question why he wants to use only 6GB when he has 12
available. I suspect he may be in a cluster and if that is true and it is
Active-Active then 6GB may be too much since AWE memory is fixed and can not
be dynamically adjusted.
--
Andrew J. Kelly SQL MVP
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message
news:uEmOq6LbHHA.5044@.TK2MSFTNGP05.phx.gbl...
> kulkarni.ninad@.gmail.com wrote:
>> We are going to turn on AWE on our production environment currently
>> its 4 processor box with 12GB of RAM and windows advanced server 2000
>> loaded on it. Now my question is do i need to use /PAE and /3GB
>> switches in our boot.ini in order to enable 6gigs to our sql server
>> or /PAE will okay?
> With 6Gb of RAM, I would use the /3GB switch as well. That would
> effectively allow SQL to use as much as 5GB. Without the /3Gb switch you
> are pretty well limited to 4Gb.|||Andrew J. Kelly wrote:
> That is not really true. /3GB allows SQL Server (or any properly enabled
> application) to use up to 3GB of the 4GB of directly addressable memory but
> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
> the App to see memory above 4GB and is totally independent of the /3GB
> switch. The decision to use /3GB or not is dependent on how much of that
> directly addressable memory you need to use. The only part of SQL Server
> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
> cache. I would question why he wants to use only 6GB when he has 12
> available. I suspect he may be in a cluster and if that is true and it is
> Active-Active then 6GB may be too much since AWE memory is fixed and can not
> be dynamically adjusted.
>
My thinking was that there was only 6Gb and if you didn't use the /3Gb
switch, then the OS would effectively take the full 2Gb available to it,
leaving 4Gb for applications such as SQL. While using the /3Gb switch
would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
However, now I see that there is actually 12Gb...
With 12Gb you don't need the /3Gb switch in order to allow SQL to see
6Gb (actually it could use 10Gb), however, I have to wonder why you want
to limit SQL to 6Gb when you have 12?
I think my recommendation still stands though, because if I recall, it's
not until you have over 16Gb of RAM that the OS has to use that extra Gb
of space to maintain the Address Extensions.
So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
10Gb without it. Although I'd keep a close eye on things to make sure
that the OS is not having to do a bunch of swapping. In certain
circumstances the OS might be able to make better use of that Gb than SQL.|||> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
I believe that Unisys has recommendations to not use /3GB if you have > 12GB in the machine.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Don Peterson" <sgtp.usmc@.gmail.com> wrote in message news:uhqohQYbHHA.588@.TK2MSFTNGP06.phx.gbl...
> Andrew J. Kelly wrote:
>> That is not really true. /3GB allows SQL Server (or any properly enabled
>> application) to use up to 3GB of the 4GB of directly addressable memory but
>> limits the OS to 1GB of that same space. AWE and PAE are to allow the OS and
>> the App to see memory above 4GB and is totally independent of the /3GB
>> switch. The decision to use /3GB or not is dependent on how much of that
>> directly addressable memory you need to use. The only part of SQL Server
>> that can use memory above 2GB (or 3GB with the /3GB) is the data buffer
>> cache. I would question why he wants to use only 6GB when he has 12
>> available. I suspect he may be in a cluster and if that is true and it is
>> Active-Active then 6GB may be too much since AWE memory is fixed and can not
>> be dynamically adjusted.
> My thinking was that there was only 6Gb and if you didn't use the /3Gb
> switch, then the OS would effectively take the full 2Gb available to it,
> leaving 4Gb for applications such as SQL. While using the /3Gb switch
> would limit the OS to 1Gb, thereby allowing SQL to use a full 5Gb.
> However, now I see that there is actually 12Gb...
> With 12Gb you don't need the /3Gb switch in order to allow SQL to see
> 6Gb (actually it could use 10Gb), however, I have to wonder why you want
> to limit SQL to 6Gb when you have 12?
> I think my recommendation still stands though, because if I recall, it's
> not until you have over 16Gb of RAM that the OS has to use that extra Gb
> of space to maintain the Address Extensions.
> So, using the /3Gb switch would allow SQL to use up to 11Gb instead of
> 10Gb without it. Although I'd keep a close eye on things to make sure
> that the OS is not having to do a bunch of swapping. In certain
> circumstances the OS might be able to make better use of that Gb than SQL.

AWE on 4GB multi-instance box?

Hi
We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
We are using an Active / Passive configuration which has 4 instances of SQL
Enterprise, and 1 Instance of IIS.
Both boxes are identical, and clustering works just fine.
However, in order to use the 4Gb in each box, should we enable the /3Gb
switch in Boot.Ini?
How then do we configure each instance of SQL Server? I believe that once
the /3Gb switch is enabled, that SQL will grab all the memory for itself,
leaving only 128Mb for the OS. We would probably need to specify a maximum
memory usage requirement, per instance, if that was to be the case.
There seems to be 2 options to me. Can anyone offer advice on whether we
should take:
1). Disable the AWE, via the removal of the /3Gb switch, and let SQL Server
manage its own memory
2). Enable AWE, via the use of the /3Gb switch, but then specify the maximum
memory each instance of SQL Server can use.
Does anyone have a view?
Thanks
Paul Aspinall
MCSD, MCDBA
Paul,
The /3GB switch does not enable or disable AWE. That is done via the AWE
option of sp_configure. If you have 4 actual instances of SQL Server plus
IIS on a single box with only 4GB of memory you certainly do not want to use
/3GB. You must share that 4GB with all 5 instances plus the OS and do not
want anything to swap to the swap file. Is there a particular reason to
have 4 separate instances vs. one with all the db's in it? It's hard to say
what the proper memory config should be given I know nothing about any of
your instances or how they are used. You might simply want to stay with
dynamic memory config on all 4 with no /3GB. AWE is only really useful with
more than 4GB anyway. It's IIS that I would be most worried about since it
doesn't play as well with others as SQL Server does.
Andrew J. Kelly SQL MVP
"Paul Aspinall" <paul@.aspy.co.uk> wrote in message
news:oG4Be.28164$O22.20831@.fe1.news.blueyonder.co. uk...
> Hi
> We have a SQL server 2000 cluster, built on Windows 2000 Advanced server.
> We are using an Active / Passive configuration which has 4 instances of
> SQL Enterprise, and 1 Instance of IIS.
> Both boxes are identical, and clustering works just fine.
> However, in order to use the 4Gb in each box, should we enable the /3Gb
> switch in Boot.Ini?
> How then do we configure each instance of SQL Server? I believe that once
> the /3Gb switch is enabled, that SQL will grab all the memory for itself,
> leaving only 128Mb for the OS. We would probably need to specify a maximum
> memory usage requirement, per instance, if that was to be the case.
> There seems to be 2 options to me. Can anyone offer advice on whether we
> should take:
> 1). Disable the AWE, via the removal of the /3Gb switch, and let SQL
> Server manage its own memory
> 2). Enable AWE, via the use of the /3Gb switch, but then specify the
> maximum memory each instance of SQL Server can use.
>
> Does anyone have a view?
> Thanks
>
> Paul Aspinall
> MCSD, MCDBA
>

Friday, February 24, 2012

Avoiding Divide by Zero in Report

What is the experession to evaluate if the result of a computation would be a divide by zero error for a text box in report?

IIF(divide by zero, display nothing, else display computed result)...?

you should actually use it in your query,but...

=iif(value1 = 0 or value2=0,0, value1/value2)

where the values are the values your dividing by.

it's much easier to do it in sql though...

|||I'd recommend either doing it in your Stored Procedure or creating a function to handle it. You don't want to be putting that expression in every sell in which you're doing division.

You can create a function like the one below and place it in the Report code block or put it in an assembly and reference the assembly in the report.

Public Function CalcAvg(dblNum As Double, dblDen As Double) as Object

if (dblDen = nothing) or (dblDen = 0)
CalcAvg = 0
else
CalcAvg = dblNum / dblDen
end if

End Function|||

I have this code in my Code section

Code Snippet

Public Shared Function DivideXbyY(ByVal X As Decimal, ByVal Y As Decimal) As Decimal
DivideXbyY = IIF(Y=0, 0, X / IIF(Y=0, 1, Y))
End Function

then I call it in the Table/Matrix

Code Snippet

=Code.DivideXbyY(Fields!amt.Value,Fields!tix.Value)

|||Thanks guys, your responses are very helpful and much appreciated.

avoiding accidental deletes...best practises?

Is there a way to get sql server to present a message box asking if you are you sure you want to delete all the data in a table.

also are there any 'best practises' to avoid accidental deletes.

For example Im making a lot of delete statements like:

del from table_name
where item_id = 23

but one time I executed this statement I mistakenly excuted the first line of the statment only, resulting in the loss of all the data in the table, luckily I was able to re-populate the table, but I wish to avoid that mistake in the future.

Let me ask this question: would you want a script stopping half-way through a stored procedure ?

To wait for a user dialog box to complete at 4am ?

If you are having problems with accidential deletes, consider investing in Lumigent log explorer (www.lumigent.com) or Red Gates Log Rescue (www.red-gate.com) - these tools will allow you to recover the data you deleted.

Regards,

DB007

|||

use it in a transaction ....

keep autocommit off....and keep ur logs (incase it still happens) to recover the data..

|||

Hi Airwalker,

The thing to remember is accidents will happen, and just to have processes in place to help clear up the mess afterwards.

Even with transactions (if used correctly) once committed the data is still deleted (accidentially, maliciously - by a hacker !).

The options available to you are : Recover directly from the transaction log (as per previous post), Restore a prior database backup and copy the data back into the live system, make the database read-only.

Now option 3 is unlikely as this system requires updates, but suitable for systems that are designed to be read-only.

Option 2 - takes a long time, even with page level restores available in SQL 2005 - and normally a significant amount of disk space (esp. if a copy of the database needs to be taken).

Option 1 - Usually involves the least amount of time, and can be performed offline whilst users are continuing to use the system.

Best Regards,

|||thanks for the advice guys...much appreciated.|||

If you are running SQL Server 2005, you might consider using SQL Server 2005 database snapshots. They are great for allowing you to very easily recover data that gets altered or deleted by mistakes like that.

You could have an Agent job that periodically creates a snapshot and gets rid of older snapshots. Then, you can pull data from the most recent snapshot to fix those kind of mistakes. Just be aware that DB snapshots are by no means a replacement for a good backup strategy.

http://glennberrysqlperformance.spaces.live.com

|||

one method i have adoped is .. from my application i can delete (for Eg) only one users roles (1-M) from UserROle table.. So i have created a Trigger which checks whether deleted table contains morethan one user if yes roll back and exist.. So from QA such delete statment can be prevented....

One more method is Just create a trigger in all table which checks Count(*) from deleted table and it is 0 then it means you are deleting all the rows... roll back and raiserror..

All these methods depends... it may or may not valid in all scenario...

try this script and do the necessary modificaiton

drop table test

create table test (col1 int)

insert into test select 1

insert into test select 2

insert into test select 3

insert into test select 4

insert into test select 5

insert into test select 6

insert into test select 1

alter trigger testtrig

on test for delete

declare @.i int

select @.i=count(*) from test

print @.i

if @.i=0

begin

print 'u r trying to delete blah...blah'

rollback tran

end

delete from test

delete from test where col1=1

select *From test

Madhu

Thursday, February 16, 2012

Average Disk Queue Length

Hey guys. I've a problem with Average Disk queue length staying ta 100%. the
server is a very good box and doesn't do much on it. It also has 6gb RAM with
2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz hyper
threaded.
It is on Raid 5 and all the files are located on 1 drive. I know this is the
worst practice but this is a DEV box and not much is happening on it. There
is Replication going from instance 1 on the machine to instance2 on the
machine. But, still this should not happen. Someone Please help...Disk Queue counters are not a percentage they are a number. So either it is
not the Avg Disk Queue counter or you are misinterpreting it. Have a look
at this:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...|||Disk queue length is not measured in percent. Are you sure that's what you
have? That said, the avg disk queue length should not be > 2x #of spindles.
With all of your files on the same drive, it's not surprising that you have
this problem. RAID5 compounds this, as well. Of course, replication adds
to the burden, depending on the volume of data coming across, and this will
be exacerbated by snapshot synchronization.
Can you purchase some disks and create a couple of RAID10 sets?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> the
> server is a very good box and doesn't do much on it. It also has 6gb RAM
> with
> 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> hyper
> threaded.
> It is on Raid 5 and all the files are located on 1 drive. I know this is
> the
> worst practice but this is a DEV box and not much is happening on it.
> There
> is Replication going from instance 1 on the machine to instance2 on the
> machine. But, still this should not happen. Someone Please help...|||I understand these problems and yes, I had written it wrong. It's a counter
an dis not measured in percent. The only reason i posted this question here
is because it should not be so high even with all the existing WORST
practices followed. But, I didn't have much of a choice since it's a dev box
and not much s going on with this server here which is why the Avg disk queue
length should not be high... Is there anything else i should do to find why
it's so high?
thank you in advance.
"Tom Moreau" wrote:
> Disk queue length is not measured in percent. Are you sure that's what you
> have? That said, the avg disk queue length should not be > 2x #of spindles.
> With all of your files on the same drive, it's not surprising that you have
> this problem. RAID5 compounds this, as well. Of course, replication adds
> to the burden, depending on the volume of data coming across, and this will
> be exacerbated by snapshot synchronization.
> Can you purchase some disks and create a couple of RAID10 sets?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
> > Hey guys. I've a problem with Average Disk queue length staying ta 100%.
> > the
> > server is a very good box and doesn't do much on it. It also has 6gb RAM
> > with
> > 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
> > hyper
> > threaded.
> > It is on Raid 5 and all the files are located on 1 drive. I know this is
> > the
> > worst practice but this is a DEV box and not much is happening on it.
> > There
> > is Replication going from instance 1 on the machine to instance2 on the
> > machine. But, still this should not happen. Someone Please help...
>
>|||Maybe these will help:
http://www.microsoft.com/sql/techinfo/administration/2000/perftuning.asp
Performance WP's
http://www.swynk.com/friends/vandenberg/perfmonitor.asp Perfmon counters
http://www.sql-server-performance.com/sql_server_performance_audit.asp
Hardware Performance CheckList
http://www.sql-server-performance.com/best_sql_server_performance_tips.asp
SQL 2000 Performance tuning tips
http://www.support.microsoft.com/?id=q224587 Troubleshooting App
Performance
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_24u1.asp
Disk Monitoring
--
Andrew J. Kelly SQL MVP
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
>> Disk queue length is not measured in percent. Are you sure that's what
>> you
>> have? That said, the avg disk queue length should not be > 2x #of
>> spindles.
>> With all of your files on the same drive, it's not surprising that you
>> have
>> this problem. RAID5 compounds this, as well. Of course, replication
>> adds
>> to the burden, depending on the volume of data coming across, and this
>> will
>> be exacerbated by snapshot synchronization.
>> Can you purchase some disks and create a couple of RAID10 sets?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
>> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
>> > Hey guys. I've a problem with Average Disk queue length staying ta
>> > 100%.
>> > the
>> > server is a very good box and doesn't do much on it. It also has 6gb
>> > RAM
>> > with
>> > 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
>> > hyper
>> > threaded.
>> > It is on Raid 5 and all the files are located on 1 drive. I know this
>> > is
>> > the
>> > worst practice but this is a DEV box and not much is happening on it.
>> > There
>> > is Replication going from instance 1 on the machine to instance2 on the
>> > machine. But, still this should not happen. Someone Please help...
>>|||See Andrew's reply. Also, post the numbers you get for the read and write
queue lengths.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:DD9DE915-894E-4723-ADA7-BDFA25491DBE@.microsoft.com...
>I understand these problems and yes, I had written it wrong. It's a counter
> an dis not measured in percent. The only reason i posted this question
> here
> is because it should not be so high even with all the existing WORST
> practices followed. But, I didn't have much of a choice since it's a dev
> box
> and not much s going on with this server here which is why the Avg disk
> queue
> length should not be high... Is there anything else i should do to find
> why
> it's so high?
> thank you in advance.
> "Tom Moreau" wrote:
>> Disk queue length is not measured in percent. Are you sure that's what
>> you
>> have? That said, the avg disk queue length should not be > 2x #of
>> spindles.
>> With all of your files on the same drive, it's not surprising that you
>> have
>> this problem. RAID5 compounds this, as well. Of course, replication
>> adds
>> to the burden, depending on the volume of data coming across, and this
>> will
>> be exacerbated by snapshot synchronization.
>> Can you purchase some disks and create a couple of RAID10 sets?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada tom@.cips.ca
>> www.pinpub.com
>> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
>> news:1EED4616-0587-484B-AB7F-AFDF03065F79@.microsoft.com...
>> > Hey guys. I've a problem with Average Disk queue length staying ta
>> > 100%.
>> > the
>> > server is a very good box and doesn't do much on it. It also has 6gb
>> > RAM
>> > with
>> > 2gb each for 2 instances and 2 gb for Windows. 2 Xeon procs at 2.4Ghz
>> > hyper
>> > threaded.
>> > It is on Raid 5 and all the files are located on 1 drive. I know this
>> > is
>> > the
>> > worst practice but this is a DEV box and not much is happening on it.
>> > There
>> > is Replication going from instance 1 on the machine to instance2 on the
>> > machine. But, still this should not happen. Someone Please help...
>>

Tuesday, March 27, 2012

Backing Up SQL DBs With Retrospecct

Thursday, March 22, 2012

Backing up and restoring 50 databases to a new server

Backing up and restoring 50 databases to a new server

Backing up and restoring 50 databases to a new server

Sunday, March 11, 2012

AWE SQL Performance Problem

Thursday, March 8, 2012

awe question

awe question

awe question

AWE on 4GB multi-instance box?

Friday, February 24, 2012

Avoiding Divide by Zero in Report

avoiding accidental deletes...best practises?

Thursday, February 16, 2012

Average Disk Queue Length

Average Disk Queue Length

Monday, February 13, 2012

Average Disk Queue Length

Autonumber field

Blog Archive

About Me