Datatype change INT to BIGINT on a large table

Hi folks,
I have a table which is of 500 GB in size. I need to change the datatype
of a column from INT to BIGINT.
When I tried making this change from Enterprise Manager, it was throwing
log space is full. I also truncated the log and tried again, eventhen I
face the same problem.
Also I have limited space available on data drive. After some research I
found that SQL Server interally creates a Tmp table with the new
datatype, populates that table with orginal table data, drops the
original table and then renames the Tmp table.
So I must need atleast 500 GB additional freespace on data drive, but I
do not have 500 GB free space on data drive.
I am just thinking the below alternate way to do this task.
1. BCP out the data to a temporary mapped network drive which has 500 GB
free space.
2. Drop the table.
3. Recreate the table with BIGINT datatype on the required column.
4. BCP in the data.
5. Recreate the Keys and constraints.
Can someone suggest me whether this is the best way, any possibility of
loosing the data if I follow this way. Please suggest me if there is a
better approach.
Thanks in advance.
*** Sent via Developersdex http://www.examnotes.net ***Another option is to use ALTER TABLE ... ALTER COLUMN ...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Tibor,
If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
file?
*** Sent via Developersdex http://www.examnotes.net ***|||You need to test first. Create a similar table in a smaller database, copy o
ver a subset of the rows
and do a test. Sometimes, these changes can go without touching the data (im
mediately), sometimes,
all data is changed immediately, and changes has to be logged. I haven't see
n any document
describing the exact rules for when a change is immediate or not. So, do a t
est first to be certain.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OkPRGnDyFHA.3312@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor,
> If I do ALTER TABLE ...ALTER COLUMN, will it log to transaction log
> file?
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||I think your bulk copy solution is the best way to go. To reduce the size of
the exported file, use native format instead of fixed width or tab delimited
text columns. To reduce transaction logging while importing the data, set
the database recovery model to "bulk insert" or "simple", set the database
to single user / dbo use only mode, and re-create indexes only after the
import has completed.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:u38r6IDyFHA.3588@.tk2msftngp13.phx.gbl...
> Hi folks,
> I have a table which is of 500 GB in size. I need to change the datatype
> of a column from INT to BIGINT.
> When I tried making this change from Enterprise Manager, it was throwing
> log space is full. I also truncated the log and tried again, eventhen I
> face the same problem.
> Also I have limited space available on data drive. After some research I
> found that SQL Server interally creates a Tmp table with the new
> datatype, populates that table with orginal table data, drops the
> original table and then renames the Tmp table.
> So I must need atleast 500 GB additional freespace on data drive, but I
> do not have 500 GB free space on data drive.
> I am just thinking the below alternate way to do this task.
> 1. BCP out the data to a temporary mapped network drive which has 500 GB
> free space.
> 2. Drop the table.
> 3. Recreate the table with BIGINT datatype on the required column.
> 4. BCP in the data.
> 5. Recreate the Keys and constraints.
> Can someone suggest me whether this is the best way, any possibility of
> loosing the data if I follow this way. Please suggest me if there is a
> better approach.
> Thanks in advance.
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks JT for your excellent suggestions/comments.
I tried Tibor's suggestion (ALTER TABLE...ALTER COLUMN), it logs to
transaction log even when the recovery model is set to simple.
Internally it updates all the rows. But I do not have enough space on
log drive. So I will have to go with BCP option.
*** Sent via Developersdex http://www.examnotes.net ***|||I tried BCPing with Native format and Char format option. It seems that
the the file unloaded using Char format is smaller than the one created
with Native format. Any ideas..
--
*** Sent via Developersdex http://www.examnotes.net ***|||I don't recall offhand the specifics, but there are cases (perhaps with
decimal data types) where exporting to char format and then re-importing
will cause loss of data resolution. If you have space for exporting to
native format, then go ahead and use that.
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:%235R%23ikNyFHA.3420@.TK2MSFTNGP10.phx.gbl...
>I tried BCPing with Native format and Char format option. It seems that
> the the file unloaded using Char format is smaller than the one created
> with Native format. Any ideas..
> --
> *** Sent via Developersdex http://www.examnotes.net ***sql

Dataset Query Size limit (300 lines)

Hi,
I have a formatted Dataset query with size exceeding 300 lines (vertically). The dataset editor is not allowing me write it. Is this a limitation that the query size should be less than 300 lines or do we need have any way to increase this.
Formatting helps understand the query it in a legible format.
ThanksBy "dataset editor" do you mean the Dataset dialog, generic query designer
or graphical query designer? How long is the query in characters? What do
you mean by "not allowing me to write it"? Do you get an error message?
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> Hi,
> I have a formatted Dataset query with size exceeding 300 lines
(vertically). The dataset editor is not allowing me write it. Is this a
limitation that the query size should be less than 300 lines or do we need
have any way to increase this.
> Formatting helps understand the query it in a legible format.
> Thanks|||Apologise for this confusion.....I talked about the generic Query Designer [Report Design Window --> Data Tab -- > Place where the query is written]
I have a query as, say,
"select " & Parameters!col_name>Value & " as myValue from dual UNION ALL ...(almost 18k characters)"...
I wrote the above query as follows (for legibility)
" select " & Parameters!col_name>Value & " as myValue " & (press enter)
" from dual " & (press enter)
" UNION ALL " & (press enter)
... (exceeds 300 lines vertically now)
Here, i notice that only 300 lines come in the window and after that it does not paste or allow me manually write the query. Nothing happens when i write.
Kinldy let me know if it cannot work beyond this or a solution.
"Albert Yen [MSFT]" wrote:
> By "dataset editor" do you mean the Dataset dialog, generic query designer
> or graphical query designer? How long is the query in characters? What do
> you mean by "not allowing me to write it"? Do you get an error message?
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > Hi,
> >
> > I have a formatted Dataset query with size exceeding 300 lines
> (vertically). The dataset editor is not allowing me write it. Is this a
> limitation that the query size should be less than 300 lines or do we need
> have any way to increase this.
> >
> > Formatting helps understand the query it in a legible format.
> >
> > Thanks
>
>|||I am unable to reproduce your problem with a query with up to 600 lines and
30K characters. Could you post the text of your query? Although, it would
seem strange if the specific text made any difference.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> Apologise for this confusion.....I talked about the generic Query
Designer [Report Design Window --> Data Tab -- > Place where the query is
written]
> I have a query as, say,
> "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
...(almost 18k characters)"...
> I wrote the above query as follows (for legibility)
> " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> " from dual " & (press enter)
> " UNION ALL " & (press enter)
> ... (exceeds 300 lines vertically now)
> Here, i notice that only 300 lines come in the window and after that it
does not paste or allow me manually write the query. Nothing happens when i
write.
> Kinldy let me know if it cannot work beyond this or a solution.
> "Albert Yen [MSFT]" wrote:
> > By "dataset editor" do you mean the Dataset dialog, generic query
designer
> > or graphical query designer? How long is the query in characters? What
do
> > you mean by "not allowing me to write it"? Do you get an error message?
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > Hi,
> > >
> > > I have a formatted Dataset query with size exceeding 300 lines
> > (vertically). The dataset editor is not allowing me write it. Is this a
> > limitation that the query size should be less than 300 lines or do we
need
> > have any way to increase this.
> > >
> > > Formatting helps understand the query it in a legible format.
> > >
> > > Thanks
> >
> >
> >|||Well, this is really strange.
Please follow the process as,
format the query in an editor (textpad, in my case) with an enter character after every line. Copy this query which is beyond, say 400 lines, and paste it directly in datatset querystrign editor. Are you sure you get the complete query in the window. Try copying it back to the textpad editor and check for the number of lines.
My focus is primarily on having the copy and paste operation performed, as i did. Otherwise, if you can suggest any probable reason for this behaviour..
Thanks.
"Albert Yen [MSFT]" wrote:
> I am unable to reproduce your problem with a query with up to 600 lines and
> 30K characters. Could you post the text of your query? Although, it would
> seem strange if the specific text made any difference.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > Apologise for this confusion.....I talked about the generic Query
> Designer [Report Design Window --> Data Tab -- > Place where the query is
> written]
> > I have a query as, say,
> > "select " & Parameters!col_name>Value & " as myValue from dual UNION ALL
> ...(almost 18k characters)"...
> > I wrote the above query as follows (for legibility)
> > " select " & Parameters!col_name>Value & " as myValue " & (press enter)
> > " from dual " & (press enter)
> > " UNION ALL " & (press enter)
> > ... (exceeds 300 lines vertically now)
> >
> > Here, i notice that only 300 lines come in the window and after that it
> does not paste or allow me manually write the query. Nothing happens when i
> write.
> > Kinldy let me know if it cannot work beyond this or a solution.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > By "dataset editor" do you mean the Dataset dialog, generic query
> designer
> > > or graphical query designer? How long is the query in characters? What
> do
> > > you mean by "not allowing me to write it"? Do you get an error message?
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > Hi,
> > > >
> > > > I have a formatted Dataset query with size exceeding 300 lines
> > > (vertically). The dataset editor is not allowing me write it. Is this a
> > > limitation that the query size should be less than 300 lines or do we
> need
> > > have any way to increase this.
> > > >
> > > > Formatting helps understand the query it in a legible format.
> > > >
> > > > Thanks
> > >
> > >
> > >
>
>|||I do see a limit of 32K characters in the textbox, after which I can't enter
any more text. I think the 32K character limit is imposed by the textbox
control, not us. That sounds like what you are seeing, except it's a limit
on the number of characters, not the number of lines. I'm not sure why you
have a problem after only 18K characters. At any rate, I don't have any
workarounds for this problem.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"push" <push@.discussions.microsoft.com> wrote in message
news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> Well, this is really strange.
> Please follow the process as,
> format the query in an editor (textpad, in my case) with an enter
character after every line. Copy this query which is beyond, say 400 lines,
and paste it directly in datatset querystrign editor. Are you sure you get
the complete query in the window. Try copying it back to the textpad editor
and check for the number of lines.
> My focus is primarily on having the copy and paste operation performed, as
i did. Otherwise, if you can suggest any probable reason for this
behaviour..
> Thanks.
> "Albert Yen [MSFT]" wrote:
> > I am unable to reproduce your problem with a query with up to 600 lines
and
> > 30K characters. Could you post the text of your query? Although, it
would
> > seem strange if the specific text made any difference.
> >
> > --
> > Albert Yen
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "push" <push@.discussions.microsoft.com> wrote in message
> > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > Apologise for this confusion.....I talked about the generic Query
> > Designer [Report Design Window --> Data Tab -- > Place where the query
is
> > written]
> > > I have a query as, say,
> > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
ALL
> > ...(almost 18k characters)"...
> > > I wrote the above query as follows (for legibility)
> > > " select " & Parameters!col_name>Value & " as myValue " & (press
enter)
> > > " from dual " & (press enter)
> > > " UNION ALL " & (press enter)
> > > ... (exceeds 300 lines vertically now)
> > >
> > > Here, i notice that only 300 lines come in the window and after that
it
> > does not paste or allow me manually write the query. Nothing happens
when i
> > write.
> > > Kinldy let me know if it cannot work beyond this or a solution.
> > >
> > > "Albert Yen [MSFT]" wrote:
> > >
> > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > designer
> > > > or graphical query designer? How long is the query in characters?
What
> > do
> > > > you mean by "not allowing me to write it"? Do you get an error
message?
> > > >
> > > > --
> > > > Albert Yen
> > > > SQL Server Reporting Services
> > > >
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > (vertically). The dataset editor is not allowing me write it. Is
this a
> > > > limitation that the query size should be less than 300 lines or do
we
> > need
> > > > have any way to increase this.
> > > > >
> > > > > Formatting helps understand the query it in a legible format.
> > > > >
> > > > > Thanks
> > > >
> > > >
> > > >
> >
> >
> >|||Is it practical for you to write the query as a stored procedure and call it
from the report?
"Albert Yen [MSFT]" wrote:
> I do see a limit of 32K characters in the textbox, after which I can't enter
> any more text. I think the 32K character limit is imposed by the textbox
> control, not us. That sounds like what you are seeing, except it's a limit
> on the number of characters, not the number of lines. I'm not sure why you
> have a problem after only 18K characters. At any rate, I don't have any
> workarounds for this problem.
> --
> Albert Yen
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "push" <push@.discussions.microsoft.com> wrote in message
> news:5637124F-6032-4D36-A8F3-E73F4F68E44D@.microsoft.com...
> > Well, this is really strange.
> > Please follow the process as,
> > format the query in an editor (textpad, in my case) with an enter
> character after every line. Copy this query which is beyond, say 400 lines,
> and paste it directly in datatset querystrign editor. Are you sure you get
> the complete query in the window. Try copying it back to the textpad editor
> and check for the number of lines.
> >
> > My focus is primarily on having the copy and paste operation performed, as
> i did. Otherwise, if you can suggest any probable reason for this
> behaviour..
> >
> > Thanks.
> >
> > "Albert Yen [MSFT]" wrote:
> >
> > > I am unable to reproduce your problem with a query with up to 600 lines
> and
> > > 30K characters. Could you post the text of your query? Although, it
> would
> > > seem strange if the specific text made any difference.
> > >
> > > --
> > > Albert Yen
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "push" <push@.discussions.microsoft.com> wrote in message
> > > news:8B9DF816-0E7D-45A7-8D80-03FA376EB94E@.microsoft.com...
> > > > Apologise for this confusion.....I talked about the generic Query
> > > Designer [Report Design Window --> Data Tab -- > Place where the query
> is
> > > written]
> > > > I have a query as, say,
> > > > "select " & Parameters!col_name>Value & " as myValue from dual UNION
> ALL
> > > ...(almost 18k characters)"...
> > > > I wrote the above query as follows (for legibility)
> > > > " select " & Parameters!col_name>Value & " as myValue " & (press
> enter)
> > > > " from dual " & (press enter)
> > > > " UNION ALL " & (press enter)
> > > > ... (exceeds 300 lines vertically now)
> > > >
> > > > Here, i notice that only 300 lines come in the window and after that
> it
> > > does not paste or allow me manually write the query. Nothing happens
> when i
> > > write.
> > > > Kinldy let me know if it cannot work beyond this or a solution.
> > > >
> > > > "Albert Yen [MSFT]" wrote:
> > > >
> > > > > By "dataset editor" do you mean the Dataset dialog, generic query
> > > designer
> > > > > or graphical query designer? How long is the query in characters?
> What
> > > do
> > > > > you mean by "not allowing me to write it"? Do you get an error
> message?
> > > > >
> > > > > --
> > > > > Albert Yen
> > > > > SQL Server Reporting Services
> > > > >
> > > > > This posting is provided "AS IS" with no warranties, and confers no
> > > rights.
> > > > >
> > > > > "push" <push@.discussions.microsoft.com> wrote in message
> > > > > news:274D4735-1AB9-487A-ADAF-A3295EBF309F@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > I have a formatted Dataset query with size exceeding 300 lines
> > > > > (vertically). The dataset editor is not allowing me write it. Is
> this a
> > > > > limitation that the query size should be less than 300 lines or do
> we
> > > need
> > > > > have any way to increase this.
> > > > > >
> > > > > > Formatting helps understand the query it in a legible format.
> > > > > >
> > > > > > Thanks
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>sql

