reg. performance

Is there anyway pull logs and statistical information regarding performance
and Transaction database ?
Thanks
Kalyan
If you are talking about reading tran. log then there is 3rd party tool
called lumigent log explorer. Do a google search for it. On the otherhand if
the question is regarding performance monitoring, then read BOL on the topics
like SQL profiler and windows performance moniter. The DBA is required to
baseline the values of the performance counters when the SQL is functioning
normally. This base line will help you to compare the trace logs at the time
of bad performance and see what is the problem in event of performance
degradation.
Thanks
Chinna.
"Kalyan" wrote:

> Is there anyway pull logs and statistical information regarding performance
> and Transaction database ?
>
> Thanks
> Kalyan

reg. performance

Is there anyway pull logs and statistical information regarding performance
and Transaction database ?
Thanks
KalyanIf you are talking about reading tran. log then there is 3rd party tool
called lumigent log explorer. Do a google search for it. On the otherhand if
the question is regarding performance monitoring, then read BOL on the topics
like SQL profiler and windows performance moniter. The DBA is required to
baseline the values of the performance counters when the SQL is functioning
normally. This base line will help you to compare the trace logs at the time
of bad performance and see what is the problem in event of performance
degradation.
Thanks
Chinna.
"Kalyan" wrote:
> Is there anyway pull logs and statistical information regarding performance
> and Transaction database ?
>
> Thanks
> Kalyan

Referential Integrity - linking multiple tables to transaction table

I have transaction table where the rows entered into the transaction
can come a result of changes that take place if four different tables.
So the situation is as follows:

Transaction Table
-TranId
-Calc Amount

Table 1 (the amount is inserted into the transaction table)
- Tb1Id
- Tb1Amt

Table 2 (an amount is calculated based on the percentage and inserted
into the transaction table)
-Tbl2Id
-Tb2Percentage

Table 3 (the amount is inserted into the transaction table)
-Tbl3Id
-Tbl3Amut

Table 4 (an amount is calculated based on the percentage and inserted
into the transaction table. )
-Tbl2Id
-Tb2Percentage

How do I create referential integrity between the Transaction table and
the rest of the tables. When I make changes to the values in Table 1 -
4, I need to be able to reflect this in the Transaction table.

Thanks.Can you make "Transaction Table" into a view
based on joins between your other 4 tables?

Where does TransID come from?|||Thanks,

Trans ID is generated - identify column.

We thought of using a view. But the when the core object is set to
LIVE, all changes from then on will be tracked as new transactions in
the transaction table. So in essence, you have have a row in Table 1 -
4 that has multiple transactions in the transacation table. We could
add a column to the Transaction Table set it to refer to the Ids of
Table 1 - 4. But we won't be able to use the DBMS contraints to enfore
this. Possibly a middle table to each Table 1 to 4?|||(heyvinay@.gmail.com) writes:
> I have transaction table where the rows entered into the transaction
> can come a result of changes that take place if four different tables.
> So the situation is as follows:
> Transaction Table
> -TranId
> -Calc Amount
> Table 1 (the amount is inserted into the transaction table)
> - Tb1Id
> - Tb1Amt
> Table 2 (an amount is calculated based on the percentage and inserted
> into the transaction table)
> -Tbl2Id
> -Tb2Percentage
> Table 3 (the amount is inserted into the transaction table)
> -Tbl3Id
> -Tbl3Amut
> Table 4 (an amount is calculated based on the percentage and inserted
> into the transaction table. )
> -Tbl2Id
> -Tb2Percentage
> How do I create referential integrity between the Transaction table and
> the rest of the tables. When I make changes to the values in Table 1 -
> 4, I need to be able to reflect this in the Transaction table.

Depends on what you mean with changes, but obviously if you change an
amount in one table and you want that to affect the CalcAmount in the
Transaction table, then you should consider a trigger. For that to
work, the Transaction table need to have one FK column per referencing
table.

Your description was quite brief, and very abstract. It is not at all
impossible that there is a better design, if you can give more meat of
what is behind the various tables.

--
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|||Thanks.
Well the application we are designing is for Premium calcuation. The
transaction table will store all the premium calcuation breakdowns.
The reference to table 1 - 4 are various elements within the system
that will generated chargeable premiums. Eg. the product selected;
discounts applied to the customer; specific endorsements; Payment
menthod discounts etc. Once policy is live, and changes to the source
tables (eg product, discount amount) etc or even the cancellation of
the policy will raise NEW records in the transaction tables.

