Hi
I am setting for the first time SQL to access a database. I can get the
Enterprise Manager and the Network Utility to see the databases (on 2
computers) but when I go to the program to access the databases, I get a
Connection error.
Strangely, I set up the exact replica on the server and it can only see
itself and not the other computer.
I have scanned newsgroups etc but cannot locate the problem.
Thanks
cds
Could you please post the exact error message? There are various types of
connectivity issues, and we need to see the error message, to be able to
understand what's happening.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"cds" <cdsaus2001@.yahoo.com.au> wrote in message
news:4140ccde$0$3711$afc38c87@.news.optusnet.com.au ...
> Hi
> I am setting for the first time SQL to access a database. I can get the
> Enterprise Manager and the Network Utility to see the databases (on 2
> computers) but when I go to the program to access the databases, I get a
> Connection error.
> Strangely, I set up the exact replica on the server and it can only see
> itself and not the other computer.
> I have scanned newsgroups etc but cannot locate the problem.
> Thanks
> cds
>
sql
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
new to ms sql having trouble with very basic commands
I'm more used to mysql, which i haven't coded for a while, but how do you show tables? or show databases you have available from the osql prompt?hello?|||Use the Information Schema
-- Show Tables
SELECT * FROM INFORMATION_SCHEMA.TABLES
besides the TABLES view of the Schema, there are many others
Here is a list of the views provided by SQL Server 2000
CHECK_CONSTRAINTS
Holds information about constraints in the database
COLUMN_DOMAIN_USAGE
Identifies which columns in which tables are user-defined datatypes
COLUMN_PRIVILEGES
Has one row for each column level permission granted to or by the current user
COLUMNS
Lists one row for each column in each table or view in the database
CONSTRAINT_COLUMN_USAGE
Lists one row for each column that has a constraint defined on it
CONSTRAINT_TABLE_USAGE
Lists one row for each table that has a constraint defined on it
DOMAIN_CONSTRAINTS
Lists the user-defined datatypes that have rules bound to them
DOMAINS
Lists the user-defined datatypes
KEY_COLUMN_USAGE
Lists one row for each column that's defined as a key
PARAMETERS
Lists one row for each parameter in a stored procedure or user-defined function
REFERENTIAL_CONSTRAINTS
Lists one row for each foreign constraint
ROUTINES
Lists one row for each stored procedure or user-defined function
ROUTINE_COLUMNS
Contains one row for each column returned by any table-valued functions
SCHEMATA
Contains one row for each database
TABLE_CONSTRAINTS
Lists one row for each constraint defined in the current database
TABLE_PRIVILEGES
Has one row for each table level permission granted to or by the current user
TABLES
Lists one row for each table or view in the current database
VIEW_COLUMN_USAGE
Lists one row for each column in a view including the base table of the column where possible
VIEW_TABLE_USAGE
Lists one row for each table used in a view
VIEWS
Lists one row for each view
-------------
You probably need to get hold of the SQL books online and the SQL tools; that will help quite a bit.|||thanks, thats been most helpful,
I'm going to bookmark this page :)
which sql tools are you reffering to?
New to MS SQL 2000
How do I backup databases, onto Veritas Backup? In the Enterprise Edition if
I expand the database server I could select all task, backup database and
select the destination.
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:58C5D3AA-D2FC-419E-B560-DD5703546B5A@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
if
> I expand the database server I could select all task, backup database and
> select the destination.
If you've got the Veritas netbackup SQL Server agent, you can configure
netback to capture all relevant databases. If not, you can use the SQL
Server backup database task wizard to backup your databases, then use
netbackup to backup these files.
Steve
I expand the database server I could select all task, backup database and
select the destination.
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:58C5D3AA-D2FC-419E-B560-DD5703546B5A@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
if
> I expand the database server I could select all task, backup database and
> select the destination.
If you've got the Veritas netbackup SQL Server agent, you can configure
netback to capture all relevant databases. If not, you can use the SQL
Server backup database task wizard to backup your databases, then use
netbackup to backup these files.
Steve
New Tables and Stored procedures are saving as System Tables
When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?
Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?
Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>
Monday, March 26, 2012
New Tables and Stored procedures are saving as System Tables
When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>
Friday, March 23, 2012
New SQL Server 2000 Install - Backup of Empty Database never compl
We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimber
mikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimber
mikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
New SQL Server 2000 Install - Backup of Empty Database never compl
We installed SQL Server 2000 SP3 and created a number of databases. However
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimbermikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
after a while the backups of these databases appear to hang forever and
required a reboot to clear. We then created a brand new empty database and
tried backing this up, but had the same problem.
Looking in the Event Viewer the following errors:
The device, \Device\Scsi\aarich1, did not respond within the timeout period.
We mirrored disks set, so we tried splitting the mirror both ways and
running the backups, but had the same problem.
Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
Error: 17805, Severity: 20, State: 3
Invalid buffer received from client.
18204 :
BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
create. Operating system error = 3(error not found).
When the backup hangs the CPU on the server drops off to zero and the
server becomes very unresponsive.
The Operating sytem is Windows 2003
Anyone any ideas?
Regards Mike Kimbermikekimber1,
That looks like a hardware problem to me. Have you changed the drivers
recently? If so, it could be a driver problem.
Also check that the location you are backing up to really exists. I know, an
obvious point, but there may be a type somewhere in your code.
Mark.
"mikekimber1" wrote:
> We installed SQL Server 2000 SP3 and created a number of databases. However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>|||Is drive W: a mapped drive? Try using UNC (i.e. \\server\share) instead of
mapped drives.
SCSI timeouts are significant enough and they could indicate that there is a
serious H/W problem. You should address them first and then check your
databases for corruption. Use CHECKPOINT, DBCC DROPCLEANBUFFERS and DBCC
CHECKDB to ensure that you are loading clean database pages from the disk.
Microsoft has several hotfixes for 17805 errors, but they are probably not
related to your backups.
Adrian
"mikekimber1" <mikekimber1@.discussions.microsoft.com> wrote in message
news:C73D1ABA-7494-440E-812A-365647D51A9D@.microsoft.com...
> We installed SQL Server 2000 SP3 and created a number of databases.
> However
> after a while the backups of these databases appear to hang forever and
> required a reboot to clear. We then created a brand new empty database and
> tried backing this up, but had the same problem.
> Looking in the Event Viewer the following errors:
> The device, \Device\Scsi\aarich1, did not respond within the timeout
> period.
> We mirrored disks set, so we tried splitting the mirror both ways and
> running the backups, but had the same problem.
> Looking at the Event Viewer messages for MSSQLSERVER there ar a number:
> Error: 17805, Severity: 20, State: 3
> Invalid buffer received from client.
> 18204 :
> BackupDiskFile::CreateMedia: Backup device 'w:\tmp\tst1.bak' failed to
> create. Operating system error = 3(error not found).
> When the backup hangs the CPU on the server drops off to zero and the
> server becomes very unresponsive.
> The Operating sytem is Windows 2003
> Anyone any ideas?
> Regards Mike Kimber
>
Wednesday, March 21, 2012
New server with SQL Server 2005 standard x64 crashing
We have new 64-bit server and SQL Server 2005 where we moved databases and
applications from a 32-bit server and SQL Server 2000. Since we have moved
the databases and application to the new server the server crashes and
reboots at an irregular interval. This reboot seems to always be proceeded
by the memory working set for SQL Server being paged out and available memory
going up by a large amount. This usually occurs hours before the server
crashes. The available memory stays high until the server crashes.
OS: Server 2003 Standard edition x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Hardware: HP ProLiant DL380 G5 with two Dual core cpu's 3000 MHz.
4 GB of memory
SQL Server Standard Edition (64-bit) Version: 9.00.3054.00
Using the performance monitory we can see the memory event that proceeds the
reboot and have ruled out some likely candidates like the Anti-virus and
backup software. The reboots occur anywhere from less the 24 hours apart to
4 days.
Does anyone know of any problems with SQL Server Standard x64 and memory
problems?
Any suggestions on how to identify if SQL Server is the problem or help
identifying some other service or driver as the cause of this problem?
--
MarkI have found it helpful to set the maximum memory setting in SQL Server to
less than the total available on the server. Based on the amount of memory
on the server this max amount would from 512 MB to 1024 MB less than the
maximum installed memory amount.
"masmith" wrote:
> We have new 64-bit server and SQL Server 2005 where we moved databases and
> applications from a 32-bit server and SQL Server 2000. Since we have moved
> the databases and application to the new server the server crashes and
> reboots at an irregular interval. This reboot seems to always be proceeded
> by the memory working set for SQL Server being paged out and available memory
> going up by a large amount. This usually occurs hours before the server
> crashes. The available memory stays high until the server crashes.
> OS: Server 2003 Standard edition x64 Edition
> Version 5.2.3790 Service Pack 2 Build 3790
> Hardware: HP ProLiant DL380 G5 with two Dual core cpu's 3000 MHz.
> 4 GB of memory
> SQL Server Standard Edition (64-bit) Version: 9.00.3054.00
> Using the performance monitory we can see the memory event that proceeds the
> reboot and have ruled out some likely candidates like the Anti-virus and
> backup software. The reboots occur anywhere from less the 24 hours apart to
> 4 days.
> Does anyone know of any problems with SQL Server Standard x64 and memory
> problems?
> Any suggestions on how to identify if SQL Server is the problem or help
> identifying some other service or driver as the cause of this problem?
> --
> Mark
>|||> Does anyone know of any problems with SQL Server Standard x64 and memory
> problems?
I dont have a link at hand, but I am pretty sure that SP2 for SQL Server
fixes this issue. We had the same issue from time to time, and we ended up
setting the min and max memory usage for the sql server 1 GB lower than the
total amount of memory. I searched google, and found a site saying, that the
SP2 would fix this issue.
If you haven't installed SP2, then you should take a look at the bugfixes
for that - and maybe you can permanent fix the problem with that.
/Sjang
applications from a 32-bit server and SQL Server 2000. Since we have moved
the databases and application to the new server the server crashes and
reboots at an irregular interval. This reboot seems to always be proceeded
by the memory working set for SQL Server being paged out and available memory
going up by a large amount. This usually occurs hours before the server
crashes. The available memory stays high until the server crashes.
OS: Server 2003 Standard edition x64 Edition
Version 5.2.3790 Service Pack 2 Build 3790
Hardware: HP ProLiant DL380 G5 with two Dual core cpu's 3000 MHz.
4 GB of memory
SQL Server Standard Edition (64-bit) Version: 9.00.3054.00
Using the performance monitory we can see the memory event that proceeds the
reboot and have ruled out some likely candidates like the Anti-virus and
backup software. The reboots occur anywhere from less the 24 hours apart to
4 days.
Does anyone know of any problems with SQL Server Standard x64 and memory
problems?
Any suggestions on how to identify if SQL Server is the problem or help
identifying some other service or driver as the cause of this problem?
--
MarkI have found it helpful to set the maximum memory setting in SQL Server to
less than the total available on the server. Based on the amount of memory
on the server this max amount would from 512 MB to 1024 MB less than the
maximum installed memory amount.
"masmith" wrote:
> We have new 64-bit server and SQL Server 2005 where we moved databases and
> applications from a 32-bit server and SQL Server 2000. Since we have moved
> the databases and application to the new server the server crashes and
> reboots at an irregular interval. This reboot seems to always be proceeded
> by the memory working set for SQL Server being paged out and available memory
> going up by a large amount. This usually occurs hours before the server
> crashes. The available memory stays high until the server crashes.
> OS: Server 2003 Standard edition x64 Edition
> Version 5.2.3790 Service Pack 2 Build 3790
> Hardware: HP ProLiant DL380 G5 with two Dual core cpu's 3000 MHz.
> 4 GB of memory
> SQL Server Standard Edition (64-bit) Version: 9.00.3054.00
> Using the performance monitory we can see the memory event that proceeds the
> reboot and have ruled out some likely candidates like the Anti-virus and
> backup software. The reboots occur anywhere from less the 24 hours apart to
> 4 days.
> Does anyone know of any problems with SQL Server Standard x64 and memory
> problems?
> Any suggestions on how to identify if SQL Server is the problem or help
> identifying some other service or driver as the cause of this problem?
> --
> Mark
>|||> Does anyone know of any problems with SQL Server Standard x64 and memory
> problems?
I dont have a link at hand, but I am pretty sure that SP2 for SQL Server
fixes this issue. We had the same issue from time to time, and we ended up
setting the min and max memory usage for the sql server 1 GB lower than the
total amount of memory. I searched google, and found a site saying, that the
SP2 would fix this issue.
If you haven't installed SP2, then you should take a look at the bugfixes
for that - and maybe you can permanent fix the problem with that.
/Sjang
Monday, March 12, 2012
New Production Server - Orphaned Users
Hi,
I have to move 5 databases from an old production server to a new production
server.
Old Production Server: Win 2K Server, SQL 2000 SP2
New Production Server:Win 2K3 Server, SQL 2000 SP4
I am creating the new server from scratch. Since I can't run SQL Server SP2
on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
I backed up system databases on Old Production server and restored them on
Staging and applied SP4 to the Staging server. Then I backed up system
databases on Staging and restored them on New Production Server since both o
f
them are now at the same Service Pack Level. I have to do it this way to
avoid applying SP4 directly to the old Production server.
Then I detached and attached the User databases on the New Production server
from Old Production server. My question is - would I still have orphaned
users? Thanks in advance.If I followed your chain correctly, you basically took your system databases
from a to b to c. Then
you take your user databases from a to c. This should mean that the logins e
xists with correct SID -
i.e., no orphaned users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new producti
on
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server SP
2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production serv
er
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.|||Hi
http://dimantdatabasesolutions.blog...on.
html
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new
> production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server
> SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
> of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production
> server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
I have to move 5 databases from an old production server to a new production
server.
Old Production Server: Win 2K Server, SQL 2000 SP2
New Production Server:Win 2K3 Server, SQL 2000 SP4
I am creating the new server from scratch. Since I can't run SQL Server SP2
on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
I backed up system databases on Old Production server and restored them on
Staging and applied SP4 to the Staging server. Then I backed up system
databases on Staging and restored them on New Production Server since both o
f
them are now at the same Service Pack Level. I have to do it this way to
avoid applying SP4 directly to the old Production server.
Then I detached and attached the User databases on the New Production server
from Old Production server. My question is - would I still have orphaned
users? Thanks in advance.If I followed your chain correctly, you basically took your system databases
from a to b to c. Then
you take your user databases from a to c. This should mean that the logins e
xists with correct SID -
i.e., no orphaned users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new producti
on
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server SP
2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production serv
er
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.|||Hi
http://dimantdatabasesolutions.blog...on.
html
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new
> production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server
> SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
> of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production
> server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
New Production Server - Orphaned Users
Hi,
I have to move 5 databases from an old production server to a new production
server.
Old Production Server: Win 2K Server, SQL 2000 SP2
New Production Server:Win 2K3 Server, SQL 2000 SP4
I am creating the new server from scratch. Since I can't run SQL Server SP2
on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
I backed up system databases on Old Production server and restored them on
Staging and applied SP4 to the Staging server. Then I backed up system
databases on Staging and restored them on New Production Server since both of
them are now at the same Service Pack Level. I have to do it this way to
avoid applying SP4 directly to the old Production server.
Then I detached and attached the User databases on the New Production server
from Old Production server. My question is - would I still have orphaned
users? Thanks in advance.
If I followed your chain correctly, you basically took your system databases from a to b to c. Then
you take your user databases from a to c. This should mean that the logins exists with correct SID -
i.e., no orphaned users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new
> production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server
> SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
> of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production
> server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
I have to move 5 databases from an old production server to a new production
server.
Old Production Server: Win 2K Server, SQL 2000 SP2
New Production Server:Win 2K3 Server, SQL 2000 SP4
I am creating the new server from scratch. Since I can't run SQL Server SP2
on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
I backed up system databases on Old Production server and restored them on
Staging and applied SP4 to the Staging server. Then I backed up system
databases on Staging and restored them on New Production Server since both of
them are now at the same Service Pack Level. I have to do it this way to
avoid applying SP4 directly to the old Production server.
Then I detached and attached the User databases on the New Production server
from Old Production server. My question is - would I still have orphaned
users? Thanks in advance.
If I followed your chain correctly, you basically took your system databases from a to b to c. Then
you take your user databases from a to c. This should mean that the logins exists with correct SID -
i.e., no orphaned users.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new
> production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server
> SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
> of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production
> server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
New Production Server - Orphaned Users
Hi,
I have to move 5 databases from an old production server to a new production
server.
Old Production Server: Win 2K Server, SQL 2000 SP2
New Production Server:Win 2K3 Server, SQL 2000 SP4
I am creating the new server from scratch. Since I can't run SQL Server SP2
on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
I backed up system databases on Old Production server and restored them on
Staging and applied SP4 to the Staging server. Then I backed up system
databases on Staging and restored them on New Production Server since both of
them are now at the same Service Pack Level. I have to do it this way to
avoid applying SP4 directly to the old Production server.
Then I detached and attached the User databases on the New Production server
from Old Production server. My question is - would I still have orphaned
users? Thanks in advance.If I followed your chain correctly, you basically took your system databases from a to b to c. Then
you take your user databases from a to c. This should mean that the logins exists with correct SID -
i.e., no orphaned users.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new
> production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server
> SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
> of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production
> server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
I have to move 5 databases from an old production server to a new production
server.
Old Production Server: Win 2K Server, SQL 2000 SP2
New Production Server:Win 2K3 Server, SQL 2000 SP4
I am creating the new server from scratch. Since I can't run SQL Server SP2
on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
I backed up system databases on Old Production server and restored them on
Staging and applied SP4 to the Staging server. Then I backed up system
databases on Staging and restored them on New Production Server since both of
them are now at the same Service Pack Level. I have to do it this way to
avoid applying SP4 directly to the old Production server.
Then I detached and attached the User databases on the New Production server
from Old Production server. My question is - would I still have orphaned
users? Thanks in advance.If I followed your chain correctly, you basically took your system databases from a to b to c. Then
you take your user databases from a to c. This should mean that the logins exists with correct SID -
i.e., no orphaned users.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:CDCD1A18-7031-4BA4-A08D-4363E296CD77@.microsoft.com...
> Hi,
> I have to move 5 databases from an old production server to a new
> production
> server.
> Old Production Server: Win 2K Server, SQL 2000 SP2
> New Production Server:Win 2K3 Server, SQL 2000 SP4
> I am creating the new server from scratch. Since I can't run SQL Server
> SP2
> on Win 2K3 OS, I have a Staging Server running Win 2K and SQL 2000 SP2.
> I backed up system databases on Old Production server and restored them on
> Staging and applied SP4 to the Staging server. Then I backed up system
> databases on Staging and restored them on New Production Server since both
> of
> them are now at the same Service Pack Level. I have to do it this way to
> avoid applying SP4 directly to the old Production server.
> Then I detached and attached the User databases on the New Production
> server
> from Old Production server. My question is - would I still have orphaned
> users? Thanks in advance.
New MS SQL Server
How do I backup databases, onto Veritas Backup? In the Enterprise Edition if
I expand the database server I could select all task, backup database and
select the destination. Which is the proper way to backup these databases.
You could get a Veritas agent that will be able to back up the database real
time. The problem with using the plain vanilla Backup EXEC is that it can't
back up a file that is in use. Alternatively, you can use a scheduled task
within Enterprise Manager to back up the database before you backup to tape.
We have ours set up this way and it works well...
Regards,
Hank Arnold
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:12671BDC-34F2-427E-80EF-753A96DC4818@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
> if
> I expand the database server I could select all task, backup database and
> select the destination. Which is the proper way to backup these databases.
I expand the database server I could select all task, backup database and
select the destination. Which is the proper way to backup these databases.
You could get a Veritas agent that will be able to back up the database real
time. The problem with using the plain vanilla Backup EXEC is that it can't
back up a file that is in use. Alternatively, you can use a scheduled task
within Enterprise Manager to back up the database before you backup to tape.
We have ours set up this way and it works well...
Regards,
Hank Arnold
"OSA" <OSA@.discussions.microsoft.com> wrote in message
news:12671BDC-34F2-427E-80EF-753A96DC4818@.microsoft.com...
> How do I backup databases, onto Veritas Backup? In the Enterprise Edition
> if
> I expand the database server I could select all task, backup database and
> select the destination. Which is the proper way to backup these databases.
Friday, March 9, 2012
New login permissions - too broad
Hi,
I created a new sql server login, but didn't assign it any permissions in any databases.
When I login with this new login, it logs into the master database, and is able to select tables from the system databases, such as master, msdb.
This seems very wrong to me. How can I turn these default permissions off for new logins? I thought it might have something to do with the guest account, but not sure how to best handle this.
Thanks
This is normal, default behavior, and it is considered a best practice to leave the behavior the way it is. If don't like it, you can always manually deny a specific user permissions to SELECT on specific tables in the master and msdb databases.
|||Ok.
Do you have any links where I can read up more on this? I'd like to find out exactly what permissions new logins have by default in these system databases.
I know that you can't disable the guest account in master or tempdb, as they are needed there.
Thanks
|||That is because, by default VIEW DEFINITION has been granted to public. If you want this and all subsequent logins to not be able to view metadata in the system databases, then execute the following:
REVOKE VIEW DEFINITION FROM public
GO
That will prevent them from seeing any metadata i.e. the databases will "appear" to be blank to them. They will still be able to do things like SELECT * FROM sys.databases. So, to prevent them from selecting from any of the DMVs, execute the following:
REVOKE SELECT ON DATABASE::master FROM public
Monday, February 20, 2012
New Database
I've configured my data and log files for SQL Server Databases on separate drives.
D:\MSSQL\Data\User_db_data\
E:\MSSQL\Log\User_db_logs\
however, everytime i create a new database it defaults to the old location. How can i create a database using enterprise manager to default to the new locations ?Enterprise Manager
..Right-click on server
...Click on Properties
...Database Settings tab
....Set Default data directory and Default log directory
D:\MSSQL\Data\User_db_data\
E:\MSSQL\Log\User_db_logs\
however, everytime i create a new database it defaults to the old location. How can i create a database using enterprise manager to default to the new locations ?Enterprise Manager
..Right-click on server
...Click on Properties
...Database Settings tab
....Set Default data directory and Default log directory
Subscribe to:
Posts (Atom)