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?

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.sql

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

Log is full?

What would be the best way to have the database run the following command when the logfile reaches a certain size?
BACKUP LOG kingjohnor WITH TRUNCATE_ONLY
GO
USE kingjohnor
DBCC SHRINKFILE(kingjohnor_log,1)
Thanks in advance for help : )If you are not going to back uo the transaction log file, then why not change your recovery mode to simple? This way the trans log will not grow at all - it will only hold uncommitted transactions.

Log File Shrink

BigSam wrote:
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive. I'
ve
> tried to shrink the file & it says it is successful, but I cannot shrink i
t
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam
I had a heap of trouble with the same problem one day. When I found
the solution, I saved the steps. Here they are:
1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
2) Backup Log <dbname> With Truncate_Only
3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
That should fix it.
MikeI'm unable to shrink my log file. The databse is about 2 GB & the log file
has grown to 11.5 GB. I've run transaction backups every 3 hours, but the lo
g
won't shrink. I need to reduce the size, to free up space on the drive. I've
tried to shrink the file & it says it is successful, but I cannot shrink it
enough. I've tried changing the recovery model to Simple & then shrinking th
e
log, but this doesn't work either.
Any suggestions will be appreciated.
BigSam|||BigSam wrote:
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive. I'
ve
> tried to shrink the file & it says it is successful, but I cannot shrink i
t
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam
I had a heap of trouble with the same problem one day. When I found
the solution, I saved the steps. Here they are:
1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
2) Backup Log <dbname> With Truncate_Only
3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
That should fix it.
Mike|||Sam,
If what Mike wrote doesn't help (and only as a last resort):
First, iiii Be sure to take a full backup of the database!!!!
Then, change the recovery mode to simple.
Next issue the syntax CHECKPOINT in Query Analyzer on that server then
issue DBCC Shrinkfile (<logfilename>, TruncateOnly).
Last, set your recovery mode back to Full.
SQLPoet
MikeR wrote:
> BigSam wrote:
> --
> I had a heap of trouble with the same problem one day. When I found
> the solution, I saved the steps. Here they are:
> 1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> 2) Backup Log <dbname> With Truncate_Only
> 3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> That should fix it.
> Mike|||Read about backup and especially recovery model in Books Online, Them this w
ill probably help:
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/
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive. I'
ve
> tried to shrink the file & it says it is successful, but I cannot shrink i
t
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam|||Sam,
If what Mike wrote doesn't help (and only as a last resort):
First, iiii Be sure to take a full backup of the database!!!!
Then, change the recovery mode to simple.
Next issue the syntax CHECKPOINT in Query Analyzer on that server then
issue DBCC Shrinkfile (<logfilename>, TruncateOnly).
Last, set your recovery mode back to Full.
SQLPoet
MikeR wrote:
> BigSam wrote:
> --
> I had a heap of trouble with the same problem one day. When I found
> the solution, I saved the steps. Here they are:
> 1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> 2) Backup Log <dbname> With Truncate_Only
> 3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> That should fix it.
> Mike|||Read about backup and especially recovery model in Books Online, Them this w
ill probably help:
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/
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive. I'
ve
> tried to shrink the file & it says it is successful, but I cannot shrink i
t
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive.
I've
> tried to shrink the file & it says it is successful, but I cannot shrink
it
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
Do a DBCC OPENTRAN on the database and make sure there are no open
transactions.
But it'snot clear to me if youmean the log is 11.5 gb and FULL or 11.5GB on
disk, but only partially full.

> BigSamsql

Log File Shrink

