Locks and Blocks: March 2012

Friday, March 30, 2012

Log parser

Can someone send me the command to import the application and system log of
the eventlogs into SQL Server for say 2 different servers ?
Does the command have to run every few mins ? How does it handle
duplicates,etc. ? I would like to have the logs in the database no later
than say 15 mins from the time they make it in the respective log. But I
dont know how log parser is smart enough to do that unless I run it every 5
mins but then again, what does it scan for and only ensures it does not
insert duplicates
Thanks
You have to write something custom to do this. You can do it with an SSIS
WMI data reader task. The class to look at is win32_ntlogevent.
Other options include powershell to from xp_cmdshell or a CLR function to
get to WMI.
Jason Massie
http://statisticsio.com
"Hassan" <hassan@.test.com> wrote in message
news:ut0pOZvKIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Can someone send me the command to import the application and system log
> of the eventlogs into SQL Server for say 2 different servers ?
> Does the command have to run every few mins ? How does it handle
> duplicates,etc. ? I would like to have the logs in the database no later
> than say 15 mins from the time they make it in the respective log. But I
> dont know how log parser is smart enough to do that unless I run it every
> 5 mins but then again, what does it scan for and only ensures it does not
> insert duplicates
> Thanks
>

Log on server error

Hi all i am using crystal report with asp .net.

i am using report files already created an configured with a dsn.

when i try to view the report using crystal report viewer in a web form i am getting the error

"CrystalDecisions.CrystalReports.Engine.LogOnException: Logon failed."

i am using

<code>
ConnectionInfo cnnInfo = new ConnectionInfo ( ) ;

cnnInfo.ServerName = strConnInformation[0] ;
cnnInfo.DatabaseName = strConnInformation[1] ;
cnnInfo.UserID = strConnInformation[2] ;
cnnInfo.Password = strConnInformation[3] ;

ReportDocument rptDoc = new ReportDocument ( ) ;
rptDoc.Load ( Server.MapPath ( "..//Reports//" + reportFileName ),
OpenReportMethod.OpenReportByDefault ) ;

//CrystalDecisions.CrystalReports.Engine.Table table = rptDoc.Database.Tables[0] ;

CrystalDecisions.CrystalReports.Engine.Tables rptTables = rptDoc.Database.Tables ;
foreach ( CrystalDecisions.CrystalReports.Engine.Table tbl in rptTables )
{
tblLogOnInfo = tbl.LogOnInfo ;
tblLogOnInfo.ConnectionInfo = cnnInfo ;
tbl.ApplyLogOnInfo ( tblLogOnInfo ) ;

if ( tbl.Location.IndexOf ( "." ) > 0 )
{
tbl.Location = tbl.Location.Substring (
tbl.Location.LastIndexOf(".") + 1 ) ;
}
else
{
//tbl.Location = tbl.Location ;
}
}

</code>

its working fine when i try to execute a report file which configured direct to a database in another machine by specifying the server name, database name, user id and the password,

its showing the error only when i try to execute the report which configured using dsn. dsn is created in my machine and the actual database server is another one.

can any one help me in this matter as i have to use the reports with dsn only in my application.

i got the informations from the net that its same for both type of connectivity to use the ConnectionInfo object and the TableLogonInfo objects.

what my be the problem with me.

is its related to any permision issues.Hai Vinod,

Even i faced the same problem with my C# and Access .
But this prblem arised only when i have a subreport in my main report.

Do u have any subreports in ur appli...If so u have to provide login information to those reports also.

regards,
Kolluru.|||Thanks for your reply kolluru. i will try this and will get back :)|||Do u have any subreports in ur appli...If so u have to provide login information to those reports also.

Im having the same problem and I have subreports. How do you provide the login details for the subreports?sql

Log On problems...

I've installed MSDE w/no problem - but what username & password do I use (well, I know what password since I specified it in the *.ini file before install) - but username?
Cassinni or IIS web server - I can't log on and do anything w/MSDE. Do I have to edit something w/a username, make one up, use one from that's built into windows? I've tried both settings _ windows authentication and SQL Server - but nothing. Any sugge
stions?
The default user ID is "sa". If you set the server to use Windows Authentication, you cannot logon with this user ID - at least via applications. This ID must be given a strong password. You can do that with the osql utility provided with MSDE.
If this is MSDE2000A, read the Jan 2004 version of the Readme2000A.htm that comes with the bundle. Also get a hold of the updates to this file.
I also suggest the SQL Server Books Online help file. This is downloadable at no charge and must be installed.
Mark Ferguson
"steve02a" wrote:

> I've installed MSDE w/no problem - but what username & password do I use (well, I know what password since I specified it in the *.ini file before install) - but username?
> Cassinni or IIS web server - I can't log on and do anything w/MSDE. Do I have to edit something w/a username, make one up, use one from that's built into windows? I've tried both settings _ windows authentication and SQL Server - but nothing. Any sug
gestions?

Log on Problem.

