compacting LOG file

how can you compact .log file ?
they can be > 20 gigas ! ...
can you delete it ?do you mean the log files that the server itself writes? Those have no file extension normally, usuall called something like ERRORLOG. when the server restarts it renames it to ERRORLOG.1 and starts a new one. I would be very surprised if you have a 20gb one though. that's pretty large. maybe you have a lot of failed login attempts (which are logged as I recall).

Or perhaps you mean a ldf file? These are transaction log files for a database - it's common for these to get large. You can shrink the size of the ldf using DBCC SHRINKFILE. You should not delete an ldf file unless you want to delete the database it's associated with.

Note that if you care about recoverability, you should be making regular backups of both your database and transaction log.|||thank you Jezemine

yes of course I mean transaction log files
DBCC SHRINKFILE ? i musyt look how to use it ? do you run it as a stored procedure ?

EXECUTE DBCC SHRINKFILE

thank you for helping|||it's not a sproc.

read about DBCC SHRINKFILE in bol.

Compacting a db via T-SQL

Hello dbForumers,

I was wondering if there is a way (MUST be) to instruct SQL server to compact a chosen database's files. I have a batch that runs every night who generate a huge amount to log lines that I get rid of with a backup log xxx with truncate_only, but still the logfile is several GB big afterwards with a lotsa empty space... I wanna get a clean small file everyday =)

Thank you!Its not ideal to shrink the Transaction log every day, rather define a set of size by testing the activity on the database.

During that batch overnight take before and after sizes for Tlog and set the higher level.

Also maintain regular backups of Tlogs which will reduce the size of logical file and helps to fillup the Tlog quickly. If RECOVERY MODEL Is set to SIMPLE then ensure full database backups are carried in regular intervals.|||try DBCC SHRINKFILE

USE UserDB
GO
DBCC SHRINKFILE (logical file name,Target_size)
GO

Target_size is how much free size is left over after you shrink
if you leave this off, you will get the default.

also
you may have to switch the VLogs internally before you can shrink to a size that you desire...
all of his should be done after a tlog backup.
look up "Shrinking the Transaction Log" AND DBCC Shrinkfile in [BOL]

CompactDatabase in ASP with SQL Server Microsoft SQL Server Compact

I've been playing around with Microsoft SQL Server Compact Edition
v3.1 as a posible replacement for the SQL Server CE 2.0 (can't
remember exactly what the called it). Its working pretty well. I
was able to connect to the database using the same recordset object,
ADODB.RecordSet and connection object, ADOCE.Connection.3.1. However
where I've run into trouble is when I try to compact the database.
This code works on 2.0.

srcConn = "Provider=Microsoft." & "SQLServer" & ".OLEDB.CE.2.0;Data
Source=\myDB.sdf"
destConn = "Provider=Microsoft." & "SQLServer" & ".OLEDB.CE.2.0;Data
Source=\myDB.sdf"

Set DBEngine = CreateObject("SSCE.Engine.2.0")

DBEngine.CompactDatabase srcConn, destConn

But now when I try to run it vor v3.1

srcConn = "provider=microsoft.sqlserver.mobile.oledb.3.0;data
source=\myDB.sdf"
destConn = "provider=microsoft.sqlserver.mobile.oledb.3.0;data
source=\mDB1.sdf"

Set DBEngine = CreateObject("SSCE.Engine.3.0")

DBEngine.CompactDatabase srcConn, destConn

I get this error message:

Info: Object required: 'DBEngine'

As I understand it SSCE.Engine.3.0 is still an ActiveX
object. Is it no longer posible to run this directly from
asp?

Using SQL CE under asp.net has been blocked in SQL CE 3.1, see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceengine.aspx. Maybe this is what you are facing with asp as well. Have no idea how to do a

Code Snippet