I'm unable to shrink my log file. The databse is about 2 GB & the log file
has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
won't shrink. I need to reduce the size, to free up space on the drive. I've
tried to shrink the file & it says it is successful, but I cannot shrink it
enough. I've tried changing the recovery model to Simple & then shrinking the
log, but this doesn't work either.
Any suggestions will be appreciated.
BigSamBigSam wrote:
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> won't shrink. I need to reduce the size, to free up space on the drive. I've
> tried to shrink the file & it says it is successful, but I cannot shrink it
> enough. I've tried changing the recovery model to Simple & then shrinking the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam
--
I had a heap of trouble with the same problem one day. When I found
the solution, I saved the steps. Here they are:
1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
2) Backup Log <dbname> With Truncate_Only
3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
That should fix it.
Mike|||Sam,
If what Mike wrote doesn't help (and only as a last resort):
First, iiii Be sure to take a full backup of the database!!!!
Then, change the recovery mode to simple.
Next issue the syntax CHECKPOINT in Query Analyzer on that server then
issue DBCC Shrinkfile (<logfilename>, TruncateOnly).
Last, set your recovery mode back to Full.
SQLPoet
MikeR wrote:
> BigSam wrote:
> > I'm unable to shrink my log file. The databse is about 2 GB & the log file
> > has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> > won't shrink. I need to reduce the size, to free up space on the drive. I've
> > tried to shrink the file & it says it is successful, but I cannot shrink it
> > enough. I've tried changing the recovery model to Simple & then shrinking the
> > log, but this doesn't work either.
> > Any suggestions will be appreciated.
> >
> > BigSam
> --
> I had a heap of trouble with the same problem one day. When I found
> the solution, I saved the steps. Here they are:
> 1) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> 2) Backup Log <dbname> With Truncate_Only
> 3) DBCC Shrinkfile (<logfilename>, TruncateOnly)
> That should fix it.
> Mike|||Read about backup and especially recovery model in Books Online, Them this will probably help:
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/
"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the log
> won't shrink. I need to reduce the size, to free up space on the drive. I've
> tried to shrink the file & it says it is successful, but I cannot shrink it
> enough. I've tried changing the recovery model to Simple & then shrinking the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
> BigSam|||"BigSam" <BigSam@.discussions.microsoft.com> wrote in message
news:7C3F7F32-36EF-49C3-B83C-FCE89FDF4147@.microsoft.com...
> I'm unable to shrink my log file. The databse is about 2 GB & the log file
> has grown to 11.5 GB. I've run transaction backups every 3 hours, but the
log
> won't shrink. I need to reduce the size, to free up space on the drive.
I've
> tried to shrink the file & it says it is successful, but I cannot shrink
it
> enough. I've tried changing the recovery model to Simple & then shrinking
the
> log, but this doesn't work either.
> Any suggestions will be appreciated.
Do a DBCC OPENTRAN on the database and make sure there are no open
transactions.
But it'snot clear to me if youmean the log is 11.5 gb and FULL or 11.5GB on
disk, but only partially full.
> BigSam

LOG File Maintenance

Hello! Hope everybody is doing OK.
I have a quick question. I use to run the command below to keep the Log
File of my database small.
BACKUP LOG FlexSol WITH NO_LOG
DBCC SHRINKDATABASE (FlexSol, 10)
The question is, now that I setup replication in this database, would I be
able to run this command without affecting the way replication works, if so,
then what are the steps to perform maintenance in the log file.
Thank you in advance for any input.
Marlene A. Roman
if the log reader has not read and commited transactions in the tlog to the distribution database, you will not be able to truncate this portion of the log.
So, truncate away, as replication will not be affected.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||(...and if the replication type is merge or snapshot, this will not be affected either).
Regards,
Paul Ibison
|||Thank you! :P
|||Paul, you are starting to making me look bad, and I don't need your help for
that
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:8EB60E9B-6EA6-48B3-A51E-8D60B858A60B@.microsoft.com...
> (...and if the replication type is merge or snapshot, this will not be
affected either).
> Regards,
> Paul Ibison

Log file growth- what's the culprit?

