How to determine isolation level

Running SQLServer2000 on Win2k. How can I determine the isolation level of
a running transaction?
Thanx.Vince wrote:
> Running SQLServer2000 on Win2k. How can I determine the isolation
> level of a running transaction?
> Thanx.
READ COMMITTED is the default unless you specifically change it using
SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
statement itself. If the isolation level is overriden for the session,
you can see this using DBCC USEROPTIONS (look for the item called
"isolation level"). If the "isolation level" option is not there, it
means you're using the default value.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thank you for the response. Is there any way to determine the isolation
level of another transaction (not mine); thru Enterprise Manager, for
example?
Thanx again.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:umXsW3l3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> Vince wrote:
> > Running SQLServer2000 on Win2k. How can I determine the isolation
> > level of a running transaction?
> >
> > Thanx.
> READ COMMITTED is the default unless you specifically change it using
> SET TRANSACTION ISOLATION LEVEL. It can also be overridden by the SQL
> statement itself. If the isolation level is overriden for the session,
> you can see this using DBCC USEROPTIONS (look for the item called
> "isolation level"). If the "isolation level" option is not there, it
> means you're using the default value.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Vince wrote:
> Thank you for the response. Is there any way to determine the
> isolation level of another transaction (not mine); thru Enterprise
> Manager, for example?
There may be a way to pull that information from sysprocesses, but
nothing I can see directly. In any case, that won't tell you what
isolation level the user is currently using anyway for a given
transaction. Each DML statement could use a an isolation level different
that the user's default. What is the reason you are trying to determine
this information?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I've been asked by one of our developers if this can be done. He's working
with a third party front end that doesn't afford a lot of opportunity to
deal with isolation levels, but he was able to modify some of the Selects to
include a hint (read uncommited I think) and wanted to make sure that SQL
Server was actually taking the hint.
It's an interesting question and I don't work with SQL Server very much, so
I thought I'd open it up to the experts.
Thanx again for all your help.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:OKfXA6v3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> Vince wrote:
> > Thank you for the response. Is there any way to determine the
> > isolation level of another transaction (not mine); thru Enterprise
> > Manager, for example?
> There may be a way to pull that information from sysprocesses, but
> nothing I can see directly. In any case, that won't tell you what
> isolation level the user is currently using anyway for a given
> transaction. Each DML statement could use a an isolation level different
> that the user's default. What is the reason you are trying to determine
> this information?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Vince wrote:
> I've been asked by one of our developers if this can be done. He's
> working with a third party front end that doesn't afford a lot of
> opportunity to deal with isolation levels, but he was able to modify
> some of the Selects to include a hint (read uncommited I think) and
> wanted to make sure that SQL Server was actually taking the hint.
>
If the hint is in the query, then I think you can just assume SQL Server
is using it. However, READ UNCOMMITTED (which is the same as a NOLOCK
hint in the query and allows dirty reads) is only applicable for
SELECTs.
--
David Gugick
Quest Software
www.imceda.com
www.quest.comsql

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. Thexp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix

How to delete large number of record without matter with transaction log?

Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei Cheong
Vyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>
|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>
|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx .gbl...[vbcol=seagreen]
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I
>

How to delete large number of record without matter with transaction log?

Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...[vb
col=seagreen]
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> room
> file
> SQL
> so
> to
>[/vbcol]|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
glsD:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
log[vbcol=seagreen]
one.[vbcol=seagreen]
"Query[vbcol=seagreen]
it[vbcol=seagreen]
temp[vbcol=seagreen]
that[vbcol=seagreen]
company[vbcol=seagreen]
like[vbcol=seagreen]
I[vbcol=seagreen]
>

How to delete large number of record without matter with transaction log?