AppDomain.CurrentDomain.SetData(

under asp classic.

Compact Tools Download Problem

When I try and download Sqlce30setupen.msi from the msdn site, I keep getting the install for the mobile tools. Can someone explain why this is happening. I am new to SQL compact and I am trying to go through the tutorial http://msdn2.microsoft.com/en-us/library/bb226707.aspx. This tutorial says the download should be for SQL Compact setup.

Thanks

Ryan

All downloads for SQL Compact Edition are available from http://www.microsoft.com/sql/editions/compact/downloads.mspx|||

Hi Ryan,

Are you having the same problem as me? When I download the setup file from the SQL Compact downloads url provided, it doesn't install the Compac Server Tools but tries to install "Microsoft SQL Server 2005 Mobile SP1 [ENU] Server Tools". I'm trying to install on Windows Server 2003. Interestingly if I run the same file on XP Pro it successfully installs the Compact Server Tools. I just added a new thread about this problem.

Regards,

Greg

Compact Tools Download Problem

When I try and download Sqlce30setupen.msi from the msdn site, I keep getting the install for the mobile tools. Can someone explain why this is happening. I am new to SQL compact and I am trying to go through the tutorial http://msdn2.microsoft.com/en-us/library/bb226707.aspx. This tutorial says the download should be for SQL Compact setup.

Thanks

Ryan

All downloads for SQL Compact Edition are available from http://www.microsoft.com/sql/editions/compact/downloads.mspx|||

Hi Ryan,

Are you having the same problem as me? When I download the setup file from the SQL Compact downloads url provided, it doesn't install the Compac Server Tools but tries to install "Microsoft SQL Server 2005 Mobile SP1 [ENU] Server Tools". I'm trying to install on Windows Server 2003. Interestingly if I run the same file on XP Pro it successfully installs the Compact Server Tools. I just added a new thread about this problem.

Regards,

Greg

compact sqlce database

Hi, there;

I got a SqlCe2.0 database here called JOB.sdf, size is 260K. When I tried to compact it, it took very long and that JOB.sdf.tmp was created with 27.7M!!!, I can still open table in the database, but I found that there is some invalid data (unreadable square boxes). It looks like database was corrupted. My application gave error:

Error Code: 80040E14
Message : The OLE DB Execute method failed. The SQL statement is not valid. [,,,SQL statement,,]
Minor Err.: 28560
Source : Microsoft SQL Server 2000 Windows CE Edition
Error Code: 80040E14
Message : Line 1: Incorrect syntax near '















à ??€’‰ì±′ê???????ê￠?è???3?'.
Minor Err.: 170
Source : Microsoft OLE DB Provider for SQL Server
Error Code: 80040E14
Message : Unclosed quotation mark before the character string '















à ??€’‰ì±′ê???????ê￠?è???3?'.
Minor Err.: 105
Source : Microsoft OLE DB Provider for SQL Server</E>

Note: "















" is displayed as "SOHSOH....." with black background.

Apart from compacting database, is there manual job or tool we can do/use to fix a corrupted database caused by power failure? (e.x: all data saved successfully before power failure)?

Thanks

HI,

have you tried to use newer SQLCE engine with existing corrupted database ?

Although I had never tried it yet, I think that when you try "Verify" and "Repair"

features of SQLCE, you can safely survive most of your database. Go and

download SQL CE 3.1 desktop engine and tools, its even good to work with it :-)

hope it helps,

Petr

|||

Thanks.

How can get the newer SQLCE engine? Can that be run at desktop without a device?

I have quite a bit corrupted databases whih is a disaster now!

|||

Yes, you can download all engines withing SDK from here:

http://www.microsoft.com/sql/editions/compact/default.mspx

resp.

http://www.microsoft.com/downloads/details.aspx?FamilyId=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en

may be other tools from this web may help you too.

Without VS2005, you can manage database through

free SQLServer Management Studio Express

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en

I dont know if is possible to verify/repair from this gui app, but at least you can

build small C# or VB.NET desktop project against new engine, using your corrupted database

and try verify/repair methods.

Petr

|||

:-( excuse me, SQLSerer Management Studio must be full, not only Express !!!

This is very big download, so it may dissappoint you.

BTW, best is to do small project itself and use code :-)

Petr

|||Thank you. I will give it a try.

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0

(with OLE DB). To compact a 2.0 database, i used ISSCECompact interface.

But it doesn't exists with the new version. I've only found C#

and VB.Net samples in MSDN ...

Thanks in advance.

Have you had a chance to look at: http://msdn2.microsoft.com/en-us/library/ms174034(en-US,SQL.90).aspx

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

#include "ca_merge30.h"
#include <stdio.h>

void ErrorPrint() ;

#define VerifyResult(x) hr=x;\
if(FAILED(hr)) { \
printf("Failed : HR : %x\n",hr); \
printf("at : %d \n",__LINE__); \
ErrorPrint(); \
goto Exit; \
}
#define RELEASE(x) if(NULL!=x)x->Release();

extern "C" int __cdecl wmain(){

HRESULT hr = NOERROR;
ISSCEEngine *pISSCEEngine = NULL;
VerifyResult(CoInitialize(NULL));
VerifyResult(CoCreateInstance(CLSID_Engine, NULL, CLSCTX_INPROC_SERVER,
IID_ISSCEEngine, (void**)&pISSCEEngine));

WCHAR *wszSrc=L"Data Source = src.sdf";
WCHAR *wszDst=L"Data Source = dst.sdf";
VerifyResult(pISSCEEngine->CompactDatabase(wszSrc, wszDst));

Exit:
RELEASE(pISSCEEngine);
CoUninitialize();
return hr;
}

void ErrorPrint()
{
LPVOID lpMsgBuf;
DWORD dw = GetLastError();

FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM,
NULL,
dw,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf,
0, NULL );

printf( "Failed with error %d: %ls",
dw, lpMsgBuf);

LocalFree(lpMsgBuf);
}

Hope this helps! Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

(Sample By: BalaDutt, MSFT, SQL Mobile, Microsoft Corporation)

|||Many thanks ! It's exactly what i needed !|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

This helped me as well, thanks. But I also cannot find any reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.

The docs have a sample in C#, but nothing for OLD DB in C++.

Does anyone know how to do this?

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0 (with OLE DB). To compact a 2.0 database, i used ISSCECompact interface. But it doesn't exists with the new version. I've only found C# and VB.Net samples in MSDN ...

Thanks in advance.

Have you had a chance to look at: http://msdn2.microsoft.com/en-us/library/ms174034(en-US,SQL.90).aspx

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

#include "ca_merge30.h"
#include <stdio.h>

void ErrorPrint() ;

#define VerifyResult(x) hr=x;\
if(FAILED(hr)) { \
printf("Failed : HR : %x\n",hr); \
printf("at : %d \n",__LINE__); \
ErrorPrint(); \
goto Exit; \
}
#define RELEASE(x) if(NULL!=x)x->Release();

extern "C" int __cdecl wmain(){

HRESULT hr = NOERROR;
ISSCEEngine *pISSCEEngine = NULL;
VerifyResult(CoInitialize(NULL));
VerifyResult(CoCreateInstance(CLSID_Engine, NULL, CLSCTX_INPROC_SERVER,
IID_ISSCEEngine, (void**)&pISSCEEngine));

WCHAR *wszSrc=L"Data Source = src.sdf";
WCHAR *wszDst=L"Data Source = dst.sdf";
VerifyResult(pISSCEEngine->CompactDatabase(wszSrc, wszDst));

Exit:
RELEASE(pISSCEEngine);
CoUninitialize();
return hr;
}

void ErrorPrint()
{
LPVOID lpMsgBuf;
DWORD dw = GetLastError();

FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM,
NULL,
dw,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf,
0, NULL );

printf( "Failed with error %d: %ls",
dw, lpMsgBuf);

LocalFree(lpMsgBuf);
}

Hope this helps! Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

(Sample By: BalaDutt, MSFT, SQL Mobile, Microsoft Corporation)

|||Many thanks ! It's exactly what i needed !
|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

This helped me as well, thanks. But I also cannot find any reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.

The docs have a sample in C#, but nothing for OLD DB in C++.

Does anyone know how to do this?

Compact Sql Mobile 3.0 DataBase in C++/OLE DB ?

Hello,

I'm porting my C++ (EVC3) application from Sql CE 2.0 to Sql Mobile 3.0 (with OLE DB). To compact a 2.0 database, i used ISSCECompact interface. But it doesn't exists with the new version. I've only found C# and VB.Net samples in MSDN ...

Thanks in advance.

Have you had a chance to look at: http://msdn2.microsoft.com/en-us/library/ms174034(en-US,SQL.90).aspx

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

#include "ca_merge30.h"
#include <stdio.h>

void ErrorPrint() ;

#define VerifyResult(x) hr=x;\
if(FAILED(hr)) { \
printf("Failed : HR : %x\n",hr); \
printf("at : %d \n",__LINE__); \
ErrorPrint(); \
goto Exit; \
}
#define RELEASE(x) if(NULL!=x)x->Release();

