Cannot write a small row! Please help...

Hi everyone,
This is weird. Please help!
I have a long row in the form:
WordA WordB WordC WordD etc...
The above row is 5,294 bytes in size.
I got the soundex equivalent of this row. It now became:
SoundexA SoundexB SoundexC SoundexD etc...
It's size is now 2,898 bytes. AS you would expect as soundex returns a
4 letter code.
I attempt to write the soundex field to its place in the database. I
get the followign error message:
Cannot create a row of size 9602 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
Now, I am totally confused - Does anyone know why this is happening?
Any ideas/suggestions/comments/diagnostics that I could run would be
great. Please help me!
Confused,
Al.Could it be that your possible row size is exceeded ? Not the actual
used one, but the highest possible. Therefore you should have gotten an
error while creating or altering the table once upon a time.
SELECT Sum(ISNULL(CHARACTER_MAXIMUM_LENGTH,length)) from
INFORMATION_SCHEMA.Columns col
INNER join systypes
on systypes.name = col.Data_type
Where table_name = 'YourTable'
HTH, jens Suessmeyer.|||Thanks jens,
I ran this cool command on my table and get the answer: 8392
Unfortunately I don't know where it is! Can you help me locate it or
suggest possible solutions? Can you recommend what I should do please?
Any assistance much appreciated.
Thank you,
Al.|||Sure, you should narrow down your datatypes that these fit on a
datapage of 8060 bytes. In some cases some columns use wasted datatypes
as somebody just assumes how many bytes are to go in that column. If
there is in some cases reengineering,l the people narrow down the
datatypes because they know the actual data size of the columns later.
Or you can split out the table to some "Soundex table" qwhich
references 1by1 the original table and just point with the same PK on
this.
HTH, jens Suessmeyer.

Cannot write a small row! Please help...

Hi everyone,
This is weird. Please help!
I have a long row in the form:
WordAWordBWordCWordD etc...
The above row is 5,294 bytes in size.
I got the soundex equivalent of this row. It now became:
SoundexASoundexBSoundexCSoundexD etc...
It's size is now 2,898 bytes. AS you would expect as soundex returns a
4 letter code.
I attempt to write the soundex field to its place in the database. I
get the followign error message:
Cannot create a row of size 9602 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
Now, I am totally confused - Does anyone know why this is happening?
Any ideas/suggestions/comments/diagnostics that I could run would be
great. Please help me!
Confused,
Al.
Could it be that your possible row size is exceeded ? Not the actual
used one, but the highest possible. Therefore you should have gotten an
error while creating or altering the table once upon a time.
SELECT Sum(ISNULL(CHARACTER_MAXIMUM_LENGTH,length)) from
INFORMATION_SCHEMA.Columns col
INNER join systypes
on systypes.name = col.Data_type
Where table_name = 'YourTable'
HTH, jens Suessmeyer.
|||Thanks jens,
I ran this cool command on my table and get the answer: 8392
Unfortunately I don't know where it is! Can you help me locate it or
suggest possible solutions? Can you recommend what I should do please?
Any assistance much appreciated.
Thank you,
Al.
|||Sure, you should narrow down your datatypes that these fit on a
datapage of 8060 bytes. In some cases some columns use wasted datatypes
as somebody just assumes how many bytes are to go in that column. If
there is in some cases reengineering,l the people narrow down the
datatypes because they know the actual data size of the columns later.
Or you can split out the table to some "Soundex table" qwhich
references 1by1 the original table and just point with the same PK on
this.
HTH, jens Suessmeyer.

Cannot write a small row! Please help...

Hi everyone,
This is weird. Please help!
I have a long row in the form:
WordA WordB WordC WordD etc...
The above row is 5,294 bytes in size.
I got the soundex equivalent of this row. It now became:
SoundexA SoundexB SoundexC SoundexD etc...
It's size is now 2,898 bytes. AS you would expect as soundex returns a
4 letter code.
I attempt to write the soundex field to its place in the database. I
get the followign error message:
Cannot create a row of size 9602 which is greater than the allowable
maximum of 8060.
The statement has been terminated.
Now, I am totally confused - Does anyone know why this is happening?
Any ideas/suggestions/comments/diagnostics that I could run would be
great. Please help me!
Confused,
Al.Could it be that your possible row size is exceeded ? Not the actual
used one, but the highest possible. Therefore you should have gotten an
error while creating or altering the table once upon a time.
SELECT Sum(ISNULL(CHARACTER_MAXIMUM_LENGTH,leng
th)) from
INFORMATION_SCHEMA.Columns col
INNER join systypes
on systypes.name = col.Data_type
Where table_name = 'YourTable'
HTH, jens Suessmeyer.|||Thanks jens,
I ran this cool command on my table and get the answer: 8392
Unfortunately I don't know where it is! Can you help me locate it or
suggest possible solutions? Can you recommend what I should do please?
Any assistance much appreciated.
Thank you,
Al.|||Sure, you should narrow down your datatypes that these fit on a
datapage of 8060 bytes. In some cases some columns use wasted datatypes
as somebody just assumes how many bytes are to go in that column. If
there is in some cases reengineering,l the people narrow down the
datatypes because they know the actual data size of the columns later.
Or you can split out the table to some "Soundex table" qwhich
references 1by1 the original table and just point with the same PK on
this.
HTH, jens Suessmeyer.

