Database Size...HELP

I am new to SQL so please forgive. I will try my best to describe my
problem. I really need a solution.
I have SQL 2000 with SP4. When I go into Enterprinse manager and right click
on my database it says that the size of the database is 62259.96 and space
available is 0.00.
There are some functions that are not working for my users and I believe
that it is due to there being no space available for the database. There is
265 GB of disk space free on the server and no disk space quotas in place.
Could someone please tell me how to make space available to my database?
Please let me know if more information is needed before anyone can give me
possible solutions.> There are some functions that are not working for my users and I believe that it is due to there
> being no space available for the database.
What do you base this on? Have the users write down the error message, also lock in the eventlog, as
SQL Server would log 1105 or 9002 messages when out of space.
In SQL Server, we pre-allocate storage. SQL Server can autogrow database files, but that comes with
a cost and sometimes it isn't fast enough so you still get out of space messages. You can expand a
database file using ALTER DATABASE command (of right-click in EM, properties).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Vee" <john.vee@.ole.net> wrote in message news:OXhJubjYGHA.4580@.TK2MSFTNGP03.phx.gbl...
>I am new to SQL so please forgive. I will try my best to describe my problem. I really need a
>solution.
> I have SQL 2000 with SP4. When I go into Enterprinse manager and right click on my database it
> says that the size of the database is 62259.96 and space available is 0.00.
> There are some functions that are not working for my users and I believe that it is due to there
> being no space available for the database. There is 265 GB of disk space free on the server and no
> disk space quotas in place.
> Could someone please tell me how to make space available to my database?
> Please let me know if more information is needed before anyone can give me possible solutions.
>

Database Size...HELP

I am new to SQL so please forgive. I will try my best to describe my
problem. I really need a solution.
I have SQL 2000 with SP4. When I go into Enterprinse manager and right click
on my database it says that the size of the database is 62259.96 and space
available is 0.00.
There are some functions that are not working for my users and I believe
that it is due to there being no space available for the database. There is
265 GB of disk space free on the server and no disk space quotas in place.
Could someone please tell me how to make space available to my database?
Please let me know if more information is needed before anyone can give me
possible solutions.> There are some functions that are not working for my users and I believe that it is due to
there
> being no space available for the database.
What do you base this on? Have the users write down the error message, also
lock in the eventlog, as
SQL Server would log 1105 or 9002 messages when out of space.
In SQL Server, we pre-allocate storage. SQL Server can autogrow database fil
es, but that comes with
a cost and sometimes it isn't fast enough so you still get out of space mess
ages. You can expand a
database file using ALTER DATABASE command (of right-click in EM, properties
).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Vee" <john.vee@.ole.net> wrote in message news:OXhJubjYGHA.4580@.TK2MSFTNGP03.phx.gbl...

>I am new to SQL so please forgive. I will try my best to describe my proble
m. I really need a
>solution.
> I have SQL 2000 with SP4. When I go into Enterprinse manager and right cli
ck on my database it
> says that the size of the database is 62259.96 and space available is 0.00
.
> There are some functions that are not working for my users and I believe t
hat it is due to there
> being no space available for the database. There is 265 GB of disk space f
ree on the server and no
> disk space quotas in place.
> Could someone please tell me how to make space available to my database?
> Please let me know if more information is needed before anyone can give me
possible solutions.
>

Database Size Bloating after SP4.

Has anyone had any issues with a database doubling or tripling in size after
SP4 is installed? We have several databases that are structurely the same
however certain DB's have grown enormously in size without a large increase
in data. Whenever we try to shrink the Database it won't shrink to the
approximate size we are expecting. For example...
We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
The last one I understand.. it is where it should be.. However the first two
don't make any sense..
Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
known problems that would cause this? Thanks for your help in advance!
You didn't say what sizes you log files are.
Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
it's off, then you log files could be growning and be the cause of your
large database sizes.
It's just a thought.
"Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> Has anyone had any issues with a database doubling or tripling in size
> after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large
> increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first
> two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> Any
> known problems that would cause this? Thanks for your help in advance!
|||Francis,
I have ran the shrinkfile however it returns a limited result. Such that I
can get the file down from 12GB to 10GB however it still seems like the
unused space that is reported isn't ever freed up..
"Francis" wrote:
[vbcol=seagreen]
> I have had SP4 running on 4 production servers and a couple of test servers
> for several months now without any of the issues that you mention. If you
> need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
> SHRINKFILE isn't working then try this script:
> http://www.sqlservercentral.com/scri...p?scriptid=666
> I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
> can control which file I want to schrink.
> Have you noticed that the database grows after doing a DBCC REINDEX? If so
> you may need extra free space, although 8-14 G seems excessive.
> "Sam Davis" wrote:
|||The log files are small 100-200 MB...
"Joe D" wrote:

