How to shrink transaction log

I have an application which is using SQL 2000 as
database. However, the transaction log is getting larger
and larger. I wouldn't reduce the size. The database
size is 300MB w/ used space 278MB and the transaction log
size is 6803MB w/ used space 290MB.
Is there any idea how to reduce the size of transaction
log?Quoted from Vishal :
You have various options to keep transaction log size manageable:
1.Keep taking transaction log backups at particular intervals this will help
you keep optimal transaction log size. When SQL Server finishes backing up
the transaction log, it automatically truncates the inactive portion of the
transaction log.
2.Keep transaction size as small as possible.
Refer to following urls
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH, Jens Süßmeyer.
"Ling" <Ling.Ngai@.hk.standardchartered.com> schrieb im Newsbeitrag
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||I refer you to a useful post by Andrew kelly
Either change to Simple Mode or issue regular log backups. If your are
issuing timely log backups and it still continues to grow you most likely
have an open transaction stopping the truncation. Use DBCC OPENTRAN to see.
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?|||Try using this code.
You will need to change the <logname> and <databasename> with your
transaction log name and the database name. <Desired_final_size_in_MB> is
optional parameter. This script works great and I use it regularly.
DBCC SHRINKFILE (<logname> , <DESIRED_FINAL_SIZE_IN_MB>,TRUNCATEONLY )
go
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
go
drop table mytable
go
CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO
SET NOCOUNT ON
DECLARE @.Index INT
SELECT @.Index = 0
WHILE (@.Index < 20000)
BEGIN
UPDATE MyTable
SET MyField = MyField
WHERE PK = 1 /* Some criteria to restrict to one row. */
SELECT @.Index = @.Index + 1
END
SET NOCOUNT OFF
go
DBCC SHRINKFILE ( <logname> ,<DESIRED_FINAL_SIZE_IN_MB>, truncateonly )
go
BACKUP LOG < databasename > WITH TRUNCATE_ONLY
go
"Ling" <Ling.Ngai@.hk.standardchartered.com> wrote in message
news:07c001c37b78$e8e54f60$a101280a@.phx.gbl...
> I have an application which is using SQL 2000 as
> database. However, the transaction log is getting larger
> and larger. I wouldn't reduce the size. The database
> size is 300MB w/ used space 278MB and the transaction log
> size is 6803MB w/ used space 290MB.
> Is there any idea how to reduce the size of transaction
> log?

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
Ellis
Ellis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison
sql

How to show Crystal report 11 in .NET 2.0

Hi all i want to load an rpt file my web application. I want to load rpt other than rdlc file for some specific reason.

I have Visual studio 2005 standard edition installed in my system. And also crystal report 11 developper version installed.

I am working on a ASP application. How can i load RPT file in to my web pages. can anyone help me in this matter.

I want to create the RPT file at runtime using report designed dll in version 11 and i am doing it success fully.

Now the problem is with loading the rpt file in web page can any one help me in this regard.plz

check this out

http://www.developerfusion.co.uk/show/4266/

how to show all the permission for a role

Hello,
I have an application database that has a role with specific permission to
each objects like table, proc, views.
There are about 200+ objects with different permission. some are select,
insert, ddr.
How can I write a SQL statement to show a report of this role with all the
different objects permissions.
I was able to go to role in Enterprise Manager and select permission.
I need an excel report. It would best it I can get the same results that I
see in EM using Query Analyzer.
You can use Northwind.
Any suggestions.Hi,
Execute the system stored procedure
sp_helprotect null,<Role Name>
Thanks
Hari
SQL Server MVP
"SQL Apprentice" wrote:

> Hello,
> I have an application database that has a role with specific permission to
> each objects like table, proc, views.
> There are about 200+ objects with different permission. some are select,
> insert, ddr.
> How can I write a SQL statement to show a report of this role with all the
> different objects permissions.
> I was able to go to role in Enterprise Manager and select permission.
> I need an excel report. It would best it I can get the same results that
I
> see in EM using Query Analyzer.
> You can use Northwind.
> Any suggestions.
>
>|||Thank you Hari.
"Hari Pra" <HariPra@.discussions.microsoft.com> wrote in message
news:FEB28171-446D-4EEB-8708-8BEFA4D3B0DA@.microsoft.com...
> Hi,
> Execute the system stored procedure
> sp_helprotect null,<Role Name>
> Thanks
> Hari
> SQL Server MVP
>
> "SQL Apprentice" wrote:
>
to
the
that I

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
EllisEllis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
Ellis
Ellis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to setup two equal databases environment

