sqlserver2000 date: size

Showing posts with label size. Show all posts

Friday, March 30, 2012

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?

increase size of varchar column.. table being replicated..

I have a table that is being replicated.. i would like to
increase the size of this column.. Is there a way to do
this without dropping the subscription?
Thanks,
niv
It can be done indirectly but it's not nice! You could add a new column with
the new datatype (sp_repladdcolumn), do an update on the table to populate
the column, then drop the column (sp_repldropcolumn). Do this again to
create the column having the same original name.
Alternatively, as you say, you can drop the publication then recreate from
scratch.
We're hoping that such things will be simpler in SQL Server 2005.
Regards,
Paul Ibison
sql

Increase or Decrease Texbox to accomadate text

How would I go about seting my text box so that it will increase or decrease
in size to fit my text. Here's what expression I got in my textbox:
= First(Fields!NAME.Value, "FIN") & vbcrlf &
First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") & "
" & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
First(Fields!COUNTRY.Value, "FIN")
The thing is that some contacts will not have address 2 and 3. This will
leave the result like this:
ABC Company
123 South Drive
AppleTown, MN 65343
This is the result that I want if there are no address 2 and 3:
ABC Company
123 South Drive
AppleTown, MN 65343Hi,
There is no property to achieve this
Instead you can use an if condition and display the value only if it exists.
Hope this helps.
Ponnurangam.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:7E565BDD-B1C2-48B2-8A6A-81BEE00BA16D@.microsoft.com...
> How would I go about seting my text box so that it will increase or
> decrease
> in size to fit my text. Here's what expression I got in my textbox:
> = First(Fields!NAME.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_1.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_2.Value, "FIN") & vbcrlf &
> First(Fields!ADDR_3.Value, "FIN") & vbcrlf &
> First(Fields!CITY.Value, "FIN") & "," & First(Fields!STATE.Value, "FIN") &
> "
> " & First(Fields!ZIPCODE.Value, "FIN") & vbcrlf &
> First(Fields!COUNTRY.Value, "FIN")
> The thing is that some contacts will not have address 2 and 3. This will
> leave the result like this:
> ABC Company
> 123 South Drive
>
> AppleTown, MN 65343
> This is the result that I want if there are no address 2 and 3:
> ABC Company
> 123 South Drive
> AppleTown, MN 65343|||Remember, the Height property can also be an expression.
Maybe you can come up with some smart logic.sql

increase in the size of tempDB

Hi,
the size of the tempDB increased drastically all of a sudden.
How do i find out the cause for the same
thnks
anuIt could be just about anything. You can have a Profiler trace running and correlate what happened
at the time the size increased, but I don't know of a way to determine this after the fact (without
that profiler trace).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||thnks tibor...
im new to sql dba works...
can you help me out with some url / link / info on how i can reset the
size... itz grown to arond 130 GB now...
thnks in advance
anu
"Tibor Karaszi" wrote:
> It could be just about anything. You can have a Profiler trace running and correlate what happened
> at the time the size increased, but I don't know of a way to determine this after the fact (without
> that profiler trace).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > Hi,
> >
> > the size of the tempDB increased drastically all of a sudden.
> >
> > How do i find out the cause for the same
> >
> > thnks
> >
> > anu
>
>|||Ahuradha
The reason for growing temdbd database might be a long running query / lots
of sort operations that have done by the query
I think Aaron has written a great article at his site
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> Hi,
> the size of the tempDB increased drastically all of a sudden.
> How do i find out the cause for the same
> thnks
> anu|||Easiest way is to restart SQL Server, as tempdb is re-created each time SQL server starts (size for
tempdb at startup is drawn from master..sysaltfiles, which you can set using ALTER DATABASE)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:C415A0D1-6EDF-4D91-B878-205CE63A03F5@.microsoft.com...
> thnks tibor...
> im new to sql dba works...
> can you help me out with some url / link / info on how i can reset the
> size... itz grown to arond 130 GB now...
>
> thnks in advance
> anu
> "Tibor Karaszi" wrote:
> > It could be just about anything. You can have a Profiler trace running and correlate what
happened
> > at the time the size increased, but I don't know of a way to determine this after the fact
(without
> > that profiler trace).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> > news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > > Hi,
> > >
> > > the size of the tempDB increased drastically all of a sudden.
> > >
> > > How do i find out the cause for the same
> > >
> > > thnks
> > >
> > > anu
> >
> >
> >|||thnks uri,
can u pls mail me the url ?
"Uri Dimant" wrote:
> Ahuradha
> The reason for growing temdbd database might be a long running query / lots
> of sort operations that have done by the query
> I think Aaron has written a great article at his site
>
> "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > Hi,
> >
> > the size of the tempDB increased drastically all of a sudden.
> >
> > How do i find out the cause for the same
> >
> > thnks
> >
> > anu
>
>|||http://www.aspfaq.com/show.asp?id=2446
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
news:780932A8-2D49-4092-9D91-5C24E0D6AA43@.microsoft.com...
> thnks uri,
> can u pls mail me the url ?
>
> "Uri Dimant" wrote:
> > Ahuradha
> > The reason for growing temdbd database might be a long running query / lots
> > of sort operations that have done by the query
> > I think Aaron has written a great article at his site
> >
> >
> >
> > "Anuradha" <Anuradha@.discussions.microsoft.com> wrote in message
> > news:49F0462F-7925-45B0-B02A-6F22A520BD8F@.microsoft.com...
> > > Hi,
> > >
> > > the size of the tempDB increased drastically all of a sudden.
> > >
> > > How do i find out the cause for the same
> > >
> > > thnks
> > >
> > > anu
> >
> >
> >