We can manage all this from code directly to ensure all is in sync, but
I prefer to apply referential integrity managed by the DBMS.

You wrote: "For that to work, the Transaction table need to have one FK
column per referencing table. " - does MS SQL allow null values for
foreign key constraints?

Thanks|||(heyvinay@.gmail.com) writes:
> We can manage all this from code directly to ensure all is in sync, but
> I prefer to apply referential integrity managed by the DBMS.

Referential integrity is about the integrity of - references. That is,
if the Orders table has a FK constraint to table Customers, you cannot
add an Order for a non-existing customer, and you cannot delete a customer
that has an order.

As I understood it, you want one data in one table be the result of data
in other tables. This cannot be achieved with referential integrity. You
can use views, and under some circumstances you can materialise a view.

> You wrote: "For that to work, the Transaction table need to have one FK
> column per referencing table. " - does MS SQL allow null values for
> foreign key constraints?

Yes. For instance, in a Customers table, you may have Citizen column
table that has a foreign-key constraint to the Countries table. But
this column must be nullable, since some people stateless. Not talking
of juridical persons, who normally are not citizens.

--
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|||Off the top of my head, two fundamental ways.

Insert into the 4 tables, and have triggers on those tables for insert,
update, and delete that add rows to the trans table.

Two. utilize standardized stored procedures you use to add/edit/update
the 4 tables. Inside the stored procedures, do a begin trans/ commit
around the changes and the trans table. In this way, you can pretty
much ensure that it will keep up.

The stored procedure has the advantage of allowing more business logic
around changes. You can validate data changes external to the app.
However, you MUST enforce that all changes to the 4 tables MUST uses
the specified stored procedures.

The Triggers have the advantage of "simplicity." Again though,. you are
somewhat limited in what logic you can use.

Two comments you didn't ask for. Be very stingy with indexes on the
trans table. I'm not saying ot have none, BUT don't have a lot of
indexes. And the indexes you do have should be fairly unique. Trans
tables get very large very fast, and they really slow everything down
in a sneaky insidious sort of way in a few years.

Secondly, it can be hard to get the :"right" infomration into the trans
table. As a test to see whether you have all data you need, manually
try to take each trans record, and recreate the final data results. In
a perfect world, your trans table will allow a picture of exactly what
changed when and how, allowing hte final results to be seen.
For a final test, just go ahead and write the program that will read
the trans data and recreate teh table. This is almost never trivial,
but almost always worthwhile.
-dougsql

Reducing the size of the Transaction Log.

