Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
blown
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> shrink the file or
> message
> blown
Showing posts with label particular. Show all posts
Showing posts with label particular. Show all posts
Sunday, March 25, 2012
Database Size Blowout - I mean like HUGE!
Has anybody come across a problem where their database
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
>> Has anybody come across a problem where their database
>> file becomes enormous for no particular reason?
>> I have checked all user and system tables, and they are
>> correct. It seems that in 3 days the database has
blown
>> out from 400MB to 18500MB.
>> Any ideas/suggestions would be greatly appreciated.
>> Thank you
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> >--Original Message--
> >Is it the database or log? run:
> >
> >dbcc sqlperf('logspace')
> >
> >and see if it is your log file, if it is, back it up and
> shrink the file or
> >see if you have any open transactions "dbcc opentran"
> >
> >HTH
> >
> >--
> >Ray Higdon MCSE, MCDBA, CCNA
> >--
> >"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
> message
> >news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> >> Has anybody come across a problem where their database
> >> file becomes enormous for no particular reason?
> >>
> >> I have checked all user and system tables, and they are
> >> correct. It seems that in 3 days the database has
> blown
> >> out from 400MB to 18500MB.
> >>
> >> Any ideas/suggestions would be greatly appreciated.
> >>
> >> Thank you
> >
> >
> >.
> >
file becomes enormous for no particular reason?
I have checked all user and system tables, and they are
correct. It seems that in 3 days the database has blown
out from 400MB to 18500MB.
Any ideas/suggestions would be greatly appreciated.
Thank youIs it the database or log? run:
dbcc sqlperf('logspace')
and see if it is your log file, if it is, back it up and shrink the file or
see if you have any open transactions "dbcc opentran"
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||somebody might have pumped in huge data and db might be in full recovery
mode .. check log size, truncate log ,shrink db u shall gain yur db size
again.
i agree with ray
run dbcc sqlperf('logspace')
u'll know abt the log size and do the above mentioned steps.
backup file will be huge, if u have enough space back it up first to be on a
safer side.
Regards,
Mayur
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> Has anybody come across a problem where their database
> file becomes enormous for no particular reason?
> I have checked all user and system tables, and they are
> correct. It seems that in 3 days the database has blown
> out from 400MB to 18500MB.
> Any ideas/suggestions would be greatly appreciated.
> Thank you|||I've already checked that. The log is currently using
50MB, whilst the PRIMARY database file is using 18329MB.
I've checked all the tables, and the row counts are what
they should be, back when the db was about 400MB.
Bizarre
>--Original Message--
>Is it the database or log? run:
>dbcc sqlperf('logspace')
>and see if it is your log file, if it is, back it up and
shrink the file or
>see if you have any open transactions "dbcc opentran"
>HTH
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
message
>news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
>> Has anybody come across a problem where their database
>> file becomes enormous for no particular reason?
>> I have checked all user and system tables, and they are
>> correct. It seems that in 3 days the database has
blown
>> out from 400MB to 18500MB.
>> Any ideas/suggestions would be greatly appreciated.
>> Thank you
>
>.
>|||Indexes?
http://vyaskn.tripod.com/code/sp_show_huge_tables.txt
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in message
news:f0be01c3f0f4$c4533620$a501280a@.phx.gbl...
> I've already checked that. The log is currently using
> 50MB, whilst the PRIMARY database file is using 18329MB.
> I've checked all the tables, and the row counts are what
> they should be, back when the db was about 400MB.
> Bizarre
>
> >--Original Message--
> >Is it the database or log? run:
> >
> >dbcc sqlperf('logspace')
> >
> >and see if it is your log file, if it is, back it up and
> shrink the file or
> >see if you have any open transactions "dbcc opentran"
> >
> >HTH
> >
> >--
> >Ray Higdon MCSE, MCDBA, CCNA
> >--
> >"Nathan Day" <nathand@.stanthorpe.qld.gov.au> wrote in
> message
> >news:d94c01c3f03b$e1ef7810$a101280a@.phx.gbl...
> >> Has anybody come across a problem where their database
> >> file becomes enormous for no particular reason?
> >>
> >> I have checked all user and system tables, and they are
> >> correct. It seems that in 3 days the database has
> blown
> >> out from 400MB to 18500MB.
> >>
> >> Any ideas/suggestions would be greatly appreciated.
> >>
> >> Thank you
> >
> >
> >.
> >
Monday, March 19, 2012
Database Schema Documentation Tool?
I am looking for a database documentation tool/script that I can run against
a particular database to generate a document of all the tables, columns, and
relationships in the database. I have seen a data dictionary that lists the
table names with links to the table details further down the document, and
clicking on the relationships jumps you to that table in the document. That
particular one was generated out of the programming revision control system
they were using into xml and xsl files. I am looking for something that can
generate the information out of the metadata in SQL Server.
Thanks
I think you may be thinking of Enterprise Architect
http://www.sparxsystems.com.au/
Or ER/Studio
http://www.embarcadero.com/products/erstudio/index.html
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||Look at ApexSQL Doc from www.ApexSQL.com
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||I love ApexSQL . See the details from below URL:-
http://www.sql-server-performance.com/apex_sql_doc_spotlight.asp
http://www.apexsql.com/sql_tools_doc.asp
You could try the trial version and use it for a month:-
http://www.apexsql.com/downloads.asp
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OyNRBReFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Look at ApexSQL Doc from www.ApexSQL.com
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Greg Hess" <keadrix@.hotmail.com> wrote in message
> news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Another effective but much less costly option if you only want
documentation is
SqlSpec from ElsaSoft
Their website at www.elsasoft.org has a trial version and also samples
of the output
T
|||Greg Hess wrote:
> I am looking for a database documentation tool/script that I can run against
> a particular database to generate a document of all the tables, columns, and
> relationships in the database. I have seen a data dictionary that lists the
> table names with links to the table details further down the document, and
> clicking on the relationships jumps you to that table in the document. That
> particular one was generated out of the programming revision control system
> they were using into xml and xsl files. I am looking for something that can
> generate the information out of the metadata in SQL Server.
> Thanks
You might want to try SchemaToDoc for SQL Server
(http://www.schematodoc.com). It exports to a Word doc metadata info
such as primary keys, field info (types, size, nullable, defaults),
indexes, check constraints, foreign key constraints, triggers, views,
stored procedures, and extended properties. It also lets you annotate
your tables and fields and include those comments in the Word doc. An
Enterprise edition can create a series of linked HTML files in addition
to the Word output.
|||Thank you for all your suggestions. After evaluating them I have decided to
go with SqlSpec from Elsasoft (http://www.elsasoft.org/). It does exactly
what I need it to do for a good price.
a particular database to generate a document of all the tables, columns, and
relationships in the database. I have seen a data dictionary that lists the
table names with links to the table details further down the document, and
clicking on the relationships jumps you to that table in the document. That
particular one was generated out of the programming revision control system
they were using into xml and xsl files. I am looking for something that can
generate the information out of the metadata in SQL Server.
Thanks
I think you may be thinking of Enterprise Architect
http://www.sparxsystems.com.au/
Or ER/Studio
http://www.embarcadero.com/products/erstudio/index.html
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||Look at ApexSQL Doc from www.ApexSQL.com
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Greg Hess" <keadrix@.hotmail.com> wrote in message
news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I am looking for a database documentation tool/script that I can run
>against a particular database to generate a document of all the tables,
>columns, and relationships in the database. I have seen a data dictionary
>that lists the table names with links to the table details further down the
>document, and clicking on the relationships jumps you to that table in the
>document. That particular one was generated out of the programming
>revision control system they were using into xml and xsl files. I am
>looking for something that can generate the information out of the metadata
>in SQL Server.
> Thanks
>
|||I love ApexSQL . See the details from below URL:-
http://www.sql-server-performance.com/apex_sql_doc_spotlight.asp
http://www.apexsql.com/sql_tools_doc.asp
You could try the trial version and use it for a month:-
http://www.apexsql.com/downloads.asp
Thanks
Hari
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OyNRBReFHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Look at ApexSQL Doc from www.ApexSQL.com
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Greg Hess" <keadrix@.hotmail.com> wrote in message
> news:uiaR8UcFHHA.1252@.TK2MSFTNGP02.phx.gbl...
>
|||Another effective but much less costly option if you only want
documentation is
SqlSpec from ElsaSoft
Their website at www.elsasoft.org has a trial version and also samples
of the output
T
|||Greg Hess wrote:
> I am looking for a database documentation tool/script that I can run against
> a particular database to generate a document of all the tables, columns, and
> relationships in the database. I have seen a data dictionary that lists the
> table names with links to the table details further down the document, and
> clicking on the relationships jumps you to that table in the document. That
> particular one was generated out of the programming revision control system
> they were using into xml and xsl files. I am looking for something that can
> generate the information out of the metadata in SQL Server.
> Thanks
You might want to try SchemaToDoc for SQL Server
(http://www.schematodoc.com). It exports to a Word doc metadata info
such as primary keys, field info (types, size, nullable, defaults),
indexes, check constraints, foreign key constraints, triggers, views,
stored procedures, and extended properties. It also lets you annotate
your tables and fields and include those comments in the Word doc. An
Enterprise edition can create a series of linked HTML files in addition
to the Word output.
|||Thank you for all your suggestions. After evaluating them I have decided to
go with SqlSpec from Elsasoft (http://www.elsasoft.org/). It does exactly
what I need it to do for a good price.
Tuesday, February 14, 2012
Database Owner Problems 2005
I am using SQL Server 2005 and I was having a problem with the owner of a particular database. When I go into Database Diagrams for the database I get:
"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
So I tried some different things (assigning different users as the owner) I was only able to assign one user as the owner (someone that is no longer working here), then I deleted that user (oops.). Now I still don't have an owner assigned AND I can't get into the database properties because I don't have permissions.
Any help would be appreciated.You can set sa as the owner of a db:
USE Mydb
GO
EXEC dbo.sp_changedbowner @.loginame = N'sa', @.map = false
GO
It does sound, though, like you don't have eleveated permissions. You might need to log in as sa or get a sysadmin in to run this.|||That worked. Thanks. The thing that was killing me is that I have the permissions to do it. I am the administrator, user, etc. It is mne and another person that have complete access to the database. We are migrating to SQL Sever 2005 from Access.
If you don't mind answering another question regarding SQL Server security. For this particular database, I try to transfer ownership to my login (which is mapped to my network login, Windows Authentication) and I get the following error:
"The proposed new database owner CORE_PROCESS\dkunkle is mapped as user CORE_PROCESS\dkunkle in this database. Parameter name: CORE_PROCESS\dkunkle"
Any thoughts on this error? My login is the owner of another database. This database was created/developed on another system, then moved to the server.|||If you don't mind answering another question regarding SQL Server security. omg - what a liberty :rolleyes:
I think this means that you are trying to map the owner to a login which is already mapped to a user in the database.
Not certain though. What are you executing?|||Poots has got it ... here's the pertinent section from 2005 BOL
sp_changedbowner [ @.loginame = ] 'login' [ , [ @.map= ] remap_alias_flag ]
http://www.dbforums.com/ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/local/collapse.gif</IMG>Arguments
[ @.loginame = ] 'login' Is the login ID of the new owner of the current database. login is sysname, with no default. login must be an already existing SQL Server login or Microsoft Windows user. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first.
[ @.map = ] remap_alias_flag Is the value true or false, which indicates whether existing aliases assigned to the old database owner (dbo) are mapped to the new owner of the current database or dropped. remap_alias_flag is varchar(5), with a default of NULL. This indicates that any existing aliases to the old dbo are mapped to the new owner of the current database. false indicates that existing aliases to the old database owner are dropped.
"Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects."
So I tried some different things (assigning different users as the owner) I was only able to assign one user as the owner (someone that is no longer working here), then I deleted that user (oops.). Now I still don't have an owner assigned AND I can't get into the database properties because I don't have permissions.
Any help would be appreciated.You can set sa as the owner of a db:
USE Mydb
GO
EXEC dbo.sp_changedbowner @.loginame = N'sa', @.map = false
GO
It does sound, though, like you don't have eleveated permissions. You might need to log in as sa or get a sysadmin in to run this.|||That worked. Thanks. The thing that was killing me is that I have the permissions to do it. I am the administrator, user, etc. It is mne and another person that have complete access to the database. We are migrating to SQL Sever 2005 from Access.
If you don't mind answering another question regarding SQL Server security. For this particular database, I try to transfer ownership to my login (which is mapped to my network login, Windows Authentication) and I get the following error:
"The proposed new database owner CORE_PROCESS\dkunkle is mapped as user CORE_PROCESS\dkunkle in this database. Parameter name: CORE_PROCESS\dkunkle"
Any thoughts on this error? My login is the owner of another database. This database was created/developed on another system, then moved to the server.|||If you don't mind answering another question regarding SQL Server security. omg - what a liberty :rolleyes:
I think this means that you are trying to map the owner to a login which is already mapped to a user in the database.
Not certain though. What are you executing?|||Poots has got it ... here's the pertinent section from 2005 BOL
sp_changedbowner [ @.loginame = ] 'login' [ , [ @.map= ] remap_alias_flag ]
http://www.dbforums.com/ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/local/collapse.gif</IMG>Arguments
[ @.loginame = ] 'login' Is the login ID of the new owner of the current database. login is sysname, with no default. login must be an already existing SQL Server login or Microsoft Windows user. login cannot become the owner of the current database if it already has access to the database through an existing alias or user security account within the database. To avoid this, drop the alias or user within the current database first.
[ @.map = ] remap_alias_flag Is the value true or false, which indicates whether existing aliases assigned to the old database owner (dbo) are mapped to the new owner of the current database or dropped. remap_alias_flag is varchar(5), with a default of NULL. This indicates that any existing aliases to the old dbo are mapped to the new owner of the current database. false indicates that existing aliases to the old database owner are dropped.
Subscribe to:
Posts (Atom)