Dear all,
I have an application running on a production database. And now, I
want to change a column of data to another of value and compare the result
between them. Since the data in production is changing. I need to
synchronize two set of data except the testing data. For do that, I build
replication on these two database. This is my first time using replication.
I found there's an initialize error on during synchronization job. Is there
any guideline on setup replication? Thanks
Rdgs
EllisEllis,
can you explain this in a little more detail using column names and
explaining the relationship between the databases. Also, what type of
replication have you implemented.
TIA,
Paul Ibison

How to set NT Authority in sql 2000?

We have an application store in a web server using IIS 6.0, however the application is communicating to sql 2000 that is stored in a 2000 server.

In IIS 6.0 the application pool is set to Network Services, therefore sql 2000 is looking for a user called 'NT AUTHORITY\ANONYMOUS LOGON'.

But the login failed, and I don't see how to add NT Authority as a user in a 2000 server like you can in a 2003 server.

In all, we are trying to set permissions to certain users only by using the identity impersonate='true' in the web config file, but even with this setting set to true, and our network information set as a user in sql 2000, we still are denied access to the data.

What are some things we need to look for and what configuration do we need to set?

Thanks,

xyz789

P.S.
We also have all the permissions check to have access to all the tables and store procedures for each user.

Hi,

Are you trying integrated security and impersonating the current asp.net user?

Remove the anonymous access from IIS. Make sure in the authentication, only 'Integrated Windows
authentication' is checked.

You may need kerberos authentication.

http://msdn.microsoft.com/msdnmag/issues/05/09/SecurityBriefs/

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

Thank you!

How to set my ASP.NET application to access SQL Server 2005 using clients user credentials

Hi guys,

I'm not sure if I'm just bad at googling but I can't seem to find a way to set an ASP.NET 2.0 web application to connect to SQL Server 2005 using the current client's user credentials. My web application is using Integrated Windows Authentication so its Page.User.Identity is set to a DOMAIN\username value... I want to pass that to my connectionstring or have my connections pick up the identity automatically and use that Identity when accessing the db server.

Oh and another thing, my IIS Application Pool is using a specific Identity itself, so I don't know if that might affect the above.

Hope someone could help.

Hi,

what you need is process impersonation, that allows asp.net to switch the execute owner to an other user. I have used this for asp.net and remoting calls, so that i have the original caller. I have two links for you:

http://msdn2.microsoft.com/en-us/library/ms998292.aspx|||Thanks Zhou. I'll the articles out once I get my hands on our dev server. Thanks.

How to set connection at runtime

Hi,
Our application connects to one database in the testing environment and to a
different database in the production environment. Currently, with Crystal
Reports, we maintain a single set of reports, and at runtime, when the
reports are loaded, the application sets the report's connection acordingly:
if the application is comnnected to the production server, the report's
connection is set to the production server, otherwise to the test server.
Using MS Reporting Services, how can the report's connection be set at
runtime from the application before the report connects to the database?
Thank you in advance,
RichardHi Richard,
One way you can set the connection at runtime is through a user input
parameter. You would just have to reference Parameter!param.Value in
the connection string. There may be other ways to do it dynamically
without the user having to input data, but I am not sure if there is a
way for MS Reporting Services to tell which environment it is in. Good
luck!
Lance M.
Richard wrote:
> Hi,
> Our application connects to one database in the testing environment and to a
> different database in the production environment. Currently, with Crystal
> Reports, we maintain a single set of reports, and at runtime, when the
> reports are loaded, the application sets the report's connection acordingly:
> if the application is comnnected to the production server, the report's
> connection is set to the production server, otherwise to the test server.
> Using MS Reporting Services, how can the report's connection be set at
> runtime from the application before the report connects to the database?
> Thank you in advance,
> Richard