Cannot use backup file %s. Originally formatted sector size 512 and now device sector size

Customer is trying to restore a backup of their database. SQL Server gives
the error:
Cannot use backup file %s.
Originally formatted sector size 512 and now device sector size 2048.
Only one guy (in Germany in March 2000) has ever seen this error. And he
never got a resolution.
(already posted in m.p.s.msde group)Figured it out. He was trying to restore the backup from off a CD. Copy the
file to the local hard drive, and it worked.
i've also seen that if you try restoring the backup across a network, it can
also fix it.
References:
Error: 3268
Severity: 16
Description: Cannot use the backup file '%ls' because it was originally
formatted with sector size %d and is now on a device with sector size %d.
http://ddbcinc.com/askDDBC/topic.asp?TOPIC_ID=272
Posted - 05/04/2004 : 10:36:06
----
I backed up a SQL Database from a SQL Standard Editon. Now I am trying to
restore to a Desktop Edition.
While Restoring, I get the below error:
[SQL Server] szSqlState = 37000,fNativeError = 3268, errMsg =[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the backup file
'DBASQL_DB_testdb_PID3732TID3216BID0TS96cac8VD0' because it was originally
formatted with sector size 512)
Any ideas
Thanks
Santhosh
From Books On Line:
"SQL Server uses nonbuffered I/O, which requires sectors to be aligned. You
must restore the backup set from a disk with the same sector size, or over a
network, which uses buffered I/O. Alternatively, you can specify a WITH
BLOCKSIZE clause when you back up the database."
If you can take a new backup of the existing database, use the WITH
BLOCKSIZE clause, specifying the block size of the Desktop Edition machine's
device. If you cannot take a fresh backup, you can try restoring it over the
network, though this will obviously be significantly slower.
askDDBC
http://www.windowsitpro.com/Article/ArticleID/14364/14364.html
[December 21, 1999]
Why do I get the error "Cannot use file <logfile> because it was originally
formatted with sector size <xxx> and is now on a device with sector size
<yyy> " when I move SQL Server's log file?
InstantDoc #14364
A. This only occurs with SQL 7.x, not 6.x. For performance/consistency
reasons the log writes are aligned on physical sector boundaries. 95%+ of
disks will have the same physical sector size, but if you try and move
between different vendor raid systems, or utilise some form of NAS device
then you may see this problem.
The only work-around is to re-create the log file on the new device and
backup/restore the database.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:u7176xMMGHA.3052@.TK2MSFTNGP10.phx.gbl...
> Customer is trying to restore a backup of their database. SQL Server gives
> the error:
> Cannot use backup file %s.
> Originally formatted sector size 512 and now device sector size 2048.
> Only one guy (in Germany in March 2000) has ever seen this error. And he
> never got a resolution.
>

Cannot use backup file %s. Originally formatted sector size 512 and now device sector

