No longer able to open up .rptproj

My visual studio .net application previously had not problem opening .rptroj
files, but now it tells me "Make sure the application for the project type
(.rptproj) is installed"
Also, when I try to create a new project, the "Business Intelligence"
project type is not listed. I have seen some suggestions on line about
removing msatinterop.dll from the PrivateAssemblies directory but this does
not change a thing for me.Try running VS from the command line with the /resetskippkgs switch. That
should do the trick. Your command would look something like:
C:\>cd C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE\
C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE>devenv
/resetskipkgs
This is assuming you have RS installed to the default directory. You would
obviously have to find where the devenv.exe file is in order to run the file.
"DBA72" wrote:
> My visual studio .net application previously had not problem opening .rptroj
> files, but now it tells me "Make sure the application for the project type
> (.rptproj) is installed"
> Also, when I try to create a new project, the "Business Intelligence"
> project type is not listed. I have seen some suggestions on line about
> removing msatinterop.dll from the PrivateAssemblies directory but this does
> not change a thing for me.|||Running devenv didn't work for me, but reinstalling MRS with the Report
Designer component checked did. Good Luck.
Bassist695 wrote:
> Try running VS from the command line with the /resetskippkgs switch.
That
> should do the trick. Your command would look something like:
> C:\>cd C:\Program Files\Microsoft Visual Studio .NET
2003\Common7\IDE\
> C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE>devenv
> /resetskipkgs
> This is assuming you have RS installed to the default directory. You
would
> obviously have to find where the devenv.exe file is in order to run
the file.
>
> "DBA72" wrote:
> > My visual studio .net application previously had not problem
opening .rptroj
> > files, but now it tells me "Make sure the application for the
project type
> > (.rptproj) is installed"
> >
> > Also, when I try to create a new project, the "Business
Intelligence"
> > project type is not listed. I have seen some suggestions on line
about
> > removing msatinterop.dll from the PrivateAssemblies directory but
this does
> > not change a thing for me.|||I solved this problem by reinstalling reporting services. I tried
running devenv.exe to no avail. On the default install for RS it
leaves the Report Designer unchecked. When you reinstall (or repair)
make sure this component is checked and it should solve the problem.
Bassist695 wrote:
> Try running VS from the command line with the /resetskippkgs switch.
That
> should do the trick. Your command would look something like:
> C:\>cd C:\Program Files\Microsoft Visual Studio .NET
2003\Common7\IDE\
> C:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE>devenv
> /resetskipkgs
> This is assuming you have RS installed to the default directory. You
would
> obviously have to find where the devenv.exe file is in order to run
the file.
>
> "DBA72" wrote:
> > My visual studio .net application previously had not problem
opening .rptroj
> > files, but now it tells me "Make sure the application for the
project type
> > (.rptproj) is installed"
> >
> > Also, when I try to create a new project, the "Business
Intelligence"
> > project type is not listed. I have seen some suggestions on line
about
> > removing msatinterop.dll from the PrivateAssemblies directory but
this does
> > not change a thing for me.sql

No Import/Export Option in Mgmt Studio Express

Hi,

I just installed SQL Server 2005 Express. I would like to import tables from

another DB, and I read in the help files that I just need to (1) right-click on

a non-system database in Management Studio Express, and (2) select Import/Export

under the Tasks menu.

I tried this, but my Tasks menu only contains these options: Detach, Shrink,

Backup, Restore, Generate Scripts.

I googled for \"sql server 2005 "no import" tasks\", and it appears that a

couple of people have had this problem -- the only solution I read was to

install the "Workstation Components," but I already have, and still no luck.

Any idea what I need to do? Thanks!

Did you try right clicking on the database in the object explorer window. Try that, and then the All Tasks, Generate SQL Scripts option.|||Okay, I right-clicked on my database in the object explorer, then went to Tasks->Generate Scripts. This database is empty, though (I want to import a SQL Server 7 DB into it), so I end up getting a message that says "There are no objects in database of the types scriptable by this wizard."

Ultimately, I just need to import a SQL Server 7 database into SQL Server 2005 Express, but I can't find an "import" option in 2005. Is there some component I need to install?|||Ah, I think I need SQL Server Integration Services (SSIS), and that is apparently not included in SQL Server 2005 Express Edition (see "Unsupported Features" in http://msdn2.microsoft.com/en-us/library/ms365166.aspx). Mystery solved.

No Error thrown by SSIS on missing configuration files

Hello All,

I have a package in which I have enabled "Package Configuration".

When I run the package i am sure that it reads the configuration file and executes the package correctly.

However if I remove the configuration file, the package still executes correctly with the settings which were used at the time of development.

I have event handlers for OnError and OnWarning and both these are NOT invoked.

IMO, this is incorrect behavior because if a package has been configured for "package configuration, then we should atleast have a warning generated that SSIS did not find the configuration and it would execute the package with hard coded values (from the time of development).

Is there any work around for this? how can I make SSIS warn me if the config file is missing for a package which was configured for package configuration?

A missing configuration should trigger a warning and then the package uses the values provides at design time. I always get a the warnings.|||

What event handlers have you written?

I have written event handlers at package level for OnError and OnWarning

In both the event handlers I log the message into the windows event log.

The only messages I see in the windows event log is the "package started" and "package completed".

regards,

Abhishek.

|||I was talking about SSIS logining. I choose OnError and OnWarning.|||

