database collation: size

Showing posts with label size. Show all posts

Tuesday, March 27, 2012

Changing the size of a Varchar Field

We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?

Thanks ahead of time
DMW

Edit:
We are running SQL Server 2000 and the db at the time was running in simple moodBulk copy it out, redefine the table without indexes (except clustered or primary key) or triggers, bulk copy back in with a batchsize set to keep the log from growing too large, reapply the indexes and repost the triggers.sql

Changing the ReportViewer control size.

I have an aspx page that uses the ReportViewer control. The dimensions are
set at runtime.
private void Page_Load(object sender, System.EventArgs e)
{
reportViewer.ServerUrl = "http://localhost/reportserver";
reportViewer.ReportPath = "/Samples/Company Sales";
reportViewer.Width = 900;
reportViewer.Height = 580;
}
Is it possible to change the size of the control everytime the browser
window is resized?
TIA.You can set the size to a percent.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:OqzqmWrEFHA.2572@.tk2msftngp13.phx.gbl...
>I have an aspx page that uses the ReportViewer control. The dimensions are
>set at runtime.
> private void Page_Load(object sender, System.EventArgs e)
> {
> reportViewer.ServerUrl = "http://localhost/reportserver";
> reportViewer.ReportPath = "/Samples/Company Sales";
> reportViewer.Width = 900;
> reportViewer.Height = 580;
> }
> Is it possible to change the size of the control everytime the browser
> window is resized?
> TIA.
>|||Thank you.
This worked, but first I had to modify the ReportViewer.dll since, by
default, it doesn't accept precentage for dimensions.
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:ekIm4R5EFHA.3200@.TK2MSFTNGP10.phx.gbl...
> You can set the size to a percent.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
> news:OqzqmWrEFHA.2572@.tk2msftngp13.phx.gbl...
>>I have an aspx page that uses the ReportViewer control. The dimensions
>>are set at runtime.
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>> reportViewer.ServerUrl = "http://localhost/reportserver";
>> reportViewer.ReportPath = "/Samples/Company Sales";
>> reportViewer.Width = 900;
>> reportViewer.Height = 580;
>> }
>> Is it possible to change the size of the control everytime the browser
>> window is resized?
>> TIA.
>|||Great, I'm glad you got it working. Sometimes I forget all the steps when I
did something, but just remember that it works!!
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"TechnoSpyke" <technospyke@.yahoo.com> wrote in message
news:eoca6W5EFHA.2600@.TK2MSFTNGP09.phx.gbl...
> Thank you.
> This worked, but first I had to modify the ReportViewer.dll since, by
> default, it doesn't accept precentage for dimensions.
>
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:ekIm4R5EFHA.3200@.TK2MSFTNGP10.phx.gbl...
>> You can set the size to a percent.
>> --
>> Cheers,
>> '(' Jeff A. Stucker
>> \
>> Business Intelligence
>> www.criadvantage.com
>> ---
>> "TechnoSpyke" <technospyke@.yahoo.com> wrote in message
>> news:OqzqmWrEFHA.2572@.tk2msftngp13.phx.gbl...
>>I have an aspx page that uses the ReportViewer control. The dimensions
>>are set at runtime.
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>> reportViewer.ServerUrl = "http://localhost/reportserver";
>> reportViewer.ReportPath = "/Samples/Company Sales";
>> reportViewer.Width = 900;
>> reportViewer.Height = 580;
>> }
>> Is it possible to change the size of the control everytime the browser
>> window is resized?
>> TIA.
>>
>

Changing the look of Report Manager

Is there a way to change the size of the text boxes and other items that appear in report manager when you run a report? I am referring to the size of the boxes that the user can enter information into to run the report.

Thank you,

Hi Kimberly

This has been discussed alot on the forum, in short the answer is "somewhat". You can modify the cascading style sheets for the report manager, I found a good thread on that yesterday but can't find it right now.

As for "branding" the site, one thread where this is discussed is here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=295521&SiteID=1

Enjoy

Thursday, March 22, 2012

Changing the command buffer size?

Is it possible to change the command buffer size??
I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...
Thanks!!!Originally posted by rmcat
Is it possible to change the command buffer size??

I need to export data on demand to an excel spreadsheet via a stored procedure. The only way I know how to do this is through a bulk copy command; but my query is much to big for the buffer...

Thanks!!!

Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.|||Originally posted by kbk
Are you sure it's your buffer that is causing the problem?
An Excel spread sheet can handle a max. of 65536 rows, maybe that is your limitation.

When I run the stored proc with exec output turned on, I get the following error...
"Query hints exceed maximum command buffer size of 1023 bytes (3952 bytes input)."|||What do you put for the "query hints"? INDEX=xxxxx ?|||You're playing with dynamic sql...right?|||Originally posted by rdjabarov
What do you put for the "query hints"? INDEX=xxxxx ?

Ok, I don't exactly know what "query hints" in the error message is referring to, but the command I'm trying to execute is...

bcp "select...." queryout <filename> <sql user id> <sql password> <server> <file format>

The select statement is 3953 characters long. This commmand has worked for other (shorter) selects, so I have to assume the problem here is the length of my query. Unfortunately, I can't make it any shorter, so I'm hoping to make the command buffer bigger...|||Originally posted by Brett Kaiser
You're playing with dynamic sql...right?

Yes, it's dynamic. Sections of the where clause are dependant on data passed in from a web app that calls the stored proc.|||I think you nailed it, Brett! He just finished struggling with "Unclosed quotes"!|||Convert your SELECT into a stored procedure, and you're home free!|||Originally posted by rdjabarov
Convert your SELECT into a stored procedure, and you're home free!

Success!!!!

You guys rock!

Thank you!!!

Tuesday, March 20, 2012

Changing table column size

I'm new to mssql.

I need to change the size of a column of a table from char(255) to char(500). I used the line:

alter table table_name alter column column_name char(500)

When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.

What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?Really?

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(250))
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',250) UNION ALL
SELECT REPLICATE('x',25) UNION ALL
SELECT REPLICATE('x',2)
GO

SELECT LEN(Col1) FROM myTable99
GO

-- Will Fail
INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

ALTER TABLE myTable99 ALTER COLUMN Col1 char(500)
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

SELECT LEN(Col1) FROM myTable99
GO

DROP TABLE myTable99
GO|||xiphias, you're probably testing the number of characters inserted by doing SELECT col_name FROM table. At this point you see only 255 characters, right? Check your QA settings (Tools/Options/Results/Maximum characters per column)|||Haha. That was the problem. I changed the settings and now I can see all the characters.

Now I try to do queries with the database with PHP and the number of characters return from the query is 255 again. Is there some setting file that I have to change in PHP?|||Your's is an interface problem...has nothing to do with sql server...

What's PHP?|||Sorry, I didn't know what was wrong..

