I posted a message earlier, thinking I knew the how. However I didn't
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, ho
w
can I set it up?
Thanks for any help
GordonYou thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordonsql
Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts
Thursday, March 29, 2012
Database spanning multiple partitions.....help
I posted a message earlier, thinking I knew the how. However I didn't
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, how
can I set it up?
Thanks for any help
GordonYou thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
--
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordon
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, how
can I set it up?
Thanks for any help
GordonYou thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
--
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordon
Database spanning multiple partitions.....help
I posted a message earlier, thinking I knew the how. However I didn't
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, how
can I set it up?
Thanks for any help
Gordon
You thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordon
Cany anyone tell me how to change an existing database that exists on an H:
drive to span across another partition of say an I: .
My idea is this, the database is growing. We expanded the mirrored hard
drives from 70gb to 140gb. Now we have an additional amount of unused
space. I formatted the new partition and gave it an I: name.
I thought you could give the database a "Secondary" location. I thought
this meant that if the database filled up my H: drive, then it would
automatically start writing to the I: drive. Is this the case? If so, how
can I set it up?
Thanks for any help
Gordon
You thought wrong Gordon<g>. You would be better off to change the original
partition to the full size of the new drives and not have to worry about
splitting them up. Since the two partitions are on the same drive array
there is no performance gain in splitting them. You can add a new file to
the existing file group on the new partition but what will happen is this.
SQL Server uses a proportional fill algorithm to fill the files within the
filegroup. This is based on the amount of free space in each file. Ideally
you start with multiple files that are empty and sql server will fill them
equally as it inserts new rows. In your case if you simply add a new file
there will be some data written to the new file and some to the old. The
ratio depends on the amount of free space in each file. When filling in
disproportion like that it will cause more reads and writes to one file vs
spreading evenly over all of them. While you can do what you are after you
need to realize this is not going to work the way you thought. I recommend
you repartition the drive to make it one large partition instead of two
smaller ones.
Andrew J. Kelly SQL MVP
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:90E69280-63BF-4BA8-8F73-584C9534B06E@.microsoft.com...
>I posted a message earlier, thinking I knew the how. However I didn't
> Cany anyone tell me how to change an existing database that exists on an
> H:
> drive to span across another partition of say an I: .
> My idea is this, the database is growing. We expanded the mirrored hard
> drives from 70gb to 140gb. Now we have an additional amount of unused
> space. I formatted the new partition and gave it an I: name.
> I thought you could give the database a "Secondary" location. I thought
> this meant that if the database filled up my H: drive, then it would
> automatically start writing to the I: drive. Is this the case? If so,
> how
> can I set it up?
> Thanks for any help
> Gordon
Thursday, March 22, 2012
Database size
Is there any way I can join all the information produced by this into a single output rather than multiple outputs
EXEC sp_MSforeachtable @.command1=" EXEC sp_spaceused '?'"
Like this
Listing_Images 43 16 KB 8 KB 8 KB 0 KB
User 3 16 KB 8 KB 8 KB 0 KB
I want to fill a gridview in ASP but don't know how to deal with data produced through multiple queriesLook at the code of sp_spaceused and build what you need
select name=object_name(i.id)
,rows=sum(case when indid<2 then rows else 0 end)
,reservKB=convert(int,sum(reserved)/.125)
,dataKB=convert(int,sum(case
when indid<2 then dpages
when indid=255 then isnull(used,0)
else 0 end)/.125)
,indexKB=convert(int,(sum(used)-sum(case
when indid<2 then dpages
when indid=255 then isnull(used,0)
else 0 end))/.125)
,unusedKB=convert(int,(sum(reserved)-sum(used))/.125)
from sysindexes i join sysobjects o on i.id=o.id
where indid in (0, 1, 255)
and o.xtype='U'
-- and id in (object_id('Listing_Images'), object_id('User'))
group by i.id
else
create table #t1 (Table_name varchar(128), Records varchar(11)
,reservedKB varchar(18), dataKB varchar(18), index_sizeKB varchar(18)
,unused varchar(18))
insert #t1 exec sp_spaceused Listing_Images
insert #t1 exec sp_spaceused User
select * from #t1
drop table #t1|||I see what you mean, Ive done the same thing at an application level where it executes and inserts each result as a row,
While myReader.Read
Dim newRow As DataRow = dt.NewRow()
newRow("name") = myReader("name").ToString
newRow("rows") = myReader("rows").ToString
newRow("data") = myReader("data").ToString
newRow("index_size") = myReader("index_size").ToString
newRow("reserved") = myReader("reserved").ToString
newRow("unused") = myReader("unused").ToString
dt.Rows.Add(newRow)
Total_IndexSize = Total_IndexSize + Convert.ToInt32(myReader("index_size").ToString().Split(SpaceDelimiter)(0))
Total_DataSize = Total_DataSize + Convert.ToInt32(myReader("data").ToString().Split(SpaceDelimiter)(0))
Total_ReservedSpace = Total_ReservedSpace + Convert.ToInt32(myReader("reserved").ToString().Split(SpaceDelimiter)(0))
Total_UnusedSpace = Total_UnusedSpace + Convert.ToInt32(myReader("unused").ToString().Split(SpaceDelimiter)(0))
End While|||Updated my previous post
EXEC sp_MSforeachtable @.command1=" EXEC sp_spaceused '?'"
Like this
Listing_Images 43 16 KB 8 KB 8 KB 0 KB
User 3 16 KB 8 KB 8 KB 0 KB
I want to fill a gridview in ASP but don't know how to deal with data produced through multiple queriesLook at the code of sp_spaceused and build what you need
select name=object_name(i.id)
,rows=sum(case when indid<2 then rows else 0 end)
,reservKB=convert(int,sum(reserved)/.125)
,dataKB=convert(int,sum(case
when indid<2 then dpages
when indid=255 then isnull(used,0)
else 0 end)/.125)
,indexKB=convert(int,(sum(used)-sum(case
when indid<2 then dpages
when indid=255 then isnull(used,0)
else 0 end))/.125)
,unusedKB=convert(int,(sum(reserved)-sum(used))/.125)
from sysindexes i join sysobjects o on i.id=o.id
where indid in (0, 1, 255)
and o.xtype='U'
-- and id in (object_id('Listing_Images'), object_id('User'))
group by i.id
else
create table #t1 (Table_name varchar(128), Records varchar(11)
,reservedKB varchar(18), dataKB varchar(18), index_sizeKB varchar(18)
,unused varchar(18))
insert #t1 exec sp_spaceused Listing_Images
insert #t1 exec sp_spaceused User
select * from #t1
drop table #t1|||I see what you mean, Ive done the same thing at an application level where it executes and inserts each result as a row,
While myReader.Read
Dim newRow As DataRow = dt.NewRow()
newRow("name") = myReader("name").ToString
newRow("rows") = myReader("rows").ToString
newRow("data") = myReader("data").ToString
newRow("index_size") = myReader("index_size").ToString
newRow("reserved") = myReader("reserved").ToString
newRow("unused") = myReader("unused").ToString
dt.Rows.Add(newRow)
Total_IndexSize = Total_IndexSize + Convert.ToInt32(myReader("index_size").ToString().Split(SpaceDelimiter)(0))
Total_DataSize = Total_DataSize + Convert.ToInt32(myReader("data").ToString().Split(SpaceDelimiter)(0))
Total_ReservedSpace = Total_ReservedSpace + Convert.ToInt32(myReader("reserved").ToString().Split(SpaceDelimiter)(0))
Total_UnusedSpace = Total_UnusedSpace + Convert.ToInt32(myReader("unused").ToString().Split(SpaceDelimiter)(0))
End While|||Updated my previous post
Wednesday, March 21, 2012
Database Service Won't Start
We have multiple instances at Windows 2000 Advanced Server. One default
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.Hi,
What you could do is first backup all the databases currently running on your
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet :)
Hope this helps :)
--
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via SQLMonster.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on your
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Same
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet :)
> Hope this helps :)
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.Hi,
What you could do is first backup all the databases currently running on your
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet :)
Hope this helps :)
--
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via SQLMonster.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on your
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Same
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet :)
> Hope this helps :)
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200609/1
Database Service Won't Start
We have multiple instances at Windows 2000 Advanced Server. One default
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.Hi,
What you could do is first backup all the databases currently running on you
r
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet
Hope this helps
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via droptable.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on y
our
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Sa
me
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet
> Hope this helps
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.Hi,
What you could do is first backup all the databases currently running on you
r
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet
Hope this helps
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200609/1|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via droptable.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on y
our
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Sa
me
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet
> Hope this helps
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200609/1
Database Service Won't Start
We have multiple instances at Windows 2000 Advanced Server. One default
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.
Hi,
What you could do is first backup all the databases currently running on your
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet
Hope this helps
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via droptable.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on your
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Same
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet
> Hope this helps
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200609/1
database is Enterprise Edition and another two instances are Developer
Editions. They have been running fine until recently.
We can start ANY ONE instance from the EM, only ONE. None of any other
services can be started anymore. Not even SQL Agent. We have to start
rest of other database services and sql agent from command line.
When we try to connect the SQL Server or Agent from the EM, we got
"Error 1053 - (The service did not respond to the start or control
request in a timely fashion) occured while performing this service
operation on the MSSQL service."
Thought maybe some corrpution to currnet databases, DLL etc. Installed
another new instance on the server, got the same error message.
Verified Microsoft KB article 322140, does not seem apply to us.
Any opinion is appreciated.
Hi,
What you could do is first backup all the databases currently running on your
server.
1) Please never install Enterprise Edition and Developer Edition on the Same
Drive.
2) Install both of them on separate Drives.
3) Setup the Client and Server Tools for only the Enterprise Edition.
4) And when installing the Developer Editions, install only the Server and
not the client tools.
I faced a similar problem when I installed SQL Server 2000 and SQL server
2005 express on the same drive.
So when I installed it on separate drives, no problems faced yet
Hope this helps
Best Regards
Anil Mahadev
http://anilm001.myfreewebs.net/index2.php
http://www.db2india.org
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200609/1
|||Thanks for your response.
But it works fine before, until 2 weeks ago. Now I can start them from
command line.
Thanks.
DB2DOTNETCZAR via droptable.com wrote:
> Hi,
> What you could do is first backup all the databases currently running on your
> server.
> 1) Please never install Enterprise Edition and Developer Edition on the Same
> Drive.
> 2) Install both of them on separate Drives.
> 3) Setup the Client and Server Tools for only the Enterprise Edition.
> 4) And when installing the Developer Editions, install only the Server and
> not the client tools.
> I faced a similar problem when I installed SQL Server 2000 and SQL server
> 2005 express on the same drive.
> So when I installed it on separate drives, no problems faced yet
> Hope this helps
> --
> Best Regards
> Anil Mahadev
> http://anilm001.myfreewebs.net/index2.php
> http://www.db2india.org
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200609/1
Monday, March 19, 2012
Database Schema Designing
Case 1:
A company is involved into e-commerce..hosting multiple websites for different products.
CAse 2:
The above scenario could also be implemented with a single site having multiple products for sale.
For Case 2 one would go for a single database for all the products.
While for CAse 1 ,a separate Database is developed for each Site.
What I fill is CAse2 is a more appropriate choice even if we have multiple sites for different products.
This would help us in rapid development of any ecommerce site...
ANd better ERP management for the Company.
I would appreciate some expert guidelines for the above scenario
Thanx in Advance
Warm Regards
GirijaUnless there is something to do with IIS (or whatever web server you are using) that would benefit from different databases, I would have all the data in one database, and have more than web site access the database.
Having all of your data in one database would allow provide much better information (not just straight forward data entry, but the trends you can find through relations, etc.) and make it easier to manage...in my oppinion. However, make sure that if you use one database (and these are high volume web sites) that you are writing your code carefully to avoid unneccessary record locks.
Just my oppinion...
A company is involved into e-commerce..hosting multiple websites for different products.
CAse 2:
The above scenario could also be implemented with a single site having multiple products for sale.
For Case 2 one would go for a single database for all the products.
While for CAse 1 ,a separate Database is developed for each Site.
What I fill is CAse2 is a more appropriate choice even if we have multiple sites for different products.
This would help us in rapid development of any ecommerce site...
ANd better ERP management for the Company.
I would appreciate some expert guidelines for the above scenario
Thanx in Advance
Warm Regards
GirijaUnless there is something to do with IIS (or whatever web server you are using) that would benefit from different databases, I would have all the data in one database, and have more than web site access the database.
Having all of your data in one database would allow provide much better information (not just straight forward data entry, but the trends you can find through relations, etc.) and make it easier to manage...in my oppinion. However, make sure that if you use one database (and these are high volume web sites) that you are writing your code carefully to avoid unneccessary record locks.
Just my oppinion...
Sunday, March 11, 2012
Database restores using Enterprise Manager
Good afternoon. I am using MS SQL 2K and was wondering if it is possible to restore multiple back-up files (database and transaction logs) to a database, if you haven't created a back-up set, using Enterprise Manager. I know that you can write T-SQL to first restore the back-up file and each of the transaction log files, except the last one, with the option of norecovery, and then the last transaction log file, with recovery. Any help would be greatly appreciated. Thank you.
Chris
That the way, restoring the database files one by one.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Thursday, March 8, 2012
Database restore questions
Hello Gurus,
I wanted to restore a database to multiple data files from backup which has got only few data files. Is it possible do this?
Example : Backup has 4 data files and I need to restore it to 8 data files.
Thanks
Subbu
No. A restore is a 1 for 1 operation. After you are done restoring, you can then add more files/filegroups and move data around if you want to.Database Restore question.
Hi,
I have one 90 GB datafile. Is there any way i can restore it to multiple datafiles?
Appreciate your helpYes
Restore it first, then use alter table to create new files
No
There isn't an easier way
I have one 90 GB datafile. Is there any way i can restore it to multiple datafiles?
Appreciate your helpYes
Restore it first, then use alter table to create new files
No
There isn't an easier way
Saturday, February 25, 2012
Database Replication
We currently have multiple tables being replicated using merge snapshot. In
the majority of cases the whole table is required or a row filter is applied
based on a value in that table.
We have two sets of identical databases, except for their data, that
replicate their data from a backend database towards a frontend database.
The problem is that for database 1, all the necesarry data is replicated,
but for the other database 2, which has the same parameters, replication
settings etc.. certain blob files containing pdf files will not replicate
from the backend database towards the frontend database.
The replication only replicates the .doc files, so the replication is
partially done for the table that contains the .doc and .pdf files.
When I run the initial snapshot, the pdf files are replicated fine, but when
the agent runs, the pdf files are not replicated.
Has anyone encountered similar problems? Can someone identify what is
causing the problem by explaining why this is not working? My first idea
would be the sheduling the snapshot to run every night, instead of the
agent, but this would be a far from ideal solution and one that I would only
consider as a last choice. I fail to understand why the replication works
for database 1, but not for database 2.
parnold8104@.hotmail.com
can you run this command sp_configure 'max text repl size'
and then figure out what your maximum data size in the blob columns you are
replicating?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> We currently have multiple tables being replicated using merge snapshot.
In
> the majority of cases the whole table is required or a row filter is
applied
> based on a value in that table.
> We have two sets of identical databases, except for their data, that
> replicate their data from a backend database towards a frontend database.
> The problem is that for database 1, all the necesarry data is replicated,
> but for the other database 2, which has the same parameters, replication
> settings etc.. certain blob files containing pdf files will not replicate
> from the backend database towards the frontend database.
> The replication only replicates the .doc files, so the replication is
> partially done for the table that contains the .doc and .pdf files.
> When I run the initial snapshot, the pdf files are replicated fine, but
when
> the agent runs, the pdf files are not replicated.
> Has anyone encountered similar problems? Can someone identify what is
> causing the problem by explaining why this is not working? My first idea
> would be the sheduling the snapshot to run every night, instead of the
> agent, but this would be a far from ideal solution and one that I would
only
> consider as a last choice. I fail to understand why the replication works
> for database 1, but not for database 2.
> parnold8104@.hotmail.com
>
>
|||Hi Hilary,
thanks for replying, when I run the command sp_configure I get these values:
max text repl size (B) minimum: 0
maximum: 2147483647
config_value: 52428800
run_value: 52428800
I am not sure if the data size is the problem, because the .doc files that
are replicated are larger in size then the pdf files. Another thing I
noticed is that I can replicate my pdf records in the 'faulty' database, but
only if I do an update of the record in my backend database. So when the
record is 'new' the replication skips it, but if I update the record, by for
example changing a letter in a description field, the replication picks up
the record and pushes it towards the frontend database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> can you run this command sp_configure 'max text repl size'
> and then figure out what your maximum data size in the blob columns you
are[vbcol=seagreen]
> replicating?
> "Peter A" <parnold8104@.hotmail.com> wrote in message
> news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In
> applied
database.[vbcol=seagreen]
replicated,[vbcol=seagreen]
replicate[vbcol=seagreen]
> when
> only
works
>
|||can you post your schema and publication scripts here?
What version of SQL are you using? SP?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23whCdr3JEHA.232@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for replying, when I run the command sp_configure I get these
values:
> max text repl size (B) minimum: 0
> maximum: 2147483647
> config_value: 52428800
> run_value: 52428800
> I am not sure if the data size is the problem, because the .doc files that
> are replicated are larger in size then the pdf files. Another thing I
> noticed is that I can replicate my pdf records in the 'faulty' database,
but
> only if I do an update of the record in my backend database. So when the
> record is 'new' the replication skips it, but if I update the record, by
for[vbcol=seagreen]
> example changing a letter in a description field, the replication picks up
> the record and pushes it towards the frontend database.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> are
snapshot.[vbcol=seagreen]
> database.
> replicated,
replication[vbcol=seagreen]
> replicate
but[vbcol=seagreen]
idea[vbcol=seagreen]
would
> works
>
the majority of cases the whole table is required or a row filter is applied
based on a value in that table.
We have two sets of identical databases, except for their data, that
replicate their data from a backend database towards a frontend database.
The problem is that for database 1, all the necesarry data is replicated,
but for the other database 2, which has the same parameters, replication
settings etc.. certain blob files containing pdf files will not replicate
from the backend database towards the frontend database.
The replication only replicates the .doc files, so the replication is
partially done for the table that contains the .doc and .pdf files.
When I run the initial snapshot, the pdf files are replicated fine, but when
the agent runs, the pdf files are not replicated.
Has anyone encountered similar problems? Can someone identify what is
causing the problem by explaining why this is not working? My first idea
would be the sheduling the snapshot to run every night, instead of the
agent, but this would be a far from ideal solution and one that I would only
consider as a last choice. I fail to understand why the replication works
for database 1, but not for database 2.
parnold8104@.hotmail.com
can you run this command sp_configure 'max text repl size'
and then figure out what your maximum data size in the blob columns you are
replicating?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> We currently have multiple tables being replicated using merge snapshot.
In
> the majority of cases the whole table is required or a row filter is
applied
> based on a value in that table.
> We have two sets of identical databases, except for their data, that
> replicate their data from a backend database towards a frontend database.
> The problem is that for database 1, all the necesarry data is replicated,
> but for the other database 2, which has the same parameters, replication
> settings etc.. certain blob files containing pdf files will not replicate
> from the backend database towards the frontend database.
> The replication only replicates the .doc files, so the replication is
> partially done for the table that contains the .doc and .pdf files.
> When I run the initial snapshot, the pdf files are replicated fine, but
when
> the agent runs, the pdf files are not replicated.
> Has anyone encountered similar problems? Can someone identify what is
> causing the problem by explaining why this is not working? My first idea
> would be the sheduling the snapshot to run every night, instead of the
> agent, but this would be a far from ideal solution and one that I would
only
> consider as a last choice. I fail to understand why the replication works
> for database 1, but not for database 2.
> parnold8104@.hotmail.com
>
>
|||Hi Hilary,
thanks for replying, when I run the command sp_configure I get these values:
max text repl size (B) minimum: 0
maximum: 2147483647
config_value: 52428800
run_value: 52428800
I am not sure if the data size is the problem, because the .doc files that
are replicated are larger in size then the pdf files. Another thing I
noticed is that I can replicate my pdf records in the 'faulty' database, but
only if I do an update of the record in my backend database. So when the
record is 'new' the replication skips it, but if I update the record, by for
example changing a letter in a description field, the replication picks up
the record and pushes it towards the frontend database.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> can you run this command sp_configure 'max text repl size'
> and then figure out what your maximum data size in the blob columns you
are[vbcol=seagreen]
> replicating?
> "Peter A" <parnold8104@.hotmail.com> wrote in message
> news:%23sFpoRuJEHA.2456@.TK2MSFTNGP12.phx.gbl...
> In
> applied
database.[vbcol=seagreen]
replicated,[vbcol=seagreen]
replicate[vbcol=seagreen]
> when
> only
works
>
|||can you post your schema and publication scripts here?
What version of SQL are you using? SP?
"Peter A" <parnold8104@.hotmail.com> wrote in message
news:%23whCdr3JEHA.232@.TK2MSFTNGP12.phx.gbl...
> Hi Hilary,
> thanks for replying, when I run the command sp_configure I get these
values:
> max text repl size (B) minimum: 0
> maximum: 2147483647
> config_value: 52428800
> run_value: 52428800
> I am not sure if the data size is the problem, because the .doc files that
> are replicated are larger in size then the pdf files. Another thing I
> noticed is that I can replicate my pdf records in the 'faulty' database,
but
> only if I do an update of the record in my backend database. So when the
> record is 'new' the replication skips it, but if I update the record, by
for[vbcol=seagreen]
> example changing a letter in a description field, the replication picks up
> the record and pushes it towards the frontend database.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:eJKebhuJEHA.3592@.TK2MSFTNGP09.phx.gbl...
> are
snapshot.[vbcol=seagreen]
> database.
> replicated,
replication[vbcol=seagreen]
> replicate
but[vbcol=seagreen]
idea[vbcol=seagreen]
would
> works
>
Subscribe to:
Posts (Atom)