> You didn't say what sizes you log files are.
> Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
> it's off, then you log files could be growning and be the cause of your
> large database sizes.
> It's just a thought.
>
> "Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
> news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
>
>

Database Size Bloating after SP4.

Has anyone had any issues with a database doubling or tripling in size after
SP4 is installed? We have several databases that are structurely the same
however certain DB's have grown enormously in size without a large increase
in data. Whenever we try to shrink the Database it won't shrink to the
approximate size we are expecting. For example...
We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
The last one I understand.. it is where it should be.. However the first two
don't make any sense..
Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
known problems that would cause this? Thanks for your help in advance!You didn't say what sizes you log files are.
Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
it's off, then you log files could be growning and be the cause of your
large database sizes.
It's just a thought.
"Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> Has anyone had any issues with a database doubling or tripling in size
> after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large
> increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first
> two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> Any
> known problems that would cause this? Thanks for your help in advance!|||Francis,
I have ran the shrinkfile however it returns a limited result. Such that I
can get the file down from 12GB to 10GB however it still seems like the
unused space that is reported isn't ever freed up..
"Francis" wrote:
[vbcol=seagreen]
> I have had SP4 running on 4 production servers and a couple of test server
s
> for several months now without any of the issues that you mention. If you
> need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
> SHRINKFILE isn't working then try this script:
> http://www.sqlservercentral.com/scr...sp?scriptid=666
> I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
> can control which file I want to schrink.
> Have you noticed that the database grows after doing a DBCC REINDEX? If s
o
> you may need extra free space, although 8-14 G seems excessive.
> "Sam Davis" wrote:
>|||The log files are small 100-200 MB...
"Joe D" wrote:

> You didn't say what sizes you log files are.
> Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off?
If
> it's off, then you log files could be growning and be the cause of your
> large database sizes.
> It's just a thought.
>
> "Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
> news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
>
>sql

Database Size Bloating after SP4.