OK. I have a little problem that i need help diagnosing. I have a Webpage
for internal program that connects back to SQL Server. When I rebooted the
server last night, in the morning users complainted that they can't log on
into it. As soon as I walked up to the server and simply logged in,
everything started to work. My question is why. All my services are set to
system accounts and to Automatic starts so everything should be started when
the server starts. How can I diagnose this?
Thank You!These are possibly programs that are in your Startup routine after you
login.
If you're running Windows 2003 click the Start button > Run > Msconfig.exe.
Check the Startup tab for programs that are loading at startup from the
Startup group, Win.ini load= and run=, and the registry.
For Windows 2000, use regedit & check the following entries:
HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows \CurrentVersion \Run
HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows \CurrentVersion \RunOnce
HKEY_CURRENT_USER \Software \Microsoft \Windows \CurrentVersion \Run
HKEY_CURRENT_USER \Software \Microsoft \Windows \CurrentVersion \Runonce
"Kory" <koryg@.community.nospam> wrote in message
news:uIxvwLd8FHA.3416@.TK2MSFTNGP15.phx.gbl...
> OK. I have a little problem that i need help diagnosing. I have a
Webpage
> for internal program that connects back to SQL Server. When I rebooted
the
> server last night, in the morning users complainted that they can't log on
> into it. As soon as I walked up to the server and simply logged in,
> everything started to work. My question is why. All my services are set
to
> system accounts and to Automatic starts so everything should be started
when
> the server starts. How can I diagnose this?
> Thank You!
>|||I'm running windows 2003. So basically I should remove these files from
Startup and move them to WIN.INI'
"Isaac Steinfeld" <isteinfeld@.hotmail.com> wrote in message
news:%23tM6PYd8FHA.1292@.tk2msftngp13.phx.gbl...
> These are possibly programs that are in your Startup routine after you
> login.
> If you're running Windows 2003 click the Start button > Run >
> Msconfig.exe.
> Check the Startup tab for programs that are loading at startup from the
> Startup group, Win.ini load= and run=, and the registry.
> For Windows 2000, use regedit & check the following entries:
> HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows \CurrentVersion \Run
> HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows \CurrentVersion \RunOnce
> HKEY_CURRENT_USER \Software \Microsoft \Windows \CurrentVersion \Run
> HKEY_CURRENT_USER \Software \Microsoft \Windows \CurrentVersion \Runonce
> "Kory" <koryg@.community.nospam> wrote in message
> news:uIxvwLd8FHA.3416@.TK2MSFTNGP15.phx.gbl...
>> OK. I have a little problem that i need help diagnosing. I have a
> Webpage
>> for internal program that connects back to SQL Server. When I rebooted
> the
>> server last night, in the morning users complainted that they can't log
>> on
>> into it. As soon as I walked up to the server and simply logged in,
>> everything started to work. My question is why. All my services are set
> to
>> system accounts and to Automatic starts so everything should be started
> when
>> the server starts. How can I diagnose this?
>> Thank You!
>>
>|||No.
You have a few options to have programs run without requiring a login.
One is to have these programs run as User-defined service.
See the following link for instructions:
http://support.microsoft.com/support/kb/articles/q137/8/90.asp
Another is to have it run as a Computer Script. (Either as a local policy or
as a group policy depending on your environment)
Run gpedit.msc and create a startup scripts at Computer Configuration
>Windows Settings>Scripts>Startup.
See the following link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;322241
Another option might be to do an Autologon. See
http://www.petri.co.il/configuring_autologon.htm
"Kory" <koryg@.community.nospam> wrote in message
news:%23s5Qahd8FHA.444@.TK2MSFTNGP11.phx.gbl...
> I'm running windows 2003. So basically I should remove these files from
> Startup and move them to WIN.INI'
>
> "Isaac Steinfeld" <isteinfeld@.hotmail.com> wrote in message
> news:%23tM6PYd8FHA.1292@.tk2msftngp13.phx.gbl...
> > These are possibly programs that are in your Startup routine after you
> > login.
> > If you're running Windows 2003 click the Start button > Run >
> > Msconfig.exe.
> > Check the Startup tab for programs that are loading at startup from the
> > Startup group, Win.ini load= and run=, and the registry.
> > For Windows 2000, use regedit & check the following entries:
> > HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows \CurrentVersion \Run
> > HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \Windows \CurrentVersion
\RunOnce
> > HKEY_CURRENT_USER \Software \Microsoft \Windows \CurrentVersion \Run
> > HKEY_CURRENT_USER \Software \Microsoft \Windows \CurrentVersion \Runonce
> >
> > "Kory" <koryg@.community.nospam> wrote in message
> > news:uIxvwLd8FHA.3416@.TK2MSFTNGP15.phx.gbl...
> >> OK. I have a little problem that i need help diagnosing. I have a
> > Webpage
> >> for internal program that connects back to SQL Server. When I
rebooted
> > the
> >> server last night, in the morning users complainted that they can't log
> >> on
> >> into it. As soon as I walked up to the server and simply logged in,
> >> everything started to work. My question is why. All my services are
set
> > to
> >> system accounts and to Automatic starts so everything should be started
> > when
> >> the server starts. How can I diagnose this?
> >>
> >> Thank You!
> >>
> >>
> >
> >
>

Log on Problem.

Log on problem

Why I can't make change account from Log on tab in properties MSSQLSERVER
service?
Regardless of account, I gett this error: "Could not start the MSSQLSERVER
service on Local Computer.
Error 1096: The service did not start to a logon failure."
SQL Server does work only with Local system account?!
Thanks
(MS SQLserver 2000 , WinXP SP2)You should never change the account directly thru the service. You need to
use Enterprise Manager instead as it sets other rights and properties that
the service panel does not.
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:dufrvi$rlq$1@.ss405.t-com.hr...
> Why I can't make change account from Log on tab in properties MSSQLSERVER
> service?
> Regardless of account, I gett this error: "Could not start the MSSQLSERVER
> service on Local Computer.
> Error 1096: The service did not start to a logon failure."
> SQL Server does work only with Local system account?!
> Thanks
> (MS SQLserver 2000 , WinXP SP2)
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
> --
> Andrew J. Kelly SQL MVP
I gett error all the same|||Are there any msgs in the event logs? Does it work with the local admin
account?
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:duihmd$ru9$1@.ss405.t-com.hr...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> I gett error all the same
>|||Andrew J. Kelly wrote:
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
>
Just curious - which right is it that's being applied though EM and not
in the services panel?
I normally handle all changes to services from the services panel and I
don't recall that I've ever had problems with that.
Regards
Steen|||It depends upon the permissions configured for the new account. For instance
if you use the service panel to change a SQL Server service to run using
some administrator account, you probably won't have a problem. But if you
use the service panel to change a SQL Server service to to run under a low
priveledged user, then SQL Server might not be able to access sqlservr.exe
on the file system (and a bunch of other stuff). But if you use the SQL
Server tools, the tools will check to make sure the new account has adequate
access.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uHsEWEsQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
> Just curious - which right is it that's being applied though EM and not in
> the services panel?
> I normally handle all changes to services from the services panel and I
> don't recall that I've ever had problems with that.
> Regards
> Steen|||I have solved the problem. The difficulty was that I had a User Account
without password in my machine.
Thanks for your answer.|||Rick Byham [MS] wrote:
> It depends upon the permissions configured for the new account. For instan
ce
> if you use the service panel to change a SQL Server service to run using
> some administrator account, you probably won't have a problem. But if you
> use the service panel to change a SQL Server service to to run under a low
> priveledged user, then SQL Server might not be able to access sqlservr.exe
> on the file system (and a bunch of other stuff). But if you use the SQL
> Server tools, the tools will check to make sure the new account has adequa
te
> access.
Ok - that makes sense.
Regards
Steensql

Log on problem