extern "C" int __cdecl wmain(){

HRESULT hr = NOERROR;
ISSCEEngine *pISSCEEngine = NULL;
VerifyResult(CoInitialize(NULL));
VerifyResult(CoCreateInstance(CLSID_Engine, NULL, CLSCTX_INPROC_SERVER,
IID_ISSCEEngine, (void**)&pISSCEEngine));

WCHAR *wszSrc=L"Data Source = src.sdf";
WCHAR *wszDst=L"Data Source = dst.sdf";
VerifyResult(pISSCEEngine->CompactDatabase(wszSrc, wszDst));

Exit:
RELEASE(pISSCEEngine);
CoUninitialize();
return hr;
}

void ErrorPrint()
{
LPVOID lpMsgBuf;
DWORD dw = GetLastError();

FormatMessage(
FORMAT_MESSAGE_ALLOCATE_BUFFER |
FORMAT_MESSAGE_FROM_SYSTEM,
NULL,
dw,
MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
(LPTSTR) &lpMsgBuf,
0, NULL );

printf( "Failed with error %d: %ls",
dw, lpMsgBuf);

LocalFree(lpMsgBuf);
}

Hope this helps! Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

(Sample By: BalaDutt, MSFT, SQL Mobile, Microsoft Corporation)

|||Many thanks ! It's exactly what i needed !
|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

This helped me as well, thanks. But I also cannot find any reference material for how to perform a "Verify" DB as advertised in the Documentation for SQL Server Mobile Edition.

The docs have a sample in C#, but nothing for OLD DB in C++.

Does anyone know how to do this?

Compact method to reset Identity columns

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.

Any help is greatly appreciated!

Kind Regards,

Mat

Try out: ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

Thanks,

Laxmi

|||

I tried this:

ALTER TABLE asset ALTER COLUMN iid bigint (51,1)

but get the following error message:

The specified data type is not valid. [ Data type (if known) = bigint ]

Bigint is the data type of this field, but I get the same error message if I try int as well. Thanks for your help!

Kind Regards,

Mat

|||

Use

"ALTER TABLE asset ALTER COLUMN iid IDENTITY (51,1)"

|||

Pragya,

This worked perfectly!

Thanks,

Mat

Compact method to reset Identity columns

I have a demo database in SqlCE that I am getting ready to deploy. I deleted a bunch of test records and now want to reset the identity columns. The compact method runs fine, but the identity columns are not being reset? So when I add a new record, the returned identity value is over 1,000 even though the highest value is only 50.

Any help is greatly appreciated!

Kind Regards,

Mat

Try out: ALTER TABLE <IdentityTable> ALTER COLUMN <Identity Column> <Data Type> (51, 1);

Thanks,

Laxmi

|||

I tried this:

ALTER TABLE asset ALTER COLUMN iid bigint (51,1)

but get the following error message:

The specified data type is not valid. [ Data type (if known) = bigint ]

Bigint is the data type of this field, but I get the same error message if I try int as well. Thanks for your help!

Kind Regards,

Mat

|||

Use

"ALTER TABLE asset ALTER COLUMN iid IDENTITY (51,1)"

|||

Pragya,

This worked perfectly!

Thanks,

Mat

Compact Framework SqlClient Classes don't actually inherit from System.Data.Common.Db* classes?

As you all know, in Framework 2.0 on the desktop platform you have the convenient "abstract" Db* classes that can be used to write more generic code. For instance System.Data.SqlClient.SqlConnection inherits from System.Data.Common.DbConnection, and so forth. Now the docs clearly say that this applies to the corresponding classes in the Compact Framework. And i have seen this implied by other web searchs I've done. However this does not seem to be the case -- or, I'm missing something somewhere.

The following simple code on the desktop platform compiles OK:

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
DbConnection conn = new SqlConnection();

However it doesn't compile in a compact framework project: "Cannot implicitly convert type 'System.Data.SqlClient.SqlConnection' to 'System.Data.Common.DbConnection'".

Reflector also indicates that the Compact Framework class doesn't inherit from DbConnection.

OK, so am I missing something here, or what was the reason that Microsoft did it differently?

Appologies, accidentally posted to the wrong forum. Have reposted here instead:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1641305&SiteID=1

Compact Edition not in Data Source List

After I installed the SQL Server Compact Edition, I start a new VS 2005 Basic project. When I try to create a new connection I do not see the Compact Edition in the list of data sources.

I have installed previously SQL Server CE, Everywhere, Mobile, 2005 Mobile but I uninstalled them all.

Can anyone give me an idea why I can't see the Compact Edition as a data source?