datalength doubling values

Am I missing something? I'm trying to return the size of the data contained in a varbinary(max) column, however it appears that the value being returned is double what it should be. Is this normal, or is there something else I need to do?

Thanks,

Devin

Edit: I'm not discounting that my data may be weird, but I wanted to cast my net as wide as possible.

Perhaps the field contains trailing blanks...|||

Why do you think this is double? Can you post a snippet of code that doesn't seem to make sense? Like:

set nocount on

declare @.test varbinary(max)

set @.test = 0x12

select datalength(@.test)

set @.test = 0x1234

select datalength(@.test)

Returns:

--

1

--

2

|||

Ignore this. I was using compression on the streams as I was putting them into the column. Apparently the framework's GZipStream class has a bug that causes it to mishandle files that already have compression in them (video, jpg, pdf) so that they end up larger.

DATALENGTH was reporting the correct size for the contents of the column.

Thanks,

Devin

Databases > 100 G

Hi Guys,
We are planning on making a dataware house in our
company. The size is approximately 200 to 400 G. We have
to make a recommendation for a database server. I would
love to say "Sql Server". But frankly I'm not very
confident at this point. How many people on this board
have databases larger than 100 G. Is it feasible to do
this on Sql Server ? I dont want to say Sql Server and
have to eat my words later. Any facts are welcome from
live installations > 100 G.
Thanks,
JackWhilst there is more effort required in the design,architecture and
administration of a 500Gb database as compared to a 500MB database, SQL
Server is more than capable of scaling to these database sizes. You might be
interested in having a look at
Rosetta Genomics 10-Terabyte Human Genome Database
http://www.microsoft.com/sql/techin...000/rosetta.asp
Microsoft SQL Server 2000 Scalability Project
http://www.microsoft.com/sql/techin.../wintercorp.asp
RDBMS Performance Tuning Guide for Data Warehousing
http://www.microsoft.com/technet/tr...part5/c2061.asp
SQL Server Megaservers: Scalability, Availability, Manageability
http://www.microsoft.com/technet/tr...plan/SSMSAM.asp
(you might also get more responses by posting this in .server or
.datawarehouse)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jack A" <jacka8@.excite.com> wrote in message
news:0b3101c3c3f5$45e86d90$a501280a@.phx.gbl...