Why I can't make change account from Log on tab in properties MSSQLSERVER
service?
Regardless of account, I gett this error: "Could not start the MSSQLSERVER
service on Local Computer.
Error 1096: The service did not start to a logon failure."
SQL Server does work only with Local system account?!
Thanks
(MS SQLserver 2000 , WinXP SP2)
You should never change the account directly thru the service. You need to
use Enterprise Manager instead as it sets other rights and properties that
the service panel does not.
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:dufrvi$rlq$1@.ss405.t-com.hr...
> Why I can't make change account from Log on tab in properties MSSQLSERVER
> service?
> Regardless of account, I gett this error: "Could not start the MSSQLSERVER
> service on Local Computer.
> Error 1096: The service did not start to a logon failure."
> SQL Server does work only with Local system account?!
> Thanks
> (MS SQLserver 2000 , WinXP SP2)
>
|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
> --
> Andrew J. Kelly SQL MVP
I gett error all the same
|||Are there any msgs in the event logs? Does it work with the local admin
account?
Andrew J. Kelly SQL MVP
"ER" <vladoer@.email.t-com.hr> wrote in message
news:duihmd$ru9$1@.ss405.t-com.hr...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%235vQSLSQGHA.564@.TK2MSFTNGP12.phx.gbl...
> I gett error all the same
>
|||Andrew J. Kelly wrote:
> You should never change the account directly thru the service. You need to
> use Enterprise Manager instead as it sets other rights and properties that
> the service panel does not.
>
Just curious - which right is it that's being applied though EM and not
in the services panel?
I normally handle all changes to services from the services panel and I
don't recall that I've ever had problems with that.
Regards
Steen
|||It depends upon the permissions configured for the new account. For instance
if you use the service panel to change a SQL Server service to run using
some administrator account, you probably won't have a problem. But if you
use the service panel to change a SQL Server service to to run under a low
priveledged user, then SQL Server might not be able to access sqlservr.exe
on the file system (and a bunch of other stuff). But if you use the SQL
Server tools, the tools will check to make sure the new account has adequate
access.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uHsEWEsQGHA.336@.TK2MSFTNGP12.phx.gbl...
> Andrew J. Kelly wrote:
> Just curious - which right is it that's being applied though EM and not in
> the services panel?
> I normally handle all changes to services from the services panel and I
> don't recall that I've ever had problems with that.
> Regards
> Steen
|||I have solved the problem. The difficulty was that I had a User Account
without password in my machine.
Thanks for your answer.
|||Rick Byham [MS] wrote:
> It depends upon the permissions configured for the new account. For instance
> if you use the service panel to change a SQL Server service to run using
> some administrator account, you probably won't have a problem. But if you
> use the service panel to change a SQL Server service to to run under a low
> priveledged user, then SQL Server might not be able to access sqlservr.exe
> on the file system (and a bunch of other stuff). But if you use the SQL
> Server tools, the tools will check to make sure the new account has adequate
> access.
Ok - that makes sense.
Regards
Steen

Log off Users in shared drive - Access2000

I have an Access2000 app placed on a shred drive. The problem i am having is that users leave it opened and take long breaks. Sometimes they leave it overnight. I would like to make corrections to the form but can't. How can I save and close all users. I would like to send a message telling all users to save any changes and give them 1 minute before shutting down.

ThanksHow do you plan to force them out?

I guess you could look at the timer interval event in Access...|||We have this problem on a small Access DB attached to Sybase. As far as we know, there's nothing you can do. The best I've managed to date is to open the lock file, take the computer names in the lock file and NETMSG them, but it doesn't guarantee they'll close the DB.

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:

> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt
.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.

>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.
Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> > Hi,
> > The log of the TEMPDB database is growing and growing. What should i do ?
> > What did i arrive to this situation?
> > For information we are using SSIS and OLAP, maybe it will help to answer.
> > Thanks.
>
>|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>> Hi,
>> The log of the TEMPDB database is growing and growing. What should i do ?
>> What did i arrive to this situation?
>> For information we are using SSIS and OLAP, maybe it will help to answer.
>> Thanks.
>
--
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com

LOG of TEMPDB are growing

Hi,
The log of the TEMPDB database is growing and growing. What should i do ?
What did i arrive to this situation?
For information we are using SSIS and OLAP, maybe it will help to answer.
Thanks.
Truncate the log and then run a DBCC shrinkfile returning to the desired
size. If this is a continious issue then maybe the log needs to be that
large.

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITjobfeed.com
"MIB" <MIB@.discussions.microsoft.com> wrote in message
news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
> Hi,
> The log of the TEMPDB database is growing and growing. What should i do ?
> What did i arrive to this situation?
> For information we are using SSIS and OLAP, maybe it will help to answer.
> Thanks.
|||Thanks,
But many times should i do this operation, each day each week , ...?
The Tempdb is used by many operation, if i shrinck the database maybe i will
interrupt some information on the server.
Thanks
"Jack Vamvas" wrote:

> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
>
|||"Jack Vamvas" <DEL_TO_REPLY@.del.com> wrote in message
news:2oydnUjgCo8ahJ3bnZ2dnUVZ8qijnZ2d@.bt.com...
> Truncate the log and then run a DBCC shrinkfile returning to the desired
> size. If this is a continious issue then maybe the log needs to be that
> large.
I'd probably not do the shrinkfile at all.
It will add disk I/O to what is generally your most performance critical DB
on the shrink and then again on the expansion. And you may end up with
disk-level fragmentation which will further hurt you.
I would monitor it and find out what's going on though.
Among other things, try DBCC OPENTRAN and see if there's any really long
running transactions.
Also, chekc your collations on all your databases. I found that we had a DB
doing some massive joins with another database and the different collations
wer causing problems.

>
> --
> Jack Vamvas
> ___________________________________
> Advertise your IT vacancies for free at - http://www.ITjobfeed.com
>
> "MIB" <MIB@.discussions.microsoft.com> wrote in message
> news:681E3BCE-91E9-4165-BD43-4166ACA29D54@.microsoft.com...
>
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
sql

Log of SQL

Good Afternoon,
I Want to consult if is possible read the Log File (LDF), because I Need to
Know What movements have had the DataBase is diferents Tables.
Thak for you help
Felipe Bolvar RestrepoI don't think it is easy to understand using DBCC LOG, otherwise third party
tools such as APexSQL or Lumigent's Log explorer will help you with suitable
format.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Luis Felipe Bol_var Restrepo" wrote:

> Good Afternoon,
> I Want to consult if is possible read the Log File (LDF), because I Need t
o
> Know What movements have had the DataBase is diferents Tables.
> Thak for you help
>
> Felipe Bol_var Restrepo
>
>

Log of SQL

Good Afternoon,
I Want to consult if is possible read the Log File (LDF), because I Need to
Know What movements have had the DataBase is diferents Tables.
Thak for you help
Felipe Bolvar Restrepo
I don't think it is easy to understand using DBCC LOG, otherwise third party
tools such as APexSQL or Lumigent's Log explorer will help you with suitable
format.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Luis Felipe BolXvar Restrepo" wrote:

> Good Afternoon,
> I Want to consult if is possible read the Log File (LDF), because I Need to
> Know What movements have had the DataBase is diferents Tables.
> Thak for you help
>
> Felipe BolXvar Restrepo
>
>

Log of rows inserted/updated/deleted

HI, I am wondering if it is possible to retreive this information without using row count transform. Can I get the # of rows inserted/updated or deleted by destination from the log?

Thank you,
Ccote

An OnInformation event is raised by the data-flow task when data flows into a destination. This has free text which contains the number of rows. You would have to parse that information out of the text within the OnInformation eventhandler. See here for more details: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=372847&SiteID=1

-Jamie

Log of Executed SQL

I am sure this is a very newbie questions, but...

How do you view what SQL statements have been run on your DB?

I have written an application in C#, and for some reasons some rows are being dropped. I would like to look up what SQL has been run against the Database to make it lose the rows. I was able to do this in MySql, but can not figure out how to do it in SQL Server/Express 2005.

Thanks for any help,

