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 Schedule Displayed

Hi,
I am creating a data driven subscription via code using a shared schedule
and it seems fine, but when I view the subscription in Report Manager it
shows the wrong schedule.
I have checked the MatchData field in the Subscriptions table and the
ScheduleID in the ReportSchedules table and both of these have the correct
ID.
any ideas?
thanks
MattOh, I should add that the subscription runs correctly. It just displays a
different schedule to the one I have assigned on the "Specify When the
subscription is processed." page. Note, if I set the schedule here, it
seems to remember it.
thanks again
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am creating a data driven subscription via code using a shared schedule
> and it seems fine, but when I view the subscription in Report Manager it
> shows the wrong schedule.
> I have checked the MatchData field in the Subscriptions table and the
> ScheduleID in the ReportSchedules table and both of these have the correct
> ID.
> any ideas?
> thanks
> Matt
>|||Is it possible for you to share your code? You can send it directly to me
if you want and I can take a look. Just remove the online from my address.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:#NVQ#z2gEHA.1972@.TK2MSFTNGP09.phx.gbl...
> Oh, I should add that the subscription runs correctly. It just displays a
> different schedule to the one I have assigned on the "Specify When the
> subscription is processed." page. Note, if I set the schedule here, it
> seems to remember it.
> thanks again
>
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:O40uAJ2gEHA.712@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > I am creating a data driven subscription via code using a shared
schedule
> > and it seems fine, but when I view the subscription in Report Manager it
> > shows the wrong schedule.
> >
> > I have checked the MatchData field in the Subscriptions table and the
> > ScheduleID in the ReportSchedules table and both of these have the
correct
> > ID.
> >
> > any ideas?
> >
> > thanks
> >
> > Matt
> >
> >
>

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?
>

Include sales information about sales manager

I need to extract sale informations about every employee in a parentchild dimension, regardless of hierarchy.

The problem is, when an employee is a manager, his sales amount includes aggregated information associated with him (that is his own Sales + sales of the employees reporting to him).
According to BOL, his personal sales amount is stored in DataMember (system-generated member).

Using MDX, how can I extract the "own" sale amount of every employee, whether he is a manager or not.

After a long fight with MDX, I am able to filter out "non-leaf-items", but I am not able to extract the "DataMember" part of nonleaf members.

Any clue is welcome.

To illustrate my problem, below are the numbers shown by "Adventure Works" and the numbers I need to extract.

Browsing Adventure Works cube

Filter: Date.[Calendar Year] = {CY 2004}

Detail Fields: [Measures].[Reseller Sales Amount]

Row Fields: [Employee].[Employees]

Amy E. Alberts (under Ken J. Sanchez and Brian S. Welcker) shows a total sales amount of $4,110,734.65, distributed as follow

Jae B. Pak $1,808,043.26

Rachel B. Valdez $829,512.64

Ranjit R. Varkey Chudukatil $1,374,855.78

total incl. Amy E. Alberts) $4,110,734.65

Amy E. Alberts $98,322.97 (this is not shown in the browser)

I need to extract:

Jae B. Pak $1,808,043.26

Rachel B. Valdez $829,512.64

Ranjit R. Varkey Chudukatil $1,374,855.78

Amy E. Alberts $98,322.97

Thanks.

Ren

Hi

Just a guess, not tested, but how about subtracting the descendant employee's sales if any descendants exist?

Regards

Chris

|||

I looked at many solutions but I am not able to find out the most efficient.

It seems to be an obvious problem and solution is not easy to find.