Has anyone had any issues with a database doubling or tripling in size after
SP4 is installed? We have several databases that are structurely the same
however certain DB's have grown enormously in size without a large increase
in data. Whenever we try to shrink the Database it won't shrink to the
approximate size we are expecting. For example...
We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
The last one I understand.. it is where it should be.. However the first two
don't make any sense..
Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
known problems that would cause this? Thanks for your help in advance!I have had SP4 running on 4 production servers and a couple of test servers
for several months now without any of the issues that you mention. If you
need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
SHRINKFILE isn't working then try this script:
http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=666
I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
can control which file I want to schrink.
Have you noticed that the database grows after doing a DBCC REINDEX? If so
you may need extra free space, although 8-14 G seems excessive.
"Sam Davis" wrote:
> Has anyone had any issues with a database doubling or tripling in size after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
> known problems that would cause this? Thanks for your help in advance!|||You didn't say what sizes you log files are.
Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
it's off, then you log files could be growning and be the cause of your
large database sizes.
It's just a thought.
"Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> Has anyone had any issues with a database doubling or tripling in size
> after
> SP4 is installed? We have several databases that are structurely the same
> however certain DB's have grown enormously in size without a large
> increase
> in data. Whenever we try to shrink the Database it won't shrink to the
> approximate size we are expecting. For example...
> We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> The last one I understand.. it is where it should be.. However the first
> two
> don't make any sense..
> Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> Any
> known problems that would cause this? Thanks for your help in advance!|||Francis,
I have ran the shrinkfile however it returns a limited result. Such that I
can get the file down from 12GB to 10GB however it still seems like the
unused space that is reported isn't ever freed up..
"Francis" wrote:
> I have had SP4 running on 4 production servers and a couple of test servers
> for several months now without any of the issues that you mention. If you
> need to shrink your database and EM or the DBCC SHRINKDATABASE of DBCC
> SHRINKFILE isn't working then try this script:
> http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=666
> I usuall find the DBCC SHRINKFILE works better than SHRINKDATABASE since I
> can control which file I want to schrink.
> Have you noticed that the database grows after doing a DBCC REINDEX? If so
> you may need extra free space, although 8-14 G seems excessive.
> "Sam Davis" wrote:
> > Has anyone had any issues with a database doubling or tripling in size after
> > SP4 is installed? We have several databases that are structurely the same
> > however certain DB's have grown enormously in size without a large increase
> > in data. Whenever we try to shrink the Database it won't shrink to the
> > approximate size we are expecting. For example...
> >
> > We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> > We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> > We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> >
> > The last one I understand.. it is where it should be.. However the first two
> > don't make any sense..
> >
> > Can someone give me an idea.. I have tried defragging, shrinking.. etc.. Any
> > known problems that would cause this? Thanks for your help in advance!|||The log files are small 100-200 MB...
"Joe D" wrote:
> You didn't say what sizes you log files are.
> Have you looked at the DB Option 'trunc. log on chkpt.'? Is it on or off? If
> it's off, then you log files could be growning and be the cause of your
> large database sizes.
> It's just a thought.
>
> "Sam Davis" <SamDavis@.discussions.microsoft.com> wrote in message
> news:CB4F5891-67A2-4535-BE1A-DAA3C0FE4FB1@.microsoft.com...
> > Has anyone had any issues with a database doubling or tripling in size
> > after
> > SP4 is installed? We have several databases that are structurely the same
> > however certain DB's have grown enormously in size without a large
> > increase
> > in data. Whenever we try to shrink the Database it won't shrink to the
> > approximate size we are expecting. For example...
> >
> > We have a DB that is 12.3GB with 3GB in data, 1 GB in indexes.
> > We have a DB that is 17.1GB with 3GB in data, 740MB in indexes.
> > We have a DB that is 7.8 GB with 4.6GB in data, 2.4GB in indexes.
> >
> > The last one I understand.. it is where it should be.. However the first
> > two
> > don't make any sense..
> >
> > Can someone give me an idea.. I have tried defragging, shrinking.. etc..
> > Any
> > known problems that would cause this? Thanks for your help in advance!
>
>

Database Size and SP4