Normally you would use a nice tool like SQL Profiler to give you this data. But in your case (you don't get this tool with Express) I would take a look at sp_trace_create in Books Online and start from there.|||Thanks! That is exactly what I was looking for.

Log of Executed SQL

I am sure this is a very newbie questions, but...

How do you view what SQL statements have been run on your DB?

Thanks for any help,

Log of database changes

Hi All,
I would like to know if it is possible to figure out what user change the field names of one of my tables
on my SQL Server.
It seems that somebody change and after all nobody it guilty.
Do you know?
Thanks in advance
Roberto
You should be able to find the log entry for ALTER TABLE or sp_rename if it
was recent enough.
http://www.lumigent.com/products/le_sql/le_sql.htm
http://www.lockwoodtech.com/index_lognavigator.htm
http://www.logpi.com/
http://www.apexsql.com/
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Roberto C3" <anonymous@.discussions.microsoft.com> wrote in message
news:A4CEF9C9-5F8E-464C-BA3C-29DD271D1386@.microsoft.com...
> Hi All,
> I would like to know if it is possible to figure out what user change the
> field names of one of my tables
> on my SQL Server.
> It seems that somebody change and after all nobody it guilty.
> Do you know?
> Thanks in advance
> Roberto

Log of database changes

Hi All
I would like to know if it is possible to figure out what user change the field names of one of my table
on my SQL Server
It seems that somebody change and after all nobody it guilty
Do you know
Thanks in advanc
RobertoYou should be able to find the log entry for ALTER TABLE or sp_rename if it
was recent enough.
http://www.lumigent.com/products/le_sql/le_sql.htm
http://www.lockwoodtech.com/index_lognavigator.htm
http://www.logpi.com/
http://www.apexsql.com/
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Roberto C3" <anonymous@.discussions.microsoft.com> wrote in message
news:A4CEF9C9-5F8E-464C-BA3C-29DD271D1386@.microsoft.com...
> Hi All,
> I would like to know if it is possible to figure out what user change the
> field names of one of my tables
> on my SQL Server.
> It seems that somebody change and after all nobody it guilty.
> Do you know?
> Thanks in advance
> Roberto

Log of database changes

Hi All,
I would like to know if it is possible to figure out what user change the fi
eld names of one of my tables
on my SQL Server.
It seems that somebody change and after all nobody it guilty.
Do you know?
Thanks in advance
RobertoYou should be able to find the log entry for ALTER TABLE or sp_rename if it
was recent enough.
http://www.lumigent.com/products/le_sql/le_sql.htm
http://www.lockwoodtech.com/index_lognavigator.htm
http://www.logpi.com/
http://www.apexsql.com/
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
(Reverse e-mail to reply.)
"Roberto C3" <anonymous@.discussions.microsoft.com> wrote in message
news:A4CEF9C9-5F8E-464C-BA3C-29DD271D1386@.microsoft.com...
> Hi All,
> I would like to know if it is possible to figure out what user change the
> field names of one of my tables
> on my SQL Server.
> It seems that somebody change and after all nobody it guilty.
> Do you know?
> Thanks in advance
> Roberto

Log not needed

In an unusual circumstance I face, there is a very large log created by the
importation of several million rows from another database. After importing,
the database will never change. As the original database is still
available, and the import could be re-run at any time from this, neither
logging nor backup is desirable. The whole thing can be recreated whenever
needed (the original from which the import is done is backed up).
This is for Express.
What facilities are available to either suppress the log or remove it? It
is several gigabytes long after the import.
As I understand it, SELECT queries to search the database will not create
any log, and it will never grow again. Right?
Tom EllisonWhat about a read only DB?
Lookup ALTER DATABASE (Transact-SQL) in BOL it is covered in that section
http://sqlservercode.blogspot.com/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
> In an unusual circumstance I face, there is a very large log created by
> the importation of several million rows from another database. After
> importing, the database will never change. As the original database is
> still available, and the import could be re-run at any time from this,
> neither logging nor backup is desirable. The whole thing can be recreated
> whenever needed (the original from which the import is done is backed up).
> This is for Express.
> What facilities are available to either suppress the log or remove it? It
> is several gigabytes long after the import.
> As I understand it, SELECT queries to search the database will not create
> any log, and it will never grow again. Right?
> Tom Ellison
>
>|||Dear SQL:
In BOL "ALTER DATABASE/Transact SQL Reference":
READONLY
Specifies the filegroup is read-only. Updates to objects in it are not
allowed. The primary filegroup cannot be made read-only. Only users with
exclusive database access can mark a filegroup read-only.
My log is in the primary filegroup, right? Is there more to it than this?
Tom Ellison
"SQL" <sqlservercode@.gmail.com> wrote in message
news:u8IUAOgRGHA.4608@.tk2msftngp13.phx.gbl...
> What about a read only DB?
> Lookup ALTER DATABASE (Transact-SQL) in BOL it is covered in that section
> http://sqlservercode.blogspot.com/
>
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
>|||Would it not be possible and simple to shrink the log to nothing after the
initial import? It could then function normally, meaning nothing would
happen in ordinary operations, just searching the database.
Tom Ellison
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
> In an unusual circumstance I face, there is a very large log created by
> the importation of several million rows from another database. After
> importing, the database will never change. As the original database is
> still available, and the import could be re-run at any time from this,
> neither logging nor backup is desirable. The whole thing can be recreated
> whenever needed (the original from which the import is done is backed up).
> This is for Express.
> What facilities are available to either suppress the log or remove it? It
> is several gigabytes long after the import.
> As I understand it, SELECT queries to search the database will not create
> any log, and it will never grow again. Right?
> Tom Ellison
>
>|||Yes you could issue a BACKUP LOG (logname) WITH TRUNCATE_ONLY followed by a
DBCC SHRINKFILE command
http://sqlservercode.blogspot.com/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message
news:%232PoWZgRGHA.1780@.TK2MSFTNGP12.phx.gbl...
> Would it not be possible and simple to shrink the log to nothing after the
> initial import? It could then function normally, meaning nothing would
> happen in ordinary operations, just searching the database.
> Tom Ellison
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
>|||> My log is in the primary filegroup, right?
No, the log isn't in any file group. The file group concept is only for data
files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message news:Ou1avWgRGHA.1160@.TK2MSFTNGP09.ph
x.gbl...
> Dear SQL:
> In BOL "ALTER DATABASE/Transact SQL Reference":
> READONLY
> Specifies the filegroup is read-only. Updates to objects in it are not
> allowed. The primary filegroup cannot be made read-only. Only users with
> exclusive database access can mark a filegroup read-only.
> My log is in the primary filegroup, right? Is there more to it than this?
> Tom Ellison
>
> "SQL" <sqlservercode@.gmail.com> wrote in message
> news:u8IUAOgRGHA.4608@.tk2msftngp13.phx.gbl...
>|||I'd go for first simple recovery mode, then shrinking the log file after the
import (tips and hints
at http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Then set the data
base (not filegroup) to
read-only.
Also, see if you can do the import minimally logged. Some import mechanisms
allow for this (BCP,.
BULK INSERT, DTS if you do it right etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tom Ellison" <tellison@.jcdoyle.com> wrote in message news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.ph
x.gbl...
> In an unusual circumstance I face, there is a very large log created by th
e importation of several
> million rows from another database. After importing, the database will ne
ver change. As the
> original database is still available, and the import could be re-run at an
y time from this,
> neither logging nor backup is desirable. The whole thing can be recreated
whenever needed (the
> original from which the import is done is backed up).
> This is for Express.
> What facilities are available to either suppress the log or remove it? It
is several gigabytes
> long after the import.
> As I understand it, SELECT queries to search the database will not create
any log, and it will
> never grow again. Right?
> Tom Ellison
>
>|||Dear Tibor:
Thank you very much.
Now, on site, we do not have tools for production. There is only SQL
Express and Access. The OS is not a server. Am I correct to rule out BCP
and DTS on this basis?
As the source is an Access MDB, I'm thinking BULK INSERT does not apply.
Can BULK INSERT be done from an mdb? If so, what parameters allow for this?
Assuming there may not be an extra 3 gigabytes of disk space on every
computer where we need to install this, it would be a good thing just not to
create this log.
What would it do if the log were on a NULL device?
Is there any hope?
Tom Ellison
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evtXglgRGHA.5908@.TK2MSFTNGP14.phx.gbl...
> I'd go for first simple recovery mode, then shrinking the log file after
> the import (tips and hints at
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Then set the
> database (not filegroup) to read-only.
> Also, see if you can do the import minimally logged. Some import
> mechanisms allow for this (BCP,. BULK INSERT, DTS if you do it right etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tom Ellison" <tellison@.jcdoyle.com> wrote in message
> news:eU7tOIgRGHA.2224@.TK2MSFTNGP10.phx.gbl...
>|||Tom Ellison (tellison@.jcdoyle.com) writes:
> Now, on site, we do not have tools for production. There is only SQL
> Express and Access. The OS is not a server. Am I correct to rule out BCP
> and DTS on this basis?
DTS (or SSIS), yes, BCP no. BCP comes with SQL Server Express.

> As the source is an Access MDB, I'm thinking BULK INSERT does not apply.
> Can BULK INSERT be done from an mdb? If so, what parameters allow for
> this?
I don't know much about Access, but as Access, or rather Jet, is a
DB engine, an MDB file is likely to have more than data. I mean it
has things like index pointers etc. So the answer would be no.

> Assuming there may not be an extra 3 gigabytes of disk space on every
> computer where we need to install this, it would be a good thing just
> not to create this log.
> What would it do if the log were on a NULL device?
I seriously doubt that SQL Server would let you get away with it.
The first step is to set the database to simple recovery. In this mode,
SQL Server will truncate the log regularly. Since the log cannot be
truncated past the oldest open transaction, it can still become huge,
if your transactions are huge. To keep down the log size, you could import
data in batches, of, say, 1000 rows at a time. Bowever, this is more
complex, and the import will take longer time. (You don't say how you
run the import, but I assume that you run queries against a linked
server.)
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|||Dear Erland:
Thanks very much. Pretty much what I expected.
The initial installation step is to import about 10 million rows. This is
going to create a HUGE log file, for which we have no use. I doubt the 10
gigabytes will be available on the destination computers. Some are just
laptops.
We have just spent several hours investigating various problems this is
causing.
We are first defragmenting the hard drive. However, creating both database
and log are causing a fratmented database file. In addition, as I said,
there will likely not be enough space for both on the hard drive.
Right now we are using Access to append the rows to SQL Express. It is far
too slow. We drop the indexes ahead of time, append a few million records,
and recreate the indexes. It is taking hours to do this. I'd really like
to find a way to speed this. The best approach of which I know is to create
a linked server to the Access database. I've always done that before using
EM. When I use this in QA:
sp_addlinkedserver @.server = 'MyJet', @.srvproduct = '', @.provider =
'Microsoft.Jet.OLEDB.4.0',
@.datasrc = 'C:\Documents and Settings\All Users\Documents\TData.mdb'
I cannot then access the Jet table:
SELECT * FROM MyJet...TData
Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyJet"
reported an error. Authentication failed.
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "MyJet".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyJet" returned
message "Cannot start your application. The workgroup information file is
missing or opened exclusively by another user.".
I used the same script then to try to create the linked server in SQL 2000,
and it fails similarly. However, I then opened that SQL 2000 linked server
using EM and changed:
Linked Server Properties
Security
For a login not defined in the list above, connections will:
Be made without using a security context
Or course, EM doesn't work at all with Express, and it is only by solving
this in Express that it will accomplish anything toward my goals. I've also
tried to see what T-SQL EM is generating to do this, but with no success.
If I knew just what EM was doing, it might fix me up pretty quickly, right?
The SQL 2000 linked server then commenced working. I expect this relates to
setting one of the options in the linked server, but I have not been able to
isolate it.
If I could find how to fix my linked server within Express, then I am hoping
to see improved performance in the appending.
My goals are to improve this performance and to also prevent the creation of
a useless multi-gigabyte log, which may not fit within the available disk
space, and which horribly fragments the creation of the database. I may
just break down and size the database in advance.
Please make any recommendations that occur to you. And thank you very much,
again.
Tom Ellison
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97856F8AD31CYazorman@.127.0.0.1...
> Tom Ellison (tellison@.jcdoyle.com) writes:
> DTS (or SSIS), yes, BCP no. BCP comes with SQL Server Express.
>
> I don't know much about Access, but as Access, or rather Jet, is a
> DB engine, an MDB file is likely to have more than data. I mean it
> has things like index pointers etc. So the answer would be no.
>
> I seriously doubt that SQL Server would let you get away with it.
> The first step is to set the database to simple recovery. In this mode,
> SQL Server will truncate the log regularly. Since the log cannot be
> truncated past the oldest open transaction, it can still become huge,
> if your transactions are huge. To keep down the log size, you could import
> data in batches, of, say, 1000 rows at a time. Bowever, this is more
> complex, and the import will take longer time. (You don't say how you
> run the import, but I assume that you run queries against a linked
> server.)
> --
> 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

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilsonWhat recovery model is the database in? the log is not truncated by a db bac
kup. So if full recovery
model, you either have to do regular transaction log backups or set the data
base to simple recovery
model.
Also, see for general considerations. http://www.karaszi.com/SQLServer/in...r />
hrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.ph
x.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
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/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
just[vbcol=seagreen]
the[vbcol=seagreen]
>sql

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilsonWhat recovery model is the database in? the log is not truncated by a db backup. So if full recovery
model, you either have to do regular transaction log backups or set the database to simple recovery
model.
Also, see for general considerations. 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/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
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/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> > At one client site (running SQL Server 2000), I have a log file that
just
> > grows & grows. The maintenance plan performs a full backup nightly and
the
> > AutoShrink option is checked.
> >
> > I can run a script to forcibly shrink the thing, but the automated DB
> > maintenance should be handling this.
> >
> > What else can I check?
> >
> > Thanks.
> >
> > dwilson
> >
> >
>

Log not auto-shrinking

At one client site (running SQL Server 2000), I have a log file that just
grows & grows. The maintenance plan performs a full backup nightly and the
AutoShrink option is checked.
I can run a script to forcibly shrink the thing, but the automated DB
maintenance should be handling this.
What else can I check?
Thanks.
dwilson
What recovery model is the database in? the log is not truncated by a db backup. So if full recovery
model, you either have to do regular transaction log backups or set the database to simple recovery
model.
Also, see for general considerations. 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/
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>
|||Are you backing up the T-Log in the plan? That should be freeing space
within the physical device so that it does not need to grow.
Autoshrink may not be the best idea here, as the file will then need to
autogrow at some point...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...
> At one client site (running SQL Server 2000), I have a log file that just
> grows & grows. The maintenance plan performs a full backup nightly and the
> AutoShrink option is checked.
> I can run a script to forcibly shrink the thing, but the automated DB
> maintenance should be handling this.
> What else can I check?
> Thanks.
> dwilson
>
|||Thank you both.
Great article, Tibor.
I'll turn off auto-shrink b/c I really want only the log file to shrink &
I'll schedule routine backups of the log file.
dwilson
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HF$6oonFHA.3564@.tk2msftngp13.phx.gbl...
> What recovery model is the database in? the log is not truncated by a db
backup. So if full recovery
> model, you either have to do regular transaction log backups or set the
database to simple recovery
> model.
> Also, see for general considerations.
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/
>
> "Daniel Wilson" <d.wilson@.embtrak.com> wrote in message
news:OP2hu8nnFHA.420@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
just[vbcol=seagreen]
the
>

Log MDX en AS 2000

I'm wondering if there Is any way to log the mdx queries in AS 2000, I would like to know how to register the query that I am making when drill down in OLAP or when I use a Pivot Table.

I have seen in the Analysis Server properties there is an option called 'Log queries sent to server', but register no MDX queries in the 'txt file'. I have heard about editing the repository connection string and add a log file but I am not sure how to set it and how does it works.

Could anybody help me please?

Here are two links that might help:

http://www.databasejournal.com/features/mssql/article.php/3080511

http://www.databasejournal.com/features/mssql/article.php/3091021

HTH

Thomas Ivarsson

|||

I think the links above are slightly unrelated to the original question.

What you need to do is to edit the .oqy file which Excel uses to find the connection to AS, and add ";Log File=c:\mdxqueries.txt" right after "Data Source=...;Initial Catalog=..." and this should do it.

Log logins

On a huge Sql-Server 7 installation, we have various client applications
distributed along the Lan, accessing one main database.

Each application accesses the db using one out of around ten logins. Most of
them, have only DBDataReader right on the db, as they are consultation
consolles only.

In order to monitor db usage, the customer requires some kind of log of user
access.

My need, mainly, is to INSERT a record into a log table, recording Date,
Time, Login, Host of each access.

But, and this is the problem, the job has to be done by the server itself,
not by each client, because of various reasons:
1) we don't like to increase rights of logins
2) we don't plan to change anything in our custom client application
3) few of those client applications have been developed by foreign
suppliers, so we cannot change them.

My question is: does it exist any kind of authentication LOG, which I can
work on?
Or, is it possible to activate a kind of TRIGGER, reacting on login
authentication?

Thanks in advance

AlbertoIf you right-click on the server name in enterprise manager (EM) and click
on properties, click on the SECURITY TAB.
You can then audit sucessfull logins, login failures, or all. There is only
2 things you have to remember.
1. You will have to read either the SQL server error log or the Application
log of Windows.
2. If people use generic logins or shared logins, you might not be able to
detect who it was that logged in.

Hope this helps
Oscar...

"Albe V" <vaccariTOGLI_QUESTO@.hotmail.com> wrote in message
news:JAZKa.125587$Ny5.3548606@.twister2.libero.it.. .
> On a huge Sql-Server 7 installation, we have various client applications
> distributed along the Lan, accessing one main database.
> Each application accesses the db using one out of around ten logins. Most
of
> them, have only DBDataReader right on the db, as they are consultation
> consolles only.
> In order to monitor db usage, the customer requires some kind of log of
user
> access.
> My need, mainly, is to INSERT a record into a log table, recording Date,
> Time, Login, Host of each access.
> But, and this is the problem, the job has to be done by the server itself,
> not by each client, because of various reasons:
> 1) we don't like to increase rights of logins
> 2) we don't plan to change anything in our custom client application
> 3) few of those client applications have been developed by foreign
> suppliers, so we cannot change them.
> My question is: does it exist any kind of authentication LOG, which I can
> work on?
> Or, is it possible to activate a kind of TRIGGER, reacting on login
> authentication?
> Thanks in advance
> Alberto|||Albe V (vaccariTOGLI_QUESTO@.hotmail.com) writes:
> On a huge Sql-Server 7 installation, we have various client applications
> distributed along the Lan, accessing one main database.
> Each application accesses the db using one out of around ten logins.
> Most of them, have only DBDataReader right on the db, as they are
> consultation consolles only.
> In order to monitor db usage, the customer requires some kind of log of
> user access.
> My need, mainly, is to INSERT a record into a log table, recording Date,
> Time, Login, Host of each access.

