Showing posts with label size. Show all posts
Showing posts with label size. Show all posts

Thursday, March 29, 2012

Delete data, but file size increase

I encounter one weird problem, I have a database with around 7 GB ...
when I delete a bunch of data from it, it suppose to reduce the
database file size, but weirdly, the file size increase to 8 GB.

Wondering why. Is it suppose to be like that?
Is it the architecture is designed to work like that?

Is there any way for me to reduce the database file size?

Thanks.

Peter CCHIf you take back daily with append data option, the size gets increased

Madhivanan|||What utility are you using to measure the database size?

Are you using sp_spaceused and just noting the "Database Size" column?
This also includes the transaction log, and this will increase when you
delete data from the database, until you either truncate or backup your
transaction log.

You can also get wrong space values from sp_spaceused if you are making
frequent and large changes to data, such as updates and deletes, as the
counters that record the new extent allocations and deallocations don't
get updated dynamically every time.

Try running sp_spaceused in the database, and note the values in all
columns.
Now, repeat this, but execute sp_spaceused @.updateusage='true'

(N.B. This can take a few minutes to run. I have never had a problem
running this on a live database during the day, but be aware that it
runs DBCC UPDATEUSAGE and forces updates to the sysindexes catalog; it
is less-risky to run it out of hours)

Here's a good example from one of my databases:

sp_spaceused
go

database_name, database_size, unallocated space
Roms, 22541.00 MB, -4407.84 MB

reserved, data, index_size, unused
27589472 KB, 16362352 KB, 11146992 KB, 80128 KB

Note the negative Unallocated Space value.

Now I run:

sp_spaceused @.updateusage='true'

database_name, database_size, unallocated space
Roms, 22541.00 MB, 1.43 MB

reserved, data, index_size, unused
23074376 KB, 15558360 KB, 7447464 KB, 68552 KB

You can see that, prior to this, the Reserved, Data and Index_Size
columns were all showing more space being used than the true value. I
suggest you try this after you delete your data in future, and see if
you get the values you expect.sql

Tuesday, March 27, 2012

delete backup file

Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.

Does the maintenance plan generate an error log? It should be in %Install Path%\MSSQL\LOGS. Look through there. As I recall, if there is any problem anywhere in the process of making the backup, the purge old files process will not work (this is by design).

Regards,

hmscott

Monday, March 19, 2012

Defragmentation of table

We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.
There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>

Defragmentation of table

We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you
start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx
.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>

Defragmentation of table

We are using database having a current size of over 300GB
to store attachment files in an image column of a table.
is the approach of exporting and importing data records
from the table a good way to defragment the table? if so,
is it possible to export the data records as a flat file
to a tape drive.
thanks.There are commands in SQL Server to defrag and reorg. I strongly suggest you start by reading below
excellent whitepaper:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SY" <anonymous@.discussions.microsoft.com> wrote in message
news:1dd4a01c454d4$11ae92f0$a301280a@.phx.gbl...
> We are using database having a current size of over 300GB
> to store attachment files in an image column of a table.
> is the approach of exporting and importing data records
> from the table a good way to defragment the table? if so,
> is it possible to export the data records as a flat file
> to a tape drive.
> thanks.
>

Wednesday, March 7, 2012

Defining a concatenated column size

I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
Line 1: Incorrect syntax near '*'.
In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
Any help would be appreciated.
Thanks.
On Fri, 18 Jun 2004 07:49:01 -0700, Kirk wrote:

>I have a column within a view that is a concatenation of several different columns of another table. It goes something like: tblA.columnA + '-' tblA.columnB + '-' tblA.columnC + '-' tblA.columnD As 'NewColumn'
>Is it possible to define the size of the 'NewColumn' within the view? I have a stored procedure that is going to create a temp table based on this view, but when I execute the stored procedure I get the following error:
>Line 1: Incorrect syntax near '*'.
>In looking at the stored procedure code, the error is a result of when the create table occurs, it defines the NewColumn as nvarchar(*), which is causing the error.
>Any help would be appreciated.
>Thanks.
Hi Kirk,
Define the column as nvarchar(4000) (the maximum length). Or, if you want
the max. length to be shorter, use a lower number. The result of the
concatenation will be trimmed to the max. length the column holds (and you
may also choose to trim explicitly, using LEFT or CAST function).
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Define memory size

Hello there
I use sql server 2000 Developer on windows Advenced server.
What is my maximum memory size and how can i change it?Roy
How much memory do you have on your server?
BOL has a pretty good articles about the memory and how to change it
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:Op9nzN5ZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> Hello there
> I use sql server 2000 Developer on windows Advenced server.
> What is my maximum memory size and how can i change it?
>|||4GB
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:en9suQ5ZGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Roy
> How much memory do you have on your server?
>
> BOL has a pretty good articles about the memory and how to change it
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:Op9nzN5ZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>|||http://support.microsoft.com/?id=274750
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ek13bk5ZGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 4GB
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:en9suQ5ZGHA.1192@.TK2MSFTNGP03.phx.gbl...
>

Define memory size