Hello,
It happens to me that I have control over a MSSQL 2000 server that was
looked after by another staff today. And than there is a call from the
server's owner company that the server's harddisk is running out of space.
As background information, the SQL server's running on a 5GB HDD
partition with the Win2k system dir on it. And after some inspection, I
found the "msdb" database occupies about 1.13GB and it's transaction log
occupies 2MB.
So I opened Enterprise Manager and looked into the tables one by one.
And found the 'sysdtssteplog' table has almost 7 million rows. So I run
"delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
Analyzer"(I've explicitly called it to run the SQL statement) tell me it
cannot finish the task because the disk is running out of space and no room
for transaction log. And when I see the file size, the transaction log file
of msdb has grown to over 200MB. Oops.
At least, I managed to found an unoccupied machine to install a temp SQL
server, copy the database file to, trancate, and put back to the origional
server. And the story is over. But what should I do if I experienced that
next time? There's management change in both my company and that company so
replacement of harddisk may not be feasible in a short time. And I'd like to
know if there's anything wrong in my procedure of handling the issue. This
is my first time to handle a SQL server, and I have tough time on this. I
used to be a programmer only.
Looking forward for any advice. Thanks a lot.
Regards,
Lau Lei CheongVyas's example shows how to divide a "big" transaction into a small ones
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
/*
DELETION here and Don't forget WHERE condition
*/
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
ELSE
BEGIN
CHECKPOINT --Or doing BACKUP LOG File
END
END
SET ROWCOUNT 0
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> It happens to me that I have control over a MSSQL 2000 server that was
> looked after by another staff today. And than there is a call from the
> server's owner company that the server's harddisk is running out of space.
> As background information, the SQL server's running on a 5GB HDD
> partition with the Win2k system dir on it. And after some inspection, I
> found the "msdb" database occupies about 1.13GB and it's transaction log
> occupies 2MB.
> So I opened Enterprise Manager and looked into the tables one by one.
> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
> cannot finish the task because the disk is running out of space and no
room
> for transaction log. And when I see the file size, the transaction log
file
> of msdb has grown to over 200MB. Oops.
> At least, I managed to found an unoccupied machine to install a temp
SQL
> server, copy the database file to, trancate, and put back to the origional
> server. And the story is over. But what should I do if I experienced that
> next time? There's management change in both my company and that company
so
> replacement of harddisk may not be feasible in a short time. And I'd like
to
> know if there's anything wrong in my procedure of handling the issue. This
> is my first time to handle a SQL server, and I have tough time on this. I
> used to be a programmer only.
> Looking forward for any advice. Thanks a lot.
> Regards,
> Lau Lei Cheong
>
>|||Hello Uri,
Thanks for your response.
One further question, why does the code use "WHILE 1 = 1" instead of
anything like "WHILE (TRUE)"? Is there any reason behind?
Regards,
Lau Lei Cheong
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Vyas's example shows how to divide a "big" transaction into a small ones
> SET ROWCOUNT 1000
> WHILE 1 = 1
> BEGIN
> /*
> DELETION here and Don't forget WHERE condition
> */
> IF @.@.ROWCOUNT = 0
> BEGIN
> BREAK
> END
> ELSE
> BEGIN
> CHECKPOINT --Or doing BACKUP LOG File
> END
> END
> SET ROWCOUNT 0
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> It happens to me that I have control over a MSSQL 2000 server that
>> was
>> looked after by another staff today. And than there is a call from the
>> server's owner company that the server's harddisk is running out of
>> space.
>> As background information, the SQL server's running on a 5GB HDD
>> partition with the Win2k system dir on it. And after some inspection, I
>> found the "msdb" database occupies about 1.13GB and it's transaction log
>> occupies 2MB.
>> So I opened Enterprise Manager and looked into the tables one by one.
>> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
>> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the "Query
>> Analyzer"(I've explicitly called it to run the SQL statement) tell me it
>> cannot finish the task because the disk is running out of space and no
> room
>> for transaction log. And when I see the file size, the transaction log
> file
>> of msdb has grown to over 200MB. Oops.
>> At least, I managed to found an unoccupied machine to install a temp
> SQL
>> server, copy the database file to, trancate, and put back to the
>> origional
>> server. And the story is over. But what should I do if I experienced that
>> next time? There's management change in both my company and that company
> so
>> replacement of harddisk may not be feasible in a short time. And I'd like
> to
>> know if there's anything wrong in my procedure of handling the issue.
>> This
>> is my first time to handle a SQL server, and I have tough time on this. I
>> used to be a programmer only.
>> Looking forward for any advice. Thanks a lot.
>> Regards,
>> Lau Lei Cheong
>>
>|||1=1 is TRUE condition but if @.@.rowcount=0 we exit from the loop.
You can build your own logic to fetch the rows
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:e8UVyiQjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Hello Uri,
> Thanks for your response.
> One further question, why does the code use "WHILE 1 = 1" instead of
> anything like "WHILE (TRUE)"? Is there any reason behind?
> Regards,
> Lau Lei Cheong
> "Uri Dimant" <urid@.iscar.co.il>
¼¶¼g©ó¶l¥ó·s»D:%23LDM8MQjFHA.3012@.TK2MSFTNGP12.phx.gbl...
> > Vyas's example shows how to divide a "big" transaction into a small ones
> >
> > SET ROWCOUNT 1000
> > WHILE 1 = 1
> > BEGIN
> > /*
> > DELETION here and Don't forget WHERE condition
> > */
> >
> > IF @.@.ROWCOUNT = 0
> > BEGIN
> > BREAK
> > END
> > ELSE
> > BEGIN
> > CHECKPOINT --Or doing BACKUP LOG File
> > END
> > END
> >
> > SET ROWCOUNT 0
> > "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> > news:eSbLvFQjFHA.1464@.TK2MSFTNGP14.phx.gbl...
> >> Hello,
> >>
> >> It happens to me that I have control over a MSSQL 2000 server that
> >> was
> >> looked after by another staff today. And than there is a call from the
> >> server's owner company that the server's harddisk is running out of
> >> space.
> >>
> >> As background information, the SQL server's running on a 5GB HDD
> >> partition with the Win2k system dir on it. And after some inspection, I
> >> found the "msdb" database occupies about 1.13GB and it's transaction
log
> >> occupies 2MB.
> >>
> >> So I opened Enterprise Manager and looked into the tables one by
one.
> >> And found the 'sysdtssteplog' table has almost 7 million rows. So I run
> >> "delete from sysdtssteplog" to delete it. After 15-20 minutes, the
"Query
> >> Analyzer"(I've explicitly called it to run the SQL statement) tell me
it
> >> cannot finish the task because the disk is running out of space and no
> > room
> >> for transaction log. And when I see the file size, the transaction log
> > file
> >> of msdb has grown to over 200MB. Oops.
> >>
> >> At least, I managed to found an unoccupied machine to install a
temp
> > SQL
> >> server, copy the database file to, trancate, and put back to the
> >> origional
> >> server. And the story is over. But what should I do if I experienced
that
> >> next time? There's management change in both my company and that
company
> > so
> >> replacement of harddisk may not be feasible in a short time. And I'd
like
> > to
> >> know if there's anything wrong in my procedure of handling the issue.
> >> This
> >> is my first time to handle a SQL server, and I have tough time on this.
I
> >> used to be a programmer only.
> >>
> >> Looking forward for any advice. Thanks a lot.
> >>
> >> Regards,
> >> Lau Lei Cheong
> >>
> >>
> >>
> >
> >
>