I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that
-
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of you
r
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Sunday, March 25, 2012
Database size vs total table and index size
I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply
"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...
|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
Database size vs total table and index size
I use SQL Server 2000.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
My database is 1912.69 MB with no available free space.
My logfile is 1 MB.
The size of all tables and indexes add up to 200 MB.
I have no diagrams, two views, fifty stored procedures, six users, ten
roles, no rules, no defaults, no user defined data types, no user
defined functions.
Autoshrink is set to true.
My question:
How can the database be almost 2 GB when the tables and indexes add up
to only 200MB?
When I try to shrink manually in SQL Enterprise Manager I get no error
message, but no shrinking occurs.
I am grateful for any help.
Regards,
Jan Nordgreen
Reply"damezumari" wrote:
> I use SQL Server 2000.
> My database is 1912.69 MB with no available free space.
> My logfile is 1 MB.
> The size of all tables and indexes add up to 200 MB.
> I have no diagrams, two views, fifty stored procedures, six users, ten
> roles, no rules, no defaults, no user defined data types, no user
> defined functions.
> Autoshrink is set to true.
> My question:
> How can the database be almost 2 GB when the tables and indexes add up
> to only 200MB?
> When I try to shrink manually in SQL Enterprise Manager I get no error
> message, but no shrinking occurs.
> I am grateful for any help.
> Regards,
> Jan Nordgreen
This sounds VERY unusual: database sizes that are 10 times bigger than the
actual datasize are absolutely normal and there's a lot of reasons for that -
but they usually have something to do with the TA-Log. Please verify that
your transaction log file is really that tiny, and publish the syntax of your
shrink statement ...|||First, I don=B4t think that this is TA related, because your TA size is
1MB which is really , really small.
Your issue could be based on several things like:
You can=B4t shrink the database size under the initial size, so if the
initial size was 2GB (which isn=B4t unusal and not that big) you can=B4t
shrink it with DBCC Shrinkdatabase. Look in the BOL for more
information:
"The target size for data and log files as calculated by DBCC
SHRINKDATABASE can never be smaller than the minimum size of a file.
The minimum size of a file is the size specified when the file was
originally created, or the last explicit size set with a file size
changing operation, such as DBCC SHRINKFILE."
You can shrink the database using DBCC Shrinkfile, where you can
specify a new size of a single file. Look in the BOL for more
information.
Anyway, shrinking your file to a smaller size than 2GB could decrease
performance if your database is growing and gaining automatically new
space. So shrinking the database to 200MB would cause a halt if the
size has to be extended, causing waiting processes to be stopped until
the new size is aquired from the OS:
HTH, Jens Suessmeyer.
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
>>
>>
>>
>>
>
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...
>
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...
>
Thursday, March 22, 2012
Database size
Hi all,
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/e...des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/bo...TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx
.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/e...des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/bo...TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx
.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
Database size
Hi all,
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/books/toc/4944.asp#TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
HariHi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en-us/createdb/cm_8_des_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/books/toc/4944.asp#TableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
Wednesday, March 21, 2012
Database size
Hi all,
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
Hari
Hi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en...es_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/boo...ableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
I need to estimate the disk space required for SQL Server
2000 and our application databases. Apart from the
table/index sizes (calculated as per logic given in BOL),
are there any other parameters to be considered to size
the disks ? Like more space for tempdb?
Is there any standard approach to estimate the disk space
required based on data growth?
Thanks,
Hari
Hi Hari
Yes, the transaction log is a significant consideration. It is not possible
to accurately calculate size requirements for log space / growth as the log
storage format is not even published. Best bet with this is to make regular
observations, collect empirical data on growth rates between backup windows
& ensure you have enough space for log use / growth.
There's information on calculating database size in Books Online at:
http://msdn.microsoft.com/library/en...es_02_2h45.asp
The topic's also covered widely in books, including:
http://www.microsoft.com/mspress/boo...ableOfContents
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:295d801c464af$6021d1a0$a301280a@.phx.gbl...
> Hi all,
> I need to estimate the disk space required for SQL Server
> 2000 and our application databases. Apart from the
> table/index sizes (calculated as per logic given in BOL),
> are there any other parameters to be considered to size
> the disks ? Like more space for tempdb?
> Is there any standard approach to estimate the disk space
> required based on data growth?
> Thanks,
> Hari
Saturday, February 25, 2012
database repair without data loss?
Scenario: Database maintenance plan failed in "check data and index linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which revealed a few "page id" problems. It appears all errors on related to one table. The CHECKDB stated specifically: "repair_allow_data_loss is the minimum repair level for the errors found"
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.
My question is: Is there any way to repair database/table without data loss?Yes, restore from your last backup and apply all the log backups since the
backup was taken (stopping at the point the corruption appears if necessary)
It is not *guaranteed* that repair will have to delete data to repair the
database but it is highly likely (if REPAIR_ALLOW_DATA_LOSS is needed).
Repair should always be your last resort. You should also determine the root
cause of the corruption (i.e. examine NT event logs, SQL Server error log,
run hardware diagnostics etc) as a hardware fault will most likely cause the
same or similar corruption in future if not corrected.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alan T" <infopro@.3wlogic.net> wrote in message
news:FD16E2B3-DEF4-486C-8A80-DFABB549720B@.microsoft.com...
> Scenario: Database maintenance plan failed in "check data and index
linkage" activity. Ran DBCC CHECKDB WITH PHYSICAL_ONLY option which
revealed a few "page id" problems. It appears all errors on related to one
table. The CHECKDB stated specifically: "repair_allow_data_loss is the
minimum repair level for the errors found".
> My question is: Is there any way to repair database/table without data
loss?|||Thanks, Paul. With the help of someone with a great deal more experience I was able to recover virtually all data.
The corruption was limited to one table, so after some minor unsuccessful attempts at repair we ran DBCC CHECKTABLE WITH REPAIR_ALLOW_DATA_LOSS. We then restored a "good" backup into a temporary database and from that database pulled records from the problem table that were missing in the production table after the REPAIR_ALLOW_DATA_LOSS. It appears we were able to recover all but about 11 records. It's wasn't a "perfect" recovery but I'm happy and grateful for the help.
Best wishes.
Subscribe to:
Posts (Atom)