Dear Newsgroup Readers,

On my development machine:

database_size = 17.4 Mb
unallocated_space = 1.9 Mb
data + index_size tables = 16.2 Mb

So the log files are 1.2 Mb

On my production machine:

database_size = 18.9 Mb
unallocated_space = 2.4 Mb
data + index_size tables = 4.3 Mb

So the log files are 14.6 Mb

This is a big differences, does this come that my provider has
reserved so much empty space for the log files?

How do I shrink the database in a way that I can still do backup and
recovery. SHRINKFILE … TRUNCATEONLY or EMPTYFILE and then drop it with
ALTER DATABASE?

How do I get a listing of the names (+sizes) of all the log files
including transaction logs.

Kind regards,

Johan van der Galiën.

Hello Johan,

Deleting a Transaction Log is not the way to follow to shrink it.

It seems that your database's Recovery Model is FULL (or Bulk-Logged?) In
that case, you need to backup your log file (using BACKUP LOG command) to
empty it. As an initial size, give it some room (say 1-2 GB) so that it
won't need to grow physically all the time and cause fragments. In this
case, you'll be able to backup your log file and keep its size under
control.

If you set its recovery model to SIMPLE then you don't need to take log
backups to keep its size low as it'll be emptied automatically so it'll not
need to grow (except for bulk operations) however in this case you'll not be
able to backup your log file so you can return to the lastest point when you
take a differential or full backup of your database.

See this link:
http://technet.microsoft.com/en-us/l.../ms189275.aspx

--
Ekrem Önsoy



<galien8 (AT) zonnet (DOT) nl> wrote

Dear Newsgroup Readers,

On my development machine:

database_size = 17.4 Mb
unallocated_space = 1.9 Mb
data + index_size tables = 16.2 Mb

So the log files are 1.2 Mb

On my production machine:

database_size = 18.9 Mb
unallocated_space = 2.4 Mb
data + index_size tables = 4.3 Mb

So the log files are 14.6 Mb

This is a big differences, does this come that my provider has
reserved so much empty space for the log files?

How do I shrink the database in a way that I can still do backup and
recovery. SHRINKFILE … TRUNCATEONLY or EMPTYFILE and then drop it with
ALTER DATABASE?

How do I get a listing of the names (+sizes) of all the log files
including transaction logs.

Kind regards,

Johan van der Galiën.

galien8 (AT) zonnet (DOT) nl (galien8 (AT) zonnet (DOT) nl) writes:
You don't. 19 MB is an extremely small database, and there is no
reason to shrink it.

sp_helpdb <yourdb>.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Dear Sir,

On 26 feb, 00:27, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
OUTPUT:
name db_size owner dbid created status compatibility_level
db_satoconor 18.94 MB sa 61 Sep 26 2008 Status=ONLINE,
Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE,
Version=611, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 90

name fileid filename filegroup size maxsize growth usage
db_satoconor 1 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
\db_satoconor.mdf PRIMARY 18624 KB Unlimited 1024 KB data only
db_satoconor_log 2 G:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA
\db_satoconor_log.LDF 768 KB 2147483648 KB 10% log only

Can I shrink, the 19 Mb DB to 5 Mb that is required for the data, this
way: DBCC SHRINKFILE DB_SATOCONOR.MDF TRUNCATEONLY, without losing
data and still be able to do backup and recovery?

This is important because I only got 100 Mb webspace, including the
DB, at my hosting provider so every MB counts!

Kind regards,

Johan van der Galien.

Shrink does not affect your backup options and will not cause data
loss. But don't specify the TRUNCATEONLY option.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<galien8 (AT) zonnet (DOT) nl> wrote

Dear Sir,

On 28 feb, 09:04, "Tibor Karaszi"
<tibor_please.no.email_kara... (AT) hotmail (DOT) nomail.com> wrote:
I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around
19 Mb!

I am curious why a mdf file that contains only about 4 Mb data stays
so recalcitrant at around 18 Mb? There is somehow a lot of empty space
reserved in the file?

How can I get rid of the empty space? And get my DB back to around 5
MB as it should be?!

Kind regards,

Johan van der Galien.

Are you doing transaction log backups? If not, the transaction log cannot
release the used log portion because it's still in-use. Either do regular
transaction log backups, or put the database into Simple mode where the
transaction log is automatically marked as free once a transaction has
committed. If you regularly do db maintenance, like shrinking the db, that
will cause the transaction log to grow unless the db is in Simple mode. The
web hosting service I use does nightly full backups, but no transaction log
backups. I want the log available for recovery, so I keep the db in Full
mode. When I do major updates like reloading the web database, I put the db
into Simple mode and shrink the db. Then I re-enable Full mode. After the
next nightly full backup, the transaction log is again usable if necessary.

<galien8 (AT) zonnet (DOT) nl> wrote

Dear Sir,

On 28 feb, 09:04, "Tibor Karaszi"
<tibor_please.no.email_kara... (AT) hotmail (DOT) nomail.com> wrote:
I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around
19 Mb!

I am curious why a mdf file that contains only about 4 Mb data stays
so recalcitrant at around 18 Mb? There is somehow a lot of empty space
reserved in the file?

How can I get rid of the empty space? And get my DB back to around 5
MB as it should be?!

Kind regards,

Johan van der Galien.

What version of SQL Server? Perhaps space usage reporting is
out-of-whack? Check out DBCC UPDATEUSAGE.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<galien8 (AT) zonnet (DOT) nl> wrote

Dear Sir,

On 28 feb, 09:04, "Tibor Karaszi"
<tibor_please.no.email_kara... (AT) hotmail (DOT) nomail.com> wrote:
I tried: DBCC SHRINKFILE(db_satoconor,6) and it still stays at around
19 Mb!

I am curious why a mdf file that contains only about 4 Mb data stays
so recalcitrant at around 18 Mb? There is somehow a lot of empty space
reserved in the file?

How can I get rid of the empty space? And get my DB back to around 5
MB as it should be?!

Kind regards,

Johan van der Galien.

Paul Shapiro (paul (AT) hideme (DOT) broadwayData.com) writes:
1) Johan is trying to shrink is MDF file.
2) Per his previous post, his database is in simple recovery.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

galien8 (AT) zonnet (DOT) nl (galien8 (AT) zonnet (DOT) nl) writes:
So what is the output from sp_spaceused? Or why do you think that the
database size is 5MB?

Maybe you should find another host. 100 MB is not much to write home about.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx