Cluster DR

My production cluster disks are corruptd - i would like to find out how to
restore from backups
I have all system backups and mdf and ldf files of the databases - I would
like to keep the logins, jobs etc
Thanks
Restoring a cluster is almost exactly the same as restoring a stand-alone
SQL Server. The only difference is that when you restire the master
database, the disks have to be online using the cluster tool, but leave the
SQL Server service offline. You then start and stop it in single-user mode
using the command-line. See BOL for details on how to restore a SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"John Smith" <bukusu@.gmail.com> wrote in message
news:el5gBC7GGHA.532@.TK2MSFTNGP15.phx.gbl...
> My production cluster disks are corruptd - i would like to find out how to
> restore from backups
> I have all system backups and mdf and ldf files of the databases - I would
> like to keep the logins, jobs etc
>
> Thanks
>

Client timeout expired, settings on 10hrs on server, how change cl

A client has to insert many files with bulk insert. I start it using windows
scripting host with VB Script and ole db provider for SQL Server. works good
and fast. Only really big files will take several minutes to terminate.
How can I set the client timeout?
I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
be installed on windows 2003 server) all on same server. Because scripts are
interacting with sql-server (I'm looking foreward to sql server 2005!!!)
I have seen, some have the same problem, but I have not seen solutions.
Do I have to use ODBC instead of OLE DE?
Thanks for any help!
See if this helps:
http://www.aspfaq.com/show.asp?id=2066
Andrew J. Kelly SQL MVP
"Urban" <urban@.nospamplease> wrote in message
news:AA015201-3F8A-465D-9A45-317870105454@.microsoft.com...
>A client has to insert many files with bulk insert. I start it using
>windows
> scripting host with VB Script and ole db provider for SQL Server. works
> good
> and fast. Only really big files will take several minutes to terminate.
> How can I set the client timeout?
> I'm using windows server2003 SP1, SQL Server2000 SP4, MDAC 2.8 (SP1 cannot
> be installed on windows 2003 server) all on same server. Because scripts
> are
> interacting with sql-server (I'm looking foreward to sql server 2005!!!)
> I have seen, some have the same problem, but I have not seen solutions.
> Do I have to use ODBC instead of OLE DE?
> Thanks for any help!

Client Problems

Hello:

I have an instance of SQL Express in my Server with the necesary configurations(enabling TCP/IP connections).

The problem is: with .udl files, I can create a connections very well in other machines, but, I my Win application can′t connect to the server. My connections string is:

"Data Source=192.168.1.106\SQLEXPRESS;Initial Catalog=Products;User ID=sa;Password=sqlsa"

I installed SQL Native Client in the machines where my application is executed (sqlncli.msi).

Thanks

Do you have SQL Browser running on the remote computer and an Exception created in the remote computers firewall? SQL Browser is required to enumerate the named instances on a server. Check out the FAQ at the top of this forum for a pointer to instructions about remote connections.

Mike

|||

Yes I have the SQL Browser running. But, the problem is in the clients. Could be a SQL Native Client?

|||

Hi,

I got the same problem.

I think this is a taff problem when connecting to remote comp from .net application

I use:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

Anyone can help us?

regards.

md5

|||

hi,

MD5 wrote:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

actually you pass to much info to your connection string.. the connection can be "trusted" or "not", but not both.. in this case, as you pass both, SQLExpress uses integrated security, and the "guest" (Windows OS based) login is (fortunately) not granted access to the SQLExpress instance..

so you have to choose waht you want..

integrated security (Integrated Security=True

OR

standard SQL Server authentication (User ID=micro;Password=micro

?

regards

Client Problems

Hello:

I have an instance of SQL Express in my Server with the necesary configurations(enabling TCP/IP connections).

The problem is: with .udl files, I can create a connections very well in other machines, but, I my Win application can′t connect to the server. My connections string is:

"Data Source=192.168.1.106\SQLEXPRESS;Initial Catalog=Products;User ID=sa;Password=sqlsa"

I installed SQL Native Client in the machines where my application is executed (sqlncli.msi).

Thanks

Do you have SQL Browser running on the remote computer and an Exception created in the remote computers firewall? SQL Browser is required to enumerate the named instances on a server. Check out the FAQ at the top of this forum for a pointer to instructions about remote connections.

Mike

|||

Yes I have the SQL Browser running. But, the problem is in the clients. Could be a SQL Native Client?

|||

Hi,

I got the same problem.

I think this is a taff problem when connecting to remote comp from .net application

I use:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

Anyone can help us?

regards.

md5

|||

hi,

MD5 wrote:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

actually you pass to much info to your connection string.. the connection can be "trusted" or "not", but not both.. in this case, as you pass both, SQLExpress uses integrated security, and the "guest" (Windows OS based) login is (fortunately) not granted access to the SQLExpress instance..

so you have to choose waht you want..

integrated security (Integrated Security=True

OR

standard SQL Server authentication (User ID=micro;Password=micro

?

regards

Client Problems

Hello:

I have an instance of SQL Express in my Server with the necesary configurations(enabling TCP/IP connections).

The problem is: with .udl files, I can create a connections very well in other machines, but, I my Win application can′t connect to the server. My connections string is:

"Data Source=192.168.1.106\SQLEXPRESS;Initial Catalog=Products;User ID=sa;Password=sqlsa"

I installed SQL Native Client in the machines where my application is executed (sqlncli.msi).

Thanks

Do you have SQL Browser running on the remote computer and an Exception created in the remote computers firewall? SQL Browser is required to enumerate the named instances on a server. Check out the FAQ at the top of this forum for a pointer to instructions about remote connections.

Mike

|||

Yes I have the SQL Browser running. But, the problem is in the clients. Could be a SQL Native Client?

|||

Hi,

I got the same problem.

I think this is a taff problem when connecting to remote comp from .net application

I use:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

Anyone can help us?

regards.

md5

|||

hi,

MD5 wrote:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

actually you pass to much info to your connection string.. the connection can be "trusted" or "not", but not both.. in this case, as you pass both, SQLExpress uses integrated security, and the "guest" (Windows OS based) login is (fortunately) not granted access to the SQLExpress instance..

so you have to choose waht you want..

integrated security (Integrated Security=True

OR

standard SQL Server authentication (User ID=micro;Password=micro

?

regards

ClickOnce path names are too long for SQL Server Express identifiers

I'm posting this in the ClickOnce forums as well...

My application doesn't include the .mdf and .ldf files, rather it creates the database the first time the application is run using a script that is included in the build. I want to create the database in the ClickOnce data directory. The problem I'm having is that SQL Server complains when I execute the CREATE DATABASE command as follows:

CREATE DATABASE [C:\Documents and Settings\xxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\DataiFieldMobile.mdf] ON PRIMARY
( NAME = N'myDatabase', FILENAME = N'C:\Documents and Settings\xxxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\Data\myDatabase.mdf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'myDatabase_log', FILENAME = N'C:\Documents and Settings\xxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.0002_0e86966f19503c89\Data\myDatabase_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
END

Here's the error:

System.Data.SqlClient.SqlException "The identifier that starts with 'C:\Documents and Settings\xxxxxx\Local Settings\Apps\2.0\Data\PQCK6EXN.5KG\AW630RPT.VGO\ifie..tion_014028c05b1d6ec6_0001.00' is too long. Maximum length is 128."

The problem lies in the name of the database. This is restricted to 128 characters only since it is an identifier like table name or index name or column name. So you need to change the database name by not specifying the full path of the MDF file there.|||

Umachandar Jayachandran - MS wrote:

The problem lies in the name of the database. This is restricted to 128 characters only since it is an identifier like table name or index name or column name. So you need to change the database name by not specifying the full path of the MDF file there.

Thanks for the prompt reply... however, I am not a T-SQL expert... how exactly do I change my script? Do I create a variable for the database name and then use it in the CREATE DATABASE command?

|||

I'm using the script generated by SQL Server. The first thing I tried doing was replacing the pathnames with a token. The application replaces the tokens with the ClickOnce data path, etc. Here's the first part of the script... How exactly do I need to modify the script?

USE [master]

GO

IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF')

BEGIN

CREATE DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] ON PRIMARY

( NAME = N'iFieldMobile', FILENAME = N'D:\Projects\DotNet\TabletPC\Release\iField 1.2.0.20\App\Data\iFieldMobile.mdf' , SIZE = 2112KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N'iFieldMobile_log', FILENAME = N'D:\Projects\DotNet\TabletPC\Release\iField 1.2.0.20\App\Data\iFieldMobile_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

END

GO

EXEC dbo.sp_dbcmptlevel @.dbname=N'D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF', @.new_cmptlevel=90

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))

begin

EXEC [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF].[dbo].[sp_fulltext_database] @.action = 'enable'

end

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_NULL_DEFAULT OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_NULLS OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_PADDING OFF

GO

ALTER DATABASE [D:\PROJECTS\DOTNET\TABLETPC\IFIELD\APP\DATA\IFIELDMOBILE.MDF] SET ANSI_WARNINGS OFF

GO

|||The issue is that the MDF file name cannot exceed 128 characters if you are using it as database name also. So you have to either specify a different name for the database or specify a shorter MDF file name. I am not sure what generates this script. Maybe you will have to post this question in the SQL Server Express forum here.

clever date to find most recent .bak file

Say I have 3 .bak files named:

jamesB.bak, jamesG.bak, jamesW.bak

Is there a clever way to find out which is the most recent of these
backup files?? Using sql query analyzer preferably...jamesd wrote:

Quote:

Originally Posted by

Say I have 3 .bak files named:
>
jamesB.bak, jamesG.bak, jamesW.bak
>
Is there a clever way to find out which is the most recent of these
backup files?? Using sql query analyzer preferably...

USE msdb;
GO
SELECT * FROM dbo.backupset;

SELECT * FROM dbo.backupfile;
SELECT * FROM dbo.backupfilegroup;
SELECT * FROM dbo.backupmediafamily;
SELECT * FROM dbo.backupmediaset;|||Hi Steve,
I don't think that will work, because I do not have the original msdb
database. I only have 3 .bak files (from another machine) and a virgin
database server (sql 2000).|||"jamesd" <jamesd@.ring4freedom.comwrote in message
news:1160592663.337156.238310@.k70g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

Hi Steve,
I don't think that will work, because I do not have the original msdb
database. I only have 3 .bak files (from another machine) and a virgin
database server (sql 2000).
>

xp_cmdshell with DIR and then read it into a table.|||Please assume that all 3 files have the same ntfs modified date. I am
looking for a way to examine each .bak file to read its metadata and
see which one is most recent.

If I restore each .bak file is there a way to see the date of the
backup?

Greg D. Moore (Strider) wrote:

Quote:

Originally Posted by

"jamesd" <jamesd@.ring4freedom.comwrote in message
news:1160592663.337156.238310@.k70g2000cwa.googlegr oups.com...

Quote:

Originally Posted by

Hi Steve,
I don't think that will work, because I do not have the original msdb
database. I only have 3 .bak files (from another machine) and a virgin
database server (sql 2000).

>
xp_cmdshell with DIR and then read it into a table.

Clearness needed in Sp_attach_db

Hi

With the sql server on line help , The syntax for the sp_attach_db has
the file name .It is aslo given that max of 16 files can be geven. I
attached a db with a single d.mdf .

could u pls tell what r the 16 file types or how a db can be attached
with 16 files.

With thanksYou can use CREATE DATABASE ... FOR ATTACH to attach a database of more
than 16 files. See the SQL 2000 Books Online
<tsqlref.chm::/ts_create_1up1.htm> for details.

BTW, sp_attach_db and sp_single_file_attach_db are basically just
wrappers for this command.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Raghuraman" <raghuraman_ace@.rediffmail.com> wrote in message
news:66c7bef8.0311160458.621723bc@.posting.google.c om...
> Hi
> With the sql server on line help , The syntax for the sp_attach_db has
> the file name .It is aslo given that max of 16 files can be geven. I
> attached a db with a single d.mdf .
> could u pls tell what r the 16 file types or how a db can be attached
> with 16 files.
>
> With thanks

clearing out the cache (deleting the temp files)

Could some one let me know how i go about clearing out the cache (deleting
the temp files) on the SQL server.
Thx.
Nero.
duplicate post
"Nero" wrote:

> Could some one let me know how i go about clearing out the cache (deleting
> the temp files) on the SQL server.
> Thx.
> Nero.

clearing out the cache (deleting the temp files)

Could some one let me know how i go about clearing out the cache (deleting
the temp files) on the SQL server.
Thx.
Nero.duplicate post
"Nero" wrote:
> Could some one let me know how i go about clearing out the cache (deleting
> the temp files) on the SQL server.
> Thx.
> Nero.

clearing out the cache (deleting the temp files)

Could some one let me know how i go about clearing out the cache (deleting
the temp files) on the SQL server.
Thx.
Nero.duplicate post
"Nero" wrote:

> Could some one let me know how i go about clearing out the cache (deleting
> the temp files) on the SQL server.
> Thx.
> Nero.

Clearing LOG Files?

I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear the log files when they become too big, without backing-up the DB?
Thanx..
The log files are not emptied when you do BACKUP DATABASE. They are emptied when you do BACKUP LOG.
If you don't do log backups, just set the database to simple recovery mode...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear
the log files when they become too big, without backing-up the DB?
> Thanx..
|||What recovery mode is your database is in?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big,
without backing-up the DB?
Thanx..
|||Rival,
Actually, the Transaction Logs are cleared when the Transaction Logs are
backed up, not when the Database is backed up. So, if only the DB is being
backed up the log will grow and grow and grow. So, you first step is to
make sure that the transaction log is being backed up.
If you need to abandon part of the log, you options are:
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
or, switch to the SIMPLE recovery model:
ALTER DATABASE dbname SET RECOVERY SIMPLE
If the physical LOG file is too large, you would need to use DBCC SHRINKFILE
to physically shrink it after truncation.
Russell Fields
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is
backed-up, but is there any way to clear the log files when they become too
big, without backing-up the DB?
> Thanx..
|||Thanx a lot.
Misconception cleared up... :-) I'll give it a try.

Clearing LOG Files?

I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear the log files when they become too big, without backing-up the DB?
Thanx..The log files are not emptied when you do BACKUP DATABASE. They are emptied when you do BACKUP LOG.
If you don't do log backups, just set the database to simple recovery mode...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is backed-up, but is there any way to clear
the log files when they become too big, without backing-up the DB?
> Thanx..|||What recovery mode is your database is in?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big,
without backing-up the DB?
Thanx..|||Rival,
Actually, the Transaction Logs are cleared when the Transaction Logs are
backed up, not when the Database is backed up. So, if only the DB is being
backed up the log will grow and grow and grow. So, you first step is to
make sure that the transaction log is being backed up.
If you need to abandon part of the log, you options are:
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
or, switch to the SIMPLE recovery model:
ALTER DATABASE dbname SET RECOVERY SIMPLE
If the physical LOG file is too large, you would need to use DBCC SHRINKFILE
to physically shrink it after truncation.
Russell Fields
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is
backed-up, but is there any way to clear the log files when they become too
big, without backing-up the DB?
> Thanx..

Clearing LOG Files?

I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big, withou
t backing-up the DB?
Thanx..The log files are not emptied when you do BACKUP DATABASE. They are emptied
when you do BACKUP LOG.
If you don't do log backups, just set the database to simple recovery mode..
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is backed-up, but is
there any way to clear
the log files when they become too big, without backing-up the DB?
> Thanx..|||What recovery mode is your database is in?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
I know that the Transaction LOG files are cleared when the DB is backed-up,
but is there any way to clear the log files when they become too big,
without backing-up the DB?
Thanx..|||Rival,
Actually, the Transaction Logs are cleared when the Transaction Logs are
backed up, not when the Database is backed up. So, if only the DB is being
backed up the log will grow and grow and grow. So, you first step is to
make sure that the transaction log is being backed up.
If you need to abandon part of the log, you options are:
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
or, switch to the SIMPLE recovery model:
ALTER DATABASE dbname SET RECOVERY SIMPLE
If the physical LOG file is too large, you would need to use DBCC SHRINKFILE
to physically shrink it after truncation.
Russell Fields
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5BBFA001-3CF0-46E7-AE86-3185C5B9CCA8@.microsoft.com...
> I know that the Transaction LOG files are cleared when the DB is
backed-up, but is there any way to clear the log files when they become too
big, without backing-up the DB?
> Thanx..|||Thanx a lot.
Misconception cleared up... :-) I'll give it a try.

Clear/Purge Log files

The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink database"
but this does not seems to reduce the mdf or ldf filesize.
What I did is detach the database, delete the ldf file, re-attach the
database to create a new ldf file. If I do not do so, the application cannot
work (hang!) because the ldf file is too huge and it takes ages to commit a
transaction. Is there a "better" way to control the ldf file like
auto-purging ? Should I restrict the log file size to a specific filesize
like 500MB ? Does this mean it will auto-purge each time it reach 500MB for
the ldf file ?
Thanks
The way you manage your log file is driven by your database recovery plan.
If your recovery plan is to restore from your last full backup and not apply
transaction log backups, then change your database recovery model to SIMPLE.
This will keep your log size reasonable by removing committed transactions
from the log. The log will still need to be large enough to accommodate
your largest single transaction. If you want to reduce potential data loss,
you should use the BULK_LOGGED or FULL recovery model and backup your log
periodically.
The proper way to shrink files is with DBCC SHRINKFILE. See the Books
Online for details. You should not need to do this as part of routine
maintenance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carlos" <wt_know@.hotmail.com> wrote in message
news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> The MDF file is 800 MB but the LDF file is 5 GB. I run the "shrink
database"
> but this does not seems to reduce the mdf or ldf filesize.
> What I did is detach the database, delete the ldf file, re-attach the
> database to create a new ldf file. If I do not do so, the application
cannot
> work (hang!) because the ldf file is too huge and it takes ages to commit
a
> transaction. Is there a "better" way to control the ldf file like
> auto-purging ? Should I restrict the log file size to a specific filesize
> like 500MB ? Does this mean it will auto-purge each time it reach 500MB
for
> the ldf file ?
> Thanks
>
>
|||Hi,
Instead of detaching , delete the LDF file, Attach the database ytou should
have tried the below steps:-
Alter database <dbname> set single_user with rollback immediate
go
backup log <dbname> to disk='d:\backup\dbname.trn1'
go
dbcc shrinkfile('logical_log_name',truncateonly)
go
Alter database <dbname> set multi_user
After executing the above you can execute the below command check the log
file size and usage,
dbcc sqlperf(logspace)
Like Dan suggested go for SIMPLE recovery model if your data is not critical
or you not require a transaction log based recovery (POINT_IN_TIME).
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
> The way you manage your log file is driven by your database recovery plan.
> If your recovery plan is to restore from your last full backup and not
apply
> transaction log backups, then change your database recovery model to
SIMPLE.
> This will keep your log size reasonable by removing committed transactions
> from the log. The log will still need to be large enough to accommodate
> your largest single transaction. If you want to reduce potential data
loss,[vbcol=seagreen]
> you should use the BULK_LOGGED or FULL recovery model and backup your log
> periodically.
> The proper way to shrink files is with DBCC SHRINKFILE. See the Books
> Online for details. You should not need to do this as part of routine
> maintenance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Carlos" <wt_know@.hotmail.com> wrote in message
> news:OueiitTPEHA.620@.TK2MSFTNGP10.phx.gbl...
> database"
> cannot
commit[vbcol=seagreen]
> a
filesize
> for
>
|||Thanks for the advices ! :-)
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MWo0lUPEHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hi,
> Instead of detaching , delete the LDF file, Attach the database ytou
should
> have tried the below steps:-
> Alter database <dbname> set single_user with rollback immediate
> go
> backup log <dbname> to disk='d:\backup\dbname.trn1'
> go
> dbcc shrinkfile('logical_log_name',truncateonly)
> go
> Alter database <dbname> set multi_user
> After executing the above you can execute the below command check the log
> file size and usage,
> dbcc sqlperf(logspace)
> Like Dan suggested go for SIMPLE recovery model if your data is not
critical[vbcol=seagreen]
> or you not require a transaction log based recovery (POINT_IN_TIME).
> Thanks
> Hari
> MCDBA
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:uTGdG0TPEHA.2256@.TK2MSFTNGP10.phx.gbl...
plan.[vbcol=seagreen]
> apply
> SIMPLE.
transactions[vbcol=seagreen]
> loss,
log[vbcol=seagreen]
> commit
> filesize
500MB
>