Customer is trying to restore a backup of their database. SQL Server gives
the error:
Cannot use backup file %s.
Originally formatted sector size 512 and now device sector size 2048.
Only one guy (in Germany in March 2000) has ever seen this error. And he
never got a resolution.
(already posted in m.p.s.msde group)Figured it out. He was trying to restore the backup from off a CD. Copy the
file to the local hard drive, and it worked.
i've also seen that if you try restoring the backup across a network, it can
also fix it.
References:
Error: 3268
Severity: 16
Description: Cannot use the backup file '%ls' because it was originally
formatted with sector size %d and is now on a device with sector size %d.
http://ddbcinc.com/askDDBC/topic.asp?TOPIC_ID=272
Posted - 05/04/2004 : 10:36:06
----
--
I backed up a SQL Database from a SQL Standard Editon. Now I am trying to
restore to a Desktop Edition.
While Restoring, I get the below error:
[SQL Server] szSqlState = 37000,fNativeError = 3268, errMsg =
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the ba
ckup file
'DBASQL_DB_testdb_PID3732TID3216BID0TS96
cac8VD0' because it was originally
formatted with sector size 512)
Any ideas
Thanks
Santhosh
From Books On Line:
"SQL Server uses nonbuffered I/O, which requires sectors to be aligned. You
must restore the backup set from a disk with the same sector size, or over a
network, which uses buffered I/O. Alternatively, you can specify a WITH
BLOCKSIZE clause when you back up the database."
If you can take a new backup of the existing database, use the WITH
BLOCKSIZE clause, specifying the block size of the Desktop Edition machine's
device. If you cannot take a fresh backup, you can try restoring it over the
network, though this will obviously be significantly slower.
askDDBC
http://www.windowsitpro.com/Article...4364/14364.html
[December 21, 1999]
Why do I get the error "Cannot use file <logfile> because it was originally
formatted with sector size <xxx> and is now on a device with sector size
<yyy> " when I move SQL Server's log file?
InstantDoc #14364
A. This only occurs with SQL 7.x, not 6.x. For performance/consistency
reasons the log writes are aligned on physical sector boundaries. 95%+ of
disks will have the same physical sector size, but if you try and move
between different vendor raid systems, or utilise some form of NAS device
then you may see this problem.
The only work-around is to re-create the log file on the new device and
backup/restore the database.
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:u7176xMMGHA.3052@.TK2MSFTNGP10.phx.gbl...
> Customer is trying to restore a backup of their database. SQL Server gives
> the error:
> Cannot use backup file %s.
> Originally formatted sector size 512 and now device sector size 2048.
> Only one guy (in Germany in March 2000) has ever seen this error. And he
> never got a resolution.
>

Cannot truncate logfile in SQL server 2000

Nilkanth Desai
nsdesai@.tis-world.com
I am using SQL Server 2000. In this case database size is 750 MB and since
last 10 days its logfile is constantly increasing its size. Since last 2 day
s
it has crossed 14GB. Thses logfiles are having extension of .ldf. I have
tried shrink database option from Enterprise manager and used DBCC from quer
y
analyser. But this logfile did not truncated.
Can anyone tell me solution for this problem.8f84bacebad4b881" target="_blank">http://groups.google.com.au/group/m...f84bacebad4b881
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Nilkanth Desai" <Nilkanth Desai@.discussions.microsoft.com> wrote in message
news:D70F6F79-84F6-4F9F-B514-885FB86D8B18@.microsoft.com...
> Nilkanth Desai
> nsdesai@.tis-world.com
> I am using SQL Server 2000. In this case database size is 750 MB and since
> last 10 days its logfile is constantly increasing its size. Since last 2
> days
> it has crossed 14GB. Thses logfiles are having extension of .ldf. I have
> tried shrink database option from Enterprise manager and used DBCC from
> query
> analyser. But this logfile did not truncated.
> Can anyone tell me solution for this problem.

Cannot sort a row which is greater than 8094?