Increase in Database size after restoration

we have taken a production database back up of size 1.8 GB.
we restored the database on a standalone system and the size of the database
was same.
We executed some scripts through which some records got inserted.
After the inserts the database size has drasticallly increased to 5.8 GB.
The database whose size is 5.8 GB was due to the increase in reservedspace
and unused spaces of the tables where in the records got inserted.
Then we exported the database through DTS and imported the data and shruk
the database to which the size of the database got reduced to 2 GB.
I wanted to know what is the reason for the increase in database size when
we executed the insert scripts. And how to avoid it. Or any approach by which
we can find the solution.
Read this article
http://www.sql-server-performance.co...e_settings.asp
"CUBak_DBSize" <CUBak_DBSize@.discussions.microsoft.com> wrote in message
news:3D114A81-F276-4FB9-A878-6C6712058129@.microsoft.com...
> we have taken a production database back up of size 1.8 GB.
> we restored the database on a standalone system and the size of the
database
> was same.
> We executed some scripts through which some records got inserted.
> After the inserts the database size has drasticallly increased to 5.8 GB.
> The database whose size is 5.8 GB was due to the increase in
reservedspace
> and unused spaces of the tables where in the records got inserted.
> Then we exported the database through DTS and imported the data and shruk
> the database to which the size of the database got reduced to 2 GB.
> I wanted to know what is the reason for the increase in database size when
> we executed the insert scripts. And how to avoid it. Or any approach by
which
> we can find the solution.
>

Increase in Database size after restoration

we have taken a production database back up of size 1.8 GB.
we restored the database on a standalone system and the size of the database
was same.
We executed some scripts through which some records got inserted.
After the inserts the database size has drasticallly increased to 5.8 GB.
The database whose size is 5.8 GB was due to the increase in reservedspace
and unused spaces of the tables where in the records got inserted.
Then we exported the database through DTS and imported the data and shruk
the database to which the size of the database got reduced to 2 GB.
I wanted to know what is the reason for the increase in database size when
we executed the insert scripts. And how to avoid it. Or any approach by whic
h
we can find the solution.Read this article
http://www.sql-server-performance.c...se_settings.asp
"CUBak_DBSize" <CUBak_DBSize@.discussions.microsoft.com> wrote in message
news:3D114A81-F276-4FB9-A878-6C6712058129@.microsoft.com...
> we have taken a production database back up of size 1.8 GB.
> we restored the database on a standalone system and the size of the
database
> was same.
> We executed some scripts through which some records got inserted.
> After the inserts the database size has drasticallly increased to 5.8 GB.
> The database whose size is 5.8 GB was due to the increase in
reservedspace
> and unused spaces of the tables where in the records got inserted.
> Then we exported the database through DTS and imported the data and shruk
> the database to which the size of the database got reduced to 2 GB.
> I wanted to know what is the reason for the increase in database size when
> we executed the insert scripts. And how to avoid it. Or any approach by
which
> we can find the solution.
>

