Dump Transaction Log

Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
C
BACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C
|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default...b;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default...kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.c om...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C

Dump Transaction Log

Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recovery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/default.aspx?scid=kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/default.aspx?scid=kb;EN-US;62866
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> Csql

Dump Transaction Log

Hello,
I'm getting the following error on 1 of our DB's;
The log file for database 'database' is full. Back up the transaction
log for the database to free up some log space.
How can I dump the transaction log?
Thanks,
CBACKUP LOG { database_name | @.database_name_var }
{
[ WITH
{ NO_LOG | TRUNCATE_ONLY } ]
}
example:
BACKUP LOG Northwind WITH TRUNCATE_ONLY
if you dont need your transaction logs backed up regularly (not worried
about data loss, etc) then I recommend placing the database in SIMPLE
Recovery Mode.
Greg Jackson
PDX, Oregon
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C|||backup log <databasename> to disk = 'filename'
Also check you recovery mode
select databasepropertyex('databasename','Recov
ery')
If you don't need point in time recovery, set the database to simple
recovery mode
alter database <databasename> set recovery simple
and just use full backups. If you require more upto date backups set up a
regular job to backup your transaction log (the easisest way is to use a
maintenance plan). You can also set the log file to autogrow to prevent this
issue assuming you have done one of the steps above. You should set your log
to a reasonable size so its not constally growing. Have a look at
INF: Shrinking the Transaction Log in
SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...b;en-us;Q272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/defaul...kb;EN-US;110139
INFO: Reasons Why SQL Transaction Log Is Not Being Truncated
http://support.microsoft.com/defaul...=kb;EN-US;62866
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Craig Alexander" <craig@.itas.net> wrote in message
news:2582929c.0407121143.16fa26b9@.posting.google.com...
> Hello,
> I'm getting the following error on 1 of our DB's;
> The log file for database 'database' is full. Back up the transaction
> log for the database to free up some log space.
>
> How can I dump the transaction log?
> Thanks,
> C

dump stack question

When causes a dump stack in the Error Log folder to occur? I have one from
yesterday but don't know what caused it. I see the code that was genrated,
and theres tons more info, but have no idea what the deal is. How do you
make sense of all the info?
TIA, ChrisRThe details in the stack dump are most often used by microsoft product
support. You can contact them or search the web to see if this dump has been
reported by other people. It might have been fixed in a patch already.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"ChrisR" <noemail@.bla.com> wrote in message
news:OGMUq#odFHA.1612@.tk2msftngp13.phx.gbl...
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>|||Hi
You may want to look at dumpchk see
http://support.microsoft.com/defaul...b;en-us;315263. Also look in
the event log to see what was happening around the time the file was created
.
John
"ChrisR" wrote:

> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
>sql

dump stack question

When causes a dump stack in the Error Log folder to occur? I have one from
yesterday but don't know what caused it. I see the code that was genrated,
and theres tons more info, but have no idea what the deal is. How do you
make sense of all the info?
TIA, ChrisR
The details in the stack dump are most often used by microsoft product
support. You can contact them or search the web to see if this dump has been
reported by other people. It might have been fixed in a patch already.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"ChrisR" <noemail@.bla.com> wrote in message
news:OGMUq#odFHA.1612@.tk2msftngp13.phx.gbl...
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
|||Hi
You may want to look at dumpchk see
http://support.microsoft.com/default...;en-us;315263. Also look in
the event log to see what was happening around the time the file was created.
John
"ChrisR" wrote:

> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
>

dump stack question

When causes a dump stack in the Error Log folder to occur? I have one from
yesterday but don't know what caused it. I see the code that was genrated,
and theres tons more info, but have no idea what the deal is. How do you
make sense of all the info?
TIA, ChrisRThe details in the stack dump are most often used by microsoft product
support. You can contact them or search the web to see if this dump has been
reported by other people. It might have been fixed in a patch already.
--
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"ChrisR" <noemail@.bla.com> wrote in message
news:OGMUq#odFHA.1612@.tk2msftngp13.phx.gbl...
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>|||Hi
You may want to look at dumpchk see
http://support.microsoft.com/default.aspx?scid=kb;en-us;315263. Also look in
the event log to see what was happening around the time the file was created.
John
"ChrisR" wrote:
> When causes a dump stack in the Error Log folder to occur? I have one from
> yesterday but don't know what caused it. I see the code that was genrated,
> and theres tons more info, but have no idea what the deal is. How do you
> make sense of all the info?
> TIA, ChrisR
>
>

Dump log and move log to another drive

Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:

> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>

Dump log and move log to another drive

Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
Will
Will Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:

> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>
|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>

Dump log and move log to another drive

Hi,
I am getting a message that my transaction log is full. After looking at
the drive where it is located, I only have 8 megs left on that drive which
is pretty bad! I would like to do two things. Dump the log and then move
it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
Thanks in advance,
WillWill Dupleich,
Backup the transaction log and use "alter database ... modify file ..." to
move the log to a new drive.
Troubleshooting a Full Transaction Log (Error 9002)
http://msdn2.microsoft.com/en-us/library/ms175495.aspx
AMB
"Will Dupleich" wrote:
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>
>|||Hello,
1. Do the Trasnaction log backup
2. Detach the database [SP_DETACH_DB DBname>
3. Copy/Move the LDF file to new disk/drive
4. Attach the database with new drive letter to LDF file [SP_ATTACH_DB
dbname,'MDF name with path','Ldf name with new path'
FYI, This steps require some downtime...
You can also do it ONLINE by creating a new LDF file in new drive using
ALTER DATABASE command or from SQL Server management studio.
Thanks
Hari
"Will Dupleich" <will@.getdynamic.com> wrote in message
news:Oghm22udHHA.4868@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I am getting a message that my transaction log is full. After looking at
> the drive where it is located, I only have 8 megs left on that drive which
> is pretty bad! I would like to do two things. Dump the log and then move
> it to another larger drive. I am using SQL 2005 on Windows 2000 Server.
> Thanks in advance,
> Will
>

Dumb Transaction log question.

I know I've been asking a lot of dumb questions here lately but - One must
learn somewhere.
In my test lab cluster that I'm building, W2k3 Cluster admin put each of my
physical disks in its own group. Now I'm keep one for the cluster group, and
one for the MSDTC, which leaves me four. I'm going to use 1 disk for data
and 1 disk for tranaction logs for each instance of sql. Are there any
advantages in putting the disks in the same group. (that is putting 1 disk
for the transaction logs and 1 disk for the data in the same group). I don't
see any advantages, but I'll ask anyway.
Also on transaction logs, where do I go to move the default location of
transaction logs in each instance of SQL.
The physical disk resources need to be in the same group as the SQL server resource that will use them, and the SQL server resource needs to have a dependancy on both physical disk resources.
As to the default locations, they are specified on the "Database Settings" tab of the Server Properties dialog in EM. (Not the "Edit registration properties" near the top of the right-click menu, but the "properties" near the bottom)
If you want to move database files or transaction logs, you can detach the database, move the files, and reattach the database files by specifying the new paths in the attach dialog box. Right-click on the database and select "all tasks >> detach" to detach the database. To attach the database, right click on the "databases" container and select "all tasks >> attach database".
You can also use T-SQL to call sp_detach and sp_attach, but the GUI works fine.
Just make sure that the SQL server resource has the physical disk resource(s) as a dependancy(ies) or you will not be able to attach the files.
Play around until you are comfortable with this stuff before you go live. It's quite geeky fun!
Good Luck
jg

Quote:

Originally posted by Wayne
I know I've been asking a lot of dumb questions here lately but - One must
learn somewhere.
In my test lab cluster that I'm building, W2k3 Cluster admin put each of my
physical disks in its own group. Now I'm keep one for the cluster group, and
one for the MSDTC, which leaves me four. I'm going to use 1 disk for data
and 1 disk for tranaction logs for each instance of sql. Are there any
advantages in putting the disks in the same group. (that is putting 1 disk
for the transaction logs and 1 disk for the data in the same group). I don't
see any advantages, but I'll ask anyway.
Also on transaction logs, where do I go to move the default location of
transaction logs in each instance of SQL.

|||There is no dumb question.
Its a good question. Since you are using one disk for data and another disk for log, both the disk resources will need to be in SAME SQL Server group (along with other SQL Server resources). Infact, if they are on
seperate group SQL Server cannot use it. During installation, we can just specify one disk. After installation, one has to move the second disk to the SQL group, take SQL Server resource offline, add the second disk
as a dependency for SQL Server resource and then take the SQL Server resource online. After that you can use your normal steps/techniques to move the logs on the second disk.
HTH,
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way to do this is to visit the following websites:
http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Dubious SQL Login?

I want to centralize server logs. All these server services (IIS, ISA, etc)
all know how to log to ODBC, and I want to exploit this so that I have a
central place to analyze logs instead of trapsing through the file system
looking for all these silly log files.
So I made a ServerLogs database, and I plan to have a flat table for each
log. There are of course no relationships here, just a set of flat tables.
These servers that will log through ODBC will need to be able to connect to
SQL Server so I have to make some logins. I would prefer to use Windows
Only authentication because that forces any connection to be authenticated
by Active Directory. But if I do this, then I would have to make Logins in
SQL Server for "Local System" and "Network Service".
Now these logins would only have access to the ServerLogs database, and in
there, would only have "public" permissions. No permissions other than that
which is required to log their log records.
My question is, in this scenario, does it sound like a bad idea to give
LocalSystem and NetworkService access to SQL Server? I just want a reality
check before I do something that might be dangerous or stupid.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSenseiThe service startup account implicates sqlserver's ability to access
resources. Thus, if you do not want sqlserver to access any resources
outside of the box it's running on, you would use localsystem. If it needs
to access network resources, networkservice is obviously the choice.
Now, as far as users' permissions, you would just need to grant the domain
user (or group) to sqlserver and any desired databased.
e.g.
exec sp_grantlogin 'domain\user' --'domain\group'
go
use db
go
exec sp_grantdbaccess 'domain\user' --'domain\group'
-oj
"Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message
news:Ox6%23epiOGHA.3896@.TK2MSFTNGP15.phx.gbl...
>I want to centralize server logs. All these server services (IIS, ISA,
>etc)
> all know how to log to ODBC, and I want to exploit this so that I have a
> central place to analyze logs instead of trapsing through the file system
> looking for all these silly log files.
> So I made a ServerLogs database, and I plan to have a flat table for each
> log. There are of course no relationships here, just a set of flat
> tables.
> These servers that will log through ODBC will need to be able to connect
> to
> SQL Server so I have to make some logins. I would prefer to use Windows
> Only authentication because that forces any connection to be authenticated
> by Active Directory. But if I do this, then I would have to make Logins
> in
> SQL Server for "Local System" and "Network Service".
> Now these logins would only have access to the ServerLogs database, and in
> there, would only have "public" permissions. No permissions other than
> that
> which is required to log their log records.
> My question is, in this scenario, does it sound like a bad idea to give
> LocalSystem and NetworkService access to SQL Server? I just want a
> reality
> check before I do something that might be dangerous or stupid.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||> The service startup account implicates sqlserver's ability to access
> resources. Thus, if you do not want sqlserver to access any resources
> outside of the box it's running on, you would use localsystem. If it needs
> to access network resources, networkservice is obviously the choice.
Perhaps you misunderstood, or I misstated:
I want these servers: HTTP, FTP, NNTP, SMTP, POP3, ISA
to be able to connect to SQL Server to write their logs into SQL Server
tables.
The IIS and ISA related services that want to do ODBC logging to SQL Server
all run as LocalSystem or NetworkService. Using Windows Only authentication
in SQL Server, I would have to create SQL Server logins for LocalSystem and
NetworkService so that these other services can ODBC their log entries to
SQL.
My question is whether it's a bad idea to allow LocalSystem and
NetworkService to have logins at SQL Server on behalf of these other
servers, because I know these two important accounts are used for a
MULTITUDE of other things.
In other words, I want to do this:
EXEC sp_GrantLogin 'Local System'
EXEC sp_GrantLogin 'NT AUTHORITY\NetworkService'
GO
CREATE DATABASE ServerLogs
GO
USE ServerLogs
GO
EXEC sp_GrantDBAccess 'Local System'
EXEC sp_GrantDBAccess 'NT AUTHORITY\NetworkService'
GO
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||Ah, I see.
Granting any NT acct will only give it a public role. Thus, there is really
no harm. Though, in this case, these system accts are used in many services.
So, granting them access will also allow all others from accessing.
But then again, localsystem already has *full* access to the local
machine...
-oj
"Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message
news:Ot91RFjOGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Perhaps you misunderstood, or I misstated:
> I want these servers: HTTP, FTP, NNTP, SMTP, POP3, ISA
> to be able to connect to SQL Server to write their logs into SQL Server
> tables.
> The IIS and ISA related services that want to do ODBC logging to SQL
> Server
> all run as LocalSystem or NetworkService. Using Windows Only
> authentication
> in SQL Server, I would have to create SQL Server logins for LocalSystem
> and
> NetworkService so that these other services can ODBC their log entries to
> SQL.
> My question is whether it's a bad idea to allow LocalSystem and
> NetworkService to have logins at SQL Server on behalf of these other
> servers, because I know these two important accounts are used for a
> MULTITUDE of other things.
> In other words, I want to do this:
> EXEC sp_GrantLogin 'Local System'
> EXEC sp_GrantLogin 'NT AUTHORITY\NetworkService'
> GO
> CREATE DATABASE ServerLogs
> GO
> USE ServerLogs
> GO
> EXEC sp_GrantDBAccess 'Local System'
> EXEC sp_GrantDBAccess 'NT AUTHORITY\NetworkService'
> GO
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||> Granting any NT acct will only give it a public role. Thus, there is
really
> no harm. Though, in this case, these system accts are used in many
services.
> So, granting them access will also allow all others from accessing.
Agreed. And the only thing SQL Server will let them have permissions for is
"public" access to Just the ServerLogs database

> But then again, localsystem already has *full* access to the local
> machine...
Ha. Yes, I forgot about that. I was just trying to get a reality check
before I accidentally unzipped my fly on the public internet or something
like that.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||>
> My question is, in this scenario, does it sound like a bad idea to give
> LocalSystem and NetworkService access to SQL Server? I just want a
> reality
> check before I do something that might be dangerous or stupid.
>
There's nothing wrong with giving software access to SQL Server, just make
sure that the security is set so that it can't do anything that it
shouldn't. You'll also need to make sure that it's not possible to inject
stuff that you shouldn't be able to.
As a different idea, maybe you should take a look at the SSIS (SQL Server
Integration Services) This is the all new DTS replacement. Using SSIS, you
can pull the information direct from the file system, perform some
transformation and insert the files into SQL. It can be setup on a schedule
so the entire process can be automated, and executed by SQL Server! No need
to give IIS and all those other nasties access to SQL at all.
Regards
Colin Dawson
www.cjdawson.com|||> There's nothing wrong with giving software access to SQL Server, just make
> sure that the security is set so that it can't do anything that it
> shouldn't. You'll also need to make sure that it's not possible to inject
> stuff that you shouldn't be able to.
Thank you, that's precisely what I was looking for.

> As a different idea, maybe you should take a look at the SSIS (SQL Server
> Integration Services) This is the all new DTS replacement. Using SSIS,
you
> can pull the information direct from the file system, perform some
> transformation and insert the files into SQL. It can be setup on a
schedule
> so the entire process can be automated, and executed by SQL Server! No
need
> to give IIS and all those other nasties access to SQL at all.
If you are referring to SQL 2005, I cannot do that because I am not up to
speed on it yet, but that does sound like it is more secure.
In any case, if I am reading you correctly, it sounds like I am OK as long
as LocalSystem and NetworkService have limited permissions.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||>
> If you are referring to SQL 2005, I cannot do that because I am not up to
> speed on it yet, but that does sound like it is more secure.
> In any case, if I am reading you correctly, it sounds like I am OK as long
> as LocalSystem and NetworkService have limited permissions.
>
You could do it with permissions. I wouldn't worry about not being up to
speed on SQL 2005, if you wait for that it'll be a year or so before you can
truly say that. (maybe longer!) The Integrations services are a replacement
for DTS, hence why I'm saying not to bother with it. But surely if security
is a major consideration, it's worth spending a few hours getting up to
speed on part of the SQL Server Business intelligence studio.
Specifically, it's the Integrations service project that you need to look
at.
In a nutshell you can split the task into several stages, such as read a
file, convert the format, perform some aggregations, split the data into
distinct subsets, write the data into a table. Keeping each step distinct
and seperate will allow the SSIS to split the taks up into many threads, so
you get a performance boost using multi threading, but SQL will do all the
work for you.
Regards
Colin Dawson
www.cjdawson.com

Dual Transaction Log

I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
Nick
Hi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=re...t .com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>

Dual Transaction Log

I have just taken over an SQL Server 7.0 database which has two transaction
log files. One is on the system drive, and the other is on the same drive as
the database..
The transaction log on the system drive is huge, leaving very little free
space, whilst the database drive has tonnes of free space..
How do I remove the log file on the system drive, and just use the second
one?
Thnak you
NickHi,
Best way is :-
1. Detach the database using sp_detach_db
2. copy the LDF file in system drive to new location
3. Attach the database using SP_ATTACH_DB, specify the new LDF location.
One more method to remove ldf , is backup the database and TX log , shrink
the LDF file using DBCC SHRINKFILE and REMOVE the FILE using Alter database
see the below link for details
http://groups.google.com/groups?q=remove+log+file+in+sql+7&hl=en&lr=&ie=UTF-8&selm=us%24RwvUb%24GA.223%40cppssbbsa02.microsoft.com&rnum=5
Thanks
Hari
MCDBA
"nh" <anonymous@.discussions.microsoft.com> wrote in message
news:egnAeCymEHA.2372@.TK2MSFTNGP10.phx.gbl...
>I have just taken over an SQL Server 7.0 database which has two transaction
> log files. One is on the system drive, and the other is on the same drive
> as
> the database..
> The transaction log on the system drive is huge, leaving very little free
> space, whilst the database drive has tonnes of free space..
> How do I remove the log file on the system drive, and just use the second
> one?
> Thnak you
> Nick
>

Dual Quad Core - CPU settings?

I have a IBM Dual Quad Core CPU system running SQL 2000 and all
SP/Updates. I'm seeing a log of blocking because of a vendors app that I
have no control over, causes the CPU Cores to run around 50% during the
extended periods of blocking. System is using 3.2GB of the 4GB (Windows
2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
0 (actually, it was set to use all cores, but it has not made any
performance difference by not allowing access to Core 0 - this is a
Domain Controller and normally shows 3% load across all cores).
Should I limit the access to Cores to just 4-7 (second CPU) or just Core
0/4 and not the others, or should I just allow all cores?
Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
using AWE or PAE modes? I'm currently setup for the default startup
mode.
Thanks - sorry for the ramble, been a long night.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)You really seem to have underpowered the system when it comes to memory
compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and the
server only has 4GB total anyway. The server is obviously being used for
more than SQL if 3.2GB is being used. If you want more memory you need to
move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
don't recall what Win2003 Std supports but I believe it is only 4GB so you
should consider upgrading that as well.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.21768b1c15ad1052989a7d@.adfree.Usenet.com...
>I have a IBM Dual Quad Core CPU system running SQL 2000 and all
> SP/Updates. I'm seeing a log of blocking because of a vendors app that I
> have no control over, causes the CPU Cores to run around 50% during the
> extended periods of blocking. System is using 3.2GB of the 4GB (Windows
> 2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
> 0 (actually, it was set to use all cores, but it has not made any
> performance difference by not allowing access to Core 0 - this is a
> Domain Controller and normally shows 3% load across all cores).
> Should I limit the access to Cores to just 4-7 (second CPU) or just Core
> 0/4 and not the others, or should I just allow all cores?
> Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
> using AWE or PAE modes? I'm currently setup for the default startup
> mode.
> Thanks - sorry for the ramble, been a long night.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <OH#AjM1CIHA.1168@.TK2MSFTNGP02.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> You really seem to have underpowered the system when it comes to memory
> compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and the
> server only has 4GB total anyway. The server is obviously being used for
> more than SQL if 3.2GB is being used. If you want more memory you need to
> move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
> don't recall what Win2003 Std supports but I believe it is only 4GB so you
> should consider upgrading that as well.
Yea, I'm stuck - the database app vendor suggested a single CPU, and we
moved from a Dual Xeon 2Ghz machine to this Dual Quad Core system and
believed it would help.
I bought Sql 2005 and CPU licenses and then downgraded to SQL 2000
because the app didn't convert to 2005 (tried to do a restore and it
didn't go well - schema security screwed it).
So, I'm stuck with bad stored proc's and block each other, which is
where I'm sure the real issue lies, but I don't have rights/permission
to change the vendors code.
In the old days there was speculation that Hyper Threading cause the
system to run slower, but, in testing I was seeing about 30% increase
using HT enabled under Xeon CPU's on a quality server. So, I've got Dual
Quad Core CPU's now, and I don't have the option to play - so I was
wondering if I should be only using some cores or just the second CPU's
cores (instead of all 0-7 cores, use cores 4-7 so that I'm hitting just
the one CPU)...
What about AWE/PAE modes on Win 2003 Std with SQL 2000 Std?
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||You should not have an issue with allowing SQL Server to use all the
available cores. But make sure to set the MAXDOP to something less than max.
I would start with 4 or maybe even 2. When you upgraded to 2005 did you try
the db in 80 compatibility mode? That can make a difference in app
compatibility or not. PAE is ignored on Win2003 Std and SQL2000 Std will
only ever use 2GB regardless of the OS. SQL2005 will use all that the OS can
offer.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176da90b52cdb30989a7f@.adfree.Usenet.com...
> In article <OH#AjM1CIHA.1168@.TK2MSFTNGP02.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> You really seem to have underpowered the system when it comes to memory
>> compared to CPU's. SQL 2000 Std edition can only use 2GB of memory and
>> the
>> server only has 4GB total anyway. The server is obviously being used for
>> more than SQL if 3.2GB is being used. If you want more memory you need to
>> move to either Enterprise Edition of SQL Server or upgrade to 2005 Std. I
>> don't recall what Win2003 Std supports but I believe it is only 4GB so
>> you
>> should consider upgrading that as well.
> Yea, I'm stuck - the database app vendor suggested a single CPU, and we
> moved from a Dual Xeon 2Ghz machine to this Dual Quad Core system and
> believed it would help.
> I bought Sql 2005 and CPU licenses and then downgraded to SQL 2000
> because the app didn't convert to 2005 (tried to do a restore and it
> didn't go well - schema security screwed it).
> So, I'm stuck with bad stored proc's and block each other, which is
> where I'm sure the real issue lies, but I don't have rights/permission
> to change the vendors code.
> In the old days there was speculation that Hyper Threading cause the
> system to run slower, but, in testing I was seeing about 30% increase
> using HT enabled under Xeon CPU's on a quality server. So, I've got Dual
> Quad Core CPU's now, and I don't have the option to play - so I was
> wondering if I should be only using some cores or just the second CPU's
> cores (instead of all 0-7 cores, use cores 4-7 so that I'm hitting just
> the one CPU)...
> What about AWE/PAE modes on Win 2003 Std with SQL 2000 Std?
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <egVTKn2CIHA.1208@.TK2MSFTNGP05.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> You should not have an issue with allowing SQL Server to use all the
> available cores. But make sure to set the MAXDOP to something less than max.
> I would start with 4 or maybe even 2.
I hate to sound ignorant, but MAXDOP?
> When you upgraded to 2005 did you try
> the db in 80 compatibility mode? That can make a difference in app
> compatibility or not.
Yes, all DB were in 80 mode, but, the dang schema permissions cause
massive problems. The same Vendors app installed native on 2005 didn't
have a problem and I've done a few of them that work fine, but the ones
we've moved from 2000 to 2005 all have schema permission problems.
> PAE is ignored on Win2003 Std and SQL2000 Std will
> only ever use 2GB regardless of the OS. SQL2005 will use all that the OS can
> offer.
Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
try limiting it to 1.4GB to free up memory for the server itself, since
it's a AD controller. I can't believe that it needs more than 1GB for
the store when all of the blocking is based on a single sproc that is
being called 600+ times by 4 users, it's an update of some type, but I
have not looked directly at their code, just the blocking showing all
the time.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||As a note, I've set the Max Query Plan option to 3 from 5 and set max
memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
users are not reporting any loss of application performance - they are
not reporting any increase yet.
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
for more details. I don't know what schema permission issues you had but I
am willing to bet they were minor and had to do with the fact the logins,
users and schemas has mismatches. If it works from scratch it should work
with an upgrade. Have a look at these to see if they help:
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
http://support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
When a Database Is Moved Between SQL Servers
> Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> try limiting it to 1.4GB to free up memory for the server itself, since
> it's a AD controller.
You have at least 4GB right? SQL will only use 2GB max so why would you
want to limit it even more?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176ed223bb957aa989a83@.adfree.Usenet.com...
> In article <egVTKn2CIHA.1208@.TK2MSFTNGP05.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> You should not have an issue with allowing SQL Server to use all the
>> available cores. But make sure to set the MAXDOP to something less than
>> max.
>> I would start with 4 or maybe even 2.
> I hate to sound ignorant, but MAXDOP?
>> When you upgraded to 2005 did you try
>> the db in 80 compatibility mode? That can make a difference in app
>> compatibility or not.
> Yes, all DB were in 80 mode, but, the dang schema permissions cause
> massive problems. The same Vendors app installed native on 2005 didn't
> have a problem and I've done a few of them that work fine, but the ones
> we've moved from 2000 to 2005 all have schema permission problems.
>> PAE is ignored on Win2003 Std and SQL2000 Std will
>> only ever use 2GB regardless of the OS. SQL2005 will use all that the OS
>> can
>> offer.
> Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> try limiting it to 1.4GB to free up memory for the server itself, since
> it's a AD controller. I can't believe that it needs more than 1GB for
> the store when all of the blocking is based on a single sproc that is
> being called 600+ times by 4 users, it's an update of some type, but I
> have not looked directly at their code, just the blocking showing all
> the time.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <#M0PNZ3CIHA.4584@.TK2MSFTNGP03.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
> for more details. I don't know what schema permission issues you had but I
> am willing to bet they were minor and had to do with the fact the logins,
> users and schemas has mismatches. If it works from scratch it should work
> with an upgrade. Have a look at these to see if they help:
> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
> Moving Users
> http://support.microsoft.com/?id=246133 How To Transfer Logins and
> Passwords Between SQL Servers
> http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
> Restore
> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to users
> http://support.microsoft.com/?id=168001 User Logon and/or Permission
> Errors After Restoring Dump
> http://support.microsoft.com/?id=240872 How to Resolve Permission Issues
> When a Database Is Moved Between SQL Servers
> > Yea, that's what I thought, SQL Store is using 1.75GB currently. I could
> > try limiting it to 1.4GB to free up memory for the server itself, since
> > it's a AD controller.
> You have at least 4GB right? SQL will only use 2GB max so why would you
> want to limit it even more?
Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
seen a Windows Std server use more than 3.3GB on anything.
I was worried about the 2GB limit for apps and wondered if allowing it
to run (auto) at 1.78GB left enough for the OS and server functions -
that's why I limited it to 1.4GB - this was a shotgun move (sorry, I
know it's not professional, but sometimes you have to just try
something).
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||> Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
> RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
> seen a Windows Std server use more than 3.3GB on anything.
Some motherboards or chipsets will not use all 4GB as you would expect. They
simply can't address 4GB or they will hide some of the memory to be used for
specific memory calls such as onboard video etc. I suspect that is what you
are seeing here and in reality you are using all the available memory. You
are most likely paging to disk a lot as well. I would find out what else
other than SQL Server is using the memory and see if you can address that.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176fd277dd6df39989a88@.adfree.Usenet.com...
> In article <#M0PNZ3CIHA.4584@.TK2MSFTNGP03.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> MAXDOP stands for MAX Degree of Parallelism. Please refer to BooksOnLine
>> for more details. I don't know what schema permission issues you had but
>> I
>> am willing to bet they were minor and had to do with the fact the logins,
>> users and schemas has mismatches. If it works from scratch it should work
>> with an upgrade. Have a look at these to see if they help:
>> http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
>> Moving Users
>> http://support.microsoft.com/?id=246133 How To Transfer Logins and
>> Passwords Between SQL Servers
>> http://support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
>> Restore
>> http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
>> users
>> http://support.microsoft.com/?id=168001 User Logon and/or Permission
>> Errors After Restoring Dump
>> http://support.microsoft.com/?id=240872 How to Resolve Permission
>> Issues
>> When a Database Is Moved Between SQL Servers
>> > Yea, that's what I thought, SQL Store is using 1.75GB currently. I
>> > could
>> > try limiting it to 1.4GB to free up memory for the server itself, since
>> > it's a AD controller.
>> You have at least 4GB right? SQL will only use 2GB max so why would you
>> want to limit it even more?
> Yes, IBM 3800 series with 4GB RAM and 4 additional used as fail-over
> RAM. I've never seen the RAM in use exceed 3.3GB, in fact I've never
> seen a Windows Std server use more than 3.3GB on anything.
> I was worried about the 2GB limit for apps and wondered if allowing it
> to run (auto) at 1.78GB left enough for the OS and server functions -
> that's why I limited it to 1.4GB - this was a shotgun move (sorry, I
> know it's not professional, but sometimes you have to just try
> something).
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <MPG.21768b1c15ad1052989a7d@.adfree.Usenet.com>,
void@.nowhere.lan says...
> I have a IBM Dual Quad Core CPU system running SQL 2000 and all
> SP/Updates. I'm seeing a log of blocking because of a vendors app that I
> have no control over, causes the CPU Cores to run around 50% during the
> extended periods of blocking. System is using 3.2GB of the 4GB (Windows
> 2003 Std, SQL 2000 Std CPU Licenses) and SQL is set to not use CPU Core
> 0 (actually, it was set to use all cores, but it has not made any
> performance difference by not allowing access to Core 0 - this is a
> Domain Controller and normally shows 3% load across all cores).
> Should I limit the access to Cores to just 4-7 (second CPU) or just Core
> 0/4 and not the others, or should I just allow all cores?
> Also, with Win 2003 Std and SQL 2000 Std (CPU licenses), should I be
> using AWE or PAE modes? I'm currently setup for the default startup
> mode.
> Thanks - sorry for the ramble, been a long night.
Well, it's been a full day and I've found that changing the setting from
5 to 3 made all the difference in the world. So, a crappy coded Proc
(that I have no control over), caused massive blocking and now, it's
fine again.
I should point out that a typical back of this server took 6+ hours
normally, now it takes under 3 hours. Users have found a little
improvement in performance (DB App), but the server is no longer showing
a load and all functions are responsive.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Something doesn't connect. Typically, if you see massive blocking, your CPU
consunption should go down because these processes are being blocked and are
not doing anything. And I'd expect to see CPU usage go up when you have
resolved blocking.
Linchi
"Leythos" wrote:
> As a note, I've set the Max Query Plan option to 3 from 5 and set max
> memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
> users are not reporting any loss of application performance - they are
> not reporting any increase yet.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)
>|||In article <E7A06FE9-A2D7-433F-9DC4-9AF34EA25F2B@.microsoft.com>,
LinchiShea@.discussions.microsoft.com says...
> Something doesn't connect. Typically, if you see massive blocking, your CPU
> consunption should go down because these processes are being blocked and are
> not doing anything. And I'd expect to see CPU usage go up when you have
> resolved blocking.
The same 600+ updates now don't consume CPU time and don't cause
blocking that I can see (at least not during production periods where it
was causing blocking).
So, it's just looking good now.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||What setting are you referring to that you changed from 5 to 3? As I have
said all along I think you have issues with MAXDOP and need to reduce the
parallelism.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2176f66415ad1f61989a85@.adfree.Usenet.com...
> As a note, I've set the Max Query Plan option to 3 from 5 and set max
> memory from FULL to 1.4GB and CPU load has gone from 50% to 17% and
> users are not reporting any loss of application performance - they are
> not reporting any increase yet.
>
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <ObHjn3NDIHA.4360@.TK2MSFTNGP06.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> What setting are you referring to that you changed from 5 to 3? As I have
> said all along I think you have issues with MAXDOP and need to reduce the
> parallelism.
Yea, this is SQL 2000, and the max query plan option was set for 5,
changing it to 3 cleaned up the lack of proper coding from the vendors
app.
Thanks.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||I don't have a 2000 instance to look at right now but I don't remember a
setting called that. Are you referring to the parallel query plan cost
option? If so then again I suggest you change the MAXDOP instead.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.2179ea0474b0e377989ac3@.adfree.Usenet.com...
> In article <ObHjn3NDIHA.4360@.TK2MSFTNGP06.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> What setting are you referring to that you changed from 5 to 3? As I
>> have
>> said all along I think you have issues with MAXDOP and need to reduce the
>> parallelism.
> Yea, this is SQL 2000, and the max query plan option was set for 5,
> changing it to 3 cleaned up the lack of proper coding from the vendors
> app.
> Thanks.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <#nSn#ZbDIHA.5980@.TK2MSFTNGP04.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> I don't have a 2000 instance to look at right now but I don't remember a
> setting called that. Are you referring to the parallel query plan cost
> option? If so then again I suggest you change the MAXDOP instead.
I've looked all around and only see MAXDOP in SQL 2005 and as a query
hint when the option is specified.
I do not have permission to edit any of the vendors code, so I've used
the Memory/CPU settings tabs to adjust what can be run in parallel.
If you have a setting, in SQL 2000, that applies to ALL proc's without
having to edit any of the code, just changing SQL settings, then I would
love to see it - thanks.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
I don't have an instance of 2000 so I am going from memory here but if you
right click on the server in EM and choose properties there should be a
Processor tab. On that tab there should be a dropdown box (I believe) to
choose the maximum number of processors that can be used in parallel. It
will be set to 0 by default. You can also change or see this in
sp_configure. It is the "max degree of parallelism" option. You can see more
details in BOL under max degree of parallelism. If you set it here it
affects everything on the server. You can also specify a hint at a statement
level but that is not what you want.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Leythos" <void@.nowhere.lan> wrote in message
news:MPG.217ac0fd1c33cfbc989ad6@.adfree.Usenet.com...
> In article <#nSn#ZbDIHA.5980@.TK2MSFTNGP04.phx.gbl>,
> sqlmvpnooospam@.shadhawk.com says...
>> I don't have a 2000 instance to look at right now but I don't remember a
>> setting called that. Are you referring to the parallel query plan cost
>> option? If so then again I suggest you change the MAXDOP instead.
> I've looked all around and only see MAXDOP in SQL 2005 and as a query
> hint when the option is specified.
> I do not have permission to edit any of the vendors code, so I've used
> the Memory/CPU settings tabs to adjust what can be run in parallel.
> If you have a setting, in SQL 2000, that applies to ALL proc's without
> having to edit any of the code, just changing SQL settings, then I would
> love to see it - thanks.
> --
> Leythos
> - Igitur qui desiderat pacem, praeparet bellum.
> - Calling an illegal alien an "undocumented worker" is like calling a
> drug dealer an "unlicensed pharmacist"
> spam999free@.rrohio.com (remove 999 for proper email address)|||In article <OxdtLgcDIHA.4752@.TK2MSFTNGP04.phx.gbl>,
sqlmvpnooospam@.shadhawk.com says...
> MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
> I don't have an instance of 2000 so I am going from memory here but if you
> right click on the server in EM and choose properties there should be a
> Processor tab. On that tab there should be a dropdown box (I believe) to
> choose the maximum number of processors that can be used in parallel. It
> will be set to 0 by default. You can also change or see this in
> sp_configure. It is the "max degree of parallelism" option. You can see more
> details in BOL under max degree of parallelism. If you set it here it
> affects everything on the server. You can also specify a hint at a statement
> level but that is not what you want.
Thanks for your time - somehow I seem to have missed this when looking
in those areas. I will check again.
--
Leythos
- Igitur qui desiderat pacem, praeparet bellum.
- Calling an illegal alien an "undocumented worker" is like calling a
drug dealer an "unlicensed pharmacist"
spam999free@.rrohio.com (remove 999 for proper email address)|||Just a small point, the hint is MAXDOP but the configuration setting
is called "max degree of parallelism". If type that into the SQL
Server 2000 Books On Line index you will find more information.
Roy Harvey
Beacon Falls, CT
On Mon, 15 Oct 2007 07:21:49 -0400, Leythos <void@.nowhere.lan> wrote:
>In article <OxdtLgcDIHA.4752@.TK2MSFTNGP04.phx.gbl>,
>sqlmvpnooospam@.shadhawk.com says...
>> MAXDOP is not a SQL2005 specific setting. It is there in 2000 as well. Again
>> I don't have an instance of 2000 so I am going from memory here but if you
>> right click on the server in EM and choose properties there should be a
>> Processor tab. On that tab there should be a dropdown box (I believe) to
>> choose the maximum number of processors that can be used in parallel. It
>> will be set to 0 by default. You can also change or see this in
>> sp_configure. It is the "max degree of parallelism" option. You can see more
>> details in BOL under max degree of parallelism. If you set it here it
>> affects everything on the server. You can also specify a hint at a statement
>> level but that is not what you want.
>Thanks for your time - somehow I seem to have missed this when looking
>in those areas. I will check again.

Dual Log Shipping Restore Jobs

I have 2 SQL server 2000 servers, Server A, and Server B.
Server A is production. It has a log shipping restore job that updates the
database every half hour throughout the day automatically.
Server B is a reporting/test server, that has its log shipped restored
manually every 6 hours.
There is a job on Server A, that copies the transaction logs to a folder on
Server B every half hour.
We are manually restoring the transaction logs for Server B, that are copied
into this folder every 6 hours. So yeah, we have 6 hours worth of half hour t-
logs to restore every 6 hours.
I need to create a job, on server A, that will automatically restore the t-
log every 6 hours on Server B , AND keep its current half-hour log shipping
plan so we won't have to keep manually restoring the t-logs on Server B every
day?
So server A will continue to restore every half hour, and server b will be
restored with a big bulldog t-log file for the last 6 hours.
I hope that ws made clear.
Any insight would be wonderful, thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Just a thought, have you considered replication every 30 mintues or so
instead of immediate updating? (I assume you aren't doing this with
immediate updating due to the load or something like that).
Shawn Mason
Shawn Mason
Customer Support
http://www.visualonline.net
"Frank N via droptable.com" <u10790@.uwe> wrote in message
news:5687be0f857c0@.uwe...
>I have 2 SQL server 2000 servers, Server A, and Server B.
> Server A is production. It has a log shipping restore job that updates the
> database every half hour throughout the day automatically.
> Server B is a reporting/test server, that has its log shipped restored
> manually every 6 hours.
> There is a job on Server A, that copies the transaction logs to a folder
> on
> Server B every half hour.
> We are manually restoring the transaction logs for Server B, that are
> copied
> into this folder every 6 hours. So yeah, we have 6 hours worth of half
> hour t-
> logs to restore every 6 hours.
> I need to create a job, on server A, that will automatically restore the
> t-
> log every 6 hours on Server B , AND keep its current half-hour log
> shipping
> plan so we won't have to keep manually restoring the t-logs on Server B
> every
> day?
> So server A will continue to restore every half hour, and server b will be
> restored with a big bulldog t-log file for the last 6 hours.
> I hope that ws made clear.
> Any insight would be wonderful, thanks in advance.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1

Dual Log Shipping Restore Jobs

I have 2 SQL server 2000 servers, Server A, and Server B.
Server A is production. It has a log shipping restore job that updates the
database every half hour throughout the day automatically.
Server B is a reporting/test server, that has its log shipped restored
manually every 6 hours.
There is a job on Server A, that copies the transaction logs to a folder on
Server B every half hour.
We are manually restoring the transaction logs for Server B, that are copie
d
into this folder every 6 hours. So yeah, we have 6 hours worth of half hour
t-
logs to restore every 6 hours.
I need to create a job, on server A, that will automatically restore the t-
log every 6 hours on Server B , AND keep its current half-hour log shipping
plan so we won't have to keep manually restoring the t-logs on Server B ever
y
day?
So server A will continue to restore every half hour, and server b will be
restored with a big bulldog t-log file for the last 6 hours.
I hope that ws made clear.
Any insight would be wonderful, thanks in advance.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...server/200510/1Just a thought, have you considered replication every 30 mintues or so
instead of immediate updating? (I assume you aren't doing this with
immediate updating due to the load or something like that).
Shawn Mason
Shawn Mason
Customer Support
http://www.visualonline.net
"Frank N via droptable.com" <u10790@.uwe> wrote in message
news:5687be0f857c0@.uwe...
>I have 2 SQL server 2000 servers, Server A, and Server B.
> Server A is production. It has a log shipping restore job that updates the
> database every half hour throughout the day automatically.
> Server B is a reporting/test server, that has its log shipped restored
> manually every 6 hours.
> There is a job on Server A, that copies the transaction logs to a folder
> on
> Server B every half hour.
> We are manually restoring the transaction logs for Server B, that are
> copied
> into this folder every 6 hours. So yeah, we have 6 hours worth of half
> hour t-
> logs to restore every 6 hours.
> I need to create a job, on server A, that will automatically restore the
> t-
> log every 6 hours on Server B , AND keep its current half-hour log
> shipping
> plan so we won't have to keep manually restoring the t-logs on Server B
> every
> day?
> So server A will continue to restore every half hour, and server b will be
> restored with a big bulldog t-log file for the last 6 hours.
> I hope that ws made clear.
> Any insight would be wonderful, thanks in advance.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1

Dual Log Shipping Restore Jobs

I have 2 SQL server 2000 servers, Server A, and Server B.
Server A is production. It has a log shipping restore job that updates the
database every half hour throughout the day automatically.
Server B is a reporting/test server, that has its log shipped restored
manually every 6 hours.
There is a job on Server A, that copies the transaction logs to a folder on
Server B every half hour.
We are manually restoring the transaction logs for Server B, that are copied
into this folder every 6 hours. So yeah, we have 6 hours worth of half hour t-
logs to restore every 6 hours.
I need to create a job, on server A, that will automatically restore the t-
log every 6 hours on Server B , AND keep its current half-hour log shipping
plan so we won't have to keep manually restoring the t-logs on Server B every
day?
So server A will continue to restore every half hour, and server b will be
restored with a big bulldog t-log file for the last 6 hours.
I hope that ws made clear.
Any insight would be wonderful, thanks in advance.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Just a thought, have you considered replication every 30 mintues or so
instead of immediate updating? (I assume you aren't doing this with
immediate updating due to the load or something like that).
Shawn Mason
--
Shawn Mason
Customer Support
http://www.visualonline.net
"Frank N via SQLMonster.com" <u10790@.uwe> wrote in message
news:5687be0f857c0@.uwe...
>I have 2 SQL server 2000 servers, Server A, and Server B.
> Server A is production. It has a log shipping restore job that updates the
> database every half hour throughout the day automatically.
> Server B is a reporting/test server, that has its log shipped restored
> manually every 6 hours.
> There is a job on Server A, that copies the transaction logs to a folder
> on
> Server B every half hour.
> We are manually restoring the transaction logs for Server B, that are
> copied
> into this folder every 6 hours. So yeah, we have 6 hours worth of half
> hour t-
> logs to restore every 6 hours.
> I need to create a job, on server A, that will automatically restore the
> t-
> log every 6 hours on Server B , AND keep its current half-hour log
> shipping
> plan so we won't have to keep manually restoring the t-logs on Server B
> every
> day?
> So server A will continue to restore every half hour, and server b will be
> restored with a big bulldog t-log file for the last 6 hours.
> I hope that ws made clear.
> Any insight would be wonderful, thanks in advance.
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1

DTSTransformationSTATus constants !

Hi
I am building a DTS package, to read a IIS Log files into SQL-Server database file through a DTS package.

It is runing smooth, but sometimes the Log file include some CR LF, unexpected text,...etc so the DTS package can't read the fields perfectly and it fails, and I recieve an error.

I am up to use ActiveX Script in VBScript language to have more control over the flow of the transformation of the data operation.

Now, how can I use the DTSTransformationSTATUS constants to make my loops and check the different source fields?

I will appreciate an examples that control the folow of the Transformation of a TEXT file.

Thank you for your help in advance.

KhalidHow big are the log files ? What are the patterns to these hiccups ? Have you thought about writing a small app(vb,perl) that corrects the file first then use dts to import the file - you could actually make this a part of your dts package (calling the app) ?|||The log files are 50 MB to 60 MB in size.

About writing an Application to correct the log file, this is exactuly what I am up to, and since DTS is providing a powerful tools through VBScript or VBJScript to have more control over the Transformation process, I have decided to use these tools.

There are some constants such as DTSTransformationSTATUS_Error, DTSTransformationSTATUS_ErrorInfo,.. etc . These constants can be used to control the flow of the Transformation process and to handle the errors if any.

I need an example using these constants to make things more clear for me to build my own application.

Do you have any idea about these constants?

Thanks

DTSExec and Logging to Windows Event Log

I am trying to use either DTExec or DTExecUI to run a package while logging to the Windows Event log. This purports to be supported based on books online.

I have tried passing the CLSID and the ProgId on the command line and always get a message about the logger parameter being invalid.

I am using a command line like this:

dtexec /FILE "C:\Etl.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /LOGGER "DTS.LogProviderEventLog.1"; /SET "\Package.DestinationDatabase";"Test15-1Warehouse" /SET "\Package.SourceDatabase";ATEST15D1

Message I get is:

Argument "DTS.LogProviderEventLog.1;" for option "logger" is not valid.

Same message with the CLSID.

Help!

Normally the Logger switch expects a 2nd configstring argument within the quotes. The Event Log doesn't have anything to configure, but it seems to want the semicolon that would normally precede the 2nd argument.

Instead of /LOGGER "DTS.LogProviderEventLog.1",
please try /LOGGER "DTS.LogProviderEventLog.1;".|||Actually the semicolon is already specified (just outside the quotes instead of inside which should work (if not it is a bug). The problem is that the config string is not optional so the argument is missing the config string as far as DTExec is concerned. Try putting any character there and it should work as the event log provider ignores the config string.

/LOGGER "DTS.LogProviderEventLog.1;x".

thanks,|||Thanks.

Kind of stupid but that was it.

Dts.log in script task

Hi

Can someone tell me where I can find the log file created by the Dts.log method in script task.

I have created a log provider file as Mylog.xml, but the messages recorded are from the Dts.Event.FireInformation method and not the Dts.log method.

I don't know where the messages are filed.

Regards

Baldev

To log the output of Dts.Log() to a Log Provider, go to the "Configure SSIS Logs" dialog (e.g. SSIS/Logging...). Change the Logging Mode on the Script Task from the default of "UseParentSetting" to "Enabled". That is , click on the check box next to the Script task until it is checked ( LoggingMode = "Enabled") and not checked and greyed out (LoggingMode = "UseParentSetting") or unchecked (LoggingMode = "Disabled").

With the Script Task node selected, navigate to the Details tab, and select the "ScriptTaskLogEntry" event. Dts.Log() calls will now be sent to whatever log providers are enabled for the Script Task itself. Also, make sure to select the log provider for the script task "again", since this is effectively overriding the parent containers logging settings.

|||

Great, that's exactly what I wanted.

Thanks a lot

Baldev