The first thing I would look at is what circumstances are causing the configuration files to be missing at run time, as it seems like this is the root cause of your problem. The behavior you're describing appears to be "by design" to me - this is what I would expect, although I would also expect a warning to be logged if the configuration file were missing.

One possible workaround would be to have a Script task or custom component explicitly check for the existence of the file(s) before the main package logic executes, and only continue if the config files exist.

Another possible workaround would be to ensure that the design time values for the settings that should be set by the package configuration are obviously invalid. (For example, string that is not a valid path, or the like, depending on what you're setting in your configurations.) This would cause the package to fail outright at run time if the configuration could not be set, as opposed to executing with incorrect values and failing silently.

With that said, neither one of these workarounds seems particularly clean or ideal to me. I'd look at the root cause (why are the files missing) before doing anything else.

|||

> The first thing I would look at is what circumstances are causing the configuration files to be missing at run time

The circumstance is that we are testing our operations management strategy. We are deliberately causing things to go wrong in our test environment and we see if MOM (Microsoft Operations Manager) informs the admin correctly of what went wrong.

regards,

Abhishek.

No backup

Hi,
Is there any way of restoring a MSSQL 2000 database if there has been
no backup?
Possibly from the log files?
Thanks.Perhaps sp_detach_db, sp_attach_db?
<gdf1903@.hotmail.com> wrote in message
news:1146055288.324722.292320@.i40g2000cwc.googlegroups.com...
> Hi,
> Is there any way of restoring a MSSQL 2000 database if there has been
> no backup?
> Possibly from the log files?
> Thanks.
>|||What do you want to achieve by such a "restore"? Is the database gone (deleted)? Have you discovered
some physical corruption in the database? Do you wish to go back in time?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<gdf1903@.hotmail.com> wrote in message news:1146055288.324722.292320@.i40g2000cwc.googlegroups.com...
> Hi,
> Is there any way of restoring a MSSQL 2000 database if there has been
> no backup?
> Possibly from the log files?
> Thanks.
>|||I wish to revert back to a previous time. Around 8 hours ago.|||I wish to revert back to a previous time. Around 8 hours ago.|||gdf1903@.hotmail.com wrote:
> I wish to revert back to a previous time. Around 8 hours ago.
>
That will not be possible unless you have a full backup that are more
than 8 hours old to start with.
Regards
Steen|||You are probably out of luck. You could see if any of the log reader tools can extract log records
from the ldf file for you. But the log records might not exist anymore in the ldf file. They will
most probably not exist if you never done a database backup or if the database is in simple recovery
mode (as SQL Server will remove old log records in these cases every time it does a checkpoint).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<gdf1903@.hotmail.com> wrote in message news:1146060959.519582.169180@.u72g2000cwu.googlegroups.com...
>I wish to revert back to a previous time. Around 8 hours ago.
>

no .trn files

I have two sql servers, one is replicating to the other using log shipping.
The backup directory got deleted.
Now replication says it is working, but I don't know how because the .trn
files are not being generated.
What do I have to do to get that started again?
Scott EmickScott,
Repliction is not the same as log shipping. If you are replicating, the
process can continue without transaction logs, but your publisher db will
increase in size if it is not in SIMPLE recovery model and you are not
taking transaction log backups.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Scott Emick" <semick@.ham.phoenix-sys.com> wrote in message
news:OolQXwu$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> I have two sql servers, one is replicating to the other using log
shipping.
> The backup directory got deleted.
> Now replication says it is working, but I don't know how because the .trn
> files are not being generated.
> What do I have to do to get that started again?
> Scott Emick
>|||I did flip all the db's from FULL to simple, and back again, but I
still get no .trn files. I was trying to read about backups and maint
plans yesterday, but I haven't gotten anywhere yet. Yes, the
replication is still working, and I am glad of that, but I would like
to get things back to the way they were.
Scott
"Ron Talmage" <rtalmage@.prospice.com> wrote in message news:<#qyZTf1$DHA.1844@.TK2MSFTNGP11.
phx.gbl>...
> Scott,
> Repliction is not the same as log shipping. If you are replicating, the
> process can continue without transaction logs, but your publisher db will
> increase in size if it is not in SIMPLE recovery model and you are not
> taking transaction log backups.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Scott Emick" <semick@.ham.phoenix-sys.com> wrote in message
> news:OolQXwu$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> shipping.|||Also, restarted sql server agent and the maint plans...Apparently one was
hung and I did not know it...
Scott
"Scott Emick" <semick@.msn.com> wrote in message
news:a2794831.0403020315.1c3ad442@.posting.google.com...
> I did flip all the db's from FULL to simple, and back again, but I
> still get no .trn files. I was trying to read about backups and maint
> plans yesterday, but I haven't gotten anywhere yet. Yes, the
> replication is still working, and I am glad of that, but I would like
> to get things back to the way they were.
> Scott
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:<#qyZTf1$DHA.1844@.TK2MSFTNGP11.phx.gbl>...
will

no .trn files

I have two sql servers, one is replicating to the other using log shipping.
The backup directory got deleted.
Now replication says it is working, but I don't know how because the .trn
files are not being generated.
What do I have to do to get that started again?
Scott EmickScott,
Repliction is not the same as log shipping. If you are replicating, the
process can continue without transaction logs, but your publisher db will
increase in size if it is not in SIMPLE recovery model and you are not
taking transaction log backups.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Scott Emick" <semick@.ham.phoenix-sys.com> wrote in message
news:OolQXwu$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> I have two sql servers, one is replicating to the other using log
shipping.
> The backup directory got deleted.
> Now replication says it is working, but I don't know how because the .trn
> files are not being generated.
> What do I have to do to get that started again?
> Scott Emick
>|||I did flip all the db's from FULL to simple, and back again, but I
still get no .trn files. I was trying to read about backups and maint
plans yesterday, but I haven't gotten anywhere yet. Yes, the
replication is still working, and I am glad of that, but I would like
to get things back to the way they were.
Scott
"Ron Talmage" <rtalmage@.prospice.com> wrote in message news:<#qyZTf1$DHA.1844@.TK2MSFTNGP11.phx.gbl>...
> Scott,
> Repliction is not the same as log shipping. If you are replicating, the
> process can continue without transaction logs, but your publisher db will
> increase in size if it is not in SIMPLE recovery model and you are not
> taking transaction log backups.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Scott Emick" <semick@.ham.phoenix-sys.com> wrote in message
> news:OolQXwu$DHA.1212@.TK2MSFTNGP12.phx.gbl...
> > I have two sql servers, one is replicating to the other using log
> shipping.
> >
> > The backup directory got deleted.
> >
> > Now replication says it is working, but I don't know how because the .trn
> > files are not being generated.
> >
> > What do I have to do to get that started again?
> >
> > Scott Emick
> >
> >|||Also, restarted sql server agent and the maint plans...Apparently one was
hung and I did not know it...
Scott
"Scott Emick" <semick@.msn.com> wrote in message
news:a2794831.0403020315.1c3ad442@.posting.google.com...
> I did flip all the db's from FULL to simple, and back again, but I
> still get no .trn files. I was trying to read about backups and maint
> plans yesterday, but I haven't gotten anywhere yet. Yes, the
> replication is still working, and I am glad of that, but I would like
> to get things back to the way they were.
> Scott
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:<#qyZTf1$DHA.1844@.TK2MSFTNGP11.phx.gbl>...
> > Scott,
> >
> > Repliction is not the same as log shipping. If you are replicating, the
> > process can continue without transaction logs, but your publisher db
will
> > increase in size if it is not in SIMPLE recovery model and you are not
> > taking transaction log backups.

nimdaE + ODBC

i was infected by nimdaE. when i eradicated it, i erased 3 odbc.dll files. from that time, i became unable to use my db connections (access and sqlserver).
what can i do ?
thanks.Download and install the MDAC components from this page:

http://www.microsoft.com/data/download.htm

If you are using Windows XP, then you need MDAC 2.7, otherwise you can use MDAC 2.6 RTM or MDAC 2.6 SP1.

This should restore the missing DLL files and update your drivers.

Nic Card Preferences

WE have three sql servers that talk to each other and pass text
files. All three are connected to the lan and also have GB crossover
cables connecting them to each other. We have certain processes that
run that we want to use a specific connection (crossover) to a
specific sql server, but the server is choosing to use the lan
connection rather than the crossover connection even when it is
specifically pointed to use the crossover connection. Is there a way
to force each sql server to use a specific connection when
communicating or transfering files from one sql server to another?
Any help greatly appreciatedYes. The technique is called a connection-specific domain suffix.
http://technet2.microsoft.com/windowsserver/en/library/9b3661c8-bf60-4b9d-8a34-2dbf6ac0f4551033.mspx?mfr=true
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<eric.c.smith@.gmail.com> wrote in message
news:1178897412.102618.129760@.y80g2000hsf.googlegroups.com...
> WE have three sql servers that talk to each other and pass text
> files. All three are connected to the lan and also have GB crossover
> cables connecting them to each other. We have certain processes that
> run that we want to use a specific connection (crossover) to a
> specific sql server, but the server is choosing to use the lan
> connection rather than the crossover connection even when it is
> specifically pointed to use the crossover connection. Is there a way
> to force each sql server to use a specific connection when
> communicating or transfering files from one sql server to another?
> Any help greatly appreciated
>|||eric.c.smith@.gmail.com wrote in news:1178897412.102618.129760
@.y80g2000hsf.googlegroups.com:
> WE have three sql servers that talk to each other and pass text
> files. All three are connected to the lan and also have GB crossover
> cables connecting them to each other. We have certain processes that
> run that we want to use a specific connection (crossover) to a
> specific sql server, but the server is choosing to use the lan
> connection rather than the crossover connection even when it is
> specifically pointed to use the crossover connection. Is there a way
> to force each sql server to use a specific connection when
> communicating or transfering files from one sql server to another?
> Any help greatly appreciated
>
You will need to configure explicit routes in and for the three servers.
From the Command Prompt, ROUTE PRINT will display the current route table.
ROUTE on its own displays a short help. IMHO it's a bit of an arcane art
...
BTW the route table is applicable to the whole computer - as far as I am
aware, you can't do what you require selectively by process.|||On May 11, 10:37 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Yes. The technique is called a connection-specific domain suffix.http://technet2.microsoft.com/windowsserver/en/library/9b3661c8-bf60-...
> --
> Geoff N. Hiten
> Senior Database Administrator
> MicrosoftSQLServerMVP
> <eric.c.sm...@.gmail.com> wrote in message
> news:1178897412.102618.129760@.y80g2000hsf.googlegroups.com...
>
> > WE have threesqlservers that talk to each other and pass text
> > files. All three are connected to the lan and also have GB crossover
> > cables connecting them to each other. We have certain processes that
> > run that we want tousea specific connection (crossover) to a
> > specificsqlserver, but theserveris choosing tousethe lan
> > connection rather than the crossover connection even when it is
> > specifically pointed tousethe crossover connection. Is there a way
> > toforceeachsqlservertousea specific connection when
> > communicating or transfering files from onesqlserverto another?
> > Any help greatly appreciated- Hide quoted text -
> - Show quoted text -
Geoff,
Thanks for your quick reply. I read the article (about 3 times now)
and will see if that will help. These servers have natted IP
addresses since they are behind a firewall. We use hosts file to
communicate between them. can I still use the solution you referenced
me to in this article and do it using hosts files?|||Hosts files will work just fine. Think of DNS as a service-based central
hosts file.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<eric.c.smith@.gmail.com> wrote in message
news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
> On May 11, 10:37 am, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> Yes. The technique is called a connection-specific domain
>> suffix.http://technet2.microsoft.com/windowsserver/en/library/9b3661c8-bf60-...
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> MicrosoftSQLServerMVP
>> <eric.c.sm...@.gmail.com> wrote in message
>> news:1178897412.102618.129760@.y80g2000hsf.googlegroups.com...
>>
>> > WE have threesqlservers that talk to each other and pass text
>> > files. All three are connected to the lan and also have GB crossover
>> > cables connecting them to each other. We have certain processes that
>> > run that we want tousea specific connection (crossover) to a
>> > specificsqlserver, but theserveris choosing tousethe lan
>> > connection rather than the crossover connection even when it is
>> > specifically pointed tousethe crossover connection. Is there a way
>> > toforceeachsqlservertousea specific connection when
>> > communicating or transfering files from onesqlserverto another?
>> > Any help greatly appreciated- Hide quoted text -
>> - Show quoted text -
> Geoff,
> Thanks for your quick reply. I read the article (about 3 times now)
> and will see if that will help. These servers have natted IP
> addresses since they are behind a firewall. We use hosts file to
> communicate between them. can I still use the solution you referenced
> me to in this article and do it using hosts files?
>|||<eric.c.smith@.gmail.com> wrote in message
news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
> Geoff,
> Thanks for your quick reply. I read the article (about 3 times now)
> and will see if that will help. These servers have natted IP
> addresses since they are behind a firewall. We use hosts file to
> communicate between them. can I still use the solution you referenced
> me to in this article and do it using hosts files?
Yes.
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||On May 11, 11:34 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <eric.c.sm...@.gmail.com> wrote in message
> news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
> > Geoff,
> > Thanks for your quick reply. I read the article (about 3 times now)
> > and will see if that will help. These servers have natted IP
> > addresses since they are behind a firewall. We use hosts file to
> > communicate between them. can I still use the solution you referenced
> > me to in this article and do it using hosts files?
> Yes.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Thank you all for your replies
If I wanted to use a host file for this would how would this work?
The first three in each hosts file is for LAN connectivity, the
additional ones are for private crossover connections between servers
current srvx2 hosts file
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
current srvx3 hosts file
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
current srvx4 hosts file
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
servername connection to network Private lan connection#1 Private Lan
Connection #2
srvx2 170.20.50.11 (lan) 2.1.1.2 (connection to x3) 1.1.1.1
(connection to x4)
srvx3 170.20.50.12 (lan) 2.1.1.3 (connection to x2) 3.1.1.3
(connection to x4)
srvx4 170.20.50.13 (lan) 1.1.1.3 (connection to x2) 3.1.1.4
(connection to x3)
I am not sure what to add to each host file and how it will
differentiate between them. These private IP's are not registered in
a DNS zone. The servers use the hosts file like the examples I have
above.
new srvx2 hosts file
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
2.1.1.2 srvx2
1.1.1.1 srvx2
new srvx3 hosts file
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
2.1.1.3 srvx3
3.1.1.3 srvx3
new srvx4 hosts file
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
1.1.1.3 srvx4
3.1.1.4 srvx4
I am not sure how to differentiate between the ip addresses, once
again appreciate all your help!|||On May 11, 1:07 pm, eric.c.sm...@.gmail.com wrote:
> On May 11, 11:34 am, "Greg D. Moore \(Strider\)"
>
>
> <mooregr_deletet...@.greenms.com> wrote:
> > <eric.c.sm...@.gmail.com> wrote in message
> >news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
> > > Geoff,
> > > Thanks for your quick reply. I read the article (about 3 times now)
> > > and will see if that will help. These servers have natted IP
> > > addresses since they are behind a firewall. We use hosts file to
> > > communicate between them. can I still use the solution you referenced
> > > me to in this article and do it using hosts files?
> > Yes.
> > --
> > Greg Moore
> > SQL Server DBA Consulting Remote and Onsite available!
> > Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
> Thank you all for your replies
> If I wanted to use a host file for this would how would this work?
> The first three in each hosts file is for LAN connectivity, the
> additional ones are for private crossover connections between servers
> current srvx2 hosts file
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> current srvx3 hosts file
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> current srvx4 hosts file
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> servername connection to network Private lan connection#1 Private Lan
> Connection #2
> srvx2 170.20.50.11 (lan) 2.1.1.2 (connection to x3) 1.1.1.1
> (connection to x4)
> srvx3 170.20.50.12 (lan) 2.1.1.3 (connection to x2) 3.1.1.3
> (connection to x4)
> srvx4 170.20.50.13 (lan) 1.1.1.3 (connection to x2) 3.1.1.4
> (connection to x3)
> I am not sure what to add to each host file and how it will
> differentiate between them. These private IP's are not registered in
> a DNS zone. The servers use the hosts file like the examples I have
> above.
> new srvx2 hosts file
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> 2.1.1.2 srvx2
> 1.1.1.1 srvx2
> new srvx3 hosts file
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> 2.1.1.3 srvx3
> 3.1.1.3 srvx3
> new srvx4 hosts file
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> 1.1.1.3 srvx4
> 3.1.1.4 srvx4
> I am not sure how to differentiate between the ip addresses, once
> again appreciate all your help!- Hide quoted text -
> - Show quoted text -
After researching a bit on "friendly names for hosts files this is
what I came up with...Does this look like it will accomplish what I am
trying to do?
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
2.1.1.2 srvx2b
1.1.1.1 srvx2c
2.1.1.3 srvx3b
3.1.1.3 srvx3c
1.1.1.3 srvx4b
3.1.1.4 srvx4c
Thanks all!|||I usually go with this:
170.20.50.11 srvx2
170.20.50.12 srvx3
170.20.50.13 srvx4
2.1.1.2 srvx2.Priv1
1.1.1.1 srvx2.priv2
2.1.1.3 srvx3.priv1
3.1.1.3 srvx3.priv2
1.1.1.3 srvx4.priv1
3.1.1.4 srvx4.priv2
Where each subnet has a connection-specific DNS suffix. You are trying to
alias a servername to a connection, which can have some authentication
issues.
For example, I want to use the Priv1 network to backup from srvx2 to srvx3.
I would reference servx3 as servx3.priv1, and it would work. I still have
the same network name, and all my authentication stuff works, but I am
forcing traffic across a specific subnet.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<eric.c.smith@.gmail.com> wrote in message
news:1178912150.700412.190180@.p77g2000hsh.googlegroups.com...
> On May 11, 1:07 pm, eric.c.sm...@.gmail.com wrote:
>> On May 11, 11:34 am, "Greg D. Moore \(Strider\)"
>>
>>
>> <mooregr_deletet...@.greenms.com> wrote:
>> > <eric.c.sm...@.gmail.com> wrote in message
>> >news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
>> > > Geoff,
>> > > Thanks for your quick reply. I read the article (about 3 times now)
>> > > and will see if that will help. These servers have natted IP
>> > > addresses since they are behind a firewall. We use hosts file to
>> > > communicate between them. can I still use the solution you
>> > > referenced
>> > > me to in this article and do it using hosts files?
>> > Yes.
>> > --
>> > Greg Moore
>> > SQL Server DBA Consulting Remote and Onsite available!
>> > Email: sql (at) greenms.com
>> > http://www.greenms.com/sqlserver.html
>> Thank you all for your replies
>> If I wanted to use a host file for this would how would this work?
>> The first three in each hosts file is for LAN connectivity, the
>> additional ones are for private crossover connections between servers
>> current srvx2 hosts file
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> current srvx3 hosts file
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> current srvx4 hosts file
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> servername connection to network Private lan connection#1
>> Private Lan
>> Connection #2
>> srvx2 170.20.50.11 (lan) 2.1.1.2 (connection to x3)
>> 1.1.1.1
>> (connection to x4)
>> srvx3 170.20.50.12 (lan) 2.1.1.3 (connection to x2)
>> 3.1.1.3
>> (connection to x4)
>> srvx4 170.20.50.13 (lan) 1.1.1.3 (connection to x2)
>> 3.1.1.4
>> (connection to x3)
>> I am not sure what to add to each host file and how it will
>> differentiate between them. These private IP's are not registered in
>> a DNS zone. The servers use the hosts file like the examples I have
>> above.
>> new srvx2 hosts file
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> 2.1.1.2 srvx2
>> 1.1.1.1 srvx2
>> new srvx3 hosts file
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> 2.1.1.3 srvx3
>> 3.1.1.3 srvx3
>> new srvx4 hosts file
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> 1.1.1.3 srvx4
>> 3.1.1.4 srvx4
>> I am not sure how to differentiate between the ip addresses, once
>> again appreciate all your help!- Hide quoted text -
>> - Show quoted text -
> After researching a bit on "friendly names for hosts files this is
> what I came up with...Does this look like it will accomplish what I am
> trying to do?
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> 2.1.1.2 srvx2b
> 1.1.1.1 srvx2c
> 2.1.1.3 srvx3b
> 3.1.1.3 srvx3c
> 1.1.1.3 srvx4b
> 3.1.1.4 srvx4c
> Thanks all!
>|||On May 11, 2:46 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> I usually go with this:
> 170.20.50.11 srvx2
> 170.20.50.12 srvx3
> 170.20.50.13 srvx4
> 2.1.1.2 srvx2.Priv1
> 1.1.1.1 srvx2.priv2
> 2.1.1.3 srvx3.priv1
> 3.1.1.3 srvx3.priv2
> 1.1.1.3 srvx4.priv1
> 3.1.1.4 srvx4.priv2
> Where each subnet has a connection-specific DNS suffix. You are trying to
> alias a servername to a connection, which can have some authentication
> issues.
> For example, I want to use the Priv1 network to backup from srvx2 to srvx3.
> I would reference servx3 as servx3.priv1, and it would work. I still have
> the same network name, and all my authentication stuff works, but I am
> forcing traffic across a specific subnet.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> <eric.c.sm...@.gmail.com> wrote in message
> news:1178912150.700412.190180@.p77g2000hsh.googlegroups.com...
>
> > On May 11, 1:07 pm, eric.c.sm...@.gmail.com wrote:
> >> On May 11, 11:34 am, "Greg D. Moore \(Strider\)"
> >> <mooregr_deletet...@.greenms.com> wrote:
> >> > <eric.c.sm...@.gmail.com> wrote in message
> >> >news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
> >> > > Geoff,
> >> > > Thanks for your quick reply. I read the article (about 3 times now)
> >> > > and will see if that will help. These servers have natted IP
> >> > > addresses since they are behind a firewall. We use hosts file to
> >> > > communicate between them. can I still use the solution you
> >> > > referenced
> >> > > me to in this article and do it using hosts files?
> >> > Yes.
> >> > --
> >> > Greg Moore
> >> > SQL Server DBA Consulting Remote and Onsite available!
> >> > Email: sql (at) greenms.com
> >> >http://www.greenms.com/sqlserver.html
> >> Thank you all for your replies
> >> If I wanted to use a host file for this would how would this work?
> >> The first three in each hosts file is for LAN connectivity, the
> >> additional ones are for private crossover connections between servers
> >> current srvx2 hosts file
> >> 170.20.50.11 srvx2
> >> 170.20.50.12 srvx3
> >> 170.20.50.13 srvx4
> >> current srvx3 hosts file
> >> 170.20.50.11 srvx2
> >> 170.20.50.12 srvx3
> >> 170.20.50.13 srvx4
> >> current srvx4 hosts file
> >> 170.20.50.11 srvx2
> >> 170.20.50.12 srvx3
> >> 170.20.50.13 srvx4
> >> servername connection to network Private lan connection#1
> >> Private Lan
> >> Connection #2
> >> srvx2 170.20.50.11 (lan) 2.1.1.2 (connection to x3)
> >> 1.1.1.1
> >> (connection to x4)
> >> srvx3 170.20.50.12 (lan) 2.1.1.3 (connection to x2)
> >> 3.1.1.3
> >> (connection to x4)
> >> srvx4 170.20.50.13 (lan) 1.1.1.3 (connection to x2)
> >> 3.1.1.4
> >> (connection to x3)
> >> I am not sure what to add to each host file and how it will
> >> differentiate between them. These private IP's are not registered in
> >> a DNS zone. The servers use the hosts file like the examples I have
> >> above.
> >> new srvx2 hosts file
> >> 170.20.50.11 srvx2
> >> 170.20.50.12 srvx3
> >> 170.20.50.13 srvx4
> >> 2.1.1.2 srvx2
> >> 1.1.1.1 srvx2
> >> new srvx3 hosts file
> >> 170.20.50.11 srvx2
> >> 170.20.50.12 srvx3
> >> 170.20.50.13 srvx4
> >> 2.1.1.3 srvx3
> >> 3.1.1.3 srvx3
> >> new srvx4 hosts file
> >> 170.20.50.11 srvx2
> >> 170.20.50.12 srvx3
> >> 170.20.50.13 srvx4
> >> 1.1.1.3 srvx4
> >> 3.1.1.4 srvx4
> >> I am not sure how to differentiate between the ip addresses, once
> >> again appreciate all your help!- Hide quoted text -
> >> - Show quoted text -
> > After researching a bit on "friendly names for hosts files this is
> > what I came up with...Does this look like it will accomplish what I am
> > trying to do?
> > 170.20.50.11 srvx2
> > 170.20.50.12 srvx3
> > 170.20.50.13 srvx4
> > 2.1.1.2 srvx2b
> > 1.1.1.1 srvx2c
> > 2.1.1.3 srvx3b
> > 3.1.1.3 srvx3c
> > 1.1.1.3 srvx4b
> > 3.1.1.4 srvx4c
> > Thanks all!- Hide quoted text -
> - Show quoted text -
Looks like this worked like a champ, there was one thing to consider
from this however that our developer had to tweak to make this work
proplerly...
"One caveat, SQL Server has a limit on extensions that you can use to
reference external tables from a query to a linked server:
srvx3.wire.dbo.batches (maxed)
So, when you add a dot to the host file reference it breaks the remote
reference possibility because there are to many extensions:
srvx3.priv1.wire.dbo.batches (one too many)
All I did was change the host name(s) to SRVX3P1
THANKS AGAIN ALL FOR YOUR HELP!!!|||Use [Servername.SuffixName].Database.Schema.Object and it will resolve
correctly within SQL.
Square brackets are your friend.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<eric.c.smith@.gmail.com> wrote in message
news:1179435475.288996.61950@.q23g2000hsg.googlegroups.com...
> On May 11, 2:46 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> I usually go with this:
>> 170.20.50.11 srvx2
>> 170.20.50.12 srvx3
>> 170.20.50.13 srvx4
>> 2.1.1.2 srvx2.Priv1
>> 1.1.1.1 srvx2.priv2
>> 2.1.1.3 srvx3.priv1
>> 3.1.1.3 srvx3.priv2
>> 1.1.1.3 srvx4.priv1
>> 3.1.1.4 srvx4.priv2
>> Where each subnet has a connection-specific DNS suffix. You are trying
>> to
>> alias a servername to a connection, which can have some authentication
>> issues.
>> For example, I want to use the Priv1 network to backup from srvx2 to
>> srvx3.
>> I would reference servx3 as servx3.priv1, and it would work. I still
>> have
>> the same network name, and all my authentication stuff works, but I am
>> forcing traffic across a specific subnet.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> <eric.c.sm...@.gmail.com> wrote in message
>> news:1178912150.700412.190180@.p77g2000hsh.googlegroups.com...
>>
>> > On May 11, 1:07 pm, eric.c.sm...@.gmail.com wrote:
>> >> On May 11, 11:34 am, "Greg D. Moore \(Strider\)"
>> >> <mooregr_deletet...@.greenms.com> wrote:
>> >> > <eric.c.sm...@.gmail.com> wrote in message
>> >> >news:1178900235.067952.185940@.y5g2000hsa.googlegroups.com...
>> >> > > Geoff,
>> >> > > Thanks for your quick reply. I read the article (about 3 times
>> >> > > now)
>> >> > > and will see if that will help. These servers have natted IP
>> >> > > addresses since they are behind a firewall. We use hosts file to
>> >> > > communicate between them. can I still use the solution you
>> >> > > referenced
>> >> > > me to in this article and do it using hosts files?
>> >> > Yes.
>> >> > --
>> >> > Greg Moore
>> >> > SQL Server DBA Consulting Remote and Onsite available!
>> >> > Email: sql (at) greenms.com
>> >> >http://www.greenms.com/sqlserver.html
>> >> Thank you all for your replies
>> >> If I wanted to use a host file for this would how would this work?
>> >> The first three in each hosts file is for LAN connectivity, the
>> >> additional ones are for private crossover connections between servers
>> >> current srvx2 hosts file
>> >> 170.20.50.11 srvx2
>> >> 170.20.50.12 srvx3
>> >> 170.20.50.13 srvx4
>> >> current srvx3 hosts file
>> >> 170.20.50.11 srvx2
>> >> 170.20.50.12 srvx3
>> >> 170.20.50.13 srvx4
>> >> current srvx4 hosts file
>> >> 170.20.50.11 srvx2
>> >> 170.20.50.12 srvx3
>> >> 170.20.50.13 srvx4
>> >> servername connection to network Private lan connection#1
>> >> Private Lan
>> >> Connection #2
>> >> srvx2 170.20.50.11 (lan) 2.1.1.2 (connection to x3)
>> >> 1.1.1.1
>> >> (connection to x4)
>> >> srvx3 170.20.50.12 (lan) 2.1.1.3 (connection to x2)
>> >> 3.1.1.3
>> >> (connection to x4)
>> >> srvx4 170.20.50.13 (lan) 1.1.1.3 (connection to x2)
>> >> 3.1.1.4
>> >> (connection to x3)
>> >> I am not sure what to add to each host file and how it will
>> >> differentiate between them. These private IP's are not registered in
>> >> a DNS zone. The servers use the hosts file like the examples I have
>> >> above.
>> >> new srvx2 hosts file
>> >> 170.20.50.11 srvx2
>> >> 170.20.50.12 srvx3
>> >> 170.20.50.13 srvx4
>> >> 2.1.1.2 srvx2
>> >> 1.1.1.1 srvx2
>> >> new srvx3 hosts file
>> >> 170.20.50.11 srvx2
>> >> 170.20.50.12 srvx3
>> >> 170.20.50.13 srvx4
>> >> 2.1.1.3 srvx3
>> >> 3.1.1.3 srvx3
>> >> new srvx4 hosts file
>> >> 170.20.50.11 srvx2
>> >> 170.20.50.12 srvx3
>> >> 170.20.50.13 srvx4
>> >> 1.1.1.3 srvx4
>> >> 3.1.1.4 srvx4
>> >> I am not sure how to differentiate between the ip addresses, once
>> >> again appreciate all your help!- Hide quoted text -
>> >> - Show quoted text -
>> > After researching a bit on "friendly names for hosts files this is
>> > what I came up with...Does this look like it will accomplish what I am
>> > trying to do?
>> > 170.20.50.11 srvx2
>> > 170.20.50.12 srvx3
>> > 170.20.50.13 srvx4
>> > 2.1.1.2 srvx2b
>> > 1.1.1.1 srvx2c
>> > 2.1.1.3 srvx3b
>> > 3.1.1.3 srvx3c
>> > 1.1.1.3 srvx4b
>> > 3.1.1.4 srvx4c
>> > Thanks all!- Hide quoted text -
>> - Show quoted text -
> Looks like this worked like a champ, there was one thing to consider
> from this however that our developer had to tweak to make this work
> proplerly...
> "One caveat, SQL Server has a limit on extensions that you can use to
> reference external tables from a query to a linked server:
> srvx3.wire.dbo.batches (maxed)
> So, when you add a dot to the host file reference it breaks the remote
> reference possibility because there are to many extensions:
> srvx3.priv1.wire.dbo.batches (one too many)
> All I did was change the host name(s) to SRVX3P1
> THANKS AGAIN ALL FOR YOUR HELP!!!
>

Newsgroup for attachments?

Is there a newsgroup where we can post file attachements (for code examples,
rdl files, etc.)?
Thank you,
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comOn Nov 5, 10:16 pm, "Alain Quesnel" <alainsanss...@.logiquel.com>
wrote:
> Is there a newsgroup where we can post file attachements (for code examples,
> rdl files, etc.)?
> Thank you,
> --
> Alain Quesnel
> alainsanss...@.logiquel.com
> www.logiquel.com
This is by far the largest, highest traffic Reporting Services
newsgroup (and has the most responses and participation); however, 3
others have the option to upload files (and I'm sure there are others
as well):
http://groups.google.com/group/SQL-SERVER-REPORTING-SERVICES
http://groups.google.com/group/RS2005
http://groups.google.com/group/ReportingServices
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I take it Microsoft doesn't have any newsgroups that accept attachments?
Thank you,
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1194323747.519601.245000@.57g2000hsv.googlegroups.com...
> On Nov 5, 10:16 pm, "Alain Quesnel" <alainsanss...@.logiquel.com>
> wrote:
>> Is there a newsgroup where we can post file attachements (for code
>> examples,
>> rdl files, etc.)?
>> Thank you,
>> --
>> Alain Quesnel
>> alainsanss...@.logiquel.com
>> www.logiquel.com
>
> This is by far the largest, highest traffic Reporting Services
> newsgroup (and has the most responses and participation); however, 3
> others have the option to upload files (and I'm sure there are others
> as well):
> http://groups.google.com/group/SQL-SERVER-REPORTING-SERVICES
> http://groups.google.com/group/RS2005
> http://groups.google.com/group/ReportingServices
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Newbie-shrink transaction log files

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goals
in backing up to disk are 1) to have a couple days of backups on disk in case
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.
I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:
USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO
I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran log
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?
By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?
Thanks for your help.
Hi Coop,
I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.
Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Coop" wrote:

> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to back
> up my databases to disk (we also backup to tape using Backup Exec). My goals
> in backing up to disk are 1) to have a couple days of backups on disk in case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above script
> every time I create or drop a database. I know I can just have a SQL Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.
|||Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@.microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.

Newbie-shrink transaction log files

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goal
s
in backing up to disk are 1) to have a couple days of backups on disk in cas
e
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.
I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:
USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO
I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran lo
g
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?
By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?
Thanks for your help.Hi Coop,
I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.
Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Coop" wrote:

> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to bac
k
> up my databases to disk (we also backup to tape using Backup Exec). My go
als
> in backing up to disk are 1) to have a couple days of backups on disk in c
ase
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transacti
on
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above scri
pt
> every time I create or drop a database. I know I can just have a SQL Agen
t
> job run the script, BUT isn't the maintenance job supposed to shrink tran
log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.|||Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@.microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.

Newbie-shrink transaction log files

Hi. I know this is an often-discussed topic, but I hope you can clear up
some confusion for me. I set up a SQL Server 2005 maintenance plan to back
up my databases to disk (we also backup to tape using Backup Exec). My goals
in backing up to disk are 1) to have a couple days of backups on disk in case
I need to do quick restores, and 2) to reduce the size of transaction log
files. In the maint plan, I selected the options to shrink the databases,
backup databases (full), and backup transaction logs. The problem is that
when I executed the maint plan, it didn't reduce the size of the transaction
log physical files which are growing.
I grabbed this SQL code from the Internet, and it works to shrink the tran
log file size:
USE mydb
BACKUP LOG mydb WITH TRUNCATEONLY
DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
GO
I want to automate this task. I don't want to have to edit the above script
every time I create or drop a database. I know I can just have a SQL Agent
job run the script, BUT isn't the maintenance job supposed to shrink tran log
files as part of the "Backup Database (Full)" and Backup Database
"(Transaction Log)" options?
By the way, is the BACKUP LOG statement required to precede the DBCC
SHRINKFILE statement?
Thanks for your help.Hi Coop,
I see a couple of problems with your request. First, do not shrink your
database and transaction log files periodically. When you shrink a file, the
next thing SQL Server will do is to expand these files to do its work, and
shrinking and expanding these files periodically could be a performance
problem in addition to creating more disk fragmentation that probably will
also affect the performance of your database. My recommendation here is just
schedule full database and transaction log backups. You can still shrink the
files manually once in a while only if you need it.
Second, you want to use TRUNCATE_ONLY which truncates the log without really
doing a backup. The problem here is that you are not doing a transaction log
backup and you are breaking the log chain. I recommend here that you see the
SQL Server documentation (BOL) and understand the SQL Server recovery models
simple, bulk-logged and full. If you really need to use TRUNCATE_ONLY better
change the recovery model to simple.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Coop" wrote:
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to back
> up my databases to disk (we also backup to tape using Backup Exec). My goals
> in backing up to disk are 1) to have a couple days of backups on disk in case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above script
> every time I create or drop a database. I know I can just have a SQL Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.|||Coop
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Coop" <Coop@.discussions.microsoft.com> wrote in message
news:91C8FA70-23CF-4D9A-8879-93EC8EDAEF71@.microsoft.com...
> Hi. I know this is an often-discussed topic, but I hope you can clear up
> some confusion for me. I set up a SQL Server 2005 maintenance plan to
> back
> up my databases to disk (we also backup to tape using Backup Exec). My
> goals
> in backing up to disk are 1) to have a couple days of backups on disk in
> case
> I need to do quick restores, and 2) to reduce the size of transaction log
> files. In the maint plan, I selected the options to shrink the databases,
> backup databases (full), and backup transaction logs. The problem is that
> when I executed the maint plan, it didn't reduce the size of the
> transaction
> log physical files which are growing.
> I grabbed this SQL code from the Internet, and it works to shrink the tran
> log file size:
> USE mydb
> BACKUP LOG mydb WITH TRUNCATEONLY
> DBCC SHRINKFILE [mydb_log, TRUNCATEONLY]
> GO
> I want to automate this task. I don't want to have to edit the above
> script
> every time I create or drop a database. I know I can just have a SQL
> Agent
> job run the script, BUT isn't the maintenance job supposed to shrink tran
> log
> files as part of the "Backup Database (Full)" and Backup Database
> "(Transaction Log)" options?
> By the way, is the BACKUP LOG statement required to precede the DBCC
> SHRINKFILE statement?
> Thanks for your help.