Hello there
I use sql server 2000 Developer on windows Advenced server.
What is my maximum memory size and how can i change it?Roy
How much memory do you have on your server?
BOL has a pretty good articles about the memory and how to change it
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:Op9nzN5ZGHA.3848@.TK2MSFTNGP05.phx.gbl...
> Hello there
> I use sql server 2000 Developer on windows Advenced server.
> What is my maximum memory size and how can i change it?
>|||4GB
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:en9suQ5ZGHA.1192@.TK2MSFTNGP03.phx.gbl...
> Roy
> How much memory do you have on your server?
>
> BOL has a pretty good articles about the memory and how to change it
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:Op9nzN5ZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello there
>> I use sql server 2000 Developer on windows Advenced server.
>> What is my maximum memory size and how can i change it?
>|||http://support.microsoft.com/?id=274750
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:ek13bk5ZGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 4GB
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:en9suQ5ZGHA.1192@.TK2MSFTNGP03.phx.gbl...
>> Roy
>> How much memory do you have on your server?
>>
>> BOL has a pretty good articles about the memory and how to change it
>>
>>
>> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
>> news:Op9nzN5ZGHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello there
>> I use sql server 2000 Developer on windows Advenced server.
>> What is my maximum memory size and how can i change it?
>>
>

Friday, February 17, 2012

default tempdb size

Hello All,
SQL 2K W/SP3
Is it possible to change default tempdb size to something else other than
2MB so everytime server restarts, tempdb does nto go back to 2 MB?
Thanks,
BivaYep. You can also change location for the db files. Info is stored in
master..sysaltfiles, and you change it using ALTER DATABASE (see Books
Online for details).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...
> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>|||Biva,
ALTER DATABASE should work just fine.
Russell Fields
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...
> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>

default tempdb size

Hello All,
SQL 2K W/SP3
Is it possible to change default tempdb size to something else other than
2MB so everytime server restarts, tempdb does nto go back to 2 MB?
Thanks,
BivaYep. You can also change location for the db files. Info is stored in
master..sysaltfiles, and you change it using ALTER DATABASE (see Books
Online for details).
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...
quote:

> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>
|||Biva,
ALTER DATABASE should work just fine.
Russell Fields
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40102f2f$0$43850$39cecf19@.news.twtelecom.net...
quote:

> Hello All,
> SQL 2K W/SP3
> Is it possible to change default tempdb size to something else other than
> 2MB so everytime server restarts, tempdb does nto go back to 2 MB?
> Thanks,
> Biva
>
|||Hey Guys,
Thanks a bunch for the info ...
sqlgirl
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Tuesday, February 14, 2012

Default Printer settings

I have an issue with default papaer size and margin sizes. My printer is
configured with a default paper size of A4, every time I print a report with
the print control it defaults to Letter. So I have to hit the go button to
the print to run.
Secondly the default margin sizes are 12.7mm (1/2 inch), I woudl like to
reduce this to 5mm (@. 3/16 inch). I have had a similiar issue with blank
pages and found reducing the default print margin control to something less
than 1/2 inch sorts this out.
Is there a congfig file setting that I can change to make these printer
settings stick?
thanks
RayThe print control will use the margins set in your report. If you do not
set the margins, it will default to half an inch.
--
| Thread-Topic: Default Printer settings
| thread-index: AcVKL/UhL/9g9tA8Qqi+9Q5klGKalQ==| X-WBNR-Posting-Host: 203.10.231.231
| From: "=?Utf-8?B?UmF5IFNlcHBhbGE=?="
<Ray.Seppala@.defence.gov.au.(Donotspam)>
| Subject: Default Printer settings
| Date: Tue, 26 Apr 2005 00:17:08 -0700
| Lines: 16
| Message-ID: <6315458B-6B62-4F09-AFC1-76E19A8B4ABD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:41806
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have an issue with default papaer size and margin sizes. My printer is
| configured with a default paper size of A4, every time I print a report
with
| the print control it defaults to Letter. So I have to hit the go button
to
| the print to run.
|
| Secondly the default margin sizes are 12.7mm (1/2 inch), I woudl like to
| reduce this to 5mm (@. 3/16 inch). I have had a similiar issue with blank
| pages and found reducing the default print margin control to something
less
| than 1/2 inch sorts this out.
|
| Is there a congfig file setting that I can change to make these printer
| settings stick?
|
| thanks
|
| Ray
||||Thanks,
I have since found out that I have to re-publish all my reports for RS SP2
to be able to read each Report's print layout and margin settings.
Thanks for the the reply Brad!
""Brad Syputa - MS"" wrote:
> The print control will use the margins set in your report. If you do not
> set the margins, it will default to half an inch.
> --
> | Thread-Topic: Default Printer settings
> | thread-index: AcVKL/UhL/9g9tA8Qqi+9Q5klGKalQ==> | X-WBNR-Posting-Host: 203.10.231.231
> | From: "=?Utf-8?B?UmF5IFNlcHBhbGE=?="
> <Ray.Seppala@.defence.gov.au.(Donotspam)>
> | Subject: Default Printer settings
> | Date: Tue, 26 Apr 2005 00:17:08 -0700
> | Lines: 16
> | Message-ID: <6315458B-6B62-4F09-AFC1-76E19A8B4ABD@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:41806
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | I have an issue with default papaer size and margin sizes. My printer is
> | configured with a default paper size of A4, every time I print a report
> with
> | the print control it defaults to Letter. So I have to hit the go button
> to
> | the print to run.
> |
> | Secondly the default margin sizes are 12.7mm (1/2 inch), I woudl like to
> | reduce this to 5mm (@. 3/16 inch). I have had a similiar issue with blank
> | pages and found reducing the default print margin control to something
> less
> | than 1/2 inch sorts this out.
> |
> | Is there a congfig file setting that I can change to make these printer
> | settings stick?
> |
> | thanks
> |
> | Ray
> |
>