Could anyone help to capture perfmon counters including SQL Server related to
a table on fly. I'm aware of DBCC PERFMON but it doesn't give all the
counters that are related to SQL SErver.
Thanks in advance.
Check out master..sysperfinfo. Note though that the average values are not timeadjusted in this
table. Search KB and you will find an article on this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Satya SKJ" <satyaskj@.yahoo.co.uk> wrote in message
news:3433A607-BE42-4B8D-B99B-7CE406795C37@.microsoft.com...
> Could anyone help to capture perfmon counters including SQL Server related to
> a table on fly. I'm aware of DBCC PERFMON but it doesn't give all the
> counters that are related to SQL SErver.
> Thanks in advance.
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Friday, February 24, 2012
Sunday, February 19, 2012
capture blocking in Profiler
I was just looking in Profier wanting to capture blocking info and I dont
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisR
Hi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default...b;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>
|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default...b;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisR
Hi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default...b;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>
|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default...b;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
capture blocking in Profiler
I was just looking in Profier wanting to capture blocking info and I dont
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
>> I was just looking in Profier wanting to capture blocking info and I dont
>> see it. sp_who and dbcc inputbuffer are good but, what if the the
>> blocking spid keeps changing? It would seem to me that there would be a
>> way to get all blocking info on every spid involved?
>> TIA, ChrisR
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/default.aspx?scid=kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
>> I was just looking in Profier wanting to capture blocking info and I dont
>> see it. sp_who and dbcc inputbuffer are good but, what if the the
>> blocking spid keeps changing? It would seem to me that there would be a
>> way to get all blocking info on every spid involved?
>> TIA, ChrisR
capture blocking in Profiler
I was just looking in Profier wanting to capture blocking info and I dont
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/defaul...kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/defaul...kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
spid keeps changing? It would seem to me that there would be a way to get
all blocking info on every spid involved?
TIA, ChrisRHi Chris,
You can use the script in this article to capture blocking information.
http://support.microsoft.com/defaul...kb;en-us;271509
Another way is to use PSSDIAG to capture blocking as well, which is
essentially running the same script.
Yih-Yoon Lee
E-mail: yihyoon@.gmail.com
ChrisR wrote:
> I was just looking in Profier wanting to capture blocking info and I dont
> see it. sp_who and dbcc inputbuffer are good but, what if the the blocking
> spid keeps changing? It would seem to me that there would be a way to get
> all blocking info on every spid involved?
> TIA, ChrisR
>|||Thanks Yih.
"Yih-Yoon Lee" <yihyoon@.gmail.com> wrote in message
news:OYuJiXFbFHA.2520@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hi Chris,
> You can use the script in this article to capture blocking information.
> http://support.microsoft.com/defaul...kb;en-us;271509
> Another way is to use PSSDIAG to capture blocking as well, which is
> essentially running the same script.
> Yih-Yoon Lee
> E-mail: yihyoon@.gmail.com
> ChrisR wrote:
Thursday, February 16, 2012
Cant's shrink tempdb in sql2k5
Hi, tried to shrink 190gb tempdb but got following message:
DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.
Anyone know why? The tempdb is almost empty, just doesn't release free space.
If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
|||
Thanks for the info, but they don't address my issue.
The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.
|||I personally have not seen this issue.As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||
hi rmiao,
is your tempdb files set to autogrow?
regards
jag
|||first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Madhu
|||Yes, but don't think it's related.
|||I like to know why as well, but didn't get any answer from Microsoft.|||most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...
Madhu
|||You could also look at :
http://msdn2.microsoft.com/en-gb/library/ms176029.aspx
this gives practical monitoring suggestions and links to other tempdb concept articles.
Tuesday, February 14, 2012
Cant's shrink tempdb in sql2k5
Hi, tried to shrink 190gb tempdb but got following message:
DBCC SHRINKFILE: Page 1:24027896 could not be moved because it is a work table page.
Anyone know why? The tempdb is almost empty, just doesn't release free space.
If the database is still being used by an active transaction and the worktable locates at the end of the file, you can't shrink it. Consider taking at look at the following resources.http://support.microsoft.com/kb/307487
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
|||
Thanks for the info, but they don't address my issue.
The problem I have is that tempdb has lot of free pages in internal object reserved page pool, but sql2k5 doesn't use those free pages when create new internal object. It expands tempdb to get more pages instead, so causes tempdb to keep growing. I got the page number of so called work table page and checked it with dbcc page to get object id it belongs to, but have no way to check if the object is still be used in the tempdb. The empty tempdb keeps grow and eventually run out of disk space, the only way to stop it is restarting sql. I think sql2k5 should have better way to handle this.
|||I personally have not seen this issue.As clearly stated in the whitepaper, internal objects are untouchable by user. If you think there is a bug, you can file one at http://connect.microsoft.com. Be sure to provide a complete repro script.|||I'm having the same issue. Has anyone came up with a solution. The only way it will shrink is to restart the services.|||
hi rmiao,
is your tempdb files set to autogrow?
regards
jag
|||first we need to know why SQL Server is consuming tempdb so alarmingly ... is there any database set as Snapshot issolation level or rowversioning... is there excessive use of Tempdb/tablevariable.. or else just run SQL Profiler and find out the reason behind the growth first
http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx
Madhu
|||Yes, but don't think it's related.
|||I like to know why as well, but didn't get any answer from Microsoft.|||most probably... excessive tempdb consumption is due to some setting /feature in some user database... that u need to findout yourself... read the link provided and see what feature u are using which consumes tempdb...
Madhu
|||You could also look at :
http://msdn2.microsoft.com/en-gb/library/ms176029.aspx
this gives practical monitoring suggestions and links to other tempdb concept articles.
Friday, February 10, 2012
Can't truncate a 99 GB transaction log need badly help
Hi,
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|UsedPages|Est imatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
Zorba
Hi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller
|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller
|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|Est imatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>
|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|UsedPages|Est imatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
Zorba
Hi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller
|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller
|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|Est imatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>
|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/Transacti...leGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>
Can't truncate a 99 GB transaction log need badly help
Hi,
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|Use
dPages|EstimatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
ZorbaHi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|Use
dPages|EstimatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|Use
dPages|EstimatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
ZorbaHi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|Use
dPages|EstimatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/Transact...ileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>
Can't truncate a 99 GB transaction log need badly help
Hi,
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
ZorbaHi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I am having hard time trying to truncate a 99 GB transaction log for a
>> database.
>> I already tried
>> CHECKPOINT
>> DBCC SHRINKFILE (emptydb_Log, 15000)
>> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
>> And I get this results:
>> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
>> 8 | 2 | 11601696| 63| 11601696| 56
>> From allocated 99 GB to this log in taskpad I can see that used space is
>> 13642.14 MB however none of the above work, even a full backup with
>> truncate log option selected.
>> Maintenance plan also can't do the job also.
>> Can someone explain to me how I can safely get rid of these free 76996 GB
>> from log file.
>>
>> I really appreciate your support, it is imperative to find a solution
>> ASAP as I run out of space.
>> Thank you,
>> Zorba
>>
>
I am having hard time trying to truncate a 99 GB transaction log for a
database.
I already tried
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, 15000)
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
And I get this results:
DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
8 | 2 | 11601696| 63| 11601696|
56
From allocated 99 GB to this log in taskpad I can see that used space is
13642.14 MB however none of the above work, even a full backup with truncate
log option selected.
Maintenance plan also can't do the job also.
Can someone explain to me how I can safely get rid of these free 76996 GB
from log file.
I really appreciate your support, it is imperative to find a solution ASAP
as I run out of space.
Thank you,
ZorbaHi,
try this
Backup log database name with truncate_only
dbcc shrinfile(logical name,size);
This will take lot of time as it is very big.
hope this help
from
doller|||Hi,
try this
Backup log database name with truncate_only
dbcc shrinkfile(logical name,size); size is give at the time of
database creation
This will take lot of time as it is very big.
hope this help
from
doller|||Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
Andrew J. Kelly SQL MVP
"Zorba" <nospam@.nonexistent> wrote in message
news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I am having hard time trying to truncate a 99 GB transaction log for a
> database.
> I already tried
> CHECKPOINT
> DBCC SHRINKFILE (emptydb_Log, 15000)
> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
> And I get this results:
> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
> 8 | 2 | 11601696| 63| 11601696| 56
> From allocated 99 GB to this log in taskpad I can see that used space is
> 13642.14 MB however none of the above work, even a full backup with
> truncate log option selected.
> Maintenance plan also can't do the job also.
> Can someone explain to me how I can safely get rid of these free 76996 GB
> from log file.
>
> I really appreciate your support, it is imperative to find a solution ASAP
> as I run out of space.
> Thank you,
> Zorba
>
>|||Thank you all, for helping me.
Finally after 5 hours the only method that gave result was to backup only
the transaction log to a file and
after that:
CHECKPOINT
DBCC SHRINKFILE (emptydb_Log, NOTRUNCATE )
DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
After first attempt this had reduced the log size to 10.6 GB and after
second backup of transaction log with same process it went down to 60 MB.
Now I learned that I should keep my eyes more often on these automatically
scheduled maintenance plans :-).
Note: a full DB backup with truncate log option selected was not effective.
Thank you,
Zorba
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eo$HJlhtFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Do you have an open transaction? Try running DBCC OPENTRAN on that DB.
>
> http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues
> http://www.support.microsoft.com/?id=317375 Log File Grows too big
> http://www.support.microsoft.com/?id=110139 Log file filling up
> http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
> http://www.support.microsoft.com/?id=315512 Considerations for
> Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
> Server 7.0 Tran Log
> http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
> Server 2000 with DBCC SHRINKFILE
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
> considerations
>
> --
> Andrew J. Kelly SQL MVP
>
> "Zorba" <nospam@.nonexistent> wrote in message
> news:OL0AXtctFHA.460@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I am having hard time trying to truncate a 99 GB transaction log for a
>> database.
>> I already tried
>> CHECKPOINT
>> DBCC SHRINKFILE (emptydb_Log, 15000)
>> DBCC SHRINKFILE (emptydb_Log, TRUNCATEONLY )
>> And I get this results:
>> DbId|FieldId|CurrentSize|MinimumSize|UsedPages|EstimatedPages
>> 8 | 2 | 11601696| 63| 11601696| 56
>> From allocated 99 GB to this log in taskpad I can see that used space is
>> 13642.14 MB however none of the above work, even a full backup with
>> truncate log option selected.
>> Maintenance plan also can't do the job also.
>> Can someone explain to me how I can safely get rid of these free 76996 GB
>> from log file.
>>
>> I really appreciate your support, it is imperative to find a solution
>> ASAP as I run out of space.
>> Thank you,
>> Zorba
>>
>
Subscribe to:
Posts (Atom)