Thanks.

Hello

From what you'r saying maybe you need to re-install the SQL Server CE.
I have the same problem but it's because I'm using VB.NET Express Edition.

see ya

Compact Edition DVD Standalone

Is it possible to run SQL Server Compact Edition as a standalone application from a DVD?

i.e. it would not leave anything installed on the users computer after the DVD was ejected.

Hi:

For what it's worth, I am attempting the same thing: developing a product catalog that will enable users to browse products and configure order for printing. As my product is using Framework 2, I will simply have an autorun install the program since I have to check for the existence of the framework anyway.

Please post back your findings.

|||If both .Net CF2 and SQL Server 2005 are installed on the target system, you should be able to deploy the DLLs for SQL CE on the DVD.|||I do not believe there is a dependancy on SQL Server/.NET CF2 for a runtime SQL Server CE application - believe that there is only a dependency on VB6 runtime/.Net 1/1.1 runtime of .Net2.0 runtime (according the language the app has been written in. Plus of course the SQL CE Dlls on the DVD|||Make connection string like this:

"Data Source=".\MyDatabase.sdf";Password="MyPassword";mode="Read Only";SSCE:Temp File Directory = "C:\";"

This will use temporary database on c: disk if there's need to write temp data for queries.

On DVD/CD include these files in root:
Microsoft.SqlServerCe.Client.dll
sqlceca30.dll
sqlcecompact30.dll
sqlceer30EN.dll
sqlceme30.dll
sqlceoledb30.dll
sqlceqp30.dll
sqlcese30.dll
System.Data.SqlServerCe.dll

Now, this works for me, but I have requirement, not to write to C:, but to users temp folder.
Does somebody know how to change connection string to write into temp folder?

Compact Database Error Code: 0

I am currently working in VB.NET with SQL Mobile 2005. I am trying to compact a database, .sdf, using the SqlCeEngine Object compact(connectionstring). When I use this code to compact a small database, only about 300KB, it returns okay however when I try to compact a larger database, about 9MB, it returns with a SQL Error Code: 0, "The operation completed successfully" however with no compacted database. The code works however it only seems to work for smaller databases. Does anyone have any ideas on how to correct this error or tell me why it returns with that type of error.

Snuggs

I am able to compact the database of size nearly 21 MB in our lab. Is it possible for you to share the repro for us to diagnose.

Thanks,

LaxmI NRO, MSFT, SQL Mobile, Microsoft Corporation

|||What do I need to do in order to give you the repro, do you mean just an actual copy of the database itself or something else?|||

Yes actual copy of the database itself. Before sharing it to me, can you check whether the DB is in correct state. compact.exe /verify would let you know if there any bad pages in the DB.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||The DB is in the correct state. Do you have a direct email address that I can send this to you because this will not let me send this as an attachment.|||Please send it to l-a-x-m-i-n-r-o-A-T-m-i-c-r-o-s-o-f-t-D-O-T-c-o-m Please also paste this question link in the mail for the context. Thanks, Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation|||

Hi Snuggs,

As per our conversation I could see that you have opened a bug here in Product Feedback center. http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=d0f14331-1b05-418a-9a11-2378457e4eb8

And as mentioned we could not repro. We are able to compact the DB very easily with a sample C# program mentioned in the above URL.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Compact Database Error Code: 0

I am currently working in VB.NET with SQL Mobile 2005. I am trying to compact a database, .sdf, using the SqlCeEngine Object compact(connectionstring). When I use this code to compact a small database, only about 300KB, it returns okay however when I try to compact a larger database, about 9MB, it returns with a SQL Error Code: 0, "The operation completed successfully" however with no compacted database. The code works however it only seems to work for smaller databases. Does anyone have any ideas on how to correct this error or tell me why it returns with that type of error.

Snuggs

I am able to compact the database of size nearly 21 MB in our lab. Is it possible for you to share the repro for us to diagnose.

Thanks,

LaxmI NRO, MSFT, SQL Mobile, Microsoft Corporation

|||What do I need to do in order to give you the repro, do you mean just an actual copy of the database itself or something else?|||

Yes actual copy of the database itself. Before sharing it to me, can you check whether the DB is in correct state. compact.exe /verify would let you know if there any bad pages in the DB.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||The DB is in the correct state. Do you have a direct email address that I can send this to you because this will not let me send this as an attachment.|||Please send it to l-a-x-m-i-n-r-o-A-T-m-i-c-r-o-s-o-f-t-D-O-T-c-o-m Please also paste this question link in the mail for the context. Thanks, Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation|||

Hi Snuggs,

As per our conversation I could see that you have opened a bug here in Product Feedback center. http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=d0f14331-1b05-418a-9a11-2378457e4eb8

And as mentioned we could not repro. We are able to compact the DB very easily with a sample C# program mentioned in the above URL.

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

Compact Database

Is there a stored procedure that allow the compact database programmatically
in SQL Server?"Claudio Di Flumeri" <claudioNOSPAM@.mtgc.net> wrote in message
news:c0conk$15i74v$1@.ID-198343.news.uni-berlin.de...
> Is there a stored procedure that allow the compact database
programmatically
> in SQL Server?

Check documentation for
DBCC SHRINKDATABASE|||"Claudio Di Flumeri" <claudioNOSPAM@.mtgc.net> wrote in message news:<c0conk$15i74v$1@.ID-198343.news.uni-berlin.de>...
> Is there a stored procedure that allow the compact database programmatically
> in SQL Server?

I believe compacting is an Access concept - it doesn't exist in MSSQL.
If you want to check the integrity of a database, then you can look at
DBCC CHECKDB; if you want to physically reduce the size of a database,
then DBCC SHRINKDATABASE and DBCC SHRINKFILE would help.

Simon

Compact and Repair in an adp

Hi,

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.

I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

Thanks!

J~JA wrote:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do that
from Access? Will it not mess anything up?

No idea. You can definitely use Query Analyzer to run a
DBCC SHRINKDATABASE command.

If you expect the database to grow back to its previous size and
stay there, then you shouldn't shrink it. If you expect it to
stay at its reduced size, then you should.|||JA (jarmour@.kc.rr.com) writes:

Quote:

Originally Posted by

I just deleted about 1 million records from a SQL Server 2000 database. I
access it using Access 2000.
>
I have Compact and Repair available in the adp under Tools. Should I do
that from Access? Will it not mess anything up?

To me that sounds like tools for the local Access database.

In any case, one million rows is not that much, unless the rows are
extremely wide.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

compact and repair database

I'm an Access man moving towards SQL Server and I'm interested in knowing if there is a utility for my SQL Server version 7 database that is similar to Access' Compact and Repair utility? I'd like a recommendation from a guru on what Prevenatative Maintenance measures I should be running daily/weekly/monthly etc...

Thanks in advance.
dogSQL Server used to need a lot of "care and feeding" on a regular basis. In version 2000, it comes with maintenance plan wizards that will provide a wizard interface to create and schedule maintenance jobs that handle 99% of the needs of any database, and all of the needs of 99% of the databases that I've seen.

After you run up your box and have Windoze and SQL running happily, you should visit the maintenance wizard soon.

At your convenience, you should read up on the whole suite of DBCC commands in SQL Books Online. You'll probably know which ones you need long before you need them, if you ever need them!

As an observation, you write like you are fairly comfortable with programming, databases, etc. If so, you probably want to find a place like this to post questions, since you'll get answers that make sense to you relatively quickly from folks that have done all of this a time or two!

-PatP|||SQL Server used to need a lot of "care and feeding" on a regular basis. In version 2000, it comes with maintenance plan wizards that will provide a wizard interface to create and schedule maintenance jobs that handle 99% of the needs of any database, and all of the needs of 99% of the databases that I've seen.

A note about the maintenance plan wizard: I've had a maintenance plan created for about 2 months now but I don't think its doing anything. I've been in to look at the settings 3 or 4 times now and all looks well but I notice that its not backing up to the directory I specified and I suspect its not doing any of the integrity and reindexing procedures either. After defining it, is there another step I have to take to get the maintenance plan running? I'm finishing up development and users are starting to input data, so its moving to the front burner.|||? Did you schedule it? That's an area you have to fill out in there. Also, look at your jobs in Enterprise Manager. When you create a Maintenance plan, it creates a job to run the on a scheduled basis. Make sure the jobs are enable. Then, look at the properties of them to make sure they have a valid schedule.

Compact & Repair

How do you repair and get rid of dead files for this
server versus Access database.
> How do you repair and get rid of dead files for this
> server versus Access database.
In SQL Server you can use DBCC SHRINKDATABASE and DBCC SHRINKFILE commands.
Is this what you are asking?
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com