PHP is a programming language for dynamic web pages. I guess I'll have to ask that in a different forum.

Thanks for the help.|||True, it seems you need to control from PHP point of view also.sql

Wednesday, March 7, 2012

Changing Parameter Dropdown Size

I have parameters that are too long to fit into the dropdown box which then
requires horizontal scrolling. How can i change the width of the dropdown
controls?
TIA
DeanOn Jun 25, 9:35 am, "Dean" <deanl...@.hotmail.com.nospam> wrote:
> I have parameters that are too long to fit into the dropdown box which then
> requires horizontal scrolling. How can i change the width of the dropdown
> controls?
> TIA
> Dean
Currently, there is no way to control the width of the drop-down
boxes. Possibly, the next version of SSRS (V2008) will have this
functionality, since this topic has come up a few times in this group.
Sorry that I can not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Changing paper size through asp

I have designed a report on my local drive to be used as a label (4" X2") I've installed the driver for the label printer and set the paper size. Works Great. I put the report on our Crystal Server where it is called through asp. Instead of getting individual 4X2 labels, I get an 8X10 with all the labels listed. Is there a way to define the paper size through the asp page? Or does the printer driver need to be installed on the server? I don't want to mess things up for our IT people and none of them know the solution. Thanks!Hi,

To set the papersize you can use,

session("oRpt").PaperSize property.

=>session("oRpt") is the Report Object

But I'm not sure about the driver. Postively it will expect all the resources in Server.

Thursday, February 16, 2012

changing fieldsize in a replicated database