I've recently started getting the following error when I retrieve
long text entries from my MS SQL Server database...
"Cannot sort a row of size 8150, which is greater than the allowable
maximum of 8094"
I have one column in one of the tables involved in the SELECT statement
that has a maximum of 8000 characters.
Should I resize this column to 7800 or similar, to avoid this problem,
or should I modify my SELECT statement to ignore the errors and proceed
as normal?
Thanks.
--
fiddlewidawiddumInformation from the SQL Server Books Onine (SELECT, ORDER BY clause):
There is no limit to the number of items in the ORDER BY clause. However,
there is a limit of 8,060 bytes for the row size of intermediate worktables
needed for sort operations. This limits the total size of columns specified
in an ORDER BY clause.
You should set the column width accordingly.
Martin C K Poon
Senior Analyst Programmer
====================================
"Stimp" <ren@.spumco.com> bl
news:slrne3u0f8.vf.ren@.carbon.redbrick.dcu.ie g...
> I've recently started getting the following error when I retrieve
> long text entries from my MS SQL Server database...
> "Cannot sort a row of size 8150, which is greater than the allowable
> maximum of 8094"
> I have one column in one of the tables involved in the SELECT statement
> that has a maximum of 8000 characters.
> Should I resize this column to 7800 or similar, to avoid this problem,
> or should I modify my SELECT statement to ignore the errors and proceed
> as normal?
> Thanks.
> --
> fiddlewidawiddum|||Do you have to order by the full length of 8000 character column? Maybe
left(<colum>, 2000)
could be just fine.
ML
http://milambda.blogspot.com/|||On Fri, 14 Apr 2006 ML <ML@.discussions.microsoft.com> wrote:
> Do you have to order by the full length of 8000 character column? Maybe
> left(<colum>, 2000)
I order by the unique identifier column... not the varchar column.
I get the impression that the SELECT statement is pulling back several
rows (including the 8000 character one) before it sorts through them and
so gives me this error if the 8000 character column is filled to its
maximum capacity AND another varchar column (which is included in
the SELECT statement) has more than 94 chars.
fiddlewidawiddum|||Well, post the query then, so we can have a look.
Sorting is done implicitly by the Query Optimizer when GROUP BY or DISTINCT
is used in the query.
ML
http://milambda.blogspot.com/|||On Fri, 14 Apr 2006 ML <ML@.discussions.microsoft.com> wrote:
> Well, post the query then, so we can have a look.
> Sorting is done implicitly by the Query Optimizer when GROUP BY or DISTINC
T
> is used in the query.
SELECT TOP 6 d.EntryTitle, d.EntryText, d.EntryDate, l.LocName, c.CountryNam
e
FROM Diary d, Location l, Country c
WHERE d.idLocation = l.idLocation AND
d.idCountry = c.idCountry
ORDER BY d.idDiary desc
EntryTitle is 255 chars long (although I'll probably resize this to 65)
EntryText was 8000 chars long, but I've since resized it to 7900
fiddlewidawiddum|||Judging by the simplicity of the query, I guess the problem originates in th
e
execution plan. Try this:
SELECT TOP 6 d.EntryTitle, d.EntryText, d.EntryDate, l.LocName, c.CountryNam
e
FROM Diary d
inner join Location l on d.idLocation = l.idLocation
inner join Country c on d.idCountry = c.idCountry
ORDER BY d.idDiary desc
Here I've used the contemporary join syntax that may help the Query
Optimizer to build a different execution plan.
ML
http://milambda.blogspot.com/|||On Fri, 14 Apr 2006 ML <ML@.discussions.microsoft.com> wrote:
> Judging by the simplicity of the query, I guess the problem originates in
the
> execution plan. Try this:
> SELECT TOP 6 d.EntryTitle, d.EntryText, d.EntryDate, l.LocName, c.CountryN
ame
> FROM Diary d
> inner join Location l on d.idLocation = l.idLocation
> inner join Country c on d.idCountry = c.idCountry
> ORDER BY d.idDiary desc
> Here I've used the contemporary join syntax that may help the Query
> Optimizer to build a different execution plan.
hmm, I assumed that my query would work in a similar way to having
JOINS.
I'll try that if the error crops up again (it's since disappeared once
I resized the column to 7900 and trimmed a few rows that had entries >
7900).
cheers!
--
fiddlewidawiddum|||You could try adding the ROBUST PLAN hint and see if it helps.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stimp" <ren@.spumco.com> wrote in message news:slrne3u0f8.vf.ren@.carbon.redbrick.dcu.ie...[
color=darkred]
> I've recently started getting the following error when I retrieve
> long text entries from my MS SQL Server database...
> "Cannot sort a row of size 8150, which is greater than the allowable
> maximum of 8094"
> I have one column in one of the tables involved in the SELECT statement
> that has a maximum of 8000 characters.
> Should I resize this column to 7800 or similar, to avoid this problem,
> or should I modify my SELECT statement to ignore the errors and proceed
> as normal?
> Thanks.
> --
> fiddlewidawiddum[/color]

Cannot sort a row of size 9966, which is greater than the allowable maximum of 8094.

I understand I received this error because of the SQL Server row-size
limit, but I'm not sure how to fix it. The report query joins three
tables, and brings data from those tables. The problem is each table
has a Notes varchar(7800) field. If I comment out all notes field then
the query runs fine. Any ideas on how report needs to designed so that
all data can be shown?
Query to pull data from each table runs fine, I was wondering if I
should split the query into three queries, and then create a report
with two nested sub reports pulling data from each table. Before I go
down this path, if you have any suggestions or have implemented similar
solutions please share your thoughts.
Thanks
YogeshYou could use a convert and convert the the field to a smaller varchar.
Perhaps enough for them to see some of the note and then use drill through
to allow them to pull up another report that shows the notes for the current
record (search BOL for drill through).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Yogesh" <yogeshprabhu@.hotmail.com> wrote in message
news:1124397578.652639.273140@.g49g2000cwa.googlegroups.com...
>I understand I received this error because of the SQL Server row-size
> limit, but I'm not sure how to fix it. The report query joins three
> tables, and brings data from those tables. The problem is each table
> has a Notes varchar(7800) field. If I comment out all notes field then
> the query runs fine. Any ideas on how report needs to designed so that
> all data can be shown?
> Query to pull data from each table runs fine, I was wondering if I
> should split the query into three queries, and then create a report
> with two nested sub reports pulling data from each table. Before I go
> down this path, if you have any suggestions or have implemented similar
> solutions please share your thoughts.
> Thanks
> Yogesh
>

Cannot sort a row of size 8095, which is greater than the allowable maximum of 8094