The best tool for this is the SQL Profiler. Set up a trace that captures
login events. I don't have the SQL7 docs available, so I prefer to not
give any details, but refer you to Books Online. On SQL2000 you can set
C2 auditing, which enures that you don't loose events if the log runs
out of disk space. (That throttles the server instead.) But I don't think
C2 is available on SQL7. Then again, with only ten users, you are not
likely to fill the log if you only filter logins.

Beware, though, that applications that may be written so that they
connect and reconnect frequently, for instance once per query.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Log LastChangedDate with trigger

CREATE TRIGGER logChanges
ON RepInfo
FOR UPDATE
AS
DECLARE @.RepInfoID INT
IF COLUMNS_UPDATED() > 0
UPDATE RepInfo
SET LastChangedDate = GetDate()
WHERE @.RepInfoID = RepInfoID
GO
--
Trying to update LastChangedDate in a given record when any of the columns
in the record are modified. The first time I ran this, I did not have any of
the "@.RepInfoID" clauses, and I go the whole column to update to GETDATE();
I
didn't want that, so I threw in the references to RepInfoID since it's the
identity column, trying to mimic some VB.NET code I have in another
application. No joy. This has to be a simple fix, but one that's elusive to
me. Any help is appreciated!
--
Thanks,
-AAAaron wrote:
> CREATE TRIGGER logChanges
> ON RepInfo
> FOR UPDATE
> AS
> DECLARE @.RepInfoID INT
> IF COLUMNS_UPDATED() > 0
> UPDATE RepInfo
> SET LastChangedDate = GetDate()
> WHERE @.RepInfoID = RepInfoID
> GO
> --
> Trying to update LastChangedDate in a given record when any of the columns
> in the record are modified. The first time I ran this, I did not have any
of
> the "@.RepInfoID" clauses, and I go the whole column to update to GETDATE()
; I
> didn't want that, so I threw in the references to RepInfoID since it's the
> identity column, trying to mimic some VB.NET code I have in another
> application. No joy. This has to be a simple fix, but one that's elusive t
o
> me. Any help is appreciated!
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
CREATE TRIGGER logChanges
ON RepInfo
FOR UPDATE
AS
- -- Assuming Columns_Updated() is a user-defined function
IF COLUMNS_UPDATED() > 0
UPDATE RepInfo
SET LastChangedDate = GetDate()
FROM inserted as i
WHERE i.RepInfoID = RepInfo.RepInfoID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhqcxoechKqOuFEgEQKr4ACgvVYOXFpxFHW1
vT9osNSW/67oajAAnj9u
y8qk0gGECHy2AZQ9bWS00e6K
=Jj7k
--END PGP SIGNATURE--|||Thanks Pal, that works great!
Btw, IF (COLUMNS_UPDATED()) is not a user-defined function. Here's a clip
from SQL Server Books Online:
--<Snip>--
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or
columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or updated.
--<Snip>--
-AA
"MGFoster" wrote:

> Aaron wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Try this:
> CREATE TRIGGER logChanges
> ON RepInfo
> FOR UPDATE
> AS
> - -- Assuming Columns_Updated() is a user-defined function
> IF COLUMNS_UPDATED() > 0
> UPDATE RepInfo
> SET LastChangedDate = GetDate()
> FROM inserted as i
> WHERE i.RepInfoID = RepInfo.RepInfoID
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQhqcxoechKqOuFEgEQKr4ACgvVYOXFpxFHW1
vT9osNSW/67oajAAnj9u
> y8qk0gGECHy2AZQ9bWS00e6K
> =Jj7k
> --END PGP SIGNATURE--
>sql

Log just keeps growing!

> Hi,
>
> My 600Mb database continually has a 4Gb log file with it, which is a bit
of
> a pain as we have a very small number of inserts during the day so there's
> no need for the log to be this big.
>
> We do have a load process that loads approx 150,000 - 200,000 rows
running
> once a week, but, even still I can't see why the log grows as it does.
Every
> night we have a full backup running.
>
> So, I've got a couple of questions:
>
> 1) - What backup mode do I need to have set on the DB to ensure once
> it's backed up the log is then truncated correctly? I'm guessing the log
> itself is being truncated but the actual file isn't being shrunk?
>
> 2) - I keep detaching the DB and then reattaching it without a log
to
> get rid of the huge log file. Is this the best way to keep this huge log
> under control or am I risking data corruption?
>
> 3) - Are there any jobs I can run during the day that can keep this
> log under control?
>
> Any advice appreciated.
>
> Thanks
>
>You can use simple recovery mode. For more information check these out:
http://www.support.microsoft.com/?id=110139
http://www.support.microsoft.com/?id=272318
http://www.support.microsoft.com/?id=317375
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>|||Bear in mind, the transaction log supports point-in-time database recovery.
If you need this functionality, truncating the log frequently during the day
will cause a problem.
What you should do is schedule frequent transaction log backups (to disk if
you plan point-in-time recovery, then make sure to back those up to tape
quickly, so you can clear off disk space). To recover the newly freed
space, schedule a job to run fairly frequently in the database that does
dbcc shrinkfile (<database_name>_log, 0) (in SQL 7, do not put the ,0 in).
Anyhow, here is your detail on backup modes:
Full recovery model (select into/bulk copy disabled, trunc. log on chkpt.
disabled) -- This is the default. It enables you to do point-in-time
restores, but the transaction log will grow unless you back it up frequently
to disk, and then shrink the log file afterwards.
Bulk logged recovery model (select into/bulk copy enabled, trunc. log on
chkpt. disabled) -- This improves the performance of bulk data loads and
other data processes (eg create index) other than simple INSERTs, UPDATEs
and DELETEs by only minimally logging the event. If you plan to backup the
transaction log so you can restore from it later (point-in-time restore),
you need to a full or differential database backup AFTER EVERY database
operation other than a INSERT, UPDATE or DELETE. You still need to backup
the log frequently, and schedule dbcc shrinkfile to return the inactive
space to the OS
Simple Recovery model (trunc. log on chkpt and select/into bulk copy
enabled). Not only are major database operations not logged, the
transaction log is automatically emptied out at regular intervals
(checkpoints). The only backups you can restore from here are full database
backups or differential backups. You may need to still run dbcc shrinkfile
to keep the log file under control.
If you have more questions, you can try emailing me, I'm not sure if my
account is still getting spammed out of control or not.
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andy_mcdba@.yahoo.com
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
Andy_mcdba@.yahoo.com gets filled up to the account
limit with spam every couple of hours now so replies may
not be possible. I will remove this disclaimer once every
ISP involved with relaying the spam can help me out.
*******************************************************************
"London Developer" <dev@.nowhere.com> wrote in message
news:%23GGCbQngDHA.1760@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > My 600Mb database continually has a 4Gb log file with it, which is a bit
> of
> > a pain as we have a very small number of inserts during the day so
there's
> > no need for the log to be this big.
> >
> > We do have a load process that loads approx 150,000 - 200,000 rows
> running
> > once a week, but, even still I can't see why the log grows as it does.
> Every
> > night we have a full backup running.
> >
> > So, I've got a couple of questions:
> >
> > 1) - What backup mode do I need to have set on the DB to ensure
once
> > it's backed up the log is then truncated correctly? I'm guessing the log
> > itself is being truncated but the actual file isn't being shrunk?
> >
> > 2) - I keep detaching the DB and then reattaching it without a log
> to
> > get rid of the huge log file. Is this the best way to keep this huge log
> > under control or am I risking data corruption?
> >
> > 3) - Are there any jobs I can run during the day that can keep
this
> > log under control?
> >
> > Any advice appreciated.
> >
> > Thanks
> >
> >
>

