I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>> I've noticed that running DBCC SQLPERF(Threads) shows the login name
>> truncated to 10 characters, which is a tiny problem if you use Windows
>> authentication, as all you seem to get is the first 10 characters of the
>> domain name. This leaves you guessing as to which pesky user is using
>> all
>> that resource. Anybody know of any useful workarounds?
>> Thanks.|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright © SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
> > Having thought about it a little harder, I can get the desired result by
> > loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> > joining them on their respective SPIDs. I suppose the only danger is that
> > the SPIDS change user between the two calls.
> >
> > "BarryC" wrote:
> >
> >> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> >> truncated to 10 characters, which is a tiny problem if you use Windows
> >> authentication, as all you seem to get is the first 10 characters of the
> >> domain name. This leaves you guessing as to which pesky user is using
> >> all
> >> that resource. Anybody know of any useful workarounds?
> >>
> >> Thanks.
>
>
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Thursday, March 8, 2012
Bug in DBCC SQLPerf ?
I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.
Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.
|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.
Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.
|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>
Bug in DBCC SQLPerf ?
I've noticed that running DBCC SQLPERF(Threads) shows the login name
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>
truncated to 10 characters, which is a tiny problem if you use Windows
authentication, as all you seem to get is the first 10 characters of the
domain name. This leaves you guessing as to which pesky user is using all
that resource. Anybody know of any useful workarounds?
Thanks.Having thought about it a little harder, I can get the desired result by
loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
joining them on their respective SPIDs. I suppose the only danger is that
the SPIDS change user between the two calls.
"BarryC" wrote:
> I've noticed that running DBCC SQLPERF(Threads) shows the login name
> truncated to 10 characters, which is a tiny problem if you use Windows
> authentication, as all you seem to get is the first 10 characters of the
> domain name. This leaves you guessing as to which pesky user is using all
> that resource. Anybody know of any useful workarounds?
> Thanks.|||Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
have all information in a documented table (sysprocesses)?
select spid as [Spid],
kpid as [Thread ID],
loginame as [LoginName],
status as [Status],
physical_io as [IO],
cpu as [CPU],
memusage as [MemUsage]
from master.dbo.sysprocesses
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"BarryC" <BarryC@.discussions.microsoft.com> wrote in message
news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...[vbcol=seagreen]
> Having thought about it a little harder, I can get the desired result by
> loading the data from DBCC SQLPERF(Threads) and sp_who into #tables and
> joining them on their respective SPIDs. I suppose the only danger is that
> the SPIDS change user between the two calls.
> "BarryC" wrote:
>|||A perfectly reasonable question to which I'm sure there is an excellent
answer. Just makes me feel like a man, I guess.
Thanks, though - you're quite right of course...
"Gert E.R. Drapers" wrote:
> Why use DBCC SQLPERF(THREADS) which is undocumented and unsupported if you
> have all information in a documented table (sysprocesses)?
> select spid as [Spid],
> kpid as [Thread ID],
> loginame as [LoginName],
> status as [Status],
> physical_io as [IO],
> cpu as [CPU],
> memusage as [MemUsage]
> from master.dbo.sysprocesses
>
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> You assume all risk for your use.
> Copyright ? SQLDev.Net 1991-2005 All rights reserved.
> "BarryC" <BarryC@.discussions.microsoft.com> wrote in message
> news:A4D03A1F-6CAF-4881-82F5-7F6BC8BEDF3A@.microsoft.com...
>
>
Bug in DBCC SHRINKDATABASE ?
Hi,
My real DB size is 5 MB and transaction log 30 MB. But transaction log
allocated 900 MB. And if I run
DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
it does not shrink me transaction log neither a bit. Why ????
I do not want to have so big transaction log.
BJ
Run DBCC SHRINKFILE instead
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> Hi,
> My real DB size is 5 MB and transaction log 30 MB. But transaction log
> allocated 900 MB. And if I run
> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> it does not shrink me transaction log neither a bit. Why
????
> I do not want to have so big transaction log.
|||I tried
DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
and nothing happened.
"Uri Dimant" wrote:
> BJ
> Run DBCC SHRINKFILE instead
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> ????
>
>
|||B.J
What are you really trying to do?
You have emptied the file, have you tried to remove it by using EM?
Are you trying to reduce the size of the log file?
Are you trying to delete/remove the log file?
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...[vbcol=seagreen]
> I tried
> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> and nothing happened.
> "Uri Dimant" wrote:
|||I want to reduce transaction log size.
when I right click my database and point to all task / shrink database / files
then I have :
Data
current size : 5 MB
Space used : 4 MB
Log
current size : 900,62 MB
Space used : 33,4 MB
And this does not like me. I have allocated to much size. I want to truncate
log to 35 MB. I did all combination of options in all task / shrink database
and no result and also I have not set minimal transaction log so I do not
know wehre is problem.
Hope I said you what you want.
"Uri Dimant" wrote:
> B.J
> What are you really trying to do?
> You have emptied the file, have you tried to remove it by using EM?
> Are you trying to reduce the size of the log file?
> Are you trying to delete/remove the log file?
>
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
>
>
|||BJ
1) Backup Log file (to truncate a commited transaction (inactive portion)
2) Run DBCC SHRINKFILE
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database /
files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
truncate
> log to 35 MB. I did all combination of options in all task / shrink
database[vbcol=seagreen]
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
log[vbcol=seagreen]
|||Hi B.J.
Try to look up BACKUP LOG and DBCC SHRINKFILE in BOL and make sure that you
do the right things. It could be that you haven't truncated the logfile
correctly and/or haven't shrinked the logfile but the database.
Regards
Steen
B.J. wrote:[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink
> database / files then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
> truncate log to 35 MB. I did all combination of options in all task /
> shrink database and no result and also I have not set minimal
> transaction log so I do not know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
|||B J
Have a look at these articles on shrinking the Transaction log.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Usually a transaction log only needs to be around 10 - 20% of the size of
the database. I assume you have a database in Full recovery mode and you do
not perform transaction log backups. Either set your database to simple
recovery mode or start taking regular transaction log backups.
Hope this helps
John
"B.J." wrote:
[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database / files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to truncate
> log to 35 MB. I did all combination of options in all task / shrink database
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
|||I did it and nothing. Read all about how to truncate log from help which is
installed to SQL Server 2000 but it does not work.
"Uri Dimant" wrote:
> BJ
> 1) Backup Log file (to truncate a commited transaction (inactive portion)
> 2) Run DBCC SHRINKFILE
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> files
> truncate
> database
> log
>
>
My real DB size is 5 MB and transaction log 30 MB. But transaction log
allocated 900 MB. And if I run
DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
it does not shrink me transaction log neither a bit. Why ????
I do not want to have so big transaction log.
BJ
Run DBCC SHRINKFILE instead
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> Hi,
> My real DB size is 5 MB and transaction log 30 MB. But transaction log
> allocated 900 MB. And if I run
> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> it does not shrink me transaction log neither a bit. Why
????
> I do not want to have so big transaction log.
|||I tried
DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
and nothing happened.
"Uri Dimant" wrote:
> BJ
> Run DBCC SHRINKFILE instead
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> ????
>
>
|||B.J
What are you really trying to do?
You have emptied the file, have you tried to remove it by using EM?
Are you trying to reduce the size of the log file?
Are you trying to delete/remove the log file?
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...[vbcol=seagreen]
> I tried
> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> and nothing happened.
> "Uri Dimant" wrote:
|||I want to reduce transaction log size.
when I right click my database and point to all task / shrink database / files
then I have :
Data
current size : 5 MB
Space used : 4 MB
Log
current size : 900,62 MB
Space used : 33,4 MB
And this does not like me. I have allocated to much size. I want to truncate
log to 35 MB. I did all combination of options in all task / shrink database
and no result and also I have not set minimal transaction log so I do not
know wehre is problem.
Hope I said you what you want.
"Uri Dimant" wrote:
> B.J
> What are you really trying to do?
> You have emptied the file, have you tried to remove it by using EM?
> Are you trying to reduce the size of the log file?
> Are you trying to delete/remove the log file?
>
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
>
>
|||BJ
1) Backup Log file (to truncate a commited transaction (inactive portion)
2) Run DBCC SHRINKFILE
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database /
files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
truncate
> log to 35 MB. I did all combination of options in all task / shrink
database[vbcol=seagreen]
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
log[vbcol=seagreen]
|||Hi B.J.
Try to look up BACKUP LOG and DBCC SHRINKFILE in BOL and make sure that you
do the right things. It could be that you haven't truncated the logfile
correctly and/or haven't shrinked the logfile but the database.
Regards
Steen
B.J. wrote:[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink
> database / files then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
> truncate log to 35 MB. I did all combination of options in all task /
> shrink database and no result and also I have not set minimal
> transaction log so I do not know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
|||B J
Have a look at these articles on shrinking the Transaction log.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Usually a transaction log only needs to be around 10 - 20% of the size of
the database. I assume you have a database in Full recovery mode and you do
not perform transaction log backups. Either set your database to simple
recovery mode or start taking regular transaction log backups.
Hope this helps
John
"B.J." wrote:
[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database / files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to truncate
> log to 35 MB. I did all combination of options in all task / shrink database
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
|||I did it and nothing. Read all about how to truncate log from help which is
installed to SQL Server 2000 but it does not work.
"Uri Dimant" wrote:
> BJ
> 1) Backup Log file (to truncate a commited transaction (inactive portion)
> 2) Run DBCC SHRINKFILE
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> files
> truncate
> database
> log
>
>
Bug in DBCC SHRINKDATABASE ?
Hi,
My real DB size is 5 MB and transaction log 30 MB. But transaction log
allocated 900 MB. And if I run
DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
it does not shrink me transaction log neither a bit. Why '''?
I do not want to have so big transaction log.BJ
Run DBCC SHRINKFILE instead
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> Hi,
> My real DB size is 5 MB and transaction log 30 MB. But transaction log
> allocated 900 MB. And if I run
> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> it does not shrink me transaction log neither a bit. Why
'''?
> I do not want to have so big transaction log.|||I tried
DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
and nothing happened.
"Uri Dimant" wrote:
> BJ
> Run DBCC SHRINKFILE instead
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> '''?
>
>|||B.J
What are you really trying to do?
You have emptied the file, have you tried to remove it by using EM?
Are you trying to reduce the size of the log file?
Are you trying to delete/remove the log file?
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...[vbcol=seagreen]
> I tried
> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> and nothing happened.
> "Uri Dimant" wrote:
>|||I want to reduce transaction log size.
when I right click my database and point to all task / shrink database / fil
es
then I have :
Data
current size : 5 MB
Space used : 4 MB
Log
current size : 900,62 MB
Space used : 33,4 MB
And this does not like me. I have allocated to much size. I want to truncate
log to 35 MB. I did all combination of options in all task / shrink database
and no result and also I have not set minimal transaction log so I do not
know wehre is problem.
Hope I said you what you want.
"Uri Dimant" wrote:
> B.J
> What are you really trying to do?
> You have emptied the file, have you tried to remove it by using EM?
> Are you trying to reduce the size of the log file?
> Are you trying to delete/remove the log file?
>
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
>
>|||BJ
1) Backup Log file (to truncate a commited transaction (inactive portion)
2) Run DBCC SHRINKFILE
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database /
files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
truncate
> log to 35 MB. I did all combination of options in all task / shrink
database[vbcol=seagreen]
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>
log[vbcol=seagreen]|||Hi B.J.
Try to look up BACKUP LOG and DBCC SHRINKFILE in BOL and make sure that you
do the right things. It could be that you haven't truncated the logfile
correctly and/or haven't shrinked the logfile but the database.
Regards
Steen
B.J. wrote:[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink
> database / files then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
> truncate log to 35 MB. I did all combination of options in all task /
> shrink database and no result and also I have not set minimal
> transaction log so I do not know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>|||B J
Have a look at these articles on shrinking the Transaction log.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Usually a transaction log only needs to be around 10 - 20% of the size of
the database. I assume you have a database in Full recovery mode and you do
not perform transaction log backups. Either set your database to simple
recovery mode or start taking regular transaction log backups.
Hope this helps
John
"B.J." wrote:
[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database / f
iles
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to trunca
te
> log to 35 MB. I did all combination of options in all task / shrink databa
se
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>|||I did it and nothing. Read all about how to truncate log from help which is
installed to SQL Server 2000 but it does not work.
"Uri Dimant" wrote:
> BJ
> 1) Backup Log file (to truncate a commited transaction (inactive portion)
> 2) Run DBCC SHRINKFILE
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> files
> truncate
> database
> log
>
>
My real DB size is 5 MB and transaction log 30 MB. But transaction log
allocated 900 MB. And if I run
DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
it does not shrink me transaction log neither a bit. Why '''?
I do not want to have so big transaction log.BJ
Run DBCC SHRINKFILE instead
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> Hi,
> My real DB size is 5 MB and transaction log 30 MB. But transaction log
> allocated 900 MB. And if I run
> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> it does not shrink me transaction log neither a bit. Why
'''?
> I do not want to have so big transaction log.|||I tried
DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
and nothing happened.
"Uri Dimant" wrote:
> BJ
> Run DBCC SHRINKFILE instead
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> '''?
>
>|||B.J
What are you really trying to do?
You have emptied the file, have you tried to remove it by using EM?
Are you trying to reduce the size of the log file?
Are you trying to delete/remove the log file?
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...[vbcol=seagreen]
> I tried
> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> and nothing happened.
> "Uri Dimant" wrote:
>|||I want to reduce transaction log size.
when I right click my database and point to all task / shrink database / fil
es
then I have :
Data
current size : 5 MB
Space used : 4 MB
Log
current size : 900,62 MB
Space used : 33,4 MB
And this does not like me. I have allocated to much size. I want to truncate
log to 35 MB. I did all combination of options in all task / shrink database
and no result and also I have not set minimal transaction log so I do not
know wehre is problem.
Hope I said you what you want.
"Uri Dimant" wrote:
> B.J
> What are you really trying to do?
> You have emptied the file, have you tried to remove it by using EM?
> Are you trying to reduce the size of the log file?
> Are you trying to delete/remove the log file?
>
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
>
>|||BJ
1) Backup Log file (to truncate a commited transaction (inactive portion)
2) Run DBCC SHRINKFILE
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database /
files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
truncate
> log to 35 MB. I did all combination of options in all task / shrink
database[vbcol=seagreen]
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>
log[vbcol=seagreen]|||Hi B.J.
Try to look up BACKUP LOG and DBCC SHRINKFILE in BOL and make sure that you
do the right things. It could be that you haven't truncated the logfile
correctly and/or haven't shrinked the logfile but the database.
Regards
Steen
B.J. wrote:[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink
> database / files then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
> truncate log to 35 MB. I did all combination of options in all task /
> shrink database and no result and also I have not set minimal
> transaction log so I do not know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>|||B J
Have a look at these articles on shrinking the Transaction log.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Usually a transaction log only needs to be around 10 - 20% of the size of
the database. I assume you have a database in Full recovery mode and you do
not perform transaction log backups. Either set your database to simple
recovery mode or start taking regular transaction log backups.
Hope this helps
John
"B.J." wrote:
[vbcol=seagreen]
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database / f
iles
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to trunca
te
> log to 35 MB. I did all combination of options in all task / shrink databa
se
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>|||I did it and nothing. Read all about how to truncate log from help which is
installed to SQL Server 2000 but it does not work.
"Uri Dimant" wrote:
> BJ
> 1) Backup Log file (to truncate a commited transaction (inactive portion)
> 2) Run DBCC SHRINKFILE
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> files
> truncate
> database
> log
>
>
Bug in DBCC SHRINKDATABASE ?
Hi,
My real DB size is 5 MB and transaction log 30 MB. But transaction log
allocated 900 MB. And if I run
DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
it does not shrink me transaction log neither a bit. Why '''?
I do not want to have so big transaction log.BJ
Run DBCC SHRINKFILE instead
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> Hi,
> My real DB size is 5 MB and transaction log 30 MB. But transaction log
> allocated 900 MB. And if I run
> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> it does not shrink me transaction log neither a bit. Why
'''?
> I do not want to have so big transaction log.|||I tried
DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
and nothing happened.
"Uri Dimant" wrote:
> BJ
> Run DBCC SHRINKFILE instead
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > Hi,
> >
> > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > allocated 900 MB. And if I run
> >
> > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> >
> > it does not shrink me transaction log neither a bit. Why
> '''?
> > I do not want to have so big transaction log.
>
>|||B.J
What are you really trying to do?
You have emptied the file, have you tried to remove it by using EM?
Are you trying to reduce the size of the log file?
Are you trying to delete/remove the log file?
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> I tried
> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> and nothing happened.
> "Uri Dimant" wrote:
> > BJ
> > Run DBCC SHRINKFILE instead
> >
> > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > Hi,
> > >
> > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > allocated 900 MB. And if I run
> > >
> > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > >
> > > it does not shrink me transaction log neither a bit. Why
> > '''?
> > > I do not want to have so big transaction log.
> >
> >
> >|||I want to reduce transaction log size.
when I right click my database and point to all task / shrink database / files
then I have :
Data
current size : 5 MB
Space used : 4 MB
Log
current size : 900,62 MB
Space used : 33,4 MB
And this does not like me. I have allocated to much size. I want to truncate
log to 35 MB. I did all combination of options in all task / shrink database
and no result and also I have not set minimal transaction log so I do not
know wehre is problem.
Hope I said you what you want.
"Uri Dimant" wrote:
> B.J
> What are you really trying to do?
> You have emptied the file, have you tried to remove it by using EM?
> Are you trying to reduce the size of the log file?
> Are you trying to delete/remove the log file?
>
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > I tried
> >
> > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> >
> > and nothing happened.
> >
> > "Uri Dimant" wrote:
> >
> > > BJ
> > > Run DBCC SHRINKFILE instead
> > >
> > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > Hi,
> > > >
> > > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > > allocated 900 MB. And if I run
> > > >
> > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > >
> > > > it does not shrink me transaction log neither a bit. Why
> > > '''?
> > > > I do not want to have so big transaction log.
> > >
> > >
> > >
>
>|||BJ
1) Backup Log file (to truncate a commited transaction (inactive portion)
2) Run DBCC SHRINKFILE
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database /
files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
truncate
> log to 35 MB. I did all combination of options in all task / shrink
database
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
> > B.J
> > What are you really trying to do?
> > You have emptied the file, have you tried to remove it by using EM?
> >
> > Are you trying to reduce the size of the log file?
> >
> > Are you trying to delete/remove the log file?
> >
> >
> >
> >
> >
> >
> > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > I tried
> > >
> > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > >
> > > and nothing happened.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > BJ
> > > > Run DBCC SHRINKFILE instead
> > > >
> > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction
log
> > > > > allocated 900 MB. And if I run
> > > > >
> > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > >
> > > > > it does not shrink me transaction log neither a bit. Why
> > > > '''?
> > > > > I do not want to have so big transaction log.
> > > >
> > > >
> > > >
> >
> >
> >|||Hi B.J.
Try to look up BACKUP LOG and DBCC SHRINKFILE in BOL and make sure that you
do the right things. It could be that you haven't truncated the logfile
correctly and/or haven't shrinked the logfile but the database.
Regards
Steen
B.J. wrote:
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink
> database / files then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
> truncate log to 35 MB. I did all combination of options in all task /
> shrink database and no result and also I have not set minimal
> transaction log so I do not know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>> B.J
>> What are you really trying to do?
>> You have emptied the file, have you tried to remove it by using EM?
>> Are you trying to reduce the size of the log file?
>> Are you trying to delete/remove the log file?
>>
>>
>>
>> "B.J." <BJ@.discussions.microsoft.com> wrote in message
>> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
>> I tried
>> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
>> and nothing happened.
>> "Uri Dimant" wrote:
>> BJ
>> Run DBCC SHRINKFILE instead
>> "B.J." <BJ@.discussions.microsoft.com> wrote in message
>> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
>> Hi,
>> My real DB size is 5 MB and transaction log 30 MB. But
>> transaction log allocated 900 MB. And if I run
>> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
>> it does not shrink me transaction log neither a bit. Why
>> '''? I do not want to have so big transaction log.|||B J
Have a look at these articles on shrinking the Transaction log.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Usually a transaction log only needs to be around 10 - 20% of the size of
the database. I assume you have a database in Full recovery mode and you do
not perform transaction log backups. Either set your database to simple
recovery mode or start taking regular transaction log backups.
Hope this helps
John
"B.J." wrote:
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database / files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to truncate
> log to 35 MB. I did all combination of options in all task / shrink database
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
> > B.J
> > What are you really trying to do?
> > You have emptied the file, have you tried to remove it by using EM?
> >
> > Are you trying to reduce the size of the log file?
> >
> > Are you trying to delete/remove the log file?
> >
> >
> >
> >
> >
> >
> > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > I tried
> > >
> > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > >
> > > and nothing happened.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > BJ
> > > > Run DBCC SHRINKFILE instead
> > > >
> > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > > > allocated 900 MB. And if I run
> > > > >
> > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > >
> > > > > it does not shrink me transaction log neither a bit. Why
> > > > '''?
> > > > > I do not want to have so big transaction log.
> > > >
> > > >
> > > >
> >
> >
> >|||I did it and nothing. Read all about how to truncate log from help which is
installed to SQL Server 2000 but it does not work.
"Uri Dimant" wrote:
> BJ
> 1) Backup Log file (to truncate a commited transaction (inactive portion)
> 2) Run DBCC SHRINKFILE
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> > I want to reduce transaction log size.
> >
> > when I right click my database and point to all task / shrink database /
> files
> > then I have :
> > Data
> > current size : 5 MB
> > Space used : 4 MB
> >
> > Log
> > current size : 900,62 MB
> > Space used : 33,4 MB
> >
> >
> > And this does not like me. I have allocated to much size. I want to
> truncate
> > log to 35 MB. I did all combination of options in all task / shrink
> database
> > and no result and also I have not set minimal transaction log so I do not
> > know wehre is problem.
> > Hope I said you what you want.
> >
> > "Uri Dimant" wrote:
> >
> > > B.J
> > > What are you really trying to do?
> > > You have emptied the file, have you tried to remove it by using EM?
> > >
> > > Are you trying to reduce the size of the log file?
> > >
> > > Are you trying to delete/remove the log file?
> > >
> > >
> > >
> > >
> > >
> > >
> > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > > I tried
> > > >
> > > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > > >
> > > > and nothing happened.
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > BJ
> > > > > Run DBCC SHRINKFILE instead
> > > > >
> > > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction
> log
> > > > > > allocated 900 MB. And if I run
> > > > > >
> > > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > > >
> > > > > > it does not shrink me transaction log neither a bit. Why
> > > > > '''?
> > > > > > I do not want to have so big transaction log.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Thank you,
KB help me, exactly
DBCC SHRINKFILE('N_Log', 0);
BACKUP LOG N WITH TRUNCATE_ONLY
DBCC SHRINKFILE('N', 0);
Have a nice day :)
"John Bandettini" wrote:
> B J
> Have a look at these articles on shrinking the Transaction log.
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
> Usually a transaction log only needs to be around 10 - 20% of the size of
> the database. I assume you have a database in Full recovery mode and you do
> not perform transaction log backups. Either set your database to simple
> recovery mode or start taking regular transaction log backups.
> Hope this helps
> John
> "B.J." wrote:
> > I want to reduce transaction log size.
> >
> > when I right click my database and point to all task / shrink database / files
> > then I have :
> > Data
> > current size : 5 MB
> > Space used : 4 MB
> >
> > Log
> > current size : 900,62 MB
> > Space used : 33,4 MB
> >
> >
> > And this does not like me. I have allocated to much size. I want to truncate
> > log to 35 MB. I did all combination of options in all task / shrink database
> > and no result and also I have not set minimal transaction log so I do not
> > know wehre is problem.
> > Hope I said you what you want.
> >
> > "Uri Dimant" wrote:
> >
> > > B.J
> > > What are you really trying to do?
> > > You have emptied the file, have you tried to remove it by using EM?
> > >
> > > Are you trying to reduce the size of the log file?
> > >
> > > Are you trying to delete/remove the log file?
> > >
> > >
> > >
> > >
> > >
> > >
> > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > > I tried
> > > >
> > > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > > >
> > > > and nothing happened.
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > BJ
> > > > > Run DBCC SHRINKFILE instead
> > > > >
> > > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > > > > allocated 900 MB. And if I run
> > > > > >
> > > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > > >
> > > > > > it does not shrink me transaction log neither a bit. Why
> > > > > '''?
> > > > > > I do not want to have so big transaction log.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
My real DB size is 5 MB and transaction log 30 MB. But transaction log
allocated 900 MB. And if I run
DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
it does not shrink me transaction log neither a bit. Why '''?
I do not want to have so big transaction log.BJ
Run DBCC SHRINKFILE instead
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> Hi,
> My real DB size is 5 MB and transaction log 30 MB. But transaction log
> allocated 900 MB. And if I run
> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> it does not shrink me transaction log neither a bit. Why
'''?
> I do not want to have so big transaction log.|||I tried
DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
and nothing happened.
"Uri Dimant" wrote:
> BJ
> Run DBCC SHRINKFILE instead
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > Hi,
> >
> > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > allocated 900 MB. And if I run
> >
> > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> >
> > it does not shrink me transaction log neither a bit. Why
> '''?
> > I do not want to have so big transaction log.
>
>|||B.J
What are you really trying to do?
You have emptied the file, have you tried to remove it by using EM?
Are you trying to reduce the size of the log file?
Are you trying to delete/remove the log file?
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> I tried
> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> and nothing happened.
> "Uri Dimant" wrote:
> > BJ
> > Run DBCC SHRINKFILE instead
> >
> > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > Hi,
> > >
> > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > allocated 900 MB. And if I run
> > >
> > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > >
> > > it does not shrink me transaction log neither a bit. Why
> > '''?
> > > I do not want to have so big transaction log.
> >
> >
> >|||I want to reduce transaction log size.
when I right click my database and point to all task / shrink database / files
then I have :
Data
current size : 5 MB
Space used : 4 MB
Log
current size : 900,62 MB
Space used : 33,4 MB
And this does not like me. I have allocated to much size. I want to truncate
log to 35 MB. I did all combination of options in all task / shrink database
and no result and also I have not set minimal transaction log so I do not
know wehre is problem.
Hope I said you what you want.
"Uri Dimant" wrote:
> B.J
> What are you really trying to do?
> You have emptied the file, have you tried to remove it by using EM?
> Are you trying to reduce the size of the log file?
> Are you trying to delete/remove the log file?
>
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > I tried
> >
> > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> >
> > and nothing happened.
> >
> > "Uri Dimant" wrote:
> >
> > > BJ
> > > Run DBCC SHRINKFILE instead
> > >
> > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > Hi,
> > > >
> > > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > > allocated 900 MB. And if I run
> > > >
> > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > >
> > > > it does not shrink me transaction log neither a bit. Why
> > > '''?
> > > > I do not want to have so big transaction log.
> > >
> > >
> > >
>
>|||BJ
1) Backup Log file (to truncate a commited transaction (inactive portion)
2) Run DBCC SHRINKFILE
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database /
files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
truncate
> log to 35 MB. I did all combination of options in all task / shrink
database
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
> > B.J
> > What are you really trying to do?
> > You have emptied the file, have you tried to remove it by using EM?
> >
> > Are you trying to reduce the size of the log file?
> >
> > Are you trying to delete/remove the log file?
> >
> >
> >
> >
> >
> >
> > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > I tried
> > >
> > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > >
> > > and nothing happened.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > BJ
> > > > Run DBCC SHRINKFILE instead
> > > >
> > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction
log
> > > > > allocated 900 MB. And if I run
> > > > >
> > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > >
> > > > > it does not shrink me transaction log neither a bit. Why
> > > > '''?
> > > > > I do not want to have so big transaction log.
> > > >
> > > >
> > > >
> >
> >
> >|||Hi B.J.
Try to look up BACKUP LOG and DBCC SHRINKFILE in BOL and make sure that you
do the right things. It could be that you haven't truncated the logfile
correctly and/or haven't shrinked the logfile but the database.
Regards
Steen
B.J. wrote:
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink
> database / files then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to
> truncate log to 35 MB. I did all combination of options in all task /
> shrink database and no result and also I have not set minimal
> transaction log so I do not know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
>> B.J
>> What are you really trying to do?
>> You have emptied the file, have you tried to remove it by using EM?
>> Are you trying to reduce the size of the log file?
>> Are you trying to delete/remove the log file?
>>
>>
>>
>> "B.J." <BJ@.discussions.microsoft.com> wrote in message
>> news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
>> I tried
>> DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
>> and nothing happened.
>> "Uri Dimant" wrote:
>> BJ
>> Run DBCC SHRINKFILE instead
>> "B.J." <BJ@.discussions.microsoft.com> wrote in message
>> news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
>> Hi,
>> My real DB size is 5 MB and transaction log 30 MB. But
>> transaction log allocated 900 MB. And if I run
>> DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
>> it does not shrink me transaction log neither a bit. Why
>> '''? I do not want to have so big transaction log.|||B J
Have a look at these articles on shrinking the Transaction log.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Usually a transaction log only needs to be around 10 - 20% of the size of
the database. I assume you have a database in Full recovery mode and you do
not perform transaction log backups. Either set your database to simple
recovery mode or start taking regular transaction log backups.
Hope this helps
John
"B.J." wrote:
> I want to reduce transaction log size.
> when I right click my database and point to all task / shrink database / files
> then I have :
> Data
> current size : 5 MB
> Space used : 4 MB
> Log
> current size : 900,62 MB
> Space used : 33,4 MB
>
> And this does not like me. I have allocated to much size. I want to truncate
> log to 35 MB. I did all combination of options in all task / shrink database
> and no result and also I have not set minimal transaction log so I do not
> know wehre is problem.
> Hope I said you what you want.
> "Uri Dimant" wrote:
> > B.J
> > What are you really trying to do?
> > You have emptied the file, have you tried to remove it by using EM?
> >
> > Are you trying to reduce the size of the log file?
> >
> > Are you trying to delete/remove the log file?
> >
> >
> >
> >
> >
> >
> > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > I tried
> > >
> > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > >
> > > and nothing happened.
> > >
> > > "Uri Dimant" wrote:
> > >
> > > > BJ
> > > > Run DBCC SHRINKFILE instead
> > > >
> > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > > > allocated 900 MB. And if I run
> > > > >
> > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > >
> > > > > it does not shrink me transaction log neither a bit. Why
> > > > '''?
> > > > > I do not want to have so big transaction log.
> > > >
> > > >
> > > >
> >
> >
> >|||I did it and nothing. Read all about how to truncate log from help which is
installed to SQL Server 2000 but it does not work.
"Uri Dimant" wrote:
> BJ
> 1) Backup Log file (to truncate a commited transaction (inactive portion)
> 2) Run DBCC SHRINKFILE
>
>
> "B.J." <BJ@.discussions.microsoft.com> wrote in message
> news:4E607247-C485-468F-B5E7-7EAFAA1C03D9@.microsoft.com...
> > I want to reduce transaction log size.
> >
> > when I right click my database and point to all task / shrink database /
> files
> > then I have :
> > Data
> > current size : 5 MB
> > Space used : 4 MB
> >
> > Log
> > current size : 900,62 MB
> > Space used : 33,4 MB
> >
> >
> > And this does not like me. I have allocated to much size. I want to
> truncate
> > log to 35 MB. I did all combination of options in all task / shrink
> database
> > and no result and also I have not set minimal transaction log so I do not
> > know wehre is problem.
> > Hope I said you what you want.
> >
> > "Uri Dimant" wrote:
> >
> > > B.J
> > > What are you really trying to do?
> > > You have emptied the file, have you tried to remove it by using EM?
> > >
> > > Are you trying to reduce the size of the log file?
> > >
> > > Are you trying to delete/remove the log file?
> > >
> > >
> > >
> > >
> > >
> > >
> > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > > I tried
> > > >
> > > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > > >
> > > > and nothing happened.
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > BJ
> > > > > Run DBCC SHRINKFILE instead
> > > > >
> > > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction
> log
> > > > > > allocated 900 MB. And if I run
> > > > > >
> > > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > > >
> > > > > > it does not shrink me transaction log neither a bit. Why
> > > > > '''?
> > > > > > I do not want to have so big transaction log.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Thank you,
KB help me, exactly
DBCC SHRINKFILE('N_Log', 0);
BACKUP LOG N WITH TRUNCATE_ONLY
DBCC SHRINKFILE('N', 0);
Have a nice day :)
"John Bandettini" wrote:
> B J
> Have a look at these articles on shrinking the Transaction log.
> INF: How to Shrink the SQL Server 7.0 Transaction Log
> http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
> Usually a transaction log only needs to be around 10 - 20% of the size of
> the database. I assume you have a database in Full recovery mode and you do
> not perform transaction log backups. Either set your database to simple
> recovery mode or start taking regular transaction log backups.
> Hope this helps
> John
> "B.J." wrote:
> > I want to reduce transaction log size.
> >
> > when I right click my database and point to all task / shrink database / files
> > then I have :
> > Data
> > current size : 5 MB
> > Space used : 4 MB
> >
> > Log
> > current size : 900,62 MB
> > Space used : 33,4 MB
> >
> >
> > And this does not like me. I have allocated to much size. I want to truncate
> > log to 35 MB. I did all combination of options in all task / shrink database
> > and no result and also I have not set minimal transaction log so I do not
> > know wehre is problem.
> > Hope I said you what you want.
> >
> > "Uri Dimant" wrote:
> >
> > > B.J
> > > What are you really trying to do?
> > > You have emptied the file, have you tried to remove it by using EM?
> > >
> > > Are you trying to reduce the size of the log file?
> > >
> > > Are you trying to delete/remove the log file?
> > >
> > >
> > >
> > >
> > >
> > >
> > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > news:8404C0B7-1D3C-4B71-B22B-D5EEC6C12A69@.microsoft.com...
> > > > I tried
> > > >
> > > > DBCC SHRINKFILE ('IDFROMSysfile..', EMPTYFILE);
> > > >
> > > > and nothing happened.
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > BJ
> > > > > Run DBCC SHRINKFILE instead
> > > > >
> > > > > "B.J." <BJ@.discussions.microsoft.com> wrote in message
> > > > > news:0D8B571A-0F7D-4F47-AD2C-3B1FEEB7924B@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > My real DB size is 5 MB and transaction log 30 MB. But transaction log
> > > > > > allocated 900 MB. And if I run
> > > > > >
> > > > > > DBCC SHRINKDATABASE (MyDB, 0, TRUNCATEONLY)
> > > > > >
> > > > > > it does not shrink me transaction log neither a bit. Why
> > > > > '''?
> > > > > > I do not want to have so big transaction log.
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
Subscribe to:
Posts (Atom)