Increase in Database size after restoration

we have taken a production database back up of size 1.8 GB.
we restored the database on a standalone system and the size of the database
was same.
We executed some scripts through which some records got inserted.
After the inserts the database size has drasticallly increased to 5.8 GB.
The database whose size is 5.8 GB was due to the increase in reservedspace
and unused spaces of the tables where in the records got inserted.
Then we exported the database through DTS and imported the data and shruk
the database to which the size of the database got reduced to 2 GB.
I wanted to know what is the reason for the increase in database size when
we executed the insert scripts. And how to avoid it. Or any approach by which
we can find the solution.Read this article
http://www.sql-server-performance.com/database_settings.asp
"CUBak_DBSize" <CUBak_DBSize@.discussions.microsoft.com> wrote in message
news:3D114A81-F276-4FB9-A878-6C6712058129@.microsoft.com...
> we have taken a production database back up of size 1.8 GB.
> we restored the database on a standalone system and the size of the
database
> was same.
> We executed some scripts through which some records got inserted.
> After the inserts the database size has drasticallly increased to 5.8 GB.
> The database whose size is 5.8 GB was due to the increase in
reservedspace
> and unused spaces of the tables where in the records got inserted.
> Then we exported the database through DTS and imported the data and shruk
> the database to which the size of the database got reduced to 2 GB.
> I wanted to know what is the reason for the increase in database size when
> we executed the insert scripts. And how to avoid it. Or any approach by
which
> we can find the solution.
>

Increase field size AND linked tables

I would like to increase a field size to allow input of up to 16 characters
versus the current 11, but the message is that I can't do the modification
due to linked tables. I believe the modification must be done at the server
,
but I wouldn't know where to start. It would seem that it should be an easy
fix if someone could point me in the right direction. I hate to call the
tech guys out to open a couple windows and type 16. The database was done i
n
access converted to SQL.
ThanksDear rtucker913,
Please post DDL or any example which clarifies your request.
Thanks in advance,
"rtucker913" wrote:

> I would like to increase a field size to allow input of up to 16 character
s
> versus the current 11, but the message is that I can't do the modification
> due to linked tables. I believe the modification must be done at the serv
er,
> but I wouldn't know where to start. It would seem that it should be an ea
sy
> fix if someone could point me in the right direction. I hate to call the
> tech guys out to open a couple windows and type 16. The database was done
in
> access converted to SQL.
> Thankssql

Increase Default Tlog size of Tempdb

Hello,
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MB
Just use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the desired size. The
specified file size will be persisted and used upon following startups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup script for Alter tempdb
> database.
> Thanks in advance.
> MB
>

Increase Default Tlog size of Tempdb

Hello,
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MBJust use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the de
sired size. The
specified file size will be persisted and used upon following startups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...en">
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But
the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup sc
ript for Alter tempdb
> database.
> Thanks in advance.
> MB
>

Increase Default Tlog size of Tempdb

Hello,
When we start the SQL Server default size of temp db is 8 MB. I would like
to make the default tempdb Data 200 MB and Tlog 100 MB.
For Data we can increase the size of model database and it works fine. But
the same is not working in case of Tlog.
Can anybody have idea how to do the set up? I don't want to use startup
script for Alter tempdb database.
Thanks in advance.
MBJust use ALTER DATABASE tempdb MODIFY FILE. Or use the GUI to specify the desired size. The
specified file size will be persisted and used upon following startups.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MB" <MB@.MB.com> wrote in message news:uXUN2MZrHHA.3228@.TK2MSFTNGP03.phx.gbl...
> Hello,
> When we start the SQL Server default size of temp db is 8 MB. I would like to make the default
> tempdb Data 200 MB and Tlog 100 MB.
> For Data we can increase the size of model database and it works fine. But the same is not working
> in case of Tlog.
> Can anybody have idea how to do the set up? I don't want to use startup script for Alter tempdb
> database.
> Thanks in advance.
> MB
>

Increase database size

I have a database that is over 45 GB and need to increase the size, can someone help me with the code to get it done?
ThanksFrom Books Online:

ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}

< filespec > ::=