Log issues

Hi All,
I have 2 issues with my log files.
1) I have a database where I combine 20 tables from 20
DBs into one very large table. Each table in the 20 DBs
are the same. My procedure uses 20 insert into
statements. For some reason my log file fills and the job
fails. Does anyone know how I can stop the file from
growing like this?
2) I created my Dbs with 400MB trans file. I only use
about 30MB. Is there a way to drop the size down to 50MB
and let it grow from there?
TIA
Joe
1) you can either change the recovery model to simple(if you do not wish to
log the process), however if you are deleting a large number of records in
one transaction(in which case, you will still exceed your configured trans
log size of 400Mb) then you will have to batch these deletes and do them in
smaller/manageable batches.
2) dbcc shrinkfile(<logicalname logfile>,50,turncateonly)
check out BOL for more info.
"JOE" wrote:

> Hi All,
> I have 2 issues with my log files.
> 1) I have a database where I combine 20 tables from 20
> DBs into one very large table. Each table in the 20 DBs
> are the same. My procedure uses 20 insert into
> statements. For some reason my log file fills and the job
> fails. Does anyone know how I can stop the file from
> growing like this?
> 2) I created my Dbs with 400MB trans file. I only use
> about 30MB. Is there a way to drop the size down to 50MB
> and let it grow from there?
> TIA
> Joe
>
|||Thanks for the info.
I will try the simple.
I do not delete but I do Truncate my table before I stert
the inserts. I Truncate because I know it does not hit
the Trans Log.
Joe

Log issues

'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.