As far as I know, the database size contains both Index and actual data. Is
there any command to find out the exact space occupied used by Index and
Data ? The SQL Server 2000 Database is of 5GB allocated with data (I think
it is data + Index) of 1.5GB.
Some fellow has mentioned that applying SP4 will get a better management of
memory. Is it correct ?
ThanksRobert
sp_helpdb 'pubs'
use pubs
go
sp_spaceused
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
> As far as I know, the database size contains both Index and actual data.
> Is there any command to find out the exact space occupied used by Index
> and Data ? The SQL Server 2000 Database is of 5GB allocated with data (I
> think it is data + Index) of 1.5GB.
> Some fellow has mentioned that applying SP4 will get a better management
> of memory. Is it correct ?
> Thanks
>|||Dear Uri,
Thank you for your advice.
s there any general rule of thumb about the ratio between Index and Data ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
> Robert
> sp_helpdb 'pubs'
> use pubs
> go
> sp_spaceused
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual data.
>> Is there any command to find out the exact space occupied used by Index
>> and Data ? The SQL Server 2000 Database is of 5GB allocated with data (I
>> think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better management
>> of memory. Is it correct ?
>> Thanks
>|||Robert
Keep your indexes narrow as you can.
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for your advice.
> s there any general rule of thumb about the ratio between Index and Data ?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual data.
>> Is there any command to find out the exact space occupied used by Index
>> and Data ? The SQL Server 2000 Database is of 5GB allocated with data
>> (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better management
>> of memory. Is it correct ?
>> Thanks
>>
>|||Dear Uri,
Can you elaborate what does index narrow means ?
Thanking you in anticipation.
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
> Robert
> Keep your indexes narrow as you can.
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for your advice.
>> s there any general rule of thumb about the ratio between Index and Data
>> ?
>> Thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual
>> data. Is there any command to find out the exact space occupied used by
>> Index and Data ? The SQL Server 2000 Database is of 5GB allocated with
>> data (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better
>> management of memory. Is it correct ?
>> Thanks
>>
>>
>|||Robert
Short, that means to create an index on columns like INT (4 bytes....) and
not on VARCHAR(8000).....
http://www.sql-server-performance.com/clustered_indexes.asp
http://www.sql-server-performance.com/nonclustered_indexes.asp
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Can you elaborate what does index narrow means ?
> Thanking you in anticipation.
> Rob
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> Keep your indexes narrow as you can.
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for your advice.
>> s there any general rule of thumb about the ratio between Index and Data
>> ?
>> Thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual
>> data. Is there any command to find out the exact space occupied used
>> by Index and Data ? The SQL Server 2000 Database is of 5GB allocated
>> with data (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better
>> management of memory. Is it correct ?
>> Thanks
>>
>>
>>
>|||Thank you for your advice
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:emhSBlOoGHA.1668@.TK2MSFTNGP05.phx.gbl...
> Robert
> Short, that means to create an index on columns like INT (4 bytes....)
> and not on VARCHAR(8000).....
> http://www.sql-server-performance.com/clustered_indexes.asp
> http://www.sql-server-performance.com/nonclustered_indexes.asp
>
>
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Can you elaborate what does index narrow means ?
>> Thanking you in anticipation.
>> Rob
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> Keep your indexes narrow as you can.
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Dear Uri,
>> Thank you for your advice.
>> s there any general rule of thumb about the ratio between Index and
>> Data ?
>> Thanks
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>> Robert
>> sp_helpdb 'pubs'
>> use pubs
>> go
>> sp_spaceused
>>
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>> As far as I know, the database size contains both Index and actual
>> data. Is there any command to find out the exact space occupied used
>> by Index and Data ? The SQL Server 2000 Database is of 5GB allocated
>> with data (I think it is data + Index) of 1.5GB.
>> Some fellow has mentioned that applying SP4 will get a better
>> management of memory. Is it correct ?
>> Thanks
>>
>>
>>
>>
>

Database Size and SP4

As far as I know, the database size contains both Index and actual data. Is
there any command to find out the exact space occupied used by Index and
Data ? The SQL Server 2000 Database is of 5GB allocated with data (I think
it is data + Index) of 1.5GB.
Some fellow has mentioned that applying SP4 will get a better management of
memory. Is it correct ?
ThanksRobert
sp_helpdb 'pubs'
use pubs
go
sp_spaceused
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
> As far as I know, the database size contains both Index and actual data.
> Is there any command to find out the exact space occupied used by Index
> and Data ? The SQL Server 2000 Database is of 5GB allocated with data (I
> think it is data + Index) of 1.5GB.
> Some fellow has mentioned that applying SP4 will get a better management
> of memory. Is it correct ?
> Thanks
>|||Dear Uri,
Thank you for your advice.
s there any general rule of thumb about the ratio between Index and Data ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
> Robert
> sp_helpdb 'pubs'
> use pubs
> go
> sp_spaceused
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:OChQ0CBoGHA.4628@.TK2MSFTNGP05.phx.gbl...
>|||Robert
Keep your indexes narrow as you can.
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Thank you for your advice.
> s there any general rule of thumb about the ratio between Index and Data ?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23BSW9EBoGHA.4772@.TK2MSFTNGP03.phx.gbl...
>|||Dear Uri,
Can you elaborate what does index narrow means ?
Thanking you in anticipation.
Rob
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
> Robert
> Keep your indexes narrow as you can.
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oln$soNoGHA.3532@.TK2MSFTNGP04.phx.gbl...
>|||Robert
Short, that means to create an index on columns like INT (4 bytes....) and
not on VARCHAR(8000).....
http://www.sql-server-performance.c...red_indexes.asp
http://www.sql-server-performance.c...red_indexes.asp
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
> Dear Uri,
> Can you elaborate what does index narrow means ?
> Thanking you in anticipation.
> Rob
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23HGYBuNoGHA.1236@.TK2MSFTNGP03.phx.gbl...
>|||Thank you for your advice
Robert
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:emhSBlOoGHA.1668@.TK2MSFTNGP05.phx.gbl...
> Robert
> Short, that means to create an index on columns like INT (4 bytes....)
> and not on VARCHAR(8000).....
> http://www.sql-server-performance.c...red_indexes.asp
> http://www.sql-server-performance.c...red_indexes.asp
>
>
>
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:Oa9cuaOoGHA.1208@.TK2MSFTNGP04.phx.gbl...
>

database server Link DOWN

Hi every body.

We have instaled Sql Server 2000 Standard Sp4, whit a Nortel Product. That aplication make to gestion calls using MSQL2000; in some moment the DB stoping and the Reason is "[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Arithmetic overflow error for data type smallint, value = 33459."

We do not have any idea about this problem.

Thx

Eliseo Berdugo

Hi, try to desinstal and reinstall your "Driver for JDBC".

Database resource won't start after changing cluster subnet

I'm running a SQL Server 2000 SP4 2-node active/acitve cluster on
Windows 2003 Server Enterprise Edition SP1. I changed the subnet mask
of my public network config, as well as the MCS virtual subnet and two
database resource subnets (all are using the same subnet). DNS is
correctly resolving to the IP addresses for all cluster resources. I
have two databases on this cluster. After changing the subnet, the
first one starts correctly on both nodes. The second one fails to
start at the SQL Server resource on node 1, but starts correctly on
node 2. I've included the three major application log errors below.
Before I start troubleshooting any further, I'm looking for any
suggestions. Does anyone know if running the SQL Server installation
advanced options mode would make any difference? Could a machine
reboot be necessary?
Type: Error
User: N/A
Source: MSSQL$LCCSQLINST1
Category: (3)
Event ID: 17052
Description:
[sqsrvres] ODBC sqldriverconnect failed
=======================================
Type: Error
User: N/A
Source: MSSQL$LCCSQLINST1
Category: (3)
Event ID: 17052
Description:
[sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
message = [Microsoft][ODBC SQL Server
Driver][DBNETLIB]SQL Server does not exist or access denied.
=======================================
Type: Error
User: N/A
Source: MSSQL$LCCSQLINST1
Category: (3)
Event ID: 17052
Description:
[sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 35;
message = [Microsoft][ODBC SQL Server
Driver][DBNETLIB]ConnectionOpen (Connect()).
=======================================
Thanks,
Curt
http://support.microsoft.com/kb/244980/en-us
There's a section in there on how to change the subnet for the resources on
a SQL Server 2000 failover cluster configuration.
Sincerely,
Anthony Thomas

"Curtis Morrison" <curtmorrison@.hotmail.com> wrote in message
news:1174920700.449721.209260@.l77g2000hsb.googlegr oups.com...
> I'm running a SQL Server 2000 SP4 2-node active/acitve cluster on
> Windows 2003 Server Enterprise Edition SP1. I changed the subnet mask
> of my public network config, as well as the MCS virtual subnet and two
> database resource subnets (all are using the same subnet). DNS is
> correctly resolving to the IP addresses for all cluster resources. I
> have two databases on this cluster. After changing the subnet, the
> first one starts correctly on both nodes. The second one fails to
> start at the SQL Server resource on node 1, but starts correctly on
> node 2. I've included the three major application log errors below.
> Before I start troubleshooting any further, I'm looking for any
> suggestions. Does anyone know if running the SQL Server installation
> advanced options mode would make any difference? Could a machine
> reboot be necessary?
> Type: Error
> User: N/A
> Source: MSSQL$LCCSQLINST1
> Category: (3)
> Event ID: 17052
> Description:
> [sqsrvres] ODBC sqldriverconnect failed
> =======================================
> Type: Error
> User: N/A
> Source: MSSQL$LCCSQLINST1
> Category: (3)
> Event ID: 17052
> Description:
> [sqsrvres] checkODBCConnectError: sqlstate = 08001; native error = 11;
> message = [Microsoft][ODBC SQL Server
> Driver][DBNETLIB]SQL Server does not exist or access denied.
> =======================================
> Type: Error
> User: N/A
> Source: MSSQL$LCCSQLINST1
> Category: (3)
> Event ID: 17052
> Description:
> [sqsrvres] checkODBCConnectError: sqlstate = 01000; native error = 35;
> message = [Microsoft][ODBC SQL Server
> Driver][DBNETLIB]ConnectionOpen (Connect()).
> =======================================
> Thanks,
> Curt
>

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:

> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i ca
n
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:

> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don'
t
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separa
te
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want t
o
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:

> Sunday it ran this was the normal day i had it set all the other days mond
ay
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i ge
t
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor an
d
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fil
l
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:

> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganiz
e
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database reindex

I had job set up do to database reindex using database maintenance
plans...since loading SP4 for sql 2000 these jobs are not running ...i can
do start job and that fine but i need them to run early morning of hours
Any ideasHi Tracey
Have you checked that the jobs and the job schedule are both enabled? What
does the job history say? Have you got any information in the SQL Server log
or the Windows Event Log at the times these jobs are supposed to run? Have
you tried re-creating the maintenance plan or jobs?
John
"TRACEY" wrote:
> I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||TRACEY
If I undestsood you , under Optimization Tab choose schedule to create a
job
"TRACEY" <TRACEY@.discussions.microsoft.com> wrote in message
news:3F05031E-F96D-484D-83CF-A4F87A9DD259@.microsoft.com...
>I had job set up do to database reindex using database maintenance
> plans...since loading SP4 for sql 2000 these jobs are not running ...i
> can
> do start job and that fine but i need them to run early morning of hours
> Any ideas|||That how i set up the job..
under Optimization Tab choose schedule to create a
job
Its enabled...and there nothing in the job history nothing in the sql
logs...nothing in events...i just got it to fire off at night and then in
morning i see nothing ...no history
I can just right click it and boom it starts the index which i really don't
want to do during activity..
This only happened since SP4 ........
Im almost going to write a script to do the dbreindex ...but i like to
figure out why..
The only thing we did do was for some of the tables we put these in separate
file groups (the vendor erp creates temp files for each person called
X_.owner) so we put these in file groups so i can see when they get large
...and when i can then run a utility to clear them out...but the data
remains where it is and the log files.|||Hi Tracey
If there is nothing in the job history is sounds like the schedule is wrong.
If you schedule it for a different time rather than manually run it, can you
see the job starting?
John
"TRACEY" wrote:
> That how i set up the job..
> under Optimization Tab choose schedule to create a
> job
> Its enabled...and there nothing in the job history nothing in the sql
> logs...nothing in events...i just got it to fire off at night and then in
> morning i see nothing ...no history
> I can just right click it and boom it starts the index which i really don't
> want to do during activity..
> This only happened since SP4 ........
> Im almost going to write a script to do the dbreindex ...but i like to
> figure out why..
> The only thing we did do was for some of the tables we put these in separate
> file groups (the vendor erp creates temp files for each person called
> X_.owner) so we put these in file groups so i can see when they get large
> ...and when i can then run a utility to clear them out...but the data
> remains where it is and the log files.
>
>|||Sunday it ran this was the normal day i had it set all the other days monday
thursday failed....odd but at least it ran last night.
I checked the logs
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'SQL1'
Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
2:00:03 AM
[1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space ....thats why i get
100%
That means ever time record insert its going to split ha.....
how to change it to 90
Do i select the change free space per page percentage to 10%
will that be DBCC REINDEX 'DB','90'...?
And do you do master, model,
Well im glad it ran last night...
Thanks|||Hi Tracey
In general you would want to pass 0 to the maintainance plan to re-organise
the indexes using their original value. In any given database you may want to
have some indexes with fill factors or 0 or 100 (if the data is static!) and
others with less, therefore resetting the index fill factor across the board
is not necessarily a good thing. If you want to change the fill factor for a
give index use
DBCC DBREINDEX ( 'database.owner.table_name' , index_name, fillfactor )
Analyse the information from DBCC SHOWCONTIG or
sys.dm_db_index_physical_stats instead (SQL 2005) for information on what
indexes are fragmented.
John
"TRACEY" wrote:
> Sunday it ran this was the normal day i had it set all the other days monday
> thursday failed....odd but at least it ran last night.
> I checked the logs
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'SQL1'
> Starting maintenance plan 'Reindex Databases Costpoint' on 10/29/2006
> 2:00:03 AM
> [1] Database DeltekTC: Index Rebuild (leaving 100%% free space)...
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space ....thats why i get
> 100%
> That means ever time record insert its going to split ha.....
> how to change it to 90
> Do i select the change free space per page percentage to 10%
> will that be DBCC REINDEX 'DB','90'...?
> And do you do master, model,
> Well im glad it ran last night...
> Thanks|||Took me ages to find the thread.
How do you bookmark it so i can go to it.
I have it set to Database maintenance plan optimizations set to regorganize
data and index pages
reorganize pages with the orginal amount of free space
Any ideas why its reporting 100%
That means ever time record insert its going to split
Why is it not taking the default fill factor in the tables.
Thanks|||Hi Tracey
That will depend on what newsreader client you are using. You can set
Outlook Express to only display message threads that you have participated
in, or you can search the technet discussion groups using (say) your email
address. Here is a link for the thread http://tinyurl.com/y3ntk4
The SQL Maint utility uses a value of 100 for the RebldIdx to specify that
you use the original values as this works on free space and not the
fillfactor. DBCC DBREINDEX uses 0 to maintain the current fill factors. You
can reindex specific indexes using this command to change the fill factor and
then use a maintenance plan or your own job to keep it (and all other
indexes) at their own level. Another method to restore the fillfactors would
be to drop and re-create the indexes if you already have scripts that for
their original definions. This would be the case if you use a source code
control system such as Visual Source Safe for you database code.
Having a fill factor of 100% is not necessarily a bad thing, if you have
table that contains very static data you may want to use 100% fill factor,
similarly if the table is being treated as an ISAM structure then a high fill
factor may be appropriate if you rarely update existing rows.
John
"TRACEY" wrote:
> Took me ages to find the thread.
> How do you bookmark it so i can go to it.
> I have it set to Database maintenance plan optimizations set to regorganize
> data and index pages
> reorganize pages with the orginal amount of free space
> Any ideas why its reporting 100%
> That means ever time record insert its going to split
> Why is it not taking the default fill factor in the tables.
> Thanks

Database Recovery Problem

Greetings,
I am attempting to restore some databases to a SQL Server
2000 Installation that fell apart after loading SP4 for
Windows 2000. The backups were made using Veritas Backup
Exec 8.6. on a remote backup server. When I do a restore
on my databases Veritas restores all of them to the the
SQL Server and they appear in Enterprise Manager. However
the databases are in a state of loading and I cannot seem
to find a way to take them out of this loading state to
complete the restore to normal operation.
I am new to SQL Server so I am at a loss as to what to do
to rectify this situation.
Thanks in advance for the help.
Joe.If you have restored the databases with norecovery you can
restore database mydb with recovery
to bring it on-line.
If in fact veritas hasn't completed the restore then you
will have to go back to that product.
>"Joe Toth" <jtoth@.cmefcu.org> wrote in message
>news:029d01c34727$da60cee0$a401280a@.phx.gbl...
>> Greetings,
>> I am attempting to restore some databases to a SQL
Server
>> 2000 Installation that fell apart after loading SP4 for
>> Windows 2000. The backups were made using Veritas Backup
>> Exec 8.6. on a remote backup server. When I do a restore
>> on my databases Veritas restores all of them to the the
>> SQL Server and they appear in Enterprise Manager.
However
>> the databases are in a state of loading and I cannot
seem
>> to find a way to take them out of this loading state to
>> complete the restore to normal operation.
>> I am new to SQL Server so I am at a loss as to what to
do
>> to rectify this situation.
>> Thanks in advance for the help.
>> Joe.
>
>.
>|||It's been a few months since I restored from BE, but I
think it's an option when configuring the restore on the
SQL tab. The db is left in this state so you can apply
logs if you need to.
--Scott
>--Original Message--
>Greetings,
>I am attempting to restore some databases to a SQL Server
>2000 Installation that fell apart after loading SP4 for
>Windows 2000. The backups were made using Veritas Backup
>Exec 8.6. on a remote backup server. When I do a restore
>on my databases Veritas restores all of them to the the
>SQL Server and they appear in Enterprise Manager. However
>the databases are in a state of loading and I cannot seem
>to find a way to take them out of this loading state to
>complete the restore to normal operation.
>I am new to SQL Server so I am at a loss as to what to do
>to rectify this situation.
>Thanks in advance for the help.
>Joe.
>.
>