quote:

---

> Hi Guys,
> We are planning on making a dataware house in our
> company. The size is approximately 200 to 400 G. We have
> to make a recommendation for a database server. I would
> love to say "Sql Server". But frankly I'm not very
> confident at this point. How many people on this board
> have databases larger than 100 G. Is it feasible to do
> this on Sql Server ? I dont want to say Sql Server and
> have to eat my words later. Any facts are welcome from
> live installations > 100 G.
> Thanks,
> Jack

---

Database/Table File Sizes

I would like to get some stats out of a database Mainly I would like to get
the no records in each table and if possible the size of each table, not
sure if the latter is possible?
Thanks
DerrickDerrick,
Try using "dbcc checktable"
eg:
use db_audit
go
dbcc checktable('tblaudit')
go
DBCC results for 'tblAudit'.
There are 126452 rows in 1305 pages for object 'tblAudit'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Each page in SQL Server 2000 is 8kb. I'm not sure about other versions.
I'm fairly sure you can use "dbcc checkdb" to return information about
all of the tables in a database in a similar form.
I hope this helps,
Danny.
"Derrick King" <derrick.king@.bradford.gov.uk> wrote in message
news:belrgl$8hl$1@.newsreaderm1.core.theplanet.net...
> I would like to get some stats out of a database Mainly I would like to
get
> the no records in each table and if possible the size of each table, not
> sure if the latter is possible?
> Thanks
> Derrick
>|||> Each page in SQL Server 2000 is 8kb. I'm not sure about other versions.
> I'm fairly sure you can use "dbcc checkdb" to return information about
> all of the tables in a database in a similar form.
Yes, DBCC CHECKDB can also used. Also, DBCC CHECKDB should return accurate info. Note that
CHECKDB also does physical checking, so it might take a while.
One thing about my first suggestion: You might want to run DBCC UPDATEUSAGE using the COUNT_ROWS
parameter to update rowcount as well as space usage.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Daniel Johnson" <danny@.sysola.net> wrote in message news:belsc8$9h2$1@.titan.btinternet.com...
> Derrick,
> Try using "dbcc checktable"
> eg:
> use db_audit
> go
> dbcc checktable('tblaudit')
> go
> DBCC results for 'tblAudit'.
> There are 126452 rows in 1305 pages for object 'tblAudit'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Each page in SQL Server 2000 is 8kb. I'm not sure about other versions.
> I'm fairly sure you can use "dbcc checkdb" to return information about
> all of the tables in a database in a similar form.
> I hope this helps,
> Danny.
>
> "Derrick King" <derrick.king@.bradford.gov.uk> wrote in message
> news:belrgl$8hl$1@.newsreaderm1.core.theplanet.net...
> > I would like to get some stats out of a database Mainly I would like to
> get
> > the no records in each table and if possible the size of each table, not
> > sure if the latter is possible?
> >
> > Thanks
> >
> > Derrick
> >
> >
>