Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Showing posts with label related. Show all posts
Showing posts with label related. Show all posts
Friday, March 30, 2012
Help Identifying Full table scans with objectname
Hello
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Is there a faster way to clearly identify and record
all the full table scans with the related objects (tables,
stored procs). I tried using profiler, but the profiler
gives only Scan start/stop. Also I tried the performance
counter, but the performance counter for sqlserver gives
only scans/sec but doesn't not tell me which sql
statements/tables/indexes are involved.
I need something like the following for all full table
scans.
DatabaseName, ObjectName, SQL (if applicable)
Any help will be great.
thanks
TonyYou usually look at query execution plans to get an idea what table is being
scaned. probably should start looking at table without an index - they
definitely use table scans. For table with indexes, it's difficult to say.
At various times, the query optimizer may choose to do table scan, or to
pick index seek depending on how the query is written.
richard
"Tony" <anonymous@.discussions.microsoft.com> wrote in message
news:4ab601c3ffc4$3e59a0c0$a501280a@.phx.gbl...
> Hello
> Is there a faster way to clearly identify and record
> all the full table scans with the related objects (tables,
> stored procs). I tried using profiler, but the profiler
> gives only Scan start/stop. Also I tried the performance
> counter, but the performance counter for sqlserver gives
> only scans/sec but doesn't not tell me which sql
> statements/tables/indexes are involved.
> I need something like the following for all full table
> scans.
> DatabaseName, ObjectName, SQL (if applicable)
> Any help will be great.
> thanks
> Tony|||excellent question...
I need the answer to this as well.
Greg Jackson
PDX, Oregon
Monday, March 12, 2012
HELP ! Production down and backups not restoring properly!
I was mistakenly logged into the wrong server this morning and deleted all
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backu
ps?
You could do that, but log backups has lot of advantages to diff backups. Th
ese includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequ
ent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it f
or diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input fro
m this group. It just
> seems that it ends up having an inordinate amount of files to have to rest
ore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backu
ps? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the d
atabase
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it s
till shows the database
> in a recovering state and it cannot be accessed!! How can this happen? Wh
at have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My
hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tabl
es in the msdb database,
which can give you a clue where it is.
> 2. When doing a restore through the SMS after selecting a file off disk y
ou get the backup sets
> in that file with checkboxes to select the backupset to restore. My full
backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and
RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday mor
ning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it
gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk y
ou get the backup sets
> in that file with checkboxes to select the backupset to restore. My full
backups do not show
> this. The list is empty so I cannot go on. The restore seems successful
by script. My script
> statements originated from the "Generate Script" button when doing backups
through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backu
ps?
You could do that, but log backups has lot of advantages to diff backups. Th
ese includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequ
ent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it f
or diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input fro
m this group. It just
> seems that it ends up having an inordinate amount of files to have to rest
ore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backu
ps? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the d
atabase
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it s
till shows the database
> in a recovering state and it cannot be accessed!! How can this happen? Wh
at have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My
hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tabl
es in the msdb database,
which can give you a clue where it is.
> 2. When doing a restore through the SMS after selecting a file off disk y
ou get the backup sets
> in that file with checkboxes to select the backupset to restore. My full
backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and
RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday mor
ning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. Whe
n I restore the full
> backup from last saturday it restores fine. When I go to restore the firs
t log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it
gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk y
ou get the backup sets
> in that file with checkboxes to select the backupset to restore. My full
backups do not show
> this. The list is empty so I cannot go on. The restore seems successful
by script. My script
> statements originated from the "Generate Script" button when doing backups
through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
HELP ! Production down and backups not restoring properly!
I was mistakenly logged into the wrong server this morning and deleted all
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!
Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!
Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
HELP ! Production down and backups not restoring properly!
I was mistakenly logged into the wrong server this morning and deleted all
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backups?
You could do that, but log backups has lot of advantages to diff backups. These includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it for diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input from this group. It just
> seems that it ends up having an inordinate amount of files to have to restore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backups? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!! How can this happen? What have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tables in the msdb database,
which can give you a clue where it is.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on. The restore seems successful by script. My script
> statements originated from the "Generate Script" button when doing backups through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
of our product related information!!
I've been doing weekly full backups with hourly log file backups.
I have 2 questions...
1. The most pressing first. My weekly backup happens every Saturday
morning at 2am. My hourly log backups stop between midnight and begin again
at 6:15am Saturday. When I restore the full backup from last saturday it
restores fine. When I go to restore the first log it tells me the log is
too recent and there is an earlier one with LSN###....
I'm really stuck here...there is absolutely no way that there could be an
earlier log. I even tried a RESTORE LOG using the full backup to get the
log from there and it gives the same message.
2. When doing a restore through the SMS after selecting a file off disk you
get the backup sets in that file with checkboxes to select the backupset to
restore. My full backups do not show this. The list is empty so I cannot
go on. The restore seems successful by script. My script statements
originated from the "Generate Script" button when doing backups through the
interface. Am I missing something here? Below is a sample backup
statement:
BACKUP DATABASE [Products]
FILEGROUP = N'PRIMARY'
TO DISK = @.strBackupFile
WITH NOFORMAT, NOINIT,
NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
ANY HELP IS ***GREATLY*** APPRECIATED!!!Actually now I'm seeing after restoring the full backup WITH RECOVERY it
still shows the database in a recovering state and it cannot be accessed!!
How can this happen? What have I done wrong here?
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||And while on the topic of backups, I setup this process based on input from
this group. It just seems that it ends up having an inordinate amount of
files to have to restore to get back online. Wouldn't it be better to just
do differentials hourly instead of log backups? Then in a failure you'd
never have more than 2 files to restore.
Just wondering.
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all
>of our product related information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday
> morning at 2am. My hourly log backups stop between midnight and begin
> again at 6:15am Saturday. When I restore the full backup from last
> saturday it restores fine. When I go to restore the first log it tells me
> the log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an
> earlier log. I even tried a RESTORE LOG using the full backup to get the
> log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk
> you get the backup sets in that file with checkboxes to select the
> backupset to restore. My full backups do not show this. The list is
> empty so I cannot go on. The restore seems successful by script. My
> script statements originated from the "Generate Script" button when doing
> backups through the interface. Am I missing something here? Below is a
> sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Wouldn't it be better to just do differentials hourly instead of log backups?
You could do that, but log backups has lot of advantages to diff backups. These includes
Point in time restore.
Ability to backup log if database becomes toast (zero data loss)
Ability to backup log, and restore the most recent db backup and all subsequent log backup sin case
you get a corruption of the database.
Etc.
In short, log backups has so many advantages so you don't want to trade it for diff backups just for
convenience. But sometimes you want to do both db, diff *and* log backups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OnZRT6xEHHA.4740@.TK2MSFTNGP03.phx.gbl...
> And while on the topic of backups, I setup this process based on input from this group. It just
> seems that it ends up having an inordinate amount of files to have to restore to get back online.
> Wouldn't it be better to just do differentials hourly instead of log backups? Then in a failure
> you'd never have more than 2 files to restore.
> Just wondering.
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!!
Perhaps just a refresh problem in your GUI?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OppZhXxEHHA.3224@.TK2MSFTNGP04.phx.gbl...
> Actually now I'm seeing after restoring the full backup WITH RECOVERY it still shows the database
> in a recovering state and it cannot be accessed!! How can this happen? What have I done wrong
> here?
>
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>>information!!
>> I've been doing weekly full backups with hourly log file backups.
>> I have 2 questions...
>> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
>> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
>> backup from last saturday it restores fine. When I go to restore the first log it tells me the
>> log is too recent and there is an earlier one with LSN###....
>> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
>> tried a RESTORE LOG using the full backup to get the log from there and it gives the same
>> message.
>> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
>> in that file with checkboxes to select the backupset to restore. My full backups do not show
>> this. The list is empty so I cannot go on. The restore seems successful by script. My script
>> statements originated from the "Generate Script" button when doing backups through the interface.
>> Am I missing something here? Below is a sample backup statement:
>> BACKUP DATABASE [Products]
>> FILEGROUP = N'PRIMARY'
>> TO DISK = @.strBackupFile
>> WITH NOFORMAT, NOINIT,
>> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>>
>> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>|||> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
You need to hunt down that log backup. You can check the backup history tables in the msdb database,
which can give you a clue where it is.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on.
I suggest you read up on the RESTORE command and use RESTORE HEADERONLY and RESTORE FILELISTONLY so
you can manage a restore when the GUI is acting up on you.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:OaKy0QxEHHA.4404@.TK2MSFTNGP06.phx.gbl...
>I was mistakenly logged into the wrong server this morning and deleted all of our product related
>information!!
> I've been doing weekly full backups with hourly log file backups.
> I have 2 questions...
> 1. The most pressing first. My weekly backup happens every Saturday morning at 2am. My hourly
> log backups stop between midnight and begin again at 6:15am Saturday. When I restore the full
> backup from last saturday it restores fine. When I go to restore the first log it tells me the
> log is too recent and there is an earlier one with LSN###....
> I'm really stuck here...there is absolutely no way that there could be an earlier log. I even
> tried a RESTORE LOG using the full backup to get the log from there and it gives the same message.
> 2. When doing a restore through the SMS after selecting a file off disk you get the backup sets
> in that file with checkboxes to select the backupset to restore. My full backups do not show
> this. The list is empty so I cannot go on. The restore seems successful by script. My script
> statements originated from the "Generate Script" button when doing backups through the interface.
> Am I missing something here? Below is a sample backup statement:
> BACKUP DATABASE [Products]
> FILEGROUP = N'PRIMARY'
> TO DISK = @.strBackupFile
> WITH NOFORMAT, NOINIT,
> NAME = @.strBackupFile, SKIP, REWIND, NOUNLOAD, STATS = 10
>
> ANY HELP IS ***GREATLY*** APPRECIATED!!!
>
Monday, February 27, 2012
Help - Best way to store related data (across or down)?
Good day.
I have a form with approx 90 related components. All 90 components are
associated with an App_ID. An App_ID can occur more than once but with
different data from the 90 fields.
I simply cannot decide whether to store the data from each component in
it's own column or as a separate row.
Any suggestions would be greatly appreciated.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***I think you hit on the key word here "related". To me that automatically
implies that you would set up a separate, but related, table to contain your
90 components. Without knowing more than you said about your app, I would
probably store each related component in its own row in the second table.
My assumption is that not all 90 components would necessarily be populated
for all App_ID's and that if, in the future, you add a 91st component you
don't necessarily want to change your whole schema.
Here's some example DDL. It contains 3 related tables, Apps which contains
your AppID and other App-specific info, AppComponents which contains an
App-specific component list, and LookupComponents which contains
Component-specific information (in this case just ComponentID and Name):
CREATE TABLE [dbo].[AppComponents] (
[AppID] [int] NOT NULL ,
[ComponentID] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Apps] (
[AppID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
CREATE TABLE [dbo].[LookupComponents] (
[ComponentID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
ALTER TABLE [dbo].[AppComponents] WITH NOCHECK ADD
CONSTRAINT [PK_AppComponents] PRIMARY KEY CLUSTERED
(
[AppID],
[ComponentID]
)
GO
ALTER TABLE [dbo].[Apps] WITH NOCHECK ADD
CONSTRAINT [PK_Apps] PRIMARY KEY CLUSTERED
(
[AppID]
)
GO
ALTER TABLE [dbo].[LookupComponents] WITH NOCHECK ADD
CONSTRAINT [PK_LookupComponents] PRIMARY KEY CLUSTERED
(
[ComponentID]
)
GO
ALTER TABLE [dbo].[AppComponents] ADD
CONSTRAINT [FK_AppComponents_Apps] FOREIGN KEY
(
[AppID]
) REFERENCES [dbo].[Apps] (
[AppID]
),
CONSTRAINT [FK_AppComponents_LookupComponents] FOREIGN KEY
(
[ComponentID]
) REFERENCES [dbo].[LookupComponents] (
[ComponentID]
)
GO
"bajan_rick" <bajanrick@.devdex.com> wrote in message
news:eaMBbw1QFHA.4028@.tk2msftngp13.phx.gbl...
> Good day.
> I have a form with approx 90 related components. All 90 components are
> associated with an App_ID. An App_ID can occur more than once but with
> different data from the 90 fields.
>
> I simply cannot decide whether to store the data from each component in
> it's own column or as a separate row.
>
> Any suggestions would be greatly appreciated.
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> I have a form with approx 90 related components. All 90 components
are associated with an App_ID. An App_ID can occur more than once but
with different data from the 90 fields. <<
Do not confuse the forms that collect data with the data model and the
tables in the schema. If they are all attributes of the same entity,
then they belong in one row in the table. If a subset of columns is
another entity, then it needs to be in its own table.
Then we have to ask if "app_id" is really a key or not. It sounds like
a sequential form number (remember the "Bates Numbering Stamp" in the
old days?) that has nothing to do with the data model, but is used for
data collection control.
Without any real information, it is imposible to give specific advice.
But a while a 90 column table is possible, it is rare. I would guess
-- I mean guess -- that this is really a 5 to 6 column schema.
The worst thing you can do is build an EAV design or some other variant
that mixes data and metadata in the name of a "flexible" design. They
fall aprt in about a year of production work after running insanely
slow.|||Thanks guys.
I'm not if I can explain the app anymore. I guess it's more like a
spreadsheet where there is the possiblit of multiple rows being
represented by 1 App_ID (PK).
My table is currently:
CREATE TABLE [dbo].[App_Sheet] (
[SheetID] [int] NOT NULL ,
[AppID] [int] NOT NULL ,
[Component_ID] [int],
[Value] [varchar] (50) NULL
)
GO
CELKO:
What do you mean by
"..this is really a 5 to 6 column schema"
*** Sent via Developersdex http://www.examnotes.net ***
I have a form with approx 90 related components. All 90 components are
associated with an App_ID. An App_ID can occur more than once but with
different data from the 90 fields.
I simply cannot decide whether to store the data from each component in
it's own column or as a separate row.
Any suggestions would be greatly appreciated.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***I think you hit on the key word here "related". To me that automatically
implies that you would set up a separate, but related, table to contain your
90 components. Without knowing more than you said about your app, I would
probably store each related component in its own row in the second table.
My assumption is that not all 90 components would necessarily be populated
for all App_ID's and that if, in the future, you add a 91st component you
don't necessarily want to change your whole schema.
Here's some example DDL. It contains 3 related tables, Apps which contains
your AppID and other App-specific info, AppComponents which contains an
App-specific component list, and LookupComponents which contains
Component-specific information (in this case just ComponentID and Name):
CREATE TABLE [dbo].[AppComponents] (
[AppID] [int] NOT NULL ,
[ComponentID] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Apps] (
[AppID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
CREATE TABLE [dbo].[LookupComponents] (
[ComponentID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
ALTER TABLE [dbo].[AppComponents] WITH NOCHECK ADD
CONSTRAINT [PK_AppComponents] PRIMARY KEY CLUSTERED
(
[AppID],
[ComponentID]
)
GO
ALTER TABLE [dbo].[Apps] WITH NOCHECK ADD
CONSTRAINT [PK_Apps] PRIMARY KEY CLUSTERED
(
[AppID]
)
GO
ALTER TABLE [dbo].[LookupComponents] WITH NOCHECK ADD
CONSTRAINT [PK_LookupComponents] PRIMARY KEY CLUSTERED
(
[ComponentID]
)
GO
ALTER TABLE [dbo].[AppComponents] ADD
CONSTRAINT [FK_AppComponents_Apps] FOREIGN KEY
(
[AppID]
) REFERENCES [dbo].[Apps] (
[AppID]
),
CONSTRAINT [FK_AppComponents_LookupComponents] FOREIGN KEY
(
[ComponentID]
) REFERENCES [dbo].[LookupComponents] (
[ComponentID]
)
GO
"bajan_rick" <bajanrick@.devdex.com> wrote in message
news:eaMBbw1QFHA.4028@.tk2msftngp13.phx.gbl...
> Good day.
> I have a form with approx 90 related components. All 90 components are
> associated with an App_ID. An App_ID can occur more than once but with
> different data from the 90 fields.
>
> I simply cannot decide whether to store the data from each component in
> it's own column or as a separate row.
>
> Any suggestions would be greatly appreciated.
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> I have a form with approx 90 related components. All 90 components
are associated with an App_ID. An App_ID can occur more than once but
with different data from the 90 fields. <<
Do not confuse the forms that collect data with the data model and the
tables in the schema. If they are all attributes of the same entity,
then they belong in one row in the table. If a subset of columns is
another entity, then it needs to be in its own table.
Then we have to ask if "app_id" is really a key or not. It sounds like
a sequential form number (remember the "Bates Numbering Stamp" in the
old days?) that has nothing to do with the data model, but is used for
data collection control.
Without any real information, it is imposible to give specific advice.
But a while a 90 column table is possible, it is rare. I would guess
-- I mean guess -- that this is really a 5 to 6 column schema.
The worst thing you can do is build an EAV design or some other variant
that mixes data and metadata in the name of a "flexible" design. They
fall aprt in about a year of production work after running insanely
slow.|||Thanks guys.
I'm not if I can explain the app anymore. I guess it's more like a
spreadsheet where there is the possiblit of multiple rows being
represented by 1 App_ID (PK).
My table is currently:
CREATE TABLE [dbo].[App_Sheet] (
[SheetID] [int] NOT NULL ,
[AppID] [int] NOT NULL ,
[Component_ID] [int],
[Value] [varchar] (50) NULL
)
GO
CELKO:
What do you mean by
"..this is really a 5 to 6 column schema"
*** Sent via Developersdex http://www.examnotes.net ***
Friday, February 24, 2012
Help
Could my prblem be related to this?
http://support.microsoft.com/defaul...kb;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>Hi
The article says that you are not using pooling, the solution may be to turn
it on!
You may want to check MDAC and SQL Server versions to see if there are any
upgrades/patches that may help.
You should also check to see if you have any network issues such as a faulty
card/router or switch.
John
"TLehtinen" wrote:
> Could my prblem be related to this?
> http://support.microsoft.com/defaul...kb;en-us;328476
> We have one SQL-server and 2 separate iis server as cluster. I run netstat
> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp sessio
n
> open to our SQLserver. What shoud I do to prevent these erros happening?
>
> T
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestiss?:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
>
>
>|||Hello!
I just recieved erros and noticed that at the same time netstat reported
4000 connections to our sql-server. So it had it's limit. Now everyting
seems to go ok, becouse we raised upper limit to 20 000 connections. We all
so chekced our application to close connections more rapidly.
All tought, at the same time our other cluster server reported only 2000
connections and cluster has equal load balancing... How many connections
you have on on your client computers(IIS)? 4000 seems not to be so much...
We do have web hits somewhere in 3 000 000 per day and many of our sites are
dymamic ones... but, should there be so much open connctions?
t
"John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
viestiss:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...[vbcol=seagreen]
> Hi
> The article says that you are not using pooling, the solution may be to
> turn
> it on!
> You may want to check MDAC and SQL Server versions to see if there are any
> upgrades/patches that may help.
> You should also check to see if you have any network issues such as a
> faulty
> card/router or switch.
> John
> "TLehtinen" wrote:
>|||Hi
You can use perfmon to monitor connections on your database, if you track
the counter you may see some trend. If you are using connection pooling then
you would need fewer connections and the time taken to connect would not
usually be an issue therefore 4000 connections could service a significantly
larger number of users. Each connection will use a small amount of memory on
the server so reducing the number of connections by using pooling is
benificial in many ways.
If connections are being left open then you would need to check your
application and IIS settings, matching up the connections from perfmon to yo
u
web logs may help to identify what is happening.
John
"TLehtinen" wrote:
> Hello!
> I just recieved erros and noticed that at the same time netstat reported
> 4000 connections to our sql-server. So it had it's limit. Now everyting
> seems to go ok, becouse we raised upper limit to 20 000 connections. We al
l
> so chekced our application to close connections more rapidly.
> All tought, at the same time our other cluster server reported only 2000
> connections and cluster has equal load balancing... How many connections
> you have on on your client computers(IIS)? 4000 seems not to be so much..
.
> We do have web hits somewhere in 3 000 000 per day and many of our sites a
re
> dymamic ones... but, should there be so much open connctions?
>
> t
> "John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
> viestiss?:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
>
>
http://support.microsoft.com/defaul...kb;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>Hi
The article says that you are not using pooling, the solution may be to turn
it on!
You may want to check MDAC and SQL Server versions to see if there are any
upgrades/patches that may help.
You should also check to see if you have any network issues such as a faulty
card/router or switch.
John
"TLehtinen" wrote:
> Could my prblem be related to this?
> http://support.microsoft.com/defaul...kb;en-us;328476
> We have one SQL-server and 2 separate iis server as cluster. I run netstat
> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp sessio
n
> open to our SQLserver. What shoud I do to prevent these erros happening?
>
> T
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestiss?:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
>
>
>|||Hello!
I just recieved erros and noticed that at the same time netstat reported
4000 connections to our sql-server. So it had it's limit. Now everyting
seems to go ok, becouse we raised upper limit to 20 000 connections. We all
so chekced our application to close connections more rapidly.
All tought, at the same time our other cluster server reported only 2000
connections and cluster has equal load balancing... How many connections
you have on on your client computers(IIS)? 4000 seems not to be so much...
We do have web hits somewhere in 3 000 000 per day and many of our sites are
dymamic ones... but, should there be so much open connctions?
t
"John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
viestiss:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...[vbcol=seagreen]
> Hi
> The article says that you are not using pooling, the solution may be to
> turn
> it on!
> You may want to check MDAC and SQL Server versions to see if there are any
> upgrades/patches that may help.
> You should also check to see if you have any network issues such as a
> faulty
> card/router or switch.
> John
> "TLehtinen" wrote:
>|||Hi
You can use perfmon to monitor connections on your database, if you track
the counter you may see some trend. If you are using connection pooling then
you would need fewer connections and the time taken to connect would not
usually be an issue therefore 4000 connections could service a significantly
larger number of users. Each connection will use a small amount of memory on
the server so reducing the number of connections by using pooling is
benificial in many ways.
If connections are being left open then you would need to check your
application and IIS settings, matching up the connections from perfmon to yo
u
web logs may help to identify what is happening.
John
"TLehtinen" wrote:
> Hello!
> I just recieved erros and noticed that at the same time netstat reported
> 4000 connections to our sql-server. So it had it's limit. Now everyting
> seems to go ok, becouse we raised upper limit to 20 000 connections. We al
l
> so chekced our application to close connections more rapidly.
> All tought, at the same time our other cluster server reported only 2000
> connections and cluster has equal load balancing... How many connections
> you have on on your client computers(IIS)? 4000 seems not to be so much..
.
> We do have web hits somewhere in 3 000 000 per day and many of our sites a
re
> dymamic ones... but, should there be so much open connctions?
>
> t
> "John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
> viestiss?:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
>
>
Sunday, February 19, 2012
Help
Could my prblem be related to this?
http://support.microsoft.com/default...b;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl. ..
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>
Hi
The article says that you are not using pooling, the solution may be to turn
it on!
You may want to check MDAC and SQL Server versions to see if there are any
upgrades/patches that may help.
You should also check to see if you have any network issues such as a faulty
card/router or switch.
John
"TLehtinen" wrote:
> Could my prblem be related to this?
> http://support.microsoft.com/default...b;en-us;328476
> We have one SQL-server and 2 separate iis server as cluster. I run netstat
> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
> open to our SQLserver. What shoud I do to prevent these erros happening?
>
> T
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestiss?:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl ...
>
>
>
|||Hello!
I just recieved erros and noticed that at the same time netstat reported
4000 connections to our sql-server. So it had it's limit. Now everyting
seems to go ok, becouse we raised upper limit to 20 000 connections. We all
so chekced our application to close connections more rapidly.
All tought, at the same time our other cluster server reported only 2000
connections and cluster has equal load balancing... How many connections
you have on on your client computers(IIS)? 4000 seems not to be so much...
We do have web hits somewhere in 3 000 000 per day and many of our sites are
dymamic ones... but, should there be so much open connctions?
t
"John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
viestiss:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...[vbcol=seagreen]
> Hi
> The article says that you are not using pooling, the solution may be to
> turn
> it on!
> You may want to check MDAC and SQL Server versions to see if there are any
> upgrades/patches that may help.
> You should also check to see if you have any network issues such as a
> faulty
> card/router or switch.
> John
> "TLehtinen" wrote:
|||Hi
You can use perfmon to monitor connections on your database, if you track
the counter you may see some trend. If you are using connection pooling then
you would need fewer connections and the time taken to connect would not
usually be an issue therefore 4000 connections could service a significantly
larger number of users. Each connection will use a small amount of memory on
the server so reducing the number of connections by using pooling is
benificial in many ways.
If connections are being left open then you would need to check your
application and IIS settings, matching up the connections from perfmon to you
web logs may help to identify what is happening.
John
"TLehtinen" wrote:
> Hello!
> I just recieved erros and noticed that at the same time netstat reported
> 4000 connections to our sql-server. So it had it's limit. Now everyting
> seems to go ok, becouse we raised upper limit to 20 000 connections. We all
> so chekced our application to close connections more rapidly.
> All tought, at the same time our other cluster server reported only 2000
> connections and cluster has equal load balancing... How many connections
> you have on on your client computers(IIS)? 4000 seems not to be so much...
> We do have web hits somewhere in 3 000 000 per day and many of our sites are
> dymamic ones... but, should there be so much open connctions?
>
> t
> "John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
> viestiss?:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
>
>
http://support.microsoft.com/default...b;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestiss:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl. ..
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>
Hi
The article says that you are not using pooling, the solution may be to turn
it on!
You may want to check MDAC and SQL Server versions to see if there are any
upgrades/patches that may help.
You should also check to see if you have any network issues such as a faulty
card/router or switch.
John
"TLehtinen" wrote:
> Could my prblem be related to this?
> http://support.microsoft.com/default...b;en-us;328476
> We have one SQL-server and 2 separate iis server as cluster. I run netstat
> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
> open to our SQLserver. What shoud I do to prevent these erros happening?
>
> T
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestiss?:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl ...
>
>
>
|||Hello!
I just recieved erros and noticed that at the same time netstat reported
4000 connections to our sql-server. So it had it's limit. Now everyting
seems to go ok, becouse we raised upper limit to 20 000 connections. We all
so chekced our application to close connections more rapidly.
All tought, at the same time our other cluster server reported only 2000
connections and cluster has equal load balancing... How many connections
you have on on your client computers(IIS)? 4000 seems not to be so much...
We do have web hits somewhere in 3 000 000 per day and many of our sites are
dymamic ones... but, should there be so much open connctions?
t
"John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
viestiss:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...[vbcol=seagreen]
> Hi
> The article says that you are not using pooling, the solution may be to
> turn
> it on!
> You may want to check MDAC and SQL Server versions to see if there are any
> upgrades/patches that may help.
> You should also check to see if you have any network issues such as a
> faulty
> card/router or switch.
> John
> "TLehtinen" wrote:
|||Hi
You can use perfmon to monitor connections on your database, if you track
the counter you may see some trend. If you are using connection pooling then
you would need fewer connections and the time taken to connect would not
usually be an issue therefore 4000 connections could service a significantly
larger number of users. Each connection will use a small amount of memory on
the server so reducing the number of connections by using pooling is
benificial in many ways.
If connections are being left open then you would need to check your
application and IIS settings, matching up the connections from perfmon to you
web logs may help to identify what is happening.
John
"TLehtinen" wrote:
> Hello!
> I just recieved erros and noticed that at the same time netstat reported
> 4000 connections to our sql-server. So it had it's limit. Now everyting
> seems to go ok, becouse we raised upper limit to 20 000 connections. We all
> so chekced our application to close connections more rapidly.
> All tought, at the same time our other cluster server reported only 2000
> connections and cluster has equal load balancing... How many connections
> you have on on your client computers(IIS)? 4000 seems not to be so much...
> We do have web hits somewhere in 3 000 000 per day and many of our sites are
> dymamic ones... but, should there be so much open connctions?
>
> t
> "John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
> viestiss?:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
>
>
Help
Could my prblem be related to this?
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>> Hello!
>>
>> alias?
>>
>> Everything has worked fine before we added this quite huge new web page
>> thing... Now pages applications that have worked before are reporting
>> theese error. not all the time only about ten error / day (and we do have
>> now quite a bit of transactions).
>>
>>
>> T
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> DSN, Did you configure an alias in Client Network Utility?
>>
>>
>> "TLehtinen" <timo@.raa.to> wrote in message
>> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> Our sql server started to this...
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied.
>>
>> We added new, quite huge sql-based site to our iis farm and after that
>> we get some times these error messages. Pages wich report these erros
>> are fully working. Why do get these errors? In last day we have
>> recieved about ten of these erors. Eroros are coming from all kins of
>> asp. and .net pages at the same time. After error occusr everything
>> works fine again.
>>
>> Wha can we do to prevent this happening? What causes these type of
>> error messages?
>>
>>
>> Server and connection strings are configured well, this is not error of
>> miss spelled program or connectionstrings. Theese erros are caused
>> somewhere else.
>>
>>
>> t
>>
>>
>>
>>
>>
>
>Hi
The article says that you are not using pooling, the solution may be to turn
it on!
You may want to check MDAC and SQL Server versions to see if there are any
upgrades/patches that may help.
You should also check to see if you have any network issues such as a faulty
card/router or switch.
John
"TLehtinen" wrote:
> Could my prblem be related to this?
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
> We have one SQL-server and 2 separate iis server as cluster. I run netstat
> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
> open to our SQLserver. What shoud I do to prevent these erros happening?
>
> T
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> > The error is pretty clear. It could not find the server. Did you connect
> > to SQL Server on this (new one) web page?
> >
> >
> > "TLehtinen" <timo@.raa.to> wrote in message
> > news:441564a0$0$7479$39db0f71@.news.song.fi...
> >> Hello!
> >>
> >> alias?
> >>
> >> Everything has worked fine before we added this quite huge new web page
> >> thing... Now pages applications that have worked before are reporting
> >> theese error. not all the time only about ten error / day (and we do have
> >> now quite a bit of transactions).
> >>
> >>
> >> T
> >>
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> >> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> >> Hi
> >> DSN, Did you configure an alias in Client Network Utility?
> >>
> >>
> >> "TLehtinen" <timo@.raa.to> wrote in message
> >> news:44155ecc$0$7489$39db0f71@.news.song.fi...
> >> Our sql server started to this...
> >> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> >> access denied.
> >>
> >> We added new, quite huge sql-based site to our iis farm and after that
> >> we get some times these error messages. Pages wich report these erros
> >> are fully working. Why do get these errors? In last day we have
> >> recieved about ten of these erors. Eroros are coming from all kins of
> >> asp. and .net pages at the same time. After error occusr everything
> >> works fine again.
> >>
> >> Wha can we do to prevent this happening? What causes these type of
> >> error messages?
> >>
> >>
> >> Server and connection strings are configured well, this is not error of
> >> miss spelled program or connectionstrings. Theese erros are caused
> >> somewhere else.
> >>
> >>
> >> t
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
>|||Hello!
I just recieved erros and noticed that at the same time netstat reported
4000 connections to our sql-server. So it had it's limit. Now everyting
seems to go ok, becouse we raised upper limit to 20 000 connections. We all
so chekced our application to close connections more rapidly.
All tought, at the same time our other cluster server reported only 2000
connections and cluster has equal load balancing... How many connections
you have on on your client computers(IIS)? 4000 seems not to be so much...
We do have web hits somewhere in 3 000 000 per day and many of our sites are
dymamic ones... but, should there be so much open connctions?
t
"John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
viestissä:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
> Hi
> The article says that you are not using pooling, the solution may be to
> turn
> it on!
> You may want to check MDAC and SQL Server versions to see if there are any
> upgrades/patches that may help.
> You should also check to see if you have any network issues such as a
> faulty
> card/router or switch.
> John
> "TLehtinen" wrote:
>> Could my prblem be related to this?
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
>> We have one SQL-server and 2 separate iis server as cluster. I run
>> netstat
>> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp
>> session
>> open to our SQLserver. What shoud I do to prevent these erros happening?
>>
>> T
>> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
>> > The error is pretty clear. It could not find the server. Did you
>> > connect
>> > to SQL Server on this (new one) web page?
>> >
>> >
>> > "TLehtinen" <timo@.raa.to> wrote in message
>> > news:441564a0$0$7479$39db0f71@.news.song.fi...
>> >> Hello!
>> >>
>> >> alias?
>> >>
>> >> Everything has worked fine before we added this quite huge new web
>> >> page
>> >> thing... Now pages applications that have worked before are reporting
>> >> theese error. not all the time only about ten error / day (and we do
>> >> have
>> >> now quite a bit of transactions).
>> >>
>> >>
>> >> T
>> >>
>> >>
>> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> >> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> >> Hi
>> >> DSN, Did you configure an alias in Client Network Utility?
>> >>
>> >>
>> >> "TLehtinen" <timo@.raa.to> wrote in message
>> >> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> >> Our sql server started to this...
>> >> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> >> access denied.
>> >>
>> >> We added new, quite huge sql-based site to our iis farm and after
>> >> that
>> >> we get some times these error messages. Pages wich report these
>> >> erros
>> >> are fully working. Why do get these errors? In last day we have
>> >> recieved about ten of these erors. Eroros are coming from all kins
>> >> of
>> >> asp. and .net pages at the same time. After error occusr everything
>> >> works fine again.
>> >>
>> >> Wha can we do to prevent this happening? What causes these type of
>> >> error messages?
>> >>
>> >>
>> >> Server and connection strings are configured well, this is not error
>> >> of
>> >> miss spelled program or connectionstrings. Theese erros are caused
>> >> somewhere else.
>> >>
>> >>
>> >> t
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>|||Hi
You can use perfmon to monitor connections on your database, if you track
the counter you may see some trend. If you are using connection pooling then
you would need fewer connections and the time taken to connect would not
usually be an issue therefore 4000 connections could service a significantly
larger number of users. Each connection will use a small amount of memory on
the server so reducing the number of connections by using pooling is
benificial in many ways.
If connections are being left open then you would need to check your
application and IIS settings, matching up the connections from perfmon to you
web logs may help to identify what is happening.
John
"TLehtinen" wrote:
> Hello!
> I just recieved erros and noticed that at the same time netstat reported
> 4000 connections to our sql-server. So it had it's limit. Now everyting
> seems to go ok, becouse we raised upper limit to 20 000 connections. We all
> so chekced our application to close connections more rapidly.
> All tought, at the same time our other cluster server reported only 2000
> connections and cluster has equal load balancing... How many connections
> you have on on your client computers(IIS)? 4000 seems not to be so much...
> We do have web hits somewhere in 3 000 000 per day and many of our sites are
> dymamic ones... but, should there be so much open connctions?
>
> t
> "John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
> viestissä:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
> > Hi
> >
> > The article says that you are not using pooling, the solution may be to
> > turn
> > it on!
> >
> > You may want to check MDAC and SQL Server versions to see if there are any
> > upgrades/patches that may help.
> >
> > You should also check to see if you have any network issues such as a
> > faulty
> > card/router or switch.
> >
> > John
> >
> > "TLehtinen" wrote:
> >
> >> Could my prblem be related to this?
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
> >>
> >> We have one SQL-server and 2 separate iis server as cluster. I run
> >> netstat
> >> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp
> >> session
> >> open to our SQLserver. What shoud I do to prevent these erros happening?
> >>
> >>
> >> T
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> >> viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> >> > The error is pretty clear. It could not find the server. Did you
> >> > connect
> >> > to SQL Server on this (new one) web page?
> >> >
> >> >
> >> > "TLehtinen" <timo@.raa.to> wrote in message
> >> > news:441564a0$0$7479$39db0f71@.news.song.fi...
> >> >> Hello!
> >> >>
> >> >> alias?
> >> >>
> >> >> Everything has worked fine before we added this quite huge new web
> >> >> page
> >> >> thing... Now pages applications that have worked before are reporting
> >> >> theese error. not all the time only about ten error / day (and we do
> >> >> have
> >> >> now quite a bit of transactions).
> >> >>
> >> >>
> >> >> T
> >> >>
> >> >>
> >> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> >> >> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> >> >> Hi
> >> >> DSN, Did you configure an alias in Client Network Utility?
> >> >>
> >> >>
> >> >> "TLehtinen" <timo@.raa.to> wrote in message
> >> >> news:44155ecc$0$7489$39db0f71@.news.song.fi...
> >> >> Our sql server started to this...
> >> >> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> >> >> access denied.
> >> >>
> >> >> We added new, quite huge sql-based site to our iis farm and after
> >> >> that
> >> >> we get some times these error messages. Pages wich report these
> >> >> erros
> >> >> are fully working. Why do get these errors? In last day we have
> >> >> recieved about ten of these erors. Eroros are coming from all kins
> >> >> of
> >> >> asp. and .net pages at the same time. After error occusr everything
> >> >> works fine again.
> >> >>
> >> >> Wha can we do to prevent this happening? What causes these type of
> >> >> error messages?
> >> >>
> >> >>
> >> >> Server and connection strings are configured well, this is not error
> >> >> of
> >> >> miss spelled program or connectionstrings. Theese erros are caused
> >> >> somewhere else.
> >> >>
> >> >>
> >> >> t
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
We have one SQL-server and 2 separate iis server as cluster. I run netstat
and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
open to our SQLserver. What shoud I do to prevent these erros happening?
T
"Uri Dimant" <urid@.iscar.co.il> kirjoitti
viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> The error is pretty clear. It could not find the server. Did you connect
> to SQL Server on this (new one) web page?
>
>
> "TLehtinen" <timo@.raa.to> wrote in message
> news:441564a0$0$7479$39db0f71@.news.song.fi...
>> Hello!
>>
>> alias?
>>
>> Everything has worked fine before we added this quite huge new web page
>> thing... Now pages applications that have worked before are reporting
>> theese error. not all the time only about ten error / day (and we do have
>> now quite a bit of transactions).
>>
>>
>> T
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> Hi
>> DSN, Did you configure an alias in Client Network Utility?
>>
>>
>> "TLehtinen" <timo@.raa.to> wrote in message
>> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> Our sql server started to this...
>> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> access denied.
>>
>> We added new, quite huge sql-based site to our iis farm and after that
>> we get some times these error messages. Pages wich report these erros
>> are fully working. Why do get these errors? In last day we have
>> recieved about ten of these erors. Eroros are coming from all kins of
>> asp. and .net pages at the same time. After error occusr everything
>> works fine again.
>>
>> Wha can we do to prevent this happening? What causes these type of
>> error messages?
>>
>>
>> Server and connection strings are configured well, this is not error of
>> miss spelled program or connectionstrings. Theese erros are caused
>> somewhere else.
>>
>>
>> t
>>
>>
>>
>>
>>
>
>Hi
The article says that you are not using pooling, the solution may be to turn
it on!
You may want to check MDAC and SQL Server versions to see if there are any
upgrades/patches that may help.
You should also check to see if you have any network issues such as a faulty
card/router or switch.
John
"TLehtinen" wrote:
> Could my prblem be related to this?
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
> We have one SQL-server and 2 separate iis server as cluster. I run netstat
> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp session
> open to our SQLserver. What shoud I do to prevent these erros happening?
>
> T
> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> > The error is pretty clear. It could not find the server. Did you connect
> > to SQL Server on this (new one) web page?
> >
> >
> > "TLehtinen" <timo@.raa.to> wrote in message
> > news:441564a0$0$7479$39db0f71@.news.song.fi...
> >> Hello!
> >>
> >> alias?
> >>
> >> Everything has worked fine before we added this quite huge new web page
> >> thing... Now pages applications that have worked before are reporting
> >> theese error. not all the time only about ten error / day (and we do have
> >> now quite a bit of transactions).
> >>
> >>
> >> T
> >>
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> >> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> >> Hi
> >> DSN, Did you configure an alias in Client Network Utility?
> >>
> >>
> >> "TLehtinen" <timo@.raa.to> wrote in message
> >> news:44155ecc$0$7489$39db0f71@.news.song.fi...
> >> Our sql server started to this...
> >> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> >> access denied.
> >>
> >> We added new, quite huge sql-based site to our iis farm and after that
> >> we get some times these error messages. Pages wich report these erros
> >> are fully working. Why do get these errors? In last day we have
> >> recieved about ten of these erors. Eroros are coming from all kins of
> >> asp. and .net pages at the same time. After error occusr everything
> >> works fine again.
> >>
> >> Wha can we do to prevent this happening? What causes these type of
> >> error messages?
> >>
> >>
> >> Server and connection strings are configured well, this is not error of
> >> miss spelled program or connectionstrings. Theese erros are caused
> >> somewhere else.
> >>
> >>
> >> t
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>
>|||Hello!
I just recieved erros and noticed that at the same time netstat reported
4000 connections to our sql-server. So it had it's limit. Now everyting
seems to go ok, becouse we raised upper limit to 20 000 connections. We all
so chekced our application to close connections more rapidly.
All tought, at the same time our other cluster server reported only 2000
connections and cluster has equal load balancing... How many connections
you have on on your client computers(IIS)? 4000 seems not to be so much...
We do have web hits somewhere in 3 000 000 per day and many of our sites are
dymamic ones... but, should there be so much open connctions?
t
"John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
viestissä:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
> Hi
> The article says that you are not using pooling, the solution may be to
> turn
> it on!
> You may want to check MDAC and SQL Server versions to see if there are any
> upgrades/patches that may help.
> You should also check to see if you have any network issues such as a
> faulty
> card/router or switch.
> John
> "TLehtinen" wrote:
>> Could my prblem be related to this?
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
>> We have one SQL-server and 2 separate iis server as cluster. I run
>> netstat
>> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp
>> session
>> open to our SQLserver. What shoud I do to prevent these erros happening?
>>
>> T
>> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
>> > The error is pretty clear. It could not find the server. Did you
>> > connect
>> > to SQL Server on this (new one) web page?
>> >
>> >
>> > "TLehtinen" <timo@.raa.to> wrote in message
>> > news:441564a0$0$7479$39db0f71@.news.song.fi...
>> >> Hello!
>> >>
>> >> alias?
>> >>
>> >> Everything has worked fine before we added this quite huge new web
>> >> page
>> >> thing... Now pages applications that have worked before are reporting
>> >> theese error. not all the time only about ten error / day (and we do
>> >> have
>> >> now quite a bit of transactions).
>> >>
>> >>
>> >> T
>> >>
>> >>
>> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
>> >> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
>> >> Hi
>> >> DSN, Did you configure an alias in Client Network Utility?
>> >>
>> >>
>> >> "TLehtinen" <timo@.raa.to> wrote in message
>> >> news:44155ecc$0$7489$39db0f71@.news.song.fi...
>> >> Our sql server started to this...
>> >> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
>> >> access denied.
>> >>
>> >> We added new, quite huge sql-based site to our iis farm and after
>> >> that
>> >> we get some times these error messages. Pages wich report these
>> >> erros
>> >> are fully working. Why do get these errors? In last day we have
>> >> recieved about ten of these erors. Eroros are coming from all kins
>> >> of
>> >> asp. and .net pages at the same time. After error occusr everything
>> >> works fine again.
>> >>
>> >> Wha can we do to prevent this happening? What causes these type of
>> >> error messages?
>> >>
>> >>
>> >> Server and connection strings are configured well, this is not error
>> >> of
>> >> miss spelled program or connectionstrings. Theese erros are caused
>> >> somewhere else.
>> >>
>> >>
>> >> t
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>|||Hi
You can use perfmon to monitor connections on your database, if you track
the counter you may see some trend. If you are using connection pooling then
you would need fewer connections and the time taken to connect would not
usually be an issue therefore 4000 connections could service a significantly
larger number of users. Each connection will use a small amount of memory on
the server so reducing the number of connections by using pooling is
benificial in many ways.
If connections are being left open then you would need to check your
application and IIS settings, matching up the connections from perfmon to you
web logs may help to identify what is happening.
John
"TLehtinen" wrote:
> Hello!
> I just recieved erros and noticed that at the same time netstat reported
> 4000 connections to our sql-server. So it had it's limit. Now everyting
> seems to go ok, becouse we raised upper limit to 20 000 connections. We all
> so chekced our application to close connections more rapidly.
> All tought, at the same time our other cluster server reported only 2000
> connections and cluster has equal load balancing... How many connections
> you have on on your client computers(IIS)? 4000 seems not to be so much...
> We do have web hits somewhere in 3 000 000 per day and many of our sites are
> dymamic ones... but, should there be so much open connctions?
>
> t
> "John Bell" <jbellnewsposts@.hotmail.com> kirjoitti
> viestissä:23572079-E9D7-4E22-A61C-ED6434F2E5B2@.microsoft.com...
> > Hi
> >
> > The article says that you are not using pooling, the solution may be to
> > turn
> > it on!
> >
> > You may want to check MDAC and SQL Server versions to see if there are any
> > upgrades/patches that may help.
> >
> > You should also check to see if you have any network issues such as a
> > faulty
> > card/router or switch.
> >
> > John
> >
> > "TLehtinen" wrote:
> >
> >> Could my prblem be related to this?
> >> http://support.microsoft.com/default.aspx?scid=kb;en-us;328476
> >>
> >> We have one SQL-server and 2 separate iis server as cluster. I run
> >> netstat
> >> and found out that both IIS machines have nearly 2000 TIME-WAIT tcp
> >> session
> >> open to our SQLserver. What shoud I do to prevent these erros happening?
> >>
> >>
> >> T
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> >> viestissä:e%23YXTvpRGHA.5924@.TK2MSFTNGP09.phx.gbl...
> >> > The error is pretty clear. It could not find the server. Did you
> >> > connect
> >> > to SQL Server on this (new one) web page?
> >> >
> >> >
> >> > "TLehtinen" <timo@.raa.to> wrote in message
> >> > news:441564a0$0$7479$39db0f71@.news.song.fi...
> >> >> Hello!
> >> >>
> >> >> alias?
> >> >>
> >> >> Everything has worked fine before we added this quite huge new web
> >> >> page
> >> >> thing... Now pages applications that have worked before are reporting
> >> >> theese error. not all the time only about ten error / day (and we do
> >> >> have
> >> >> now quite a bit of transactions).
> >> >>
> >> >>
> >> >> T
> >> >>
> >> >>
> >> >> "Uri Dimant" <urid@.iscar.co.il> kirjoitti
> >> >> viestissä:utVxLcpRGHA.5468@.TK2MSFTNGP14.phx.gbl...
> >> >> Hi
> >> >> DSN, Did you configure an alias in Client Network Utility?
> >> >>
> >> >>
> >> >> "TLehtinen" <timo@.raa.to> wrote in message
> >> >> news:44155ecc$0$7489$39db0f71@.news.song.fi...
> >> >> Our sql server started to this...
> >> >> [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
> >> >> access denied.
> >> >>
> >> >> We added new, quite huge sql-based site to our iis farm and after
> >> >> that
> >> >> we get some times these error messages. Pages wich report these
> >> >> erros
> >> >> are fully working. Why do get these errors? In last day we have
> >> >> recieved about ten of these erors. Eroros are coming from all kins
> >> >> of
> >> >> asp. and .net pages at the same time. After error occusr everything
> >> >> works fine again.
> >> >>
> >> >> Wha can we do to prevent this happening? What causes these type of
> >> >> error messages?
> >> >>
> >> >>
> >> >> Server and connection strings are configured well, this is not error
> >> >> of
> >> >> miss spelled program or connectionstrings. Theese erros are caused
> >> >> somewhere else.
> >> >>
> >> >>
> >> >> t
> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
> >>
> >>
>
>
Subscribe to:
Posts (Atom)