Hello,
I receive an error "Cannot sort a row of size 8095, which is greater than
the allowable maximum of 8094." when selecting values from a table in the
database. If I delete the existing data and try it, I'm not able to
reproduce it again. I wonder how could this error have possibly occured in
the first place. Any ideas?
Thanks,
Felix.JHi, Felix
Try running your query with "OPTION (ROBUST PLAN)".
Razvan|||Felix,
I also encountered this problem.
Have you tried the OPTION (ROBUST PLAN)? Does it work?
Julius

Cannot sort a row

i encounter this error..
Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?
why this error occur? can someone explain? how to avoid this? thanks!!i encounter this error..

Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?

why this error occur? can someone explain? how to avoid this? thanks!!

see this ...Link (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58825)

Cannot shrink transaction log

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank YouHave you enabled the transaction checkpoint ? so that it will reduce automatically ?

dave|||Run this script (courtesy of SQL Server Professional magazine)

Sometimes a piece of data blocks the rest of the transaction log from being truncated. This script will solve your problem by created and removing garbage data until the active portion of the log loops to the beginning of the t'log.

Cannot shrink the transaction logs.

I have a SQL Server 2000 SP3 database that has a large transaction
file (Size 9384.99 space used 87MB). I would like to shrink it, but
it's not working. I don't get any errors, the file just doesn't get any
smaller.
I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink
Databases.. Shrink File.. selecting the log file and trying both
"Compress" and "Truncate: options.
I can see a progress bar, so i know its doing something However the
file does not get smaller. And I have no idea why.
Any suggestions (including "go find someone who knows what he's
doing") are welcome
Jim Helfer
WTW Architects
Pittsburgh PACheck out http://www.karaszi.com/SQLServer/info_dont_shrink.asp, and pay special attention to the
section about DBCC LOGINFO.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Helfer" <JimHelfer@.newsgroup.nospam> wrote in message
news:eyczNyvSHHA.1200@.TK2MSFTNGP04.phx.gbl...
> I have a SQL Server 2000 SP3 database that has a large transaction file (Size 9384.99 space
> used 87MB). I would like to shrink it, but it's not working. I don't get any errors, the file
> just doesn't get any smaller.
> I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink Databases.. Shrink File..
> selecting the log file and trying both "Compress" and "Truncate: options.
> I can see a progress bar, so i know its doing something However the file does not get smaller.
> And I have no idea why.
> Any suggestions (including "go find someone who knows what he's doing") are welcome
> Jim Helfer
> WTW Architects
> Pittsburgh PA
>
>

Cannot shrink the transaction logs.

I have a SQL Server 2000 SP3 database that has a large transaction
file (Size 9384.99 space used 87MB). I would like to shrink it, but
it's not working. I don't get any errors, the file just doesn't get any
smaller.
I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink
Databases.. Shrink File.. selecting the log file and trying both
"Compress" and "Truncate: options.
I can see a progress bar, so i know its doing something However the
file does not get smaller. And I have no idea why.
Any suggestions (including "go find someone who knows what he's
doing") are welcome
Jim Helfer
WTW Architects
Pittsburgh PACheck out http://www.karaszi.com/SQLServer/info_dont_shrink.asp, and pay spe
cial attention to the
section about DBCC LOGINFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jim Helfer" <JimHelfer@.newsgroup.nospam> wrote in message
news:eyczNyvSHHA.1200@.TK2MSFTNGP04.phx.gbl...
> I have a SQL Server 2000 SP3 database that has a large transaction file
(Size 9384.99 space
> used 87MB). I would like to shrink it, but it's not working. I don't get
any errors, the file
> just doesn't get any smaller.
> I'm using the Enterprise Manager. Datavbases.. Tasks.. Shrink Databases
. Shrink File..
> selecting the log file and trying both "Compress" and "Truncate: options.
> I can see a progress bar, so i know its doing something However the file
does not get smaller.
> And I have no idea why.
> Any suggestions (including "go find someone who knows what he's doing") a
re welcome
> Jim Helfer
> WTW Architects
> Pittsburgh PA
>
>

Cannot Shrink log file

Database version 2000 SP 4

Database size : 100 GB , Transaction Log 200: GB , Recovery mode: Simple

I am trying to shrink the transaction log and every time I go to options, it gives me a bigger size to shrink to than the existing log size on the EM. There was replication setup on this database and i removed all the articles and restarted Sql server and the server itself as well.

The database backup works fine.

Please let me know how to fix this issue.

thanks,

Mark.

Hi,

http://sqlserver2000.databases.aspfaq.com/how-do-i-reclaim-space-in-sql-server.html

HTh, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Cannot shrink log file

Hello
I am using this command to shrink log files because the size of log files is
going to double from data files and taking all space but i am getting this
message so help me how can i stop these process and what is the save way to
save my data also.
use BygTest
go
exec sp_helpfile
go
dbcc shrinkfile(MBYGDB_log, 10)
go
Message is!
Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in use.
Best regards,
--
Olav
Click SoftTo shrink the log file I use the following:
USE BygTest
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
BACKUP LOG BygTest WITH TRUNCATE_ONLY
dbcc shrinkfile (MBYGDB_log,10)
but in reference to the error message, I don't know the reason :-(
good luck
Javi
"Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> Hello
> I am using this command to shrink log files because the size of log files
> is
> going to double from data files and taking all space but i am getting this
> message so help me how can i stop these process and what is the save way
> to
> save my data also.
> use BygTest
> go
> exec sp_helpfile
> go
> dbcc shrinkfile(MBYGDB_log, 10)
> go
> Message is!
> Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in
> use.
> Best regards,
> --
> Olav
> Click Soft|||Thanks for good tip. i have one last question in this regards that if size of
my data file is 2 124 876 and size of log file is 4 248 987 and i shrink it
to for example 10 424 then what is effect on the database data?
--
Olav
Click Soft
"Javi" wrote:
> To shrink the log file I use the following:
> USE BygTest
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> BACKUP LOG BygTest WITH TRUNCATE_ONLY
> dbcc shrinkfile (MBYGDB_log,10)
> but in reference to the error message, I don't know the reason :-(
> good luck
> Javi
> "Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
> news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> > Hello
> > I am using this command to shrink log files because the size of log files
> > is
> > going to double from data files and taking all space but i am getting this
> > message so help me how can i stop these process and what is the save way
> > to
> > save my data also.
> >
> > use BygTest
> > go
> >
> > exec sp_helpfile
> > go
> >
> > dbcc shrinkfile(MBYGDB_log, 10)
> > go
> >
> > Message is!
> > Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in
> > use.
> >
> > Best regards,
> >
> > --
> > Olav
> > Click Soft
>
>|||Olav
If don't carry about the data then set your database to SIMPLE recovery
mode rather FULL .
With FULL recovery mode you will have ( in order to manage the LOG's size
and not to lose the data) BACKUP LOG file.
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...
> Thanks for good tip. i have one last question in this regards that if size
of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
it
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
> > To shrink the log file I use the following:
> >
> > USE BygTest
> > CHECKPOINT
> > CHECKPOINT
> > CHECKPOINT
> > CHECKPOINT
> > CHECKPOINT
> > BACKUP LOG BygTest WITH TRUNCATE_ONLY
> > dbcc shrinkfile (MBYGDB_log,10)
> >
> > but in reference to the error message, I don't know the reason :-(
> >
> > good luck
> >
> > Javi
> >
> > "Olav" <Olav@.discussions.microsoft.com> escribi? en el mensaje
> > news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> > > Hello
> > > I am using this command to shrink log files because the size of log
files
> > > is
> > > going to double from data files and taking all space but i am getting
this
> > > message so help me how can i stop these process and what is the save
way
> > > to
> > > save my data also.
> > >
> > > use BygTest
> > > go
> > >
> > > exec sp_helpfile
> > > go
> > >
> > > dbcc shrinkfile(MBYGDB_log, 10)
> > > go
> > >
> > > Message is!
> > > Cannot shrink log file 2 (MBYDB_Log) because all logical log files are
in
> > > use.
> > >
> > > Best regards,
> > >
> > > --
> > > Olav
> > > Click Soft
> >
> >
> >|||The data will be still there, but you will be not able to undo changes in
the database, I mean, if you at 7:00 has a job that loads data in the
database and at 10:00 other job deletes some registers, using the log file
you can undo these, but if the data you have is good for you, you don't want
undo the changes, then you don't need to maintain the log file info in order
to undo these changes. Use backup to maintain safe your data. It's better.
HTH
Javi
"Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...
> Thanks for good tip. i have one last question in this regards that if size
> of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
> it
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
>> To shrink the log file I use the following:
>> USE BygTest
>> CHECKPOINT
>> CHECKPOINT
>> CHECKPOINT
>> CHECKPOINT
>> CHECKPOINT
>> BACKUP LOG BygTest WITH TRUNCATE_ONLY
>> dbcc shrinkfile (MBYGDB_log,10)
>> but in reference to the error message, I don't know the reason :-(
>> good luck
>> Javi
>> "Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
>> news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
>> > Hello
>> > I am using this command to shrink log files because the size of log
>> > files
>> > is
>> > going to double from data files and taking all space but i am getting
>> > this
>> > message so help me how can i stop these process and what is the save
>> > way
>> > to
>> > save my data also.
>> >
>> > use BygTest
>> > go
>> >
>> > exec sp_helpfile
>> > go
>> >
>> > dbcc shrinkfile(MBYGDB_log, 10)
>> > go
>> >
>> > Message is!
>> > Cannot shrink log file 2 (MBYDB_Log) because all logical log files are
>> > in
>> > use.
>> >
>> > Best regards,
>> >
>> > --
>> > Olav
>> > Click Soft
>>

Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
--
MG
"MarkGsch" wrote:
> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
--
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.
Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
MG
"MarkGsch" wrote:

> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce the
> initial size of the log. I am trying everything, including DBCC SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== ===
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Cannot shrink log file

Hello
I am using this command to shrink log files because the size of log files is
going to double from data files and taking all space but i am getting this
message so help me how can i stop these process and what is the save way to
save my data also.
use BygTest
go
exec sp_helpfile
go
dbcc shrinkfile(MBYGDB_log, 10)
go
Message is!
Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in use.
Best regards,
Olav
Click Soft
To shrink the log file I use the following:
USE BygTest
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
BACKUP LOG BygTest WITH TRUNCATE_ONLY
dbcc shrinkfile (MBYGDB_log,10)
but in reference to the error message, I don't know the reason :-(
good luck
Javi
"Olav" <Olav@.discussions.microsoft.com> escribi en el mensaje
news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> Hello
> I am using this command to shrink log files because the size of log files
> is
> going to double from data files and taking all space but i am getting this
> message so help me how can i stop these process and what is the save way
> to
> save my data also.
> use BygTest
> go
> exec sp_helpfile
> go
> dbcc shrinkfile(MBYGDB_log, 10)
> go
> Message is!
> Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in
> use.
> Best regards,
> --
> Olav
> Click Soft
|||Thanks for good tip. i have one last question in this regards that if size of
my data file is 2 124 876 and size of log file is 4 248 987 and i shrink it
to for example 10 424 then what is effect on the database data?
Olav
Click Soft
"Javi" wrote:

> To shrink the log file I use the following:
> USE BygTest
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> BACKUP LOG BygTest WITH TRUNCATE_ONLY
> dbcc shrinkfile (MBYGDB_log,10)
> but in reference to the error message, I don't know the reason :-(
> good luck
> Javi
> "Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
> news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
>
>
|||Olav
If don't carry about the data then set your database to SIMPLE recovery
mode rather FULL .
With FULL recovery mode you will have ( in order to manage the LOG's size
and not to lose the data) BACKUP LOG file.
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...
> Thanks for good tip. i have one last question in this regards that if size
of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
it[vbcol=seagreen]
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
files[vbcol=seagreen]
this[vbcol=seagreen]
way[vbcol=seagreen]
in[vbcol=seagreen]
|||The data will be still there, but you will be not able to undo changes in
the database, I mean, if you at 7:00 has a job that loads data in the
database and at 10:00 other job deletes some registers, using the log file
you can undo these, but if the data you have is good for you, you don't want
undo the changes, then you don't need to maintain the log file info in order
to undo these changes. Use backup to maintain safe your data. It's better.
HTH
Javi
"Olav" <Olav@.discussions.microsoft.com> escribi en el mensaje
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...[vbcol=seagreen]
> Thanks for good tip. i have one last question in this regards that if size
> of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
> it
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:

Cannot shrink log file

Hello
I am using this command to shrink log files because the size of log files is
going to double from data files and taking all space but i am getting this
message so help me how can i stop these process and what is the save way to
save my data also.
use BygTest
go
exec sp_helpfile
go
dbcc shrinkfile(MBYGDB_log, 10)
go
Message is!
Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in us
e.
Best regards,
Olav
Click SoftTo shrink the log file I use the following:
USE BygTest
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
CHECKPOINT
BACKUP LOG BygTest WITH TRUNCATE_ONLY
dbcc shrinkfile (MBYGDB_log,10)
but in reference to the error message, I don't know the reason :-(
good luck
Javi
"Olav" <Olav@.discussions.microsoft.com> escribi en el mensaje
news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
> Hello
> I am using this command to shrink log files because the size of log files
> is
> going to double from data files and taking all space but i am getting this
> message so help me how can i stop these process and what is the save way
> to
> save my data also.
> use BygTest
> go
> exec sp_helpfile
> go
> dbcc shrinkfile(MBYGDB_log, 10)
> go
> Message is!
> Cannot shrink log file 2 (MBYDB_Log) because all logical log files are in
> use.
> Best regards,
> --
> Olav
> Click Soft|||Thanks for good tip. i have one last question in this regards that if size o
f
my data file is 2 124 876 and size of log file is 4 248 987 and i shrink it
to for example 10 424 then what is effect on the database data?
Olav
Click Soft
"Javi" wrote:

> To shrink the log file I use the following:
> USE BygTest
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> CHECKPOINT
> BACKUP LOG BygTest WITH TRUNCATE_ONLY
> dbcc shrinkfile (MBYGDB_log,10)
> but in reference to the error message, I don't know the reason :-(
> good luck
> Javi
> "Olav" <Olav@.discussions.microsoft.com> escribió en el mensaje
> news:8090B57B-7613-491E-836D-1F058B4C754F@.microsoft.com...
>
>|||Olav
If don't carry about the data then set your database to SIMPLE recovery
mode rather FULL .
With FULL recovery mode you will have ( in order to manage the LOG's size
and not to lose the data) BACKUP LOG file.
"Olav" <Olav@.discussions.microsoft.com> wrote in message
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...
> Thanks for good tip. i have one last question in this regards that if size
of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
it[vbcol=seagreen]
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
>
files[vbcol=seagreen]
this[vbcol=seagreen]
way[vbcol=seagreen]
in[vbcol=seagreen]|||The data will be still there, but you will be not able to undo changes in
the database, I mean, if you at 7:00 has a job that loads data in the
database and at 10:00 other job deletes some registers, using the log file
you can undo these, but if the data you have is good for you, you don't want
undo the changes, then you don't need to maintain the log file info in order
to undo these changes. Use backup to maintain safe your data. It's better.
HTH
Javi
"Olav" <Olav@.discussions.microsoft.com> escribi en el mensaje
news:4C5ED48E-DFF3-4C76-864A-71AA9B83B56C@.microsoft.com...[vbcol=seagreen]
> Thanks for good tip. i have one last question in this regards that if size
> of
> my data file is 2 124 876 and size of log file is 4 248 987 and i shrink
> it
> to for example 10 424 then what is effect on the database data?
> --
> Olav
> Click Soft
>
> "Javi" wrote:
>

Cannot Shrink Log File

I have a Db w no open transactions, whose log file is 23GB and I cannot
shrink it below its initial size. Perhaps I need to know how to reduce the
initial size of the log. I am trying everything, including DBCC SHRINKFILE
(MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size i
n
Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
2005 SP2 x64 running in 64-bit mode). Thanks.Try doing a
backup log <db_name>
with Truncate_only
and then try shrinking the file.
--
MG
"MarkGsch" wrote:

> I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce th
e
> initial size of the log. I am trying everything, including DBCC SHRINKFIL
E
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Do you mean that you created it at 23GB to begin with. How many VLF's are in
it? You may want to have a look at this section of BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2eb39bb3-f889-40be-9ca7-daf7
d5107c46.htm
Andrew J. Kelly SQL MVP
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.
Sounds like you're in FULL RECOVERY mode and no transaction log backups are
being made.
I'd try to make a transaction log backup first.
barring that, do a BACKUP LOG <dbname> with TRUNCATE_ONLY
Then immediately do a FULL backup and then start doing your transaction log
backups.
(if you don't care about transaction logs, you can avoid this problem in the
future by setting the database to SIMPLE RECOVERY.)
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||You are at the mercy of the next break point of the virtual log file you are
currently writing to. Try adding in a bunch of data, then doing the backup
log ... with truncate thing then trying to shrink. I can take a LOT data to
get this to work.
Also, if it is a single file database, can you detach and reattach it, thus
getting the default tlog created? If so, make sure you immediately size the
tlog correctly, and set an appropriate growth rate.
TheSQLGuru
President
Indicium Resources, Inc.
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce
> the
> initial size of the log. I am trying everything, including DBCC
> SHRINKFILE
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
> in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||In addition to the other posts:
Some additional details here: http://www.karaszi.com/SQLServer/in...ink.as
p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"MarkGsch" <markgsch@.noemail.noemail> wrote in message
news:CDD66765-139C-4BEB-9F85-3FC2282A29DE@.microsoft.com...
>I have a Db w no open transactions, whose log file is 23GB and I cannot
> shrink it below its initial size. Perhaps I need to know how to reduce th
e
> initial size of the log. I am trying everything, including DBCC SHRINKFIL
E
> (MSAS_Cube_Data_log, TRUNCATEONLY), then trying to reduce the initial size
in
> Mgt Studio or using DBCC. How can I reduce that initial size ? (using SQL
> 2005 SP2 x64 running in 64-bit mode). Thanks.|||Hi MarkGsch,
Just check with you to see if you need further assistance on this issue.
To reduce the size of the log, you can run:
BACKUP LOG ... WITH TRUNCATE_ONLY
and then run DBCC SHRINKFILE to shrink the file to the size that you want.
If you have any other questions or concerns, please feel free to post back.
Have a good day!
Best regards,
Charles Wang
Microsoft Online Community Support
========================================
=============
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
If you are using Outlook Express, please make sure you clear the check box
"Tools/Options/Read: Get 300 headers at a time" to see your reply promptly.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==============
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============