We have a SQL Server 2000 SP3 database with recovery model set to full. I
set up 3 automatic backup jobs as follows:
Each day,Mon thru Fri at 9:00 am
1 step - full backup of database to file A with overwrite
Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
1 step - transaction log backup of database to file A with append
Each day,Mon thru Fri at 5:05 pm
1st step - transaction log backup of database to file A with append
2nd step - execute BACKUP LOG WITH NO_LOG
3rd step - execute DBCC SHRINK (db_file_name,1)
First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want, even
though I back up the Log.
When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
However, I get an error in the Event Viever(Application Log) stating
"Database Log truncated".
My question is:
Should I worry about this error ?
Is any data loss likely will this arrangement ?
After all, I truncate the Transaction Log on purpose, right after backing it
back in the previous step.
Regards,
SezginYou should not use the no_log command if you require point in time recovery,
and if you do not require point in time recovery, set your recovery mode to
simple. I would advise not to be shrinking your log or data file like you
are as if the db or log has to autogrow it costs you at the point it needs
to allocate more space and that is typically when your db is busy.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> We have a SQL Server 2000 SP3 database with recovery model set to full. I
> set up 3 automatic backup jobs as follows:
> Each day,Mon thru Fri at 9:00 am
> 1 step - full backup of database to file A with overwrite
> Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> 1 step - transaction log backup of database to file A with append
> Each day,Mon thru Fri at 5:05 pm
> 1st step - transaction log backup of database to file A with append
> 2nd step - execute BACKUP LOG WITH NO_LOG
> 3rd step - execute DBCC SHRINK (db_file_name,1)
> First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
> NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
even
> though I back up the Log.
>
> When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> However, I get an error in the Event Viever(Application Log) stating
> "Database Log truncated".
> My question is:
> Should I worry about this error ?
> Is any data loss likely will this arrangement ?
> After all, I truncate the Transaction Log on purpose, right after backing
it
> back in the previous step.
> Regards,
> Sezgin
>
>|||Thanks for your reply Ray,
The database has a high number of transactions every day. I have to make
sure any possible data loss is minimized. That is why I used full recovery
model. During tests, in about 5 weeks time, the Transaction Log grew up to
nearly 7 GB (the data file currently is just over 1 GB). I tried my best to
shrink the Transaction Log without using the "Backup Log with no_log"
command, but to no success. Could you offer any advice or alternative
solution.
Best regards,
Sezgin Rafet
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> You should not use the no_log command if you require point in time
recovery,
> and if you do not require point in time recovery, set your recovery mode
to
> simple. I would advise not to be shrinking your log or data file like you
> are as if the db or log has to autogrow it costs you at the point it needs
> to allocate more space and that is typically when your db is busy.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > We have a SQL Server 2000 SP3 database with recovery model set to full.
I
> > set up 3 automatic backup jobs as follows:
> >
> > Each day,Mon thru Fri at 9:00 am
> > 1 step - full backup of database to file A with overwrite
> >
> > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > 1 step - transaction log backup of database to file A with append
> >
> > Each day,Mon thru Fri at 5:05 pm
> > 1st step - transaction log backup of database to file A with append
> > 2nd step - execute BACKUP LOG WITH NO_LOG
> > 3rd step - execute DBCC SHRINK (db_file_name,1)
> >
> > First I tried to shrink the Log file without issuing the "BACKUP LOG
WITH
> > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> even
> > though I back up the Log.
> >
> >
> > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > However, I get an error in the Event Viever(Application Log) stating
> > "Database Log truncated".
> >
> > My question is:
> > Should I worry about this error ?
> > Is any data loss likely will this arrangement ?
> >
> > After all, I truncate the Transaction Log on purpose, right after
backing
> it
> > back in the previous step.
> >
> > Regards,
> >
> > Sezgin
> >
> >
> >
> >
>|||THe log should eventually shrink, and you should NOT be using backup log
with no_log...
I noticed however that you said you were using dbcc shrink...
The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
be part of the problem?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply Ray,
>
> The database has a high number of transactions every day. I have to make
> sure any possible data loss is minimized. That is why I used full recovery
> model. During tests, in about 5 weeks time, the Transaction Log grew up to
> nearly 7 GB (the data file currently is just over 1 GB). I tried my best
to
> shrink the Transaction Log without using the "Backup Log with no_log"
> command, but to no success. Could you offer any advice or alternative
> solution.
>
> Best regards,
>
> Sezgin Rafet
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > You should not use the no_log command if you require point in time
> recovery,
> > and if you do not require point in time recovery, set your recovery mode
> to
> > simple. I would advise not to be shrinking your log or data file like
you
> > are as if the db or log has to autogrow it costs you at the point it
needs
> > to allocate more space and that is typically when your db is busy.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > We have a SQL Server 2000 SP3 database with recovery model set to
full.
> I
> > > set up 3 automatic backup jobs as follows:
> > >
> > > Each day,Mon thru Fri at 9:00 am
> > > 1 step - full backup of database to file A with overwrite
> > >
> > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > 1 step - transaction log backup of database to file A with append
> > >
> > > Each day,Mon thru Fri at 5:05 pm
> > > 1st step - transaction log backup of database to file A with append
> > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > >
> > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> WITH
> > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> > even
> > > though I back up the Log.
> > >
> > >
> > > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > > However, I get an error in the Event Viever(Application Log) stating
> > > "Database Log truncated".
> > >
> > > My question is:
> > > Should I worry about this error ?
> > > Is any data loss likely will this arrangement ?
> > >
> > > After all, I truncate the Transaction Log on purpose, right after
> backing
> > it
> > > back in the previous step.
> > >
> > > Regards,
> > >
> > > Sezgin
> > >
> > >
> > >
> > >
> >
> >
>|||Thanks Wayne,
I used DBCC SHRINKFILE(db_log_file_name,1) , just have mistyped it in the
post.
Sezgin Rafet
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23G0pjgIyDHA.2440@.TK2MSFTNGP12.phx.gbl...
> THe log should eventually shrink, and you should NOT be using backup log
> with no_log...
> I noticed however that you said you were using dbcc shrink...
> The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
> be part of the problem?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks for your reply Ray,
> >
> >
> >
> > The database has a high number of transactions every day. I have to make
> > sure any possible data loss is minimized. That is why I used full
recovery
> > model. During tests, in about 5 weeks time, the Transaction Log grew up
to
> > nearly 7 GB (the data file currently is just over 1 GB). I tried my best
> to
> > shrink the Transaction Log without using the "Backup Log with no_log"
> > command, but to no success. Could you offer any advice or alternative
> > solution.
> >
> >
> >
> > Best regards,
> >
> >
> >
> > Sezgin Rafet
> >
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > > You should not use the no_log command if you require point in time
> > recovery,
> > > and if you do not require point in time recovery, set your recovery
mode
> > to
> > > simple. I would advise not to be shrinking your log or data file like
> you
> > > are as if the db or log has to autogrow it costs you at the point it
> needs
> > > to allocate more space and that is typically when your db is busy.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > > We have a SQL Server 2000 SP3 database with recovery model set to
> full.
> > I
> > > > set up 3 automatic backup jobs as follows:
> > > >
> > > > Each day,Mon thru Fri at 9:00 am
> > > > 1 step - full backup of database to file A with overwrite
> > > >
> > > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > > 1 step - transaction log backup of database to file A with append
> > > >
> > > > Each day,Mon thru Fri at 5:05 pm
> > > > 1st step - transaction log backup of database to file A with append
> > > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > > >
> > > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> > WITH
> > > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I
want,
> > > even
> > > > though I back up the Log.
> > > >
> > > >
> > > > When I use BACKUP LOG WITH NO_LOG everything seems to be working
OK.
> > > > However, I get an error in the Event Viever(Application Log) stating
> > > > "Database Log truncated".
> > > >
> > > > My question is:
> > > > Should I worry about this error ?
> > > > Is any data loss likely will this arrangement ?
> > > >
> > > > After all, I truncate the Transaction Log on purpose, right after
> > backing
> > > it
> > > > back in the previous step.
> > > >
> > > > Regards,
> > > >
> > > > Sezgin
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Reducing the size of the transaction log file