I have been developing a large SSIS project and it uses scripts extensively.

During development, i encountered no problems.

However, when i tried executing them on a server (windows server 2003), i was given this error for all the scripts:

Here is a brief log:

PackageStart,servername,network\login,PkgName,{A6778813-1F3A-4133-A00C-6F02AC8CC8B1},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:26 PM,3/10/2006 5:24:26 PM,0,0x,Beginning of package execution.

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Variables' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 26 Columns 51-63
Line Text: Dim DaysToKeepErrorFile As Integer = CInt(Dts.Variables("vDaysToKeepErrorFiles").Value.ToString)

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 49 Columns 25-31
Line Text: Dts.Log(MaintenanceMsg, 0, ZeroByte)

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,7,0x,Error 30456: 'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.
Line 55 Columns 17-23
Line Text: Dts.Log("Script Error:" + ex.Message, 0, ZeroByte)

OnError,servername,network\login,PkgName,Rename Error Files,{8d38e1b7-44e8-419e-963e-c689edd0be2c},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,5,0x,The script files failed to load.
PackageEnd,servername,network\login,PkgName,{A6778813-1F3A-4133-A00C-6F02AC8CC8B1},{EA6E6337-8C07-428D-9E3B-CEEB4F95A185},3/10/2006 5:24:30 PM,3/10/2006 5:24:30 PM,1,0x,End of package execution.

I've been using the same scripts for months in development and testing. Both dev and test server has SQL 2005 and SSIS installed.

What causes this?

Seems very strange, as obvious Log is a method of the ScriptObjectModel class.

Can you logon to the server anbd built a test package on the server and try running it there an then in BIDS, obviously using a script task a Log.

Has this ever worked on the problem server, or is it a recent failure? Are the good and bad machines running the same service pack? I am not clear on what machines work and fail and if failures relate to some or all machines.

|||

If you find it strange, imagine me! haha...

Do i need VS2005 installed on the server to open the VBA window to edit the script? I got this error when trying :

Cannot show the editor for this task.

ADDITIONAL INFORMATION:

The operation could not be completed. (Microsoft.VisualBasic.Vsa.DT)

1. I've checked the assembly versions of Microsoft.SqlServer.ManagedDTS.dll and microsoft.sqlserver.scripttask.dll on the server and my machine, they're the same. 9.0.242.0

2. This is the first time we're trying to deploy on the server. It has worked fine on four other machines : 2 desktops and 2 laptops.

3. The server is running SP1 the good machines aren't. I figured this shouldn't be a problem as SP1 should be 'backward compatible'. I'll try to get SP1 on the good machines just to eliminate the problem, but it'll take some time, which i don't have.

Any other ideas?

|||

Things have gotten more interesting.

My package has multiple scripts and i discovered that some work, and some dont!

What makes it interesting is that each of these scripts are identitical (i copied and pasted the original script i wrote), except for name and variable values.

If the script's content is identical, and both use dts.log and dts.variables object, why is it that one works and one doesnt?

What on earth could be causing this?

Another question:

where is the reference 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel' referenced from? its not part of the references i see in the VSA ide but i see it in the package xml. I couldn't find it in the windows\assembly folder either. Anybody knows ANYTHING about this issue?

|||

Well, i've got it solved.

I copied a working script, deleted the bad ones and pasted them in again. I think the important step is after pasting the copied script task, i opened the script in the editor and closed it. Somehow, this seemed to work. Although it still doesn't solve the mystery of why it worked on some machines but failed on others. The only difference is the server was running Windows Server 2003 and the development was winxp.

So to anyone whos reusing scripts, i suggest opening the 'design script' window and closing it after pasting, just to be safe and make sure SSIS precompiles everything properly.

Log is growing crazy when run DBCC INDEXDEFRAG or DBREINDEX

We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
server used for JDE application. Database size is 100 Gig. This DB is
configured for replication (only 25 tables). This database is also
configured for Log ship to a stand by server where we run our reports.
When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
file start growing crazy, which makes replication and log ship to break.
Our concern is how can we avoid growing log file while DBREINDEX or
INDEXDEFRAG running?
Your response is appreciated.
Thanks,
AbbasBackup or truncate the transaction log frequently during
the processes is the only thing I can think of. Or set
the Database Recovery model to Simple. Any other ideas?
>--Original Message--
>We have SQL 2K Enterprise Edition with SP3. Server is
dedicated database
>server used for JDE application. Database size is 100
Gig. This DB is
>configured for replication (only 25 tables). This
database is also
>configured for Log ship to a stand by server where we run
our reports.
>When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this
database, the log
>file start growing crazy, which makes replication and log
ship to break.
>Our concern is how can we avoid growing log file while
DBREINDEX or
>INDEXDEFRAG running?
>Your response is appreciated.
>Thanks,
>Abbas
>
>.
>|||*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||You can't. These actions, like all in the server are logged. These type of
actions will send a lot of data to the log files. I suggest doing them in
small batches so the logs can recover in between the reindexing. If done
often or there is little fragmentation INDEXDEFRAGmay produce less log
entries.
--
Andrew J. Kelly
SQL Server MVP
"Moh Abb" <mabbas@.aligntech.com> wrote in message
news:%23kkvKL%23cDHA.1828@.TK2MSFTNGP10.phx.gbl...
> We have SQL 2K Enterprise Edition with SP3. Server is dedicated database
> server used for JDE application. Database size is 100 Gig. This DB is
> configured for replication (only 25 tables). This database is also
> configured for Log ship to a stand by server where we run our reports.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
> Your response is appreciated.
> Thanks,
> Abbas
>
>|||Hello
You can't avoid this, but you can accommodate yourself to this :-)
I'm using the system of two connected jobs. One of them runs
DBCC INDEXDEFRAG and second periodically (every minute)
checks log state and stops first job when log have more than 70%
of space filled. And the system waits for the next log backup and
starts again. In general controlling job can start log backup instead
of stopping defragmentation.
> When we run DBCC DBREINDEX or DBCC INDEXDEFRAG on this database, the log
> file start growing crazy, which makes replication and log ship to break.
> Our concern is how can we avoid growing log file while DBREINDEX or
> INDEXDEFRAG running?
Serge Shakhovsql

Friday, March 30, 2012

Log parser

Log on server error

Log On problems...

Log on Problem.

Log on Problem.

Log on Problem.

Log on problem

Log on problem

Log off Users in shared drive - Access2000

LOG of TEMPDB are growing

LOG of TEMPDB are growing

LOG of TEMPDB are growing

Log of SQL

Log of SQL

Log of rows inserted/updated/deleted

Log of Executed SQL

Log of Executed SQL

Log of database changes

Log of database changes

Log of database changes

Log not needed

Log not auto-shrinking

Log not auto-shrinking

Log not auto-shrinking

Log MDX en AS 2000

Log logins

Log LastChangedDate with trigger

Log just keeps growing!

Log issues

Log issues

'Log' is not a member of 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel'.

Log is growing crazy when run DBCC INDEXDEFRAG or DBREINDEX

Locks and Blocks

Blog Archive

About Me