Release Database Transaction Log Used Space After Database Backup

What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log.
A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,

Release Database Transaction Log Used Space After Database Backup

What are the SQL Server commands that will shrink or release database
transaction log used disk space afer the database backup has completed?
Thank You,These two things are not related. A database backup will not empty the log. A log backup will,
however. To then shrink the log file, use DBCC SHRINKFILE. But read
http://www.karaszi.com/SQLServer/info_dont_shrink.asp first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:85D71BCF-F22C-4035-8A02-16771C713B58@.microsoft.com...
> What are the SQL Server commands that will shrink or release database
> transaction log used disk space afer the database backup has completed?
> Thank You,

Relationship constraints

Hi,

I am trying to get the best solution for my problem:

I have 2 tables. Customer and Transaction. A transaction can contain a customer but not always. As I see it I would have 3 options in doing this.

1. Create a CustomerId field in the Transaction table and create relationship on the field but not enforced it and allow NULL or 0 for non customer related transactions.

2. Same setup as 1 but load a "dummy" customer and setup the constraints correctly. But I have a problem that I already have data in my customer table, so I can't take the easy route and only select the 1st customer as my "dummy" customer.

3. Create a CustomerTransaction table with TransactionId and CustomerId and create proper relationships.

What would be the best practice handeling the above scenario?

Regards,

Adriaan

Hi Adrian,

Remember that a foreign key constraint can ban be built upon a nullable column, so if you had migrated your CustomerID column from table Customer to table Transaction, and created it as nullable, then this would meet your needs (your option 1)

Do not go down the path of Option 2 as it's always ugly...

Option 3 will still leave you with a table identifying that a particular transaction did not involve a customer (a null CustomerID in the CustomerTransaction table) - this is no different then option 1 but introduces additional entities.

You've posed a design question, so I think you'll find that there is no "correct" answer and my idea of best practice will not be some elses ;)

Cheers,

Rob

|||Hi Robert,

Thanks for the reply I never realized that foreign key constraints can be built upon nullable columns, I just assumed that it would not be possible.

I always like to have a simple table structure and try not to introduce extra entities where I should not.

Thanks again!

Reinstalling MS DTC

Here I go for last time, I promise.
I uninstalled MS DTC (in an stupid attemp to avoid a distributed transaction
to start), by doing :
net stop MSDTC
MSDTC - uninstall
After some charitative aim told me what I wanted to do is not possible, I
decided to reinstall :
MSDTC -install
net start MSDTC
I can see the "Distributed Transaction Coordinator" item, in the service
manager, it is running for sure.
Also, the Services console shows it as "started".
But I still getting the message MSDTC on server 'myserver' is unavailable'.
I've repeated the operation a couple of times, to be sure (deinstall and
reinstall) and still not working.
Actually, I recall yesterday I was able to stop and restart MSDTC without
problem, several times, but I was just doing that : stop & restart.
Today it happens that I "uninstall" it and then it is not like before any
more.
I'm using an account with Administrator privilegies (Windows 2000 Advanced
Server / SQL Server 2000 Enterprise)
Is there something else I need to do to ? should I need to restart the SQL
Server ? SQL Agent ?
Any help is greatly appreciated.
Regards,Refer to the following MSKB article:
How To Reinstall MS DTC for a Nonclustered Windows 2000 Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;279786
Michael D. Long
Microsoft MVP - Windows SDK
"Craig Kenisston" <craigkenisston@.hotmail.com> wrote in message
news:%23c9KX9IhEHA.1656@.TK2MSFTNGP09.phx.gbl...
> Here I go for last time, I promise.
> I uninstalled MS DTC (in an stupid attemp to avoid a distributed
transaction
> to start), by doing :
> net stop MSDTC
> MSDTC - uninstall
>
> After some charitative aim told me what I wanted to do is not possible, I
> decided to reinstall :
> MSDTC -install
> net start MSDTC
> I can see the "Distributed Transaction Coordinator" item, in the service
> manager, it is running for sure.
> Also, the Services console shows it as "started".
> But I still getting the message MSDTC on server 'myserver' is
unavailable'.
> I've repeated the operation a couple of times, to be sure (deinstall and
> reinstall) and still not working.
> Actually, I recall yesterday I was able to stop and restart MSDTC without
> problem, several times, but I was just doing that : stop & restart.
> Today it happens that I "uninstall" it and then it is not like before any
> more.
> I'm using an account with Administrator privilegies (Windows 2000 Advanced
> Server / SQL Server 2000 Enterprise)
> Is there something else I need to do to ? should I need to restart the SQL
> Server ? SQL Agent ?
> Any help is greatly appreciated.
>
> Regards,
>

Reindexing and Transaction Logs

Hi All,

Just after some feedback on a scenario where we have full logging setup on one of the databases, and the transaction logs are backed up every 60min. At 0000-0100 the log jumps from being a few thousand k up to over 1.7gb.
I did some profiling for this time, and it appears that this jump is related to the reindexing of the indexes on the database.

Is this normal for the log file to jump in size so much? Or is this an indication of some other issue (potentially with the indexes)?

Is there any way that the reindexing can be excluded from the log files or is this a necessity?

Thanks in advance for your help.

Cheers
TroyIt is quite normal for re-indexing to fill up a transaction log. Especially if you use dbcc indexdefrag.|||So I presume there is no 2 ways around this really then? Massive jump in size, which makes for some interesting issues in terms of planning for space *sigh*...|||Your log may stay smaller with database recovery model set to SIMPLE|||haha thanks for that - umm but then I loose my logging...sorta defeats the point of backing up the logs :P
But yeah I hear what you are all saying - suppose I was just after a solution which would allow me to turn off the logging for the index rebuilds and leave it on for all the remainder of the normal i/u/d's in the database...

Thanks all, have a good Christmas/Break/etc...