At this time I am only playing with the applications that generate the data and send it to the database. Without doing too much, and with deleting most data tables that were created, my transaction log file has grown over a gigabyte. I tried using the SQL server management studio (express) to shrink the database (tried shrinking files, too) but that did not make the file smaller. Right now there is hardly any data in the database (6 tables, a dozen columns and rows each) so it must be old transactions that are kept in the log. How do I get rid of the old data and make the file size smaller? Thanks.
KamenHi,

I had the same issue yesterday, i had to, Backup the log file then use shrink, backup one moore time and shrink.|||1. Check your database 'Recovery Model', set it according to your requirement.
2. Make a backup plan matching your data sensitivity & requirement as well as schedule it.
3. Plan Log backup along with Log Truncate option.

Refer Books OnLine from SQL Query Analyzer.|||Thank you very much!
Kamen

Reducing physical transaction log file size.

I need some help to reduce the physical file size of a database transaction
log. I ran the shrink database option under the System Manager and was able
to shring the logical size of the log file, however, I would also like to
reduce the physical size of the file to free up disk space. I have tried to
run the DBCC Shrinkfile and DBCC shrinkdatabase commands but that does not
appear to be successfull. Can anybody help? I am running SQL 2000. Thanks
.http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Elbryyan" <Elbryyan@.discussions.microsoft.com> wrote in message
news:67FAF140-014E-4CB2-800E-740AF1B69212@.microsoft.com...[vbcol=seagreen]
>I need some help to reduce the physical file size of a database transaction
> log. I ran the shrink database option under the System Manager and was ab
le
> to shring the logical size of the log file, however, I would also like to
> reduce the physical size of the file to free up disk space. I have tried
to
> run the DBCC Shrinkfile and DBCC shrinkdatabase commands but that does not
> appear to be successfull. Can anybody help? I am running SQL 2000. Thanks.[/vbc
ol]

Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K

Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
Sam
You can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.

> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>
|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegr oups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>

Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K

Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>

Reduce the 'Space Allocated' for Transaction Logs in MS SQL 2K

