database size

Hi,
I've got a question about database size.
When I go to EM database properties I can see that database size is 4599.56
MB. Also it tells that I've got 2664,98 MB available space. I thought I can
shrink database and so return that space to OS. However after shrinking DB
the size remains the same. I tried dbcc shrinkfile as well, but no luck
exec sp_helpdb dbname
4599.56 MB
Has anybody got an idea how should I reduce the DB size?
Thanks a lot
DariusIt may be the data is stored all over the place in that 4599.56 MB
(framentation), that why despite it indicates half of them are not
actually used but when you try to shrink it, it does not work.
Try to use DBCC SHRINKFILE with the NOTRUNCATE option along with the
target size.
When NOTRUNCATE is specified along with target_size, the space freed is
not released to the operating system. The only effect of the DBCC
SHRINKFILE is to relocate used pages from above the target_size line to
the front of the file.
This should then deframent the data file and after that run the DBCC
SHRINKFILE again with the target_size. It should then do the trick.
Alternative deframentation options -
1) backup the database and restore the database.
2) use 3rd party deframentation tools (this one you will need to take
the db offline beforehand).
Mel|||Hi Mel,
Unfortunately no luck with DBCC SHRINKFILE NOTRUNCATE and DBCC SHRINKFILE,
targetsize after that.
I tried to backup and restore then. Size of database backup file is ~ 4.7 GB
and after restore the db size was still 4599.69 MB. Have tried SHRINKFILE
on restored DB, but free space still remains 2663.52 MB
Any other ideas?
Darius
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145376698.375718.152140@.i40g2000cwc.googlegroups.com...
> It may be the data is stored all over the place in that 4599.56 MB
> (framentation), that why despite it indicates half of them are not
> actually used but when you try to shrink it, it does not work.
> Try to use DBCC SHRINKFILE with the NOTRUNCATE option along with the
> target size.
> When NOTRUNCATE is specified along with target_size, the space freed is
> not released to the operating system. The only effect of the DBCC
> SHRINKFILE is to relocate used pages from above the target_size line to
> the front of the file.
> This should then deframent the data file and after that run the DBCC
> SHRINKFILE again with the target_size. It should then do the trick.
> Alternative deframentation options -
> 1) backup the database and restore the database.
> 2) use 3rd party deframentation tools (this one you will need to take
> the db offline beforehand).
> Mel
>|||Backup and restore will not reduce size for database files. Is it a data or
log file that is "too
large"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sabotage" <nospam> wrote in message news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx.gbl...reen">
> Hi Mel,
> Unfortunately no luck with DBCC SHRINKFILE NOTRUNCATE and DBCC SHRINKFILE
, targetsize after that.
> I tried to backup and restore then. Size of database backup file is ~ 4.7
GB and after restore the
> db size was still 4599.69 MB. Have tried SHRINKFILE on restored DB, but
free space still
> remains 2663.52 MB
> Any other ideas?
> Darius
> "MSLam" <MelodySLam@.googlemail.com> wrote in message
> news:1145376698.375718.152140@.i40g2000cwc.googlegroups.com...
>|||Try to backup transection logs
Or shrink logs using T-SQL script
http://www.neweb.co.il/mcse/scripts/shrinksqllogs.html
??????!
??? ????? ??? "??" ??? ?????.
???
"Tibor Karaszi" wrote:

> Backup and restore will not reduce size for database files. Is it a data o
r log file that is "too
> large"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sabotage" <nospam> wrote in message news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx
.gbl...
>|||It is a data file I am having problems with. Log file can be reduced.
D.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23E0b1QxYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Backup and restore will not reduce size for database files. Is it a data
> or log file that is "too large"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sabotage" <nospam> wrote in message
> news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx.gbl...
>|||It is a data file I am having problems with. Log file can be reduced.
D.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23E0b1QxYGHA.4060@.TK2MSFTNGP02.phx.gbl...
> Backup and restore will not reduce size for database files. Is it a data
> or log file that is "too large"?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sabotage" <nospam> wrote in message
> news:eQqzVZwYGHA.444@.TK2MSFTNGP05.phx.gbl...
>|||Well, I thought the backup and restore database may work in terms of
defragmentation.
Because I used to support Oracle and one way to defragment the data
files were export the data out and import back to the same database
container. It doesn't seen be the case for MSSQL I wonder if the
restore also restore the storing pattern (how data is stored).
It may be unusual, but can you tell me how large is your model database
at that instance? The database cannot be made smaller than the size of
the model database.
I will be suprised if it is because of that. Can you post the result
set here (the one you received after the running the dbcc shrinkfile
'datafile' cmd'? It will give me more ideas.
Mel|||And you tried DBCC SHRINKFILE, not specifying either NOTRUNCATE or TRUNCATEO
NLY?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sabotage" <nospam@.dot.com> wrote in message news:z7c1g.52$4k5.29@.newsfe4-gui.ntli.net...[vb
col=seagreen]
> It is a data file I am having problems with. Log file can be reduced.
> D.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23E0b1QxYGHA.4060@.TK2MSFTNGP02.phx.gbl...
>[/vbcol]|||Mel, Tibor,
Model database size is 1.63 MB, so there's no problem with it.
Also I have tried all the variations of DBCC SHRINKFILE, with and without
NOTRUNCATE and TRUNCATEONLY
The results of DBCC SHRINKFILE ('dbname') are:
DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages
-- -- -- -- -- --
7 1 588632 128 588608 588608
I upload the data to the database using BULK INSERT from text file in native
format
Darius
"MSLam" <MelodySLam@.googlemail.com> wrote in message
news:1145395598.936282.159010@.e56g2000cwe.googlegroups.com...
> Well, I thought the backup and restore database may work in terms of
> defragmentation.
> Because I used to support Oracle and one way to defragment the data
> files were export the data out and import back to the same database
> container. It doesn't seen be the case for MSSQL I wonder if the
> restore also restore the storing pattern (how data is stored).
> It may be unusual, but can you tell me how large is your model database
> at that instance? The database cannot be made smaller than the size of
> the model database.
> I will be suprised if it is because of that. Can you post the result
> set here (the one you received after the running the dbcc shrinkfile
> 'datafile' cmd'? It will give me more ideas.
> Mel
>