I am looking for a way to increase the size of a varchar field in a
replicated database, without disturbing the data of course. I do not
want to remove the replication just for this one issue. What would the
process be in writing a SQL query to accomplish this? The table name is
tblBid, and the field is bidNotes. It is a varchar(500), and I would
like it to be 900. Is there a size limitation on varchars?
Thanks.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Daniel,
Directly this can't be done. Indirectly it can, but not nicely! You could
add a new column with the new datatype (sp_repladdcolumn), do an update on
the table to populate the column, then drop the column (sp_repldropcolumn).
Do this again to create the column having the same original name.
BTW this is available directly using Alter Table in SQL 2005.
Rgds,
Paul Ibison (SQL Server MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Changing field length for Merge

Hello -
I am using Merge replication, pushing subscriptions to clients, and SQL
Server 2000.
I need to change the field size in one of the tables that is in a
publication. Basically, from nvarchar[4] to nvarchar[8].
Can I do this without having to drop/add all subscribers?
I'm hoping that the schema changes can be propagated to clients with a
reinit-subscription.
Thanks.
Regards,
bruce
Please have a look at this article:
http://www.replicationanswers.com/AddColumn.asp. BTW, such changes are much
simpler (just Alter Table...) in SQL Server 2005.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi, I have the same problem than Bruce, I'm using a merge replication but
with a sligtly difference, the field I must change is part of the table's
primary key... I read the Paul Ibison's article but it look fine for fields
doesn't belongs to the primary key, because if I drop the field in order to
made the change on the field, it will throw an duplicate key error. So, what
else can I do?
Thanks in advance
"Paul Ibison" wrote:

> Please have a look at this article:
> http://www.replicationanswers.com/AddColumn.asp. BTW, such changes are much
> simpler (just Alter Table...) in SQL Server 2005.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Sunday, February 12, 2012

Changing database size

Hello,
My SQL server 7 database, data size is 150M, but only 82M
occupied with data.
Transaction log size is 1.8Gig, and only 20M is used the
actual data.
This is I think, due to not backing up transaction data
for more then a year.
Now we are trying to reduce the database transaction log
size, but SQL server doesn't allow us to reduce it.
It says, "your new size must be larger then current size."
We tried to use alter database commands, says the same
message. Is there another way to reduce the allocated
space to transaction logs?
MCHave you checked out this article?
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||Hi,
Check the database option "Truncate Log on Checkpoint" for this database,
If option is not selected then perform a transaction log
backup using "Backup Log" command (refer BOL) and then try to shrink the
Transaction log using DBCC SHRINKFILE
command (Refer BOL).
Note: Incase ur database is not production or data is not critical please
enable the option "Truncate Log on Checkpoint" , so as Transaction log file
will
be cleared after comitting the trasaction and the file will not grow.
Thanks
Hari
MCDBA
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||You can not shrink 'fragmented' log files.
You can have a lot of free space in the beginning of a log file and active
information only at the end.
Shrinking will not help you, even if there's a lot of free space.
I think running the statement BACKUP log with no_log command could help.
Currently I don't have my backup scripts nearby.
A full backup or differential backup is recommended after running this
statement.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||Well,
To get rid of un-used space from the transaction log,you
can not use shrink or backup and use truncate option.
This doesn't alter the over all database log file size.
I tried backup and restore to a new database, but restore
will restore orgional size of the file. So that didn't help
If you folks have a script that you know works, I
appreciate if you can send it to me
Thnks
>--Original Message--
>You can not shrink 'fragmented' log files.
>You can have a lot of free space in the beginning of a
log file and active
>information only at the end.
>Shrinking will not help you, even if there's a lot of
free space.
>I think running the statement BACKUP log with no_log
command could help.
>Currently I don't have my backup scripts nearby.
>A full backup or differential backup is recommended after
running this
>statement.
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>
>"mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in message
>news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
>> Hello,
>> My SQL server 7 database, data size is 150M, but only
82M
>> occupied with data.
>> Transaction log size is 1.8Gig, and only 20M is used the
>> actual data.
>> This is I think, due to not backing up transaction data
>> for more then a year.
>> Now we are trying to reduce the database transaction log
>> size, but SQL server doesn't allow us to reduce it.
>> It says, "your new size must be larger then current
size."
>> We tried to use alter database commands, says the same
>> message. Is there another way to reduce the allocated
>> space to transaction logs?
>> MC
>
>.
>|||These articles do not help to re-size trasaction log file
MC
>--Original Message--
>Hello,
>My SQL server 7 database, data size is 150M, but only 82M
>occupied with data.
>Transaction log size is 1.8Gig, and only 20M is used the
>actual data.
>This is I think, due to not backing up transaction data
>for more then a year.
>Now we are trying to reduce the database transaction log
>size, but SQL server doesn't allow us to reduce it.
>It says, "your new size must be larger then current size."
>We tried to use alter database commands, says the same
>message. Is there another way to reduce the allocated
>space to transaction logs?
>MC
>.
>|||You can't reduce the size of the log using ALTER DATABASE (which by your
messages is what you are trying to do). You need to use DBCC SHRINKFILE,
which is restricted in the sense that it can only shrink from the end of the
file towards the beginning of the file. And if there are log records at the
end of the file, it cannot be shrunk.
Check out below KB articles:
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
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> Hello,
> My SQL server 7 database, data size is 150M, but only 82M
> occupied with data.
> Transaction log size is 1.8Gig, and only 20M is used the
> actual data.
> This is I think, due to not backing up transaction data
> for more then a year.
> Now we are trying to reduce the database transaction log
> size, but SQL server doesn't allow us to reduce it.
> It says, "your new size must be larger then current size."
> We tried to use alter database commands, says the same
> message. Is there another way to reduce the allocated
> space to transaction logs?
> MC
>|||Yes you can, i've written this script a few years ago and it is a specific
order of SQL statements.
Not an 'out of a book' solution but it is possible.
(Not that I don't want to give it to you, but i have to look it up)
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OvF6EB$%23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> You can't reduce the size of the log using ALTER DATABASE (which by your
> messages is what you are trying to do). You need to use DBCC SHRINKFILE,
> which is restricted in the sense that it can only shrink from the end of
the
> file towards the beginning of the file. And if there are log records at
the
> end of the file, it cannot be shrunk.
> Check out below KB articles:
> 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
> Log File Grows too big
> http://www.support.microsoft.com/?id=317375
> Log file filling up
> http://www.support.microsoft.com/?id=110139
> Considerations for Autogrow and AutoShrink
> http://www.support.microsoft.com/?id=315512
> http://www.mssqlserver.com/faq/logs-shrinklog.asp
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
message
> news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > Hello,
> > My SQL server 7 database, data size is 150M, but only 82M
> > occupied with data.
> >
> > Transaction log size is 1.8Gig, and only 20M is used the
> > actual data.
> >
> > This is I think, due to not backing up transaction data
> > for more then a year.
> >
> > Now we are trying to reduce the database transaction log
> > size, but SQL server doesn't allow us to reduce it.
> > It says, "your new size must be larger then current size."
> > We tried to use alter database commands, says the same
> > message. Is there another way to reduce the allocated
> > space to transaction logs?
> >
> > MC
> >
>|||Nico,
This makes me curious. It seems like you are saying that you can shrink the
size of a database file using ALTER DATABASE without the usage of DBCC
SHRINKFILE or DBCC SHRINKDATABASE. This is news to me, and I would be very
interested to see how you accomplish that. I do in no way doubt what you are
saying, this is out of pure curiosity, as I though it wasn't possible. :-)
(Just for the record, I do not consider deleting the transaction log file a
usable method, as we see posts here on a daily basis where deletion of
transaction log files renders corrupt databases. Extreme caution has to be
taken if you even want to consider taking that path. :-) )
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@.denco.be> wrote in message
news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Yes you can, i've written this script a few years ago and it is a specific
> order of SQL statements.
> Not an 'out of a book' solution but it is possible.
> (Not that I don't want to give it to you, but i have to look it up)
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OvF6EB$%23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > You can't reduce the size of the log using ALTER DATABASE (which by your
> > messages is what you are trying to do). You need to use DBCC SHRINKFILE,
> > which is restricted in the sense that it can only shrink from the end of
> the
> > file towards the beginning of the file. And if there are log records at
> the
> > end of the file, it cannot be shrunk.
> >
> > Check out below KB articles:
> >
> > 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
> >
> > Log File Grows too big
> > http://www.support.microsoft.com/?id=317375
> >
> > Log file filling up
> > http://www.support.microsoft.com/?id=110139
> >
> > Considerations for Autogrow and AutoShrink
> > http://www.support.microsoft.com/?id=315512
> >
> > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > Hello,
> > > My SQL server 7 database, data size is 150M, but only 82M
> > > occupied with data.
> > >
> > > Transaction log size is 1.8Gig, and only 20M is used the
> > > actual data.
> > >
> > > This is I think, due to not backing up transaction data
> > > for more then a year.
> > >
> > > Now we are trying to reduce the database transaction log
> > > size, but SQL server doesn't allow us to reduce it.
> > > It says, "your new size must be larger then current size."
> > > We tried to use alter database commands, says the same
> > > message. Is there another way to reduce the allocated
> > > space to transaction logs?
> > >
> > > MC
> > >
> >
> >
>|||No, I didn't say it was possible with ALTER DATABASE, in my opinion it
isn't.
But it is possible to shrink the file, even if the active part is at the end
of the file.
I'll try to dig up the exact script soon.
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> Nico,
> This makes me curious. It seems like you are saying that you can shrink
the
> size of a database file using ALTER DATABASE without the usage of DBCC
> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me, and I would be very
> interested to see how you accomplish that. I do in no way doubt what you
are
> saying, this is out of pure curiosity, as I though it wasn't possible. :-)
> (Just for the record, I do not consider deleting the transaction log file
a
> usable method, as we see posts here on a daily basis where deletion of
> transaction log files renders corrupt databases. Extreme caution has to be
> taken if you even want to consider taking that path. :-) )
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > Yes you can, i've written this script a few years ago and it is a
specific
> > order of SQL statements.
> > Not an 'out of a book' solution but it is possible.
> >
> > (Not that I don't want to give it to you, but i have to look it up)
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:OvF6EB$%23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > > You can't reduce the size of the log using ALTER DATABASE (which by
your
> > > messages is what you are trying to do). You need to use DBCC
SHRINKFILE,
> > > which is restricted in the sense that it can only shrink from the end
of
> > the
> > > file towards the beginning of the file. And if there are log records
at
> > the
> > > end of the file, it cannot be shrunk.
> > >
> > > Check out below KB articles:
> > >
> > > 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
> > >
> > > Log File Grows too big
> > > http://www.support.microsoft.com/?id=317375
> > >
> > > Log file filling up
> > > http://www.support.microsoft.com/?id=110139
> > >
> > > Considerations for Autogrow and AutoShrink
> > > http://www.support.microsoft.com/?id=315512
> > >
> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > >
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > > Hello,
> > > > My SQL server 7 database, data size is 150M, but only 82M
> > > > occupied with data.
> > > >
> > > > Transaction log size is 1.8Gig, and only 20M is used the
> > > > actual data.
> > > >
> > > > This is I think, due to not backing up transaction data
> > > > for more then a year.
> > > >
> > > > Now we are trying to reduce the database transaction log
> > > > size, but SQL server doesn't allow us to reduce it.
> > > > It says, "your new size must be larger then current size."
> > > > We tried to use alter database commands, says the same
> > > > message. Is there another way to reduce the allocated
> > > > space to transaction logs?
> > > >
> > > > MC
> > > >
> > >
> > >
> >
> >
>|||Gentlmen,
I don't think we are talking about the same think here.
I already tried DBCC
SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
MS artciles. These procedures does not change ALLOCATED
database file size, nor database lof file size.
I am trying to change allocated database file size.
Once you allocated a database size after the database
creation, you can not reduce the allocated size as per MS.
These procedures doesn't do that. What it did is this.
My log file size in SQL Enterprise manager shows 1.8G
It showed that 1.6G was used by data. DBCC
SHRINKFILE or DBCC SHRINKDATABASE procedures
brought the used space down to 80M. But the actual
allocated log file size did not change.
The only easy option I can think of is to create a new
database and DTS all the data to new database.
MC
>--Original Message--
>No, I didn't say it was possible with ALTER DATABASE, in
my opinion it
>isn't.
>But it is possible to shrink the file, even if the active
part is at the end
>of the file.
>I'll try to dig up the exact script soon.
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
>message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
>> Nico,
>> This makes me curious. It seems like you are saying
that you can shrink
>the
>> size of a database file using ALTER DATABASE without
the usage of DBCC
>> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
and I would be very
>> interested to see how you accomplish that. I do in no
way doubt what you
>are
>> saying, this is out of pure curiosity, as I though it
wasn't possible. :-)
>> (Just for the record, I do not consider deleting the
transaction log file
>a
>> usable method, as we see posts here on a daily basis
where deletion of
>> transaction log files renders corrupt databases.
Extreme caution has to be
>> taken if you even want to consider taking that path. :-
) )
>> --
>> Tibor Karaszi, SQL Server MVP
>> Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>>
>> "Nico De Greef" <ndg@.denco.be> wrote in message
>> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
>> > Yes you can, i've written this script a few years ago
and it is a
>specific
>> > order of SQL statements.
>> > Not an 'out of a book' solution but it is possible.
>> >
>> > (Not that I don't want to give it to you, but i have
to look it up)
>> >
>> > --
>> > Nico De Greef
>> > Belgium
>> > Freelance Software Architect
>> > MCP, MCSD, .NET certified
>> >
>> > "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> > message news:OvF6EB$%
23DHA.2180@.TK2MSFTNGP09.phx.gbl...
>> > > You can't reduce the size of the log using ALTER
DATABASE (which by
>your
>> > > messages is what you are trying to do). You need to
use DBCC
>SHRINKFILE,
>> > > which is restricted in the sense that it can only
shrink from the end
>of
>> > the
>> > > file towards the beginning of the file. And if
there are log records
>at
>> > the
>> > > end of the file, it cannot be shrunk.
>> > >
>> > > Check out below KB articles:
>> > >
>> > > 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
>> > >
>> > > Log File Grows too big
>> > > http://www.support.microsoft.com/?id=317375
>> > >
>> > > Log file filling up
>> > > http://www.support.microsoft.com/?id=110139
>> > >
>> > > Considerations for Autogrow and AutoShrink
>> > > http://www.support.microsoft.com/?id=315512
>> > >
>> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
>> > >
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > Archive at:
>> > >
>> >
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>> > >
>> > >
>> > > "mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in
>> > message
>> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
>> > > > Hello,
>> > > > My SQL server 7 database, data size is 150M, but
only 82M
>> > > > occupied with data.
>> > > >
>> > > > Transaction log size is 1.8Gig, and only 20M is
used the
>> > > > actual data.
>> > > >
>> > > > This is I think, due to not backing up
transaction data
>> > > > for more then a year.
>> > > >
>> > > > Now we are trying to reduce the database
transaction log
>> > > > size, but SQL server doesn't allow us to reduce
it.
>> > > > It says, "your new size must be larger then
current size."
>> > > > We tried to use alter database commands, says the
same
>> > > > message. Is there another way to reduce the
allocated
>> > > > space to transaction logs?
>> > > >
>> > > > MC
>> > > >
>> > >
>> > >
>> >
>> >
>>
>
>.
>|||These steps should give the result you're looking for:
BACKUP LOG WITH NO_LOG
DBCC SHRINKDATABASE
(full BACKUP)
DBCC SHRINKDATABASE
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in message
news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> Gentlmen,
> I don't think we are talking about the same think here.
> I already tried DBCC
> SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> MS artciles. These procedures does not change ALLOCATED
> database file size, nor database lof file size.
> I am trying to change allocated database file size.
> Once you allocated a database size after the database
> creation, you can not reduce the allocated size as per MS.
> These procedures doesn't do that. What it did is this.
> My log file size in SQL Enterprise manager shows 1.8G
> It showed that 1.6G was used by data. DBCC
> SHRINKFILE or DBCC SHRINKDATABASE procedures
> brought the used space down to 80M. But the actual
> allocated log file size did not change.
> The only easy option I can think of is to create a new
> database and DTS all the data to new database.
> MC
> >--Original Message--
> >No, I didn't say it was possible with ALTER DATABASE, in
> my opinion it
> >isn't.
> >But it is possible to shrink the file, even if the active
> part is at the end
> >of the file.
> >I'll try to dig up the exact script soon.
> >
> >--
> >Nico De Greef
> >Belgium
> >Freelance Software Architect
> >MCP, MCSD, .NET certified
> >
> >"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> >> Nico,
> >>
> >> This makes me curious. It seems like you are saying
> that you can shrink
> >the
> >> size of a database file using ALTER DATABASE without
> the usage of DBCC
> >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> and I would be very
> >> interested to see how you accomplish that. I do in no
> way doubt what you
> >are
> >> saying, this is out of pure curiosity, as I though it
> wasn't possible. :-)
> >>
> >> (Just for the record, I do not consider deleting the
> transaction log file
> >a
> >> usable method, as we see posts here on a daily basis
> where deletion of
> >> transaction log files renders corrupt databases.
> Extreme caution has to be
> >> taken if you even want to consider taking that path. :-
> ) )
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> Archive at:
> >>
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >>
> >>
> >> "Nico De Greef" <ndg@.denco.be> wrote in message
> >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> >> > Yes you can, i've written this script a few years ago
> and it is a
> >specific
> >> > order of SQL statements.
> >> > Not an 'out of a book' solution but it is possible.
> >> >
> >> > (Not that I don't want to give it to you, but i have
> to look it up)
> >> >
> >> > --
> >> > Nico De Greef
> >> > Belgium
> >> > Freelance Software Architect
> >> > MCP, MCSD, .NET certified
> >> >
> >> > "Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> >> in
> >> > message news:OvF6EB$%
> 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> >> > > You can't reduce the size of the log using ALTER
> DATABASE (which by
> >your
> >> > > messages is what you are trying to do). You need to
> use DBCC
> >SHRINKFILE,
> >> > > which is restricted in the sense that it can only
> shrink from the end
> >of
> >> > the
> >> > > file towards the beginning of the file. And if
> there are log records
> >at
> >> > the
> >> > > end of the file, it cannot be shrunk.
> >> > >
> >> > > Check out below KB articles:
> >> > >
> >> > > 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
> >> > >
> >> > > Log File Grows too big
> >> > > http://www.support.microsoft.com/?id=317375
> >> > >
> >> > > Log file filling up
> >> > > http://www.support.microsoft.com/?id=110139
> >> > >
> >> > > Considerations for Autogrow and AutoShrink
> >> > > http://www.support.microsoft.com/?id=315512
> >> > >
> >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> >> > >
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > Archive at:
> >> > >
> >> >
> >>
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >> > >
> >> > >
> >> > > "mcamci@.ozoptics.com"
> <anonymous@.discussions.microsoft.com> wrote in
> >> > message
> >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> >> > > > Hello,
> >> > > > My SQL server 7 database, data size is 150M, but
> only 82M
> >> > > > occupied with data.
> >> > > >
> >> > > > Transaction log size is 1.8Gig, and only 20M is
> used the
> >> > > > actual data.
> >> > > >
> >> > > > This is I think, due to not backing up
> transaction data
> >> > > > for more then a year.
> >> > > >
> >> > > > Now we are trying to reduce the database
> transaction log
> >> > > > size, but SQL server doesn't allow us to reduce
> it.
> >> > > > It says, "your new size must be larger then
> current size."
> >> > > > We tried to use alter database commands, says the
> same
> >> > > > message. Is there another way to reduce the
> allocated
> >> > > > space to transaction logs?
> >> > > >
> >> > > > MC
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
> >.
> >|||Nico,
Were these the steps you referred to earlier? I.e., the steps that could
shrink the log file even there are log records at the end of the log file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@.denco.be> wrote in message
news:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> These steps should give the result you're looking for:
> BACKUP LOG WITH NO_LOG
> DBCC SHRINKDATABASE
> (full BACKUP)
> DBCC SHRINKDATABASE
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > Gentlmen,
> > I don't think we are talking about the same think here.
> > I already tried DBCC
> > SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> > MS artciles. These procedures does not change ALLOCATED
> > database file size, nor database lof file size.
> >
> > I am trying to change allocated database file size.
> > Once you allocated a database size after the database
> > creation, you can not reduce the allocated size as per MS.
> > These procedures doesn't do that. What it did is this.
> > My log file size in SQL Enterprise manager shows 1.8G
> > It showed that 1.6G was used by data. DBCC
> > SHRINKFILE or DBCC SHRINKDATABASE procedures
> > brought the used space down to 80M. But the actual
> > allocated log file size did not change.
> >
> > The only easy option I can think of is to create a new
> > database and DTS all the data to new database.
> > MC
> >
> > >--Original Message--
> > >No, I didn't say it was possible with ALTER DATABASE, in
> > my opinion it
> > >isn't.
> > >But it is possible to shrink the file, even if the active
> > part is at the end
> > >of the file.
> > >I'll try to dig up the exact script soon.
> > >
> > >--
> > >Nico De Greef
> > >Belgium
> > >Freelance Software Architect
> > >MCP, MCSD, .NET certified
> > >
> > >"Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> > >> Nico,
> > >>
> > >> This makes me curious. It seems like you are saying
> > that you can shrink
> > >the
> > >> size of a database file using ALTER DATABASE without
> > the usage of DBCC
> > >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> > and I would be very
> > >> interested to see how you accomplish that. I do in no
> > way doubt what you
> > >are
> > >> saying, this is out of pure curiosity, as I though it
> > wasn't possible. :-)
> > >>
> > >> (Just for the record, I do not consider deleting the
> > transaction log file
> > >a
> > >> usable method, as we see posts here on a daily basis
> > where deletion of
> > >> transaction log files renders corrupt databases.
> > Extreme caution has to be
> > >> taken if you even want to consider taking that path. :-
> > ) )
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> Archive at:
> > >>
> > >http://groups.google.com/groups?
> > oi=djq&as_ugroup=microsoft.public.sqlserver
> > >>
> > >>
> > >> "Nico De Greef" <ndg@.denco.be> wrote in message
> > >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > >> > Yes you can, i've written this script a few years ago
> > and it is a
> > >specific
> > >> > order of SQL statements.
> > >> > Not an 'out of a book' solution but it is possible.
> > >> >
> > >> > (Not that I don't want to give it to you, but i have
> > to look it up)
> > >> >
> > >> > --
> > >> > Nico De Greef
> > >> > Belgium
> > >> > Freelance Software Architect
> > >> > MCP, MCSD, .NET certified
> > >> >
> > >> > "Tibor Karaszi"
> > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > >> in
> > >> > message news:OvF6EB$%
> > 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > >> > > You can't reduce the size of the log using ALTER
> > DATABASE (which by
> > >your
> > >> > > messages is what you are trying to do). You need to
> > use DBCC
> > >SHRINKFILE,
> > >> > > which is restricted in the sense that it can only
> > shrink from the end
> > >of
> > >> > the
> > >> > > file towards the beginning of the file. And if
> > there are log records
> > >at
> > >> > the
> > >> > > end of the file, it cannot be shrunk.
> > >> > >
> > >> > > Check out below KB articles:
> > >> > >
> > >> > > 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
> > >> > >
> > >> > > Log File Grows too big
> > >> > > http://www.support.microsoft.com/?id=317375
> > >> > >
> > >> > > Log file filling up
> > >> > > http://www.support.microsoft.com/?id=110139
> > >> > >
> > >> > > Considerations for Autogrow and AutoShrink
> > >> > > http://www.support.microsoft.com/?id=315512
> > >> > >
> > >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > >> > >
> > >> > >
> > >> > > --
> > >> > > Tibor Karaszi, SQL Server MVP
> > >> > > Archive at:
> > >> > >
> > >> >
> > >>
> > >http://groups.google.com/groups?
> > oi=djq&as_ugroup=microsoft.public.sqlserver
> > >> > >
> > >> > >
> > >> > > "mcamci@.ozoptics.com"
> > <anonymous@.discussions.microsoft.com> wrote in
> > >> > message
> > >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > >> > > > Hello,
> > >> > > > My SQL server 7 database, data size is 150M, but
> > only 82M
> > >> > > > occupied with data.
> > >> > > >
> > >> > > > Transaction log size is 1.8Gig, and only 20M is
> > used the
> > >> > > > actual data.
> > >> > > >
> > >> > > > This is I think, due to not backing up
> > transaction data
> > >> > > > for more then a year.
> > >> > > >
> > >> > > > Now we are trying to reduce the database
> > transaction log
> > >> > > > size, but SQL server doesn't allow us to reduce
> > it.
> > >> > > > It says, "your new size must be larger then
> > current size."
> > >> > > > We tried to use alter database commands, says the
> > same
> > >> > > > message. Is there another way to reduce the
> > allocated
> > >> > > > space to transaction logs?
> > >> > > >
> > >> > > > MC
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> > >.
> > >
>|||Yes
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%236E4Z1R$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> Nico,
> Were these the steps you referred to earlier? I.e., the steps that could
> shrink the log file even there are log records at the end of the log file?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> > These steps should give the result you're looking for:
> >
> > BACKUP LOG WITH NO_LOG
> > DBCC SHRINKDATABASE
> > (full BACKUP)
> > DBCC SHRINKDATABASE
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> message
> > news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > > Gentlmen,
> > > I don't think we are talking about the same think here.
> > > I already tried DBCC
> > > SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> > > MS artciles. These procedures does not change ALLOCATED
> > > database file size, nor database lof file size.
> > >
> > > I am trying to change allocated database file size.
> > > Once you allocated a database size after the database
> > > creation, you can not reduce the allocated size as per MS.
> > > These procedures doesn't do that. What it did is this.
> > > My log file size in SQL Enterprise manager shows 1.8G
> > > It showed that 1.6G was used by data. DBCC
> > > SHRINKFILE or DBCC SHRINKDATABASE procedures
> > > brought the used space down to 80M. But the actual
> > > allocated log file size did not change.
> > >
> > > The only easy option I can think of is to create a new
> > > database and DTS all the data to new database.
> > > MC
> > >
> > > >--Original Message--
> > > >No, I didn't say it was possible with ALTER DATABASE, in
> > > my opinion it
> > > >isn't.
> > > >But it is possible to shrink the file, even if the active
> > > part is at the end
> > > >of the file.
> > > >I'll try to dig up the exact script soon.
> > > >
> > > >--
> > > >Nico De Greef
> > > >Belgium
> > > >Freelance Software Architect
> > > >MCP, MCSD, .NET certified
> > > >
> > > >"Tibor Karaszi"
> > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > > >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> > > >> Nico,
> > > >>
> > > >> This makes me curious. It seems like you are saying
> > > that you can shrink
> > > >the
> > > >> size of a database file using ALTER DATABASE without
> > > the usage of DBCC
> > > >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> > > and I would be very
> > > >> interested to see how you accomplish that. I do in no
> > > way doubt what you
> > > >are
> > > >> saying, this is out of pure curiosity, as I though it
> > > wasn't possible. :-)
> > > >>
> > > >> (Just for the record, I do not consider deleting the
> > > transaction log file
> > > >a
> > > >> usable method, as we see posts here on a daily basis
> > > where deletion of
> > > >> transaction log files renders corrupt databases.
> > > Extreme caution has to be
> > > >> taken if you even want to consider taking that path. :-
> > > ) )
> > > >> --
> > > >> Tibor Karaszi, SQL Server MVP
> > > >> Archive at:
> > > >>
> > > >http://groups.google.com/groups?
> > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >>
> > > >>
> > > >> "Nico De Greef" <ndg@.denco.be> wrote in message
> > > >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > > >> > Yes you can, i've written this script a few years ago
> > > and it is a
> > > >specific
> > > >> > order of SQL statements.
> > > >> > Not an 'out of a book' solution but it is possible.
> > > >> >
> > > >> > (Not that I don't want to give it to you, but i have
> > > to look it up)
> > > >> >
> > > >> > --
> > > >> > Nico De Greef
> > > >> > Belgium
> > > >> > Freelance Software Architect
> > > >> > MCP, MCSD, .NET certified
> > > >> >
> > > >> > "Tibor Karaszi"
> > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > > >> in
> > > >> > message news:OvF6EB$%
> > > 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > > >> > > You can't reduce the size of the log using ALTER
> > > DATABASE (which by
> > > >your
> > > >> > > messages is what you are trying to do). You need to
> > > use DBCC
> > > >SHRINKFILE,
> > > >> > > which is restricted in the sense that it can only
> > > shrink from the end
> > > >of
> > > >> > the
> > > >> > > file towards the beginning of the file. And if
> > > there are log records
> > > >at
> > > >> > the
> > > >> > > end of the file, it cannot be shrunk.
> > > >> > >
> > > >> > > Check out below KB articles:
> > > >> > >
> > > >> > > 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
> > > >> > >
> > > >> > > Log File Grows too big
> > > >> > > http://www.support.microsoft.com/?id=317375
> > > >> > >
> > > >> > > Log file filling up
> > > >> > > http://www.support.microsoft.com/?id=110139
> > > >> > >
> > > >> > > Considerations for Autogrow and AutoShrink
> > > >> > > http://www.support.microsoft.com/?id=315512
> > > >> > >
> > > >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > > >> > >
> > > >> > >
> > > >> > > --
> > > >> > > Tibor Karaszi, SQL Server MVP
> > > >> > > Archive at:
> > > >> > >
> > > >> >
> > > >>
> > > >http://groups.google.com/groups?
> > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >> > >
> > > >> > >
> > > >> > > "mcamci@.ozoptics.com"
> > > <anonymous@.discussions.microsoft.com> wrote in
> > > >> > message
> > > >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > >> > > > Hello,
> > > >> > > > My SQL server 7 database, data size is 150M, but
> > > only 82M
> > > >> > > > occupied with data.
> > > >> > > >
> > > >> > > > Transaction log size is 1.8Gig, and only 20M is
> > > used the
> > > >> > > > actual data.
> > > >> > > >
> > > >> > > > This is I think, due to not backing up
> > > transaction data
> > > >> > > > for more then a year.
> > > >> > > >
> > > >> > > > Now we are trying to reduce the database
> > > transaction log
> > > >> > > > size, but SQL server doesn't allow us to reduce
> > > it.
> > > >> > > > It says, "your new size must be larger then
> > > current size."
> > > >> > > > We tried to use alter database commands, says the
> > > same
> > > >> > > > message. Is there another way to reduce the
> > > allocated
> > > >> > > > space to transaction logs?
> > > >> > > >
> > > >> > > > MC
> > > >> > > >
> > > >> > >
> > > >> > >
> > > >> >
> > > >> >
> > > >>
> > > >>
> > > >
> > > >
> > > >.
> > > >
> >
> >
>|||OK, thanks. :-)
In SQK2K, DBCC SHRINKFILE (and I presume SHRINKDATABASE as well) will
generate "dummy" log records so the beginning of the log will move to the
beginning of the log file. A later backup of the log (a normal backup should
do just as good) will remove the tail of the log. The log is not entirely at
the beginning of the file, meaning that DBCC SHRINKFILE (-DATABASE) should
be able to cot then end of the log file.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico De Greef" <ndg@.denco.be> wrote in message
news:uwXKG2S$DHA.712@.tk2msftngp13.phx.gbl...
> Yes
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%236E4Z1R$DHA.2664@.TK2MSFTNGP09.phx.gbl...
> > Nico,
> >
> > Were these the steps you referred to earlier? I.e., the steps that could
> > shrink the log file even there are log records at the end of the log
file?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Nico De Greef" <ndg@.denco.be> wrote in message
> > news:%23RYptXQ$DHA.3188@.TK2MSFTNGP09.phx.gbl...
> > > These steps should give the result you're looking for:
> > >
> > > BACKUP LOG WITH NO_LOG
> > > DBCC SHRINKDATABASE
> > > (full BACKUP)
> > > DBCC SHRINKDATABASE
> > >
> > > --
> > > Nico De Greef
> > > Belgium
> > > Freelance Software Architect
> > > MCP, MCSD, .NET certified
> > >
> > > "mcamci@.ozoptics.com" <anonymous@.discussions.microsoft.com> wrote in
> > message
> > > news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
> > > > Gentlmen,
> > > > I don't think we are talking about the same think here.
> > > > I already tried DBCC
> > > > SHRINKFILE or DBCC SHRINKDATABASE procedures as per given
> > > > MS artciles. These procedures does not change ALLOCATED
> > > > database file size, nor database lof file size.
> > > >
> > > > I am trying to change allocated database file size.
> > > > Once you allocated a database size after the database
> > > > creation, you can not reduce the allocated size as per MS.
> > > > These procedures doesn't do that. What it did is this.
> > > > My log file size in SQL Enterprise manager shows 1.8G
> > > > It showed that 1.6G was used by data. DBCC
> > > > SHRINKFILE or DBCC SHRINKDATABASE procedures
> > > > brought the used space down to 80M. But the actual
> > > > allocated log file size did not change.
> > > >
> > > > The only easy option I can think of is to create a new
> > > > database and DTS all the data to new database.
> > > > MC
> > > >
> > > > >--Original Message--
> > > > >No, I didn't say it was possible with ALTER DATABASE, in
> > > > my opinion it
> > > > >isn't.
> > > > >But it is possible to shrink the file, even if the active
> > > > part is at the end
> > > > >of the file.
> > > > >I'll try to dig up the exact script soon.
> > > > >
> > > > >--
> > > > >Nico De Greef
> > > > >Belgium
> > > > >Freelance Software Architect
> > > > >MCP, MCSD, .NET certified
> > > > >
> > > > >"Tibor Karaszi"
> > > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> > > > >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
> > > > >> Nico,
> > > > >>
> > > > >> This makes me curious. It seems like you are saying
> > > > that you can shrink
> > > > >the
> > > > >> size of a database file using ALTER DATABASE without
> > > > the usage of DBCC
> > > > >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to me,
> > > > and I would be very
> > > > >> interested to see how you accomplish that. I do in no
> > > > way doubt what you
> > > > >are
> > > > >> saying, this is out of pure curiosity, as I though it
> > > > wasn't possible. :-)
> > > > >>
> > > > >> (Just for the record, I do not consider deleting the
> > > > transaction log file
> > > > >a
> > > > >> usable method, as we see posts here on a daily basis
> > > > where deletion of
> > > > >> transaction log files renders corrupt databases.
> > > > Extreme caution has to be
> > > > >> taken if you even want to consider taking that path. :-
> > > > ) )
> > > > >> --
> > > > >> Tibor Karaszi, SQL Server MVP
> > > > >> Archive at:
> > > > >>
> > > > >http://groups.google.com/groups?
> > > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >>
> > > > >>
> > > > >> "Nico De Greef" <ndg@.denco.be> wrote in message
> > > > >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> > > > >> > Yes you can, i've written this script a few years ago
> > > > and it is a
> > > > >specific
> > > > >> > order of SQL statements.
> > > > >> > Not an 'out of a book' solution but it is possible.
> > > > >> >
> > > > >> > (Not that I don't want to give it to you, but i have
> > > > to look it up)
> > > > >> >
> > > > >> > --
> > > > >> > Nico De Greef
> > > > >> > Belgium
> > > > >> > Freelance Software Architect
> > > > >> > MCP, MCSD, .NET certified
> > > > >> >
> > > > >> > "Tibor Karaszi"
> > > > <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> > > > >> in
> > > > >> > message news:OvF6EB$%
> > > > 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
> > > > >> > > You can't reduce the size of the log using ALTER
> > > > DATABASE (which by
> > > > >your
> > > > >> > > messages is what you are trying to do). You need to
> > > > use DBCC
> > > > >SHRINKFILE,
> > > > >> > > which is restricted in the sense that it can only
> > > > shrink from the end
> > > > >of
> > > > >> > the
> > > > >> > > file towards the beginning of the file. And if
> > > > there are log records
> > > > >at
> > > > >> > the
> > > > >> > > end of the file, it cannot be shrunk.
> > > > >> > >
> > > > >> > > Check out below KB articles:
> > > > >> > >
> > > > >> > > 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
> > > > >> > >
> > > > >> > > Log File Grows too big
> > > > >> > > http://www.support.microsoft.com/?id=317375
> > > > >> > >
> > > > >> > > Log file filling up
> > > > >> > > http://www.support.microsoft.com/?id=110139
> > > > >> > >
> > > > >> > > Considerations for Autogrow and AutoShrink
> > > > >> > > http://www.support.microsoft.com/?id=315512
> > > > >> > >
> > > > >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
> > > > >> > >
> > > > >> > >
> > > > >> > > --
> > > > >> > > Tibor Karaszi, SQL Server MVP
> > > > >> > > Archive at:
> > > > >> > >
> > > > >> >
> > > > >>
> > > > >http://groups.google.com/groups?
> > > > oi=djq&as_ugroup=microsoft.public.sqlserver
> > > > >> > >
> > > > >> > >
> > > > >> > > "mcamci@.ozoptics.com"
> > > > <anonymous@.discussions.microsoft.com> wrote in
> > > > >> > message
> > > > >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
> > > > >> > > > Hello,
> > > > >> > > > My SQL server 7 database, data size is 150M, but
> > > > only 82M
> > > > >> > > > occupied with data.
> > > > >> > > >
> > > > >> > > > Transaction log size is 1.8Gig, and only 20M is
> > > > used the
> > > > >> > > > actual data.
> > > > >> > > >
> > > > >> > > > This is I think, due to not backing up
> > > > transaction data
> > > > >> > > > for more then a year.
> > > > >> > > >
> > > > >> > > > Now we are trying to reduce the database
> > > > transaction log
> > > > >> > > > size, but SQL server doesn't allow us to reduce
> > > > it.
> > > > >> > > > It says, "your new size must be larger then
> > > > current size."
> > > > >> > > > We tried to use alter database commands, says the
> > > > same
> > > > >> > > > message. Is there another way to reduce the
> > > > allocated
> > > > >> > > > space to transaction logs?
> > > > >> > > >
> > > > >> > > > MC
> > > > >> > > >
> > > > >> > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >>
> > > > >>
> > > > >
> > > > >
> > > > >.
> > > > >
> > >
> > >
> >
> >
>|||I know these steps.
But these steps do not alter the database log file size.
Didn't do it.
MC
>--Original Message--
>These steps should give the result you're looking for:
>BACKUP LOG WITH NO_LOG
>DBCC SHRINKDATABASE
>(full BACKUP)
>DBCC SHRINKDATABASE
>--
>Nico De Greef
>Belgium
>Freelance Software Architect
>MCP, MCSD, .NET certified
>"mcamci@.ozoptics.com"
<anonymous@.discussions.microsoft.com> wrote in message
>news:2ae501c3fcac$c7c6e0e0$a401280a@.phx.gbl...
>> Gentlmen,
>> I don't think we are talking about the same think here.
>> I already tried DBCC
>> SHRINKFILE or DBCC SHRINKDATABASE procedures as per
given
>> MS artciles. These procedures does not change ALLOCATED
>> database file size, nor database lof file size.
>> I am trying to change allocated database file size.
>> Once you allocated a database size after the database
>> creation, you can not reduce the allocated size as per
MS.
>> These procedures doesn't do that. What it did is this.
>> My log file size in SQL Enterprise manager shows 1.8G
>> It showed that 1.6G was used by data. DBCC
>> SHRINKFILE or DBCC SHRINKDATABASE procedures
>> brought the used space down to 80M. But the actual
>> allocated log file size did not change.
>> The only easy option I can think of is to create a new
>> database and DTS all the data to new database.
>> MC
>> >--Original Message--
>> >No, I didn't say it was possible with ALTER DATABASE,
in
>> my opinion it
>> >isn't.
>> >But it is possible to shrink the file, even if the
active
>> part is at the end
>> >of the file.
>> >I'll try to dig up the exact script soon.
>> >
>> >--
>> >Nico De Greef
>> >Belgium
>> >Freelance Software Architect
>> >MCP, MCSD, .NET certified
>> >
>> >"Tibor Karaszi"
>> <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote in
>> >message news:egDGqK$%23DHA.1796@.TK2MSFTNGP12.phx.gbl...
>> >> Nico,
>> >>
>> >> This makes me curious. It seems like you are saying
>> that you can shrink
>> >the
>> >> size of a database file using ALTER DATABASE without
>> the usage of DBCC
>> >> SHRINKFILE or DBCC SHRINKDATABASE. This is news to
me,
>> and I would be very
>> >> interested to see how you accomplish that. I do in no
>> way doubt what you
>> >are
>> >> saying, this is out of pure curiosity, as I though it
>> wasn't possible. :-)
>> >>
>> >> (Just for the record, I do not consider deleting the
>> transaction log file
>> >a
>> >> usable method, as we see posts here on a daily basis
>> where deletion of
>> >> transaction log files renders corrupt databases.
>> Extreme caution has to be
>> >> taken if you even want to consider taking that
path. :-
>> ) )
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> Archive at:
>> >>
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >>
>> >>
>> >> "Nico De Greef" <ndg@.denco.be> wrote in message
>> >> news:eNasIF$%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
>> >> > Yes you can, i've written this script a few years
ago
>> and it is a
>> >specific
>> >> > order of SQL statements.
>> >> > Not an 'out of a book' solution but it is possible.
>> >> >
>> >> > (Not that I don't want to give it to you, but i
have
>> to look it up)
>> >> >
>> >> > --
>> >> > Nico De Greef
>> >> > Belgium
>> >> > Freelance Software Architect
>> >> > MCP, MCSD, .NET certified
>> >> >
>> >> > "Tibor Karaszi"
>> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> >> in
>> >> > message news:OvF6EB$%
>> 23DHA.2180@.TK2MSFTNGP09.phx.gbl...
>> >> > > You can't reduce the size of the log using ALTER
>> DATABASE (which by
>> >your
>> >> > > messages is what you are trying to do). You need
to
>> use DBCC
>> >SHRINKFILE,
>> >> > > which is restricted in the sense that it can only
>> shrink from the end
>> >of
>> >> > the
>> >> > > file towards the beginning of the file. And if
>> there are log records
>> >at
>> >> > the
>> >> > > end of the file, it cannot be shrunk.
>> >> > >
>> >> > > Check out below KB articles:
>> >> > >
>> >> > > 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
>> >> > >
>> >> > > Log File Grows too big
>> >> > > http://www.support.microsoft.com/?id=317375
>> >> > >
>> >> > > Log file filling up
>> >> > > http://www.support.microsoft.com/?id=110139
>> >> > >
>> >> > > Considerations for Autogrow and AutoShrink
>> >> > > http://www.support.microsoft.com/?id=315512
>> >> > >
>> >> > > http://www.mssqlserver.com/faq/logs-shrinklog.asp
>> >> > >
>> >> > >
>> >> > > --
>> >> > > Tibor Karaszi, SQL Server MVP
>> >> > > Archive at:
>> >> > >
>> >> >
>> >>
>> >http://groups.google.com/groups?
>> oi=djq&as_ugroup=microsoft.public.sqlserver
>> >> > >
>> >> > >
>> >> > > "mcamci@.ozoptics.com"
>> <anonymous@.discussions.microsoft.com> wrote in
>> >> > message
>> >> > > news:135201c3fbb0$ee90d1b0$a501280a@.phx.gbl...
>> >> > > > Hello,
>> >> > > > My SQL server 7 database, data size is 150M,
but
>> only 82M
>> >> > > > occupied with data.
>> >> > > >
>> >> > > > Transaction log size is 1.8Gig, and only 20M is
>> used the
>> >> > > > actual data.
>> >> > > >
>> >> > > > This is I think, due to not backing up
>> transaction data
>> >> > > > for more then a year.
>> >> > > >
>> >> > > > Now we are trying to reduce the database
>> transaction log
>> >> > > > size, but SQL server doesn't allow us to reduce
>> it.
>> >> > > > It says, "your new size must be larger then
>> current size."
>> >> > > > We tried to use alter database commands, says
the
>> same
>> >> > > > message. Is there another way to reduce the
>> allocated
>> >> > > > space to transaction logs?
>> >> > > >
>> >> > > > MC
>> >> > > >
>> >> > >
>> >> > >
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>

Tuesday, March 27, 2012

Changing the size of a Varchar Field

Changing the ReportViewer control size.

Changing the look of Report Manager

Thursday, March 22, 2012

Changing the command buffer size?

Tuesday, March 20, 2012

Changing table column size

Wednesday, March 7, 2012

Changing Parameter Dropdown Size

Changing paper size through asp

Thursday, February 16, 2012

changing fieldsize in a replicated database

Changing field length for Merge

Sunday, February 12, 2012

Changing database size

Changing database size

database collation

Blog Archive

About Me