Hey Guys --
We have a database that gets lots of activity, but it's not mission
critical data, so no need for a backup. Problem is the Transaction Log
was set to Automatically Grow, and over a year or so it grew to over 50
gigs and filled-up the HD.
We did a backup and the transaction log is down now, but the Space
Allocated is still sitting at 50 Gigs and locking that space for
transaction logs not leaving any room for anything else. How can I
change this? I went into the DB properties and tried to change the
Space Allocated to like 100 (Megs) but I get the error: The new DBFile
size must be larger than the current size. How can I reduce this
filesize?
Thanks for any insight or ideas.
SamYou can shrink the file using DBCC SHRINKFILE. See the Books Online for
details.
Also, assuming SQL 2000, change the database recovery model to SIMPLE if you
don't need the transaction log for recovery.

> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup.
Do you mean transaction log backup? If you perform no full database
backups, your only option in the event of database loss is to recreate the
database from script.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>|||Shrink that pig !
Backup T-Log
DBCC SHRINKFILE(FIleName,0)
Change Growth Settings...
"Alex" <samalex@.gmail.com> wrote in message
news:1134491880.285851.217750@.g44g2000cwa.googlegroups.com...
> Hey Guys --
> We have a database that gets lots of activity, but it's not mission
> critical data, so no need for a backup. Problem is the Transaction Log
> was set to Automatically Grow, and over a year or so it grew to over 50
> gigs and filled-up the HD.
> We did a backup and the transaction log is down now, but the Space
> Allocated is still sitting at 50 Gigs and locking that space for
> transaction logs not leaving any room for anything else. How can I
> change this? I went into the DB properties and tried to change the
> Space Allocated to like 100 (Megs) but I get the error: The new DBFile
> size must be larger than the current size. How can I reduce this
> filesize?
> Thanks for any insight or ideas.
> Sam
>

reduce num of transaction files

I have two transaction log files and I want to reduce it to one. How do I de
lete one and make sure I'm not losing any information? Can I just back up th
e transaction logs, then delete the 2nd file and it will continue logging tr
ansactions to the remaining
file?
Thanks!Hi,
It is not possible to migrate the transaction log data from one log file to
another to delete a transaction log file.
To purge the transactions from a transaction log file, the transaction log
must be truncated (Backup log <dbname> with truncate_only) or
backed up (Backup log <dbname> to disk='c:\backup\dbanme.trn).
Once the transaction log file no longer contains any active or inactive
transactions, the Empty log file can be removed from the database.
Steps to remove the Empty Log file:
backup log <dbname> to disk='c:\backup\dbname.trn'
go
use <dbname>
go
dbcc shrinkfile('logical_log_filename_to_dele
te','emptyfile')
go
alter database <dbname> remove file 'logical_log_file_name_to_delete'
Thanks
Hari
MCDBA
"holly" <anonymous@.discussions.microsoft.com> wrote in message
news:1ABE2C6B-C01F-4D16-A689-E61E1A2E68C6@.microsoft.com...
> I have two transaction log files and I want to reduce it to one. How do I
delete one and make sure I'm not losing any information? Can I just back up
the transaction logs, then delete the 2nd file and it will continue logging
transactions to the remaining file?
> Thanks!

Reduce Log Size

I have a database here that's 3.3 GB that has a transaction log of 14.8 GB.
We want to make the log WAY smaller. like doing a checkpoint or backup with
log truncation or something.
Got some BOL / code snips for us?
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneThere's some info about log file shrinking in here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message news:up96UXaBGHA.4016@.TK2MSFTNGP11.phx
.gbl...
>I have a database here that's 3.3 GB that has a transaction log of 14.8 GB.
> We want to make the log WAY smaller. like doing a checkpoint or backup wi
th log truncation or
> something.
> Got some BOL / code snips for us?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||How to truncate the Transaction Log:
http://msdn.microsoft.com/library/d...r />
_7vaf.asp
And just as important; why your transaction log may grow to a large size
between backups and how to minimize it's growth:
http://support.microsoft.com/kb/317375/
http://support.microsoft.com/defaul...kb;en-us;873235
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:up96UXaBGHA.4016@.TK2MSFTNGP11.phx.gbl...
>I have a database here that's 3.3 GB that has a transaction log of 14.8 GB.
> We want to make the log WAY smaller. like doing a checkpoint or backup
> with log truncation or something.
> Got some BOL / code snips for us?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>