Increased Backup size for unknown reason

Does any one know what could be making the size of a backup get so big in SQL server?

I noticed if you back-up the same database in Enterprise Manager over and over (without making any changes to the database), the size of the backup gets bigger and bigger. To get around this I simply erase the backup and create a new one.

Now I'm experiencing the same kind of problem, different situation. I decided to make very few changes to my database. If anything, I shrunk the size of the tables and stored procedures... Now all of a sudden my database backup is 7 times larger.

What could be increasing the size so much, if I haven't increased the amount of tables or stored procedures?

What is the log file about? Mine is huge? Is there a way to reset it or clear it?

any help would be great.

Thank you,

AlecIf you're sure you're not making any changes are you adding/appending to the backup rather than overwriting?

Incorrect user login information showing in Enterprise Manager

When I check properties for database user x the login name says domain1\x .
If I delete that login from the server then look at the user x's properties
again it still says domain1\x in the login name!
How can this be fixed?Eric,
Since you say Enterprise Manager, I assume that you are using SQL Server
2000.
If I delete a login in SQL Server 2000 EM, it goes through and deletes the
users.
If I "sp_revokelogin 'domain1\x'" it still leaves the 'x' user behind, and I
can still see 'domain1\x' in EM if I was looking at it earlier. But, once I
refresh the EM user view I still see user 'x' but with a blank login.
EM does have some latency in refreshing (refresh a couple of times may be
necessary). Could that be your problem?
If not, have you done anything out of the ordinary, such as restoring a
database from another server, or even another domain?
RLF
"EricW" <ewientzek@.hotmail.com> wrote in message
news:OA8zHbd1HHA.4672@.TK2MSFTNGP05.phx.gbl...
> When I check properties for database user x the login name says domain1\x
> . If I delete that login from the server then look at the user x's
> properties again it still says domain1\x in the login name!
> How can this be fixed?
>
>|||I'm speaking about Managemenst Studio in SQL 2005.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eQrTdlg1HHA.4500@.TK2MSFTNGP02.phx.gbl...
> Eric,
> Since you say Enterprise Manager, I assume that you are using SQL Server
> 2000.
> If I delete a login in SQL Server 2000 EM, it goes through and deletes the
> users.
> If I "sp_revokelogin 'domain1\x'" it still leaves the 'x' user behind, and
> I can still see 'domain1\x' in EM if I was looking at it earlier. But,
> once I refresh the EM user view I still see user 'x' but with a blank
> login.
> EM does have some latency in refreshing (refresh a couple of times may be
> necessary). Could that be your problem?
> If not, have you done anything out of the ordinary, such as restoring a
> database from another server, or even another domain?
> RLF
> "EricW" <ewientzek@.hotmail.com> wrote in message
> news:OA8zHbd1HHA.4672@.TK2MSFTNGP05.phx.gbl...
>|||Eric W,
OK, you are using SQL 2005.
When using SSMS you delete a login, you will get this message: "Deleting
server logins does not delete the database users associated with the logins.
To complete the process, delete the users in each database. It may be
necessary to first transfer the ownership of schemas to new users."
But your question is" "Why does the user entry still know the login name?"
The answer is that it records the SID in the user. If you:
select * from sys.database_principals
you will see the SIDs of the logins used to create the users. In fact, if
you copy the SID for a deleted Windows login and paste it into:
SELECT SUSER_SNAME(0x0...9)
it will still return the name of the Windows Login. (In SQL Server 2000,
sysusers maintained the login's SID, but since the rows were usually deleted
automatically, you never saw this behavior manifested.)
To get rid of this, you must also drop the user yourself. Which may mean
that you must first drop that user's schema.
RLF
"EricW" <ewientzek@.hotmail.com> wrote in message
news:OaZe5gP2HHA.4476@.TK2MSFTNGP06.phx.gbl...
> I'm speaking about Managemenst Studio in SQL 2005.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eQrTdlg1HHA.4500@.TK2MSFTNGP02.phx.gbl...
>

incorrect information in syscolumns

Hi,
using sql server 2000 enterprise manager i altered some column's
length. however, the information was not updated in syscolumns (neither
sp_help shows the right information... it shows the old information
before my update). anybody seen this problem before? following comments
didn't help either:
DBCC CHECKTABLE
DBCC CLEANTABLE
DBCC CHECKCATALOG
Why does it happen? any idea on how to solve it? thanks!ALTER TABLE table_name ALTER COLUMN column_name VARCHAR(60)
HTH. Ryan
<yoni@.nobhillsoft.com> wrote in message
news:1146147097.080242.106330@.i40g2000cwc.googlegroups.com...
> Hi,
> using sql server 2000 enterprise manager i altered some column's
> length. however, the information was not updated in syscolumns (neither
> sp_help shows the right information... it shows the old information
> before my update). anybody seen this problem before? following comments
> didn't help either:
> DBCC CHECKTABLE
> DBCC CLEANTABLE
> DBCC CHECKCATALOG
> Why does it happen? any idea on how to solve it? thanks!
>|||Ryan,
what do you mean by that? running ALTER statements? the problem is,
someone already ALTERed the table,a nd the chagnes dont reflect in the
sys tables. beyond this specific table, i have to wonter how many more
times did SQL Server screw up, because we are querying the tables daily
for changes. is there any commant or anything to rebuild all these
tables, or in any way fix the corruption? thanks|||Rather than using EM you can acheive the same result in Query Analyser using
the ALTER STATEMENT i supplied.
I've not experienced the problems you are seeing with EM. What version of
SQL / Service Pack are you running.
SELECT @.@.VERSION
HTH. Ryan
<yoni@.nobhillsoft.com> wrote in message
news:1146148823.202698.316100@.u72g2000cwu.googlegroups.com...
> Ryan,
> what do you mean by that? running ALTER statements? the problem is,
> someone already ALTERed the table,a nd the chagnes dont reflect in the
> sys tables. beyond this specific table, i have to wonter how many more
> times did SQL Server screw up, because we are querying the tables daily
> for changes. is there any commant or anything to rebuild all these
> tables, or in any way fix the corruption? thanks
>|||Sql Server 2000 SP 4. EM version 8.0 (if it matters at all)
my question is not on what i can do, but on how can i fix somehting
that's already done - some bug in SQL that causes the catalog (system
tables) to be out of sync. remember the DBCC commands i mentioned above
that did not work. BTW, running sp_MShelpcolumns DOES return the
correct information (MS obviously keep the best for themselfes)
so, i need to find some way to bring my system tables up-to-date
thanks|||Can you provide a repro? Also, sp_MShelpcolumns gets its information from th
e system tables, so you
might want to take a look at the source code to see what it does differently
from what you do.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yoni@.nobhillsoft.com> wrote in message news:1146151671.533212.62710@.i39g2000cwa.googlegroup
s.com...
> Sql Server 2000 SP 4. EM version 8.0 (if it matters at all)
> my question is not on what i can do, but on how can i fix somehting
> that's already done - some bug in SQL that causes the catalog (system
> tables) to be out of sync. remember the DBCC commands i mentioned above
> that did not work. BTW, running sp_MShelpcolumns DOES return the
> correct information (MS obviously keep the best for themselfes)
> so, i need to find some way to bring my system tables up-to-date
> thanks
>|||Sure, here is a reprop:
sp_help vendor_mig_out
produces:
vendor_num\nvarchar\no\12...
data_dest_code\int\no\4
duns#\nvarchar\no\22
name\nvarchar\no\70
address\nnvarchar\no\70
and on... now look at
sp_MShelpcolumns N'dbo.vendor_mig_out', null, 'id', 1
i get:
vendor_num\nvarchar\6
data_dest_code\int\4
duns#\nvarchar\11
name\nvarchar\35
address\nvarchar\35
and on... i trimmed the output here because its so large, but
specifically look at the field lengths... that's what i changed in EM
GUI and that's how i suddenly saw that it didn't change in system
tables...
thanks!|||That doesn't help us reproduce the situation, it only tell us what it look l
ike at your system right
now.Anyhow, perhaps you have to tables named vendor_mig_out? One owned by db
o and another owned by
the user you are currently using? Try:
EXEC sp_help 'dbo.vendor_mig_out'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yoni@.nobhillsoft.com> wrote in message
news:1146161498.084957.230330@.v46g2000cwv.googlegroups.com...
> Sure, here is a reprop:
> sp_help vendor_mig_out
> produces:
> vendor_num\nvarchar\no\12...
> data_dest_code\int\no\4
> duns#\nvarchar\no\22
> name\nvarchar\no\70
> address\nnvarchar\no\70
> and on... now look at
> sp_MShelpcolumns N'dbo.vendor_mig_out', null, 'id', 1
> i get:
> vendor_num\nvarchar\6
> data_dest_code\int\4
> duns#\nvarchar\11
> name\nvarchar\35
> address\nvarchar\35
> and on... i trimmed the output here because its so large, but
> specifically look at the field lengths... that's what i changed in EM
> GUI and that's how i suddenly saw that it didn't change in system
> tables...
> thanks!
>|||Hi Tibor,
well, no, that's not it. i only got one table. i admit its a very
strange behavior. i can't reproduce it even...it just happened with
this table, and i dont know with how many other tables, which is what
really worries me. and like i said the DBCC dont help. any other idea?|||If sp_help return something different from sp_MShelpcolumns and if sp_MShelp
columns contains the
correct information, I would be a bit worried. Consider opening a case with
MS, and be prepared that
without repro, they might need access to the database, in some way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yoni@.nobhillsoft.com> wrote in message
news:1146228517.310431.306940@.g10g2000cwb.googlegroups.com...
> Hi Tibor,
> well, no, that's not it. i only got one table. i admit its a very
> strange behavior. i can't reproduce it even...it just happened with
> this table, and i dont know with how many other tables, which is what
> really worries me. and like i said the DBCC dont help. any other idea?
>