( NAME = logical_file_name
[ , NEWNAME = new_logical_name ]
[ , FILENAME = 'os_file_name' ]
[ , SIZE = size ]
[ , MAXSIZE = { max_size | UNLIMITED } ]
[ , FILEGROWTH = growth_increment ] )|||Can you fill in a few blanks first? Normally a database will grow as needed, until it fills the available disk space. Is your database refusing to grow (a setting), or running out of free disk (meaning you need to start using a new drive for it to grow more)?

Another question is which part of your database is growing, the data or the log? It might be as simple as needing to do a log backup.

This question makes me leary... There are way too many ways to get into trouble if you don't understand this kind of problem!

-PatP|||The problem was that the log was NOT set to automatically grow. It's growth was restricted. The person that was in charge of the database before me realize the problem. I tell you it is a long process to becoming a GOOD DBA. God help me.

Lystra|||How long does the backup take?|||About 40 minutes, it about 33GB in size. That reminds me, when you backup a database are you suppose to get back log space? I thought you get it back when you use the with truncate_only.

Thanks|||How big are the ldf and mdf files on the server?

The ldf, once it's grown, will not shrink, unless you have autoshrink on, which is not a good idea.

It's also not just the backup, you have to dump the transaction logs. That will "empty out" the log, but leave the log at the size it has grown to...which is good in a well maintained databse because it figures it will grow to that size again, so you don't want to to incur the overhead of shrinking a growing all the time, which is overhead.

BUT, if it's not well maintained, you probably should dump the log, and use DBB SHRINKFILE or SHRINKDATABASE

Look them up in BOL

Monday, March 19, 2012

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

Monday, March 12, 2012

Inconsistent result set.

Hi All
What does it mean if i have a result set that comes as < long text>? I have
a table with a column of data type txt and size 16. When i try to get data
from that colomun, either i get the data or i get the result set as <long
text>. What is causing this inconsistency in my result set? Thanx in advance
.Enterprise Manager will not display large pieces of text stored in a text or
ntext column.
If you need to see more of the text values use Query Analyzer. But mind the
fact, that by default QA only displays up to 256 characters, so you'd need t
o
change that to suit your needs. Go to Tools | Options | Results and set
"Maximum characters per column" to a value of your choice (between 30 and
8192).
Or, better yet, manipulate long text in an appropriate client application.
Above all, get out of Enterprise Manager. ;)
ML|||Thanx for the reply,but still why is it at times i get the right result set.
I should be getting the '<long text>' all the time i run my query from what
you are saying. Also i have been doing this in QA. Thanx once again.
"ML" wrote:

> Enterprise Manager will not display large pieces of text stored in a text
or
> ntext column.
> If you need to see more of the text values use Query Analyzer. But mind th
e
> fact, that by default QA only displays up to 256 characters, so you'd need
to
> change that to suit your needs. Go to Tools | Options | Results and set
> "Maximum characters per column" to a value of your choice (between 30 and
> 8192).
> Or, better yet, manipulate long text in an appropriate client application.
> Above all, get out of Enterprise Manager. ;)
>
> ML|||I honestly do not know the exact limit used to display either actual
text/ntext or a generic <long text> message in Enterprise Manager, since I
really don't use it often. :)
But I have noticed that the limit may not be a fixed value. I think it
depends on the size of the active result set, but I'm not sure. Maybe someon
e
else knows. Anyway, since large text and/or images for that matter only mean
anything when displayed and manipulated in an appropriate client application
,
the ratio behind the "EM fun" is irrelevant. At least IMHO.
ML

Friday, March 30, 2012

Increased Backup size for unknown reason

increase size of varchar column.. table being replicated..

Increase or Decrease Texbox to accomadate text

increase in the size of tempDB

increase in the size of tempDB

increase in the size of tempDB

Increase in Database size after restoration

Increase in Database size after restoration

Increase in Database size after restoration

Increase field size AND linked tables

Increase Default Tlog size of Tempdb

Increase Default Tlog size of Tempdb

Increase Default Tlog size of Tempdb

Increase database size

Monday, March 19, 2012

Incorrect Compatibility level Error 15414:

Monday, March 12, 2012

Inconsistent result set.

sqlserver2000 date

Blog Archive

About Me