Mirroring and Log File Growth

We currently have a 10GB database that is functioning properly mirrored. The only issue we have is that the log files grow very quickly during the early morning hours when a large number of transactions hit our DB from scheduled jobs. We have transaction log backup and shrink job that runs every 3 hours to backup the log and shrink the logical file to 10GB. In most cases this will shrink the log back down to it's desired size of 10GB. However, on some mornings it takes more repetitions of the log backup/shrink job to return the log size back to "normal". During this these times when it does not shrink the DB effectively, I get a report of the job process by email that states "Cannot shrink log file 2 (e_Log) because all logical log files are in use." I run a DBCC OPENTRAN command, and there are no open transactions. Eventually, the log file will return to it's normal size of 10GB through the log file backup job. I have a few questions though.

1. Is this normal behavior during moderate database use?
2. Does mirroring have any effect on the SIZE of the log file?
3. Is it normal for the size of the data file to be smaller than the size of the log file on a heavily used database?
4. Does anyone have any suggestions to better maintain the log file size?

Thank you,
Greg

Mirroring won't affect the log, unless the Principal can't connect to the mirror in which case transactions will queue in the log and may cause issues.

Why are you shrinking your log? It should be as big as it needs to be, i.e. large enough to hold the transactions from your scheduled jobs. Having it auto-grow then re-shinking it is a waste of time and resources (it will slow down your scheduled jobs while the log grows).

The reason you can't shrink it sometimes is because the log actually consists of Virtual Log Files (VLFs). If the active one of these is near the end of the log file, it won't be shrinkable until it moves back to the beginning of the log file due to transactions having happened.

Normally the log file is smaller than the data file, but there may be exceptions. The bottom line is, the log needs to be as big as is required by the size and volume of transactions using the database.

Ensure you have adequate log backups of a suitable frequency all the time, and especially during your batch processing.

mirror db blocking issue

Hello All
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
Paul
That isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.com/sf_block_prevention.asp
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegro ups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>
|||You are absolutely right, I was being a dumb-XXX
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P
|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegrou ps.com...
> You are absolutely right, I was being a dumb-XXX
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>
sql

mirror db blocking issue

Hello All
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
PaulThat isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.com/sf_block_prevention.asp
--
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegroups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>|||You are absolutely right, I was being a dumb-ass
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
--
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegroups.com...
> You are absolutely right, I was being a dumb-ass
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>

mirror db blocking issue

Hello All
I've set up a job to backup our financial db and restore it to the same
machine (different files & db name) for reporting. This was to avoid a
blocking issue caused by the front end financial system.
The thing is, the blocking is still there, even when you are reporting
on the mirror database and the blocking process is on the other db!
Is this something to do with the objects having the same names/indexes
in sysobjects? Any ideas on how to resolve it?
Thanks!
PaulThat isn't possible. I suspect you have more to this than you are reporting.
Locks in one db do not affect queries in another unless the query or
transaction is across both dbs. You might want to have a look at these:
http://support.microsoft.com/kb/271509
http://www.sql-server-performance.c..._prevention.asp
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165263399.585739.293410@.16g2000cwy.googlegroups.com...
> Hello All
> I've set up a job to backup our financial db and restore it to the same
> machine (different files & db name) for reporting. This was to avoid a
> blocking issue caused by the front end financial system.
> The thing is, the blocking is still there, even when you are reporting
> on the mirror database and the blocking process is on the other db!
> Is this something to do with the objects having the same names/indexes
> in sysobjects? Any ideas on how to resolve it?
> Thanks!
> Paul
>|||You are absolutely right, I was being a dumb-XXX
I had the dbname hardcoded in the stored procedures
Thsnks for your time though!
P|||That is the biggest argument to never using the db name in the object
qualifier unless you are actually intending to go cross db in the first
place. You are not the first one to get bit by that and you won't be the
last.
Andrew J. Kelly SQL MVP
"pdm" <paulusm@.gmail.com> wrote in message
news:1165316471.090188.94890@.16g2000cwy.googlegroups.com...
> You are absolutely right, I was being a dumb-XXX
> I had the dbname hardcoded in the stored procedures
> Thsnks for your time though!
> P
>

minimum req files for a desktop to use DTS?

Hey all,
I have an operator who needs to be able to run a dts package, but I do not want to give them Enetrprise Manager. Is it possible to just install the objects needed to run DTS?Give them osql.exe, create a bat that executes the package...

make sure you can connect and execute with something other than sa, because you'll have to hard code the is and pwd...|||You can write a little vb app to execute the package or just schedule it.|||Look at dtsrun.exe or dtsrunui.exe.|||Originally posted by rnealejr
Look at dtsrun.exe or dtsrunui.exe.