incorrect information in syscolumns

Hi,
using sql server 2000 enterprise manager i altered some column's
length. however, the information was not updated in syscolumns (neither
sp_help shows the right information... it shows the old information
before my update). anybody seen this problem before? following comments
didn't help either:
DBCC CHECKTABLE
DBCC CLEANTABLE
DBCC CHECKCATALOG
Why does it happen? any idea on how to solve it? thanks!ALTER TABLE table_name ALTER COLUMN column_name VARCHAR(60)
--
HTH. Ryan
<yoni@.nobhillsoft.com> wrote in message
news:1146147097.080242.106330@.i40g2000cwc.googlegroups.com...
> Hi,
> using sql server 2000 enterprise manager i altered some column's
> length. however, the information was not updated in syscolumns (neither
> sp_help shows the right information... it shows the old information
> before my update). anybody seen this problem before? following comments
> didn't help either:
> DBCC CHECKTABLE
> DBCC CLEANTABLE
> DBCC CHECKCATALOG
> Why does it happen? any idea on how to solve it? thanks!
>|||Ryan,
what do you mean by that? running ALTER statements? the problem is,
someone already ALTERed the table,a nd the chagnes dont reflect in the
sys tables. beyond this specific table, i have to wonter how many more
times did SQL Server screw up, because we are querying the tables daily
for changes. is there any commant or anything to rebuild all these
tables, or in any way fix the corruption? thanks|||Rather than using EM you can acheive the same result in Query Analyser using
the ALTER STATEMENT i supplied.
I've not experienced the problems you are seeing with EM. What version of
SQL / Service Pack are you running.
SELECT @.@.VERSION
HTH. Ryan
<yoni@.nobhillsoft.com> wrote in message
news:1146148823.202698.316100@.u72g2000cwu.googlegroups.com...
> Ryan,
> what do you mean by that? running ALTER statements? the problem is,
> someone already ALTERed the table,a nd the chagnes dont reflect in the
> sys tables. beyond this specific table, i have to wonter how many more
> times did SQL Server screw up, because we are querying the tables daily
> for changes. is there any commant or anything to rebuild all these
> tables, or in any way fix the corruption? thanks
>|||Sql Server 2000 SP 4. EM version 8.0 (if it matters at all)
my question is not on what i can do, but on how can i fix somehting
that's already done - some bug in SQL that causes the catalog (system
tables) to be out of sync. remember the DBCC commands i mentioned above
that did not work. BTW, running sp_MShelpcolumns DOES return the
correct information (MS obviously keep the best for themselfes)
so, i need to find some way to bring my system tables up-to-date
thanks|||Can you provide a repro? Also, sp_MShelpcolumns gets its information from the system tables, so you
might want to take a look at the source code to see what it does differently from what you do.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yoni@.nobhillsoft.com> wrote in message news:1146151671.533212.62710@.i39g2000cwa.googlegroups.com...
> Sql Server 2000 SP 4. EM version 8.0 (if it matters at all)
> my question is not on what i can do, but on how can i fix somehting
> that's already done - some bug in SQL that causes the catalog (system
> tables) to be out of sync. remember the DBCC commands i mentioned above
> that did not work. BTW, running sp_MShelpcolumns DOES return the
> correct information (MS obviously keep the best for themselfes)
> so, i need to find some way to bring my system tables up-to-date
> thanks
>|||Sure, here is a reprop:
sp_help vendor_mig_out
produces:
vendor_num\nvarchar\no\12...
data_dest_code\int\no\4
duns#\nvarchar\no\22
name\nvarchar\no\70
address\nnvarchar\no\70
and on... now look at
sp_MShelpcolumns N'dbo.vendor_mig_out', null, 'id', 1
i get:
vendor_num\nvarchar\6
data_dest_code\int\4
duns#\nvarchar\11
name\nvarchar\35
address\nvarchar\35
and on... i trimmed the output here because its so large, but
specifically look at the field lengths... that's what i changed in EM
GUI and that's how i suddenly saw that it didn't change in system
tables...
thanks!|||That doesn't help us reproduce the situation, it only tell us what it look like at your system right
now.Anyhow, perhaps you have to tables named vendor_mig_out? One owned by dbo and another owned by
the user you are currently using? Try:
EXEC sp_help 'dbo.vendor_mig_out'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yoni@.nobhillsoft.com> wrote in message
news:1146161498.084957.230330@.v46g2000cwv.googlegroups.com...
> Sure, here is a reprop:
> sp_help vendor_mig_out
> produces:
> vendor_num\nvarchar\no\12...
> data_dest_code\int\no\4
> duns#\nvarchar\no\22
> name\nvarchar\no\70
> address\nnvarchar\no\70
> and on... now look at
> sp_MShelpcolumns N'dbo.vendor_mig_out', null, 'id', 1
> i get:
> vendor_num\nvarchar\6
> data_dest_code\int\4
> duns#\nvarchar\11
> name\nvarchar\35
> address\nvarchar\35
> and on... i trimmed the output here because its so large, but
> specifically look at the field lengths... that's what i changed in EM
> GUI and that's how i suddenly saw that it didn't change in system
> tables...
> thanks!
>|||Hi Tibor,
well, no, that's not it. i only got one table. i admit its a very
strange behavior. i can't reproduce it even...it just happened with
this table, and i dont know with how many other tables, which is what
really worries me. and like i said the DBCC dont help. any other idea?|||If sp_help return something different from sp_MShelpcolumns and if sp_MShelpcolumns contains the
correct information, I would be a bit worried. Consider opening a case with MS, and be prepared that
without repro, they might need access to the database, in some way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<yoni@.nobhillsoft.com> wrote in message
news:1146228517.310431.306940@.g10g2000cwb.googlegroups.com...
> Hi Tibor,
> well, no, that's not it. i only got one table. i admit its a very
> strange behavior. i can't reproduce it even...it just happened with
> this table, and i dont know with how many other tables, which is what
> really worries me. and like i said the DBCC dont help. any other idea?
>