I have an issue where a database on SQL 2005 (set to "simple" recovery mode)
has exponential log growth when I run one particular software service
against the DB, it grows by 100-200 megabytes a minute.
This service is very simple; it contains several "select" statements (that
wouldn't cause log growth, right?), and two or three insert/update
statements. The insert/update statements are all in stored procedures that
the service calls (it is written in .Net 2.0). The stored procedures have no
transaction statements in them at all, and if I add begin/end transaction
statements it doesn't make a difference.
So, how do I figure out exactly what in the service is causing the log file
growth? I have no idea how to determine that.
Thanks for any help.
Jon
Is the log file growth in Tempdb or a user db? You can use profiler and
trace the log autogrow events and see what statements are just before the
growth.
Andrew J. Kelly SQL MVP
"Jon" <rosenberg@.mainstreams.com> wrote in message
news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>I have an issue where a database on SQL 2005 (set to "simple" recovery
>mode) has exponential log growth when I run one particular software service
>against the DB, it grows by 100-200 megabytes a minute.
> This service is very simple; it contains several "select" statements (that
> wouldn't cause log growth, right?), and two or three insert/update
> statements. The insert/update statements are all in stored procedures that
> the service calls (it is written in .Net 2.0). The stored procedures have
> no transaction statements in them at all, and if I add begin/end
> transaction statements it doesn't make a difference.
> So, how do I figure out exactly what in the service is causing the log
> file growth? I have no idea how to determine that.
> Thanks for any help.
> Jon
>
|||It's a user db. Thanks, I'll try and use profiler to figure it out, I wasn't
aware of the autogrow events.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OECNe790HHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is the log file growth in Tempdb or a user db? You can use profiler and
> trace the log autogrow events and see what statements are just before the
> growth.
> --
> Andrew J. Kelly SQL MVP
> "Jon" <rosenberg@.mainstreams.com> wrote in message
> news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>

Log file growth- what's the culprit?

I have an issue where a database on SQL 2005 (set to "simple" recovery mode)
has exponential log growth when I run one particular software service
against the DB, it grows by 100-200 megabytes a minute.
This service is very simple; it contains several "select" statements (that
wouldn't cause log growth, right?), and two or three insert/update
statements. The insert/update statements are all in stored procedures that
the service calls (it is written in .Net 2.0). The stored procedures have no
transaction statements in them at all, and if I add begin/end transaction
statements it doesn't make a difference.
So, how do I figure out exactly what in the service is causing the log file
growth? I have no idea how to determine that.
Thanks for any help.
JonIs the log file growth in Tempdb or a user db? You can use profiler and
trace the log autogrow events and see what statements are just before the
growth.
--
Andrew J. Kelly SQL MVP
"Jon" <rosenberg@.mainstreams.com> wrote in message
news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>I have an issue where a database on SQL 2005 (set to "simple" recovery
>mode) has exponential log growth when I run one particular software service
>against the DB, it grows by 100-200 megabytes a minute.
> This service is very simple; it contains several "select" statements (that
> wouldn't cause log growth, right?), and two or three insert/update
> statements. The insert/update statements are all in stored procedures that
> the service calls (it is written in .Net 2.0). The stored procedures have
> no transaction statements in them at all, and if I add begin/end
> transaction statements it doesn't make a difference.
> So, how do I figure out exactly what in the service is causing the log
> file growth? I have no idea how to determine that.
> Thanks for any help.
> Jon
>|||It's a user db. Thanks, I'll try and use profiler to figure it out, I wasn't
aware of the autogrow events.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OECNe790HHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is the log file growth in Tempdb or a user db? You can use profiler and
> trace the log autogrow events and see what statements are just before the
> growth.
> --
> Andrew J. Kelly SQL MVP
> "Jon" <rosenberg@.mainstreams.com> wrote in message
> news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>>I have an issue where a database on SQL 2005 (set to "simple" recovery
>>mode) has exponential log growth when I run one particular software
>>service against the DB, it grows by 100-200 megabytes a minute.
>> This service is very simple; it contains several "select" statements
>> (that wouldn't cause log growth, right?), and two or three insert/update
>> statements. The insert/update statements are all in stored procedures
>> that the service calls (it is written in .Net 2.0). The stored procedures
>> have no transaction statements in them at all, and if I add begin/end
>> transaction statements it doesn't make a difference.
>> So, how do I figure out exactly what in the service is causing the log
>> file growth? I have no idea how to determine that.
>> Thanks for any help.
>> Jon
>

Log file growth- what's the culprit?

I have an issue where a database on SQL 2005 (set to "simple" recovery mode)
has exponential log growth when I run one particular software service
against the DB, it grows by 100-200 megabytes a minute.
This service is very simple; it contains several "select" statements (that
wouldn't cause log growth, right?), and two or three insert/update
statements. The insert/update statements are all in stored procedures that
the service calls (it is written in .Net 2.0). The stored procedures have no
transaction statements in them at all, and if I add begin/end transaction
statements it doesn't make a difference.
So, how do I figure out exactly what in the service is causing the log file
growth? I have no idea how to determine that.
Thanks for any help.
JonIs the log file growth in Tempdb or a user db? You can use profiler and
trace the log autogrow events and see what statements are just before the
growth.
Andrew J. Kelly SQL MVP
"Jon" <rosenberg@.mainstreams.com> wrote in message
news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>I have an issue where a database on SQL 2005 (set to "simple" recovery
>mode) has exponential log growth when I run one particular software service
>against the DB, it grows by 100-200 megabytes a minute.
> This service is very simple; it contains several "select" statements (that
> wouldn't cause log growth, right?), and two or three insert/update
> statements. The insert/update statements are all in stored procedures that
> the service calls (it is written in .Net 2.0). The stored procedures have
> no transaction statements in them at all, and if I add begin/end
> transaction statements it doesn't make a difference.
> So, how do I figure out exactly what in the service is causing the log
> file growth? I have no idea how to determine that.
> Thanks for any help.
> Jon
>|||It's a user db. Thanks, I'll try and use profiler to figure it out, I wasn't
aware of the autogrow events.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OECNe790HHA.3848@.TK2MSFTNGP03.phx.gbl...
> Is the log file growth in Tempdb or a user db? You can use profiler and
> trace the log autogrow events and see what statements are just before the
> growth.
> --
> Andrew J. Kelly SQL MVP
> "Jon" <rosenberg@.mainstreams.com> wrote in message
> news:ekMLJP50HHA.728@.TK2MSFTNGP05.phx.gbl...
>

Log backups not truncating log

I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
> >I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old open transactions (DBCC
OPENTRAN).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
>> <jheas...@.salvagedirect.com> wrote in message
>> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>> >I just took over administration of a SS 2000 database that is hosted
>> > by a third party service. There was a full backup being run each
>> > night, with no transaction log backups. The TL did not truncate after
>> > I started log backups. There are no uncommitted transactions in the
>> > log according to DBCC OPENTRAN. I don't have access to the machine
>> > itself, only to SQL Server via Enterprise Manager. Please respond if
>> > you have any suggestions. Thank you.
>> Not truncating, or not shrinking?
>> Log backups don't shrink the size of the data file.
>> --
>> Greg Moore
>> SQL Server DBA Consulting
>> Email: sql (at) greenms.com http://www.greenms.com
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
> >I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> > I just took over administration of a SS 2000 database that is hosted
> > by a third party service. There was a full backup being run each
> > night, with no transaction log backups. The TL did not truncate after
> > I started log backups. There are no uncommitted transactions in the
> > log according to DBCC OPENTRAN. I don't have access to the machine
> > itself, only to SQL Server via Enterprise Manager. Please respond if
> > you have any suggestions. Thank you.
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication|||Ahh. Try the DBCC SHRINKFILE command. However, due to virtual log
segmentation internally, it may STILL not shrink. There is help available
on the web for forcing a tlog to be shrinkable. It involves writing dummy
records to the tlog to force it over to the next virtual log boundary.
An easier fix if you can do it is to simply detatch and reattach using
sp_detach_db and sp_attach_single_file_db (assuming you have single file
db). There is a sp_attach_db command too, but you need to record
appropriate information for each db file prior to detach. PLEASE study
this command pair and test first if you use it!!
--
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174966348.518536.144910@.y80g2000hsf.googlegroups.com...
> On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>> Full backups do not remove transactions from the transaction log. If you
>> are not backing up the tlog, it will grow indefinitely. You can either
>> start backing it up (the tlog), or you can issue the BACKUP log
>> mydatabase
>> with truncate_only command to flush out the committed transactions. ONLY
>> do
>> this if you no longer care about using the tlog for recovery purposes!
>> --
>> TheSQLGuru
>> President
>> Indicium Resources, Inc.
>> <jheas...@.salvagedirect.com> wrote in message
>> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>> >I just took over administration of a SS 2000 database that is hosted
>> > by a third party service. There was a full backup being run each
>> > night, with no transaction log backups. The TL did not truncate after
>> > I started log backups. There are no uncommitted transactions in the
>> > log according to DBCC OPENTRAN. I don't have access to the machine
>> > itself, only to SQL Server via Enterprise Manager. Please respond if
>> > you have any suggestions. Thank you.
> I understand that full backups do not remove transactions from the
> TL. My problem is the tlog file is not being truncated after the tlog
> backup runs.
>|||Well, yesterday afternoon the tlog finally cleared on its own, five
days after beginning tlog backups. I don't know why it took so long,
perhaps someone has insight into that. As the day wore on, the file
also shrunk physically.
I much appreciate the replies. Tibor, the articled on shrinking db
files is very informative. Thanks again, all.|||On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> Well, yesterday afternoon the tlog finally cleared on its own, five
> days after beginning tlog backups. I don't know why it took so long,
> perhaps someone has insight into that. As the day wore on, the file
> also shrunk physically.
> I much appreciate the replies. Tibor, the articled on shrinking db
> files is very informative. Thanks again, all.
The log file PHYSICALLY shrank as the day progressed? Do you have the
Autoshrink option enabled on that database? You should turn that
off. Shrinking the database file is a logged operation, so every time
autoshrink decides to shrink the database, you're going to get log
activity, which won't truncate until the shrink operation finishes.
That might explain what you were seeing before. Autoshrink should NOT
be used on a production database.|||On Mar 29, 9:18 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 28, 8:32 am, jheas...@.salvagedirect.com wrote:
> > Well, yesterday afternoon the tlog finally cleared on its own, five
> > days after beginning tlog backups. I don't know why it took so long,
> > perhaps someone has insight into that. As the day wore on, the file
> > also shrunk physically.
> > I much appreciate the replies. Tibor, the articled on shrinking db
> > files is very informative. Thanks again, all.
> The log file PHYSICALLY shrank as the day progressed? Do you have the
> Autoshrink option enabled on that database? You should turn that
> off. Shrinking the database file is a logged operation, so every time
> autoshrink decides to shrink the database, you're going to get log
> activity, which won't truncate until the shrink operation finishes.
> That might explain what you were seeing before. Autoshrink should NOT
> be used on a production database.
Yes, it is enabled. I called the hosting company to ask about it.
They said it's part of their standard procedure to enable Autoshrink
when they set up a SQL Server instance for a client. The tech I spoke
with did not know why, but said it was OK to disable it, which I did.
Thank you, Tracy.

Log backups not truncating log

I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com
|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.
|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegro ups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>
|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.
|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.
|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegr oups.com...
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.
|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.
|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication

Log backups not truncating log

I just took over administration of a SS 2000 database that is hosted
by a third party service. There was a full backup being run each
night, with no transaction log backups. The TL did not truncate after
I started log backups. There are no uncommitted transactions in the
log according to DBCC OPENTRAN. I don't have access to the machine
itself, only to SQL Server via Enterprise Manager. Please respond if
you have any suggestions. Thank you.<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>
Not truncating, or not shrinking?
Log backups don't shrink the size of the data file.
Greg Moore
SQL Server DBA Consulting
Email: sql (at) greenms.com http://www.greenms.com|||On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.com> wrote:
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
> Not truncating, or not shrinking?
> Log backups don't shrink the size of the data file.
> --
> Greg Moore
> SQL Server DBA Consulting
> Email: sql (at) greenms.com http://www.greenms.com
Not truncating. There is 450 MB of used space after the most recent
TL backup.|||What does you virtual log file layout say (see about mind section of
http://www.karaszi.com/SQLServer/info_dont_shrink.asp). Also, check for old
open transactions (DBCC
OPENTRAN).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<jheasley@.salvagedirect.com> wrote in message
news:1174930061.164826.99350@.n76g2000hsh.googlegroups.com...
> On Mar 26, 1:12 pm, "Greg D. Moore \(Strider\)"
> <mooregr_deletet...@.greenms.com> wrote:
> Not truncating. There is 450 MB of used space after the most recent
> TL backup.
>|||What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
What percentage is used?
As said, a log backup does not shrink the file. To shrink the file,
you'll need to run a DBCC SHRINKFILE against the log.|||On Mar 26, 6:36 pm, "cstrong" <clive.str...@.googlemail.com> wrote:
> What is the size of the log? Run a DBCC SQLPERF(LOGSPACE).
> What percentage is used?
> As said, a log backup does not shrink the file. To shrink the file,
> you'll need to run a DBCC SHRINKFILE against the log.
I understand the backup does not shrink the file physically, but
should truncate the transactions that have been written to the data
file. My file is not truncating after the backup, so it keeps growing
physically. Here are the results of the DBCC commands:
DBCC OPENTRAN:
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC SQLPERF(LOGSPACE)
Database Name: MyDb
Log Size (MB): 474.4922
Log Space Used (%): 99.84307
Status: 0
DBCC LOGINFO
This returns 1878 rows. The status column has a value of 2 for every
row, which would seem to indicate every VLF is in use.
Thanks for the replies.|||Full backups do not remove transactions from the transaction log. If you
are not backing up the tlog, it will grow indefinitely. You can either
start backing it up (the tlog), or you can issue the BACKUP log mydatabase
with truncate_only command to flush out the committed transactions. ONLY do
this if you no longer care about using the tlog for recovery purposes!
TheSQLGuru
President
Indicium Resources, Inc.
<jheasley@.salvagedirect.com> wrote in message
news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
>|||On Mar 26, 10:48 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Full backups do not remove transactions from the transaction log. If you
> are not backing up the tlog, it will grow indefinitely. You can either
> start backing it up (the tlog), or you can issue the BACKUP log mydatabase
> with truncate_only command to flush out the committed transactions. ONLY
do
> this if you no longer care about using the tlog for recovery purposes!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> <jheas...@.salvagedirect.com> wrote in message
> news:1174924819.329414.142970@.y80g2000hsf.googlegroups.com...
>
I understand that full backups do not remove transactions from the
TL. My problem is the tlog file is not being truncated after the tlog
backup runs.|||On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
> I just took over administration of a SS 2000 database that is hosted
> by a third party service. There was a full backup being run each
> night, with no transaction log backups. The TL did not truncate after
> I started log backups. There are no uncommitted transactions in the
> log according to DBCC OPENTRAN. I don't have access to the machine
> itself, only to SQL Server via Enterprise Manager. Please respond if
> you have any suggestions. Thank you.
Is the database enabled for replication? Transactions won't truncate
from the log if they are awaiting replication.|||On Mar 27, 8:45 am, "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote:
> On Mar 26, 11:00 am, jheas...@.salvagedirect.com wrote:
>
> Is the database enabled for replication? Transactions won't truncate
> from the log if they are awaiting replication.
The db is not enable for replication

Log Backups confusing me

Please help me out on log backups.


What happens when 2 log backups of the same db happen simultaneously?

Hypothetically:
One Backup Log job run by SQL Server agent(xp_SQLmaint) every 60 minutes & one Log backup run by Backup Exec every 59 minutes.

Is this a dumb idea and will it cause restore problems based on LSN's contained in the 2 separate backup sets?

Thanks
GI believe you would get error 3023 (Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.) when the 2nd tran log tries to start while the 1st was running.|||Thanks

So what you're saying is that it's impossible run 2 log backups of the same db at the same time. This implies that one of my jobs must be killed. Which would you use? xp_sqlmaint or backup exec SQL agent(Log backup without truncate)?|||We create maintenance plans for each user db and group the system dbs into a single plan. All dbs get a nightly full backup (except tempdb of course). The user dbs get tran logs that range from every 15 mins to every 2 hours based on importance and use.

SQL Agent runs the jobs, so they run under the xp_sqlmaint exec (except for the SQL LiteSpeed xp).

The final choice is yours. Which way do you feel most comfortable with? You will be the one (I assume) that will have to do the restore if everything goes to heck in a handbasket!|||And to answer your last question, there is no way to ever run two transaction log backups on the database at the exact same time. The second job will just fail.

Locks the tables while running Report...Very Very Urgent....

Hi all ,

When i Run the report in reporting services, it locks the tables.
so is there any option to Unlock the tables. I m using just select query to run the report but when i run the report it locks the tables.

I used with(nolock) option in select query but it didnt work...still showing me lock on the tables.

Pls help...its urgent

Thanking You,
Rupali Rane.

If this is SQL server you could try changing the query to a stored procedure and putting "set transaction isolation level read uncommitted" in the beginning of the procedure.

|||

"set transaction isolation level read uncommitted" is the equivelant of specifying (NOLOCK) on all tables.

rupamp- Where are you seeing the locks, what type of locks, and do you have a (NOLOCK) on all tables, even in subqueries/derived tables?

As stated above, if you specify "set transaction isolation level read uncommitted" at the beginning of the query, you can skip the (NOLOCK) in the query.

BobP

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mike
are u using some syntax such as
select xxx into #abc from xyz ?
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>
|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>

Locks in tempdb

I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and it
takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run it
on a production box and it is taking hours and in some cases not even coming
close to finishing. The locks on the production box keep going up. The last
time I issued a kill command there were 20000 locks for this bad spid.
The test box does not have anything running on it besides anti virus
software. The production box does have some very low intensive stuff running
on it.
- The procedure in question is using dynamic sql
- I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Does anyone have any obvious rabbit holes I could go down?
Thanks,
Mikeare u using some syntax such as
select xxx into #abc from xyz '
that will definitely create lots of lock in tempdb.
If so, try avoid it.
Kan
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>|||Could you post the proc in question? It could be any number of things.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Mike S" <sagaeta@.yahoo.com> wrote in message
news:edvn8ILKEHA.1156@.TK2MSFTNGP09.phx.gbl...
> I am runninga stored procedure on a test box (1GB CPU and 500MB RAM) and
it
> takes about 20 minutes. The locks max out at about 2400 (sp_lock). I run
it
> on a production box and it is taking hours and in some cases not even
coming
> close to finishing. The locks on the production box keep going up. The
last
> time I issued a kill command there were 20000 locks for this bad spid.
> The test box does not have anything running on it besides anti virus
> software. The production box does have some very low intensive stuff
running
> on it.
> - The procedure in question is using dynamic sql
> - I set SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> Does anyone have any obvious rabbit holes I could go down?
> Thanks,
> Mike
>