No kidding...

Good one for the memory banks...

I avoid DTS for the most part...

Minimum Install MSI for SQL Server 2005

Off of the MSDN SQL Server 2005 Enterprise Edition, what is the MSI I should use to install SQL Server 2005.

I have to Upload all files to a server, and this is going to be painfully slow. Can anyone tell me what the minimum is to upload and install SQL Server Enterprise (not Express)?

(For 32 and 64 bit non-Itanium machines)

That partially depends upon what functionality you intend to install.

MSSQL SSIS AS NS BIDS SSMS Other Client tools|||MSSQL
SSIS
and Reporting Services.
|||

Calling the MSIs directly is not a supported install method. You need to go through setup.exe to install the product.

Thanks,
Sam Lester (MSFT)

|||Does this mean my only choice to get these set up is to upload the entire 985 Meg on each and every server I need to install this on?
|||

Nope, you don't need to copy the files to each machine. You can copy it to a network location and run that install from each machine or you could insert the cd/dvd's into each local machine. The most common approach is probably to copy it to a network share and run the setup.exe from there.

Thanks,
Sam

|||If you have Dedicated Hosted Internet Servers, what are the options?

I don't have physical access to the machine. And I don't have access to an internal Microsoft network to create a Network install.

This is for an Internet Service Provider that is hosting the machine, and our access to it is primarily through Remote Desktop, and FTP.

Can one do a Network Install, and then copy up a smaller file signature to the hosted server?

Otherwise I'm looking at a 800Meg + Upload for EACH machine I need to install this on.
|||** bumpsql

Minimum Install MSI for SQL Server 2005

Off of the MSDN SQL Server 2005 Enterprise Edition, what is the MSI I should use to install SQL Server 2005.

I have to Upload all files to a server, and this is going to be painfully slow. Can anyone tell me what the minimum is to upload and install SQL Server Enterprise (not Express)?

(For 32 and 64 bit non-Itanium machines)

That partially depends upon what functionality you intend to install.

MSSQL SSIS AS NS BIDS SSMS Other Client tools|||MSSQL
SSIS
and Reporting Services.
|||

Calling the MSIs directly is not a supported install method. You need to go through setup.exe to install the product.

Thanks,
Sam Lester (MSFT)

|||Does this mean my only choice to get these set up is to upload the entire 985 Meg on each and every server I need to install this on?
|||

Nope, you don't need to copy the files to each machine. You can copy it to a network location and run that install from each machine or you could insert the cd/dvd's into each local machine. The most common approach is probably to copy it to a network share and run the setup.exe from there.

Thanks,
Sam

|||If you have Dedicated Hosted Internet Servers, what are the options?

I don't have physical access to the machine. And I don't have access to an internal Microsoft network to create a Network install.

This is for an Internet Service Provider that is hosting the machine, and our access to it is primarily through Remote Desktop, and FTP.

Can one do a Network Install, and then copy up a smaller file signature to the hosted server?

Otherwise I'm looking at a 800Meg + Upload for EACH machine I need to install this on.
|||** bump

Migration without backup-files *.dts

Hello All,

I have to

migrate SQL 2000 DTS-Packages to SQL 2005. My Problem is that I didn't saved my

DTS-Packages as files and the SQL 2000 Server exist no longer.

Actually I

have only the old hard disk with the SQL 2000 databases. Do you have any ideas

what can I do?

Thanks

Jonas

Jonas1980 wrote:

Hello All,

I have to migrate SQL 2000 DTS-Packages to SQL 2005. My Problem is that I didn't saved my DTS-Packages as files and the SQL 2000 Server exist no longer.

Actually I have only the old hard disk with the SQL 2000 databases. Do you have any ideas what can I do?

Thanks

Jonas

Do you have backups of the databases?|||Yes, I have!|||

Jonas1980 wrote:

Yes, I have!

You may be able to access the DTS packages by restoring the backups to SQL Server 2005.

Check this link for more details: http://msdn2.microsoft.com/en-us/library/ms143706.aspx

|||

I can

restore the databases, e.g. the msdb-database from the hard disk of my old

server. Do you know any possibility to migrate my dts packages that are saved

in the msdb database without a running SQL Server 2000?

|||If using local server packages you can do this. Get the old msdb online, does not have to be called msdb though, just need to mount the DB somehow. Then rip the contents of the sysdtspackages table into a real msdb DB's sysdtspackages table. You can even use DTS or SSIS to do the data transfer or even just a INSERT ... SELECT if both DBs are on the same server.