Incorrect Compatibility level Error 15414:

I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
John
This suggests you are using some capabilities available in SQL2000 that were
not previously available in down level versions. To check for indexed views
or indexes on computed columns run the following query in Query Analyzer.
These indexes will need to be dropped in order to downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John

Incorrect Compatibility level Error 15414:

I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
JohnThis suggests you are using some capabilities available in SQL2000 that were
not previously available in down level versions. To check for indexed views
or indexes on computed columns run the following query in Query Analyzer.
These indexes will need to be dropped in order to downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John

Incorrect Compatibility level Error 15414:

I have a SQL Server 2000 Enterprise Edition along with
SP3A with several databases. These databases are 20 GB in
size. On one of the databases the Compatibility level was
changed from 70 to 80. I need to change the compatiblity
level 80 to 70.
When tried to change the compatibilty level I received the
following error:
Error 15414: Cannot set compatibilty level because
database has a view or computed column is indexed. These
indexes require a SQL Server compatible database.
Please help me resolve this problem.
JohnThis suggests you are using some capabilities available in SQL2000 that were
not previously available in down level versions. To check for indexed views
or indexes on computed columns run the following query in Query Analyzer.
These indexes will need to be dropped in order to downgrade the database
compatability
-- indexed views
select name from sysobjects where xtype = 'V'
and objectproperty(id,'IsMSShipped')=0
and objectproperty(id,'IsIndexed')=1
-- indexed computed columns
select object_name(c.id),c.name
from syscolumns c join sysindexkeys k
on c.colid = k.colid and c.id=k.id
where columnproperty(c.id,c.name,'IsComputed')=1
and objectproperty(c.id,'IsMSShipped')=0
and objectproperty(c.id,'IsIndexed')=1
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:239601c427b6$d93c5f00$a101280a@.phx.gbl...
> I have a SQL Server 2000 Enterprise Edition along with
> SP3A with several databases. These databases are 20 GB in
> size. On one of the databases the Compatibility level was
> changed from 70 to 80. I need to change the compatiblity
> level 80 to 70.
> When tried to change the compatibilty level I received the
> following error:
> Error 15414: Cannot set compatibilty level because
> database has a view or computed column is indexed. These
> indexes require a SQL Server compatible database.
> Please help me resolve this problem.
> John

Inconsistent sp_spaceused

SQL 2000 Enterprise, SP4, 8.00.2175
sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
name rows reserved data index_size
unused
-- -- -- -- --
--
table 0 7248 KB 5032 KB 32 KB
2184 KB
How come my table has 0 records yet still occupies space?Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?|||Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
"Tibor Karaszi" wrote:
> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> > SQL 2000 Enterprise, SP4, 8.00.2175
> >
> > sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> >
> > name rows reserved data index_size
> > unused
> > -- -- -- -- --
> > --
> > table 0 7248 KB 5032 KB 32 KB
> > 2184 KB
> >
> > How come my table has 0 records yet still occupies space?
>
>|||Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken space.
> "Tibor Karaszi" wrote:
> > Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> > news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> > > SQL 2000 Enterprise, SP4, 8.00.2175
> > >
> > > sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> > >
> > > name rows reserved data index_size
> > > unused
> > > -- -- -- -- --
> > > --
> > > table 0 7248 KB 5032 KB 32 KB
> > > 2184 KB
> > >
> > > How come my table has 0 records yet still occupies space?
> >
> >
> >|||sp_spaceused is not guaranteed to provide actual, up-to-the-minute correct
values. DO NOT rely on it for such.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?

Inconsistent sp_spaceused

SQL 2000 Enterprise, SP4, 8.00.2175
sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
name rows reserved data index_size
unused
-- -- -- -- --
--
table 0 7248 KB 5032 KB 32 KB
2184 KB
How come my table has 0 records yet still occupies space?Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in me
ssage
news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
--
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?|||Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken spac
e.
"Tibor Karaszi" wrote:

> Check out DBCC UPDATEUSAGE and the ROWS_COUNT option.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message
> news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
>
>|||Leon Shargorodsky,
if you dropped a variable length column, you can reclaim the space using
"dbcc cleantable".
If it is a heap (table without clustered index), create a clustered index
and if you do not want to keep it then drop it.
If it is not a heap, use "dbcc dbreindex" or "alter index ... rebuild" if
you are using 2005.
AMB
"Leon Shargorodsky" wrote:
[vbcol=seagreen]
> Nope, didn't help a bit: still shows 0 records yet roughly 7MB of taken sp
ace.
> "Tibor Karaszi" wrote:
>|||sp_spaceused is not guaranteed to provide actual, up-to-the-minute correct
values. DO NOT rely on it for such.
TheSQLGuru
President
Indicium Resources, Inc.
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:A1FBD362-9CB9-41F1-A57A-6662C4BFF5A8@.microsoft.com...
> SQL 2000 Enterprise, SP4, 8.00.2175
> sp_spaceused 'table', @.updateusage = 'TRUE' returns this:
> name rows reserved data index_size
> unused
> -- -- -- -- --
--
> --
> table 0 7248 KB 5032 KB 32 KB
> 2184 KB
> How come my table has 0 records yet still occupies space?